COUNT窗口函数带与不带ORDER BY的结果差异及原理确认

测试数据 #

declare @t table ([period] date, [contract] int, price int);
insert into @t values
('2022-11-01', 1, 234),
('2022-12-01', 1, 277),
('2023-01-01', 1, 489),
('2022-11-01', 2, 187),
('2022-12-01', 2, 598),
('2022-03-01', 2, 478);

结论：你的理解完全正确 #

窗口函数的默认统计范围确实会因是否指定ORDER BY产生差异：

• 未指定ORDER BY时：窗口范围默认是整个分区，所以COUNT(*) OVER(PARTITION BY contract)会统计每个分区（单条contract对应的所有记录）的总行数，结果全为3，符合你的预期。
• 指定ORDER BY时：窗口范围默认是从分区起始行到当前行（等效于显式写ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW），此时COUNT(*) OVER(PARTITION BY contract ORDER BY period)会逐行累加统计，得到1、2、3的递增结果。

实际验证示例 #

不带ORDER BY的查询

SELECT 
    period,
    contract,
    price,
    COUNT(*) OVER(PARTITION BY contract) AS total_count
FROM @t;

结果中每个contract对应的total_count均为3，对应整个分区的总行数。

带ORDER BY的查询

SELECT 
    period,
    contract,
    price,
    COUNT(*) OVER(PARTITION BY contract ORDER BY period) AS running_count
FROM @t;

结果中每个contract的running_count会从1开始累加，最终到3，对应从分区开头到当前行的累计行数。

如果想在指定ORDER BY的同时仍统计整个分区的行数，可显式指定窗口范围：

SELECT 
    period,
    contract,
    price,
    COUNT(*) OVER(PARTITION BY contract ORDER BY period ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS total_count
FROM @t;

此时即使加了ORDER BY，结果也会全为3。

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