「ユーザーのイベントログで、ユーザーが一番最後に行ったイベントを抽出する」など、 グループで分けたデータの最後のレコードを取得したい時があります。
ウィンドウ関数を使うと、下記のような形で求めることができます。
WITH tbl AS ( SELECT * ,ROW_NUMBER() OVER( PARTITION BY user_id ORDER BY event_time DESC ) AS row_num FROM event_log ) SELECT * FROM tbl WHERE row_num=1
「PARTITION BY」でグループ単位を指定し、「ORDER BY」で並び順を指定し、「ROW_NUMBER()」で重複しない連番を振ります。
連番が１、つまり、グループ内で一番最初のレコードが、求めたいレコードになるようにグループ単位と並び順を設定して、連番の番号が１のものだけ抽出します。
上記例では、ユーザー単位で、時系列に降順で並べることにより、番号１のレコードが時系列で一番最後のレコードになるようにしています。
応用
- ユーザーが一番最初に行ったレコードを取得する場合は、昇順に並べることにより取得できます。
- 「ORDER BY event_time ASC」
- その日アクセスしたユーザーが、一番最後に行ったイベントを取得する場合は、ユーザー単位に加え、日でグループ化することにより取得できます。
- 「PARTITION BY user_id, DATE(event_time)」
- その日一番最後にアクセスしたユーザーを取得する場合は、日でグループ化して、時系列の最後のレコードを取得します。
- 「PARTITION BY DATE(event_time)」
- 「ORDER BY event_time DESC」
