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

• 和SQL8 每篇文章同一时刻最大在看人数想法一样！类似题目，只是更复杂了些

1. 首先，开始等待时间都是event_time，利用diff=1来记录进来人数。
2. 而结束等待时间分为三种情况：①接单前取消订单，则order id为空，end_time即为结束等待时间。②正常上车，则start_time为结束等待时间。③接单后取消，则start_time为空，finish_time为结束等待时间
3. 用ifnull来整合start_tim和finish_time
4. 利用窗口函数来记录每个时间段的最大同时等车人数

with t1 as(select
	city,
    dt,
    sum(diff) over (partition by city order by dt,diff desc) as diff
from(
select city,event_time as dt,1 as diff from tb_get_car_record
union all 
select city,end_time as dt,-1 as diff from tb_get_car_record where order_id is null
union all
select city,ifnull(start_time,finish_time),-1 as diff from tb_get_car_record left join tb_get_car_order using(uid))t where date_format(dt,'%Y-%m')='2021-10')

select city,max(diff) as max_wait_uv from t1 group by city order by max_wait_uv,city