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

with
    passengerList as (
    select 
        * 
    from ( 
        select 
            city,
            event_time as time,
            1 signnum 
        from  tb_get_car_record # 在用户打车记录表中找出所有的event_time就是所有用户上车的情况，并且记录为1，等车为1，取消为-1
        union all 
        select 
            city,
            end_time as time,
            -1 signnum #这种情况是乘客未上车之前就已经取消订单，所有在用户打车记录表中的order_id为null
        from tb_get_car_record    
        where order_id is null 
        union all
        select 
            city,
            start_time as time,
            -1 signnum #这种情况是司机接单后，用户已经不处于等车阶段，所以记录为-1
        from tb_get_car_order join tb_get_car_record using(order_id)
        where start_time is not null
        union all 
        select 
            city,
            finish_time as time,
            -1 signnum   
        from tb_get_car_order join tb_get_car_record using(order_id) 
        where start_time is null #这种情况是司机接单后，司机主动点击取消或者用户自己取消，也不出于等车阶段，所以记录为-1
        ) t1 
        where date_format(time,'%Y-%m') = '2021-10'
    )
select 
    city,
    max(uv)  
from (
    select 
        city,
        sum(signnum)over(partition by city order by time , signnum desc) as uv #根据不同城市进行一个分类汇总，这样根据signnum最大值就可以求出最大的同时在线人数，但是要注意按照时间的顺序来，相同时间是先加后减所以也要signnum desc
    from 
    passengerList
) t1
group by city
order by max(uv)

本体有一定难度，因为用户等车和取消有四种

1.司机未接单前用户主动取消 order_id is null

2.司机接单后用户或者司机主动取消 start_time is null

3.司机接单后完成订单 start_time

4.用户等车，那就是event_time