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

# 开始打车时间：event_time
# 结束等车状态情况及等待结束的时间：
#  1. 用户没打到车取消打车：order_time(没有start_time,finish_time)
#  2. 用户打到车但取消等待：finish_time(没有start_time)
#  3. 用户打到车上车前：start_time(有fare)

WITH t AS(SELECT
city,
event_time AS dt,
1 AS diff
FROM tb_get_car_record
WHERE event_time LIKE '2021-10%'
UNION ALL 
SELECT
city,
order_time AS dt,
-1 AS diff
FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
WHERE event_time LIKE '2021-10%' AND finish_time IS NULL
UNION ALL 
SELECT
city,
finish_time AS dt,
-1 AS diff
FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
WHERE event_time LIKE '2021-10%' AND start_time IS NULL AND finish_time IS NOT NULL
UNION ALL 
SELECT
city,
start_time AS dt,
-1 AS diff
FROM tb_get_car_order JOIN tb_get_car_record USING(order_id)
WHERE event_time LIKE '2021-10%' AND fare IS NOT NULL),

t2 AS (SELECT
city,
SUM(diff) OVER(PARTITION BY city ORDER BY dt,diff DESC) AS uv
FROM t)

SELECT
city,
MAX(uv) AS max_wait_uv
FROM t2
GROUP BY city
ORDER BY max_wait_uv,city;