牛客——SQL179 各城市最大同时等车人数

moliyy / 2023-09-04 / 原文

描述

用户打车记录表tb_get_car_record

id	uid	city	event_time	end_time	order_id
1	108	北京	2021-10-20 08:00:00	2021-10-20 08:00:40	9008
2	118	北京	2021-10-20 08:00:10	2021-10-20 08:00:45	9018
3	102	北京	2021-10-20 08:00:30	2021-10-20 08:00:50	9002
4	106	北京	2021-10-20 08:05:41	2021-10-20 08:06:00	9006
5	103	北京	2021-10-20 08:05:50	2021-10-20 08:07:10	9003
6	104	北京	2021-10-20 08:01:01	2021-10-20 08:01:20	9004
7	105	北京	2021-10-20 08:01:15	2021-10-20 08:01:30	9019
8	101	北京	2021-10-20 08:28:10	2021-10-20 08:30:00	9011

（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	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
2	9018	108	214	2021-10-20 08:00:45	2021-10-20 08:04:50	2021-10-20 08:21:00	14	38	5
3	9002	102	202	2021-10-20 08:00:50	2021-10-20 08:06:00	2021-10-20 08:31:00	10	41.5	5
4	9006	106	206	2021-10-20 08:06:00	2021-10-20 08:09:00	2021-10-20 08:31:00	8	25.5	4
5	9003	103	203	2021-10-20 08:07:10	2021-10-20 08:15:00	2021-10-20 08:31:00	11	41.5	4
6	9004	104	204	2021-10-20 08:01:20	2021-10-20 08:13:00	2021-10-20 08:31:00	7.5	22	4
7	9019	105	205	2021-10-20 08:01:30	2021-10-20 08:11:00	2021-10-20 08:51:00	10	39	4
8	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-订单号, 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月期间，单日中最大的同时等车人数。

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

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

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

输出示例：

示例结果如下

city	max_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人在等车。

自己没有啥好的解题思路，也没有做出来

下次一定要理清各种状态，其实并不难，空想才难

别人优秀的解题思路：

这是一道典型的统计同时在线用户数的题，和统计直播间在线用户数的逻辑是一样的。进入的用户定义uv为1，离开的用户定义uv为-1。

因而，需要对用户进入和离开的时间进行定义。

进入时间：event_time,开始打车的时间即为等车开始。

SELECT city,event_time uv_time,1 AS uv FROM  tb_get_car_record

离开时间：有3种情况

司机接单前取消，则没有生成order_id,这种情况 order_id IS NULL 记录end_time
1. ```
SELECT city,end_time uv_time,-1 AS uv FROM  tb_get_car_record WHERE order_id IS NULL
```
机接单后取消，则没有上车时间，start_time IS NULL 记录 finish_time

正常上车，记录start_time,start_time IS NOT NULL

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)

定义完用户进入等车和离开等车这两种事件之后，关联所有表格，使用窗口函数排序累加即可。

完整代码：

select city,
       max(s) as max_wait_uv
from (
         select city,
                sum(uv) over (partition by city  order by uv_time,uv desc ) as s
         from (
                  select city, event_time as uv_time, 1 as uv
                  from tb_get_car_record
                  union all
                  select city, end_time as uv_time, -1 as uv
                  from tb_get_car_record
                  where order_id is null
                  union all
                  select city, IFNULL(start_time, finish_time) as uv_time, -1 as uv
                  from tb_get_car_record
                           left join tb_get_car_order using (order_id)
              ) t
         where date_format(uv_time, '%Y-%m') = '2021-10'
     ) tt
group by city
order by max_wait_uv ,city;