262.行程和用户（数据库）

题目

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

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

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

示例

表：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’) 。

解题

Trips 表：	Users 表：	输出结果

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

方法一

SELECT
    Request_at 'Day', ROUND(AVG(t.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 '2022-02-01' AND '2022-02-03'
GROUP BY 
    Request_at

avg(t.status!=‘completed’) 中 t.status!=‘completed’ 是布尔判断：
当 t.status 的值不等于 completed 时（即被顾客或司机取消的订单），判断结果为True，也就得到值1；当 t.status 的值等于 completed 时，得到值0；也就是说，每条订单记录都会得到一个 0 或者 1 。 （t.status!=‘completed’ 等价于这个 IF(t.status!=‘completed’,1,0) ，这里是简写了）
avg 背后的含义是：所有订单值的和/所有订单的数量。因为不符合题目条件的订单的值是 0 求和相当于把值的和转化成了数量的和，所以 avg 得到的是：满足条件的订单数量/所有订单的数量，即取消率。

方法二

SELECT
    t.request_at DAY, 
    (ROUND(COUNT(IF(t.status != 'completed', t.status, NULL)) / COUNT(t.status), 2)) AS '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 '2022-02-01' AND '2022-02-03'
GROUP BY 
    request_at

方法三

SELECT 
	request_at AS "Day", ROUND(SUM(t.status != "completed")/COUNT(request_at),2) AS 'Cancellation Rate'
FROM Trips T 
	INNER JOIN Users U ON T.client_id = U.users_id
	INNER JOIN Users U2 ON T.driver_id = U2.users_id
WHERE 
	U.banned != "Yes" AND U2.banned != "Yes" AND Request_at BETWEEN '2022-02-01' AND '2022-02-03'
GROUP BY 
	request_at

方法四

SELECT 
	request_at AS DAY, ROUND(SUM(IF(t.status!='completed',1,0))/COUNT(1),2) 'Cancellation Rate'
FROM 
	trips t
WHERE 
	client_id IN (SELECT users_id FROM users WHERE banned='no') AND driver_id IN (SELECT users_id FROM users WHERE banned='no')
GROUP BY 
	1 
HAVING 
	request_at BETWEEN '2022-02-01' AND '2022-02-03'

总结

SELECT
   t.*, u1.*, u2.* 
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 '2022-02-01' AND '2022-02-03'

SELECT 
	t.* 
FROM 
	trips t
WHERE 
	client_id IN (SELECT users_id FROM users WHERE banned='no') AND 
	driver_id IN (SELECT users_id FROM users WHERE banned='no') AND 
	request_at BETWEEN '2022-02-01' AND '2022-02-03'

无论是用 JOIN ON 连接还是用 IN 将非静止用户找出来，这都不是难点。难点是接下来如何统计出：被取消的订单数和订单的总数。
四种方法都计算出了取消率，尤其第一种用的AVG函数实在是惊艳得一批。。

就比如计算班级的及格率可以这么玩：
SELECT class,SUM(score>=60)/COUNT(score) FROM temp GROUP BY class
SELECT class,AVG(score>=60) FROM temp GROUP BY class

---

对力扣里的讨论做了简单整理。搬运自： [https://leetcode-cn.com/problems/trips-and-users/comments/](https://leetcode-cn.com/problems/trips-and-users/comments/)