順位・前後の行 / Snowflake
LAG
前の行の値を取る
名前の英語・略語の意味
lag = 遅れる・後れを取る
何に使う？
前後の値や期間内の最初・最後の値と比較する。
戻り値入力に応じた型
書き方
LAG(value[, offset[, fallback]]) OVER ([PARTITION BY partition] ORDER BY order)
引数に何を入れる？
| 引数 | 型 / 指定方法 | 渡す値の例 | 役割・省略時の動き |
|---|
value必須 | EXPRESSION | value / 42 / NULL | 処理する値や列。必要な型に揃えて渡す。 |
offset省略可 | INTEGER | 1 / 0 / -1 | 正数は前の行。+1は前の行、−1は次の行。0は現在行。省略は1。OVER内のORDER BYで並べた順が基準。 |
fallback省略可 | EXPRESSION | -1 / NULL | 指定した位置の行が同じ区分内に存在しない場合に返す値。省略はNULL。行が存在して値がNULLの場合は、NULLをそのまま返す。 |
partition省略可 | EXPRESSION | department / user_id | グループを分ける列。PARTITION BYに指定する。省略すると全行を1グループとして扱う。 |
order必須 | ORDER BY EXPRESSION | value DESC / id | 並び順を決める列・式。OVERや集約の句に指定する。 |
そのまま試せるSQL
入力値をSQLに含めています。テーブルの準備は不要です。
WITH data AS (
SELECT 1 AS id, 10 AS value
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, 30
)
SELECT id, value,
LAG(value, 1, -1) OVER (ORDER BY id) AS result
FROM data
ORDER BY id;
このSQLの結果
| id | value | result |
|---|
1 | 10 | -1 |
2 | NULL | 10 |
3 | 30 | NULL |
使う前に知っておきたいこと
前後はOVER内のORDER BYの順番で決まる。日付が昇順なら次の行は新しい日付、降順なら古い日付。同値の行は一意なIDも並び順に加える。PARTITION BYの区分を越えて参照しない。値がNULLの行も数える。 Snowflakeでは負のoffsetで向きが反転する。既定のRESPECT NULLSはNULLの行も数える。IGNORE NULLSを指定すると、値がNULLの行を飛ばして数える。fallbackは参照先の行がない場合だけ使う。
もう一方のSQLでは BigQuery
LAG(value[, offset[, fallback]]) OVER ([PARTITION BY partition] ORDER BY order)
BigQueryのoffsetは0以上の整数リテラルかパラメータ。負数・NULLはエラー。fallbackは参照先の行がない場合だけ使い、valueの型に変換できる定数式を指定する。
公式リファレンス ↗