PieCloudDB中计算正常用户行程取消率的SQL查询异常求助

数据表结构 #

行程信息表（trips） #

用户信息表（users） #

需求说明 #

正常用户指complaint字段为"No"的用户，异常用户为complaint字段为"Yes"的用户。需计算每日请求中，乘客和司机均为正常用户的行程取消率，结果保留两位小数。

预期结果 #

尝试的SQL及错误结果 #

尝试的SQL语句：

select date, 
    round(
        (sum(case status when 'cancelled_by_client' then 1 else 0 end)
        +sum(case status when 'cancelled_by_driver' then 1 else 0 end)) 
        / count(1), 2) as Cancellation_Rate
from trips  as t
where client_id not in 
    (select users_id from trip_user where complaint='Yes')
    and driver_id not in 
    (select users_id from trip_user where complaint='Yes')
GROUP BY date
ORDER BY date;

得到的错误结果：

问题原因及修正 #

核心问题 #

SQL中引用的用户信息表名为trip_user，但实际提供的用户信息表表名为users，导致子查询无法获取正确的异常用户ID，过滤条件完全失效，最终统计的取消行程数为0。

修正后的SQL #

方案1：修正表名的子查询写法

select date, 
    round(
        sum(case when status in ('cancelled_by_client', 'cancelled_by_driver') then 1 else 0 end)
        / count(1), 2) as cancellation_rate
from trips  as t
where client_id not in 
    (select users_id from users where complaint='Yes')
    and driver_id not in 
    (select users_id from users where complaint='Yes')
GROUP BY date
ORDER BY date;

方案2：使用JOIN关联表（更直观）

SELECT 
    t.date,
    ROUND(
        SUM(CASE WHEN t.status IN ('cancelled_by_client', 'cancelled_by_driver') THEN 1 ELSE 0 END) 
        / COUNT(*), 
        2
    ) AS cancellation_rate
FROM trips t
JOIN users client 
    ON t.client_id = client.users_id 
    AND client.role = 'client'
    AND client.complaint = 'No'
JOIN users driver 
    ON t.driver_id = driver.users_id 
    AND driver.role = 'driver'
    AND driver.complaint = 'No'
GROUP BY t.date
ORDER BY t.date;

验证结果 #

修正后的SQL会正确过滤出乘客和司机均为正常用户的行程，计算出符合预期的取消率：

• 2023-11-15：3条有效行程，2条取消，取消率≈0.67
• 2023-11-16：仅1条有效行程且已完成，取消率0.00
• 2023-11-17：2条有效行程，1条取消，取消率0.50

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