Oracle 19c中基于自定义非互斥分组的聚合求和实现

针对Oracle 19c环境下的自定义分组求和需求，推荐两种实现方式，可根据你的列数选择更合适的方案：

方法1：UNION ALL 分别计算各分组 #

这种方式写法直观，适合列数较少的场景，需要为每个分组单独编写聚合查询后合并结果：

SELECT 
    Name, 
    'G1' AS "Group", 
    SUM(Quant_1) AS Quant_1, 
    SUM(Quant_2) AS Quant_2
    -- 依次添加其他数值列的SUM语句，比如SUM(Quant_3), SUM(Quant_4)...
FROM your_table
WHERE Category <= 2
GROUP BY Name

UNION ALL

SELECT 
    Name, 
    'G2' AS "Group", 
    SUM(Quant_1) AS Quant_1, 
    SUM(Quant_2) AS Quant_2
    -- 重复添加其他数值列的SUM语句
FROM your_table
WHERE Category <= 4
GROUP BY Name

UNION ALL

SELECT 
    Name, 
    'G3' AS "Group", 
    SUM(Quant_1) AS Quant_1, 
    SUM(Quant_2) AS Quant_2
    -- 重复添加其他数值列的SUM语句
FROM your_table
WHERE Category <= 6
GROUP BY Name

ORDER BY Name, "Group";

方法2：CROSS JOIN 分组条件表（推荐多列场景） #

这种方式通过预定义分组条件，避免重复编写大量SUM语句，更适合你有20余列数值型字段的场景，维护性更高：

WITH group_definitions AS (
    SELECT 'G1' AS group_name, 2 AS max_category FROM dual
    UNION ALL
    SELECT 'G2' AS group_name, 4 AS max_category FROM dual
    UNION ALL
    SELECT 'G3' AS group_name, 6 AS max_category FROM dual
)
SELECT 
    t.Name,
    gd.group_name AS "Group",
    SUM(t.Quant_1) AS Quant_1,
    SUM(t.Quant_2) AS Quant_2,
    -- 一次性添加所有其他数值列的SUM，比如SUM(t.Quant_3), SUM(t.Quant_4)...直到覆盖所有列
    SUM(t.Quant_20) AS Quant_20
FROM your_table t
CROSS JOIN group_definitions gd
WHERE t.Category <= gd.max_category
GROUP BY t.Name, gd.group_name
ORDER BY t.Name, gd.group_name;

说明 #

• 两种方法都会按Name和自定义分组聚合，得到你需要的累积求和结果
• 方法2中，group_definitions CTE定义了每个分组的名称和对应的Category上限，通过CROSS JOIN将原表每行与三个分组条件匹配，筛选符合条件的行后聚合，只需编写一次所有数值列的SUM语句
• 将代码中的your_table替换为你的实际表名，补充所有需要求和的数值列即可

内容的提问来源于stack exchange，提问作者Yeltsin Parmenio Vega Niño