题解 | #各城市最大同时等车人数#
各城市最大同时等车人数
http://www.nowcoder.com/practice/f301eccab83c42ab8dab80f28a1eef98
一、知识点总结与拓展
感觉本题主要的考点是对瞬时在线用户数这种业务的理解。用到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一样按照城市升序
SQL解题集 文章被收录于专栏
这是牛客SQL相关的解题集
查看5道真题和解析
