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

with t1 as(select max(date(delivered_ts)) as max_d
FROM parcel),
t2 as(
    select 
        a.courier_id,
        courier_name,
        city,
        count(*) as orders_7d,
        count(case when timestampdiff(second,shipped_ts,delivered_ts)/60 <=promised_minutes then 1 else null end) as on_time_7d,
        round(count(case when timestampdiff(second,shipped_ts,delivered_ts)/60<=promised_minutes then 1 else null end)/count(*),2)as on_time_rate,
        round(avg(timestampdiff(second,shipped_ts,delivered_ts)/60),2)as avg_minutes_7d
    from parcel a
    join courier b using (courier_id) 
    where datediff((select max_d from t1),delivered_ts) between 0 and 6
    group by a.courier_id,courier_name,city)
select*,
    rank()over(partition by city order by on_time_rate desc,avg_minutes_7d ) as rank_in_city
from t2
where orders_7d>0

先查询出最新的日期

根据最新日期进行表连接

最后再用窗口函数