SQL查询重构优化咨询：能否用单个ROLLUP替代UNION ALL？

可以通过将维度字段（p、id、v）转换为行维度，结合单个ROLLUP来实现需求，避免多次UNION ALL和重复扫描数据。以下是优化后的查询：

WITH base_data AS (
    SELECT 
        p,
        id,
        v,
        CASE WHEN ts BETWEEN TRUNC(SYSDATE, 'MONTH') AND SYSDATE THEN 1 ELSE 0 END AS Anzahl,
        CASE WHEN ts BETWEEN TRUNC(SYSDATE, 'YEAR') AND SYSDATE THEN 1 ELSE 0 END AS Anzahl2
    FROM t
),
unpivoted_data AS (
    SELECT 
        dim_type,
        COALESCE(dim_value, 'Unbekannt') AS dim_value,
        Anzahl,
        Anzahl2
    FROM base_data
    UNPIVOT (
        dim_value FOR dim_type IN (
            p AS 'p',
            id AS 'id',
            v AS 'v'
        )
    )
)
SELECT 
    CASE 
        WHEN GROUPING(dim_value) = 1 THEN 'Gesamt' 
        ELSE dim_value
    END AS Art_der_Reklamation,
    SUM(Anzahl) AS Anzahl,
    SUM(Anzahl) * 100 / 30 AS AnteilMonat,
    SUM(Anzahl2) AS Anzahl2,
    SUM(Anzahl2) * 100 AS AnteilJahr
FROM unpivoted_data
GROUP BY dim_type, ROLLUP(dim_value)
ORDER BY dim_type, GROUPING(dim_value);

优化说明： #

• 简化基础数据查询：将原两次UNION ALL拆分月度/年度数据的逻辑，改为用CASE表达式一次性计算Anzahl和Anzahl2，减少对表t的扫描次数。
• 维度行转列：通过原生UNPIVOT语法将p、id、v三个维度字段转为统一的行结构，让所有维度的分组逻辑可以在一个查询中处理。
• 统一ROLLUP分组：按维度类型和维度值分组，结合ROLLUP生成每个维度的明细、小计和总计，彻底消除原查询中多次UNION ALL拼接结果的冗余操作。

如果数据库不支持原生UNPIVOT语法，也可以用手动UNION ALL实现行转列：

unpivoted_data AS (
    SELECT 
        'p' AS dim_type,
        COALESCE(p, 'Unbekannt') AS dim_value,
        Anzahl,
        Anzahl2
    FROM base_data
    UNION ALL
    SELECT 
        'id' AS dim_type,
        COALESCE(TO_CHAR(id), 'Unbekannt') AS dim_value,
        Anzahl,
        Anzahl2
    FROM base_data
    UNION ALL
    SELECT 
        'v' AS dim_type,
        COALESCE(TO_CHAR(v), 'Unbekannt') AS dim_value,
        Anzahl,
        Anzahl2
    FROM base_data
)

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