Zenn
😺

SQLをかいてみた：MAUの日次推移

2023/07/14に公開3件
SQL
tech

がく＠ちゅらデータです。

「MAUの日次推移を出したいんですが、SQLでどうかいたらいいかわからないの、教えてドラ○もん！」
みたいな相談を受けたので、考えていましたので、メモとして残しておきます。

確認環境：Snowflake　（ date_trunc が他DBMSでは動かないかもなので、その場合は適宜書き換えてください）

こんなテーブルがあったとします。日毎のログインユーザを保持しています。

user_id login_date user_id login_date
user_a 2021-06-01 user_e 2021-06-04
user_b 2021-06-01 user_f 2021-06-04
user_a 2021-06-01 user_g 2021-06-04
user_b 2021-06-01 user_h 2021-06-04
user_c 2021-06-01 user_i 2021-06-04
user_a 2021-06-02 user_j 2021-06-04
user_b 2021-06-02 user_c 2021-07-01
user_a 2021-06-03 user_a 2021-07-02
user_b 2021-06-03 user_b 2021-07-02
user_d 2021-06-03 user_a 2021-07-03
user_b 2021-07-03

みたいなデータがあったとして、ここから欲しいデータとしては、月初からのユニーク user_id 数　を日毎に出したい

login_date count (unique user_id) user_id
2021-06-01 3 user_a, user_b, user_c user_a, user_b, user_a, user_b, user_c
2021-06-02 3 user_a, user_b, user_c user_a, user_b
2021-06-03 4 user_a, user_b, user_c, user_d user_a, user_b, user_d
2021-06-04 10 user_a, user_b, user_c, user_d, user_e, user_f, user_g, user_h, user_i, user_j user_e, user_f, user_g, user_h, user_i, user_j
2021-07-01 1 user_c user_c
2021-07-02 3 user_a, user_b, user_c user_a, user_b
2021-07-03 3 user_a, user_b, user_c user_a, user_b 
with hoge as ( 
　　　　select 
    login_date
    , user_id
　　　　from 
    user_login
　　　　group by 
    login_date
    , user_id
)
, hoge2 as (
　　　　select distinct
    h1.login_date as login_date,
    h2.user_id as user_id
　　　　from 
    hoge h1
    left outer join hoge h2
    on h1.login_date >= h2.login_date
       and date_trunc('month', h1.login_date) = date_trunc('month', h2.login_date)
)
select
    login_date
    , count( user_id )
from 
    hoge2
group by 
    login_date
order by 
    login_date
;
  • 自己結合 ( hoge h1 left outer join hoge h2 )
  • onに、h1.login_date >= h2.login_date
    • 複数が紐づく形になる
  • 月ごとにやりたいので、h1、h2は、同じ月にしてる

ウィンドウ関数でできないかなぁ・・・・と思ったのですが、いい方法が思いつかなかった＞＜
※　　with句に「hoge」はよくないので、ちゃんと使うときはしっかり意味のわかる文字列にしましょう！

累計を出すだけなら、自己結合は必要ないかなぁとも思ったのですが、月初からその日までのCOUNT　DISTINCT　だったので、ちょっとパフォーマンスに難がありそうなSQLになってしまいました

もし、もっといい解決方法があるよ！という方がいたらぜひ教えてください！

Discussion

indigo13loveindigo13love

記事ありがとうございます！

  1. 各 user_id について、月ごとの最初のログイン日 min(login_date) を計算する
  2. 最初のログイン日ごとの user_id の数を count() する (= 各日に新しく追加された user_id 数)
  3. 各日に新しく追加された user_id 数について、各月内で login_date 順に sum ウィンドウ関数で累積和を計算する

という手順にすることで、範囲自己結合のような結合爆発が起こり得る結合を取り除くことができそうです。

下記の方法でも、結果に「新規ユーザが一人もいない日」を含めるための外部自己結合が含まれていますが、これは必ず 1:1 なので結合爆発が起こることがありません。

with
first_logins as (
    select user_id, date_trunc(month, login_date) month, min(login_date) first_login_date
    from history
    group by user_id, month
),
first_login_counts as (
    select first_login_date, month, count(user_id) cnt
    from first_logins
    group by first_login_date, month
)
select c.login_date, sum(ifnull(f.cnt, 0)) over (partition by date_trunc(month, login_date) order by login_date) cnt
from (select distinct login_date from history) c
left join first_login_counts f on c.login_date = f.first_login_date
order by login_date
;
/*
LOGIN_DATE	CNT
2021-06-01	3
2021-06-02	3
2021-06-03	4
2021-06-04	10
2021-07-01	1
2021-07-02	3
2021-07-03	3
*/
bgcanarybgcanary

ページを更新したらベストなコメントがついていたので、補足します。(最初にコメントしたかった...)

Window関数でシンプルに書こうとすると、以下のクエリになりますが、エラーになります。( count句の後のDISTINCT を抜くと重複込みでカウントしてくれます。)

SELECT
    DISTINCT
    DATE_TRUNC('month', login_date) AS login_month,
    login_date,
    count(DISTINCT user_id) OVER (
            PARTITION BY login_month
            ORDER BY LOGIN_DATE)
FROM
    user_login
ORDER BY
    login_date
;

Error: distinct cannot be used with a window frame or an order.

count(distinct) と over(order by) の併用はできないみたいです。

ウィンドウ関数内でキーワード DISTINCT を使用することは禁止されており、コンパイル時間エラーが発生します。

https://docs.snowflake.com/ja/sql-reference/functions/count#usage-notes

上記エラーを回避するため、あらかじめ月ごとにユーザーが最初にログインした日を求めて、重複を排除すると、Window関数でうまく求められるようになります。

WITH first_user_login AS (
    SELECT
        DATE_TRUNC('month', login_date) AS login_month,
        MIN(login_date) AS first_login_date,
        user_id
    FROM
        user_login
    GROUP BY
        login_month,
        user_id
)
SELECT
    DISTINCT
    first_login_date AS "日付",
    count(user_id) OVER (
            PARTITION BY login_month
            ORDER BY first_login_date) AS "MAUの日時推移"
FROM
    first_user_login
ORDER BY
    first_login_date
;

ただ、このクエリでは「新規ユーザが一人もいない日」の行が無いため、 indigo13love さんのコメントのようなクエリを実行する必要があります。

Gaku Tashiro＠ちゅらデータエンジニアGaku Tashiro＠ちゅらデータエンジニア

@bgcanary さんへ

コメントありがとうございます！
一日コメントが遅れてしまい、ほんと申し訳ないです…

distinctの検証などほんとありがとうございます！そこは気にかけておりませんでした
SQLって、ほんと奥が深いと

うまくいかーん、けど結果だけは出た、もっといいのがあるよなぁと恥を忍んで記事化してよかったです！ありがとうございました！