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

with tmp as (SELECT city,event_time uv_time,1 AS uv FROM  tb_get_car_record 
UNION ALL
SELECT city,end_time uv_time,-1 AS uv FROM  tb_get_car_record WHERE order_id IS NULL #接单前取消
UNION ALL
SELECT city,IFNULL(start_time,finish_time) uv_time,-1 AS uv FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id))

select city,max(uvcnt) as max_wait_uv from (select city,
    sum(uv) over(partition by city order by uv_time,uv desc) as uvcnt
from tmp where left(uv_time,7) = '2021-10') t 
group by city order by max(uvcnt),city;

首先明确等车时间的定义：

• 如果订单是正常状态，那么event_time uv=1;
• 如果司机接单前取消订单即order_id 为null，则用end_time表示 uv=-1；
• 如果司机接单后取消，那么start_time可能为null ,则用IFNULL(start_time,finish_time)表示 uv=-1

瞬时问题 :

• 根据city分区 按照dt正序 uv降序进行sum求和 展示每天的最大同时在线人数
• 根据city分组 求uvcnt最大时的city和max(uvcnt