小让の码场

外观

SQL179 各城市最大同时等车人数 ​

Static BadgeStatic BadgeStatic Badge

Important

同时在线人数问题：(进入时间,人数+1) UNION [ALL] (离开时间,人数-1)

题目描述 ​

用户打车记录表tb_get_car_record

iduidcityevent_timeend_timeorder_id
1108北京2021-10-20 08:00:002021-10-20 08:00:409008
2118北京2021-10-20 08:00:102021-10-20 08:00:459018
3102北京2021-10-20 08:00:302021-10-20 08:00:509002
4106北京2021-10-20 08:05:412021-10-20 08:06:009006
5103北京2021-10-20 08:05:502021-10-20 08:07:109003
6104北京2021-10-20 08:01:012021-10-20 08:01:209004
7105北京2021-10-20 08:01:152021-10-20 08:01:309019
8101北京2021-10-20 08:28:102021-10-20 08:30:009011

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

打车订单表tb_get_car_order

idorder_iduiddriver_idorder_timestart_timefinish_timemileagefaregrade
190081082042021-10-20 08:00:402021-10-20 08:03:002021-10-20 08:31:0013.2384
290181082142021-10-20 08:00:452021-10-20 08:04:502021-10-20 08:21:0014385
390021022022021-10-20 08:00:502021-10-20 08:06:002021-10-20 08:31:001041.55
490061062062021-10-20 08:06:002021-10-20 08:09:002021-10-20 08:31:00825.54
590031032032021-10-20 08:07:102021-10-20 08:15:002021-10-20 08:31:001141.54
690041042042021-10-20 08:01:202021-10-20 08:13:002021-10-20 08:31:007.5224
790191052052021-10-20 08:01:302021-10-20 08:11:002021-10-20 08:51:0010394
890111012112021-10-20 08:30:002021-10-20 08:31:002021-10-20 08:54:0010355

（order_id-订单号, uid-用户ID, driver_id-司机ID, order_time-接单时间, start_time-开始计费的上车时间, finish_time-订单完成时间, mileage-行驶里程数, fare-费用, grade-评分）

场景逻辑说明：

  • 用户提交打车请求后，在用户打车记录表生成一条打车记录，订单号-order_id设为null；
  • 当有司机接单时，在打车订单表生成一条订单，填充接单时间-order_time及其左边的字段，上车时间及其右边的字段全部为null，并把订单号和接单时间（打车结束时间）写入打车记录表；若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间。
  • 若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的订单完成时间-finish_time填充为取消时间，其余字段设为null。
  • 当司机接上乘客时，填充打车订单表中该订单的上车时间start_time。
  • 当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。

问题：请统计各个城市在2021年10月期间，单日中最大的同时等车人数。

注: 等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态。

如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。

结果按各城市最大等车人数升序排序，相同时按城市升序排序。

输出示例：

示例结果如下

citymax_wait_uv
北京5

解释：由打车订单表可以得知北京2021年10月20日有8条打车记录，108号乘客从08:00:00等到08:03:00，118号乘客从08:00:10等到08:04:50....,由此得知08:02:00秒时刻，共有5人在等车。

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 (108, '北京', '2021-10-20 08:00:00', '2021-10-20 08:00:40', 9008),
       (108, '北京', '2021-10-20 08:00:10', '2021-10-20 08:00:45', 9018),
       (102, '北京', '2021-10-20 08:00:30', '2021-10-20 08:00:50', 9002),
       (106, '北京', '2021-10-20 08:05:41', '2021-10-20 08:06:00', 9006),
       (103, '北京', '2021-10-20 08:05:50', '2021-10-20 08:07:10', 9003),
       (104, '北京', '2021-10-20 08:01:01', '2021-10-20 08:01:20', 9004),
       (103, '北京', '2021-10-20 08:01:15', '2021-10-20 08:01:30', 9019),
       (101, '北京', '2021-10-20 08:28:10', '2021-10-20 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 (9008, 108, 204, '2021-10-20 08:00:40', '2021-10-20 08:03:00', '2021-10-20 08:31:00', 13.2, 38, 4),
       (9018, 108, 214, '2021-10-20 08:00:45', '2021-10-20 08:04:50', '2021-10-20 08:21:00', 14, 38, 5),
       (9002, 102, 202, '2021-10-20 08:00:50', '2021-10-20 08:06:00', '2021-10-20 08:31:00', 10.0, 41.5, 5),
       (9006, 106, 203, '2021-10-20 08:06:00', '2021-10-20 08:09:00', '2021-10-20 08:31:00', 8.0, 25.5, 4),
       (9003, 103, 202, '2021-10-20 08:07:10', '2021-10-20 08:15:00', '2021-10-20 08:31:00', 11.0, 41.5, 4),
       (9004, 104, 202, '2021-10-20 08:01:20', '2021-10-20 08:13:00', '2021-10-20 08:31:00', 7.5, 22, 4),
       (9019, 103, 202, '2021-10-20 08:01:30', '2021-10-20 08:11:00', '2021-10-20 08:51:00', 10, 39, 4),
       (9011, 101, 211, '2021-10-20 08:30:00', '2021-10-20 08:31:00', '2021-10-20 08:54:00', 10, 35, 5);

答案 ​

Tip

  • 进入时间：event_time 开始打车的时间为进入时间，人数+1；

  • 离开时间：以下三种情况都视为离开时间，人数-1；

    • 若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间，order_id为空
    • 若乘客上车前，乘客或司机点击取消订单，则上车时间start_time为空，记录订单完成时间-finish_time为取消时间
    • 用户正常上车，则记录上车时间start_time
sql
SELECT city, MAX(max_wait_uv) AS `max_wait_uv`
FROM (SELECT city, MAX(uv_cnt) AS `max_wait_uv`
      FROM (SELECT city,
                   DATE(uv_time)                                                             AS `dt`,
                   SUM(uv) OVER (PARTITION BY city, DATE(uv_time) ORDER BY uv_time, uv DESC) AS `uv_cnt`
            FROM (
                     -- 用户开始打车，记录打车时间为event_time，打车人数+1
                     SELECT city, event_time AS `uv_time`, +1 AS `uv`
                     FROM tb_get_car_record
                     UNION ALL
                     -- 若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间，order_id为空
                     SELECT city, end_time AS `uv_time`, -1 AS `uv`
                     FROM tb_get_car_record
                     WHERE order_id IS NULL
                     UNION ALL
                     -- 若乘客上车前，乘客或司机点击取消订单，则上车时间start_time为空，记录订单完成时间-finish_time为取消时间
                     -- 用户正常上车，则记录上车时间start_time
                     SELECT tgcr.city, IFNULL(tgco.start_time, tgco.finish_time) AS `uv_time`, -1 AS `uv`
                     FROM tb_get_car_record tgcr
                              INNER JOIN tb_get_car_order tgco USING (order_id)) a
            WHERE YEAR(uv_time) = 2021
              AND MONTH(uv_time) = 10) b
      GROUP BY city, dt) c
GROUP BY city
ORDER BY max_wait_uv, city;