複数の条件を組み合わせて抽出したい。

Question

create table mydb.login_log (user_id integer, timestamp timestamp);
create table mydb.charge_log (user_id integer, timestamp timestamp, category char(10));
create table mydb.quest_log (user_id integer, timestamp timestamp, quest_id integer);

LOAD DATA LOCAL INFILE 'C:/Windows/Temp/login_log.csv' INTO TABLE mydb.login_log
 FIELDS
 TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'C:/Windows/Temp/charge_log.csv' INTO TABLE mydb.charge_log
 FIELDS
 TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n';

LOAD DATA LOCAL INFILE 'C:/Windows/Temp/quest_log.csv' INTO TABLE mydb.quest_log
 FIELDS
 TERMINATED BY ','
 ENCLOSED BY '"'
 LINES TERMINATED BY '\r\n';

```### 前提・実現したいこと
sqlで、複数の条件に合致したデータを抽出したい。

具体的には、
ログインの履歴を持つデータ(login_log)と
課金の履歴を持つデータ(charge_log)と
クエストの履歴を持つデータ(quest_log)を用いて
1月に課金(charge_logのcategoryがbuy)したユーザーについて、
ユーザーごとに、
ユーザーID・2月のログイン回数・2月にクエストを行った日数・2月にクエストを行った回数を出力したい。

(ex)
login_log
user_id | timestamp
001	|2020-01-01 12:10:00
001	|2020-01-18 19:15:00
002	|2020-01-17 09:40:00
...

charge_log
user_id | timestamp          |category
001	|2020-01-01 12:10:00 |buy
002	|2020-01-17 09:40:00 |present
...

quest_log
user_id | timestamp          |quest_id
001	|2020-01-01 12:15:00 |10001
002	|2020-01-17 09:47:00 |10002
...


### 発生している問題
出力したログイン回数、クエストを行った日数、クエストを行った回数の数値が誤っている。

### 該当のソースコード
SELECT
  t1.user_id AS 'ユーザーID'
  ,COUNT(t2.user_id) AS '2月のログイン回数'
  ,COUNT(DATE(t3.timestamp)) AS '2月でクエストを行った日数'
  ,COUNT(t3.quest_id) AS '2月でクエストを行った回数'
FROM charge_log t1
  LEFT JOIN login_log t2
    ON
      t1.user_id = t2.user_id
    AND
      MONTH(t2.timestamp) = 2
  LEFT JOIN quest_log t3
    ON
      t1.user_id = t3.user_id
    AND
      MONTH(t3.timestamp) = 2
WHERE
  t1.category = 'buy'
AND
  MONTH(t1.timestamp) = 1
GROUP BY
  t1.user_id
;

### 試したこと
一旦ログイン回数だけを出力するsqlを考えて実行したところ、正しい数値を出力することができたが
全体のsqlに組み込む方法がわからない。

実行したsql：
select
t1.user_id
,count(t1.user_id) as '2月のログイン回数'
from login_log t1
left join charge_log t2
on t1.user_id = t2.user_id
where month(t1.timestamp) = 2
and
  t2.category = 'buy'
and
  month(t2.timestamp) = 1
group by
  t1.user_id
;

Comments

[Orlofsky]

質問のテーブル定義とデータは CREATE TABLE文とINSERT文で、SELECT文も含めて https://teratail.com/help/question-tips#questionTips3-7 の [コード] で提示できると動作確認し易いので、適切なコメントが付き易いです。

[yunak]

お教えいただきありがとうございます。
今回使用しているデータが外部から提供されたデータであり、公開するのに抵抗があるため、類似のデータとして例を記載しております。
それだと回答しにくいかとは思うのですが、記載できず申し訳ございません。

[Orlofsky]

質問を修正してください。
SELECT文が書いてあるからデータはテーブルに入っているのはわかっているから、CSVの話は全部削りましょう。
charge_log, login_log , quest_log のテーブルの説明をCREATE TABLE文とINSERT文に直してください。

[sazi]

ログイン, 課金, クエストはそれぞれで独立（ログインだけとか課金だけとかがある）していますか？

[yunak]

修正しました。
修正対応をすることが初めてなので、対応方法等誤っていたらお教えいただけると幸いです。

お手数をお掛けしますが、何卒宜しくお願いいたします。

[yunak]

完全に独立はしていないです。

ログインをした後に、複数回課金やクエストが行われることはありますが、
ログインを一度もせずに課金やクエストが行われることはありません。

課金とクエストの間には関係性はありません。

[Orlofsky]

sazi さんにお任せします。疲れました。

Answer 1

1月に課金(charge_logのcategoryがbuy)したユーザーについて、
ユーザーごとに、ユーザーID・2月のログイン回数・2月にクエストを行った日数・2月にクエストを行った回数を出力したい。

複数のテーブルで集計する際には、集計する単位で揃える必要があります。

select  login.user_id
      , login.cnt as login_count
      , coalesce(quest.day_count, 0) as quest_day_count
      , coalesce(quest.cnt, 0) as quest_count
from (
        select user_id, count(*) as cnt
        from login_log
        where MONTH(timestamp) = 2
          and user_id in (
                select user_id
                from charge_log
                where MONTH(timestamp) = 1 and category = 'buy'
              )
        group by user_id
      ) login
      left join (
        select user_id, count(*) as cnt, count(distinct date(timestamp)) as day_count
        from quest_log
        where MONTH(timestamp) = 2
        group by user_id
      ) quest
      on  login.user_id=quest.user_id

Comments

[yunak]

ご回答いただきありがとうございます。

理解能力が足りず大変申し訳ないのですが、「集計する単位で揃える」というのは
ご記載いただいたSQLで言うとどの部分にあたりますか？

coalesceを調べてみたところ「リストの最初の非NULL値を返す」ということだったので
これはまた別の目的で使用している句だと認識しております。

また、記載していただいたSQLを実行したところ、
5月にログインしていないユーザーのレコードも出力されました。

where MONTH(timestamp) = 5
の部分で条件が絞られるはずだと思っており、原因が思い当たりません。

何度も質問してしまい大変申し訳ございません。
ご教授いただけると幸いです。

[sazi]

この場合の揃えるというのはuser_idです。
user_id毎に集計するのですから、集計してから結合します。
結合してdistinctでも場合によっては大丈夫でしょうけど。

[sazi]

>5月にログインしていないユーザーのレコードも出力されました。
質問でOKだったと言われる内容とその部分のSQLは変わりませんけど？
条件はあくまで質問のものを使用していますので、分かりませんが、月だけではなく、年も必要だったりしませんか？

[sazi]

coalesce()は、ログインしているけど、クエストしていないような場合にはNullになるので0に置き換える考慮です。

[sazi]

条件を合わせました。
※年月じゃなくていいのかは気になりますが。

[yunak]

「集計する単位で揃える」について、user_id毎という意味であるという旨、理解しました。
ありがとうございます。

質問に記載したSQLにミスがあったので編集しました。
失礼いたしました。

coalesce()の用途についても理解できました。
ありがとうございます。

今存在しているデータは年を跨ぐものがないので月だけを指定しておりましたが、
今後運用していくことを考えると年も必要になります。
ご指摘いただき、ありがとうございます。

教えていただいた内容を踏まえて、書き直してみます。
本当にありがとうございます。