ウィンドウ関数

Snowflakeにおけるデータパイプラインの自動化

Emily Melhuish

Technical Curriculum Developer, Snowflake

ウィンドウ関数とは？

行を集約せずに、行をまたいだ計算を実行する

周囲の行セット（ウィンドウ）を使用して各行の結果を算出
GROUP BY と異なり、入力行数と出力行数が一致する
OVER 句で定義する

主な3カテゴリ:

ランキング: ROW_NUMBER(), RANK(), DENSE_RANK()
集計: SUM(), AVG(), COUNT() with OVER()
ナビゲーション: LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

詳細は DataCamp の Window Functions in Snowflake コースを参照

OVER() 句

SELECT shipment_id
, region
, delivery_days
, AVG(delivery_days) 
    OVER ( PARTITION BY region ORDER BY dispatched_at ) AS avg_days_in_region 
FROM shipments;

SHIPMENT_ID	REGION	DELIVERY_DAYS	RUNNING_AVG_DAYS_IN_REGION
SHP-001	EMEA	3	3.00
SHP-002	EMEA	5	4.00
SHP-003	EMEA	4	4.00
SHP-004	APAC	6	6.00

ROW_NUMBER vs RANK vs DENSE_RANK

SELECT
    shipment_id AS shipment, delivery_days AS days,
    ROW_NUMBER() OVER (ORDER BY delivery_days) AS row_number,
    RANK()       OVER (ORDER BY delivery_days) AS rank,
    DENSE_RANK() OVER (ORDER BY delivery_days) AS dense_rank
FROM shipments ORDER BY delivery_days;

SHIPMENT	DELIVERY_DAYS	ROW_NUMBER	RANK	DENSE_RANK
SHP-001	3	1	1	1
SHP-002	5	2	2	2
SHP-003	5	3	2	2
SHP-004	7	4	4	3

LAG と LEAD

入力

SQL の LAG と LEAD の入力

出力

LAG と LEAD の出力

累計と移動平均

ROWS フレームと RANGE フレーム

ROWS BETWEEN: 物理的な行位置を基準にカウント — 値に関係なく正確なオフセット

-- ROWS: always picks exactly N preceding physical rows
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- Includes current row and two preceding

RANGE BETWEEN: 値を基準に、ORDER BY の値が同じ行をまとめて処理

-- RANGE: includes all rows tied at the boundary value
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

値の境界に基づくロジックが必要な場合にのみ RANGE を使用すること

練習しましょう！

Snowflakeにおけるデータパイプラインの自動化