如何在SQL查询中添加各州年度营收占总营收的百分比列

要实现需求，我们可以利用SQL Server的窗口函数SUM() OVER()计算所有州的年度总营收总和，再通过算术运算得到每个州的营收占比，最后格式化为带百分号的字符串。

完整SQL查询语句 #

SELECT STATE, 
 ROUND(SUM(CASE WHEN LOADDATE = '2022-02-07' THEN TNET END),0) AS JAN_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-03-07' THEN TNET END),0) AS FEB_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-04-07' THEN TNET END),0) AS MAR_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-05-07' THEN TNET END),0) AS APR_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-06-07' THEN TNET END),0) AS MAY_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-07-07' THEN TNET END),0) AS JUN_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-08-07' THEN TNET END),0) AS JUL_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-09-07' THEN TNET END),0) AS AUG_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-10-07' THEN TNET END),0) AS SEP_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-11-07' THEN TNET END),0) AS OCT_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2022-12-07' THEN TNET END),0) AS NOV_TOTALREV,
 ROUND(SUM(CASE WHEN LOADDATE = '2023-01-07' THEN TNET END),0) AS DEC_TOTALREV,
 ROUND(SUM(TNET),0) AS YEAR_TOTALREV,
 -- 新增百分比列
 CONCAT(ROUND( (SUM(TNET) * 100.0) / SUM(SUM(TNET)) OVER(), 0 ), '%') AS YEAR_REV_PERCENTAGE
FROM [dbo].[example]
WHERE LOADDATE BETWEEN '2022-02-01' AND '2023-01-31'
GROUP BY STATE

关键逻辑说明 #

1. 全局总营收计算：SUM(SUM(TNET)) OVER() 是窗口函数的用法，外层SUM()会对所有分组（每个州）的SUM(TNET)（单州年度营收）再次求和，得到所有州的年度总营收总和。
2. 占比计算：(SUM(TNET) * 100.0) / 全局总营收 中乘以100.0是为了避免整数除法导致的精度丢失，确保计算结果为小数。
3. 格式转换：用ROUND(..., 0)取整后，通过CONCAT(..., '%')拼接百分号，得到符合要求的百分比格式。

查询结果示例 #

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