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

select weather_type,
round(sum(sum_delivery)/sum(delivery_cnt),2) as average_delivery_time,sum(delivery_cnt) as delivery_count
from (select b.weather_type,a.staff_id,
sum(delivery_time) as sum_delivery,
sum(is_complaint)/count(is_complaint) as complaint_rate,
count(record_id) as delivery_cnt
from delivery_records a
left join weather_conditions b
on a.weather_id=b.weather_id
left join delivery_staff c
on a.staff_id=c.staff_id
group by weather_type,staff_id) a
where sum_delivery/delivery_cnt>20 and complaint_rate<0.5
group by weather_type

直接用staff平均配送时间限制感觉会比较麻烦，所以第一步可以直接在[weather_type,staff_id]维度上求和。——为什么是求和而不是直接求均值？-最后所需的avgerage_delivery_time是在weather_type维度下进行平均的，但中间限制的平均配速>20的外卖员是在staff_id维度上限制的。所以先求sum(delivery_time)，后续外循环weather维度只需要换分母就行，不用那么麻烦。complaint_rate则不存在这个问题，直接第一步计算后where限制就行。

因此第一步，求[weather_type，staff_id]维度下的总时长，complaint_rate,delivery_cnt

select b.weather_type,a.staff_id,
sum(delivery_time) as sum_delivery,
sum(is_complaint)/count(is_complaint) as complaint_rate,
count(record_id) as delivery_cnt
from delivery_records a
left join weather_conditions b
on a.weather_id=b.weather_id
left join delivery_staff c
on a.staff_id=c.staff_id
group by weather_type,staff_id

第二步，在weather_type维度上将staff_id维度聚合一下，下面再加个where限制就行

select weather_type,
round(sum(sum_delivery)/sum(delivery_cnt),2) as average_delivery_time,sum(delivery_cnt) as delivery_count
from (select b.weather_type,a.staff_id,
sum(delivery_time) as sum_delivery,
sum(is_complaint)/count(is_complaint) as complaint_rate,
count(record_id) as delivery_cnt
from delivery_records a
left join weather_conditions b
on a.weather_id=b.weather_id
left join delivery_staff c
on a.staff_id=c.staff_id
group by weather_type,staff_id) a
where sum_delivery/delivery_cnt>20 and complaint_rate<0.5
group by weather_type