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

-- 自关联解决
with t_base as 
(
    select
        t1.uid,
        date_format(t1.event_time,'%Y-%m-%d') as dt, -- 日期
        t1.city,
        t1.event_time,
        case when t2.start_time is null then t2.finish_time 
             else t2.start_time
             end as start_time -- 用finish_time兜底start_time。话说为啥mysql不支持nvl函数啊
    from tb_get_car_record t1
    join tb_get_car_order t2
    on t1.order_id = t2.order_id
    where date_format(t1.event_time,'%Y-%m') = '2021-10'
)
,t1 as
(
	select
	  t1.uid,
	  t1.dt,
	  t1.city,
	  count(distinct t2.uid) as wait_uv
  from t_base t1
  join t_base t2 --自关联，计算等待人数
  on t2.start_time between t1.event_time and t1.start_time
  group by t1.uid,t1.dt,t1.city
)

select
    city,
    max(wait_uv) as max_wait_uv
from t1
group by city --通过城市聚合，选出最大等待人数

解题思路：

使用自关联，以某人的结束等待时间作为一个节点，统计在这个人等车阶段有多少人同时等车。通过筛选条件：某人结束等待时间在当前人的等待时间区间中。

然后根据日期和city聚合，就可以看到当前节点的等待人数，再通过city聚合选出对大值即可。