1. 题目描述
背景
某即时配送平台的运力运营团队需要对新注册骑手进行首周履约能力分层，并观察不同层级骑手在入职后第 N 周的时段（早/午/晚/夜）产能留存情况。团队希望用一张 SQL 同时产出：
- 每个层级、每个留存周、每个时段的在岗骑手数（该周该时段至少完成 1 单的骑手）；
- 该层级首周人数基准下的时段产能留存率；
- 以及该层级该留存周跨时段平均单均时效（分钟，四舍五入保留 2 位）。
分层规则：以骑手reg_date为锚点，Day0 =reg_date。首周 = Day0 ~ Day0+6 共 7 天。首周完成单量（仅计status='FINISHED'的订单）：
- ≥ 30 单→'T1'（金牌）
- 15 ~ 29 单→'T2'（银牌）
- 1 ~ 14 单→'T3'（铜牌）
- 首周 0 单的骑手不进入分析（整骑手剔除）。
时段规则（按pickup_time的小时判定，闭区间说明：morning= 06:00:00–10:59:59，noon= 11:00:00–13:59:59，evening= 17:00:00–20:59:59，night= 其他所有时间，包括 00:00–05:59、14:00–16:59、21:00–23:59）。
留存周定义（以 Day0 为锚）：
- W1= Day0+7 ~ Day0+13
- W2= Day0+14 ~ Day0+20
- W4= Day0+28 ~ Day0+34
首周本身（Day0~Day0+6）只用来定层和算基准人数，不作为留存周输出。
表 1：t_rider（骑手注册表）
- rider_id：BIGINT，骑手 ID
- rider_name：VARCHAR(32)，骑手姓名
- city_code：VARCHAR(16)，所在城市编码
- reg_date：DATE,注册日期（2025 年内）
表 2：t_delivery（配送订单流水表）
- order_id：BIGINT，订单 ID
- rider_id：BIGINT，配送骑手 ID（关联 t_rider.rider_id）
- pickup_time：DATETIME，取餐时间（2025 年内）
- deliver_time：DATETIME，送达时间（> pickup_time，同一订单不跨日问题）
- status：VARCHAR(16)，订单状态，取值'FINISHED'/'CANCELLED'/'TIMEOUT'
2. 问题
输出每个分层 × 留存周 × 时段下的骑手履约情况。
仅统计status = 'FINISHED'的订单；时段仅以pickup_time判定；时效 =TIMESTAMPDIFF(SECOND, pickup_time, deliver_time) / 60.0（分钟）。
输出字段：
- tier（层级：T1/T2/T3）
- retention_week（留存周：W1/W2/W4）
- time_slot（时段：morning/noon/evening/night）
- active_rider_cnt（该层该周该时段至少完成 1 单的不同骑手数）
- retention_rate（=active_rider_cnt/ 该层级首周分层后的总骑手数，四舍五入保留 2 位）
- avg_duration_min（该层该周该时段所有 FINISHED 订单的单均时效分钟，四舍五入保留 2 位；若无订单此字段为0.00）
必须输出所有 3 × 3 × 4 = 36 行组合（即使某组合下active_rider_cnt = 0，也要出行，retention_rate = 0.00、avg_duration_min = 0.00）。
排序规则：
- tier按 T1, T2, T3 升序（字典序即可）
- retention_week按 W1, W2, W4 升序（字典序即可）
- time_slot按固定顺序：morning → noon → evening → night（不是字典序，需显式指定）
- 若上述三项完全相同（不会出现，但排序稳定起见）按active_rider_cnt降序。
3. 示例数据表
t_rider
| rider_id | rider_name | city_code | reg_date |
|---|---|---|---|
| 1 | ZhangSan | BJ | 2025-03-01 |
| 2 | LiSi | BJ | 2025-03-01 |
| 3 | WangWu | SH | 2025-03-05 |
t_delivery（只展示 W1/W2/W4 留存期内关键订单，首周订单用于分层不展开）
| order_id | rider_id | pickup_time | deliver_time | status |
|---|---|---|---|---|
| 10101 | 1 | 2025-03-08 09:00:00 | 2025-03-08 09:40:00 | FINISHED |
| 10102 | 1 | 2025-03-10 12:30:00 | 2025-03-10 13:00:00 | FINISHED |
| 10201 | 1 | 2025-03-16 19:00:00 | 2025-03-16 19:50:00 | FINISHED |
| 10202 | 1 | 2025-03-17 19:00:00 | 2025-03-17 19:30:00 | CANCELLED |
| 10401 | 1 | 2025-03-30 23:30:00 | 2025-03-31 00:10:00 | FINISHED |
| 20101 | 2 | 2025-03-10 11:30:00 | 2025-03-10 12:00:00 | FINISHED |
| 20102 | 2 | 2025-03-11 13:00:00 | 2025-03-11 13:30:00 | FINISHED |
| 30101 | 3 | 2025-03-13 07:00:00 | 2025-03-13 07:30:00 | FINISHED |
| 30201 | 3 | 2025-03-20 12:00:00 | 2025-03-20 13:10:00 | TIMEOUT |
| 30401 | 3 | 2025-04-05 20:00:00 | 2025-04-05 20:25:00 | FINISHED |
4. 示例数据查询结果表
分层结果：T1=1 人（骑手 1）、T2=1 人（骑手 3）、T3=1 人（骑手 2）。
关键事实推导：
- 骑手 1：W1 morning(10101=40min) 活跃；W1 noon(10102=30min) 活跃；W2 evening(10201=50min) 活跃（CANCELLED 不计）；W4 night(10401=40min) 活跃。
- 骑手 2：W1 noon (20101=30min, 20102=30min) 活跃；W2/W4 无。
- 骑手 3：W1 morning(30101=30min) 活跃；W2 无（TIMEOUT 剔除）；W4 evening(30401=25min) 活跃。
每行基准人数 = 各层总数 = 1。留存率要么 1.00 要么 0.00。完整 36 行按规则排序：
| tier | retention_week | time_slot | active_rider_cnt | retention_rate | avg_duration_min |
|---|---|---|---|---|---|
| T1 | W1 | morning | 1 | 1.00 | 40.00 |
| T1 | W1 | noon | 1 | 1.00 | 30.00 |
| T1 | W1 | evening | 0 | 0.00 | 0.00 |
| T1 | W1 | night | 0 | 0.00 | 0.00 |
| T1 | W2 | morning | 0 | 0.00 | 0.00 |
| T1 | W2 | noon | 0 | 0.00 | 0.00 |
| T1 | W2 | evening | 1 | 1.00 | 50.00 |
| T1 | W2 | night | 0 | 0.00 | 0.00 |
| T1 | W4 | morning | 0 | 0.00 | 0.00 |
| T1 | W4 | noon | 0 | 0.00 | 0.00 |
| T1 | W4 | evening | 0 | 0.00 | 0.00 |
| T1 | W4 | night | 1 | 1.00 | 40.00 |
| T2 | W1 | morning | 1 | 1.00 | 30.00 |
| T2 | W1 | noon | 0 | 0.00 | 0.00 |
| T2 | W1 | evening | 0 | 0.00 | 0.00 |
| T2 | W1 | night | 0 | 0.00 | 0.00 |
| T2 | W2 | morning | 0 | 0.00 | 0.00 |
| T2 | W2 | noon | 0 | 0.00 | 0.00 |
| T2 | W2 | evening | 0 | 0.00 | 0.00 |
| T2 | W2 | night | 0 | 0.00 | 0.00 |
| T2 | W4 | morning | 0 | 0.00 | 0.00 |
| T2 | W4 | noon | 0 | 0.00 | 0.00 |
| T2 | W4 | evening | 1 | 1.00 | 25.00 |
| T2 | W4 | night | 0 | 0.00 | 0.00 |
| T3 | W1 | morning | 0 | 0.00 | 0.00 |
| T3 | W1 | noon | 1 | 1.00 | 30.00 |
| T3 | W1 | evening | 0 | 0.00 | 0.00 |
| T3 | W1 | night | 0 | 0.00 | 0.00 |
| T3 | W2 | morning | 0 | 0.00 | 0.00 |
| T3 | W2 | noon | 0 | 0.00 | 0.00 |
| T3 | W2 | evening | 0 | 0.00 | 0.00 |
| T3 | W2 | night | 0 | 0.00 | 0.00 |
| T3 | W4 | morning | 0 | 0.00 | 0.00 |
| T3 | W4 | noon | 0 | 0.00 | 0.00 |
| T3 | W4 | evening | 0 | 0.00 | 0.00 |
| T3 | W4 | night | 0 | 0.00 |
0.00 |
