如何不使用UNION ALL或自连接生成表的汇总行以减少表扫描

原始数据表 #

预期输出结果 #

已尝试的UNION ALL方案 #

原查询（修正语法错误后）如下，该方案需要两次扫描数据表：

SELECT date,
       category,
       'ALL' AS subcategory,
       SUM(price) AS price
FROM your_table
GROUP BY date, category
UNION ALL
SELECT date,
       category,
       subcategory,
       price
FROM your_table;

优化方案：使用ROLLUP子句 #

利用SQL的ROLLUP分组扩展，可以在一次表扫描中同时获取明细行和按date+category维度的汇总行，避免重复扫描。

优化后的查询代码 #

SELECT 
    date,
    category,
    CASE 
        WHEN subcategory IS NULL THEN 'ALL' 
        ELSE subcategory 
    END AS subcategory,
    SUM(price) AS price
FROM your_table
GROUP BY date, category, subcategory WITH ROLLUP
HAVING date IS NOT NULL AND category IS NOT NULL;

逻辑说明 #

1. WITH ROLLUP会在分组时自动生成两类行：
   • 第一类是date+category+subcategory粒度的明细行，此时SUM(price)等价于单条记录的price值
   • 第二类是date+category粒度的汇总行，此时subcategory字段为NULL，通过CASE语句替换为'ALL'
2. HAVING子句过滤掉更高层级的无效汇总行（比如全表总汇总，此时date或category会为NULL），仅保留目标数据。

该查询仅需扫描一次数据表，性能优于原UNION ALL方案，输出结果完全符合预期。

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