(LeetCode 262) 行程和用户

表：Trips
+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Id | int |
| Client_Id | int |
| Driver_Id | int |
| City_Id | int |
| Status | enum |
| Request_at | date |
+-------------+----------+
Id 是这张表的主键。
这张表中存所有出租车的行程信息。每段行程有唯一 Id ，其中 Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。
Status 是一个表示行程状态的枚举类型，枚举成员为(‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’) 。

表：Users

+-------------+----------+
| Column Name | Type |
+-------------+----------+
| Users_Id | int |
| Banned | enum |
| Role | enum |
+-------------+----------+
Users_Id 是这张表的主键。
这张表中存所有用户，每个用户都有一个唯一的 Users_Id ，Role 是一个表示用户身份的枚举类型，枚举成员为 (‘client’, ‘driver’, ‘partner’) 。
Banned 是一个表示用户是否被禁止的枚举类型，枚举成员为 (‘Yes’, ‘No’) 。

写一段 SQL 语句查出 "2013-10-01" 至 "2013-10-03" 期间非禁止用户（乘客和司机都必须未被禁止）的取消率。非禁止用户即 Banned 为 No 的用户，禁止用户即 Banned 为 Yes 的用户。

取消率 的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

返回结果表中的数据可以按任意顺序组织。其中取消率 Cancellation Rate 需要四舍五入保留 两位小数

解题思路

1. 条件筛选 (乘客和司机都必须未被禁止)
2. 条件筛选 2013-10-01 至 2013-10-03 期间
3. 取消率 (被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

解题步骤

1. 条件筛选 非禁止用户

SELECT
    a.* 
FROM
    trips a
    LEFT JOIN users b ON a.Client_Id = b.Users_Id
    LEFT JOIN users c ON a.Driver_Id = c.Users_Id 
WHERE
    b.Banned = "No" 
    AND c.Banned = "No";

2. 条件筛选 2013-10-01 至 2013-10-03 期间

SELECT
    a.* 
FROM
    trips a
    LEFT JOIN users b ON a.Client_Id = b.Users_Id
    LEFT JOIN users c ON a.Driver_Id = c.Users_Id 
WHERE
    b.Banned = "No" 
    AND c.Banned = "No" 
    AND ( a.Request_at >= "2013-10-01" AND a.Request_at <= "2013-10-03" );

3. 取消率 (被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)。

SELECT
    a.Request_at AS DAY,
    ROUND( sum( a.`Status` != "completed" ) / count( 1 ), 2 ) AS "Cancellation Rate" 
FROM
    trips a
    LEFT JOIN users b ON a.Client_Id = b.Users_Id
    LEFT JOIN users c ON a.Driver_Id = c.Users_Id 
WHERE
    b.Banned = "No" 
    AND c.Banned = "No" 
    AND ( a.Request_at >= "2013-10-01" AND a.Request_at <= "2013-10-03" ) 
GROUP BY
    a.Request_at;

知识点分析

1. 关联同一张表两次，条件查询
2. 聚合函数 count() sum() ,sum() 可以传递表达式，count() 不行
3. round 函数，round(x,d) ：x 是要处理的数字，d是要保留的小数点位置