大数据第40天—Mysql练习题11-行程和用户-杨大伟
需求：写一段 SQL 语句查出 2019年10月1日 至 2019年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。
取消率的计算方式如下：(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
Trips表：所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。
| Id | Client_Id | Driver_Id | City_Id | Status | Request_at |
|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 | completed | 2019-10-01 |
| 2 | 2 | 11 | 1 | cancelled_by_driver | 2019-10-01 |
| 3 | 3 | 12 | 6 | completed | 2019-10-01 |
| 4 | 4 | 13 | 6 | cancelled_by_client | 2019-10-01 |
| 5 | 1 | 10 | 1 | completed | 2019-10-02 |
| 6 | 2 | 11 | 6 | completed | 2019-10-02 |
| 7 | 3 | 12 | 6 | completed | 2019-10-02 |
| 8 | 2 | 12 | 12 | completed | 2019-10-03 |
| 9 | 3 | 10 | 12 | completed | 2019-10-03 |
| 10 | 4 | 13 | 12 | cancelled_by_driver | 2019-10-03 |
Users 表存所有用户。每个用户有唯一键 Users_Id。Banned 表示这个用户是否被禁止，Role 则是一个表示（‘client’, ‘driver’, ‘partner’）的枚举类型。
| Users_Id | Banned | Cancellation Rate |
|---|---|---|
| 1 | No | client |
| 2 | Yes | client |
| 3 | No | client |
| 4 | No | client |
| 10 | No | driver |
| 11 | No | driver |
| 12 | No | driver |
| 13 | No | driver |
展示效果：
| Day | Cancellation Rate |
|---|---|
| 2019-10-01 | 0.33 |
| 2019-10-02 | 0.00 |
| 2019-10-03 | 0.50 |
1 Create table If Not Exists 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)); 2 3 Create table If Not Exists Users (Users_Id int, Banned varchar(50), Role ENUM('client', 'driver', 'partner')); 4 5 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (1, 1, 10, 1, 'completed', '2019-10-01'); 6 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (2, 2, 11, 1, 'cancelled_by_driver', '2019-10-01'); 7 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (3, 3, 12, 6, 'completed', '2019-10-01'); 8 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (4, 4, 13, 6, 'cancelled_by_client', '2019-10-01'); 9 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (5, 1, 10, 1, 'completed', '2019-10-02'); 10 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (6, 2, 11, 6, 'completed', '2019-10-02'); 11 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (7, 3, 12, 6, 'completed', '2019-10-02'); 12 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (8, 2, 12, 12, 'completed', '2019-10-03'); 13 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (9, 3, 10, 12, 'completed', '2019-10-03'); 14 insert into Trips (Id, Client_Id, Driver_Id, City_Id, Status, Request_at) values (10, 4, 13, 12, 'cancelled_by_driver', '2019-10-03'); 15 16 insert into Users (Users_Id, Banned, Role) values (1, 'No', 'client'); 17 insert into Users (Users_Id, Banned, Role) values (2, 'Yes', 'client'); 18 insert into Users (Users_Id, Banned, Role) values (3, 'No', 'client'); 19 insert into Users (Users_Id, Banned, Role) values (4, 'No', 'client'); 20 insert into Users (Users_Id, Banned, Role) values (10, 'No', 'driver'); 21 insert into Users (Users_Id, Banned, Role) values (11, 'No', 'driver'); 22 insert into Users (Users_Id, Banned, Role) values (12, 'No', 'driver'); 23 insert into Users (Users_Id, Banned, Role) values (13, 'No', 'driver');
最终SQL:
1 方法一： 2 SELECT 3 T.request_at AS `Day`, 4 ROUND( 5 SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS), 6 2 7 ) AS `Cancellation Rate` 8 FROM 9 Trips AS T 10 JOIN 11 Users AS U1 12 ON 13 T.client_id = U1.users_id AND U1.banned ='No' 14 JOIN 15 Users AS U2 16 ON 17 T.driver_id = U2.users_id AND U2.banned ='No' 18 WHERE 19 T.request_at BETWEEN '2019-10-01' AND '2019-10-03' 20 GROUP BY 21 T.request_at; 22 23 方法二： 24 SELECT 25 T.request_at AS `Day`, 26 ROUND( 27 SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS), 28 2 29 ) AS `Cancellation Rate` 30 FROM 31 trips AS T 32 LEFT JOIN ( 33 SELECT 34 users_id 35 FROM 36 users 37 WHERE 38 banned = 'Yes' 39 ) AS A 40 ON 41 T.Client_Id = A.users_id 42 LEFT JOIN ( 43 SELECT 44 users_id 45 FROM 46 users 47 WHERE 48 banned = 'Yes' 49 ) AS A1 50 ON 51 T.Driver_Id = A1.users_id 52 WHERE 53 A.users_id IS NULL 54 AND A1.users_id IS NULL 55 AND T.request_at BETWEEN '2019-10-01' AND '2019-10-03' 56 GROUP BY 57 T.request_at; 58 59 方法三： 60 SELECT 61 T.request_at AS `Day`, 62 ROUND( 63 SUM(IF(T.STATUS = 'completed',0,1))/ COUNT(T.STATUS), 64 2 65 ) AS `Cancellation Rate` 66 FROM 67 trips AS T 68 WHERE 69 T.Client_Id NOT IN ( 70 SELECT 71 users_id 72 FROM 73 users 74 WHERE 75 banned = 'Yes' 76 ) 77 AND 78 T.Driver_Id NOT IN ( 79 SELECT 80 users_id 81 FROM 82 users 83 WHERE 84 banned = 'Yes' 85 ) 86 AND 87 T.request_at BETWEEN '2019-10-01' AND '2019-10-03';
