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

select city,max(uv_cnt) as max_wait_uv
from(
select city,sum(uv)over(partition by city order by uv_time,uv desc) as uv_cnt
from(
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 #如果上车前用户或者司机取消，记录finshtime，否则记录starttime作为上车时间
FROM tb_get_car_order LEFT JOIN tb_get_car_record USING(order_id)) as t1
WHERE DATE_FORMAT(uv_time,'%Y%m')='202110') as t2
group by city
order by max_wait_uv,city

瞬时uv的问题，注意三种情况：用户在接单前取消、用户或司机在上车前取消、用户上车，然后按照城市最后分类聚合统计即可