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

# 注意over窗口中排序的num desc
# 因为如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。
with tmp as(
    select 
        city,
        rd.event_time et,
        (
            case when rd.order_id is null then end_time
                 else start_time
                 END
        ) st
    from tb_get_car_record rd
    left join tb_get_car_order od
    on rd.order_id = od.order_id
    where date_format(event_time,'%Y-%m') = '2021-10'
)
select 
    city,
    max(wait_num)
from
    (
    select 
        city,
        sum(num) over(partition by city order by dt,num desc) wait_num
    from
        (
            select
                city,
                et dt,
                1 num
            from 
                tmp
            union all
            select
                city,
                st dt,
                -1 num
            from 
                tmp
        )t1
    )t2
group by city
order by max(wait_num),city