[PrestoSQL] 前のレコードとの差が◯秒(分)以内なら1つのレコードに集約する時のメモ

今回想定するケース

何らかの event のログを保持する以下のような event_logs テーブルを考えます。
カラムは、ユーザのid (user_id) とユーザの行動イベントのタイムスタンプ (event_timestamp) とします。
例えばスマホのログに多いのですが、チャタリング(微細な振動などによって生じるクリック)などで無駄にログが残っていたりすると、ノイズになってしまうのでこういう集約がしたくなります。
今回は、注目しているレコードとその次のレコードとの時間の差が1分以内であれば同じレコードに集約します。今回はログの中でのminを取ります。

「user_id ごとに group by して min でいいじゃん」と思われるかもしれませんが、hogeさんが 06:00 頃に使ったログと 06:30 のログは別に残したいので、それだけでは実現できないのが今回の少し難しいところです。

解法 (クエリ)

# 直近のログとの差が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

# 直近のログとの差が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となり、前の行の値となります。

参考

直近との差が1分以上ならインクリメント