为何Max()与Min()窗口函数在SQL中的表现存在差异？

测试SQL语句 #

Select 
max(My_Date) over (order by My_Date) as Max_Datum 
, min(My_Date) over (order by My_Date) as Min_Datum 
From 
    (
    Select dateadd(m,-1,getdate()) as My_Date
    Union all 
    Select getdate() as My_Date
    Union all 
    Select dateadd(m,1,getdate()) as My_Date
    Union all 
    Select dateadd(m,2,getdate()) as My_Date
) t1

执行结果 #

Max_Datum               Min_Datum
2024-08-02 16:20:39.733 2024-08-02 16:20:39.733
2024-09-02 16:20:39.733 2024-08-02 16:20:39.733
2024-10-02 16:20:39.733 2024-08-02 16:20:39.733
2024-11-02 16:20:39.733 2024-08-02 16:20:39.733

原因解析 #

差异的核心在于窗口函数的默认框架范围：当你在OVER(ORDER BY ...)中指定排序但未显式定义窗口框架时，SQL Server会默认使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的框架——即窗口范围从数据集的第一行延伸到当前行。

• 对于MAX(My_Date)：由于数据是按My_Date升序排列，当前行的日期是窗口范围内的最大值，因此每一行的Max_Datum会随当前行递增。
• 对于MIN(My_Date)：窗口范围内的最小值始终是数据集的第一行日期（最旧的日期），因此所有行的Min_Datum都保持一致。

如何让两者行为一致（返回全局极值） #

如果需要让MAX()和MIN()都返回整个数据集的全局极值，可以显式指定窗口框架为整个数据集：

Select 
max(My_Date) over (order by My_Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Max_Datum 
, min(My_Date) over (order by My_Date RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as Min_Datum 
From 
    (
    Select dateadd(m,-1,getdate()) as My_Date
    Union all 
    Select getdate() as My_Date
    Union all 
    Select dateadd(m,1,getdate()) as My_Date
    Union all 
    Select dateadd(m,2,getdate()) as My_Date
) t1

此时所有行的Max_Datum都会返回数据集的最大日期，Min_Datum返回最小日期。

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