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

WITH base_record AS (
    # 圈出10月份的数据，并确定好开始和结束时间
    SELECT 
        r.city,
        r.event_time start_wait,
        # COALESCE替代3层CASE WHEN
        COALESCE(o.start_time, o.finish_time, r.end_time) end_wait
    FROM tb_get_car_record r
    LEFT JOIN tb_get_car_order o 
    ON r.order_id = o.order_id
    WHERE DATE(r.event_time) BETWEEN '2021-10-01' AND '2021-10-31'
),
wait_log AS (
    # 打标
    SELECT city, start_wait dt, 1 AS cnt FROM base_record
    UNION ALL
    SELECT city, end_wait, -1 FROM base_record
),
running_cnt AS (
    # 计算并发
    SELECT 
        city,
        SUM(cnt) OVER(PARTITION BY city ORDER BY dt ASC, cnt DESC) concurrent_uv # 不用按日期截断，否则跨天订单会出问题
    FROM wait_log
)
SELECT 
    city, 
    MAX(concurrent_uv) max_wait_uv
FROM running_cnt
GROUP BY city
ORDER BY max_wait_uv ASC, city ASC;

开始时间肯定是event_time，然后结束时间，如果record表中的order_id为空，那么说明没有司机接单，终止时间是record表中的end_time，如果order表中的strat_time为空，那么就是接单后取消订单，终止时间是finish_time，剩下的就是正常的，应该为start_time