SQL计算未封禁用户每日行程取消率 除法运算报错求助

你的SQL存在以下几处错误，修改后即可正常运行： #

• 筛选逻辑错误：原WHERE子句用OR判断未封禁条件，需求要求乘客、司机双方均未被封禁，需要改为AND
• 除法逻辑写反：取消率=取消订单数/总订单数，原代码写反为总订单数/取消数，既不符合计算逻辑，也会出现取消数为0时的除零报错
• 缺少时间范围过滤：没有添加2013-10-01至2013-10-03的统计条件
• 整数除法问题：大部分数据库中整数直接相除会返回整数，无法得到正确的小数占比
• 没有做两位小数格式化，不符合输出要求

修正后的SQL代码（SQL Server环境）：

WITH CTE AS (
    SELECT 
        Request_at AS [DAY],
        SUM(CASE
            WHEN Status IN ('cancelled_by_driver', 'cancelled_by_client') THEN 1
            ELSE 0
        END) AS Cancellations,
        COUNT(*) AS TotalTrips
    FROM Trips
    WHERE 
        Request_at BETWEEN '2013-10-01' AND '2013-10-03'
        AND Client_id NOT IN (SELECT Users_id FROM [Study].[dbo].[Users] WHERE Banned='YES' AND Roll='Client')
        AND Driver_id NOT IN (SELECT Users_id FROM [Study].[dbo].[Users] WHERE Banned='YES' AND Roll='Driver')
    GROUP BY Request_at
)
SELECT 
    [DAY],
    FORMAT(CAST(Cancellations AS DECIMAL(10,2)) / NULLIF(TotalTrips, 0), '0.00') AS CancellationRate
FROM CTE

其他数据库适配调整：

• MySQL环境：将格式化部分替换为ROUND(CAST(Cancellations AS DECIMAL(10,2)) / NULLIF(TotalTrips, 0), 2)
• PostgreSQL环境：将格式化部分替换为TO_CHAR(CAST(Cancellations AS NUMERIC) / NULLIF(TotalTrips, 0), '0.00')

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