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

# 时间线扫描法
# 先合并所有表格，并且注意，可能存在用户取消订单情况，所以要用left join,并且筛选出2021年10月订单
with t0 as(
select r.uid,r.order_id,city, event_time,end_time, driver_id,order_time, start_time, finish_time 
from tb_get_car_record r
left join tb_get_car_order o on r.order_id = o.order_id
where date_format(event_time,"%Y-%m") = "2021-10"
),

# 开始等车时间： 所有的情况下，开始时间都是event_time-打车时间
# 开始等车的人次被计作 1，如果有人开始等车，则 +1
start_time as (
select city,uid,order_id, 1 as mark,event_time as time from t0
),


# 停止等车时间：
# 根据题目叙述来梳理，可以画一个图来看，
# 如果打车成功，order_time 和start_time 都不为空，停止等车时间就是上车时间start_time; 
# 如果无人接单，order_time 和start_time 都为空，停止等车时间就是订单结束时间end_time;
# 如果接单了但取消订单了，那么order_time不为空， start_time 都为空，停止等车时间就是订单完成时间finish_time.
# 停止等车的人次别计作 -1， 如果有人停止等车，则-1
end_wait_time as(
select city, uid, order_id, -1 as mark, 
       case when order_time is not null and start_time is not null then start_time
            when order_time is null and start_time is null then end_time
            when order_time is not null and start_time is null then finish_time
            else "none" end as time
from t0
),


# 时间线扫描，合并所有的动作
time_line as(
select * from start_time
union all 
select * from end_wait_time
),

# 按照时间，和动作排序，在每个时间点，计算从一开始到现在，上车人次和下车人次之和，就是这个时间点上的同时在等车的人数。最后，形成每个时间点上的同时等车人数
f as(
select *,
       sum(mark) over(order by time rows between unbounded preceding and current row) as cnt
from time_line order by time, uid
)

# 根据城市分组，选择每个城市最大的同时在线人数。
select city, max(cnt) as max_wait_uv
from f
group by city
order by max_wait_uv, city