泰坦尼克号数据集单SQL查询合并生存率问题求助

需要编写单条SQL查询，计算年龄≥50岁的男性和年龄≥40岁的女性的合并生存率。当前单查询代码返回0，但正确结果约为0.457143；拆分查询能得到正确结果，但需要合并为单查询。

错误代码 #

df_combined_surv = sqldf("SELECT (SUM(CASE WHEN Sex = 'male' AND Age >= 50 AND Survived = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 AND Survived = 1 THEN 1 ELSE 0 END)) / (SUM(CASE WHEN Sex = 'male' AND Age >= 50 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 THEN 1 ELSE 0 END)) AS SurvivalPercentage FROM df_titanic")

df_combined_surv.head()

补充说明（拆分查询可得到正确结果） #

df_combined_surv1 = sqldf("SELECT COUNT(*) FROM df_titanic WHERE CASE WHEN ((Sex = 'male' AND Age >= 50 AND Survived = 1) OR (Sex = 'female' AND Age >= 40 AND Survived = 1)) THEN 1 ELSE 0 END")
                          
df_combined_surv2 = sqldf("SELECT COUNT(*) FROM df_titanic WHERE CASE WHEN ((Sex = 'male' AND Age >= 50) OR (Sex = 'female' AND Age >= 40)) THEN 1 ELSE 0 END") 
 
df_combined_surv1 / df_combined_surv2

修正方案 #

问题根源是SQL整数除法特性：两个整数相除时结果会自动取整为整数。你的分子和分母均为整数类型的SUM结果，因此得到0。只需将其中一个值转为浮点数，即可触发浮点除法。

修正方法1：给分子乘以1.0转为浮点数 #

df_combined_surv = sqldf("SELECT (SUM(CASE WHEN Sex = 'male' AND Age >= 50 AND Survived = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 AND Survived = 1 THEN 1 ELSE 0 END)) * 1.0 / (SUM(CASE WHEN Sex = 'male' AND Age >= 50 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 THEN 1 ELSE 0 END)) AS SurvivalPercentage FROM df_titanic")

df_combined_surv.head()

修正方法2：用CAST转换分子为浮点数 #

df_combined_surv = sqldf("SELECT CAST((SUM(CASE WHEN Sex = 'male' AND Age >= 50 AND Survived = 1 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 AND Survived = 1 THEN 1 ELSE 0 END)) AS FLOAT) / (SUM(CASE WHEN Sex = 'male' AND Age >= 50 THEN 1 ELSE 0 END) + SUM(CASE WHEN Sex = 'female' AND Age >= 40 THEN 1 ELSE 0 END)) AS SurvivalPercentage FROM df_titanic")

简化写法（更清晰） #

先通过WHERE筛选目标人群，再统计生存率，逻辑更简洁：

df_combined_surv = sqldf("SELECT SUM(CASE WHEN Survived = 1 THEN 1 ELSE 0 END) * 1.0 / COUNT(*) AS SurvivalPercentage FROM df_titanic WHERE (Sex = 'male' AND Age >= 50) OR (Sex = 'female' AND Age >= 40)")

内容的提问来源于stack exchange，提问作者Seungryul Andrew Lee