我正在尝试计算我的数据中男性的比例和女性的比例。
我有以下代码:
select
SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) / COUNT(Gender) as perc_male,
SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) / COUNT (Gender) as perc_female
From [my_table]
但是,当运行它时,我得到 0%。谁能看到我可能在这里做错了什么?
谢谢
我认为值小于 1,这就是你得到 0 的原因尝试如下
select
SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) / COUNT(Gender)*1.00
as perc_male,
SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) / COUNT (Gender)*1.00
as perc_female
From [my_table]
或在浮点数据类型中使用显式强制转换
只需使用:
select
SUM(CASE WHEN Gender = 'M' THEN 1.0 ELSE 0.0 END) / COUNT(Gender)
as perc_male,
SUM(CASE WHEN Gender = 'F' THEN 1.0 ELSE 0.0 END) / COUNT (Gender)
as perc_female
From [my_table]
您正在使用整数。在处理整数时,SQL 总是尝试返回整数,这里就是这种情况 - ot 向下舍入到 0。
使用其中一个值的显式强制转换来获得所需的精度,例如
select
SUM(CASE WHEN Gender = 'M' THEN 1 ELSE 0 END) / cast(COUNT(Gender) as decimal(5,3)) as perc_male,
SUM(CASE WHEN Gender = 'F' THEN 1 ELSE 0 END) / cast(COUNT(Gender) as decimal(5,3)) as perc_female
From [my_table]
问题是整数除法。 我认为最简单的方法是使用AVG()
:
SELECT AVG(CASE WHEN Gender = 'M' THEN 1.0 ELSE 0 END) as perc_male,
AVG(CASE WHEN Gender = 'F' THEN 1.0 ELSE 0 END) as perc_female
FROM [my_table]