UNBOUNDED PRECEDING与当前行窗口框架的工作原理剖析

测试数据 #

CREATE SCHEMA test_schema;
CREATE TABLE test_schema.joinedDf(id LONG, firstName TEXT, lastName TEXT, age INT, month INT, salary DECIMAL);

INSERT INTO test_schema.joinedDf(id, firstName, lastName, age, month, salary) VALUES
(1111, 'Peter', 'Ivanov', 29, 10, 300000),
(1111, 'Peter', 'Ivanov', 29, 12, 350000),
(1111, 'Peter', 'Ivanov', 29, 11, 350000);

查询1：指定全窗口范围 #

执行查询：

SELECT id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) average_salary
FROM test_schema.joinedDf;

得到结果：

1111, Peter, Ivanov, 29, 10, 300000, 333333.3333
1111, Peter, Ivanov, 29, 12, 350000, 333333.3333
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

查询2：仅指定ORDER BY的默认窗口 #

执行查询：

SELECT id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id ORDER BY salary) average_salary
FROM test_schema.joinedDf;

得到结果：

1111, Peter, Ivanov, 29, 10, 300000, 300000.0000
1111, Peter, Ivanov, 29, 12, 350000, 333333.3333
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

疑问 #

已知PARTITION BY中指定ORDER BY时，默认窗口框架为UNBOUNDED PRECEDING AND CURRENT ROW，但为什么结果不是预期的逐步累加计算：

1111, Peter, Ivanov, 29, 10, 300000, 300000.0000
1111, Peter, Ivanov, 29, 12, 350000, 325000.0000
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

原因解释 #

核心问题在于默认窗口框架的模式是RANGE而非ROWS：

• 当窗口函数仅指定ORDER BY而未显式声明框架模式时，SQL标准默认使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，而不是ROWS模式。
• RANGE模式下，窗口范围是基于排序键的逻辑值：所有排序键值小于等于当前行排序键值的行都会被包含在窗口内，而不是仅到物理上的当前行。
• 在你的数据中，有两行salary=350000，它们的排序键值相同。因此，当计算这两行的窗口平均值时，窗口会包含所有salary<=350000的行（也就是全部3行），所以平均值为(300000+350000+350000)/3=333333.3333。

如何得到预期的逐步计算结果 #

如果想要按照物理行顺序逐行累加计算平均值，需要显式指定ROWS模式的窗口框架：

SELECT id, firstName, lastName, age, month, salary,
AVG(salary) OVER (PARTITION BY id ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) average_salary
FROM test_schema.joinedDf;

此时执行结果会符合你预期的逐步累加：

1111, Peter, Ivanov, 29, 10, 300000, 300000.0000
1111, Peter, Ivanov, 29, 12, 350000, 325000.0000
1111, Peter, Ivanov, 29, 11, 350000, 333333.3333

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