题解 | SQL 179. 各城市最大同时等车人数

WITH t1 AS(
	SELECT 
		city,
		SUM(uv) OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt #每个城市等车瞬时UV
    FROM (
    	SELECT 
			city,
			event_time uv_time,
			1 AS uv 
			FROM  tb_get_car_record #进入等车状态
		UNION ALL
		SELECT 
			city,
			end_time uv_time,
			-1 AS uv 
			FROM  tb_get_car_record 
			WHERE order_id IS NULL #接单前取消
		UNION ALL
		SELECT 
			city,
			IFNULL(start_time,finish_time) uv_time,
			-1 AS uv 
			FROM tb_get_car_order 
			LEFT JOIN tb_get_car_record USING(order_id) #接单后取消或上车
    )AS t 
	WHERE DATE_FORMAT(uv_time,'%Y%m')='202110' #2021年10月
)
SELECT 
	city,
	MAX(uv_cnt) max_wait_uv 
	FROM t1 
	GROUP BY city 
	ORDER BY max_wait_uv,city;#排序先按照uv升序，uv一样按照城市升序
	
# 这道题关键在于联想到实时统计uv的场景！