LeetCode:Trips and Users - 出租车接单取消率

原创
2015/08/23 20:42
阅读数 3.7K

1、题目名称

Trips and Users(出租车预约取消率)

2、题目地址

https://leetcode.com/problems/trips-and-users/

3、题目内容

表Trips内存储了出租车的接单数据,包括Id、Client_Id、Driver_Id、City_Id、Status、Request_at共计6列,其中Status列是个枚举列,包括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语句,查询非禁止客户(Users表中Banned列为No的客户)在2013年10月1日至2013年10月3日间的单据取消率,结果为四舍五入后的两位有效数字。

4、初始化数据库脚本

在MySQL数据库中建立一个名为LEETCODE的数据库,用MySQL命令行中的source命令执行下面脚本:

-- 执行脚本前必须建立名为LEETCODE的DATABASE
USE LEETCODE;
 
DROP TABLE IF EXISTS Trips;
CREATE TABLE Trips (
  Id INT NOT NULL PRIMARY KEY,
  Client_Id INT,
  Driver_Id INT,
  City_Id INT,
  Status ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'),
  Request_at DATE
);

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

DROP TABLE IF EXISTS Users;
CREATE TABLE Users (
  Users_Id INT NOT NULL PRIMARY KEY,
  Banned VARCHAR(5),
  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');

 

5、解题SQL1

我一开始想的办法比较复杂,第3-7行的SQL查出每日的未被禁止的客户提交的所有单据数量,第8-14行的SQL查出这些单据中以日为单位每日由未被禁止的客户取消的单据数量,在SELECT子句中计算取消率。

SELECT A.Request_at AS DAY,
       ROUND(IFNULL(B.CANCELLED / A.TOTAL, 0), 2) AS 'Cancellation Rate'
FROM   (SELECT COUNT(*) AS TOTAL, Request_at
        FROM   Trips T1, Users U1
        WHERE  T1.Client_Id = U1.Users_Id AND U1.Banned = 'No'
        GROUP  BY Request_at
        HAVING COUNT(*)) AS A
LEFT   JOIN (SELECT COUNT(*) AS CANCELLED, Request_at
             FROM   (SELECT DISTINCT T.Id, T.Request_at
                     FROM   Trips T, Users U
                     WHERE  T.Status <> 'completed' AND
                            T.Client_Id = U.Users_Id AND U.Banned = 'No') AS TMP
             GROUP  BY TMP.Request_at
             HAVING COUNT(*)) AS B ON A.Request_at = B.Request_at
WHERE  A.Request_at >= '2013-10-01' AND A.Request_at <= '2013-10-03';

6、解题SQL2

下面的SQL是一个比较简单的方法,通过 LEFT JOIN 跨表查询,并统计状态不为completed的出租车订单。

SELECT Request_at DAY,
       ROUND(SUM(IF(Status = 'completed', 0, 1)) / COUNT(*), 2) 'Cancellation Rate'
FROM   Trips t
LEFT   JOIN Users t1 ON t.Client_Id = t1.Users_Id
WHERE  t1.Banned = 'No' AND Request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP  BY t.Request_at;

END

展开阅读全文
打赏
2
4 收藏
分享
加载中
更多评论
打赏
0 评论
4 收藏
2
分享
返回顶部
顶部