如何优化带GROUP BY的COUNT(*)查询并高效添加总计行？

嘿，这个问题我太懂了！你现在用UNION的方式确实会让数据库重复扫描同一张表两次，数据量一大就会明显变慢。给你两个高效的优化方案，都能避免重复扫描，性能提升不少：

方案1：使用GROUP BY ROLLUP（最优推荐） #

这是数据库原生支持的聚合扩展，专门用来生成分组小计和总计，只需要扫描一次表就能得到结果，执行效率最高：

SELECT 
    COALESCE(PCUST, 'TOTAL') AS PCUST,
    COUNT(*) AS count
FROM PAYMENT 
WHERE PAYMENT_DATE = '2020-12-30'
GROUP BY ROLLUP(PCUST);

解释： #

• ROLLUP(PCUST) 会在按PCUST分组的结果末尾，自动添加一行总计数据，这一行的PCUST值为NULL
• COALESCE函数把NULL替换成你需要的'TOTAL'，保证输出格式和你原来的查询完全一致
• 整个查询只扫描一次PAYMENT表，比原来的两次扫描效率提升一倍左右（数据量越大越明显）

方案2：使用CTE预过滤数据 #

如果你的数据库版本比较旧，不支持ROLLUP，可以用公共表表达式（CTE）先把符合条件的数据查出来一次，再基于这个结果做分组和总计：

WITH filtered_payments AS (
    -- 只执行一次过滤，把符合日期条件的数据提取出来
    SELECT PCUST 
    FROM PAYMENT 
    WHERE PAYMENT_DATE = '2020-12-30'
)
-- 分组统计
SELECT PCUST, COUNT(*) AS count
FROM filtered_payments
GROUP BY PCUST
-- 用UNION ALL替代UNION，避免不必要的去重操作
UNION ALL
-- 总计统计
SELECT 'TOTAL', COUNT(*) AS count
FROM filtered_payments;

解释： #

• filtered_payments CTE只执行一次，数据库会把符合条件的数据临时存储（或者在内存中处理）
• 后续的分组和总计都基于这个临时结果，不需要再扫描原表
• 用UNION ALL代替UNION，因为分组行和总计行不可能重复，省去了UNION的去重开销，进一步提升效率

对比原方案的优势 #

原方案中两次SELECT都要执行WHERE PAYMENT_DATE = '2020-12-30'过滤，相当于数据库要扫描两次PAYMENT表；而上面两个方案都只扫描一次原表，性能差异在数据量较大时会非常显著。

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