前後の行を参照する - LAG / LEAD / FIRST_VALUE / LAST_VALUE

前後の行の値を参照するウィンドウ関数。LAG で差分、時系列分析の定番

概念図

LAG / LEAD / FIRST_VALUE / LAST_VALUE diagram

構文

sql

LAG(col, offset, default) OVER ( [PARTITION BY ...] ORDER BY ... )

サンプル

ユーザーごとに前回の注文金額と差分を計算する

sql

SELECT
  user_id,
  order_date,
  amount,
  LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS prev_amount,
  amount - LAG(amount) OVER (PARTITION BY user_id ORDER BY order_date) AS diff_from_prev
FROM orders;

LAG / LEAD の基本

LAG(col, offset, default) は現在行から offset 行前 の値を返します。LEAD は逆に offset 行後。offset と default は省略可で、デフォルトは offset=1, default=NULL です。

前月比・前日比・セッション間隔・入退室ペアリングなど、時系列分析ではほぼ必ず登場します。

LAG で差分を計算する

「前の行との差」を得る定番は col - LAG(col) OVER (...)。最初の行は LAG が NULL を返すので差も NULL になります。0 埋めしたければ第 3 引数にデフォルト値を渡します。

sql

-- センサーの測定値の前回差分 (最初の行は 0 扱い)
SELECT
  sensor_id,
  ts,
  value,
  value - LAG(value, 1, value) OVER (PARTITION BY sensor_id ORDER BY ts) AS delta
FROM measurements;

FIRST_VALUE / LAST_VALUE

FIRST_VALUE(col) と LAST_VALUE(col) はウィンドウ内の最初 / 最後の値を返します。「各ユーザーの初回注文金額を全行に並べる」など、グループ内の基準値を全行に配りたいときに便利です。

LAST_VALUE のフレーム罠

LAST_VALUE を ORDER BY ありのウィンドウでそのまま使うと、デフォルトフレームが UNBOUNDED PRECEDING AND CURRENT ROW なので、LAST は「現在行」になってしまいます。ほぼ必ず期待した結果になりません。

正しくは、フレームを明示して ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING に広げます。

sql

-- 期待通りの「そのユーザーの最新注文金額」
SELECT
  user_id, order_date, amount,
  LAST_VALUE(amount) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS latest_amount
FROM orders;

RDBMS 対応状況

PostgreSQL / SQL Server / Oracle / MySQL 8.0+: LAG / LEAD / FIRST_VALUE / LAST_VALUE すべて対応
SQLite 3.25+: 対応
IGNORE NULLS オプション: Oracle / SQL Server / PostgreSQL 16+ で対応。MySQL は未対応