一家 O2O 外卖平台需复盘各商圈（Zone）在高峰时段（11:00–13:59 与 18:00–20:59）的履约表现，用于评估供需匹配与骑手调度效果。请基于订单与骑手、商圈信息，统计每个商圈在 2024 年 2 月的高峰时段“已送达订单数”和“平均送达时长（分钟）”，并与 2024 年 1 月、2023 年 2 月对比；同时给出 2024 年 2 月每个商圈“高峰已送达订单数”最多的骑手（若并列按骑手ID升序）。数据来自商圈表、骑手表与订单表。 【原始表】 Zones（商圈）表 zone_id INT 主键：商圈唯一标识 zone_name VARCHAR：商圈名称 zone_type ENUM('核心区','商圈','居民区') Couriers（骑手）表 courier_id INT 主键：骑手唯一标识 courier_name VARCHAR：骑手姓名 grade ENUM('A','B','C') Orders（订单）表 order_id INT 主键：订单流水 courier_id INT 外键 → Couriers.courier_id zone_id INT 外键 → Zones.zone_id order_time DATETIME：下单时间 delivered_time DATETIME：送达时间（仅已送达有值） status ENUM('delivered','cancelled')：订单状态 fee DECIMAL(10,2)：订单费用（本题不直接使用） 【要求】 按商圈输出下列字段，并按 zone_id 升序（若并列再按 zone_name 升序）： zone_id：商圈ID zone_name：商圈名称 peak_2023_02_delivered：2023-02 高峰时段已送达订单数 peak_2024_02_delivered：2024-02 高峰时段已送达订单数 peak_2024_01_delivered：2024-01 高峰时段已送达订单数 yoy_delta：同比增量 = 2024-02 − 2023-02 mom_delta：环比增量 = 2024-02 − 2024-01 avg_peak_minutes_2024_02：2024-02 高峰时段平均送达时长（分钟，保留两位小数） top_courier_2024_02：2024-02 高峰时段该商圈送达订单数最多的骑手姓名（并列取 courier_id 最小者） 高峰时段定义：HOUR(order_time) BETWEEN 11 AND 13 OR BETWEEN 18 AND 20。仅统计 status='delivered' 的订单。平均送达时长=TIMESTAMPDIFF(MINUTE, order_time, delivered_time)。 【示例输入】 Zones +---------+-----------+-----------+ zone_id zone_name zone_type +---------+-----------+-----------+ 1 Zone A 核心区 2 Zone B 商圈 3 Zone C 居民区 +---------+-----------+-----------+ Couriers +------------+--------------+-------+ courier_id courier_name grade +------------+--------------+-------+ 1 Alice A 2 Bob B 3 Carol B +------------+--------------+-------+ Orders +----------+------------+---------+---------------------+---------------------+-----------+-------+ order_id courier_id zone_id order_time delivered_time status fee +----------+------------+---------+---------------------+---------------------+-----------+-------+ 201 1 1 2023-02-10 11:30:00 2023-02-10 12:00:00 delivered 20.00 202 2 1 2023-02-12 18:20:00 2023-02-12 18:50:00 delivered 18.00 203 2 2 2023-02-15 12:00:00 2023-02-15 12:40:00 delivered 16.00 204 3 1 2024-01-20 19:00:00 2024-01-20 19:40:00 delivered 22.00 205 1 3 2024-01-10 11:10:00 2024-01-10 11:50:00 delivered 15.00 206 1 3 2024-01-25 18:30:00 2024-01-25 19:10:00 delivered 17.00 207 1 1 2024-02-12 11:05:00 2024-02-12 11:35:00 delivered 19.00 208 2 1 2024-02-18 18:10:00 2024-02-18 18:35:00 delivered 21.00 209 1 1 2024-02-28 12:45:00 2024-02-28 13:20:00 delivered 23.00 210 2 2 2024-02-03 12:15:00 2024-02-03 12:50:00 delivered 14.00 211 1 3 2024-02-06 18:05:00 2024-02-06 18:40:00 delivered 16.00 212 3 3 2024-02-22 11:20:00 2024-02-22 11:55:00 delivered 18.00 +----------+------------+---------+---------------------+---------------------+-----------+-------+ 【示例输出】 +---------+-----------+----------------------+----------------------+----------------------+-----------+----------+------------------------+---------------------+ zone_id zone_name peak_2023_02_deliv. peak_2024_02_deliv. peak_2024_01_deliv. yoy_delta mom_delta avg_peak_minutes_2024_02 top_courier_2024_02 +---------+-----------+----------------------+----------------------+----------------------+-----------+----------+------------------------+---------------------+ 1 Zone A 2 3 1 1 2 30.00 Alice 2 Zone B 1 1 0 0 1 35.00 Bob 3 Zone C 0 2 2 2 0 35.00 Alice +---------+-----------+----------------------+----------------------+----------------------+-----------+----------+------------------------+---------------------+
示例1

输入

DROP TABLE IF EXISTS Orders;
DROP TABLE IF EXISTS Couriers;
DROP TABLE IF EXISTS Zones;

CREATE TABLE Zones (
  zone_id   INT PRIMARY KEY,
  zone_name VARCHAR(50),
  zone_type ENUM('核心区','商圈','居民区')
);

CREATE TABLE Couriers (
  courier_id   INT PRIMARY KEY,
  courier_name VARCHAR(50),
  grade        ENUM('A','B','C')
);

CREATE TABLE Orders (
  order_id       INT PRIMARY KEY,
  courier_id     INT,
  zone_id        INT,
  order_time     DATETIME,
  delivered_time DATETIME,
  status         ENUM('delivered','cancelled'),
  fee            DECIMAL(10,2)
);

INSERT INTO Zones VALUES
(1,'Zone A','核心区'),
(2,'Zone B','商圈'),
(3,'Zone C','居民区');

INSERT INTO Couriers VALUES
(1,'Alice','A'),
(2,'Bob','B'),
(3,'Carol','B');

INSERT INTO Orders VALUES
(201,1,1,'2023-02-10 11:30:00','2023-02-10 12:00:00','delivered',20.00),
(202,2,1,'2023-02-12 18:20:00','2023-02-12 18:50:00','delivered',18.00),
(203,2,2,'2023-02-15 12:00:00','2023-02-15 12:40:00','delivered',16.00),
(204,3,1,'2024-01-20 19:00:00','2024-01-20 19:40:00','delivered',22.00),
(205,1,3,'2024-01-10 11:10:00','2024-01-10 11:50:00','delivered',15.00),
(206,1,3,'2024-01-25 18:30:00','2024-01-25 19:10:00','delivered',17.00),
(207,1,1,'2024-02-12 11:05:00','2024-02-12 11:35:00','delivered',19.00),
(208,2,1,'2024-02-18 18:10:00','2024-02-18 18:35:00','delivered',21.00),
(209,1,1,'2024-02-28 12:45:00','2024-02-28 13:20:00','delivered',23.00),
(210,2,2,'2024-02-03 12:15:00','2024-02-03 12:50:00','delivered',14.00),
(211,1,3,'2024-02-06 18:05:00','2024-02-06 18:40:00','delivered',16.00),
(212,3,3,'2024-02-22 11:20:00','2024-02-22 11:55:00','delivered',18.00);

输出

zone_id|zone_name|peak_2023_02_delivered|peak_2024_02_delivered|peak_2024_01_delivered|yoy_delta|mom_delta|avg_peak_minutes_2024_02|top_courier_2024_02
1|Zone A|2|3|1|1|2|30.00|Alice
2|Zone B|1|1|0|0|1|35.00|Bob
3|Zone C|0|2|2|2|0|35.00|Alice
加载中...