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

-- 建立子表,用union all 全连接
with t1 as(
SELECT -- 用户进入打车状态 记为1重命名为uv
       city,
       event_time uv_time,
       1 uv -- 进入打车状态
FROM tb_get_car_record
    UNION ALL
SELECT -- 司机接单前取消，则没有生成order_id,这种情况 order_id IS NULL 记录end_time
       city,
       end_time uv_time,
       -1 uv -- 结束等车,记为 -1 为uv
FROM tb_get_car_record
WHERE order_id IS NULL
    UNION ALL
SELECT -- 司机接单后取消或正常上车
       city,
       ifnull(start_time,finish_time) uv_time,
       -1 uv -- 结束打车后记录结束时间 记为-1
FROM tb_get_car_order
JOIN tb_get_car_record USING(order_id)
),t2 AS
(SELECT
       city, -- 下边排序一定要有根据uv降序排序,不然会报错,先进后出
       sum(uv) OVER(PARTITION BY city ORDER BY uv_time,uv desc) AS uv_cnt
FROM t1
WHERE date_format(uv_time,"%Y-%m") = '2021-10')
SELECT
       city,
       max(uv_cnt) max_wait_uv
FROM t2
GROUP BY city -- 结果按各城市最大等车人数升序排序，相同时按城市升序排序。
ORDER BY max_wait_uv ASC ,city ASC ;