题解 | #各城市最大同时等车人数#

# 难点：
# 1、生成开始等车-结束等车记录表：
# （1）提取：开始等车时间——event_time，1，城市作为开始等车记录表
# （2）提取：结束等车时间——start_time,-1，城市作为结束等车记录表
# （3）用union all连接两表（由于存在时间重复的订单，所以用union all）
# 2、计算每个城市单日最大同时等车人数
# （1）用sum() over(order by ) 按时间算累计值
# （2）提取最大的累计值
select t5.city,max(t5.sum_diff) from (
select t4.dt,t4.city 
,sum(t4.diff) over(order by t4.dt asc,t4.diff desc)  as sum_diff from (
    select * from (
select t1.event_time as dt,1 as diff,t1.city from (
select o1.order_id,o1.uid,r1.event_time,o1.start_time,o1.mileage
,r1.city from tb_get_car_order as o1 
left join tb_get_car_record as r1 
on o1.order_id = r1.order_id where year(r1.event_time) = 2021 
and month(r1.event_time) = 10 ) t1 
union all 
select t2.start_time as dt,-1 as diff ,t2.city 
from (
select o1.order_id,o1.uid,r1.event_time,o1.start_time,o1.mileage
,r1.city from tb_get_car_order as o1 
left join tb_get_car_record as r1 
on o1.order_id = r1.order_id where year(r1.event_time) = 2021 
and month(r1.event_time) = 10 
) t2 ) t3 
) t4 
) t5  group by t5.city