Oracle SQL分层分组查询优化：简化多步骤分组及冗余列处理

我有一张经多表关联得到的大表，包含多个键列（部分为计算生成）和一个值列，源数据如下：

需求步骤 #

步骤1 #

按所有键列分组，对value1求和得到sum(value1)，并调用自定义函数calculate_value2基于键组合生成value2，结果如下：

步骤2 #

按除key4外的其他键列分组，对sum(value1)和value2再次求和，结果如下：

现有问题 #

示例查询采用三层嵌套结构，但实际查询中需包含多个修饰列，每一层分组和选择时都要携带这些列，导致代码过于臃肿。我尝试过两种优化方案：

• 使用主键替代键列，分组完成后通过子查询获取所需列
• 在分组结果上层关联源表获取列
  但两种方案仍较为繁琐，希望得到更简洁的单查询实现建议。

以下是两种简洁的单查询实现思路，可有效避免代码臃肿：

思路1：用CTE（公共表表达式）拆分逻辑 #

用CTE封装第一次聚合的逻辑，后续二次聚合直接引用，避免重复书写键列和修饰列：

WITH step1_agg AS (
    SELECT 
        key1, key2, key3, key4,
        SUM(value1) AS sum_value1,
        calculate_value2(key1, key2, key3, key4) AS value2,
        -- 一次性引入所有修饰列，后续无需重复定义
        修饰列1, 修饰列2, 修饰列3
    FROM your_large_table
    GROUP BY key1, key2, key3, key4, 修饰列1, 修饰列2, 修饰列3
)
SELECT 
    key1, key2, key3,
    SUM(sum_value1) AS sum_sum_value1,
    SUM(value2) AS sum_value2,
    -- 同一key1/key2/key3分组下修饰列值唯一，用MAX/MIN直接提取
    MAX(修饰列1) AS 修饰列1,
    MAX(修饰列2) AS 修饰列2,
    MAX(修饰列3) AS 修饰列3
FROM step1_agg
GROUP BY key1, key2, key3;

CTE的优势在于将步骤1的聚合逻辑模块化，二次聚合时只需关注核心分组和求和逻辑，修饰列只需定义一次。

思路2：窗口函数+DISTINCT合并两次聚合 #

利用窗口函数在一次分组聚合中直接计算最终求和结果，无需二次分组查询：

SELECT DISTINCT
    key1, key2, key3,
    -- 按key1/key2/key3分区，对第一次聚合的sum_value1求和
    SUM(SUM(value1)) OVER (PARTITION BY key1, key2, key3) AS sum_sum_value1,
    -- 对自定义函数生成的value2按分区求和
    SUM(calculate_value2(key1, key2, key3, key4)) OVER (PARTITION BY key1, key2, key3) AS sum_value2,
    -- 修饰列用窗口函数提取唯一值
    MAX(修饰列1) OVER (PARTITION BY key1, key2, key3) AS 修饰列1,
    MAX(修饰列2) OVER (PARTITION BY key1, key2, key3) AS 修饰列2
FROM your_large_table
GROUP BY key1, key2, key3, key4, 修饰列1, 修饰列2;

这里通过SUM() OVER (PARTITION BY ...)在第一次分组的基础上完成二次求和，最后用DISTINCT去重得到最终结果，整个逻辑合并在一个查询中，代码更紧凑。

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