一、题目
问题:请统计各个城市在2021年10月期间,单日中最大的同时等车人数。
注: 等车指从开始打车起,直到取消打车、取消等待或上车前的这段时间里用户的状态。
如果同一时刻有人停止等车,有人开始等车,等车人数记作先增加后减少。
结果按各城市最大等车人数升序排序,相同时按城市升序排序。
二、数据源
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);
场景逻辑说明:
场景逻辑说明:
-
用户提交打车请求后,在用户打车记录表生成一条打车记录,订单号-order_id设为null;
-
当有司机接单时,在打车订单表生成一条订单,填充接单时间-order_time及其左边的字段,上车时间及其右边的字段全部为null,并把订单号和接单时间(打车结束时间)写入打车记录表;若一直无司机接单、超时或中途用户主动取消打车,则记录打车结束时间。
-
若乘客上车前,乘客或司机点击取消订单,会将打车订单表对应订单的订单完成时间-finish_time填充为取消时间,其余字段设为null。
-
当司机接上乘客时,填充打车订单表中该订单的上车时间start_time。
-
当订单完成时填充订单完成时间、里程数、费用;评分设为null,在用户给司机打1~5星评价后填充。
三、输出示例
四、代码
WITH t1 AS(
SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt #每个城市等车瞬时UV
FROM (
SELECT city,event_time uv_time,1 AS uv FROM tb_get_car_record #进入等车状态
UNION ALL
SELECT city,end_time uv_time,-1 AS uv FROM tb_get_car_record WHERE order_id IS NULL #接单前取消
UNION ALL
SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)#接单后取消或上车
)AS t WHERE DATE_FORMAT(uv_time,'%Y%m')='202110' #2021年10月
)
SELECT city,MAX(uv_cnt) max_wait_uv FROM t1 GROUP BY citY ORDER BY max_wait_uv,citY;#排序先按照uv升序,uv一样按照城市升序
五、题解
题目:请统计各个城市在2021年10月期间,单日中最大的同时等车人数。
这是一道典型的统计同时在线用户数的题,和统计直播间在线用户数的逻辑是一样的。进入的用户定义uv为1,离开的用户定义uv为-1。
因而,需要对用户进入和离开的时间进行定义。
进入时间:event_time,开始打车的时间即为等车开始。
离开时间:有3种情况
- 状态1:司机接单前取消,则没有生成order_id,这种情况 order_id IS NULL 记录end_time
- 状态2:司机接单后取消,则没有上车时间,start_time IS NULL 记录 finish_time
- 状态3:正常上车,记录start_time,start_time IS NOT NULL
- 状态2和3可以直接使用IFNULL()合并,IFNULL(start_time,finish_time) 如果start_time空则返回finish_time,不空则start_time
定义完用户进入等车和离开等车这两种事件之后,关联所有表格,使用窗口函数排序累加即可。