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

with zb as (with cb2 as ((with cb as (select city,event_time,1 as wait_nu,if(start_time is not null,start_time,end_time) end_time,-1 as leave_num 
/*将开始打车时间点标记为1,结束为-1,如果没有接单则以打车结束时间当作结束等待时间*/
from tb_get_car_order tgo
left join tb_get_car_record tgr on tgo.order_id= tgr.order_id /*左连接,要取到所有打车信息*/
where event_time like '2021-10%')
select city,event_time,wait_nu
from cb
union all 
select city,end_time,leave_num
from cb
order by event_time))
select city,date(event_time) time_day,event_time,
sum(wait_nu) over(partition by city,date(event_time) order by event_time,wait_nu desc) wait_uv /*个时间段等待人数*/
from cb2)
select city,max(wait_uv) as max_wait_uv
from zb
group by city
order by max_wait_uv,city