题目内容

【背景】：菜鸟需要评估不同物流线路在不同季节的运输效率以及不同包裹类型的丢失率，以优化物流运营和服务质量。

【原始表】:

logistics_route(物流线路)表：

• route_id(线路ID)：线路的唯一标识符，INT

• route_name(线路名称)：物流线路的名称，VARCHAR(50)

package_info (包裹信息)表：

• package_id(包裹 ID): 包裹的唯一标识符，INT

• route_id(线路ID)：关联物流线路表的线路 ID，INT

• package_type(包裹类型)：包裹的类型，VARCHAR(20)(大型和小型)

• start_time(开始配送时间)：包裹开始配送的时间 DATETIME

• delivery_time(送达时间)：包裹送达的时间，DATETIME

lost_package (丢失包裹)表：

• lost_id(丟失 ID)：丢失的唯一标识符，INT

• package_id(包裹ID)：关联包裹信息表的包裹 ID，INT

【要求】：查询出每条物流线路在每个季节的平均送达时间(单位小时，分钟部分以小数展示)以及每条物流线路全年包裹的丢失率(丟失包裹数量 / 总包裏数量)、每条物流线路运输包裹类型占比。结果按照线路 ID 升序排列。要求查询出来的表格的字段如下：

• route_id: 线路 ID。

• route_name 线路名称

• spring_average_deliver_time：春季该线路的平均送达时间。每个包裹送达时间：deliver_time-start_time。春季：3月、4月、5月(round 保留 2 位小数)

• summer_average_delivery_time：夏季该线路的平均送达时间。每个包裹送达时间：deliver_time-start_time 。夏季：6月、7月、8月(round 保留 2 位小数)

• autumn_average_delivery_time：秋季该线路的平均送达时间，每个包裹送达时间：deliver_time-start_time 。秋季：9月、10月、11月(round 保留 2 位小数)

• winter_average_delivery_time：冬季该线路的平均送达时间。每个包裹送达时间：deliver_time-start_time 。冬季：12月、1月、2月(round 保留 2 位小数)

• lost_count：该路线全年包裹丢失总数量

• lost_rate：该路线全年包裹丟失率 (round 保留 2 位小数)

• small_packages：该路线运输"小型"包裹类型占比，“小型“包裹数量/总包裹数量(round 保留 2 位小数)

• big_packages：该路线运输“大型"包裹类型占比，“大型”包裹数量/总包裹数量(round 保留 2 位小数)

【示例】

logistics_route(物流线路)表：

package_info (包裹信息)表：

lost_package (丢失包裹)表：（每条数据代表丢失一件包裹）

【按要求查出来的表】

【解释】 上述示例中“路线A"有关的 package_info (包裹信息)有 $2$ 笔，分别是 package_id(包裹 ID)：$1$ 和 $5$，这两个包裹分别是 $1$ 是在冬季运输的，$5$ 是在春季运输的， $1$ 的平均运输时间是 $2024-01-05$ $10:00-2024-01-04$ $09:10=24$ 小时 $50$ 分钟 约等于 $24.83$ 小时，$5$ 的平均运输时间是 $2024-04-10$ $12:00-2024-04-09$ $10:50 = 25$ 小时 $10$ 分钟 约等于 $25.17$ 小时;其中 package_id(包裹 ID)：$1$ 在送达指定地点后丢失了，所以丢失总数量是 $1$ ; 丢失率 $=1/2=0.5$ ; package_id(包裹 ID)：$1$ 和 $5$ 都是“小型”包裹，所以 small_packages 是 $1$ ；big_packages 是：$0$ ;

样例1

输入

DROP TABLE IF EXISTS logistics_route；
DROP TABLE IF EXISTS package_info;
DROP TABLE IF EXISTS lost_package;
--创建表
CREATE TABLE logistics_route(
route_id INT PRIMARY KEY
route_name VARCHAR(5O)
);

CREATE TABLE package_info(
package_id INT PRIMARY KEY,
route_id INT,
package_type VARCHAR(20),
start_time DATETIME
delivery_time DATETIME
);

CREATE TABLE lost_package(
lost_id INT PRIMARY KEY,
package_id INT
);

-- 插入数据
INSERT INTO logistics_route(route_id,route_name)
VALUES(1，'线路 A'),
(2,'线路 B'),
(3,'线路 C'),
(4,'线路 D');


INSERT INTO package_info (package_id,route_id,
package_type,start_time,delivery_time)
VALUES(1,1,'小型','2024-01-04 9:10','2024-01-05 10:00'),
(2,2,'大型','2024-01-09 11:05','2024-01-10 12:00'),
(3,3,'小型','2024-02-09 11:30','2024-02-10 12:00'),
(4,4,'大型','2024-03-09 11:15','2024-03-10 12:00'),
(5,1,'小型','2024-04-09 10:50','2024-04-10 12:00'),
(6,2,'小型','2024-05-09 11:20','2024-05-10 12:00'),
(7,3,'大型','2024-06-09 14:30','2024-06-10 15:00'),
(8,4,'小型','2024-07-09 11:06','2024-07-10 12:00');

INSERT INTO lost_package (lost_id, package_id)
VALUES (1,1)
(2,6);


select*from logistics_route;
select*from package_info;
select*from lost_package;

输出

route_id|route_name|spring_average_delivery_time|summer_average_delivery_time|autumn_average_delivery_time|winter_average_delivery_time|lost_count|lost_rate|small_packages|big_packages
1|线路 A|25.17|0.00|0.00|24.83|1|0.50|1.00|0.00
2|线路 B|24.67|0.00|0.00|24.92|1|0.50|0.50|0.50
3|线路 C|0.00|24.50|0.00|24.50|0|0.00|0.50|0.50
4|线路 D|24.75|24.90|0.00|0.00|0|0.00|0.50|0.50