如何在分组SQL中计算交易数占总数百分比并生成汇总表

需求说明 #

需要生成包含汇总行的银行交易统计表格，列定义如下：

• TRN：交易数量
• %：单银行交易数占总交易数的百分比
• BANK：汇款银行名称（汇总行显示ALL）

原问题 #

原SQL通过UNION ALL拼接汇总行与分组统计行，但分组后的百分比无法正确计算，需借助窗口函数OVER()实现准确占比。

修正后的SQL方案 #

方案1：用WITH ROLLUP自动生成汇总行（更高效）

SELECT 
    COUNT(*) AS TRN,
    CASE 
        WHEN BANK_ID IS NULL THEN '100%'
        ELSE CONCAT(ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2), '%')
    END AS `%`,
    COALESCE(BANK_ID, 'ALL') AS BANK
FROM TABLE2
GROUP BY BANK_ID WITH ROLLUP
ORDER BY TRN DESC;

方案2：保留原UNION ALL写法（贴合原有思路）

-- 汇总行
SELECT 
    COUNT(*) AS TRN,
    '100%' AS `%`,
    'ALL' AS BANK
FROM TABLE2

UNION ALL

-- 各银行统计行
SELECT 
    COUNT(*) AS TRN,
    CONCAT(ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM TABLE2), 2), '%') AS `%`,
    BANK_ID AS BANK
FROM TABLE2
GROUP BY BANK_ID
ORDER BY TRN DESC;

关键细节说明 #

• 总交易数计算：SUM(COUNT(*)) OVER () 或子查询(SELECT COUNT(*) FROM TABLE2)均可获取全表总交易数，前者属于窗口函数写法，无需重复扫描表
• 百分比精度：用100.0触发浮点除法，避免整数除法导致的精度丢失；ROUND()可控制小数位数，按需调整
• 语法修正：原SQL中GROUP BY A.BANK_ID属于错误引用，需改为GROUP BY BANK_ID；特殊列名%用反引号`%`包裹避免语法报错
• ROLLUP优势：GROUP BY ... WITH ROLLUP会自动生成汇总行，无需单独编写UNION ALL语句，逻辑更简洁

内容的提问来源于stack exchange，提问作者Matěj Alex