如何使用Window Functions为数据表添加最高值列？

用户有如下数据表，用于复现数据的SQL语句如下：

DROP TABLE IF EXISTS units_table;
CREATE TEMP TABLE units_table
(
 Brand varchar(100),
 units numeric(38,12)
);

INSERT INTO units_table (Brand, units)
VALUES ('A',200),('B',0),('C',300),('D',400),('E',1500),('F',700),('G',800),('H',450);

用户希望通过窗口函数为表添加一列，显示全表的最高units值，但遇到两个问题：

• 使用无排序的FIRST_VALUE语句时，Highest列值为0
• 使用带ORDER BY的语句则报错

正确实现方法 #

方法1：使用MAX()窗口函数（最简洁推荐） #

直接利用MAX()窗口函数，无需排序即可计算全表范围内的最大值：

SELECT 
    Brand,
    units,
    MAX(units) OVER () AS Highest
FROM units_table;

执行后每一行的Highest列都会返回全表最高的units值（即1500）。

方法2：正确使用FIRST_VALUE()窗口函数 #

你之前用FIRST_VALUE出错的原因：

• 无排序时，窗口函数会按数据存储的默认顺序取第一个值，恰好你的数据中B的units为0排在前列，所以返回了0
• 若要让FIRST_VALUE取最大值，需按units降序排序，同时指定全局窗口范围，否则默认窗口仅包含从开头到当前行的数据，无法取到全局最大值。

正确的FIRST_VALUE写法：

SELECT 
    Brand,
    units,
    FIRST_VALUE(units) OVER (
        ORDER BY units DESC 
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS Highest
FROM units_table;

这种写法可以实现需求，但比MAX()方式繁琐，优先推荐方法1。

关于带ORDER BY语句报错的原因 #

你之前的报错大概率是因为未指定完整的窗口范围，仅写了OVER(ORDER BY units DESC)时，默认窗口范围是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，部分SQL方言对这种场景的语法要求严格，或者存在语法拼写错误。补充完整窗口范围后即可解决。

内容的提问来源于stack exchange，提问作者user2458552