如何用Spark SQL窗口函数获取分组内的首尾变化差异-SQL-PHP中文网

first_value和last_value必须配order by并显式指定rows范围，否则行为不可靠；last_value默认只读当前行及之前，取组尾需unbounded following；null值需用ignorenulls或count校验。

如何用spark sql窗口函数获取分组内的首尾变化差异

窗口函数中用 `first_value` 和 `last_value` 必须配 `ORDER BY`

不写 ORDER BY 的窗口定义下，first_value 和 last_value 行为不可靠——Spark 默认采用“无序行集”，此时两者都可能返回任意一行的值，不是你想要的“组内首/尾”。必须显式指定排序依据，比如时间戳、序列号等单调字段。

常见错误现象：last_value(col) OVER (PARTITION BY user_id) 返回结果和 first_value 一样，甚至每行都相同。

正确写法示例：first_value(amount) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
last_value 默认只看到当前行及之前（即 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），要取组内最后一行，得改成 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
如果排序字段有重复值（如多条记录同秒级时间），建议加二级排序（如 ORDER BY event_time, log_id）避免非确定性

计算首尾差值时优先用 `first_value` + `last_value`，别用 `collect_list`

有人想先聚合出整个数组再取首尾，比如 collect_list(amount) OVER (PARTITION BY user_id)，这在数据量大时极易 OOM 或拖慢执行——collect_list 强制把整组数据拉到一个 task 内存里。

而 first_value/last_value 是流式计算，内存占用恒定，性能稳定。

安全写法：last_value(amount) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - first_value(amount) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
注意：两个窗口定义必须完全一致（包括 PARTITION BY、ORDER BY、ROWS），否则结果错位
若只需差值，可封装为子查询或 CTE 避免重复写窗口定义

`last_value` 在 Spark 3.0+ 默认行为变更需显式声明 `ROWS`

Spark 3.0 起，last_value 的默认窗口帧从 UNBOUNDED PRECEDING TO UNBOUNDED FOLLOWING 改为 UNBOUNDED PRECEDING TO CURRENT ROW，和 first_value 对齐。这意味着不写 ROWS 时，last_value 实际只看“当前行及之前”，不是整组末尾。

这个改动让新手容易误以为它能直接取组尾——其实不能。

兼容写法（推荐）：last_value(amount) OVER (PARTITION BY user_id ORDER BY event_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Spark 2.x 用户升级后若发现 last_value 结果变短了，大概率是这个原因
别依赖隐式默认帧，所有 last_value 都应显式声明 ROWS

处理空值与边界情况：用 `IGNORE NULLS` 和 `COUNT` 校验

当分组内存在 NULL 的目标字段（如 amount），first_value/last_value 默认跳过它们，但若全为 NULL，结果也是 NULL。这时候差值会变成 NULL，而非 0。

可控写法：first_value(amount, true) OVER (...)（Spark 3.0+ 支持第二个参数 ignoreNulls = true，显式启用跳过逻辑）
更稳妥的做法：先用 COUNT(amount) OVER (PARTITION BY user_id) 判断该组是否有有效值，再决定是否参与差值计算
若业务要求“全 NULL 组差值为 0”，需配合 COALESCE(diff, 0) 或 IF(COUNT(...) > 0, diff, 0)

实际跑通的关键往往不在函数名，而在窗口帧范围和 NULL 处理的细节上；漏掉 ROWS BETWEEN... 或忽略排序稳定性，结果就悄悄偏了。