【BigQuery】分析関数で連続ログイン日数を集計する

 

 

こんにちは、ミナピピン(@python_mllover)です。今回は業務で「ユーザーごとの連続ログイン日数」を集計したので、その時に使ったコードを保存しておこうと思います。

 

分析関数とは？

 

まず分析関数とは簡単に言うと「集合関数と同じ集計動作をそれぞれの行に制限範囲で実行するもの」です。ここでいう集合関数とは、MAXやSUMやAVG等、GROUP BYと共に使って行の特徴量をまとめて集計する関数のことです。

 

分析関数と集合関数との違いは「行をまとめるかどうか」という点にあります。分析関数はそれぞれの行で集計計算し結果を返します。ここが集合関数との大きな違いです。集合関数ではGROUP BYの同じカラム値をもつ全行を一つに集計しますが、分析関数では集計対象となる行の範囲を任意で指定できます。

 

集計関数に続くOVER句でこの範囲指定を行います。よく使う一般的な集合関数は後ろにOVER句をつけれれば、分析関数（MINとかMAXとかAVG）になるという認識でおっけーです。別の見方をすると、それぞれの行ので分析関数によって計算された集計値というものは、その行以外の行を参照して得た値の集計結果です。

 

たとえば、ある行を処理している時に、一つ前の行と一つ後の行から得た値の合計をだす等の処理が自己結合することなく可能になります。つまり、分析関数は他の行を参照できる関数です。（これが集計では非常に便利です。実務の集計ではGROUP BY一発で簡単に終わる集計はなかなかありません。）

 

なので、実務では今回のテーマみたいなユーザーごとの連続ログイン日数、連続課金日数みたいな連続値や、課金前に行っていたクエスト・強化・イベントクエストなどの各行動を行動順に連番を振りたい場合などに使用します。

 

 

テストデータを用意する

 

というわけで実際に分析関数を使用して連続最長ログイン日数を取得したいと思います。まずテストデータを用意します。実行環境は「Bigquery」です。

 

<データセット>

user_id	login_date
1	2020/1/1
1	2020/1/2
1	2020/1/3
1	2020/1/4
2	2020/1/1
2	2020/1/2
2	2020/1/4
3	2020/1/1
3	2020/1/3
3	2020/1/4
3	2020/1/5
3	2020/1/8

 

連続ログイン日数を集計する

 

SELECT
  user_id
  ,MAX(consecuitive_days) AS max_consecuitive_days
FROM 
  (
    SELECT
      user_id
      ,grp
      ,COUNT(1) consecuitive_days
    FROM 
      (
        SELECT
          user_id, 
          COUNTIF(step > 1) OVER(PARTITION BY user_id ORDER BY login_date) AS grp
        FROM
          (
            SELECT
              user_id
              ,login_date
              ,DATE_DIFF(login_date, LAG(login_date) OVER(PARTITION BY user_id ORDER BY login_date), DAY) AS step 
            FROM
              `データセット`
           )
        ) 
    GROUP BY
      user_id
      ,grp
  ) 
GROUP BY
  user_id
ORDER BY
  user_id

 

 

まずDATE_DIFF()とLAG()で前の行との日数の差分を取得します。ここでOVER(PARTITION BY user_id ORDER BY login_date)と分析関数を用いることでユーザーごとにログイン日の降順にしたときの差分を取るという風に指定してあげることでユーザーごとの前のログイン日との差分が取得できます。↓

 

1	1	2020-01-01	null
2	1	2020-01-02	1
3	1	2020-01-03	1
4	1	2020-01-04	1
5	2	2020-01-01	null
6	2	2020-01-02	1
7	2	2020-01-04	2
8	3	2020-01-01	null
9	3	2020-01-02	1
10	3	2020-01-04	2
11	3	2020-01-05	1
12	3	2020-01-06	1

 

そしてその上のクエリでCOUNTIF(step > 1) OVER(PARTITION BY user_id ORDER BY login_date) AS grpとすることで日数の差分が1以上になる＝連続が途切れると判定することでユーザーごとのログイン動向から連続日数を取得します。↓のuser_id＝3のデータに注目してもらえば分かりますが、連続ログインが途切れるごとにgrpに数値が1増えています。

 

1	1	2020-01-01	0
2	1	2020-01-02	0
3	1	2020-01-03	0
4	1	2020-01-04	0
5	2	2020-01-01	0
6	2	2020-01-02	0
7	2	2020-01-04	1
8	3	2020-01-01	0
9	3	2020-01-02	0
10	3	2020-01-03	0
11	3	2020-01-05	1
12	3	2020-01-06	1
13	3	2020-01-10	2
14	3	2020-01-11	2
15	3	2020-01-12	2
16	3	2020-01-13	2

 

あとはこれをuser_idとgrpをキーとしてGROUP BYで集計すればユーザーごとの各連続ログイン日数を取得できます。

 

行	user_id	grp	consecuitive_days
1	1	0	4
2	2	1	1
3	2	0	2
4	3	0	3
5	3	2	4
6	3	1	2

 

最後はこれをuser_idで集計してMAX(consecuitive_days)で最大連続ログイン日数を取得することが可能です。平均連続ログイン日数とかならAVG()とかで集計できますね。

 

分析関数には他にもROW_NUMBER()とか色々あるので興味のある方は調べてみてください。こんな感じで分析関数とかUDFを使いこなせるようになると集計の幅も広がりますし、SQLを使いこなすという域に入ってくると思います。

 

関連記事：【Bigquery】UDFを使って多次元のJSON配列の要素数を取得する