排名與前後列 / Snowflake
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的分組欄位，省略時所有列為一組。 |
order必填 | ORDER BY EXPRESSION | value DESC / id | 排序欄位或運算式，放在OVER或彙總子句中，而非函數引數。 |
可直接執行的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排序。不跨分區，值為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須為非負整數常值或參數，負數或NULL會報錯。fallback僅在目標列不存在時使用，且須為可轉成value型別的常數運算式。
官方參考文件 ↗