窗口函数

Snowflake 中的数据管道自动化

Emily Melhuish

Technical Curriculum Developer, Snowflake

什么是窗口函数？

在不折叠行的情况下跨行计算

以周围的一组（其“窗口”）为基础，为每行计算结果
不同于 GROUP BY：每行输入，每行输出
由 OVER 子句定义

三大类：

排名：ROW_NUMBER(), RANK(), DENSE_RANK()
聚合：SUM(), AVG(), COUNT() with OVER()
导航：LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

更多内容见 DataCamp 的“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：总是精确取前 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 中的数据管道自动化