LeetCode_sql_day15（262.行程与用户）

描述：262. 行程和用户 - 力扣（LeetCode）

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

编写解决方案找出 "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 |
+----------+--------+--------+
输出：
+------------+-------------------+
| Day        | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 | 0.33              |
| 2013-10-02 | 0.00              |
| 2013-10-03 | 0.50              |
+------------+-------------------+

解释：
2013-10-01：
  - 共有 4 条请求，其中 2 条取消。
  - 然而，id=2 的请求是由禁止用户（user_id=2）发出的，所以计算时应当忽略它。
  - 因此，总共有 3 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 3) = 0.33
2013-10-02：
  - 共有 3 条请求，其中 0 条取消。
  - 然而，id=6 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 0 条取消。
  - 取消率为 (0 / 2) = 0.00
2013-10-03：
  - 共有 3 条请求，其中 1 条取消。
  - 然而，id=8 的请求是由禁止用户发出的，所以计算时应当忽略它。
  - 因此，总共有 2 条非禁止请求参与计算，其中 1 条取消。
  - 取消率为 (1 / 2) = 0.50

数据准备：

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))

Create table If Not Exists Users (users_id int, banned varchar(50), role ENUM('client', 'driver', 'partner'))

Truncate table Trips

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')

Truncate table Users

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')

分析：

①观察trip表需要client_id,driver_id，而这两个id在users表中，不妨分解一下users表

分解为只有client_id 数据的表t1 和只有driver_id数据的表t2

select * from users where role = 'client'

select * from users where role = 'driver'

②连接三张表，并进行相应的筛选，找到clent和driver的banned都为no的，以及相应日期的数据

select request_at,
       t1.users_id uid,
       t1.banned t1b,
       t2.users_id did,
       t2.banned t2b,
       status
from (select * from users where role = 'client') t1,
     (select * from users where role = 'driver') t2,
     trips
where Trips.client_id = t1.users_id
  and Trips.driver_id = t2.users_id
  and request_at between '2013-10-01' and '2013-10-03'
  and t1.banned = 'no'
  and t2.banned = 'no'

③然后通过对日期分类求出取消率

select request_at,
       round((count(if(status !='completed',1,null))/(count(1)) ),2)as 'Cancellation Rate'
       from t1
group by request_at;

代码：

with t1 as (
select request_at,
       t1.users_id uid,
       t1.banned t1b,
       t2.users_id did,
       t2.banned t2b,
       status
from (select * from users where role = 'client') t1,
     (select * from users where role = 'driver') t2,
     trips
where Trips.client_id = t1.users_id
  and Trips.driver_id = t2.users_id
  and request_at between '2013-10-01' and '2013-10-03'
  and t1.banned = 'no'
  and t2.banned = 'no')
select request_at,
       round((count(if(status !='completed',1,null))/(count(1)) ),2)as 'Cancellation Rate'
       from t1
group by request_at;

总结：

分解users表之后再进行连接会使题目迎刃而解