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

#各城市单日中最大的同时等车人数，题目中给出的示例结果并未体现出各城市每日的最大的同时等车人数，因此可以忽略单日，只要求各城市最大的同时等车人数即可
#用户开始打车，等车+1
with t1 as
(select city,sum(uv) over(partition by city order by uv_time,uv desc) as wait_uv
from
    (select city, event_time uv_time,1 as uv from tb_get_car_record
union all 
#在司机接单前，用户取消，非等车-1
select city, end_time uv_time,-1 as uv from tb_get_car_record  where order_id is null
union all 
#在司机接单后，用户/司机取消,或用户上车，非等车-1
select city, ifnull(start_time,finish_time) uv_time,-1 as uv from tb_get_car_record left join tb_get_car_order using(order_id)
)a
where date_format(uv_time,'%Y-%m')='2021-10'
)

#主查询
select city, max(wait_uv) as max_wait_uv
from t1
group by city
order by max_wait_uv, city