ウェブ分析ではセッション(訪問)を数えますが、データはイベントの連続です。一般的なルールでは、新しいセッションは訪問者の最初のイベントで始まるか、イベントのない状態が30分を超えた後に始まります。
3つのステップがあり、それぞれウィンドウ関数を使います。
- 新しいセッションに印を付けます:訪問者ごとの
LAG(event_time)を使い、前のイベントがないか、間隔が30分を超える場合にis_new = 1にします。
- 番号を付けます:訪問者ごとに時間順で
SUM(is_new)の累計を求めると、1, 1, 1, 2, 2, ...となります。
- (訪問者、セッション番号)ごとに集計します:開始、終了、イベント数、購入に至ったかどうかです。
SUM(is_new) OVER (PARTITION BY visitor_id ORDER BY event_time, event_id) AS session_no
2つのイベントが同じタイムスタンプを持つことがあるため、並び順に同順位を決める列(event_id)を追加してください。30分ルールは慣習であり、ツールによって異なるため、使ったルールを数値と一緒に明記してください。