小让の码场

外观

SQL178 工作日各时段叫车量、等待接单时间和调度时间 ​

Static BadgeStatic Badge

题目描述 ​

用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1107北京2021-09-20 11:00:002021-09-20 11:00:309017
2108北京2021-09-20 21:00:002021-09-20 21:00:409008
3108北京2021-09-20 18:59:302021-09-20 19:01:009018
4102北京2021-09-21 08:59:002021-09-21 09:01:009002
5106北京2021-09-21 17:58:002021-09-21 18:01:009006
6103北京2021-09-22 07:58:002021-09-22 08:01:009003
7104北京2021-09-23 07:59:002021-09-23 08:01:009004
8103北京2021-09-24 19:59:202021-09-24 20:01:009019
9101北京2021-09-24 08:28:102021-09-24 08:30:009011

（uid 用户ID, city-城市, event_time-打车时间, end_time-打车结束时间, order_id-订单号）

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190171072132021-09-20 11:00:302021-09-20 11:02:102021-09-20 11:31:0011385
290081082042021-09-20 21:00:402021-09-20 21:03:002021-09-20 21:31:0013.2384
390181082142021-09-20 19:01:002021-09-20 19:04:502021-09-20 19:21:0014385
490021022022021-09-21 09:01:002021-09-21 09:06:002021-09-21 09:31:001041.55
590061062032021-09-21 18:01:002021-09-21 18:09:002021-09-21 18:31:00825.54
690071072032021-09-22 11:01:002021-09-22 11:07:002021-09-22 11:31:009.9305
790031032022021-09-22 08:01:002021-10-22 08:15:002021-10-22 08:31:001141.54
890041042022021-09-23 08:01:002021-09-23 08:13:002021-09-23 08:31:007.5224
990051052022021-09-23 10:01:002021-09-23 10:13:002021-09-23 10:31:009295
1090191032022021-09-24 20:01:002021-09-24 20:11:002021-09-24 20:51:0010394
1190111012112021-09-24 08:30:002021-09-24 08:31:002021-09-24 08:54:0010355

（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星评价后填充。

问题：统计周一到周五各时段的叫车量、平均等待接单时间和平均调度时间。全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数，平均调度时间仅计算完成了的订单，结果按叫车量升序排序。

注：

  • 不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 , 20:00:00）、休息时间 [20:00:00 , 07:00:00）
  • 时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）
  • 从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间。

输出示例：

示例数据的输出结果如下：

periodget_car_numavg_wait_timeavg_dispatch_time
工作时间10.51.7
休息时间10.72.3
晚高峰32.17.3
早高峰42.28.0

解释：订单9017打车开始于11点整，属于工作时间，等待时间30秒，调度时间为1分40秒，示例数据中工作时间打车订单就一个，平均等待时间0.5分钟，平均调度时间1.7分钟。

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 (107, '北京', '2021-09-20 11:00:00', '2021-09-20 11:00:30', 9017),
       (108, '北京', '2021-09-20 21:00:00', '2021-09-20 21:00:40', 9008),
       (108, '北京', '2021-09-20 18:59:30', '2021-09-20 19:01:00', 9018),
       (102, '北京', '2021-09-21 08:59:00', '2021-09-21 09:01:00', 9002),
       (106, '北京', '2021-09-21 17:58:00', '2021-09-21 18:01:00', 9006),
       (103, '北京', '2021-09-22 07:58:00', '2021-09-22 08:01:00', 9003),
       (104, '北京', '2021-09-23 07:59:00', '2021-09-23 08:01:00', 9004),
       (103, '北京', '2021-09-24 19:59:20', '2021-09-24 20:01:00', 9019),
       (101, '北京', '2021-09-24 08:28:10', '2021-09-24 08:30:00', 9011);

INSERT INTO tb_get_car_order(order_id, uid, driver_id, order_time, start_time, finish_time, mileage, fare, grade)
VALUES (9017, 107, 213, '2021-09-20 11:00:30', '2021-09-20 11:02:10', '2021-09-20 11:31:00', 11, 38, 5),
       (9008, 108, 204, '2021-09-20 21:00:40', '2021-09-20 21:03:00', '2021-09-20 21:31:00', 13.2, 38, 4),
       (9018, 108, 214, '2021-09-20 19:01:00', '2021-09-20 19:04:50', '2021-09-20 19:21:00', 14, 38, 5),
       (9002, 102, 202, '2021-09-21 09:01:00', '2021-09-21 09:06:00', '2021-09-21 09:31:00', 10.0, 41.5, 5),
       (9006, 106, 203, '2021-09-21 18:01:00', '2021-09-21 18:09:00', '2021-09-21 18:31:00', 8.0, 25.5, 4),
       (9007, 107, 203, '2021-09-22 11:01:00', '2021-09-22 11:07:00', '2021-09-22 11:31:00', 9.9, 30, 5),
       (9003, 103, 202, '2021-09-22 08:01:00', '2021-09-22 08:15:00', '2021-09-22 08:31:00', 11.0, 41.5, 4),
       (9004, 104, 202, '2021-09-23 08:01:00', '2021-09-23 08:13:00', '2021-09-23 08:31:00', 7.5, 22, 4),
       (9005, 105, 202, '2021-09-23 10:01:00', '2021-09-23 10:13:00', '2021-09-23 10:31:00', 9, 29, 5),
       (9019, 103, 202, '2021-09-24 20:01:00', '2021-09-24 20:11:00', '2021-09-24 20:51:00', 10, 39, 4),
       (9011, 101, 211, '2021-09-24 08:30:00', '2021-09-24 08:31:00', '2021-09-24 08:54:00', 10, 35, 5);

答案 ​

sql
SELECT CASE
           WHEN TIME(tgcr.event_time) >= '07:00:00' AND TIME(tgcr.event_time) < '09:00:00' THEN '早高峰'
           WHEN TIME(tgcr.event_time) >= '09:00:00' AND TIME(tgcr.event_time) < '17:00:00' THEN '工作时间'
           WHEN TIME(tgcr.event_time) >= '17:00:00' AND TIME(tgcr.event_time) < '20:00:00' THEN '晚高峰'
           ELSE '休息时间' END                                                     AS `period`,
       COUNT(*)                                                                    AS `get_car_num`,
       ROUND(AVG(TIMESTAMPDIFF(SECOND, tgcr.event_time, tgco.order_time) / 60), 1) AS `avg_wait_time`,
       ROUND(AVG(TIMESTAMPDIFF(SECOND, tgco.order_time, tgco.start_time) / 60), 1) AS `avg_dispatch_time`
FROM tb_get_car_record tgcr
         INNER JOIN tb_get_car_order tgco USING (order_id)
WHERE DAYOFWEEK(tgcr.event_time) BETWEEN 2 AND 6
GROUP BY period
ORDER BY get_car_num;