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

-- 1、所有数据（完成、用户取消、司机取消等。注意：LEFT JOIN ）
WITH t AS
(
SELECT city,
event_time in_t,    -- 开始打车时间
IF(fare IS NULL,end_time,start_time) out_t  -- 停止等待时间
FROM tb_get_car_order o 
LEFT JOIN tb_get_car_record r ON o.order_id=r.order_id
WHERE DATE_FORMAT(event_time,'%Y%m')='202110'
),
-- 2、将时间拆分合并，开始打车时间tag=1，停止等待时间tag=-1
t2 AS
(
SELECT city,in_t dt,1 tag FROM t 
UNION ALL 
SELECT city,out_t dt,-1 tag FROM t
)
-- 3、按城市分组取出最大值
SELECT city,MAX(cnt) max_wait_uv
FROM 
(   -- 3、根据城市、日期分区求和（各时间点的等待人数）
    SELECT city,SUM(tag)OVER(PARTITION BY city,DATE(dt) ORDER BY dt,tag DESC) cnt FROM t2
) t3
GROUP BY city
ORDER BY max_wait_uv,city