SQL如何获取分组后第一行和最后一行的差值_利用FIRST_VALUE和LAST

first_value和last_value不是聚合函数而是窗口函数，必须配合over()及显式窗口帧（如rows between unbounded preceding and unbounded following）使用，否则默认帧仅覆盖当前行及之前，导致结果非全组首尾值。

sql如何获取分组后第一行和最后一行的差值_利用first_value和last_value

为什么FIRST_VALUE和LAST_VALUE返回的不是分组内首尾值？

直接在GROUP BY后套用FIRST_VALUE()或LAST_VALUE()会报错，或者返回意外结果——因为这两个是窗口函数，不能和聚合语句混用。它们必须配合OVER()子句，在未分组的数据集上定义窗口范围，而不是在GROUP BY后的结果集上运行。

正确写法：先开窗排序，再用ROWS BETWEEN限制窗口边界

FIRST_VALUE()和LAST_VALUE()默认窗口是UNBOUNDED PRECEDING TO CURRENT ROW，所以LAST_VALUE()往往只看到当前行及之前，而非整个分组末尾。必须显式指定窗口帧：

对每个分组（如user_id），按时间/序号升序排序：ORDER BY created_at
用ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING把整组纳入窗口
这样FIRST_VALUE(val)才取该组排序后第一行的val，LAST_VALUE(val)才取最后一行的val

SELECT
  user_id,
  FIRST_VALUE(score) OVER (
    PARTITION BY user_id 
    ORDER BY created_at 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_score,
  LAST_VALUE(score) OVER (
    PARTITION BY user_id 
    ORDER BY created_at 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_score,
  LAST_VALUE(score) OVER (...) - FIRST_VALUE(score) OVER (...) AS diff
FROM user_scores;

常见陷阱：NULL出现在LAST_VALUE结果里

即使窗口帧设对了，PostgreSQL 和某些 MySQL 版本仍可能让LAST_VALUE()在最后一行之外返回NULL——这是因默认的RANGE语义导致重复排序键时行为不确定。解决方法只有两个：

RHTV

RHTV是一款AI文本写作工具，RunningHub 推出的原生 AI 无限画布创作工具。

下载

确保ORDER BY字段组合能唯一标识每行（例如加id作为次级排序：ORDER BY created_at, id）
改用ROW_NUMBER() + 自连接或子查询，虽然啰嗦但100%可控

另外注意：SQL Server 对LAST_VALUE()要求必须写完整窗口帧，缺了ROWS BETWEEN ...直接报错；而 BigQuery 允许省略但语义不同，务必查对应文档。

替代方案：用子查询+聚合更直观可靠

如果只是要首尾差值，不强求单条SQL，用聚合函数往往更稳：

MIN()/MAX()适用于“最小/最大值”，但不是“第一/最后一行”的值
真正按顺序取首尾，可用ARRAY_AGG(val ORDER BY ts)[OFFSET(0)]（BigQuery）或STRING_AGG(... ORDER BY ...)[1]（PostgreSQL 9.6+）
最通用的是两次关联：(SELECT val FROM t t2 WHERE t2.group_id = t1.group_id ORDER BY ts LIMIT 1) 和同理LIMIT 1 OFFSET x找末行

窗口函数写起来短，但调试成本高；聚合+子查询多几行，却容易验证每一步是否取到预期行——尤其当排序字段有空值、时区混用或存在逻辑删除标记时，后者反而省时间。