SQL统计dispute_lost为1的占比异常问题求助

问题根源是整数除法：SQL中两个整数类型的数值相除时，会自动舍弃小数部分返回整数结果。比如你实际需要的占比是4.09%，意味着Dispute_count和Total_Dispute_count的比值是0.0409，但整数除法会把这个结果取整为0，乘以100后还是0。

修正方案1：通过数值转换触发浮点除法 #

在分子或分母上乘以1.0，将整数转为浮点数，这样除法运算会保留小数部分：

SELECT 
  (SELECT COUNT(dispute_lost) FROM yellevate_data_2 WHERE dispute_lost = 1) AS Dispute_count,
  COUNT(dispute_lost) AS Total_Dispute_count,
  ((SELECT COUNT(dispute_lost) FROM yellevate_data_2 WHERE dispute_lost = 1) * 1.0 / COUNT(dispute_lost)) * 100 AS mult
FROM yellevate_data_2;

修正方案2：显式转换数据类型 #

用CAST函数将其中一个计数转为浮点类型，确保除法运算为浮点运算：

SELECT 
  (SELECT COUNT(dispute_lost) FROM yellevate_data_2 WHERE dispute_lost = 1) AS Dispute_count,
  COUNT(dispute_lost) AS Total_Dispute_count,
  ((CAST((SELECT COUNT(dispute_lost) FROM yellevate_data_2 WHERE dispute_lost = 1) AS FLOAT) / COUNT(dispute_lost)) * 100) AS mult
FROM yellevate_data_2;

优化方案：避免重复扫描表 #

原SQL中两次子查询会重复扫描表，用SUM+CASE可以一次完成统计，同时解决除法问题：

SELECT 
  SUM(CASE WHEN dispute_lost = 1 THEN 1 ELSE 0 END) AS Dispute_count,
  COUNT(dispute_lost) AS Total_Dispute_count,
  (SUM(CASE WHEN dispute_lost = 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(dispute_lost)) * 100 AS mult
FROM yellevate_data_2;

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