MySQLの複雑な条件の場合の出力方法について

MySQL初学者です。

有料会員のユーザーについて、ユーザーごとに、4月の日ごとのログイン回数・4月の日ごとのログイン時のプレイ回数
を出力したいのですが、、

条件の多い場合の問題が1日かけても解けません(T ^ T)

テーブル1:LoginT
uid:ユーザーID
ldata:ログイン時間
play:ゲームのプレイ回数

テーブル2:MemberT
uid:ユーザーID
member:有料会員＝１

MySQL
1create table LoginT(lid int primary key auto_increment,uid int,ldate timestamp,play Int);
2insert into LoginT(uid,ldate,play) values
3(1,'2011-04-06 15:55:00',1),
4(1,'2011-04-06 16:25:00',1),
5(1,'2011-04-06 20:01:00',0),
6(1,'2011-04-10 15:50:00',1),
7(1,'2011-04-10 19:21:00',1),
8(2,'2011-04-05 10:00:00',1),
9(2,'2011-04-06 15:11:00',1),
10(2,'2011-05-02 10:15:00',0),
11(3,'2011-03-26 12:00:00',1),
12(3,'2011-04-03 15:00:00',0),
13(3,'2011-04-03 15:50:00',1),
14(3,'2011-04-10 15:00:00',1),
15(4,'2011-04-12 07:15:00',1),
16(4,'2011-04-12 05:52:00',1),
17(4,'2011-05-02 05:15:00',0),
18(4,'2011-05-12 05:53:00',0),
19(4,'2011-05-22 00:33:00',1);
20
21
22
23create table MemberT(uid int primary key,member tinyint);
24insert into MemberT values
25(1,1),
26(2,1),
27(3,0),
28(4,1);
29

MySQLのバージョンは8.0.19です。

わかる方いらっしゃいましたら、回答お願いいたします。

hope_mucci

2020/05/26 21:58

MySQLのバージョンを追記してください。 8.0かそうじゃないかで回答が大きく違ってきます。

hope_mucci

2020/05/26 22:11 編集

あと、添付のSQLを動かすとエラーになります。動くように訂正してください。また、実データのおおよその件数と実際に発行してみたクエリも編集で追記してください。

u-tyan

2020/05/27 03:34

MySQLのバージョン記入しました。動くように訂正しました。練習問題のようなものなので、実データの件数は添付のままと考えてもらえれば問題ないです。投稿不慣れなもので、よろしくお願いいたします。

行動規範の内容に同意します

回答1件

ベストアンサー

正直、初学者でこの問題はきつい。
学習の順番を間違えていないだろうか。

クエリの作り方は、

まずLoginTとMemberTをInner Joinする。

その際条件でmember=1を入れておけば有料ユーザーのみ絞り込むことができる。
また、さらにldateを範囲指定で2011-04に絞り込めば後続の集計で手間が省ける。
日ごとの集計にしたいので日付を'yyyy-mm-dd'形式にフォーマットしておく。

次に、上記クエリからログイン回数、プレイ回数を集計する。

幸いなことにmysql8なので、with句やwindow関数が使える。
partition by でユーザーIDと日付単位で集計すれば簡単に集計できる。
count(*)でログイン回数（レコード数がログイン回数ってことでいいよね）
sum(play)でプレイ回数。

以上をクエリにすると以下になる。
with句を知らないなら、ここでバッチリ覚えてください。クエリの構築が全然楽になります。

SQL
1with q1 as (
2    select
3        lt.uid
4        ,date_format(lt.ldate,'%Y-%m-%d') as logindate
5        ,lt.play
6    from
7        LoginT lt
8    inner join MemberT mt
9        on lt.uid = mt.uid
10        and mt.member = 1
11    where ldate >= '2011-04-01' and ldate < '2011-05-01'
12)
13select distinct
14    uid
15    ,logindate
16    ,count(*) over(partition by uid,logindate) as logincount
17    ,sum(play) over(partition by uid,logindate) as playcount
18from q1;
19-- 正直なところ、もっと簡単な問題から学習を積み上げていった方が良い気がする。
20-- 独学でやっているなら一足飛びし過ぎだし、講師がいるのならちょっと進め方に難がある。