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

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一样按照城市升序

union all不去重进行上下拼接，将日期拼接到一起，添加一列，进入等车时作为+1，离开等车时-1，根据时间排序，然后开窗sum聚合，此时得到的就是每个城市的瞬时的uv，然后按照条件进行分组聚合排序等操作