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

with a as(
select uid,city,event_time as dt,1 as flag from tb_get_car_record
union 
select uid,city,end_time as dt,-1 as flag from tb_get_car_record where order_id is null
union 
(select t.uid,city,ifnull(start_time,finish_time) as dt,-1 as flag from tb_get_car_order t right join tb_get_car_record tr on t.order_id = tr.order_id))

select city,max(uv) as max_wait_uv
from(
select city,sum(flag) over(partition by city order by dt,flag desc) as uv
from a
where date_format(dt,'%Y%m') ='202110')b
group by city
order by max_wait_uv,city

二刷