新しいことにはウェルカム

技術 | 電子工作 | ガジェット | ゲーム のメモ書き

SQLでグループの最後のレコードを取得する方法

「ユーザーのイベントログで、ユーザーが一番最後に行ったイベントを抽出する」など、 グループで分けたデータの最後のレコードを取得したい時があります。

ウィンドウ関数を使うと、下記のような形で求めることができます。

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()」で重複しない連番を振ります。

連番が1、つまり、グループ内で一番最初のレコードが、求めたいレコードになるようにグループ単位と並び順を設定して、連番の番号が1のものだけ抽出します。

上記例では、ユーザー単位で、時系列に降順で並べることにより、番号1のレコードが時系列で一番最後のレコードになるようにしています。

応用

  • ユーザーが一番最初に行ったレコードを取得する場合は、昇順に並べることにより取得できます。
    • 「ORDER BY event_time ASC」
  • その日アクセスしたユーザーが、一番最後に行ったイベントを取得する場合は、ユーザー単位に加え、日でグループ化することにより取得できます。
    • 「PARTITION BY user_id, DATE(event_time)」
  • その日一番最後にアクセスしたユーザーを取得する場合は、日でグループ化して、時系列の最後のレコードを取得します。
    • 「PARTITION BY DATE(event_time)」
    • 「ORDER BY event_time DESC」