Leetcode 262. Trips and Users

262. Trips and Users

原题目链接:262. Trips and Users

这个题目没有官方提供的解法,本题主要参考原题的讨论部分以及以下链接的内容进行编写。

LeetCode 262. Trips and Users

Trips and Users解题思路

Trips表有所有出租车的行程数据。每一趟行程都有一个独一无二的Id,然后Client_Id和Driver_Id是Users表中User_Id的外键。Status是一个枚举(ENUM)类型数据,主要有以下几种数据:

  • completed
  • cancelled_by_driver
  • cancelled_by_client
+----+-----------+-----------+---------+--------------------+----------+
| 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,然后Role也是一个枚举类型,主要有以下几种数据:

  • client
  • driver
  • partner
+----------+--------+--------+
| 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 |
+----------+--------+--------+

写一个SQL查询语句找出在2013年10月1日到3日之间由开放用户要求的订单取消率。对于上述的表格,你的SQL语句应该返回以下数据,其中订单取消率圆整到两位小数点。

+------------+-------------------+
|     Day    | Cancellation Rate |
+------------+-------------------+
| 2013-10-01 |       0.33        |
| 2013-10-02 |       0.00        |
| 2013-10-03 |       0.50        |
+------------+-------------------+

Solution

方法:综合采用子查询,CASE WHENWHEREBETWEENGROUP BYJOIN语句以及COUNT()SUM()ROUND函数[Accepted]

Algorithm

对于被取消的概率,我们需要计算两个数据:1,每天开放用户的总请求数;2,每天开放用户的被取消的请求数,然后相除就可以得到这个结果。

首先一定要每天的数据使用GROUP BY进行分组,对于分组好的数据分别用count(*)计算总请求数,SUM()函数与CASE/WHEN语句计算被取消的请求数。

下面是一个版本的写法:

MySQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT 
request_at AS Day,
round(
sum(
CASE WHEN -- 这里临时生成了一个字段,由status字段决定 若是完成字段值为0 反之为1 也就是是否被取消
status="completed" THEN 0
ELSE 1
END ) -- 对这个字段使用sum函数即可求得被取消了的个数
/count(*), 2) -- 除以总个数后四舍五入2位即为被取消的概率
AS "Cancellation Rate"
FROM
(SELECT * FROM Trips WHERE client_id NOT IN -- 这里要把被 BAN 的乘客过滤掉
(SELECT users_id FROM Users WHERE banned = "yes" AND role = "client") -- 找出被BAN的乘客
AND request_at BETWEEN "2013-10-01" AND "2013-10-03" -- 限定日期
) AS t
GROUP BY request_at -- 对日期分组
ORDER BY request_at;

也可以进行一些优化,通过内链筛选条件去掉一些被禁止的乘客,比起先查下被禁止乘客集合再使用NOT IN进行筛选要来得快。优化以后的版本如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT 
request_at AS Day,
round(
sum(
CASE WHEN -- 这里临时生成了一个字段,由status字段决定 若是完成字段值为0 反之为1 也就是是否被取消
status="completed" THEN 0
ELSE 1
END ) -- 对这个字段使用sum函数即可求得被取消了的个数
/count(*), 2) -- 除以总个数后四舍五入2位即为被取消的概率
AS "Cancellation Rate"
FROM
Trips t INNER JOIN Users u ON t.Client_Id = u.Users_Id and u.Banned='No' -- 限定为非ban用户
WHERE request_at BETWEEN "2013-10-01" AND "2013-10-03" -- 限定日期
GROUP BY request_at -- 对日期分组
ORDER BY request_at;