leecode 数据库：262. 行程和用户

已于 2023-05-16 07:53:54 修改
阅读量1.3k 收藏 3
点赞数 3
分类专栏： leecode数据库 文章标签： 数据库 sql mysql 大数据 数据分析
于 2023-05-04 08:36:07 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Clittle225/article/details/130479906
版权

导入数据：

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

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

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

写一段 SQL 语句查出 "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

解题思路；
统计每天非禁止用户的取消率，需要知道非禁止用户有哪些，总行程数，取消的行程数。

解法一


首先确定被禁止用户的行程记录，再剔除这些行程记录。

行程表中，字段 client_id 和 driver_id，都与用户表中的 users_id 关联。因此只要 client_id 和 driver_id 中有一个被禁止了，此条行程记录要被剔除。

先说一种错误的找出没被禁止用户行程记录的方法。此方法很有迷惑性。

思路：

if (client_id = users_id 或 driver_id = users_id) 且 users_id没有被禁止
{
    此条记录没被禁止。
}

SQL 代码

SELECT *
FROM Trips AS T JOIN Users AS U 
ON (T.client_id = U.users_id  OR T.driver_id = U.users_id )  AND U.banned ='No'

乍一看，思路是对。其实是错误的。因为，我们不知觉得肯定了一个假设—— client_id 与 driver_id 是相同的。只有当两者相同时，才能用此条件排除被禁止用户的行程记录。

错误的结果：

+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
| Id   | Client_Id | Driver_Id | City_Id | STATUS              | Request_at | Users_Id | Banned | Role   |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+
|    1 |         1 |        10 |       1 | completed           | 2013-10-01 |        1 | No     | client |
|    1 |         1 |        10 |       1 | completed           | 2013-10-01 |       10 | No     | driver |
|    2 |         2 |        11 |       1 | cancelled_by_driver | 2013-10-01 |       11 | No     | driver |
|    3 |         3 |        12 |       6 | completed           | 2013-10-01 |        3 | No     | client |
|    3 |         3 |        12 |       6 | completed           | 2013-10-01 |       12 | No     | driver |
|    4 |         4 |        13 |       6 | cancelled_by_client | 2013-10-01 |        4 | No     | client |
|    4 |         4 |        13 |       6 | cancelled_by_client | 2013-10-01 |       13 | No     | driver |
|    5 |         1 |        10 |       1 | completed           | 2013-10-02 |        1 | No     | client |
|    5 |         1 |        10 |       1 | completed           | 2013-10-02 |       10 | No     | driver |
|    6 |         2 |        11 |       6 | completed           | 2013-10-02 |       11 | No     | driver |
|    7 |         3 |        12 |       6 | completed           | 2013-10-02 |        3 | No     | client |
|    7 |         3 |        12 |       6 | completed           | 2013-10-02 |       12 | No     | driver |
|    8 |         2 |        12 |      12 | completed           | 2013-10-03 |       12 | No     | driver |
|    9 |         3 |        10 |      12 | completed           | 2013-10-03 |        3 | No     | client |
|    9 |         3 |        10 |      12 | completed           | 2013-10-03 |       10 | No     | driver |
|   10 |         4 |        13 |      12 | cancelled_by_driver | 2013-10-03 |        4 | No     | client |
|   10 |         4 |        13 |      12 | cancelled_by_driver | 2013-10-03 |       13 | No     | driver |
+------+-----------+-----------+---------+---------------------+------------+----------+--------+--------+

结果中，被禁止的 users_id = 2，其行程记录没被剔除掉。

明显， client_id 与 driver_id 不一定相同 。

正确的做法是对 client_id 和 driver_id 各自关联的 users_id，同时检测是否被禁止。

if (client_id = users_id_1 且 users_id_1没被禁止 并且 client_id = users_id_2 且 users_id_2没被禁止){
    此条记录没被禁止。
}

SQL 代码：

SELECT *
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')

在此基础上，按日期分组，统计每组的 总行程数，取消的行程数 。

每组的总行程数：COUNT(T.STATUS)。

每组的取消的行程数：

SUM(
	IF(T.STATUS = 'completed',0,1)
)

取消率 = 每组的取消的行程数 / 每组的总行程数

完整逻辑为:

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM Trips AS T
JOIN Users AS U1 ON (T.client_id = U1.users_id AND U1.banned ='No')
JOIN Users AS U2 ON (T.driver_id = U2.users_id AND U2.banned ='No')
WHERE T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

其中 SUM 求和函数，COUNT 计数函数，ROUND 四舍五入函数。

解法二

思路与解法一相同。而采用不同的方法排除掉被禁止用户的行程记录。想到排除，就联想到集合差。

client_id 和 driver_id 的全部为集合 U。被禁止的 users_id 集合为 A。

U 减去 A 的结果为没被禁止的用户。

(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A

 好了，先演示一个错误的解法：

行程表连接表 A，排除掉被被禁止的行程。

SELECT *
FROM trips AS T,
(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id)

剩下的逻辑与解法一后部分相同，完善后的逻辑为：

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM trips AS T,
(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A
WHERE (T.Client_Id != A.users_id AND T.Driver_Id != A.users_id) AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

很可惜，当表 A 为空时，此方法的结果是空表。但是表 A 为空，可能是有用户但是没有被禁止的用户。因此方法是错误的。

正确的解法是：行程表 left join 表 A 两次，A.users_id 都为 NULL 的行都是没被排除的行。

SELECT *
FROM trips AS T LEFT JOIN 
(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL

补上其它部分的逻辑为：

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM trips AS T LEFT JOIN 
(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A ON (T.Client_Id = A.users_id)
LEFT JOIN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
) AS A1
ON (T.Driver_Id = A1.users_id)
WHERE A.users_id IS NULL AND A1.users_id IS NULL AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

解法三

与解法二思路相同。找出被禁止的用户后，不再连接行程表和用户表，直接从行程表中排除掉被被禁止用户的行程记录。

被禁止的用户用子查询：

(
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)

行程表中 client_id 和 driver_id 都在此子查询结果中的行要剔除掉。

SELECT *
FROM trips AS T
WHERE 
T.Client_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)

补上其它部分：

SELECT T.request_at AS `Day`, 
	ROUND(
			SUM(
				IF(T.STATUS = 'completed',0,1)
			)
			/ 
			COUNT(T.STATUS),
			2
	) AS `Cancellation Rate`
FROM trips AS T
WHERE 
T.Client_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)
AND
T.Driver_Id NOT IN (
	SELECT users_id
	FROM users
	WHERE banned = 'Yes'
)
AND T.request_at BETWEEN '2013-10-01' AND '2013-10-03'
GROUP BY T.request_at

结语：希望大家多多指正。

作者：jason-2
链接：https://leetcode.cn/problems/trips-and-users/solution/san-chong-jie-fa-cong-nan-dao-yi-zong-you-gua-he-n/
来源：力扣（LeetCode）
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。

来源：力扣（LeetCode）
链接：https://leetcode.cn/problems/trips-and-users
著作权归领扣网络所有。商业转载请联系官方授权，非商业转载请注明出处。

小布先生~噫嘘唏
关注
  • 3
    点赞
  • 踩
  • 3
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
262.行程和用户
zrh_CSDN的博客
11-04 335
SQL架构 Trips 表中存所有出租车的行程信息。每段行程有唯一键 Id，Client_Id 和 Driver_Id 是 Users 表中 Users_Id 的外键。Status 是枚举类型，枚举成员为 (‘completed’, ‘cancelled_by_driver’, ‘cancelled_by_client’)。 +----+-----------+-----------+---...
ReviewLeeCode:查看LeeCode ..
04-12
Review LeeCode... GFMA 对应的是(Google,Facebook,Microsoft,Amazon) 计算机中的0 在计算机中，实数在计算和存储过程中会有一些微小的误差，对于一些和0做比较的语句来讲，有时候会因为误差而 导致原本是等于零但...
leecode-数据库题（全）.pdf
03-17
leecode-数据库题（全）.pdf
Leecode：Leecode的代码和注释
02-11
【标题】"Leecode：Leecode的代码和注释" 涉及到的知识点主要集中在使用Python编程语言解决LeetCode上的算法问题。LeetCode是一个在线平台，提供了大量的编程挑战，旨在帮助开发者提高其编程技能，尤其是解决算法...
leecode:自己的leecode练习
07-05
【标题】"leecode:自己的leecode练习"指的是用户在进行LeetCode这个在线编程平台的个人训练，LeetCode是一个流行的网站，提供各种算法题目，帮助程序员提升编程技巧和解决实际问题的能力，尤其在面试准备中十分受...
leecode:leecode刷题用
04-06
【描述】"leecode刷题" 暗示了用户正在通过练习LeetCode上的编程题目来提高自己的编程技能。刷题通常是为了熟悉常见的数据结构和算法，加强问题解决能力和逻辑思维能力，这对于面试和日常工作中的问题解决至关重要。...
简单的 mongoDB 学习
qq_19342829的博客
07-31 564
mongodb入门学习整理
社区养老服务小程序的设计
Karen198的博客
07-31 680
管理员账户功能包括：系统首页，个人中心，用户管理，服务人员管理，服务产品管理，服务预约管理，服务状态管理，服务退订管理，活动管理，视频管理。主要技术：Java,Spring,mybatis,mysql,jquery,html。服务器：SpringBoot自带 apache tomcat。微信端账号功能包括：系统首页，服务产品，活动，视频，我的。JDK版本：Java JDK1.8。数据库可视化工具： navicat。数据库版本： mysql5.7。开发工具：IDEA(推荐)开发系统：Windows。
用 Bytebase 实现批量、多环境、多租户数据库的丝滑变更
Bytebase的博客
08-02 1127
现在，你已学会如何使用部署配置和数据库分组在 Bytebase 中运行批量更改。动手试试吧！💡 更多资讯，请关注 Bytebase 公号：Bytebase。
MySQL备份与恢复
m0_74860678的博客
08-01 624
在生产环境中，数据的安全性至关重要任何数据的丢失都可能产生严重的后果造成数据丢失的原因程序错误人为操作错误运算错误磁盘故障灾难（如火灾、地震）和盗窃# 开启二进制日志功能[mysqld]# 重启服务# 有 mysql-bin.000001 就代表成功。
杂谈 | windows批处理脚本 | 配置项目环境变量
zwcslj的博客
07-31 1277
命令回显功能是指在运行命令或脚本时，系统将命令的执行过程和结果显示在屏幕上，使用户能够实时地看到命令的执行情况。通常，当命令回显功能打开时，在执行每一步命令时，系统将会显示执行的命令本身以及对应的结果，从而帮助用户了解命令的执行过程，排查可能出现的问题。关闭回显时，命令提示符不会出现在“命令提示符”窗口中。若要再次显示命令提示符，请键入 echo on。
MHA高可用配置及故障切换
m0_70627741的博客
08-02 1410
HMA（MasterHigh Availablity）是一套优秀的Mysql高可用环境下的故障切换和主从复制的软件解决MySLQ单点的问题MySQL故障切换过程中，MHA能做到0-30秒内自动完成故障切换操作。故障切换的过程中最大程度上保证数据的一致性，以达到真正意义上的高可用。MHA是建在主从复制的基础上的；0-30秒自动完成故障切换是MHA的特性。
使用MySQLdump定时备份数据库实战
ithongchou的博客
08-03 79
手动执行脚本文件，并查看结果，检验脚本没有问题，因为脚本第一行已经指定脚本执行器，所以这里执行不需要使用。工具来设置定时任务。以下是一个简单的例子，展示如何每天凌晨。然后，你需要设置一个cron任务来定时执行这个脚本。确定系统定时任务已经开始，查看系统定时任务是否已经开启。上编辑的脚本格式在·Linux·平台不兼容问题。首先，你需要创建一个备份脚本。进行数据库的定时备份，你可以使用。保存这个脚本到一个文件，比如。
实战：MySQL数据同步神器之Canal
为无为，事无事，味无味。
08-02 1034
canal [kə'næl]，译意为水道/管道/沟渠，主要用途是基于 MySQL 数据库增量日志解析，提供增量数据订阅和消费。项目起源于阿里巴巴内部对于跨机房数据同步的需求，通过解析MySQL的二进制日志（Binary Log），Canal能够捕获并推送数据库的变更事件，满足了诸如数据库镜像、实时备份、索引实时维护等多种业务场景的需求。GitHub - alibaba/canal: 阿里巴巴 MySQL binlog 增量订阅&消费组件。
Switchover和Failover切换方案(实战)
一个只求实际的程序猿
08-02 784
-检查主库下switchover_status字段,如果值为"session active"或者"to standby", 则主数据库角色可以切换为备库角色。=======================检查=================================--两个节点各执行2次。2.--备库切换为主库(切换可在1节点执行,启停需所有集群节点都执行)3.--主库切换为备库(切换1节点执行,启停需所有集群节点都执行)4.--获取主、备库上的信息以及保护模式(主备库执行)
Qt拖拽事件详解及代码实现
最新发布
Isana_Yashiro的博客
08-05 552
qt拖拽事件是一项非常常用并且非常好用的功能，拖拽实际上是一种信息传递的载体，其目的是将信息从一个对象传递给另一个对象。通过拖拽可以简化文件打开或业务操作流程，qt初学者学会拖拽事件后就可以马上开发出许多有趣的项目，本文以一个简单的示例来介绍拖拽事件功能。DragDropWidget类实现了拖拽发起对话框，主要是重写了鼠标事件函数，用于设置拖拽搭载的信息，这里就是一个字符串，实际上也可以是自定义的类或者其他对象。代码详见dragdropwidget.h及dragdropwidget.cpp。
接口测试学习笔记1
weixin_53666393的博客
08-03 875
同时也像白盒测试那样，检查程序的代码，但这种检查不像白盒测试那样详细、完整，一般只对程序模块之间的调用、核心代码进行检查，在集成测试时经常采用，一般也是程序员大量采用。现在企业说的接口测试，一般指的是Web接口测试或Http接口测试，主要指的是基于B/S架构的接口测试（B/S架构主要基于HTTP 超文本传输协议，所以也叫HTTP接口测试）对请求对象（网页、文件、图片...）进行的操作，就是一些命令，程序员在编程的时候使用，在接口文档中会指明，测试人员测试接口的时候，按照说明选择正确的方法。
linux安装weblogic
m0_46400195的博客
08-05 482
域文件位置就是python文件中设置的，位置（cd /root/web/weblogic/softWare/projects/doamins/testDomain）（以自己python为主）想要启动weblogic 12版本及以后都需要去oracle_common目录下配合启动脚本进行服务器启动。weblogic不允许root用户进行安装，所以要提前创建weblogic用户来进行安装。例如：http://192.168.0.143:7001/console。用户名：启动weblogic的用户名。
简单的docker学习 第8章 docker常用服务安装
qq_19342829的博客
08-05 984
b站docker学习笔记
【redis 第一篇章】认识一下
Tsing
08-01 371
1、定义：泛指非关系性数据库。2、特点：方便扩展，数据之间没有关系；大数据量高性能；数据类型多样性。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值