一、定义等待时间

1、start_wait_time开始等待时间

始终是event_time

2、end_wait_time结束等待时间

有哪些情况呢？

（1）司机接单【前】乘客取消订单

【乘客开始打车event_time→取消订单end_time】 （order_id 为 null）

（2）司机接单【后】乘客取消订单

【乘客开始打车event_time→取消订单finish_time】 （start_time  为 null）

（3）正常完成订单

【乘客开始打车event_time→乘客上车start_time】

二、出错

使用sum(event_type) over (partition by city, date(event_time) order by event_time) 计算实时等车人数，但：如果同一时间点既有用户开始等车（+1）又有用户结束等车（-1），MySQL 的窗口函数可能无法保证严格的“先加后减”顺序。

三、解决方法

在 ORDER BY 中增加更细粒度的时间排序sum(event_type) over (partition by city,date(event_time) order by event_time,event_type DESC）

四、完整代码

with
    base as (
        select
            city,
            tb_get_car_record.uid,
            date(event_time) as day,
            event_time as start_wait_time,
            case
                when order_id is null then end_time
                when start_time is null then finish_time
                else start_time
            end as end_wait_time
        from
            tb_get_car_record
            join tb_get_car_order using (order_id)
        where
            year(event_time) = 2021
            and month(event_time) = 10
    ),
    temp as (
        select
            city,
            uid,
            start_wait_time as event_time,
            '1' as event_type
        from
            base
        union all
        select
            city,
            uid,
            end_wait_time as event_time,
            '-1' as event_type
        from
            base
        order by
            city,
            uid,
            event_time
    )
select
    city,
    max(CAST(wait_uv AS unsigned)) as max_wait_uv
from
    (
        select
            city,
            date(event_time) as event_time,
            sum(event_type) over (
                partition by
                    city,
                    date(event_time)
                order by
                    event_time,
                    event_type desc
            ) as wait_uv
        from
            temp
    ) as t1
group by
    city
order by
    max_wait_uv,
    city