PostgreSQL计算银行客户流失率结果为0的问题求助

问题出在整数除法的截断特性：SQL中两个整数相除时，会自动截断小数部分只保留整数结果。比如流失客户数是3、总客户数是100时，3/100的整数运算结果为0，再乘以100依然是0。

修改方案（任选其一即可） #

1. 将其中一个运算数转换为浮点类型，强制触发浮点除法：

SELECT
    COUNT(*) AS total_customers,
    SUM(CASE WHEN churn = 'Yes' THEN 1 ELSE 0 END) AS churned_customers,
    (SUM(CASE WHEN churn = 'Yes' THEN 1 ELSE 0 END) / CAST(COUNT(*) AS FLOAT)) * 100 AS churn_rate_percentage
FROM
    bank_churn_data

2. 用1.0替代1作为CASE语句的返回值，让分子变成浮点数：

SELECT
    COUNT(*) AS total_customers,
    SUM(CASE WHEN churn = 'Yes' THEN 1.0 ELSE 0 END) AS churned_customers,
    (SUM(CASE WHEN churn = 'Yes' THEN 1.0 ELSE 0 END) / COUNT(*)) * 100 AS churn_rate_percentage
FROM
    bank_churn_data

3. 调整运算顺序，先乘100.0再做除法，避免整数截断：

SELECT
    COUNT(*) AS total_customers,
    SUM(CASE WHEN churn = 'Yes' THEN 1 ELSE 0 END) AS churned_customers,
    (SUM(CASE WHEN churn = 'Yes' THEN 1 ELSE 0 END) * 100.0 / COUNT(*)) AS churn_rate_percentage
FROM
    bank_churn_data

以上方法都能让除法运算保留小数部分，最终得到正确的流失率百分比。

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