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

select
city,
max(cnt_all) max_wait_uv
from 
(
    select
    *,
    sum(cnt) over(partition by city,date(dt) order by dt,cnt desc) cnt_all
    from
    (
	    select-- 用户开始打车
	    a.city,a.event_time dt,1 cnt
	    from  tb_get_car_record a
		where date_format(a.event_time,'%Y%m')='202110'
	    union all
	    select-- 司机接上乘客
	    a.city,b.start_time dt ,-1 cnt
	    from  tb_get_car_record a
	    left join tb_get_car_order b
	    on a.order_id=b.order_id
	    where  start_time is not null
		and  date_format(b.start_time,'%Y%m')='202110'
        union all
        select-- 上车前取消
	    a.city,b.finish_time dt ,-1 cnt
	    from  tb_get_car_record a
	    left join tb_get_car_order b
	    on a.order_id=b.order_id
	    where  start_time is null
		and  date_format(b.finish_time,'%Y%m')='202110'
	) tmp
) a
group by city
order by max_wait_uv

sum(cnt) over(partition by city,date(dt) order by dt,cnt desc) cnt_all

有点蒙了，代码和别人的都一样，就差了order by dt,**cnt** desc

应该是同一时刻先来后走，所以要用cnt倒序排序

果然大家的代码都对cnt排序了！！！！搞了50分钟！！！