SQL学习（6）：大厂真题实例-打车平均订单

本文链接：https://blog.csdn.net/UoB1887858/article/details/125388006

SQL学习（6）：大厂真题实例-打车平均订单

题目来源：《牛客题霸：SQL大厂真题》：04出行场景（滴滴打车） SQL 19 2021年国庆在北京接单三次及以上司机

文章目录

SQL学习（6）：大厂真题实例-打车平均订单

1.题目描述

用户打车记录表tb_get_car_record

uid-用户ID,
city-城市, 
event_time-打车时间, 
end_time-打车结束时间, 
order_id-订单号

打车订单表tb_get_car_order

order_id-订单号, 
uid-用户ID, 
driver_id-司机ID, 
order_time-接单时间, 
start_time-开始计费的上车时间,  
finish_time-订单完成时间, 
mileage-行驶里程数, 
fare-费用, 
grade-评分

场景逻辑说明：

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

问题：请统计2021年国庆7天期间在北京市****接单至少3次的司机的平均接单数和平均兼职收入（暂不考虑平台佣金，直接计算完成的订单费用总额），结果保留3位小数。最终显示city, avg_order_num, avg_income.

2.问题分析

1.分析和注意事项

梳理场景逻辑：题目要求司机接单次数，我们只需要考虑接单和不接单的情况，其余情况都建立在这两种选择之后。因此，只要打车订单表中出现了数据，就记作一次接单，不需要通过NULL筛选。

在这里插入图片描述

条件

  city=‘北京’——用户打车表
  订单时间 between '2021-10-01' and '2021-10-07'——打车订单表
  接单数目>3——需要通过计算

通过条件知道，需要使用JOIN连接，建议使用唯一数据，因此使用order_id作为连接条件
条件过多：使用子查询

由于接单数目需要通过计算，建议通过子查询计算，外部查询进行条件筛选。因此在子查询中使用聚合函数+over赋予每一行数据cnt作为订单数目。

其次，问题需要提取的平均订单数为count(order_id)/count(distinct driver_id);平均费用为sum(fare)/count(distinct driver_id)

依照问题需求，子查询需要提取
```
  用户表city：条件和最终提取
  订单表driver_id：最终提取
  订单表order_time：条件
  订单表order_id：最终提取
  订单表fare：最终提取
```
DATEDIFF(b.order_time, ‘2021-10-01’) between 0 and 6童谣可以作为条件。between…and 既可以对数据，也可以对时间字符串。
还需要注意，即使我们在子查询中对city进行了筛选，由于最终需要使用统计函数，我们仍然需要没有实际实用意义的group by city完成SQL语法。或者我们可以将此条件放置外围，使用group by city having city='北京'

2.完整代码

select
  c.city,
  round(count(c.order_id) / count(distinct c.driver_id), 3) as avg_order_num,
  round(sum(c.fare) / count(distinct c.driver_id), 3) as avg_fare
from
  (
    select
      a.city,
      b.order_id,
      b.driver_id,
      b.order_time,
      b.fare,
      count(b.order_id) over(partition by b.driver_id) as cnt
    from
      tb_get_car_record as a
      join tb_get_car_order as b on a.order_id = b.order_id
    where
      a.city = '北京'
      and date(order_time) between '2021-10-01' and '2021-10-07'
  ) as c
where
  c.cnt >= 3
  group by c.city