sucher

sucher(ズーハー)はエンジニアを応援します。

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

82 views
約 2 分

スポンサーリンク



今回想定するケース

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

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

解法 (クエリ)

解説

一言で言えば、Lag関数を使って1分以内のレコードであれば1のラベルをそうでなければ0を振り、userごとにidをカウントすることで別のsession_idを振っています

lag(x[, offset[, default_value]]) → [same as input]
Lag関数はoffsetに指定された行の値を返します。現在の行が0、デフォルトでは1となり、前の行の値となります。

参考

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

スポンサーリンク





この記事を書いた人

Toshiki Sasaki
主にSQLとPythonの記事を書きます。
高専出身で電気電子を専攻していましたが、気まぐれで大学院では機械読解の研究に従事していました。
電気電子・NLP系の記事もちらほら書いていけたらいいなと思ってます。
Follow :

コメント

*
*
* (公開されません)

Share / Subscribe
Facebook Likes
Tweets
Hatena Bookmarks
Pinterest
Pocket
Evernote
Feedly
Send to LINE