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

思路：

等车分为三个阶段

1.司机接到单前等车为加1

2.司机接到单前取消减1

3.司机接到单后取消开始打车时间或者上车了忘记结束打车时间ifnull()判断两种情况

4.sum()over（）规定范围内求和

最后max()找出同时最大的等车人数

with a as(

select city,sum(uv) over(partition by city order by uv_time,uv desc) wait_uv

from(

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)

union all

select city,event_time,1 as uv from tb_get_car_record

union all

select city,end_time,-1 as uv from tb_get_car_record where order_id is null

)b where DATE_FORMAT(uv_time,"%Y%m")='202110'

)

select city,max(wait_uv) max_wait_uv from a group by city