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

with courier_daily_stats as(
       -- 第一步：计算每个骑手近7天的基础指标
select  a.courier_id,
        courier_name,
        city,
        # if (day(a.shipped_ts) >= (day(max(a.shipped_ts) - 7)),1,0) as in_dateRange,
        if(timestampdiff(minute, a.shipped_ts, a.delivered_ts) <= a.promised_minutes, 1, 0) AS is_on_time,
        timestampdiff(minute, a.shipped_ts, a.delivered_ts) as delivered_time,
        shipped_ts
from parcel a 
join courier b on a.courier_id = b.courier_id
), courier_7d_stats as (
    -- 第二步：聚合计算每个骑手近7天的核心指标
    select  courier_id,
            courier_name,
            city,
            count(*) as orders_7d,
            count(case when is_on_time = 1 then 1 end) on_time_7d,
            round(count(case when is_on_time = 1 then 1 end)/count(*), 2) as on_time_rate, 
            round(avg(delivered_time), 2) as avg_minutes_7d
    from courier_daily_stats
    where shipped_ts >= date_sub((select max(shipped_ts) from parcel), interval 7 day)
    group by 1,2,3
    having orders_7d > 0
), city_rank as(
    -- 第三步：分城市计算排名
        select
            *,
            rank() over(partition by city order by on_time_rate desc, avg_minutes_7d) as rank_in_city 
        from courier_7d_stats           
)

-- 第四步：呈现
select  courier_id,
        courier_name,
        city,    
        orders_7d,
        on_time_7d,
        on_time_rate,
        avg_minutes_7d,
        rank_in_city
from city_rank