一、知识点总结与拓展

感觉本题主要的考点是对瞬时在线用户数这种业务的理解。用到SQL的方法相对简单，只要是union的用法，除此之外还用大了ifnull()函数。
  • union 去重连接；union all 全连接不去重
  • ifnull(表达式1,表达式2)：如果表达式1为空则返回表达式2，不为空则返回表达式1
  • 瞬时UV的计算方法：进入事件记作UV=1，离开事件记作UV=-1，使用窗口函数SUM(uv)OVER(ORDER BY 事件，uv DESC)进行累加。

二、题目解读

题目：请统计各个城市在2021年10月期间，单日中最大的同时等车人数。
这是一道典型的统计同时在线用户数的题，和统计直播间在线用户数的逻辑是一样的。进入的用户定义uv为1，离开的用户定义uv为-1。

因而，需要对用户进入和离开的时间进行定义。
进入时间：event_time,开始打车的时间即为等车开始。
离开时间：有3种情况
  • 状态1：司机接单前取消，则没有生成order_id,这种情况 order_id IS NULL 记录end_time
  • 状态2：司机接单后取消，则没有上车时间，start_time IS NULL 记录 finish_time
  • 状态3：正常上车，记录start_time,start_time IS NOT NULL
  • 状态2和3可以直接使用IFNULL()合并，IFNULL(start_time,finish_time) 如果start_time空则返回finish_time，不空则start_time
定义完用户进入等车和离开等车这两种事件之后，关联所有表格，使用窗口函数排序累加即可。

  • 具体的状态的数据可以看下面的图




三、解题步骤

解题思路：整个解题过程只要3个步骤，首先建立一张子表，对用户进入等车状态和离开等车状态进行定义后进行表并联；接着使用窗口函数对每个城市的等车状态进出uv进行累加，最后取出每个城市最大的UV即可。
1）建立子表，对用户进入等车状态和离开等车状态进行定义后进行表并联
  • uv为1，用户进入打车状态 
SELECT city,event_time uv_time,1 AS uv FROM tb_get_car_record
  • uv为-1，状态1：司机接单前取消，则没有生成order_id,这种情况 order_id IS NULL 记录end_time 
SELECT city,end_time uv_time,-1 AS uv FROM  tb_get_car_record WHERE order_id IS NULL #接单前取消
  • uv为-1，状态2：接单后取消或者用户正常上车 
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)#接单后取消或上车
  • 建立子表：使用union all 全关联 
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)#接单后取消或上车

2）使用窗口函数对每个城市的等车状态进出uv进行累加
  • 统计2021年10月，每个城市的瞬时UV情况 
SELECT city,SUM(uv)OVER(PARTITION BY city ORDER BY uv_time,uv DESC) AS uv_cnt
DATE_FORMAT(uv_time,'%Y%m')='202110'
3）最后取出每个城市最大的UV，排序先按照uv升序，uv一样按照城市升序。
  • 完整代码 
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一样按照城市升序