首页 > 试题广场 >

近7天骑手履约时效看板

[编程题]近7天骑手履约时效看板
  • 热度指数：118 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

【背景】
出行平台需要做“近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_7d>0的骑手，按城市、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
算法知识视频讲解
添加回答
在做测评的芝士很卖萌头像 在做测评的芝士很卖萌
WITH t AS
(SELECT c.courier_id, c.courier_name, c.city,
COUNT(p.parcel_id) AS orders_7d,
COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, p.shipped_ts, p.delivered_ts)<=p.promised_minutes THEN p.parcel_id END) AS on_time_7d,
ROUND(COUNT(CASE WHEN TIMESTAMPDIFF(MINUTE, p.shipped_ts, p.delivered_ts)<=p.promised_minutes THEN p.parcel_id END) / COUNT(p.parcel_id), 2) AS on_time_rate,
ROUND(AVG(TIMESTAMPDIFF(MINUTE, p.shipped_ts, p.delivered_ts)), 2) AS avg_minutes_7d
FROM courier c
JOIN parcel p
ON c.courier_id = p.courier_id
WHERE p.delivered_ts >= DATE_SUB((SELECT MAX(delivered_ts) FROM parcel), INTERVAL 6 DAY)
GROUP BY c.courier_id, c.courier_name, c.city
)
SELECT courier_id, courier_name, city, orders_7d, on_time_7d, on_time_rate, avg_minutes_7d,
RANK() OVER(PARTITION BY city ORDER BY on_time_rate DESC, avg_minutes_7d) AS rank_in_city
FROM t
WHERE orders_7d > 0
ORDER BY city, rank_in_city, courier_id;
发表于 2025-09-21 23:27:16 回复(0)
climbupmybananatree头像 climbupmybananatree
最后加个筛选条件：on_time_7d>0（隐形条件，题目没说，带上才通过的）
发表于 2025-09-21 12:28:02 回复(0)
提交观点

问题信息

难度：
2条回答 4浏览

热门推荐

相关试题

近7天骑手履约时效看板

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号