总代码

with t1 as (
    SELECT city,type
        ,rank() over(partition by city order by event_time,type desc) as 'rnk'
    from (
        SELECT city,event_time,1 as 'type' from tb_get_car_record
        union all 
        SELECT city
            ,( case when start_time is null then finish_time
                    when order_id is null then end_time
                    else start_time end
            ) as 'event_time'
            ,-1 as 'type'
        from tb_get_car_record as A
        left join tb_get_car_order as B
        using(order_id)
    ) as temp
    where date_format(event_time,'%Y-%m') = '2021-10'
)
select city 
    ,max(num) as 'max_wait_uv'
from (
    select city,type
        ,( select sum(type)
          from t1 as B where B.city = A.city 
          and B.rnk <= A.rnk
        ) as 'num'
    from t1 as A
) as t2
group by city order by max_wait_uv

思路分析

1.构造临时表

1. 遇到同时在线问题，基本都是把开始时间和结束时间单独取出后使用union all进行合并（union 会去重）
2. 并新增列（如命名为type）分别赋值为1，-1以区分人数加减
3. 若要求如【如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少】，则需在新增一列排序，以方便计算人数（如果后面计算人数只是用时间来比较而不是用序号，遇到同个时间点有加有减则会计算错误）

本题中的开始时间比较容易：为用户开始打车时间即event_time

结束时间：

• 若司机接单后取消订单则为，finish_time
• 若用户自己取消则为，end_time
• 若司机成功接单则为，start_time

代码及该部分结果（图中为了便于理解rnk作用还select了event_time）如下

with t1 as (
    SELECT city,type
        ,rank() over(partition by city order by event_time,type desc) as 'rnk'
    from (
        SELECT city,event_time,1 as 'type' from tb_get_car_record
        union all 
        SELECT city
            ,( case when start_time is null then finish_time
                    when order_id is null then end_time
                    else start_time end
            ) as 'event_time'
            ,-1 as 'type'
        from tb_get_car_record as A
        left join tb_get_car_order as B
        using(order_id)
    ) as temp
    where date_format(event_time,'%Y-%m') = '2021-10'
)
select * from t1

2.计算人数

有了临时表后接下来就容易了，只需要进行分组计算（把小于当前行的序号的type进行累计求和）即可

select city,max(num) as 'max_wait_uv'
from (
    select city,type
        ,( select sum(type) from t1 as B 
          where B.city = A.city and B.rnk <= A.rnk
        ) as 'num'
    from t1 as A
) as t2
group by city order by max_wait_uv