Oracle SQL实现分组求和及所有分组总和的方法

需求说明 #

• 实现数据透视（Pivot）后的总和求和，用于分析系统总故障时长
• 单独统计1 - Down（系统宕机）和2 - Degraded（性能降级）两类故障的时长
• 指定周期无对应故障数据时，该类别时长按0处理
• 创建总计列，计算所有故障类别的时长总和

现有按周汇总数据（已处理跨年场景，空值故障等级归类为'TBD'） #

期望查询结果 #

示例数据（Oracle） #

CREATE TABLE MY_TABLE
(
    cy               INT          NOT NULL,
    week             INT          NOT NULL,
    IMPACT           VARCHAR2(12) NOT NULL,
    duration_minutes NUMBER       NOT NULL
);

INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 42);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 88);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 50, '2 - Degraded', 45);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '1 - Down', 70);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 86);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 220);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2022, 51, '2 - Degraded', 216);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 29);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 62);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '1 - Down', 369);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 58);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 42);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 277);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 1, '2 - Degraded', 881);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '1 - Down', 40);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '1 - Down', 18);
INSERT INTO my_table(CY, WEEK, IMPACT, DURATION_MINUTES) VALUES (2023, 2, '2 - Degraded', 98);

COMMIT;

生成非透视汇总数据的查询语句 #

SELECT cy,
       week,
       impact,
       SUM(duration_minutes) AS duration_minutes
  FROM my_table
 GROUP BY cy, week, impact
 ORDER BY cy, week, impact;

实现需求的透视查询语句 #

WITH weekly_summary AS (
    SELECT 
        cy,
        week,
        NVL(impact, '0 - TBD') AS impact,  -- 将空值故障等级归类为TBD
        SUM(duration_minutes) AS duration_minutes
    FROM my_table
    GROUP BY cy, week, NVL(impact, '0 - TBD')
)
SELECT 
    cy AS "年份",
    week AS "周数",
    NVL("0 - TBD", 0) AS "0 - 待分类",
    NVL("2 - Degraded", 0) AS "2 - 性能降级",
    NVL("1 - Down", 0) AS "1 - 系统宕机",
    -- 计算总计：所有故障类别时长之和
    NVL("0 - TBD", 0) + NVL("2 - Degraded", 0) + NVL("1 - Down", 0) AS "总计"
FROM weekly_summary
PIVOT (
    SUM(duration_minutes)
    FOR impact IN (
        '0 - TBD' AS "0 - TBD",
        '2 - Degraded' AS "2 - Degraded",
        '1 - Down' AS "1 - Down"
    )
)
ORDER BY cy, week;

语句说明 #

1. CTE汇总：先按年份、周数、故障等级（含空值转TBD）汇总时长，确保每个周期内的故障类别都有统计基础
2. 透视转换：使用PIVOT将故障等级的行数据转为列，实现分类展示
3. 空值处理：用NVL将透视后无数据的类别值替换为0，满足无数据按0统计的要求
4. 总计计算：直接对三个类别处理后的值求和，得到周期总故障时长

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