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

### 问题分解：

• 计算截止当前时刻最大在等车人数（生成子表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;
``````