如何在Redshift中使用window functions仅聚合连续特定值的行？

源表数据 #

预期结果 #

实现思路 #

核心是识别连续的B=1行组：通过累计计数生成分组ID，每次遇到B=0的行就累加计数，这样连续的B=1行会被归为同一个分组。之后对不同分组分别处理：

• B=0的行直接保留原A、C值
• B=1的分组聚合计算MAX(A)和SUM(C)

SQL代码 #

WITH grouped_data AS (
    SELECT
        A,
        B,
        C,
        -- 生成分组ID：遇到B=0时累加，连续B=1行共享同一ID
        SUM(CASE WHEN B = 0 THEN 1 ELSE 0 END) OVER (ORDER BY A) AS group_id
    FROM your_table_name
)
SELECT
    CASE
        WHEN B = 0 THEN A
        ELSE MAX(A)
    END AS new_A,
    CASE
        WHEN B = 0 THEN C
        ELSE SUM(C)
    END AS new_C
FROM grouped_data
GROUP BY group_id, B, 
         CASE WHEN B = 0 THEN A ELSE NULL END, 
         CASE WHEN B = 0 THEN C ELSE NULL END
ORDER BY new_A;

代码说明 #

1. CTE grouped_data：通过窗口函数SUM() OVER (ORDER BY A)生成分组ID，确保连续的B=1行属于同一分组。
2. 分组聚合：
   • 对B=0的行，按group_id+A+C分组，直接保留原数据
   • 对B=1的分组，按group_id+B分组，计算聚合值
3. 排序：最终按new_A升序排列，匹配预期结果顺序

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