窗口函数含Frame子句时ORDER BY的作用疑问：min/max聚合场景

问题描述 #

我需要获取每个分区内某列的MIN和MAX值，以下示例中两种写法均能得到正确结果，但我不理解为何必须添加ORDER BY子句。想了解在使用MIN、MAX作为聚合函数时，ORDER BY会产生何种差异？

示例SQL代码 #

DROP TABLE IF EXISTS #HELLO;
CREATE TABLE #HELLO (Category char(2), q int);
INSERT INTO #HELLO (Category, q)
VALUES ('A',1), ('A',5), ('A',6), ('B',0), ('B',3)

SELECT *, 
     min(q) OVER (PARTITION BY category ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS minvalue
    ,max(q) OVER (PARTITION BY category ORDER BY (SELECT NULL) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS maxvalue
    ,min(q) OVER (PARTITION BY category ORDER BY q ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS minvalue2
    ,max(q) OVER (PARTITION BY category ORDER BY q ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS maxvalue2
FROM #HELLO;

解答 #

1. 为什么必须加ORDER BY？ #

这是SQL Server的语法强制要求：当你在窗口函数中显式指定ROWS或RANGE子句（用来定义窗口的范围框架）时，必须同时搭配ORDER BY子句。因为窗口框架的边界是基于行的排序位置确定的，数据库需要明确行的顺序，才能判断框架包含哪些行。

2. 不同ORDER BY写法的差异 #

（1）ORDER BY (SELECT NULL)

• 这个写法纯粹是为了满足语法要求，实际不会对分区内的行做任何排序（SELECT NULL没有有效排序依据）。
• 结合ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，窗口框架覆盖整个分区的所有行，因此计算出的MIN/MAX就是整个分区的最值。

（2）ORDER BY q

• 分区内的行会按q列数值排序，但因为你指定的框架依然是整个分区（从开头到结尾），所以最终的MIN/MAX结果和第一种写法完全一致——整个分区的最值和行的排序顺序无关。
• 但如果修改框架范围（比如改成ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），ORDER BY的作用就会立刻体现：此时MIN/MAX会计算从分区第一行到当前行的范围内的最值，行的排序顺序直接决定了当前行对应的范围包含哪些数据，结果会随行的位置变化而不同。

3. 简化写法 #

如果你的需求只是获取整个分区的MIN/MAX，完全可以省略ORDER BY和ROWS子句，直接写：

SELECT *, 
     min(q) OVER (PARTITION BY category) AS minvalue
    ,max(q) OVER (PARTITION BY category) AS maxvalue
FROM #HELLO;

因为当窗口函数没有指定ORDER BY和ROWS/RANGE时，默认的窗口框架就是RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING（即整个分区），结果和你示例中的写法完全一致，代码更简洁。

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