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

• 状态较为多样。
• 发起订单：1；
• 发起订单-司机未接单-取消订单：不会出现order_id，-1
• 发起订单-司机接单-司机\乘客取消订单：没有start_time,存在finish_time，-1
• 发起订单-司机接单-乘客成功上车：存在start_time， -1

WITH t1 AS(
    SELECT city
            , event_time AS dt
            , 1 AS statu
    FROM tb_get_car_record
    
    UNION ALL
    SELECT city
            , end_time AS dt
            , -1 AS statu
    FROM tb_get_car_record
    WHERE order_id IS NULL

    UNION ALL
    (
    SELECT city
            , IFNULL(start_time, finish_time) AS dt
            , -1 AS statu
    FROM tb_get_car_order
    JOIN tb_get_car_record USING(order_id)
    
    )
)

SELECT city
        ,MAX(wait_uv) AS max_wait_uv
FROM (
    SELECT city
            ,SUM(statu) OVER(PARTITION BY city, DATE(dt) ORDER BY dt, statu DESC) wait_uv
    FROM t1
    WHERE DATE_FORMAT(dt, '%Y%m') = '202110'
) data1
GROUP BY city
ORDER BY max_wait_uv