题解 | 近7天骑手履约时效看板

#求解答哪里有问题，on_time_rate和avg_minutes_7d一直不对

select

courier_id,

courier_name,

city,

count(*) as orders_7d,

sum(case when timestampdiff(minute,shipped_ts,delivered_ts) <= promised_minutes then 1 else 0 end) as on_time_7d,

round(sum(case when timestampdiff(minute,shipped_ts,delivered_ts) <= promised_minutes then 1 else 0 end)/count(*),2) as on_time_rate,

round(avg(timestampdiff(minute,shipped_ts,delivered_ts)),2) as avg_minutes_7d,

rank() over(partition by city order by round(sum(case when timestampdiff(minute,shipped_ts,delivered_ts) <= promised_minutes then 1 else 0 end)/count(*),2) desc,round(avg(timestampdiff(minute,shipped_ts,delivered_ts)),2)) as rank_in_city

from parcel

join courier

using(courier_id)

join (

select courier_id,max(delivered_ts) as base_ts

from parcel

group by courier_id) as u

using(courier_id)

where datediff(base_ts,delivered_ts) between 0 and 6

group by courier_id,courier_name,city

having count(*)>0

order by city,rank_in_city,courier_id