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

问题关键，弄清等车时间。这里有几个时间维度。首先是等车开始时间。这个是唯一的，event_time，

随后是取消时间，这里有好几种情况，情况一：一直无司机接单、超时、途中用户主动取消打车，记录打车结束时间，这里应该是end_time, 情况二：乘客上车前，乘客或司机点击取消订单，会将打车订单的finish_time填充为取消时间，

取消的时间段确定后，就可以采用编码的思路来解题了，也即在event_time构造新列全为1，在取消的时间段构造新列全为-1，之后合并后，使用窗口函数，对事件进行排序后累加。这样一遇到打车开始时间event_time就加一，遇到取消时间段就减一；

注意：同一时刻有人停止等车，也有人开始等车的话，等车人数先增加后减少。也即时间相同，先计算增加，然后再减少，那么增加的列必须排前边，sum(t1.num) over(partition by t1.city order by t1.time1, num desc) as wait_uv。新增一个排序字段即可

with temp as (select t1.city,
    sum(t1.num) over(partition by t1.city order by t1.time1, num desc) as wait_uv
from (
    select cr.city,
        cr.event_time time1,
        1 as 'num'
    from tb_get_car_record cr
    union all
    select cr.city,
        if(co.fare is null, co.finish_time, co.start_time) time1,
        # ifnull(co.start_time, co.finish_time) time1,
        -1 as 'num'
    from tb_get_car_order co
    join tb_get_car_record cr on co.order_id=cr.order_id
    # union all
    # select cr.city,
    #     cr.end_time time1,
    #     -1 as 'num'
    # from tb_get_car_record cr
    # where cr.order_id is null
) t1
where t1.time1 between '2021-10-01' and '2021-11-01'
)
select temp.city,
    max(temp.wait_uv) as max_wait_uv
from temp
group by temp.city
order by max_wait_uv asc