在Redshift中按组织及其后代递归汇总告警总数

核心思路 #

利用Redshift支持的递归CTE（WITH RECURSIVE），遍历每个组的所有层级后代（包含组自身），再按组ID和告警类型汇总总数。

实现SQL示例 #

假设你的表名为group_alert_data，以下是完整实现代码：

WITH RECURSIVE group_hierarchy AS (
    -- 锚点成员：初始化每个组自身作为起始节点，记录要汇总的主组ID
    SELECT
        group_id AS main_group_id,
        group_id,
        alert,
        total,
        hlevel,
        parent_id
    FROM group_alert_data

    UNION ALL

    -- 递归成员：关联子节点，将所有后代节点关联到主组ID下
    SELECT
        gh.main_group_id,
        gad.group_id,
        gad.alert,
        gad.total,
        gad.hlevel,
        gad.parent_id
    FROM group_hierarchy gh
    JOIN group_alert_data gad ON gh.group_id = gad.parent_id
)
-- 按主组ID和告警类型汇总总数
SELECT
    main_group_id AS group_id,
    alert,
    SUM(total) AS total_alert_count
FROM group_hierarchy
GROUP BY main_group_id, alert
ORDER BY main_group_id, alert;

代码说明 #

1. 递归CTE部分

   • 锚点成员：先把每个组自己加入结果集，main_group_id标记当前要汇总的目标组
   • 递归成员：通过parent_id关联上一层的group_id，把所有子节点纳入对应主组的结果集中，直到遍历完所有层级（最深10级完全覆盖）

2. 汇总部分

   • 按main_group_id（即目标组ID）和alert类型分组，用SUM(total)计算该组自身+所有后代的对应告警类型总数

补充说明 #

• 如果需要保留原表的其他字段（如root_group_id、full_path），可以在锚点成员中加入这些字段，最后在汇总结果里关联原表获取
• Redshift默认递归深度上限为100，完全满足你最深10级的层级需求
• 若存在同一组同一告警类型的多条记录，递归过程会自动全部纳入汇总，无需额外处理

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