RedShiftでSQLだけで前の行との比較を行う / LAG()関数

やりたかったこと

例えば、こういう何かの購買日が管理されているテーブル( 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でも同様のことができそう。