各城市最大同时等车人数

明确题意：

统计各个城市在2021年10月期间，单天里最大的同时等车人数。结果按最大等车人数升序排序，相同时按城市升序排序。

等车指从开始打车起，直到取消打车或取消等待或上车止的这段时间里用户的状态。如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。

问题分解：

• 计算截止当前时刻最大在等车人数（生成子表t_city_cur_max）：
  • 计算每个时刻人数变化量（生成子表t_uv_at_time）：
    • 记录等车人数增加（有人开始打车）：event_time as at_time, 1 as wait_uv
    • 记录等车人数减少（上车了、打到车取消、没打到车取消）：
      • 关联打车记录表和订单表：tb_get_car_record LEFT JOIN tb_get_car_order USING(order_id)
      • 减少：COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv
    • 合并上述记录：UNION ALL
  • 筛选时间窗：WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10"
  • 定义按城市和日期分区按时刻排序的窗口（先增加后减少，所以uv倒排）：WINDOW wd_city_date as (PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC)
  • 计算当前最大等车人数：SUM(wait_uv) over(wd_city_date) as current_max
• 按城市分组：GROUP BY city
• 计算各城市最大等待人数：MAX(current_max) as max_wait_uv

细节问题：

• 表头重命名：as
• 按最大等车人数、城市升序排序：ORDER BY max_wait_uv, city

完整代码：

SELECT city, MAX(current_max) as max_wait_uv
FROM (
    SELECT city, SUM(wait_uv) over(wd_city_date) as current_max
    FROM (
        SELECT city, event_time as at_time, 1 as wait_uv FROM tb_get_car_record
        UNION ALL
        SELECT city, COALESCE(start_time, finish_time, end_time) as at_time, -1 as wait_uv
        FROM tb_get_car_record
        LEFT JOIN tb_get_car_order USING(order_id)
    ) as t_uv_at_time
    WHERE DATE_FORMAT(at_time, "%Y-%m") = "2021-10"
    WINDOW wd_city_date as (PARTITION BY city, DATE(at_time) ORDER BY at_time, wait_uv DESC)
) as t_city_cur_max
GROUP BY city
ORDER BY max_wait_uv, city;