如何在PostgreSQL窗口框架中排除当前行的对等行？

要实现你想要的效果——窗口聚合仅包含number严格小于当前行number的记录，而排除所有与当前行number相同的对等行，有两种简洁的方法，其中最直接的是利用PostgreSQL的窗口框架EXCLUDE子句。

方法1：使用EXCLUDE GROUP（推荐） #

PostgreSQL支持窗口框架的EXCLUDE子句，其中EXCLUDE GROUP会排除当前行以及所有与当前行在ORDER BY列上值相等的行（即整个对等行组）。修改你的查询如下：

select id, string_agg(id::text, ',') over (order by number exclude group) from items;

执行结果： #

id | string_agg
---+-----------
1 | 
2 | 
3 | 1,2
4 | 1,2
5 | 1,2,3,4

这完全符合你的期望。

方法2：兼容旧版本/其他数据库的替代方案 #

如果你的环境不支持EXCLUDE子句，可以通过先给每个number分组分配排名，再聚合排名小于当前组的记录：

with numbered_items as (
  select 
    id, 
    number,
    dense_rank() over (order by number) as group_rank
  from items
)
select 
  id,
  string_agg(id::text, ',') over (order by group_rank exclude group)
from numbered_items;

或者通过CASE过滤掉当前及对等行：

select
  id,
  string_agg(case when number < current_num then id::text end, ',') over (order by number)
from items
cross join lateral (select number as current_num) as curr;

为什么原查询不符合需求？ #

原查询的窗口子句over (order by number)默认使用的窗口框架是RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW。对于ORDER BY列值相同的行（对等行），RANGE会将所有这些行都纳入窗口框架，因此：

• 当number=1时，两行的窗口都包含id=1和2
• 当number=2时，窗口包含id=1,2,3,4
  这就导致了原输出中不符合预期的聚合结果。

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