【背景】 出行平台需要做“近7天骑手履约时效看板”，按城市评估每位骑手的近7天送达单量、准时单量、准时率与平均配送时长，并在城市内排名，用于日常调度与激励。 【原始表】 1)courier（骑手信息） courier_idINT 主键 courier_nameVARCHAR(64) 非空 cityVARCHAR(32) 非空 hired_atDATE 非空 2)parcel（包裹履约） parcel_idBIGINT 主键 courier_idINT 非空 shipped_tsDATETIME 非空 -- 骑手取件时间 delivered_tsDATETIME NOT NULL -- 实际送达时间（仅统计已送达） promised_minutesINT NOT NULL -- 承诺时效（分钟） 【要求】 以“最新一条已送达记录的delivered_ts”为基准日，统计窗口为“基准日向前 7 天（含基准日）”。对每位骑手统计： orders_7d：近7天送达单量 on_time_7d：近7天准时单量（送达时长 ≤ 承诺时效） on_time_rate：ROUND(on_time_7d orders_7d, 2)（送达单量为0时记 0） avg_minutes_7d：近7天平均送达分钟数（ROUND(AVG(TIMESTAMPDIFF(MINUTE, shipped_ts, delivered_ts)), 2)） rank_in_city：在同城市内按on_time_rate降序、avg_minutes_7d升序排名（RANK） 只输出orders_7d0的骑手，按城市、rank_in_city、courier_id排序。 【示例输入】 courier +------------+--------------+---------+------------+ courier_id courier_name city hired_at +------------+--------------+---------+------------+ 1 Andy Beijing 2024-01-01 2 Ben Beijing 2024-02-01 3 Chen Shanghai 2024-03-01 +------------+--------------+---------+------------+ parcel +-----------+------------+---------------------+---------------------+------------------+ parcel_id courier_id shipped_ts delivered_ts promised_minutes +-----------+------------+---------------------+---------------------+------------------+ 90001 1 2024-08-01 10:00:00 2024-08-01 10:40:00 45 90002 1 2024-08-03 09:00:00 2024-08-03 10:10:00 60 90003 2 2024-08-02 09:00:00 2024-08-02 09:30:00 40 90004 2 2024-08-05 08:30:00 2024-08-05 09:15:00 45 90005 3 2024-08-04 11:00:00 2024-08-04 11:50:00 60 80001 1 2024-07-20 09:00:00 2024-07-20 10:00:00 45 -- 窗口外 +-----------+------------+---------------------+---------------------+------------------+ 【示例输出】 +------------+--------------+----------+-----------+------------+--------------+----------------+---------------+ courier_id courier_name city orders_7d on_time_7d on_time_rate avg_minutes_7d rank_in_city +------------+--------------+----------+-----------+------------+--------------+----------------+---------------+ 2 Ben Beijing 2 2 1.00 37.50 1 1 Andy Beijing 2 1 0.50 55.00 2 3 Chen Shanghai 1 1 1.00 50.00 1 +------------+--------------+----------+-----------+------------+--------------+----------------+---------------+
示例1

输入

DROP TABLE IF EXISTS parcel;
DROP TABLE IF EXISTS courier;

CREATE TABLE courier(
  courier_id INT PRIMARY KEY,
  courier_name VARCHAR(64) NOT NULL,
  city VARCHAR(32) NOT NULL,
  hired_at DATE NOT NULL
);

CREATE TABLE parcel(
  parcel_id BIGINT PRIMARY KEY,
  courier_id INT NOT NULL,
  shipped_ts DATETIME NOT NULL,
  delivered_ts DATETIME NOT NULL,
  promised_minutes INT NOT NULL,
  INDEX idx_parcel_courier_ts (courier_id, delivered_ts)
);

INSERT INTO courier VALUES
(1,'Andy','Beijing','2024-01-01'),
(2,'Ben','Beijing','2024-02-01'),
(3,'Chen','Shanghai','2024-03-01');

INSERT INTO parcel VALUES
(90001,1,'2024-08-01 10:00:00','2024-08-01 10:40:00',45),
(90002,1,'2024-08-03 09:00:00','2024-08-03 10:10:00',60),
(90003,2,'2024-08-02 09:00:00','2024-08-02 09:30:00',40),
(90004,2,'2024-08-05 08:30:00','2024-08-05 09:15:00',45),
(90005,3,'2024-08-04 11:00:00','2024-08-04 11:50:00',60),
(80001,1,'2024-07-20 09:00:00','2024-07-20 10:00:00',45);

输出

courier_id|courier_name|city|orders_7d|on_time_7d|on_time_rate|avg_minutes_7d|rank_in_city
2|Ben|Beijing|2|2|1.00|37.50|1
1|Andy|Beijing|2|1|0.50|55.00|2
3|Chen|Shanghai|1|1|1.00|50.00|1
加载中...