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

WITH t1 AS(
	SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt 
    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一样按照城市升序