如何在输出所有明细行的同时按服务器分组获取聚合值

原始数据 #

期望输出 #

解决方案 #

使用ROLLUP结合GROUPING函数即可实现需求，同时利用MIN()函数满足状态聚合规则（字符串排序中ERROR优先级高于OK，MIN会自动返回包含ERROR的结果）。

完整SQL代码 #

WITH t AS (
    SELECT 'A' AS server, 'C1' AS component, 'OK' AS status FROM DUAL 
    UNION ALL 
    SELECT 'A', 'C2', 'OK' FROM DUAL 
    UNION ALL 
    SELECT 'B', 'C1', 'OK' FROM DUAL 
    UNION ALL 
    SELECT 'B', 'C2', 'ERROR' FROM DUAL
)
SELECT 
    CASE WHEN GROUPING(component) = 1 THEN server ELSE server END AS server,
    CASE WHEN GROUPING(component) = 1 THEN '' ELSE component END AS component,
    CASE WHEN GROUPING(component) = 1 THEN MIN(status) ELSE status END AS status
FROM t
GROUP BY ROLLUP(server, component)
ORDER BY server, GROUPING(component);

关键逻辑说明 #

• ROLLUP(server, component)会生成两类数据行：按server+component分组的明细行，以及仅按server分组的小计行。
• GROUPING(component)用于区分行类型：返回1时为小计行（component被聚合），返回0时为明细行。
• 小计行的component设为空，状态通过MIN(status)聚合——只要当前服务器下存在ERROR，MIN就会返回ERROR，符合需求。
• ORDER BY server, GROUPING(component)保证同一服务器的明细行排在小计行之前。

内容的提问来源于stack exchange，提问作者D. Mika