Update
此行为已记录在案here https://www.postgresql.org/docs/11/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS:
4.2.8.窗口函数调用
[..]
默认的框架选项是RANGE UNBOUNDED PRECEDING
,即
与RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
。和ORDER BY
,这将框架设置为分区中的所有行
从当前行的最后一个开始ORDER BY
同行。没有ORDER BY
,这意味着分区的所有行都包含在
窗口框架,因为所有行都成为当前行的对等行。
这意味着:
在没有一个框架子句 – RANGE UNBOUNDED PRECEDING
默认使用。包括了:
- 根据以下规则,当前行“之前”的所有行
ORDER BY
clause
- 当前行
- 中具有相同值的所有行
ORDER BY
列作为当前行
在没有ORDER BY
条款 –ORDER BY NULL
是假设的(尽管我再次猜测)。就这样frame将包括来自的所有行分割,因为中的值ORDER BY
列是相同的(总是NULL
)在每一行。
原答案:
免责声明:以下更多的是猜测,而不是合格的答案。我没有找到任何文档可以证实我写的内容。同时,我认为当前给出的答案不能正确解释该行为。
结果差异的原因并不直接是 ORDER BY 子句,因为a + b + c
是相同的c + b + a
。原因是(这是我的猜测)ORDER BY 子句隐式定义了框架子句 as
rows between unbounded preceding and current row
尝试以下查询:
select *
, sum(val) over (partition by user_id) as res
, sum(val) over (partition by user_id order by ts) as res_order_by
, sum(val) over (
partition by user_id
order by ts
rows between unbounded preceding and current row
) as res_order_by_unbounded_preceding
, sum(val) over (
partition by user_id
-- order by ts
rows between unbounded preceding and current row
) as res_preceding
, sum(val) over (
partition by user_id
-- order by ts
rows between current row and unbounded following
) as res_following
, sum(val) over (
partition by user_id
order by ts
rows between unbounded preceding and unbounded following
) as res_orderby_preceding_following
from example_table;
您将看到,您可以在没有 ORDER BY 子句的情况下获得累积总和,也可以通过 ORDER BY 子句获得“完整”总和。