あるアプリケーションへのアクセスログを保存している,以下のようなテーブルがあったとする.

event_date user_id
2020-01-01 A
2020-01-02 A
2020-01-02 B
2020-01-02 C
2020-01-03 A
2020-01-03 C

単純な DAU を調べたければ簡単なのだが,「ユーザーの継続利用」に関して調べたいこともある.

例えば,ある時点までで「過去 N 日間連続してログインしたユーザー数」とかである.上のサンプルデータの時に,「過去 2 日の連続利用」を調べたかった,この例では以下のようなテーブルを作成できれば良い.

event_date count
2020-01-01 0
2020-01-02 1
2020-01-03 2

なぜなら,2020-01-01 は初日なのでもちろん継続ユーザーはいない.2020-01-02 になると,ユーザー A が前日もログインしているので count は 1 になる.同様に 2020-01-03ユーザー Aユーザー C で 2 になる.

このデータを得るための SQL はいくつかあるだろうが,window 関数の一つである LAG 関数 を使うのがシンプルで良い.LAG 関数を使うと,対象行の指定のグループにおける N 個前のデータサンプルを参照することができる.文だと説明がわかりにくいので上のテーブルを使っての具体例を挙げる.

select
	event_date,
	count,
	LAG(event_date, 1) OVER(PARTITION BY user_id ORDER BY event_date) as lag_value
from
	sample_table

これを実行すると,

event_date user_id lag_value
2020-01-01 A null
2020-01-02 A 2020-01-01
2020-01-02 B null
2020-01-02 C null
2020-01-03 A 2020-01-02
2020-01-03 C 2020-01-02

が得られる.

LAG(event_date, 1) は,新しいカラムには event_date を入れたい,1 個前の情報を抽出したい,という意味. その後の OVER の部分では,user_id でグループングして前の情報をとってくる,さらに event_date でソートしている. つまり,PARTITION BY user_id を設定することで,ユーザーごとに直前のアクセス日の抽出を実現している.

このテーブルを得ることができたらあとは簡単.またいくつか方法はあるが,現時点の N 日前の日付と N 回目のアクセス日が一致しているか調べれば良い.

DATA_SUB(event_date, interval 1 day) = lag_value のような事をすれば以下のテーブルが得られる.

event_date user_id lag_value seq_access
2020-01-01 A null false
2020-01-02 A 2020-01-01 true
2020-01-02 B null false
2020-01-02 C null false
2020-01-03 A 2020-01-02 true
2020-01-03 C 2020-01-02 true

最後に,event_dategroup by して,seq_access の sum を取れば,各日に連続 N 回ログインしているユーザー総数を集計できる.


参考