視窗函式

在 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 與 RANK 與 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 and Lead Input

輸出

Lag and Lead Output

累計與移動平均

ROWS 與 RANGE 視窗框架

ROWS BETWEEN：以實體列位置計數——固定位移，與數值無關

-- ROWS：一律精確挑選前面 N 列實體列
SUM(credits) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
-- 含當前列與前兩列

RANGE BETWEEN：依數值，將ORDER BY 數值相同的列視為一組

-- RANGE：邊界數值相同的列都會被包含
SUM(credits) OVER (ORDER BY month RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

只有在邏輯以數值邊界為主時，才使用 Range

一起來練習吧！

在 Snowflake 進行資料管線自動化