为什么带PARTITION BY和ORDER BY的窗口COUNT对同sal值返回相同累计数

原因解释 #

这是SQL窗口函数默认窗口帧规则和RANGE计算模式的特性导致的：

• 当窗口子句同时声明PARTITION BY和ORDER BY，且没有显式指定窗口帧范围时，SQL标准默认的帧规则是：

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

• RANGE模式下，所有ORDER BY排序键值相同的行会被归为同一个对等组（Peer Group），计算聚合时会把整个对等组的所有行都纳入当前计算范围，而非仅统计到当前单条行为止。

你给出的20部门数据计算逻辑如下：
20分区按sal排序后共3组对等组：[sal=100(1行), sal=109(3行), sal=110(2行)]

• 计算sal=109组内任意一行的COUNT时，帧范围包含从分区开头到sal=109组最后一行的所有行，总数量为1+3=4，因此3行sal=109的记录count值都是4
• 同理sal=110组的帧范围包含到该组最后一行，总数量为1+3+2=6，所以两行count都为6

逐行累加累计计数的实现方案 #

如果需要每行按顺序累加1的计数效果，有两种修改方式：

• 显式指定ROWS模式，按物理行统计：

COUNT(sal) OVER (
  PARTITION BY deptno ORDER BY sal
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS COUNT

• 在ORDER BY后追加唯一列（比如empno），让每个行的排序键唯一，不存在对等组：

COUNT(sal) OVER (
  PARTITION BY deptno ORDER BY sal, empno
) AS COUNT

修改后sal=109的三行count值会分别显示为2、3、4。

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