如何用窗口函数 FIRST_VALUE / LAST_VALUE 取分组首尾值

根本原因是FIRST_VALUE和LAST_VALUE默认窗口帧为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，导致LAST_VALUE仅取当前行及之前行的末值而非全分组末值；需显式指定UNBOUNDED FOLLOwinG并确保ORDER BY唯一确定、妥善处理NULL。

为什么 FIRST_VALUE 和 LAST_VALUE 有时取不到预期的首尾值？

根本原因是这两个函数默认的窗口帧（frame）是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，也就是说，LAST_VALUE 并不是看整个分组的最后一条，而是看“当前行及之前所有行”里的最后一个——这通常不是你想要的。而 FIRST_VALUE 虽然在此帧下总能取到分组第一行（因起始是 UNBOUNDED PRECEDING），但它的行为也依赖于 ORDER BY 是否明确、是否唯一。

常见错误现象：LAST_VALUE(col) 返回的总是当前行的值，或某几行重复出现同一个“末尾值”，分组内其他行没更新。

• 必须显式指定窗口帧为 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
• ORDER BY 子句不可省略——即使业务上不关心顺序，也得选一个确定性排序列（如主键、时间戳）
• 若 ORDER BY 列存在重复值，且未加额外去重手段（如 ORDER BY ts, id），FIRST_VALUE/LAST_VALUE 的结果可能非确定

postgresql / mysql 8.0+ / SQL Server 中正确写法示例

以按用户分组、取每个用户最早和最晚订单金额为例：

SELECT   user_id,   order_time,   amount,   FIRST_VALUE(amount) OVER (     PARTITION BY user_id      ORDER BY order_time, order_id      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   ) AS first_amount,   LAST_VALUE(amount) OVER (     PARTITION BY user_id      ORDER BY order_time, order_id      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   ) AS last_amount FROM orders;

注意点：

• ORDER BY order_time, order_id 确保排序唯一，避免并列导致 FIRST_VALUE 随机选中某一行
• MySQL 8.0+ 和 PostgreSQL 支持该完整语法；SQL Server 同样支持，但旧版本（如 2012）的 LAST_VALUE 帧必须显式声明，否则行为异常
• sqlite 目前（3.45）仍不支持自定义帧，LAST_VALUE 无法可靠使用

替代方案：用 MIN/MAX 还是 FIRST_VALUE/LAST_VALUE？

如果只是取分组内某个字段的极值（比如最大金额、最小时间），直接用 MIN()/MAX() 更简洁安全；但如果你需要的是「对应那条记录的其他字段」，就必须用窗口函数。

例如：要取每个用户「最早订单的订单号 + 金额 + 商品名」，就不能只靠 MIN(order_time)，因为没法把商品名一起捞出来——这时得靠 FIRST_VALUE(order_id)、FIRST_VALUE(amount)、FIRST_VALUE(product_name) 同步拉取。

• MIN/MAX 是聚合函数，丢失行上下文；FIRST_VALUE/LAST_VALUE 是窗口函数，保留原始行结构
• 性能上，带完整帧的 LAST_VALUE 在大数据集可能比 MAX 略慢，因需维护完整窗口状态
• 某些引擎（如 BigQuery）对 LAST_VALUE 的帧优化较好，但 hive/spark SQL 旧版本可能有 bug，建议测试验证

容易被忽略的 NULL 处理与排序方向

FIRST_VALUE 和 LAST_VALUE 默认把 NULL 当作最小值（即 ORDER BY col ASC 时 NULL 排最前）。如果你的排序列含 NULL，且希望它们被排除在首尾之外，得提前过滤或用 CASE 调整顺序。

• 安全做法：在 ORDER BY 中写 ORDER BY col IS NULL, col 把 NULL 排最后
• 或者用 COALESCE(col, '9999-12-31')（日期场景）临时替换，但要注意类型兼容性
• 反向取“最后非 NULL 值”时，别只改 DESC，还要配合 NULLS LAST（PostgreSQL/oracle 支持），否则 LAST_VALUE 可能真就取到 NULL

真正麻烦的从来不是语法怎么写，而是排序依据是否稳定、NULL 怎么归置、以及不同数据库对 ROWS BETWEEN 的实际实现差异——这些地方一错，首尾值就静悄悄地偏了。