今回想定するケース
何らかの event のログを保持する以下のような event_logs テーブルを考えます。
カラムは、 ユーザのid (user_id) と ユーザの行動イベントのタイムスタンプ (event_timestamp) とします。
例えばスマホのログに多いのですが、チャタリング(微細な振動などによって生じるクリック)などで無駄にログが残っていたりすると、ノイズになってしまうのでこういう集約がしたくなります。
今回は、注目しているレコードとその次のレコードとの時間の差が1分以内であれば同じレコードに集約します。今回はログの中でのminを取ります。
「user_id ごとに group by して min でいいじゃん」と思われるかもしれませんが、hogeさんが 06:00 頃に使ったログと 06:30 のログは別に残したいので、それだけでは実現できないのが今回の少し難しいところです。
解法 (クエリ)
1 2 3 4 5 6 7 8 9 | # 直近のログとの差が1分以上だったら、違うsession_idを振っていくクエリ select user_id, event_timestamp, count(willcnt) over(partition by user_id order by event_timestamp) as session_id from ( select user_id, event_timestamp, case when event_timestamp-interval '1' minute >= Lag(event_timestamp) over(partition by user_id order by event_timestamp) then 1 else 0 end as willcnt from event_logs) order by user_id, event_timestamp |
解説
一言で言えば、Lag関数を使って1分以内のレコードであれば1のラベルをそうでなければ0を振り、userごとにidをカウントすることで別のsession_idを振っています。
lag(x[, offset[, default_value]]) → [same as input]
Lag関数はoffsetに指定された行の値を返します。現在の行が0、デフォルトでは1となり、前の行の値となります。
