@gudanglifehack: SQL项目系列#19 物流与发货分析 分析发货、仓库、配送表现、客户等…

SQL项目系列第19篇 物流与发货分析 使用SQL分析发货、仓库、配送表现、客户和运输数据，以优化供应链运营并提高配送效率。 业务目标 跟踪发货 监控配送表现 分析仓库运营 衡量运输效率 识别延迟配送 优化运输成本 分析客户订单 构建物流仪表板 第1步：创建数据库 CREATE DATABASE logistics_db; USE logistics_db; 第2步：创建客户表 CREATE TABLE customers ( customer_id INT PRIMARY KEY, customer_name VARCHAR(100), city VARCHAR(50) ); 第3步：创建仓库表 CREATE TABLE warehouses ( warehouse_id INT PRIMARY KEY, warehouse_name VARCHAR(100), city VARCHAR(50) ); 第4步：创建发货表 CREATE TABLE shipments ( shipment_id INT PRIMARY KEY, customer_id INT, warehouse_id INT, shipment_date DATE, delivery_date DATE, shipping_cost DECIMAL(10,2), shipment_status VARCHAR(30), delivery_partner VARCHAR(100), FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id) ); 第5步：插入示例客户数据 INSERT INTO customers VALUES (1,'Rahul Sharma','Mumbai'), (2,'Priya Verma','Delhi'), (3,'Amit Patel','Pune'), (4,'Sneha Joshi','Bangalore'), (5,'Rohan Gupta','Hyderabad'); 第6步：插入示例仓库数据 INSERT INTO warehouses VALUES (101,'Mumbai Warehouse','Mumbai'), (102,'Delhi Warehouse','Delhi'), (103,'Pune Warehouse','Pune'), (104,'Bangalore Warehouse','Bangalore'); 第7步：插入示例发货数据 INSERT INTO shipments VALUES (1001,1,101,'2025-01-05','2025-01-07',450,'Delivered','BlueDart'), (1002,2,102,'2025-01-06','2025-01-09',620,'Delivered','DTDC'), (1003,3,103,'2025-01-07','2025-01-11',780,'Delayed','Delhivery'), (1004,4,104,'2025-01-08','2025-01-10',390,'Delivered','XpressBees'), (1005,5,101,'2025-01-09','2025-01-14',950,'Delayed','BlueDart'); 你将练习的SQL概念 DDL与DML INNER JOIN LEFT JOIN 聚合函数 GROUP BY HAVING CASE WHEN 日期函数 CTE（公用表表达式） 窗口函数 排名函数 你可以构建的业务KPI 总发货量 已配送发货量 延迟发货量 配送成功率 平均配送时间 总运输成本 按仓库计算的运输成本 按配送合作伙伴计算的运输成本 按城市统计的发货量 按仓库统计的发货量 配送合作伙伴绩效 按合作伙伴统计的平均配送时间 仓库利用率 发货高峰日 月度发货趋势 按客户统计的发货量 准时配送率 延迟配送分析 每单成本 高管物流仪表板

CREATE DATABASE logistics_db;
USE logistics_db;

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE warehouses (
    warehouse_id INT PRIMARY KEY,
    warehouse_name VARCHAR(100),
    city VARCHAR(50)
);

CREATE TABLE shipments (
    shipment_id INT PRIMARY KEY,
    customer_id INT,
    warehouse_id INT,
    shipment_date DATE,
    delivery_date DATE,
    shipping_cost DECIMAL(10,2),
    shipment_status VARCHAR(30),
    delivery_partner VARCHAR(100),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (warehouse_id) REFERENCES warehouses(warehouse_id)
);

INSERT INTO customers VALUES
(1,'Rahul Sharma','Mumbai'),
(2,'Priya Verma','Delhi'),
(3,'Amit Patel','Pune'),
(4,'Sneha Joshi','Bangalore'),
(5,'Rohan Gupta','Hyderabad');

INSERT INTO warehouses VALUES
(101,'Mumbai Warehouse','Mumbai'),
(102,'Delhi Warehouse','Delhi'),
(103,'Pune Warehouse','Pune'),
(104,'Bangalore Warehouse','Bangalore');

INSERT INTO shipments VALUES
(1001,1,101,'2025-01-05','2025-01-07',450,'Delivered','BlueDart'),
(1002,2,102,'2025-01-06','2025-01-09',620,'Delivered','DTDC'),
(1003,3,103,'2025-01-07','2025-01-11',780,'Delayed','Delhivery'),
(1004,4,104,'2025-01-08','2025-01-10',390,'Delivered','XpressBees'),
(1005,5,101,'2025-01-09','2025-01-14',950,'Delayed','BlueDart');