PostgreSQLでユーザごとに指定時刻に最も近い1件を取る

  • PostgreSQL
  • SQL

この記事は、構成と文章の整理にAIを使っています。
あくまで読みやすくするための補助で、内容そのものをAIにすべて任せて書いているわけではありませんが、ところどころ相談しながら整えています。

ユーザごとに、指定した時刻に一番近いレコードを1件だけ取りたい場面があった。位置情報とかセンサーの計測値みたいに、ユーザ単位で時系列データが並んでるテーブルが対象。

クエリ

WITH ranked_records AS (
  SELECT
    user_id,
    location,
    datetime,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY ABS(EXTRACT(EPOCH FROM (datetime - '2023-01-01 12:00:00'::timestamp)))
    ) AS rank
  FROM
    example_table
)
SELECT
  user_id,
  location,
  datetime
FROM
  ranked_records
WHERE
  rank = 1;

example_table にはユーザごとに複数の datetime 付きレコードが入ってる想定。

仕組み

  • datetime - '2023-01-01 12:00:00'::timestamp で interval が出る
  • EXTRACT(EPOCH FROM interval) でそれを秒数（double precision）に変換する
  • ABS() で符号を消して「差の絶対値」にする
  • ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY 差の絶対値) で、ユーザごとに「指定時刻に近い順」の連番を振る
  • 外側の WHERE rank = 1 で、各ユーザの最も近い1件だけ残す

PARTITION BY user_id がないと全体で1位のレコードしか残らないので、ユーザ単位で欲しい場合はここが要点になる。

注意点

timestamp型とtimestamptz型

datetime 列が timestamp without time zone ならこのままでいい。timestamptz の場合、'2023-01-01 12:00:00'::timestamp は「タイムゾーンなしのtimestamp」にキャストされる形になるので、暗黙のタイムゾーン変換が絡んで意図したズレと違う結果になることがある。列の型に合わせて ::timestamptz を使うか、比較する時刻側の型を明示した方が安全。

tie（同着）の挙動

2件のレコードが指定時刻からまったく同じ距離だった場合、ROW_NUMBER() はどちらが1位になるか保証されない。厳密に決めたいケースなら、ORDER BY 差の絶対値, id のように第2キーを足しておく。実際に同着が起きうるデータかどうかは要件次第。

パフォーマンス

このクエリは基本的に全行スキャンしてから並べ替える形になる。ユーザ数・行数が多いと重くなりやすい。(user_id, datetime) にインデックスがある場合、LATERAL + ORDER BY ABS(datetime - $1) LIMIT 1 の形にするとインデックスを活かしやすいケースがあるらしい。実機での比較は未確認。

EXTRACT(EPOCH FROM …)を挟まない書き方

interval 同士は大小比較できるので、理屈の上では ORDER BY ABS(datetime - timestamp '2023-01-01 12:00:00') でも動く可能性はある。ただし interval に対する ABS() の挙動を実機で確認していないので、ここは断定しない。元の EXTRACT(EPOCH FROM ...) 経由の書き方の方が確実。