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

WITH a AS (
    SELECT 
        city, 
        event_time AS checktime, 
        DATE(event_time) AS dt,   -- 提取日期
        1 AS uv
    FROM tb_get_car_record
    WHERE DATE_FORMAT(event_time, '%Y-%m') = '2021-10'
    
    UNION ALL
    
    SELECT 
        city, 
        end_time, 
        DATE(end_time) AS dt,     -- 提取日期
        -1 AS uv
    FROM tb_get_car_record
    WHERE DATE_FORMAT(end_time, '%Y-%m') = '2021-10' AND order_id IS NULL
),
b AS (   
    SELECT 
        tcr.city, 
        CASE 
            WHEN tco.start_time IS NULL THEN tco.finish_time 
            ELSE tco.start_time 
        END AS jieshushijian,
        DATE(CASE 
            WHEN tco.start_time IS NULL THEN tco.finish_time 
            ELSE tco.start_time 
        END) AS dt               -- 提取日期
    FROM tb_get_car_record tcr
    LEFT JOIN tb_get_car_order tco USING(order_id)
    WHERE DATE_FORMAT(tcr.end_time, '%Y-%m') = '2021-10'
),
c AS (
    SELECT 
        city, 
        dt,
        SUM(uv) OVER(PARTITION BY city, dt ORDER BY checktime, uv DESC) AS wait
    FROM (
        SELECT city, dt, checktime, uv FROM a
        UNION ALL
        SELECT city, dt, jieshushijian AS checktime, -1 AS uv FROM b
    ) bi
)
SELECT 
    city, 
    MAX(wait) AS max_wait_uv
FROM c
GROUP BY city, dt
ORDER BY dt, city   -- 按日期和城市排序，也可按 max_wait_uv 排序