262. 行程和用户(SQL）

一、题目描述

表：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 需要四舍五入保留 两位小数 。
查询结果格式如下例所示：
Trips 表：
±—±----------±----------±--------±--------------------±-----------+
| Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
±—±----------±----------±--------±--------------------±-----------+
| 1 | 1 | 10 | 1 | completed | 2013-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2013-10-01 |
| 3 | 3 | 12 | 6 | completed | 2013-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2013-10-01 |
| 5 | 1 | 10 | 1 | completed | 2013-10-02 |
| 6 | 2 | 11 | 6 | completed | 2013-10-02 |
| 7 | 3 | 12 | 6 | completed | 2013-10-02 |
| 8 | 2 | 12 | 12 | completed | 2013-10-03 |
| 9 | 3 | 10 | 12 | completed | 2013-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2013-10-03 |
±—±----------±----------±--------±--------------------±-----------+
Users 表：
±---------±-------±-------+
| Users_Id | Banned | Role |
±---------±-------±-------+
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
±---------±-------±-------+
Result 表：
±-----------±------------------+
| Day | Cancellation Rate |
±-----------±------------------+
| 2013-10-01 | 0.33 |
| 2013-10-02 | 0.00 |
| 2013-10-03 | 0.50 |
±-----------±------------------+

二、题目难度：困难
三、题解
1.使用t.Client_Id = u1.Users_Id and u1.Banned = 'No’作为条件进行多表连接，找到乘客和司机都未被禁止的订单
2.使用where进行时间判断
3.使用group by对时间进行分组
4.按要求计算取消率：ROUND四舍五入保留两位小数
SUM中使用IF语句来判断
COUNT计算总的订单数

# Write your MySQL query statement below
select
Request_at Day,
ROUND(
    SUM(
        IF(Status='completed',0,1)
    )/COUNT(*),
    2
) 'Cancellation Rate'
FROM 
Trips t join Users u1 on t.Client_Id = u1.Users_Id and u1.Banned = 'No'
join Users u2 on t.Driver_Id = u2.Users_Id and u2.Banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Request_at;

或者直接使用AVG

select
Request_at Day,
ROUND(AVG(Status!='completed'),2) 'Cancellation Rate'
FROM 
Trips t join Users u1 on t.Client_Id = u1.Users_Id and u1.Banned = 'No'
join Users u2 on t.Driver_Id = u2.Users_Id and u2.Banned = 'No'
WHERE t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY Request_at;

四、题目链接：https://leetcode-cn.com/problems/trips-and-users/