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

with 
    t1 as(
        #1、开始等车
        select
            city,
            event_time ,
            1 `flag`
        from tb_get_car_record 
        where date(event_time) like '2021-10%'
    
    ),
    t2 as(
        #2、退出等车
        select
            city,
            if(start_time is null,finish_time,start_time) ,
            -1 `flag`
        from tb_get_car_record tgcr
        join tb_get_car_order tgco
        on tgcr.order_id=tgco.order_id
        where date(event_time) like '2021-10%'
    )
select 
    city,
    max(wait_uv) `max_wait_uv`
from(
    select 
        city,
        event_time,
    sum(flag) over(partition by city,date(event_time) order by event_time,flag desc ) `wait_uv`
    from(
        select 
            *
        from t1
        union all
        select 
            *
        from t2
    )t3
)t4
group by city
order by max_wait_uv,city

#考虑到有重复的数据就要用union all
#由于等车人数记作先增加后减少，所以对flag要降序