Leetcode 262. Trips and Users 思路分析及代码分享

发表于2019年2月1日由admin

题目大意：

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      |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 则是一个表示（‘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日 至 2013年10月3日 期间非禁止用户的取消率。基于上表，你的 SQL 语句应返回如下结果，取消率（Cancellation Rate）保留两位小数。

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

如果想查看本题目是哪家公司的面试题，请参考以下免费链接： https://leetcode.jp/problemdetail.php?id=262

思路分析：

这是一道有关SQL文的题，本人长期做app开发，取数据全靠api，数据库或sql相关的知识几乎已经快忘光了哈。不过还是用我上古时期残留能力来试着分析下这道题吧。

表结构很简单，一个行程表，一个用户表，行程表里有乘客id和司机id，另外还包括了该行程的状态，成功或是被取消。最后还有一个行程时间。用户表更为简单，用户id，用户状态（被拉黑或未被拉黑）和用户角色（乘客或司机）。

题目要求检索出所有未被拉黑用户的每天行程取消率。问题拆解来看，其实有三个主要部分，

第一，是要每天的数据统计，那么肯定是要group by日期列。这个不难。这样数据库就会每个日期返回一条数据结果。

SELECT ...
FROM Trips
WHERE
...
GROUP BY Request_at // group by日期列

第二，要想知道取消率，就要分别知道该日期总取消的行程数量和总行程数量。相除就是取消率。取数量首选count函数，因为已经做了group by处理，这样再select count(列名)，就可以得到该日期内有多少条该列为非空数据的数量和。每天的总取消数和总行程数可以分别写为：

// 每天的总取消数
SELECT count(Status) as count_1, Request_at
FROM Trips
WHERE
Status != 'completed'
GROUP BY Request_at
// 每天的总行程数
SELECT count(Status) as count_all, Request_at
FROM Trips
GROUP BY Request_at

那么问题来了，如何将上面两个count相除呢？我们知道每个select语句都可以相当于一个临时表，那么我们将两个临时表按照日期列(Request_at) Join到一起就可以了。

SELECT 
  t2.count_1 / t1.count_all AS 'Cancellation Rate'
FROM

(SELECT count(Status) as count_all, Request_at
 FROM Trips
 GROUP BY Request_at) t1

LEFT JOIN

(SELECT count(Status) as count_1, Request_at
 FROM Trips
 WHERE
   Status != 'completed'
 GROUP BY Request_at) t2

ON t1.Request_at = t2.Request_at

第三，到此为止，基本的SQL文构架就出来了，剩下的就是一些细节问题需要注意，比如，数据查询范围是2013年10月1日 至 2013年10月3日 期间的非禁止用户。另外，取得取消数count(Status)时结果可能为空，这里需要用COALESCE做下处理，将NULL转为其他字符。最后还有一点，例子中给出的取消率都是保留的两位小数，所以我们在做除法之后，应对结果进行格式化： AS DECIMAL(3, 2)，3代表小数点前的位数，2则是小数点后保留的位数。

完整SQL文：

SELECT
    t1.`Request_at` AS 'Day',
    CAST(
        COALESCE(
            (t2.count_1 * 1.00 / t1.count_all),
            '0.00'
        ) AS DECIMAL(3, 2)
    ) AS 'Cancellation Rate'
FROM
    (
    SELECT
        COUNT(*),
        COUNT(A.Status) AS count_all,
        A.`Request_at`
    FROM
        Trips A
    LEFT JOIN Users B ON
        A.Client_Id = B.Users_Id AND B.Role = 'client'
    LEFT JOIN Users C ON
        A.Driver_Id = C.Users_Id AND C.Role = 'driver'
    WHERE
        B.Banned = 'NO' AND C.Banned = 'NO' AND A.Request_at >= '2013-10-01' AND A.Request_at <= '2013-10-03'
    GROUP BY
        A.`Request_at`
) t1
LEFT JOIN(
    SELECT
        COUNT(*),
        COUNT(A.Status) AS count_1,
        A.`Request_at`
    FROM
        Trips A
    LEFT JOIN Users B ON
        A.Client_Id = B.Users_Id AND B.Role = 'client'
    LEFT JOIN Users C ON
        A.Driver_Id = C.Users_Id AND C.Role = 'driver'
    WHERE
        B.Banned = 'NO' AND C.Banned = 'NO' AND A.Request_at >= '2013-10-01' AND A.Request_at <= '2013-10-03' AND A.Status != 'completed'
    GROUP BY
        A.`Request_at`
) t2
ON
    t1.Request_at = t2.Request_at

这个SQL文的执行时间为205毫秒。
Runtime: 205 ms, faster than 65.16% of MySQL online submissions for Trips and Users.

本网站文章均为原创内容，并可随意转载，但请标明本文链接
如有任何疑问可在文章底部留言。为了防止恶意评论，本博客现已开启留言审核功能。但是博主会在后台第一时间看到您的留言，并会在第一时间对您的留言进行回复！欢迎交流！
本文链接: http://leetcode.jp/leetcode-262-trips-and-users-思路分析及代码分享/
此条目发表在leetcode分类目录，贴了, , , 标签。将固定链接加入收藏夹。

发表评论

您的电子邮箱地址不会被公开。