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

1、根据打车时间、上车或者取消订单时间进行union 联立

2、按照city维度，order by dt,diff desc,对diff进行开窗统计

sum(diff) over(partition by city order by dt,diff desc)

3、最外层进行max聚合操作

其中一个小细节在于需要筛选是用取消订单时间还是上车时间

(case when fare is null then finish_time else start_time end)as dt,-1 diff

SELECT
city,
  MAX(instant_viewer_cnt) max_wait_uv
FROM (
SELECT
    city,
SUM(diff) OVER(PARTITION BY city ORDER BY dt, diff DESC) instant_viewer_cnt
  FROM (
SELECT city, r.uid,event_time as dt,1 diff
from tb_get_car_record r join tb_get_car_order o on r.order_id=o.order_id
      where date_format(event_time,"%Y%m")="202110"
union ALL
SELECT city, r.uid, (case when fare is null then finish_time else start_time end)as dt,-1 diff
from tb_get_car_record r join tb_get_car_order o on r.order_id=o.order_id
  where date_format(event_time,"%Y%m")="202110")a)b
group by city
order by max_wait_uv,city