题解 | #各城市最大同时等车人数#
各城市最大同时等车人数
http://www.nowcoder.com/practice/f301eccab83c42ab8dab80f28a1eef98
打车时，也即order_time时记录+1 分析等车的两种情况 第一种，上了车，上车时start_time记录-1 第二种，未上车，finish_time记录-1 注意不要用end_time，因为end_time记录的是打车结束时间和司机接单时间 得到各时间段等车的人数
with t1 as
(select city, event_time dt, order_id, 1 diff
from tb_get_car_record
where date_format(event_time,'%Y-%m')='2021-10'
union
select city, start_time dt, t1.order_id, -1 diff
from tb_get_car_record t1
join tb_get_car_order t2
using(order_id)
where date_format(start_time,'%Y-%m')='2021-10'
and start_time is not null
union
select city, finish_time dt, t1.order_id, -1 diff
from tb_get_car_record t1
join tb_get_car_order t2
using(order_id)
where date_format(start_time,'%Y-%m')='2021-10'
and start_time is null)
再汇总求和 sum(diff) over(partition by city,date(dt) order by dt,diff desc) cnt (按照城市和日期分区，因为是统计截止某时间段的等车人数且要求先加后减去，所以用order by dt,diff desc） 并取最大值max(cnt)
select city,max(cnt) max_wait_uv from
(select city, dt, sum(diff) over(partition by city,date(dt) order by dt,diff desc) cnt
from t1)t2
group by city
order by max_wait_uv, city
最终的sql
with t1 as
(select city, event_time dt, order_id, 1 diff
from tb_get_car_record
where date_format(event_time,'%Y-%m')='2021-10'
union
select city, start_time dt, t1.order_id, -1 diff
from tb_get_car_record t1
join tb_get_car_order t2
using(order_id)
where date_format(start_time,'%Y-%m')='2021-10'
and start_time is not null
union
select city, finish_time dt, t1.order_id, -1 diff
from tb_get_car_record t1
join tb_get_car_order t2
using(order_id)
where date_format(start_time,'%Y-%m')='2021-10'
and start_time is null)
select city,max(cnt) max_wait_uv from
(select city, dt, sum(diff) over(partition by city,date(dt) order by dt,diff desc) cnt
from t1)t2
group by city
order by max_wait_uv, city
