最近做了饿了么的24年春招题，题目如下 alt alt

题目分析

本题要求统计平均速度大于20且投诉率低于50%的快递员在不同天气条件下的平均配送时间和配送次数。
输出字段：

  • weather_type（天气类型）
  • average_delivery_time（平均配送时间，保留两位小数）
  • delivery_count（配送次数）

输出顺序：按天气类型升序（order by w.weather_type）。
涉及知识点：

  • SQL 多表连接（JOIN）
  • 窗口函数（SUM() OVER PARTITION BY、COUNT() OVER PARTITION BY）
  • 条件筛选（WHERE + 多条件）
  • 分组聚合（AVG、COUNT、GROUP BY）
  • 字段别名与排序（ORDER BY）
  • ROUND函数

解答步骤

1. 计算每个快递员的投诉总数和配送总数

  • 用窗口函数 sum(is_complaint) over(partition by staff_id) 计算每个快递员的投诉总数。
  • 用窗口函数 count(is_complaint) over(partition by staff_id) 计算每个快递员的配送总数。
select *, sum(is_complaint) over(partition by staff_id) as com_num,
count(is_complaint) over(partition by staff_id) as count_num
from delivery_records

2. 关联天气条件和快递员信息

  • 将上述子查询结果与 weather_conditions 天气条件表通过天气ID连接，获取天气类型。
  • 再与 delivery_staff 快递员表通过快递员ID连接，获取快递员信息。
join weather_conditions w on w.weather_id = dr.weather_id 
join delivery_staff ds on ds.staff_id = dr.staff_id

3. 筛选符合条件的记录

  • 只保留平均速度大于20且投诉率低于50%的快递员记录。
  • 投诉率 = 投诉总数 / 配送总数
where ds.average_speed > 20 and dr.com_num/dr.count_num < 0.5

4. 按天气类型分组统计

  • 按天气类型分组，计算：
    • 平均配送时间：round(avg(delivery_time),2)
    • 配送次数：count(*)
group by w.weather_type

5. 排序输出

  • 按天气类型升序排序。
order by w.weather_type

完整代码

select w.weather_type,
round(avg(delivery_time),2) as average_delivery_time,
count(*) as delivery_count
from (
    select *, sum(is_complaint) over(partition by staff_id) as com_num,
    count(is_complaint) over(partition by staff_id) as count_num
    from delivery_records 
) dr 
join weather_conditions w on w.weather_id = dr.weather_id 
join delivery_staff ds on ds.staff_id = dr.staff_id 
where ds.average_speed > 20 and dr.com_num/dr.count_num < 0.5
group by w.weather_type
order by w.weather_type

近似题目练习推荐

计算每日累计利润

  • 知识点：窗口函数、排序

如需更多类似题目，可在牛客网SQL练习区进行练习。