小让の码场SQL177 国庆期间近7日日均取消订单量
题目描述
现有用户打车记录表tb_get_car_record
| id | uid | city | event_time | end_time | order_id |
|---|---|---|---|---|---|
| 1 | 101 | 北京 | 2021-09-25 08:28:10 | 2021-09-25 08:30:00 | 9011 |
| 2 | 102 | 北京 | 2021-09-25 09:00:30 | 2021-09-25 09:01:00 | 9012 |
| 3 | 103 | 北京 | 2021-09-26 07:59:00 | 2021-09-26 08:01:00 | 9013 |
| 4 | 104 | 北京 | 2021-09-26 07:59:00 | 2021-09-26 08:01:00 | 9023 |
| 5 | 104 | 北京 | 2021-09-27 07:59:20 | 2021-09-27 08:01:00 | 9014 |
| 6 | 105 | 北京 | 2021-09-28 08:00:00 | 2021-09-28 08:02:10 | 9015 |
| 7 | 106 | 北京 | 2021-09-29 17:58:00 | 2021-09-29 18:01:00 | 9016 |
| 8 | 107 | 北京 | 2021-09-30 11:00:00 | 2021-09-30 11:01:00 | 9017 |
| 9 | 108 | 北京 | 2021-09-30 21:00:00 | 2021-09-30 21:01:00 | 9018 |
| 10 | 102 | 北京 | 2021-10-01 09:00:30 | 2021-10-01 09:01:00 | 9002 |
| 11 | 106 | 北京 | 2021-10-01 17:58:00 | 2021-10-01 18:01:00 | 9006 |
| 12 | 101 | 北京 | 2021-10-02 08:28:10 | 2021-10-02 08:30:00 | 9001 |
| 13 | 107 | 北京 | 2021-10-02 11:00:00 | 2021-10-02 11:01:00 | 9007 |
| 14 | 108 | 北京 | 2021-10-02 21:00:00 | 2021-10-02 21:01:00 | 9008 |
| 15 | 103 | 北京 | 2021-10-02 07:59:00 | 2021-10-02 08:01:00 | 9003 |
| 16 | 104 | 北京 | 2021-10-03 07:59:20 | 2021-10-03 08:01:00 | 9004 |
| 17 | 109 | 北京 | 2021-10-03 18:00:00 | 2021-10-03 18:01:00 | 9009 |
（uid-用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号）
打车订单表tb_get_car_order
| id | order_id | uid | driver_id | order_time | start_time | finish_time | mileage | fare | grade |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 9011 | 101 | 211 | 2021-09-25 08:30:00 | 2021-09-25 08:31:00 | 2021-09-25 08:54:00 | 10 | 35 | 5 |
| 2 | 9012 | 102 | 211 | 2021-09-25 09:01:00 | 2021-09-25 09:01:50 | 2021-09-25 09:28:00 | 11 | 32 | 5 |
| 3 | 9013 | 103 | 212 | 2021-09-26 08:01:00 | 2021-09-26 08:03:00 | 2021-09-26 08:27:00 | 12 | 31 | 4 |
| 4 | 9023 | 104 | 213 | 2021-09-26 08:01:00 | NULL | 2021-09-26 08:27:00 | NULL | NULL | NULL |
| 5 | 9014 | 104 | 212 | 2021-09-27 08:01:00 | 2021-09-27 08:04:00 | 2021-09-27 08:21:00 | 11 | 31 | 5 |
| 6 | 9015 | 105 | 212 | 2021-09-28 08:02:10 | 2021-09-28 08:04:10 | 2021-09-28 08:25:10 | 12 | 31 | 4 |
| 7 | 9016 | 106 | 213 | 2021-09-29 18:01:00 | 2021-09-2918:02:10 | 2021-09-29 18:23:00 | 11 | 39 | 4 |
| 8 | 9017 | 107 | 213 | 2021-09-3011:01:00 | 2021-09-30 11:01:40 | 2021-09-30 11:31:00 | 11 | 38 | 5 |
| 9 | 9018 | 108 | 214 | 2021-09-30 21:01:00 | 2021-09-30 21:02:50 | 2021-09-30 21:21:00 | 14 | 38 | 5 |
| 10 | 9002 | 102 | 202 | 2021-10-01 09:01:00 | 2021-10-01 0 9:06:00 | 2021-10-01 09:31:00 | 10 | 41.5 | 5 |
| 11 | 9006 | 106 | 203 | 2021-10-0118:01:00 | 2021-10-01 18:09:00 | 2021-10-01 18:31:00 | 8 | 25.5 | 4 |
| 12 | 9001 | 101 | 202 | 2021-10-02 08:30:00 | NULL | 2021-10-02 08:31:00 | NULL | NULL | NULL |
| 13 | 9007 | 107 | 203 | 2021-10-02 11:01:00 | 2021-10-0211:07:00 | 2021-10-02 11:31:00 | 9.9 | 30 | 5 |
| 14 | 9008 | 108 | 204 | 2021-10-02 21:01:00 | 2021-10-02 21:10:00 | 2021-10-02 21:31:00 | 13.2 | 38 | 4 |
| 15 | 9003 | 103 | 202 | 2021-10-02 08:01:00 | 2021-10-02 08:15:00 | 2021-10-02 08:31:00 | 11 | 41.5 | 4 |
| 16 | 9004 | 104 | 202 | 2021-10-03 08:01:00 | 2021-10-03 08:13:00 | 2021-10-03 08:31:00 | 7.5 | 22 | 4 |
| 17 | 9009 | 109 | 204 | 2021-10-0318:01:00 | NULL | 2021-10-03 18:51:00 | NULL | NULL | NULL |
（order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）
场景逻辑说明：
- 用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；
- 当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。
- 若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。
- 当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。
- 当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。
问题：请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数。
输出示例：
示例输出如下
| dt | finish_num_7d | cancel_num_7d |
|---|---|---|
| 2021-10-01 | 1.43 | 0.14 |
| 2021-10-02 | 1.57 | 0.29 |
| 2021-10-03 | 1.57 | 0.29 |
解释：
2021年9月25到10月3日每天的订单完成量为：2、1、1、1、1、2、2、3、1；每天的订单取消量为：0、1、0、0、0、0、0、1、1；
因此10.1到10.3期间的近7日订单完成量分别为10、11、11，因此日均订单完成量为：1.43、1.57、1.57；
近7日订单取消量分别为1、2、2，因此日均订单取消量为0.14、0.29、0.29；
SQL Schema
sql
DROP TABLE IF EXISTS tb_get_car_record,tb_get_car_order;
CREATE TABLE tb_get_car_record
(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
uid INT NOT NULL COMMENT '用户ID',
city VARCHAR(10) NOT NULL COMMENT '城市',
event_time datetime COMMENT '打车时间',
end_time datetime COMMENT '打车结束时间',
order_id INT COMMENT '订单号'
) CHARACTER SET utf8
COLLATE utf8_bin;
CREATE TABLE tb_get_car_order
(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
order_id INT NOT NULL COMMENT '订单号',
uid INT NOT NULL COMMENT '用户ID',
driver_id INT NOT NULL COMMENT '司机ID',
order_time datetime COMMENT '接单时间',
start_time datetime COMMENT '开始计费的上车时间',
finish_time datetime COMMENT '订单结束时间',
mileage FLOAT COMMENT '行驶里程数',
fare FLOAT COMMENT '费用',
grade TINYINT COMMENT '评分'
) CHARACTER SET utf8
COLLATE utf8_bin;
INSERT INTO tb_get_car_record(uid, city, event_time, end_time, order_id)
VALUES (101, '北京', '2021-09-25 08:28:10', '2021-09-25 08:30:00', 9011),
(102, '北京', '2021-09-25 09:00:30', '2021-09-25 09:01:00', 9012),
(103, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9013),
(104, '北京', '2021-09-26 07:59:00', '2021-09-26 08:01:00', 9023),
(104, '北京', '2021-09-27 07:59:20', '2021-09-27 08:01:00', 9014),
(105, '北京', '2021-09-28 08:00:00', '2021-09-28 08:02:10', 9015),
(106, '北京', '2021-09-29 17:58:00', '2021-09-29 18:01:00', 9016),
(107, '北京', '2021-09-30 11:00:00', '2021-09-30 11:01:00', 9017),
(108, '北京', '2021-09-30 21:00:00', '2021-09-30 21:01:00', 9018),
(102, '北京', '2021-10-01 09:00:30', '2021-10-01 09:01:00', 9002),
(106, '北京', '2021-10-01 17:58:00', '2021-10-01 18:01:00', 9006),
(101, '北京', '2021-10-02 08:28:10', '2021-10-02 08:30:00', 9001),
(107, '北京', '2021-10-02 11:00:00', '2021-10-02 11:01:00', 9007),
(108, '北京', '2021-10-02 21:00:00', '2021-10-02 21:01:00', 9008),
(103, '北京', '2021-10-02 07:59:00', '2021-10-02 08:01:00', 9003),
(104, '北京', '2021-10-03 07:59:20', '2021-10-03 08:01:00', 9004),
(109, '北京', '2021-10-03 18:00:00', '2021-10-03 18:01:00', 9009);
INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade)
VALUES (9011, 101, 211, '2021-09-25 08:30:00', '2021-09-25 08:31:00', '2021-09-25 08:54:00', 10, 35, 5),
(9012, 102, 211, '2021-09-25 09:01:00', '2021-09-25 09:01:50', '2021-09-25 09:28:00', 11, 32, 5),
(9013, 103, 212, '2021-09-26 08:01:00', '2021-09-26 08:03:00', '2021-09-26 08:27:00', 12, 31, 4),
(9023, 104, 213, '2021-09-26 08:01:00', null, '2021-09-26 08:27:00', null, null, null),
(9014, 104, 212, '2021-09-27 08:01:00', '2021-09-27 08:04:00', '2021-09-27 08:21:00', 11, 31, 5),
(9015, 105, 212, '2021-09-28 08:02:10', '2021-09-28 08:04:10', '2021-09-28 08:25:10', 12, 31, 4),
(9016, 106, 213, '2021-09-29 18:01:00', '2021-09-29 18:02:10', '2021-09-29 18:23:00', 11, 39, 4),
(9017, 107, 213, '2021-09-30 11:01:00', '2021-09-30 11:01:40', '2021-09-30 11:31:00', 11, 38, 5),
(9018, 108, 214, '2021-09-30 21:01:00', '2021-09-30 21:02:50', '2021-09-30 21:21:00', 14, 38, 5),
(9002, 102, 202, '2021-10-01 09:01:00', '2021-10-01 09:06:00', '2021-10-01 09:31:00', 10.0, 41.5, 5),
(9006, 106, 203, '2021-10-01 18:01:00', '2021-10-01 18:09:00', '2021-10-01 18:31:00', 8.0, 25.5, 4),
(9001, 101, 202, '2021-10-02 08:30:00', null, '2021-10-02 08:31:00', null, null, null),
(9007, 107, 203, '2021-10-02 11:01:00', '2021-10-02 11:07:00', '2021-10-02 11:31:00', 9.9, 30, 5),
(9008, 108, 204, '2021-10-02 21:01:00', '2021-10-02 21:10:00', '2021-10-02 21:31:00', 13.2, 38, 4),
(9003, 103, 202, '2021-10-02 08:01:00', '2021-10-02 08:15:00', '2021-10-02 08:31:00', 11.0, 41.5, 4),
(9004, 104, 202, '2021-10-03 08:01:00', '2021-10-03 08:13:00', '2021-10-03 08:31:00', 7.5, 22, 4),
(9009, 109, 204, '2021-10-03 18:01:00', null, '2021-10-03 18:51:00', null, null, null);答案
sql
SELECT *
FROM (SELECT DATE(finish_time) AS `dt`,
ROUND(SUM(COUNT(start_time)) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7,
2) AS `finish_num_7d`,
ROUND(SUM(SUM(IF(start_time IS NULL, 1, 0))) OVER (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) / 7,
2) AS `cancel_num_7d`
FROM tb_get_car_order
WHERE DATE(finish_time) BETWEEN '2021-09-25' AND '2021-10-03'
GROUP BY dt) t
WHERE dt BETWEEN '2021-10-01' AND '2021-10-03'
ORDER BY dt;