SELECT列表标量子查询中窗口函数结果不符合预期的问题

问题示例SQL #

select 
  (select sum(c) over (ROWS UNBOUNDED PRECEDING) as a),
  sum(c) over (ROWS UNBOUNDED PRECEDING ) as b 
from (select unnest(ARRAY[1, 2, 3, 3, 4, 5]) as c) x

查询结果 #

疑问解答 #

1. 为何SELECT包装后结果不同？ #

SELECT列表中的子查询(select sum(c) over (...))是逐行独立执行的：外部查询每取出一行数据，这个子查询就仅基于当前这一行计算窗口函数——此时窗口里只有当前行，sum(c)的结果就是当前行的c值，自然无法形成累积和。

而列b的窗口函数直接作用于外部查询的完整结果集，窗口范围是从第一行到当前行的所有数据，因此能正常计算累积和。

2. 能否保留SELECT子查询同时让窗口函数正常工作？ #

可以，但需要让子查询能访问到外部查询的完整数据集，而非单一行。以PostgreSQL为例，可通过以下两种方式实现：

方式1：使用LATERAL子查询

select 
  sub.a_val,
  sum(x.c) over (ROWS UNBOUNDED PRECEDING) as b
from (select unnest(ARRAY[1, 2, 3, 3, 4, 5]) as c) x,
lateral (
  select sum(t.c) over (ROWS UNBOUNDED PRECEDING) as a_val
  from (select c from x) t
) sub

方式2：预计算累积和再关联

select 
  sub.a,
  sum(x.c) over (ROWS UNBOUNDED PRECEDING) as b
from (select unnest(ARRAY[1, 2, 3, 3, 4, 5]) as c) x
join (
  select 
    c,
    sum(c) over (ROWS UNBOUNDED PRECEDING) as a
  from (select unnest(ARRAY[1, 2, 3, 3, 4, 5]) as c) t
) sub on x.c = sub.c
order by x.c;

不过上述写法都属于冗余写法，直接像列b那样定义窗口函数是最简洁高效的方式。

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