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

with t as(
select r.uid, city,event_time,start_time
from tb_get_car_record r
join tb_get_car_order o on r.order_id = o.order_id
where date_format(event_time,'%Y-%m') = '2021-10'
)


select city,max(cum_flag)  as 'max_wait_uv'

from

(select uid,city, sum(flag)over(PARTITION BY city Order by time asc, flag DESC)  cum_flag
    from
    (
    select uid,city, event_time as 'time', 1 as 'flag' from t
    union
    select uid, city, start_time as 'time', if (start_time is not NULL,-1,0)  'flag' from t
    Order by time asc, flag desc
    ) a
)b

group by city
order by  max_wait_uv asc,city asc;

• 主逻辑，依靠窗口函数记录当前的最大等待人数
• 首先先拼接两个表格，然后子查询event_time字段，并在flag赋值为1，当遇到start_time也就是结束等待的时，将flag设置为-1（注意null的情况），通过累加，就能算出算出每个‘time’的等待人数，最后分组查询求max即可