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

# 请统计各个城市在2021年10月期间，单日中最大的同时等车人数
# 筛选条件2021年10月

# 分组条件：city

# 等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态
# 等车时间段：event_time to finish_time/start_time

# 计算每天的同时等车人数，再求每天最大的同时等车人数
# 再求2021年10月的单日中最大的同时等车人数，再按城市分组

# 如何计算每天同时等车的人数？
# 如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减
# 开始等车：event_time  结束等车: case when mileage is null then finish_time else start_time
# 将所有开始等车时刻和结束等车时刻升序排序， 开始时间标记为1，结束时间标记为-1， unbounded preceding 滚动求和

select
    t2.city as city
    , max(uv) as max_wait_uv
from(   -- 2021-10月期间每个城市每天的每个时刻的等车人数
    select
        date(t1.tick) as dt
        , t1.city
        , sum(flag) over (partition by t1.city, date(t1.tick) order by t1.tick, t1.flag desc) as uv
    from(
        select 
            cr.event_time as tick
            , cr.city
            , 1 as flag
        from tb_get_car_order as co
        inner join tb_get_car_record as cr on co.order_id = cr.order_id
        union all
        select 
            (case when co.mileage is not null then co.start_time else co.finish_time end) as tick
            , cr.city
            , -1 as flag
        from tb_get_car_order as co
        inner join tb_get_car_record as cr on co.order_id = cr.order_id
        ) as t1
    where date_format(t1.tick, "%Y%m") = 202110
    ) as t2
group by t2.city
order by max_wait_uv, city