やりたかったこと
例えば、こういう何かの購買日が管理されているテーブル( purchase_log とする)があったとして、
| purchase_date | user_id |
| --- | --- | --- |
| 2016-12-01 | AAA |
| 2016-12-02 | BBB |
| 2016-12-02 | AAA |
| 2016-12-04 | BBB |
ユーザーごとに 何日おきに購入されているか を調べたかった。
やりかた
サブクエリを駆使して行同士の差分を計算する方法もあるが、ソート可能なユニークキーが存在していないといけないなどスキーマに依存する。
そこで LAG() というウィンドウ関数を使えば、ソートに気をつけるだけでできる。やったね！
SQLの例
purchase_log テーブルの場合、こんな感じで購買日ごとの購買間隔を計算する。
SELECT
purchase_date,
user_id,
purchase_date - LAG(purchase_date, 1) OVER (ORDER BY user_id ASC, purchase_date ASC) AS interval_days
FROM purchase_log
ORDER BY user_id ASC, purchase_date ASC;
とすると、
purchase_date | user_id | interval_days
---------------+---------+---------------
2016-12-01 | AAA |
2016-12-02 | AAA | 1
2016-12-02 | BBB | 0
2016-12-04 | BBB | 2
こんな感じで返ってくる。
あとは一時テーブルに入れるなりして、邪魔なデータ( user_id の境界とか)を良い感じにして、平均購買間隔などそれっぽい指標を集計すればよろし。
おわりに
私の場合は十分実用に耐えうる処理速度でした。window関数活用していきたい。
BigQueryでも同様のことができそう。