牛客SQL大厂面试真题——出行场景（滴滴打车）

昨天我们学习了Mysql的函数，相信大家已经了解到差不多了，接下来跟随我利用牛客网的上的真题对我们学习成果进行检验吧！

上一期博客传送门:Mysql常用函数总结

文章目录

• • • 1、2021年国庆在北京接单3次及以上的司机统计信息
    • 2、有取消订单记录的司机平均评分
    • 3、每个城市中评分最高的司机信息
    • 4、国庆期间近七日日均取消订单量
    • 5、工作日各时段叫车量、等待时间和调度时间
    • 6、各城市最大同时等车人数

现有两张表格：

• 用户打车记录表（ tb_get_car_record）

• 打车订单表（tb_get_car_order）

场景逻辑说明：用户提交打车请求后，在用户打车记录表生成一条打车记录，order_id-订单号设为null；当有司机接单时，在打车订单表生成一条订单，填充order_time-接单时间及其左边的字段，start_time-开始计费的上车时间及其右边的字段全部为null，并把order_id-订单号和order_time-接单时间（end_time-打车结束时间）写入打车记录表；若一直无司机接单，超时或中途用户主动取消打车，则记录end_time-打车结束时间。若乘客上车前，乘客或司机点击取消订单，会将打车订单表对应订单的finish_time-订单完成时间填充为取消时间，其余字段设为null。当司机接上乘客时，填充订单表中该start_time-开始计费的上车时间。当订单完成时填充订单完成时间、里程数、费用；评分设为null，在用户给司机打1~5星评价后填充。

具体题目如下：

1、2021年国庆在北京接单3次及以上的司机统计信息

请统计2021年国庆7天期间在北京市接单至少3次的司机的平均接单数和平均兼职收入.（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。输出格式：city avg_order_numavg_income思路：1、先把国庆期间在北京市接单次数>=3的司机id及其接单数和收入计算出来2、对所有司机的接单数和收入求平均值

select city      ,round(avg(个人订单数),3) avg_order_num      ,cast(avg(个人费用) as decimal(10,3)) avg_incomefrom (      select t2.driver_id    , t1.city    , count(t2.order_id) as 个人订单数    , sum(fare) as 个人费用      from tb_get_car_record t1 left join tb_get_car_order t2 on t1.uid = t2.uid and t1.end_time = t2.order_time      WHERE DATE_FORMAT(t2.order_time,'%Y-%m-%d') BETWEEN '2021-10-01' and '2021-10-07' and t1.city = '北京'GROUP BY t2.driver_id HAVING count(t2.order_id) >= 3 ) a GROUP BY city 

2、有取消订单记录的司机平均评分

请找到2021年10月有过取消订单记录的司机，计算他们每人全部已完成的有评分订单的平均评分及总体平均评分，保留1位小数。先按driver_id升序输出，再输出总体情况。输出格式：driver_idavg_grade思路：1、先找到2021年10月有过取消订单记录的司机2、计算每人的平均评分（只算有评分订单的）3、计算总体评分4、将每个人的和总体连接起来

with a as(select driver_idfrom tb_get_car_orderwhere  order_time is not null and start_time is null and DATE_FORMAT(order_time,'%Y-%m') = '2021-10'); # 生成临时表，符合条件的司机id(select driver_id,cast(avg(grade) as decimal(10,1)) as avg_gradefrom tb_get_car_orderwhere driver_id in (select driver_id from a) and grade is not nullGROUP BY driver_idORDER BY driver_id)   #每个司机的平均分union all (select '总体' as driver_id,round(avg(grade),1) avg_gradefrom tb_get_car_order where driver_id in (select driver_id from a) and grade is not null) #所有司机的平均分

3、每个城市中评分最高的司机信息

问题：请统计每个城市中评分最高的司机平均评分、日均接单量和日均行驶里程数。注：有多个司机评分并列最高时，都输出。   平均评分和日均接单量保留1位小数，日均行驶里程数保留3位小数，按日均接单数升序排序。 输出格式：citydriver_idavg_gradeavg_order_numavg_mileage  思路：1、找出每个城市中平均评分最高的司机id2、根据城市和司机id分组，求其平均评分、日均接单量和日均行驶里程数

with b as (select driver_idfrom (select t2.city     , t1.driver_id     , rank() over(partition by t2.city order by avg(t1.grade) desc) rkfrom tb_get_car_order t1 left join tb_get_car_record t2 on t1.uid = t2.uid and t1.order_id = t2.order_idgroup by t2.city,driver_id) awhere rk = 1); # 找出司机idselect t2.city      ,t1.driver_id      ,round(avg(grade),1) avg_grade      ,round(count(distinct order_id)/count(distinct date(start_time)),1) avg_order_num # 日均接单量=总订单数/天数 distinct去重      ,round(sum(mileage)/count(distinct date(start_time)),3) avg_mileage # 日均行驶里程数=总里程/天数from tb_get_car_order t1 left join tb_get_car_record t2 on t1.uid = t2.uid and t1.order_id = t2.order_idwhere driver_id in (select driver_id from b) and t1.order_time is not nullgroup by t2.city, t1.driver_id

4、国庆期间近七日日均取消订单量

请统计国庆头3天里，每天的近7日日均订单完成量和日均订单取消量，按日期升序排序。结果保留2位小数思路：1、将各个订单完成时间与10月1、2、3号做对比，是否属于近七日内，所以我的第一步就是在订单表基础上加上一列只包含10.1、10.2、10.3这三天的日期列dt，完成内连接。（现数据条数=原数据条数*3）见下图所示。2、完成内连接后，根据dt列分组，利用case when函数 对其进行判断：若dt列和finish_time列日期差是[0,7),那么属于近七日；再判断start_time(开始计费的上车时间)是否为空，若为空，则属于取消订单类；若不为空，则属于接单且完成订单。最后若符合完成订单或者是取消订单的条件，那么返回order_id订单号，对其进行去重计数/7天求平均

select dt      ,round(count(DISTINCT case when datediff(dt,date(t1.finish_time)) < 7 and datediff(dt,date(t1.finish_time)) >= 0 and start_time is not null then t1.order_id end)/7,2) as 订单完成量  ,round(count(DISTINCT case when datediff(dt,date(t1.finish_time)) < 7 and datediff(dt,date(t1.finish_time)) >= 0 and start_time is null  then t1.order_id end)/7,2) as 订单取消量from tb_get_car_order t1 inner join  (select distinct date(finish_time) dt      from tb_get_car_order  where date(finish_time) >= date('2021-10-01') and date(finish_time) <= date('2021-10-03')) t2group by dtorder by dt asc

5、工作日各时段叫车量、等待时间和调度时间

 统计周一到周五(2-6)各时段的叫车量、平均等待接单时间和平均调度时间。 全部以event_time-开始打车时间为时段划分依据，平均等待接单时间和平均调度时间均保留1位小数， 平均调度时间仅计算完成了的订单，结果按叫车量升序排序。注：不同时段定义：早高峰 [07:00:00 , 09:00:00)、工作时间 [09:00:00 , 17:00:00）、晚高峰 [17:00:00 , 20:00:00）、休息时间 [20:00:00 , 07:00:00）时间区间左闭右开（即7:00:00算作早高峰，而9:00:00不算做早高峰）从开始打车到司机接单为等待接单时间，从司机接单到上车为调度时间。输出格式：period  get_car_num  avg_wait_time  avg_dispatch_time  思路：1、利用日期函数dayofweek()筛选出周一到周五的订单，利用控制函数case when 划分时段并分组2、叫车量：订单数量；等待接单时间：order_time-event_time；调度时间：start_time-order_time。

select case when hour(t1.event_time)>=7 and hour(t1.event_time)<9 then '早高峰'     when hour(t1.event_time)>=9 and hour(t1.event_time)<17 then '工作时间'     when hour(t1.event_time)>=17 and hour(t1.event_time)<20 then '晚高峰'     else '休息时间' end as period,count(t1.event_time) as get_car_num   ,round(avg(timestampdiff(second,t1.event_time,t2.order_time))/60,1) as avg_wait_time     ,round(avg(case when t2.start_time is not null and t2.finish_time is not null then timestampdiff(second,t2.order_time,t2.start_time) end)/60,1) as avg_dispatch_time  from tb_get_car_record t1 left join tb_get_car_order t2 on t1.uid = t2.uid and t1.order_id = t2.order_idwhere dayofweek(t1.event_time) between 2 and 6 group by period  # 这里可以直接使用划分时段后的列名order by get_car_num asc

6、各城市最大同时等车人数

请统计各个城市在2021年10月期间，单日中最大的同时等车人数。注:  等车指从开始打车起，直到取消打车、取消等待或上车前的这段时间里用户的状态。     如果同一时刻有人停止等车，有人开始等车，等车人数记作先增加后减少。     结果按各城市最大等车人数升序排序，相同时按城市升序排序。思路：因为同一时间有人上车有人下车，所以我们可以加一列if_wait是否等车，用1代表开始等车，用-1代表停止等车，按照城市、日期和时间进行分组，这样开始+1，停止-1就可以计算每个时刻的等车人数了。1、用union all 将开始等车的订单（if_wait=1）和停止等车的订单(if_wait=-1)进行纵向连接2、开始等车的情况就很简单了，将event_time作为等车时间；而停止等车的情况分成3种：     # 若一直无司机接单、超时或中途用户主动取消打车(driver_id=null)，则记录打车结束时间(end_time)，finish_time为null。停止等车时间=end_time     # 上车前乘客或司机取消订单(start_time=null)，订单完成时间finish_time填充为取消时间，finish_time不为null。停止等车时间=finish_time     # 当司机接上乘客时，填充打车订单表中该订单的上车时间start_time。停止等车时间=start_time 将纵向连接后的表记为t1，列字段包含：city城市，dt日期，time时间，if_wait是否等车    3、根据城市、日期分区，按照时间排序，对if_wait进行求和记为wait_uv（截至当前时间的等车人数即同时等车人数）4、求各城市单日最大同时等车人数。根据城市进行分组，取wait_uv最大值，最后按照等车人数和城市升序排序

select city,max(wait_uv) max_wait_uvfrom(    select city, dt, time, if_wait,sum(if_wait) over(partition by city,dt order by time asc) wait_uv    from(     select city,date(event_time) dt ,event_time time,1 as if_wait from tb_get_car_order tgo join tb_get_car_record tgr on tgo.order_id=tgr.order_id where date_format(event_time,'%Y%m') = 202110  # 开始等待时间  union all  select city,date(event_time) dt,  case when driver_id is null then end_time    # 若一直无司机接单、超时或中途用户主动取消打车，则记录打车结束时间。      when start_time is null then finish_time#上车前乘客或司机取消订单，订单完成时间finish_time填充为取消时间。      else start_time end as time  # 当司机接上乘客时，填充打车订单表中该订单的上车时间start_time   ,-1 as if_wait  from tb_get_car_order tgo join tb_get_car_record tgr on tgo.order_id=tgr.order_id where date_format(event_time,'%Y%m') = 202110  # 结束等待时间   )t1     )t2group by cityORDER BY  max_wait_uv asc, city asc

冰雪之城