如何在SQL中用FIRST_VALUE()和LAST_VALUE()计算区间首尾差？-SQL-PHP中文网

FIRST_VALUE()和LAST_VALUE()需使用相同窗口帧（如ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING）并配合ORDER BY和PARTITION BY，否则LAST_VALUE()默认仅作用于当前行导致差值为0；注意数据库兼容性及重复排序字段的处理。

如何在sql中用first_value()和last_value()计算区间首尾差？

FIRST_VALUE() 和 LAST_VALUE() 的窗口方向必须一致

这两个函数默认只看当前行及之前的行（ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），所以 LAST_VALUE() 在默认行为下往往返回当前行值，而非整个窗口的末尾值。想算“区间首尾差”，得先让两个函数看到同一组完整数据。

常见错误现象：LAST_VALUE(price) 返回每行自己的 price，差值全为 0。

显式指定窗口帧：用 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
确保 ORDER BY 子句存在且逻辑合理（比如按时间排序）
若分组计算（如每只股票每日），需配合 PARTITION BY，否则整个结果集被当做一个窗口

计算首尾差的典型写法（以股价日序列为例子）

假设表 stock_prices 有 symbol、trade_date、close_price，想算每只股票在指定日期范围内首日与末日收盘价之差：

SELECT
  symbol,
  trade_date,
  close_price,
  FIRST_VALUE(close_price) OVER (
    PARTITION BY symbol 
    ORDER BY trade_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_close,
  LAST_VALUE(close_price) OVER (
    PARTITION BY symbol 
    ORDER BY trade_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_close,
  LAST_VALUE(close_price) OVER (
    PARTITION BY symbol 
    ORDER BY trade_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) - FIRST_VALUE(close_price) OVER (
    PARTITION BY symbol 
    ORDER BY trade_date 
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS diff
FROM stock_prices
WHERE trade_date BETWEEN '2024-01-01' AND '2024-12-31';

注意：PostgreSQL 和 SQL Server 支持该写法；MySQL 8.0+ 也支持，但旧版本不支持窗口帧语法。

LAST_VALUE() 在 Oracle 和某些 MySQL 版本中的兼容性陷阱

Oracle 默认 LAST_VALUE() 行为更严格，即使写了 UNBOUNDED FOLLOWING，也可能因空值或排序重复而返回意外结果；MySQL 5.7 及更早完全不支持窗口函数。

Oracle 建议加 RESPECT NULLS 显式声明（默认即此，但写上更安全）
MySQL 用户必须确认版本 ≥ 8.0.2，且开启窗口函数支持（默认已开）
如果排序字段有重复（如多笔同秒成交），应补充次级排序，例如 ORDER BY trade_date, id，否则 FIRST_VALUE/LAST_VALUE 的结果可能不确定

性能与可读性权衡：是否真需要两个函数？

用两次窗口函数计算首尾再相减，逻辑清晰但执行计划里会扫描两次窗口。对大数据量表，不如改用子查询聚合：

SELECT 
  t.*,
  (last_info.last_p - first_info.first_p) AS diff
FROM stock_prices t
JOIN (
  SELECT symbol, MIN(trade_date) AS min_dt, MAX(trade_date) AS max_dt
  FROM stock_prices 
  WHERE trade_date BETWEEN '2024-01-01' AND '2024-12-31'
  GROUP BY symbol
) date_range ON t.symbol = date_range.symbol
JOIN (
  SELECT symbol, trade_date AS min_dt, close_price AS first_p
  FROM stock_prices s1
  WHERE (symbol, trade_date) IN (
    SELECT symbol, MIN(trade_date) 
    FROM stock_prices 
    GROUP BY symbol
  )
) first_info ON t.symbol = first_info.symbol
JOIN (
  SELECT symbol, trade_date AS max_dt, close_price AS last_p
  FROM stock_prices s2
  WHERE (symbol, trade_date) IN (
    SELECT symbol, MAX(trade_date) 
    FROM stock_prices 
    GROUP BY symbol
  )
) last_info ON t.symbol = last_info.symbol;

这种写法更重，但避免了窗口帧开销；真正要选哪种，取决于数据量、更新频率和是否需保留原始行粒度。

最易被忽略的是：窗口函数里的 ORDER BY 不仅影响排序，还决定 UNBOUNDED FOLLOWING 的“末尾”落在哪一行——如果漏写或写错，首尾就不是你认为的那个首尾。