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

这题和SQL8其实一个思路就是求瞬时的最大UV，但是要注意两个点

1. 这里的in_time是event_time, out_time是start_time
2. 要注意同一时间段同时uv的上升和下降

1 将tb_get_car_record及tb_get_car_order合并两次分别筛选event_time和start_time进行并集获取连续的时间段的用户行为。同时将

 select city,uid,event_time as dt , 1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)
            UNION 
            select city,uid,start_time as dt, -1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)

2 将各个时间段的瞬时人数进行计算并保证时间在2021年10月

  select city, 
        sum(inc) over(partition by city order by dt Asc,inc desc) as uv 
        from
        (
            select city,uid,event_time as dt , 1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)
            UNION 
            select city,uid,start_time as dt, -1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)
         ) temp where date_format(dt,'%Y-%m') = '2021-10' 

3 按照城市分组，再按照最大等车人数升序排序，相同时按城市升序排序。

select city,
    max(uv) max_wait_uv 
    from (
        select city, 
        sum(inc) over(partition by city order by dt Asc,inc desc) as uv 
        from
        (
            select city,uid,event_time as dt , 1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)
            UNION 
            select city,uid,start_time as dt, -1 inc
            from tb_get_car_record 
            inner join tb_get_car_order 
            using(order_id,uid)
         ) temp where date_format(dt,'%Y-%m') = '2021-10' 
    ) temp2 
    group by city
    order by max_wait_uv,city