262.行程与用户（如何计算每日/3日滚动/5日滚动订单取消率

标签：01 滚动 No driver 行程 262 client 2023 id

1.题目

你需要编写一个 SQL 解决方案，计算 2013-10-01 至 2013-10-03 期间，非禁止用户的取消率。非禁止用户是指 banned 字段为 'No' 的用户。取消率的计算需要满足以下条件：

乘客和司机都必须未被禁止，即他们的 banned 字段值均为 'No'。
取消率的计算公式为：取消率 (Cancellation Rate)=(被司机或乘客取消的非禁止用户生成的订单数量) / (非禁止用户生成的订单总数)
取消率的结果需要四舍五入保留两位小数。
数据从 Trips 表和 Users 表获取。Trips 表记录了订单的详情，包括乘客、司机、状态和日期；Users 表记录了用户信息，包含用户是否被禁止 (banned) 和角色 (role1)，其中乘客的 client_id 和司机的 driver_id 都是从 Users 表的 users_id 字段中查找的。

2.数据准备

Trips 表：
- id：订单编号。
- client_id：客户（乘客）的 ID，对应 Users 表中的 users_id。
- driver_id：司机的 ID，对应 Users 表中的 users_id。
- city_id：城市 ID。
- status1：订单状态，可能的取值为：
  - 'completed'：订单已完成。
  - 'cancelled_by_driver'：订单被司机取消。
  - 'cancelled_by_client'：订单被乘客取消。
- request_at：订单日期。
Users 表：
- users_id：用户编号。
- banned：用户是否被禁止，取值可能为 'Yes' 或 'No'。
- role1：用户角色，可能的取值为 'client'（乘客）、'driver'（司机）和 'partner'。

Create table If Not Exists Trips (id int, client_id int, driver_id int, city_id int, status1 ENUM('completed', 'cancelled_by_driver', 'cancelled_by_client'), request_at varchar(50));
Create table If Not Exists Users (users_id int, banned varchar(50), role1 ENUM('client', 'driver', 'partner'));
Truncate table Trips;
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('1', '1', '10', '1', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('2', '2', '11', '1', 'cancelled_by_driver', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('3', '3', '12', '6', 'completed', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('4', '4', '13', '6', 'cancelled_by_client', '2013-10-01');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('5', '1', '10', '1', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('6', '2', '11', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('7', '3', '12', '6', 'completed', '2013-10-02');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('8', '2', '12', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('9', '3', '10', '12', 'completed', '2013-10-03');
insert into Trips (id, client_id, driver_id, city_id, status1, request_at) values ('10', '4', '13', '12', 'cancelled_by_driver', '2013-10-03');
Truncate table Users;
insert into Users (users_id, banned, role1) values ('1', 'No', 'client');
insert into Users (users_id, banned, role1) values ('2', 'Yes', 'client');
insert into Users (users_id, banned, role1) values ('3', 'No', 'client');
insert into Users (users_id, banned, role1) values ('4', 'No', 'client');
insert into Users (users_id, banned, role1) values ('10', 'No', 'driver');
insert into Users (users_id, banned, role1) values ('11', 'No', 'driver');
insert into Users (users_id, banned, role1) values ('12', 'No', 'driver');
insert into Users (users_id, banned, role1) values ('13', 'No', 'driver');

3.基础版本-解答1`LEETCODE 550ms 击败 60.14%`

第一步：client_info 和 driver_info 表

分别创建了 client_info 和 driver_info 两个 CTE，用于获取每个客户和司机的 banned 状态。具体步骤：
- client_info：从 Trips 表中提取所有独立的客户 ID (client_id)，并通过 LEFT JOIN Users 表获取这些客户的 banned 状态。
- driver_info：类似地，从 Trips 表中提取所有独立的司机 ID (driver_id)，并通过 LEFT JOIN Users 表获取司机的 banned 状态。
这两个 CTE 的作用：分别为客户和司机关联其在 Users 表中的状态。
第二步：trips_info 表

通过 LEFT JOIN 将 client_info 和 driver_info 的结果合并到 Trips 表中，获取每个订单对应的客户和司机的 banned 状态，并且过滤掉客户或司机被禁止的订单 (ci.banned = "NO" 和 di.banned = "NO")。

这个 CTE 的作用：得到只包含未被禁止的客户和司机生成的有效订单记录。
第三步：cancelled_amount 表

在 cancelled_amount CTE 中，统计了每一天被取消的订单数量。你通过 PARTITION BY request_at 和 COUNT(*) 计算每个 request_at（即订单日期）的取消订单数，过滤掉已完成的订单 (status <> 'completed')。
第四步：ttl_amount 表

通过类似的方式，在 ttl_amount CTE 中统计每一天的总订单数量。
第五步：计算取消率

通过主查询，先将 ttl_amount 和 cancelled_amount 按 request_at 进行 LEFT JOIN，计算每天的取消率 (cancelled_amount.ct / ttl_amount.ct)，并通过 ROUND 函数四舍五入保留两位小数。最后通过 GROUP BY 获取每天的取消率。

WITH client_info AS(
	SELECT DISTINCT t1.client_id
	,t2.banned
	FROM Trips t1
	LEFT JOIN Users t2 ON t1.client_id = t2.users_id
),
driver_info AS (
	SELECT DISTINCT t1.driver_id
	,t2.banned
	FROM Trips t1
	LEFT JOIN Users t2 ON t1.driver_id = t2.users_id
),
trips_info AS(
	SELECT id
	,t3.client_id
	,ci.banned as ci_banned
	,t3.driver_id
	,di.banned as di_banned
	,t3.status
	,t3.request_at
	FROM Trips t3
	LEFT JOIN client_info ci ON t3.client_id = ci.client_id
	LEFT JOIN driver_info di ON t3.driver_id = di.driver_id
	WHERE ci.banned  = "NO" AND di.banned = "NO"
),
cancelled_amount as(
	SELECT request_at
	,count(*)over(partition by request_at) as ct
	from trips_info
	WHERE  request_at BETWEEN "2013-10-01" AND "2013-10-03"
	AND status <>"completed"
),
ttl_amount as (
	SELECT request_at
	,count(*)over(partition by request_at) as ct
	from trips_info
	WHERE  request_at BETWEEN "2013-10-01" AND "2013-10-03"
)
SELECT t5.request_at as 'Day'
,MAX(t5.cr) as "Cancellation Rate"
FROM(
	SELECT  ttl_amount.request_at 
	,ROUND(IFNULL(cancelled_amount.ct/ttl_amount.ct,0),2) as cr
	FROM ttl_amount
	LEFT JOIN cancelled_amount ON cancelled_amount.request_at =ttl_amount.request_at
)t5
GROUP BY t5.request_at

4.解法2 LEETCODE 552 ms击败 58.91%

WITH callellation AS(
SELECT 
t1.id
,1 as ttl_mark
,case when status1 IN ( "cancelled_by_client", "cancelled_by_driver") THEN 1 ELSE 0 END as can_mark
,request_at
FROM trips t1
WHERE driver_id IN ( SELECT users_id FROM users WHERE banned = "NO")
AND  client_id IN ( SELECT users_id FROM users WHERE banned = "NO")
AND  request_at BETWEEN "2013-10-01" AND "2013-10-03"
)
SELECT request_at as Day
,ROUND(IFNULL(SUM(can_mark)/SUM(ttl_mark),0),2) AS "Cancellation Rate"
FROM callellation
group by request_at

5.解法2的优化版本 `LEETCODE 525 ms击败 80.63%`

核心思路：从订单表中筛选出非禁止用户的订单，按日期计算每一天的总订单数和取消订单数，最终计算每个日期的取消率并四舍五入保留两位小数。

公用表表达式 (CTE) 的定义：

使用 WITH 子句创建一个名为 callellation 的 CTE，以简化查询逻辑，并将计算结果临时存储起来供后续查询使用。
选择字段：

在 CTE 中，主要选择了 request_at（订单请求日期）作为分组依据，统计当天的订单情况：
- COUNT(*) 统计每个请求日期的总订单数，命名为 total_orders。
- 使用 SUM(CASE ...) 计算被取消的订单数（包括客户取消和司机取消），命名为 cancelled_orders。
过滤非禁止用户⭐⭐这个用法灰常值得记住

利用两个 INNER JOIN 子句：
- INNER JOIN users u_client 确保订单中的 client_id 代表的用户没有被禁止（即 banned = 'No'）。
- INNER JOIN users u_driver 确保订单中的 driver_id 代表的司机也没有被禁止。这样就能确保计算时只考虑非禁止用户生成的订单。
时间范围筛选：

使用 WHERE 子句限定查询只针对 2013 年 10 月 1 日到 2013 年 10 月 3 日之间的订单。这是为了确保计算只涉及该时间段内的订单数据。
按日期分组：

使用 GROUP BY t.request_at 将数据按日期分组，以便对每个请求日期的订单进行汇总和计算。
主查询计算取消率：

在最终的 SELECT 查询中：
- 通过 cancelled_orders / total_orders 计算取消率，并用 ROUND 函数保留两位小数。
- 使用 IFNULL 防止出现除以 0 的情况，如果某天没有订单，则显示取消率为 0。

--CTE (公用表表达式) 部分
WITH callellation AS (
  SELECT 
  t.request_at,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN t.status1 IN ('cancelled_by_client', 'cancelled_by_driver') THEN 1 ELSE 0 END) AS cancelled_orders
  FROM 
        trips t
--用两个JOIN子句确保只有非禁止用户（即 banned = 'No'）生成的订单才会被计入分析范围
  INNER JOIN 
        users u_client ON t.client_id = u_client.users_id AND u_client.banned = 'No'
  INNER JOIN 
        users u_driver ON t.driver_id = u_driver.users_id AND u_driver.banned = 'No'
--用WHERE子句来限定查询只包含请求日期在指定时间段内的订单（2013-10-01 到 2013-10-03）。
   WHERE 
        t.request_at BETWEEN '2013-10-01' AND '2013-10-03'
   GROUP BY 
        t.request_at
)
SELECT 
    request_at,
    ROUND(IFNULL(cancelled_orders / total_orders, 0), 2) AS "Cancellation Rate"
FROM 
    callellation;

6.难度UP问题版本（在实际业务环境中可能会遇到

6.1问题

除了计算固定时间段（如每日）的取消率之外，使用**移动窗口（Rolling Window）**来分析一定时间范围内的取消率变化趋势，例如：

滚动 7 天取消率
滚动 30 天取消率

现在请计算每一天截止当天的过去3天取消率，取消率同原题要求，为非禁止用户取消订单数/非禁止用户总订单数

6.2数据准备

-- 创建 Trips 表
CREATE TABLE Trips1 (
    id INT PRIMARY KEY AUTO_INCREMENT,
    client_id INT,
    driver_id INT,
    status1 VARCHAR(50),
    request_at DATE
);

-- 插入模拟数据 (涵盖2023年1月1日至2023年1月15日)
INSERT INTO Trips1 (client_id, driver_id, status1, request_at)
VALUES 
-- 2023年1月1日的数据
(1, 101, 'completed', '2023-01-01'),
(2, 102, 'cancelled_by_client', '2023-01-01'),
(3, 103, 'completed', '2023-01-01'),
(7, 107, 'cancelled_by_client', '2023-01-02'),
(8, 108, 'completed', '2023-01-02'),
(9, 109, 'cancelled_by_driver', '2023-01-02'),
(11, 111, 'completed', '2023-01-03'),
(12, 112, 'cancelled_by_client', '2023-01-03'),
(13, 113, 'completed', '2023-01-03'),
(14, 114, 'cancelled_by_driver', '2023-01-03'),
(16, 116, 'completed', '2023-01-04'),
(17, 117, 'cancelled_by_client', '2023-01-04'),
(18, 118, 'completed', '2023-01-04'),
(19, 119, 'cancelled_by_driver', '2023-01-04'),
(20, 120, 'completed', '2023-01-04'),
(23, 123, 'completed', '2023-01-05'),
(24, 124, 'cancelled_by_driver', '2023-01-05'),
(25, 125, 'completed', '2023-01-05'),
(26, 126, 'completed', '2023-01-06'),
(27, 127, 'cancelled_by_client', '2023-01-06'),
(28, 128, 'completed', '2023-01-06'),
(29, 129, 'cancelled_by_driver', '2023-01-06'),
(31, 131, 'completed', '2023-01-07'),
(32, 132, 'cancelled_by_client', '2023-01-07'),
(33, 133, 'completed', '2023-01-07'),
(4, 104, 'cancelled_by_driver', '2023-01-08'),
(5, 105, 'completed', '2023-01-08'),
(6, 106, 'completed', '2023-01-09'),
(10, 110, 'completed', '2023-01-09'),
(15, 115, 'completed', '2023-01-09'),
(21, 121, 'completed', '2023-01-10'),
(22, 122, 'cancelled_by_client', '2023-01-09'),
(30, 130, 'completed', '2023-01-10'),
(34, 134, 'cancelled_by_driver', '2023-01-10'),
(35, 135, 'completed', '2023-01-11'),
(41, 141, 'completed', '2023-01-11'),
(42, 142, 'cancelled_by_client', '2023-01-11'),
(43, 143, 'completed', '2023-01-11'),
(44, 144, 'cancelled_by_driver', '2023-01-11'),
(45, 145, 'completed', '2023-01-11'),
(46, 146, 'completed', '2023-01-12'),
(47, 147, 'cancelled_by_client', '2023-01-12'),
(48, 148, 'completed', '2023-01-12'),
(49, 149, 'cancelled_by_driver', '2023-01-12'),
(51, 151, 'completed', '2023-01-13'),
(52, 152, 'cancelled_by_client', '2023-01-13'),
(53, 153, 'completed', '2023-01-13'),
(49, 149, 'cancelled_by_driver', '2023-01-14'),
(50, 150, 'completed', '2023-01-14'),
(54, 154, 'cancelled_by_driver', '2023-01-14'),
(55, 155, 'completed', '2023-01-14'),
-- 2023年1月15日
(36, 136, 'completed', '2023-01-15'),
(37, 137, 'cancelled_by_client', '2023-01-15'),
(38, 138, 'completed', '2023-01-15'),
(39, 139, 'cancelled_by_driver', '2023-01-15'),
(40, 140, 'completed', '2023-01-15');

-- 插入 101 到 155 的用户为 driver
INSERT INTO Users (users_id, banned, role1)
VALUES
(101, 'No', 'driver'),
(102, 'No', 'driver'),
(103, 'No', 'driver'),
(104, 'No', 'driver'),
(105, 'No', 'driver'),
(106, 'No', 'driver'),
(107, 'No', 'driver'),
(108, 'No', 'driver'),
(109, 'Yes', 'driver'), -- banned = 'Yes'
(110, 'No', 'driver'),
(111, 'No', 'driver'),
(112, 'No', 'driver'),
(113, 'No', 'driver'),
(114, 'Yes', 'driver'), -- banned = 'Yes'
(115, 'No', 'driver'),
(116, 'No', 'driver'),
(117, 'No', 'driver'),
(118, 'No', 'driver'),
(119, 'No', 'driver'),
(120, 'No', 'driver'),
(121, 'No', 'driver'),
(122, 'No', 'driver'),
(123, 'No', 'driver'),
(124, 'No', 'driver'),
(125, 'No', 'driver'),
(126, 'No', 'driver'),
(127, 'Yes', 'driver'), -- banned = 'Yes'
(128, 'No', 'driver'),
(129, 'No', 'driver'),
(130, 'No', 'driver'),
(131, 'No', 'driver'),
(132, 'No', 'driver'),
(133, 'No', 'driver'),
(134, 'No', 'driver'),
(135, 'No', 'driver'),
(136, 'No', 'driver'),
(137, 'Yes', 'driver'), -- banned = 'Yes'
(138, 'No', 'driver'),
(139, 'No', 'driver'),
(140, 'No', 'driver'),
(141, 'No', 'driver'),
(142, 'Yes', 'driver'), -- banned = 'Yes'
(143, 'No', 'driver'),
(144, 'No', 'driver'),
(145, 'No', 'driver'),
(146, 'No', 'driver'),
(147, 'No', 'driver'),
(148, 'No', 'driver'),
(149, 'No', 'driver'),
(150, 'Yes', 'driver'), -- banned = 'Yes'
(151, 'No', 'driver'),
(152, 'No', 'driver'),
(153, 'No', 'driver'),
(154, 'No', 'driver'),
(155, 'Yes', 'driver'); -- banned = 'Yes'

6.3分析

数据清洗与过滤（clean_order CTE）：
- 通过 JOIN 操作，将 Trips1 表中的订单数据与 Users1 表中的用户信息进行关联。我们只保留那些未被禁用的 client 和 driver 的订单记录。
- 过滤完成后，主要保留每笔订单的 id、request_at（请求日期）和 status1（订单状态）这三个字段。
按天统计总订单数和取消订单数（daily_order CTE）：
- 基于清洗后的订单数据，使用 GROUP BY 按日期分组，计算每日的总订单量（ttl_amount）和取消订单量（cal_amount）。取消订单的定义为 status1 不等于 "completed" 的订单。
- 这样就得到了每一天的总订单和取消订单数，便于后续计算每日和滚动的取消率。
计算取消率：
- 对每日的取消率进行计算，公式为 cal_amount / ttl_amount，即取消订单数除以总订单数。
- 这里使用 IFNULL 函数来避免除以零的情况，确保返回的结果是有效数字。
滚动窗口取消率计算：
- 3天滚动取消率：通过 SUM() 函数配合窗口函数 OVER (ORDER BY request_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)，实现对当前日期及之前2天的数据进行累计，计算这3天内的取消订单数和总订单数。最终的3天滚动取消率为这两者的比值。
- 5天滚动取消率：同样的逻辑，使用 ROWS BETWEEN 4 PRECEDING AND CURRENT ROW 来定义滚动窗口，计算当前日期及之前4天的数据，得出5天的滚动取消率。
结果呈现：
- 查询结果会按request_at日期顺序输出，每天都会显示其对应的取消率、滚动3天取消率以及滚动5天取消率。

6.4解法

WITH clean_order AS (
SELECT id
,request_at
,t1.status1
FROM trips1 t1
JOIN users1 t2 ON t1.client_id = t2.users_id AND t2.banned = 'No'
JOIN users1 t3 ON t1.driver_id = t3.users_id AND t3.banned = 'No'
),
daily_order as (
SELECT request_at
,count(*) as ttl_amount
,SUM(CASE WHEN status1 <> "completed" THEN 1 ELSE 0 END) AS cal_amount
FROM clean_order
GROUP BY request_at
)
SELECT request_at
,ROUND(IFNULL(cal_amount / ttl_amount, 0), 2) AS "Cancellation Rate"
-- 计算滚动3天的总订单数和取消订单数，并计算滚动3天的取消率
,ROUND(IFNULL(SUM(cal_amount) OVER (ORDER BY request_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) /
                SUM(ttl_amount) OVER (ORDER BY request_at ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 0), 2) AS "3-Day Rolling Cancellation Rate"
-- 计算滚动5天的总订单数和取消订单数，并计算滚动5天的取消率
,ROUND(IFNULL(SUM(cal_amount) OVER (ORDER BY request_at ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) /
                SUM(ttl_amount) OVER (ORDER BY request_at ROWS BETWEEN 4 PRECEDING AND CURRENT ROW), 0), 2) AS "5-Day Rolling Cancellation Rate"
FROM daily_order
ORDER BY request_at ASC

7.需要注意的地方

在实际业务场景中，数据可能存在缺失（如订单状态或用户状态为 NULL）。在 SQL 计算时要确保数据完整性，例如使用 IFNULL() 或 COALESCE() 处理可能的 NULL 值。
使用索引优化查询（如果实际应用是大数据量情况下
- 为 Trips 表中的 request_at、client_id、driver_id 字段，以及 Users 表中的 users_id、banned 字段建立合适的索引，可以显著提高查询性能，特别是在大数据量的情况下。

CREATE INDEX idx_request_at ON Trips(request_at);
CREATE INDEX idx_client_id_driver_id ON Trips(client_id, driver_id);
CREATE INDEX idx_users_id_banned ON Users(users_id, banned);

可延申思考业务方向，抛一点IDEA~~
- 题目中要求计算 2013-10-01 到 2013-10-03 之间的取消率，但实际业务中取消率可能按不同时间窗口进行分析，抛几个可能的方向，大家可以延申思考一下，想想可以怎么写SQL~
  - 小时级别取消率：如果需要更精细的分析，可以按小时粒度计算取消率，特别是在高峰期与非高峰期的差异上。
  - 移动窗口分析：除了固定时间段，还可以使用移动窗口（如滚动7天、30天等）来计算一段时间内的平均取消率。这样可以更好地跟踪取消率的变化趋势。
  - 周期性分析：有些业务场景中，取消率可能与季节性、节假日、周末等周期因素有关。通过周、月、季度等时间维度的取消率分析，能更好地预测和理解取消行为。
- 除了按天计算取消率，还可以按城市、用户类别（VIP 客户与普通客户）或司机类型（全职与兼职）进行分组计算，得到更细化的业务洞察。例如：
  - 不同城市的取消率是否有显著差异？
  - 高峰期与非高峰期的取消率是否波动较大

标签：01,滚动,No,driver,行程,262,client,2023,id
From： https://blog.csdn.net/Yaoo415/article/details/142355578

262.行程与用户（如何计算每日/3日滚动/5日滚动订单取消率

1.题目

2.数据准备

3.基础版本-解答1`LEETCODE 550ms 击败 60.14%`

4.解法2 LEETCODE 552 ms击败 58.91%

5.解法2的优化版本 `LEETCODE 525 ms击败 80.63%`

6.难度UP问题版本（在实际业务环境中可能会遇到

6.1问题

6.2数据准备

6.3分析

6.4解法

7.需要注意的地方

相关文章

赞助商

阅读排行

262.行程与用户（如何计算每日/3日滚动/5日滚动订单取消率

1.题目

2.数据准备

3.基础版本-解答1**LEETCODE 550**ms 击败 **60.14%**

4.解法2 LEETCODE 552 ms击败 58.91%

5.解法2的优化版本 **LEETCODE 525** ms击败 **80.63%**

6.难度UP问题版本（在实际业务环境中可能会遇到

6.1问题

6.2数据准备

6.3分析

6.4解法

7.需要注意的地方

相关文章

赞助商

阅读排行

3.基础版本-解答1`LEETCODE 550ms 击败 60.14%`

5.解法2的优化版本 `LEETCODE 525 ms击败 80.63%`