如何解读PostgreSQL中ROWS模式的窗口函数帧范围定义语句

原查询语句 #

SELECT
    sum(unique1) OVER () AS total,
    sum(unique1) OVER 
    (PARTITION BY four ORDER BY unique1 RANGE BETWEEN 5::int8 PRECEDING AND 6::int2 FOLLOWING),
    unique1,
    four
FROM
    tenk1
WHERE
    unique1 < 10;

查询返回结果 #

total | sum | unique1 | four
-------+-----+---------+------
    45 |   4 |       0 |    0
    45 |  12 |       4 |    0
    45 |  12 |       8 |    0
    45 |   6 |       1 |    1
    45 |  15 |       5 |    1
    45 |  14 |       9 |    1
    45 |   8 |       2 |    2
    45 |   8 |       6 |    2
    45 |  10 |       3 |    3
    45 |  10 |       7 |    3
(10 rows)

子句各部分解读 #

把目标窗口子句拆分为三个核心部分逐一解释：

1. PARTITION BY four #

将过滤后（unique1 < 10）的数据按four字段的值分组，每个分组就是一个独立的窗口分区。比如结果中four=0的3行是一个分区，four=1的3行是另一个分区，所有窗口计算都只在各自分区内进行，不会跨分区。

2. ORDER BY unique1 #

对每个分区内的行，按unique1字段的数值从小到大排序。这是窗口帧范围计算的基础——不管是ROWS还是RANGE模式，都依赖这个排序后的行顺序来确定帧的边界。

3. ROWS BETWEEN 5::int8 PRECEDING AND 6::int2 FOLLOWING #

这是窗口帧的范围定义，核心是ROWS模式的特性：

• ROWS模式完全基于排序后的行的物理位置划定范围，和unique1的具体数值无关
• 5::int8 PRECEDING：指从当前行的位置往前数5行的位置。如果当前行是分区的第1行，前面没有足够的行，就从分区的第一行开始
• 6::int2 FOLLOWING：指从当前行的位置往后数6行的位置。如果当前行后面的行数不足6行，就到分区的最后一行结束

和RANGE模式的差异说明 #

你之前误以为两种模式结果一致，核心原因是混淆了ROWS和RANGE的逻辑：

• RANGE模式是基于ORDER BY字段的数值范围划定帧：比如原查询中RANGE BETWEEN 5 PRECEDING AND 6 FOLLOWING，是取unique1值在当前行unique1 -5到当前行unique1 +6之间的行。以four=0分区的第一行unique1=0为例，数值范围是-5到6，分区内的unique1=8超出这个范围，所以sum只计算0+4=4
• ROWS模式是基于行的物理位置：同样以four=0分区的第一行为例，当前行是分区第1行，往前5行不存在（取分区第1行），往后6行覆盖了分区所有3行，所以sum会计算0+4+8=12，这就是两种模式结果不同的原因

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