FIRST_VALUE和LAST_VALUE必须配合OVER子句使用,否则报错或返回NULL;默认窗口帧为ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,导致LAST_VALUE仅取当前行值,须显式指定UNBOUNDED FOLLOWING并配确定性ORDER BY才能取分组首尾。

FIRST_VALUE和LAST_VALUE必须配合窗口定义使用
直接写 FIRST_VALUE(price) 或 LAST_VALUE(price) 会返回 NULL(PostgreSQL/SQL Server)或报错(MySQL 8.0+),因为这两个函数不接受静态表达式,只在窗口上下文中有效。你必须显式声明 OVER 子句,并明确指定排序依据——价格波幅依赖时间顺序,所以 ORDER BY date 是刚需。
常见错误是漏掉 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING,尤其用 LAST_VALUE 时:默认窗口是 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,导致 LAST_VALUE 总等于当前行值,完全失去“整个分区末尾”的语义。
- 正确写法必须包含完整窗口帧:
OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) - 若按产品分组计算波幅,需加
PARTITION BY product_id,否则全表只算一个波幅 - 日期字段必须可比较且无重复;若有并列日期,需额外加
id或timestamp做二级排序,避免结果不确定
计算波幅要先取极值再相减,不能直接套函数
FIRST_VALUE 和 LAST_VALUE 返回的是首尾行的原始值,不是最大最小值。价格波幅通常指「最高价 − 最低价」,但有人也指「期末价 − 期初价」。二者完全不同:
- 若你要「趋势性波幅」(即涨跌幅度):用
LAST_VALUE(price) - FIRST_VALUE(price) - 若你要「振幅」(即波动范围):该用
MAX(price) - MIN(price),而不是FIRST_VALUE/LAST_VALUE - 混淆这两者会导致业务逻辑错误——比如某股票先跌后涨,
FIRST_VALUE是高点、LAST_VALUE是高点,差值为正,但实际中间跌得更深
示例(按日期升序,计算每只产品的期末相对期初变化):
SELECT
product_id,
date,
price,
LAST_VALUE(price) OVER (
PARTITION BY product_id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) - FIRST_VALUE(price) OVER (
PARTITION BY product_id
ORDER BY date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS price_change
FROM sales;
不同数据库对LAST_VALUE的默认行为差异极大
MySQL 8.0+ 和 PostgreSQL 默认窗口帧相同,但 SQL Server 在 ORDER BY 存在时隐式采用 CURRENT ROW 结尾,导致 LAST_VALUE 失效;Oracle 则要求显式写 RANGE 或 ROWS 才能用 LAST_VALUE。
- PostgreSQL:不写帧定义 →
CURRENT ROW结尾 →LAST_VALUE永远 = 当前行 - SQL Server:同上,且部分旧版本不支持
UNBOUNDED FOLLOWING在ROWS中,得换用RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING - BigQuery:支持简写,但
LAST_VALUE需配IGNORE NULLS才安全(如果价格字段可能为空)
跨库迁移时,最稳妥的做法是始终显式写出完整帧定义,并在测试数据中验证首尾行是否真被命中。
性能隐患:窗口帧设为UNBOUNDED FOLLOWING会阻止流式计算
当数据量大且按时间滚动(比如实时价格流),ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 要求引擎缓存整个分区所有行才能输出第一行结果——无法流式处理,内存压力陡增。
- 替代方案:如只需最近 N 天波幅,改用
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW+ 子查询取全局MAX/MIN,或预聚合到日粒度表 - 索引建议:确保
(product_id, date)有联合索引,否则窗口排序成本极高 - 注意 NULL:若首行或末行
price为 NULL,FIRST_VALUE/LAST_VALUE默认返回 NULL,需加IGNORE NULLS(PostgreSQL 14+/Oracle/BigQuery 支持,MySQL 不支持)
真正难的不是写对语法,而是想清楚「波幅」在你业务里到底指什么,以及你的数据更新模式能否支撑所选窗口定义。










