使用Case-When计算分组占比结果异常问题求助

此前我曾咨询如何通过CASE-WHEN实现分组，如今尝试计算各分组段相对于对应组总计数的占比时，得到错误结果：第一组占比总和为100%，但其他组的占比总和未达100%。以下是我尝试的SQL代码及输出结果，请问问题出在哪里？

我的SQL代码 #

SELECT  type ||
       CASE 
       WHEN bg_turnover = 0
       THEN '_0'
       WHEN bg_turnover > 0 AND bg_turnover <= 500
       THEN '_0_500'
       WHEN bg_turnover > 500 AND bg_turnover <= 2500
       THEN '_500_2500'
       WHEN bg_turnover > 2500 AND bg_turnover <= 5000
       THEN '_2500_5000'
       ELSE '_5000+'
       END AS BG_TURNOVER,
       count(*) AS Total, Market, round((count(*)/(select count(*) from  product_table where market = 'DK')), 2) AS Percentage
FROM   product_table 
UNPIVOT (
  bg_turnover FOR type IN (
    BINGO_TURNOVER AS 'BINGO_TURNOVER',
    BINGO_TURNOVER_L10D     AS 'BINGO_TURNOVER_L10D',
    CASINO_TURNOVER      AS 'CASINO_TURNOVER',
) where market = 'DK'
GROUP BY
       type,
       CASE 
       WHEN bg_turnover = 0
       THEN '_0'
       WHEN bg_turnover > 0 AND bg_turnover <= 500
       THEN '_0_500'
       WHEN bg_turnover > 500 AND bg_turnover <= 2500
       THEN '_500_2500'
       WHEN bg_turnover > 2500 AND bg_turnover <= 5000
       THEN '_2500_5000'
       ELSE '_5000+'
       END, Market;

输出结果 #

BINGO_TURNOVER_0                    177540  DK  0.91
BINGO_TURNOVER_0_500                15789   DK  0.08
BINGO_TURNOVER_2500_5000            411     DK  0
BINGO_TURNOVER_5000+                1007    DK  0.01
BINGO_TURNOVER_500_2500             1423    DK  0.01
BINGO_TURNOVER_L90D_0               70474   DK  0.36
BINGO_TURNOVER_L90D_0_500           2586    DK  0.01
BINGO_TURNOVER_L90D_2500_5000       80      DK  0
BINGO_TURNOVER_L90D_5000+           91      DK  0
BINGO_TURNOVER_L90D_500_2500        299     DK  0

核心问题 #

你计算占比时用的分母是(select count(*) from product_table where market = 'DK')，这是整个DK市场的总记录数，但你实际需要的是每个type分组的总记录数（比如BINGO_TURNOVER组的总条数、BINGO_TURNOVER_L90D组的总条数），所以除第一个组外，其他组的占比都是相对于全表总数，自然总和不会到100%。

另外代码还有两个小问题：

• UNPIVOT语句里最后一个CASINO_TURNOVER后面多了逗号，会触发语法错误
• 输出出现BINGO_TURNOVER_L90D，但UNPIVOT里定义的是BINGO_TURNOVER_L10D，属于笔误

修正后的SQL #

使用窗口函数COUNT(*) OVER (PARTITION BY type)获取每个type分组的总记录数，确保占比是当前分段在对应type组内的占比：

SELECT  type ||
       CASE 
       WHEN bg_turnover = 0
       THEN '_0'
       WHEN bg_turnover > 0 AND bg_turnover <= 500
       THEN '_0_500'
       WHEN bg_turnover > 500 AND bg_turnover <= 2500
       THEN '_500_2500'
       WHEN bg_turnover > 2500 AND bg_turnover <= 5000
       THEN '_2500_5000'
       ELSE '_5000+'
       END AS BG_TURNOVER,
       COUNT(*) AS Total, 
       Market, 
       ROUND(COUNT(*) * 1.0 / COUNT(*) OVER (PARTITION BY type), 2) AS Percentage
FROM   product_table 
WHERE market = 'DK'
UNPIVOT (
  bg_turnover FOR type IN (
    BINGO_TURNOVER AS 'BINGO_TURNOVER',
    BINGO_TURNOVER_L90D AS 'BINGO_TURNOVER_L90D',
    CASINO_TURNOVER AS 'CASINO_TURNOVER'
  )
)
GROUP BY
       type,
       CASE 
       WHEN bg_turnover = 0
       THEN '_0'
       WHEN bg_turnover > 0 AND bg_turnover <= 500
       THEN '_0_500'
       WHEN bg_turnover > 500 AND bg_turnover <= 2500
       THEN '_500_2500'
       WHEN bg_turnover > 2500 AND bg_turnover <= 5000
       THEN '_2500_5000'
       ELSE '_5000+'
       END, 
       Market;

关键修改点 #

• 替换占比分母为COUNT(*) OVER (PARTITION BY type)，该窗口函数会计算每个type分组的总记录数
• 给COUNT(*)乘以1.0，避免整数除法导致的精度丢失（比如小数值被直接取整为0）
• 调整WHERE子句位置，先过滤数据再执行UNPIVOT转置
• 修正UNPIVOT里的逗号和笔误，匹配输出结果中的BINGO_TURNOVER_L90D

修改后每个type分组内的占比总和会达到100%。

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