如何定义窗口实现同组值的array_agg聚合？附分组计数方案

嘿，我刚好处理过类似的需求，给你分享个靠谱的解决方案！如果你需要对表中同组内的所有value执行array_agg聚合，关键是先给每个组分配一个唯一且组内不变的编号，然后基于这个编号分组聚合就行。

核心方案 #

当你的表需要按 <SORTING> 指定的字段排序，并且用 <GROUP-START-COND> 标记新组的起始条件时，用下面这个窗口函数就能生成完美的组编号：

count( <GROUP-START-COND> or null ) over ( order by <SORTING> ) AS groupnr

这个函数的逻辑很巧妙：每碰到一次符合新组起始条件的行，计数器就自动加1；组里其他不满足起始条件的行，计数器保持不变，这样就得到了一个随组递增、组内恒定的组编号。

实际使用示例 #

给你举个具体例子，方便你套用到自己的场景里：
假设你的表是交易记录表，需要：

• 按 transaction_time（交易时间）排序
• 当交易金额 amount > 1000 时标记为新组开始
• 把每个组里的 product_name（商品名称）聚合为数组

那完整的SQL代码可以这么写：

WITH grouped_data AS (
  SELECT
    *,
    -- 生成组编号
    count(amount > 1000 or null) over (order by transaction_time) AS groupnr
  FROM your_transaction_table
)
SELECT
  groupnr,
  -- 对组内的商品名称执行array_agg聚合
  array_agg(product_name) AS grouped_products
FROM grouped_data
GROUP BY groupnr
ORDER BY groupnr;

执行后就能得到每个组对应的商品名称数组啦。

为啥这个方法管用？ #

简单解释下原理：

• count(<GROUP-START-COND> or null)：当条件成立时，or null返回true，count会统计这个值；条件不成立时返回null，count不会统计null，所以每遇到一个新组起始行，count就+1。
• over (order by <SORTING>)：保证窗口按你指定的顺序处理行，确保组编号的生成顺序完全符合你的分组逻辑。

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