题解 | 饿了么需要分析不同配送员在不同天气条件下的配送效率和用户投诉情况

select
weather_type,
round(avg(delivery_time),2) average_delivery_time,
count(*) delivery_count
from (
    select
    weather_type,
    delivery_time,
    average_speed
    from delivery_staff d 
    right join delivery_records r on d.staff_id=r.staff_id
    left join weather_conditions c on r.weather_id=c.weather_id
    where average_speed>20 and d.staff_id in (
        select staff_id
        from delivery_records
        group by staff_id
        having sum(is_complaint)/count(*)<0.5
    )
) t
group by weather_type
order by weather_type

1.解题思路：先找出满足条件的骑手（子查询where条件进行筛选），再根据天气类型进行分类（主查询groupby），得到所有满足条件骑手的平均信息。

2.筛选时注意如果出现聚合函数，可以转化为子查询，借助in子句