LAGやLEADばかり活用していましたが、それ以外にも便利な関数がたくさんあることに気づき、まとめてみました。
LAG / LEAD
使いどころ：
- 直前・直後の値を取りたいとき
- 時系列データの前後比較
SELECT
user_id,
event_date,
value,
LAG(value, 1) OVER(PARTITION BY user_id ORDER BY event_date) AS prev_value,
LEAD(value, 1) OVER(PARTITION BY user_id ORDER BY event_date) AS next_value
FROM
my_table
FIRST_VALUE / LAST_VALUE
使いどころ：
- セッション開始時の値を知りたい
- 最新の値を取得したい
SELECT
user_id,
event_date,
value,
FIRST_VALUE(value) OVER(PARTITION BY user_id ORDER BY event_date) AS first_val,
LAST_VALUE(value) OVER(PARTITION BY user_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_val
FROM
my_table
NTH_VALUE
使いどころ：
- 例えば「3番目の訪問日の金額が知りたい」ときに便利！
SELECT
user_id,
event_date,
NTH_VALUE(value, 3) OVER(PARTITION BY user_id ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS third_val
FROM
my_table
ROW_NUMBER / RANK / DENSE_RANK
使いどころ：
- 順位付けや、最新データの抽出などに大活躍！
SELECT
user_id,
value,
ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY event_date DESC) AS row_num,
RANK() OVER(PARTITION BY user_id ORDER BY value DESC) AS rank_val,
DENSE_RANK() OVER(PARTITION BY user_id ORDER BY value DESC) AS dense_rank_val
FROM
my_table
PERCENT_RANK / CUME_DIST
使いどころ：
- スコアの相対位置を知るのに便利。0〜1の範囲で順位感がわかる！
SELECT
user_id,
value,
PERCENT_RANK() OVER(PARTITION BY user_id ORDER BY value) AS percent_rank_val,
CUME_DIST() OVER(PARTITION BY user_id ORDER BY value) AS cume_dist_val
FROM
my_table
NTILE
使いどころ：
- 分位点（例：4等分でグループ分け）をサクッと計算！
SELECT
user_id,
value,
NTILE(4) OVER(PARTITION BY user_id ORDER BY value) AS quartile
FROM
my_table
ARRAY_AGG OVER
使いどころ：
- ウィンドウ内の値を配列でまとめられる！
SELECT
user_id,
event_date,
ARRAY_AGG(value) OVER(PARTITION BY user_id ORDER BY event_date) AS value_array
FROM
my_table
SUM / AVG / MIN / MAX OVER
使いどころ：
- 移動平均や累積和をサクッと出せる
SELECT
user_id,
event_date,
value,
SUM(value) OVER(PARTITION BY user_id ORDER BY event_date) AS cumulative_sum,
AVG(value) OVER(PARTITION BY user_id ORDER BY event_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM
my_table
まとめ
これらを使いこなすことで、SQLの表現力が一気に広がると思います。