LeetCode — 行程和用户

1.创建两张表

1.1 创建Trips表

Trips 表中包含所有出租车的行程信息。
每一行代表一段行程，有唯一行程id，乘驾人信息，及其他行程信息。
行程状态有三种，为枚举类型。

Drop table If Exists Trips;
Create table Trips(
Id int, 
Client_Id int, 
Driver_Id int, 
City_Id int, 
Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), 
Request_at varchar(50));

插入数据

insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');

插入数据后

1.2 Users表

Users 表包含所有用户。每个用户有唯一键 Users_Id。
Banned 表示这个用户是否被禁止。
Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。

Drop table If Exists Users;
Create table Users (
Users_Id int, 
Banned varchar(50), 
Role ENUM('client', 'driver', 'partner'));

插入数据

insert into Users (Users_Id, Banned, Role) values ('1', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('2', 'Yes', 'client');
insert into Users (Users_Id, Banned, Role) values ('3', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('4', 'No', 'client');
insert into Users (Users_Id, Banned, Role) values ('10', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('11', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('12', 'No', 'driver');
insert into Users (Users_Id, Banned, Role) values ('13', 'No', 'driver');

插入数据后

2. 问题描述

写一段 SQL 语句查出 2013年10月1日 至 2013年10月3日 期间非禁止用户的取消率。

基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

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

3. 问题分析

3.1 删除被禁止用户（包括被禁止司机和乘客）

两次join，第一次的目的是删除被禁止乘客，第二次是为了删除被禁止司机。
这是Users表在此题的唯一用处。

select *
from trips t
join users u1 on (t.Client_id=u1.Users_id and u1.banned='No');

select *
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');

从得到的结果可以看到两列Banned的值全是No。

3.2 取消率的计算

取消的对象是订单，是Trips表的行，所以我们先对Trips表的每一行做判断。
取消了的订单标记1（包括用户取消和司机取消），完成了的标记0。

SELECT Status != 'completed' FROM Trips;

对该列取平均值得到取消率，保留到小数点后两位

3.3 筛选日期范围

4.总结

我们先删除被禁止用户（包括被禁止司机和乘客），然后限制订单的时间，通过这两步得到了所有符合条件的订单。对每一笔订单做取消与否判断（包括被乘客取消，被司机取消）。按日期计算取消率，保留到小数点后两位，并修改字段名。

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  
     Request_at BETWEEN '2013-10-01' AND '2013-10-03'  # 限定订单日期
GROUP BY  
     Request_at;  #要算每一天的取消率,对日期分组

来源：力扣（LeetCode）
链接：https://leetcode-cn.com/problems/trips-and-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。