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

with t1 as
(
    select city,event_time as tm,1 as outin
    from tb_get_car_record
    union all
    select city,case 
    when order_id is null then end_time #用户取消订单/没司机接单
    when start_time is null then finish_time #用户或取消订单
    else start_time end as ft,-1
    from tb_get_car_record
    left join tb_get_car_order using(order_id)#可能出现无订单号的情况
)
select city,max(pku)
from
(
select city,tm,sum(outin) over(order by tm asc,outin desc) as pku #先计算用户增加
from t1
where substr(tm,1,7)='2021-10'
)t
group by city,date(tm) #城市单日订单峰值