牛客网SQL大厂真题二刷小白白话总结（四）出行场景（某滴打车）

目录

SQL19 2021年国庆在北京接单3次及以上的司机统计信息（简单）

SQL19 2021年国庆在北京接单3次及以上的司机统计信息（简单）

SQL21 每个城市中评分最高的司机信息（中等）

SQL22 国庆期间近7日日均取消订单量（中等）

SQL23 工作日各时段叫车量、等待接单时间和调度时间（较难）

---

SQL19 2021年国庆在北京接单3次及以上的司机统计信息（简单）

select "北京" as city,round(avg(order_num),3) as avg_order_num,
round(avg(income),3) as avg_income
from 
(select driver_id,count(order_id) as order_num,sum(fare) as income
from
tb_get_car_record join tb_get_car_order using (order_id)
where city='北京' and DATE_FORMAT(order_time,"%Y%m%d")  between '20211001' and '20211007'
group by driver_id
having count(order_id)>=3) x

这一题很简单，就是对每个司机的接单数据求均值。

SQL19 2021年国庆在北京接单3次及以上的司机统计信息（简单）

select ifnull(driver_id,'总体') as driver_id,
round(avg(grade),1) as avg_grade
from
tb_get_car_order
where driver_id in
 (select driver_id from tb_get_car_order where fare is null and grade is null
 and date_format(finish_time,'%Y-%m')='2021-10')
group by driver_id
with rollup

这道题有点小坑，不是求这些司机10月取消订单的评分，而是把时间限定放到司机的筛选中，在对他们各自所有的评分进行统计。

SQL21 每个城市中评分最高的司机信息（中等）

select city,driver_id,round(avg_grade,1),round(avg_order_num,1),round(avg_mileage,3)
from
(select city,driver_id,avg_grade,avg_order_num,avg_mileage,
rank() over(partition by city order by avg_grade desc) as rk
from
(select city,driver_id,avg(grade) as avg_grade,
 count(orde