spark 窗口函数速查表

函数() OVER(
    PARTITION BY col       -- 分区（可选）
    ORDER BY col           -- 排序（部分函数必需）
    ROWS|RANGE BETWEEN ... -- 帧（仅聚合/首尾类有效）
)

-- 每部门薪资 Top 3（允许并列）
SELECT * FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) as rk
    FROM employee
) t WHERE rk <= 3;

-- 环比计算
SELECT
    month, amount,
    amount - LAG(amount, 1) OVER(ORDER BY month) as mom_change
FROM sales;

-- 计算相邻事件间隔
SELECT
    user_id, event_time,
    DATEDIFF(event_time, LAG(event_time) OVER(PARTITION BY user_id ORDER BY event_time)) as gap_days
FROM user_log;

-- 累计求和（默认帧）
SUM(amount) OVER(PARTITION BY user_id ORDER BY month)

-- 近3行移动平均
AVG(amount) OVER(PARTITION BY user_id ORDER BY month
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- 全分区求和（不加 ORDER BY 则帧为整个分区）
SUM(amount) OVER(PARTITION BY dept)

-- LAST_VALUE 陷阱：默认帧是 UNBOUNDED PRECEDING TO CURRENT ROW
-- 必须显式指定帧才能拿到分区最后一行
LAST_VALUE(amount) OVER(PARTITION BY name ORDER BY month
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

-- 按销售额分4档
NTILE(4) OVER(ORDER BY amount DESC) as quartile

-- 百分位排名
PERCENT_RANK() OVER(PARTITION BY dept ORDER BY salary) as pct

{ROWS | RANGE} BETWEEN <起点> AND <终点>

分区第一行

-- 示例：ORDER BY amount，数据为 900, 900, 1100
-- ROWS: 第1行累计=900, 第2行累计=1800
-- RANGE: 第1行累计=1800, 第2行累计=1800（两个900一起纳入）

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

ROW_NUMBER() OVER(PARTITION BY ... ORDER BY ... DESC)
 + WHERE rk <= N