T-SQL中MAX窗口函数未按预期工作的问题排查

原始查询与结果 #

执行的T-SQL查询： #

SELECT  
    j.inv_num,
    CAST(j.inserteddatetime AS DATE) AS [CostedDate],
    MIN(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num ORDER BY CAST(j.inserteddatetime AS DATE)) AS [FirstCostedDate],
    MAX(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num ORDER BY CAST(j.inserteddatetime AS DATE)) AS [LastCostedDate]
FROM 
    dbo.JobCost AS j WITH (NOLOCK)
WHERE 
    J.INV_NUM = '353197'

查询结果： #

inv_num CostedDate  FirstCostedDate LastCostedDate
--------------------------------------------------
353197  2023-12-13  2023-12-13  2023-12-13
353197  2023-12-21  2023-12-13  2023-12-21
353197  2023-12-21  2023-12-13  2023-12-21

错误原因 #

你在窗口函数的OVER子句中添加了ORDER BY CAST(j.inserteddatetime AS DATE)，这会让MIN()和MAX()变成累计聚合——聚合范围限定为当前分区内从第一行到当前行的所有数据，而非整个分区的全局数据。

因此第一行的LastCostedDate只能取到当前行的CostedDate（2023-12-13），只有当行数据包含2023-12-21时，累计聚合的最大值才会更新为该日期。

修正方案 #

移除OVER子句中的ORDER BY部分，让窗口函数对整个inv_num分区进行全局聚合，这样就能在每一行都显示该inv_num对应的全局最小和最大日期，同时保留所有CostedDate记录。

修正后的T-SQL查询： #

SELECT  
    j.inv_num,
    CAST(j.inserteddatetime AS DATE) AS [CostedDate],
    MIN(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num) AS [FirstCostedDate],
    MAX(CAST(j.inserteddatetime AS DATE)) OVER (PARTITION BY j.inv_num) AS [LastCostedDate]
FROM 
    dbo.JobCost AS j WITH (NOLOCK)
WHERE 
    J.INV_NUM = '353197'

预期结果： #

inv_num CostedDate  FirstCostedDate LastCostedDate
--------------------------------------------------
353197  2023-12-13  2023-12-13  2023-12-21
353197  2023-12-21  2023-12-13  2023-12-21
353197  2023-12-21  2023-12-13  2023-12-21

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