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

with df as(
    #进入等车状态 uv+1
    SELECT 
    city,
    event_time as "UV变动时间点",
    1 as "UV"
    FROM tb_get_car_record
    UNION ALL 
    #退出等车_情况一 uv-1
    SELECT 
    city,
    end_time as "UV变动时间点",
    -1 as "UV"
    from tb_get_car_record
    where order_id is null
    UNION all       
    #退出等车_情况二 uv-1
    SELECT 
    df2.city,
    ifnull(df1.start_time,df1.finish_time) as "UV变动时间点",
    -1 as "UV"
    from tb_get_car_order as df1
    left join tb_get_car_record as df2
    on df1.order_id = df2.order_id
)

select city,max(UV_num) as "最大瞬时UV" from(
    select
    city,
    sum(UV) over(partition by city order by UV变动时间点 asc,UV desc ROWS between unbounded preceding and current row) as "UV_num"  #题目要求同一时刻先增后减，所以排序需要追加 UV desc；ROWS between ...之后都可以省略，此处为了防止本人忘记，就不省略了
    from df
    where date_format(UV变动时间点,'%Y-%m') = '2021-10'
) as a
group by city
order by 最大瞬时UV asc,city asc

附带注释