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

-- 思路：

• -- 1.对event_time,start_time,finish_time编码：event_time等车人数+1，start_time等车人数-1，finish_time and start_time is null等车人数-1
• -- 同时关联tb_get_car_record表拿到用户所在城市city,然后union all结果为临时表temp
• -- 2.对表temp使用开窗函数sum() 按照city维度分组，dt升序，diff降序统计等车人数变化情况
• -- 3.按照city分组，取每个城市同时等待的最大人数即可

-- 思路：
--      1.对event_time,start_time,finish_time编码：event_time等车人数+1，start_time等车人数-1，finish_time and start_time is null等车人数-1
--     同时关联tb_get_car_record表拿到用户所在城市city,然后union all结果为临时表temp
--      2.对表temp使用开窗函数sum() 按照city维度分组，dt升序，diff降序统计等车人数变化情况
--      3.按照city分组，取每个城市同时等待的最大人数即可
select
    city,
    max(waitting_unt) as max_wait_uv
from (
    select
        city, dt, sum(diff) over(partition by city,date(dt) order by dt, diff desc) as waitting_unt
    from (
        select 
            city, event_time as dt, order_id, 1 as diff              -- 开始等待,人数+1
        from tb_get_car_record
        where substr(event_time, 1, 7) ='2021-10'
        union all
        select
            r.city, o.start_time as dt, r.order_id, -1 as diff       -- 顺利上车，人数-1
        from tb_get_car_record r left join tb_get_car_order o on r.order_id = o.order_id
        where substr(o.start_time, 1, 7) ='2021-10'
        union all
        select
            r.city, o.finish_time as dt, r.order_id, -1 as diff                 -- 上车前取消，人数-1
        from tb_get_car_record r left join tb_get_car_order o on r.order_id = o.order_id
        -- 上车前取消：start_time is null
        where substr(o.finish_time, 1, 7) ='2021-10'
            and o.start_time is null
    ) temp
) t
group by city
order by max_wait_uv, city