25年美团-骑手配送履约分层留存与时段热力矩阵_牛客题霸

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（层级：T1T2T3） retention_week（留存周：W1W2W4） time_slot（时段：morningnooneveningnight） 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（只展示 W1W2W4 留存期内关键订单，首周订单用于分层不展开） 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) 活跃；W2W4 无。骑手 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

示例1

输入

DROP TABLE IF EXISTS t_rider;
DROP TABLE IF EXISTS t_delivery;

CREATE TABLE t_rider (
    rider_id    BIGINT,
    rider_name  VARCHAR(32),
    city_code   VARCHAR(16),
    reg_date    DATE
);
CREATE TABLE t_delivery (
    order_id     BIGINT,
    rider_id     BIGINT,
    pickup_time  DATETIME,
    deliver_time DATETIME,
    status       VARCHAR(16)
);

INSERT INTO t_rider VALUES
(1, 'ZhangSan', 'BJ', '2025-03-01'),
(2, 'LiSi',     'BJ', '2025-03-01'),
(3, 'WangWu',   'SH', '2025-03-05');

INSERT INTO t_delivery VALUES
(10001,1,'2025-03-01 08:00:00','2025-03-01 08:30:00','FINISHED'),
(10002,1,'2025-03-01 12:00:00','2025-03-01 12:28:00','FINISHED'),
(10003,1,'2025-03-01 18:00:00','2025-03-01 18:35:00','FINISHED'),
(10004,1,'2025-03-02 08:10:00','2025-03-02 08:40:00','FINISHED'),
(10005,1,'2025-03-02 12:10:00','2025-03-02 12:40:00','FINISHED'),
(10006,1,'2025-03-02 18:10:00','2025-03-02 18:45:00','FINISHED'),
(10007,1,'2025-03-03 08:20:00','2025-03-03 08:50:00','FINISHED'),
(10008,1,'2025-03-03 12:20:00','2025-03-03 12:50:00','FINISHED'),
(10009,1,'2025-03-03 18:20:00','2025-03-03 18:55:00','FINISHED'),
(10010,1,'2025-03-04 08:30:00','2025-03-04 09:00:00','FINISHED'),
(10011,1,'2025-03-04 12:30:00','2025-03-04 13:00:00','FINISHED'),
(10012,1,'2025-03-04 18:30:00','2025-03-04 19:05:00','FINISHED'),
(10013,1,'2025-03-05 08:40:00','2025-03-05 09:10:00','FINISHED'),
(10014,1,'2025-03-05 12:40:00','2025-03-05 13:10:00','FINISHED'),
(10015,1,'2025-03-05 18:40:00','2025-03-05 19:15:00','FINISHED'),
(10016,1,'2025-03-06 08:50:00','2025-03-06 09:20:00','FINISHED'),
(10017,1,'2025-03-06 12:50:00','2025-03-06 13:20:00','FINISHED'),
(10018,1,'2025-03-06 18:50:00','2025-03-06 19:25:00','FINISHED'),
(10019,1,'2025-03-07 09:00:00','2025-03-07 09:30:00','FINISHED'),
(10020,1,'2025-03-07 12:00:00','2025-03-07 12:30:00','FINISHED'),
(10021,1,'2025-03-07 19:00:00','2025-03-07 19:35:00','FINISHED'),
(10022,1,'2025-03-01 09:30:00','2025-03-01 10:00:00','FINISHED'),
(10023,1,'2025-03-02 09:30:00','2025-03-02 10:00:00','FINISHED'),
(10024,1,'2025-03-03 09:30:00','2025-03-03 10:00:00','FINISHED'),
(10025,1,'2025-03-04 09:30:00','2025-03-04 10:00:00','FINISHED'),
(10026,1,'2025-03-05 09:30:00','2025-03-05 10:00:00','FINISHED'),
(10027,1,'2025-03-06 09:30:00','2025-03-06 10:00:00','FINISHED'),
(10028,1,'2025-03-07 09:30:00','2025-03-07 10:00:00','FINISHED'),
(10029,1,'2025-03-01 20:00:00','2025-03-01 20:30:00','FINISHED'),
(10030,1,'2025-03-02 20:00:00','2025-03-02 20:30:00','FINISHED'),
(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'),
(20001,2,'2025-03-02 10:00:00','2025-03-02 10:20:00','FINISHED'),
(20002,2,'2025-03-04 13:00:00','2025-03-04 13:25:00','FINISHED'),
(20003,2,'2025-03-06 20:00:00','2025-03-06 20:40: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'),
(30001,3,'2025-03-05 09:00:00','2025-03-05 09:30:00','FINISHED'),
(30002,3,'2025-03-05 12:00:00','2025-03-05 12:20:00','FINISHED'),
(30003,3,'2025-03-05 18:00:00','2025-03-05 18:40:00','FINISHED'),
(30004,3,'2025-03-06 09:00:00','2025-03-06 09:30:00','FINISHED'),
(30005,3,'2025-03-06 12:00:00','2025-03-06 12:20:00','FINISHED'),
(30006,3,'2025-03-06 18:00:00','2025-03-06 18:40:00','FINISHED'),
(30007,3,'2025-03-07 09:00:00','2025-03-07 09:30:00','FINISHED'),
(30008,3,'2025-03-07 12:00:00','2025-03-07 12:20:00','FINISHED'),
(30009,3,'2025-03-07 18:00:00','2025-03-07 18:40:00','FINISHED'),
(30010,3,'2025-03-08 09:00:00','2025-03-08 09:30:00','FINISHED'),
(30011,3,'2025-03-09 12:00:00','2025-03-09 12:20:00','FINISHED'),
(30012,3,'2025-03-09 18:00:00','2025-03-09 18:40:00','FINISHED'),
(30013,3,'2025-03-10 09:00:00','2025-03-10 09:30:00','FINISHED'),
(30014,3,'2025-03-11 12:00:00','2025-03-11 12:20:00','FINISHED'),
(30015,3,'2025-03-11 18:00:00','2025-03-11 18:40: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');

输出

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

DROP TABLE IF EXISTS t_rider;
DROP TABLE IF EXISTS t_delivery;

CREATE TABLE t_rider (
    rider_id    BIGINT,
    rider_name  VARCHAR(32),
    city_code   VARCHAR(16),
    reg_date    DATE
);
CREATE TABLE t_delivery (
    order_id     BIGINT,
    rider_id     BIGINT,
    pickup_time  DATETIME,
    deliver_time DATETIME,
    status       VARCHAR(16)
);

INSERT INTO t_rider VALUES
(1, 'ZhangSan', 'BJ', '2025-03-01'),
(2, 'LiSi',     'BJ', '2025-03-01'),
(3, 'WangWu',   'SH', '2025-03-05');

INSERT INTO t_delivery VALUES
(10001,1,'2025-03-01 08:00:00','2025-03-01 08:30:00','FINISHED'),
(10002,1,'2025-03-01 12:00:00','2025-03-01 12:28:00','FINISHED'),
(10003,1,'2025-03-01 18:00:00','2025-03-01 18:35:00','FINISHED'),
(10004,1,'2025-03-02 08:10:00','2025-03-02 08:40:00','FINISHED'),
(10005,1,'2025-03-02 12:10:00','2025-03-02 12:40:00','FINISHED'),
(10006,1,'2025-03-02 18:10:00','2025-03-02 18:45:00','FINISHED'),
(10007,1,'2025-03-03 08:20:00','2025-03-03 08:50:00','FINISHED'),
(10008,1,'2025-03-03 12:20:00','2025-03-03 12:50:00','FINISHED'),
(10009,1,'2025-03-03 18:20:00','2025-03-03 18:55:00','FINISHED'),
(10010,1,'2025-03-04 08:30:00','2025-03-04 09:00:00','FINISHED'),
(10011,1,'2025-03-04 12:30:00','2025-03-04 13:00:00','FINISHED'),
(10012,1,'2025-03-04 18:30:00','2025-03-04 19:05:00','FINISHED'),
(10013,1,'2025-03-05 08:40:00','2025-03-05 09:10:00','FINISHED'),
(10014,1,'2025-03-05 12:40:00','2025-03-05 13:10:00','FINISHED'),
(10015,1,'2025-03-05 18:40:00','2025-03-05 19:15:00','FINISHED'),
(10016,1,'2025-03-06 08:50:00','2025-03-06 09:20:00','FINISHED'),
(10017,1,'2025-03-06 12:50:00','2025-03-06 13:20:00','FINISHED'),
(10018,1,'2025-03-06 18:50:00','2025-03-06 19:25:00','FINISHED'),
(10019,1,'2025-03-07 09:00:00','2025-03-07 09:30:00','FINISHED'),
(10020,1,'2025-03-07 12:00:00','2025-03-07 12:30:00','FINISHED'),
(10021,1,'2025-03-07 19:00:00','2025-03-07 19:35:00','FINISHED'),
(10022,1,'2025-03-01 09:30:00','2025-03-01 10:00:00','FINISHED'),
(10023,1,'2025-03-02 09:30:00','2025-03-02 10:00:00','FINISHED'),
(10024,1,'2025-03-03 09:30:00','2025-03-03 10:00:00','FINISHED'),
(10025,1,'2025-03-04 09:30:00','2025-03-04 10:00:00','FINISHED'),
(10026,1,'2025-03-05 09:30:00','2025-03-05 10:00:00','FINISHED'),
(10027,1,'2025-03-06 09:30:00','2025-03-06 10:00:00','FINISHED'),
(10028,1,'2025-03-07 09:30:00','2025-03-07 10:00:00','FINISHED'),
(10029,1,'2025-03-01 20:00:00','2025-03-01 20:30:00','FINISHED'),
(10030,1,'2025-03-02 20:00:00','2025-03-02 20:30:00','FINISHED'),
(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'),
(20001,2,'2025-03-02 10:00:00','2025-03-02 10:20:00','FINISHED'),
(20002,2,'2025-03-04 13:00:00','2025-03-04 13:25:00','FINISHED'),
(20003,2,'2025-03-06 20:00:00','2025-03-06 20:40: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'),
(30001,3,'2025-03-05 09:00:00','2025-03-05 09:30:00','FINISHED'),
(30002,3,'2025-03-05 12:00:00','2025-03-05 12:20:00','FINISHED'),
(30003,3,'2025-03-05 18:00:00','2025-03-05 18:40:00','FINISHED'),
(30004,3,'2025-03-06 09:00:00','2025-03-06 09:30:00','FINISHED'),
(30005,3,'2025-03-06 12:00:00','2025-03-06 12:20:00','FINISHED'),
(30006,3,'2025-03-06 18:00:00','2025-03-06 18:40:00','FINISHED'),
(30007,3,'2025-03-07 09:00:00','2025-03-07 09:30:00','FINISHED'),
(30008,3,'2025-03-07 12:00:00','2025-03-07 12:20:00','FINISHED'),
(30009,3,'2025-03-07 18:00:00','2025-03-07 18:40:00','FINISHED'),
(30010,3,'2025-03-08 09:00:00','2025-03-08 09:30:00','FINISHED'),
(30011,3,'2025-03-09 12:00:00','2025-03-09 12:20:00','FINISHED'),
(30012,3,'2025-03-09 18:00:00','2025-03-09 18:40:00','FINISHED'),
(30013,3,'2025-03-10 09:00:00','2025-03-10 09:30:00','FINISHED'),
(30014,3,'2025-03-11 12:00:00','2025-03-11 12:20:00','FINISHED'),
(30015,3,'2025-03-11 18:00:00','2025-03-11 18:40: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');