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

WITH t as(
SELECT event_time AS wait_time,	
1 AS flag,city
FROM tb_get_car_record AS gcr
JOIN tb_get_car_order AS gco
ON gcr.order_id = gco.order_id
UNION ALL
SELECT (CASE 
WHEN gcr.order_id IS NULL THEN end_time
WHEN start_time IS NULL THEN finish_time
ELSE start_time
END)AS wait_time,	
-1 AS flag,city
FROM tb_get_car_record AS gcr
JOIN tb_get_car_order AS gco
ON gcr.order_id = gco.order_id
),
t1 as(
SELECT city,
SUM(flag)OVER(PARTITION BY city,DATE(wait_time) ORDER BY wait_time ASC,flag DESC) AS wait_nv 
FROM t
WHERE YEAR(wait_time) = 2021 AND MONTH(wait_time) = 10
)


SELECT city,MAX(wait_nv) AS max_wait_uv 
FROM t1
GROUP BY city
ORDER BY max_wait_uv ASC,city ASC;

需要是UNION ALL,因为如果存在重复记录，比如有两个用户同时打车。

如果不采用UNION ALL,而是采用UNION,需要在with查询中加入uid字段，这样保证两条记录不一样，从而不会被删去。