sql: Query Design Patterns using mysql 9.0
-- ============================================================================
-- MySQL 9.0 珠宝行业查询设计模式实战完整脚本（合并版）
-- 由 01_schema.sql + 02_data.sql + 03_queries.sql + 04_pivot_grouping.sql + 05_antipatterns.sql 合并
-- ============================================================================
-- ============================================================================
-- 文件：01_schema.sql
-- 说明：创建数据库、基础表结构、索引
-- 执行顺序：第 1 步
-- 适用版本：MySQL 9.0+（InnoDB / utf8mb4）
-- ============================================================================
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
DROP DATABASE IF EXISTS JewelryDemo;
CREATE DATABASE JewelryDemo DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE JewelryDemo;
-- ============================================================================
-- 第1部分：基础表结构创建
-- ============================================================================
-- 1.1 珠宝分类表（邻接表层次模型）
CREATE TABLE jewelry_category (
cat_id INT AUTO_INCREMENT PRIMARY KEY,
parent_id INT DEFAULT NULL,
cat_name VARCHAR(50) NOT NULL COMMENT '分类名称',
cat_level TINYINT NOT NULL DEFAULT 1 COMMENT '层级：1=一级 2=二级 3=三级',
sort_order INT DEFAULT 0,
is_active TINYINT(1) DEFAULT 1,
INDEX idx_parent (parent_id),
INDEX idx_level (cat_level),
FOREIGN KEY (parent_id) REFERENCES jewelry_category(cat_id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='珠宝分类表';
-- 1.2 产品表
CREATE TABLE products (
product_id INT AUTO_INCREMENT PRIMARY KEY,
cat_id INT NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_code VARCHAR(30) NOT NULL UNIQUE COMMENT '款号',
metal_type ENUM('黄金','铂金','18K金','银','其他') DEFAULT '黄金',
gem_type ENUM('钻石','红宝石','蓝宝石','翡翠','珍珠','无') DEFAULT '无',
carat_weight DECIMAL(8,2) DEFAULT 0.00 COMMENT '克拉重量',
unit_price DECIMAL(12,2) NOT NULL COMMENT '单价',
stock_qty INT DEFAULT 0 COMMENT '库存数量',
is_active TINYINT(1) DEFAULT 1,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_cat (cat_id),
INDEX idx_code (product_code),
INDEX idx_metal (metal_type),
INDEX idx_active (is_active),
FOREIGN KEY (cat_id) REFERENCES jewelry_category(cat_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='珠宝产品表';
-- 1.3 客户表
CREATE TABLE customers (
customer_id INT AUTO_INCREMENT PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL UNIQUE,
email VARCHAR(100),
membership ENUM('普通','银卡','金卡','钻石','黑金') DEFAULT '普通',
city VARCHAR(30) DEFAULT '深圳',
reg_date DATE DEFAULT (CURRENT_DATE),
total_spent DECIMAL(14,2) DEFAULT 0.00 COMMENT '累计消费（反范式冗余字段）',
INDEX idx_phone (phone),
INDEX idx_membership (membership),
INDEX idx_city (city)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';
-- 1.4 订单主表
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
sales_channel ENUM('线下门店','天猫旗舰店','微信小程序','抖音直播','经销商') DEFAULT '线下门店',
store_id INT DEFAULT NULL COMMENT '门店ID（线下渠道）',
total_amount DECIMAL(14,2) NOT NULL DEFAULT 0.00,
discount DECIMAL(4,2) DEFAULT 1.00 COMMENT '整单折扣',
status ENUM('待付款','已付款','已发货','已完成','已取消','已退货') DEFAULT '待付款',
remark VARCHAR(200) DEFAULT NULL,
INDEX idx_customer (customer_id),
INDEX idx_date (order_date),
INDEX idx_channel (sales_channel),
INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单主表';
-- 1.5 订单明细表
CREATE TABLE order_items (
item_id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
unit_price DECIMAL(12,2) NOT NULL COMMENT '成交单价（快照）',
discount DECIMAL(4,2) DEFAULT 1.00 COMMENT '行折扣',
subtotal DECIMAL(14,2) GENERATED ALWAYS AS (ROUND(quantity * unit_price * discount, 2)) STORED,
INDEX idx_order (order_id),
INDEX idx_product (product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单明细表';
-- 1.6 销售流水表（用于增量ETL演示）
CREATE TABLE sales_flow (
flow_id BIGINT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
item_id INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
sale_amount DECIMAL(14,2) NOT NULL,
quantity INT NOT NULL,
channel VARCHAR(20) NOT NULL,
create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
INDEX idx_sale_date (sale_date),
INDEX idx_product (product_id),
INDEX idx_customer (customer_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='销售流水表';
-- 1.7 补充推荐索引
ALTER TABLE orders ADD INDEX idx_channel_date (sales_channel, order_date);
ALTER TABLE order_items ADD INDEX idx_order_product (order_id, product_id);
ALTER TABLE products ADD INDEX idx_cat_metal (cat_id, metal_type);
SELECT '01_schema.sql 执行完成' AS result;
-- ============================================================================
-- 文件：02_data.sql
-- 说明：插入分类、产品、客户、订单、明细测试数据
-- 执行顺序：第 2 步（需先执行 01_schema.sql）
-- 适用版本：MySQL 9.0+
-- ============================================================================
USE JewelryDemo;
-- ============================================================================
-- 第2部分：批量测试数据生成
-- ============================================================================
-- 2.1 分类数据（三级树形结构）
INSERT INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES
INSERT INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES
(1, NULL, '珠宝首饰', 1, 1),
(2, NULL, '投资金条', 1, 2),
(3, NULL, '定制服务', 1, 3),
(10, 1, '戒指', 2, 1),
(11, 1, '项链', 2, 2),
(12, 1, '手镯', 2, 3),
(13, 1, '耳环', 2, 4),
(14, 1, '吊坠', 2, 5),
(15, 1, '胸针', 2, 6),
(100, 10, '钻石戒指', 3, 1),
(101, 10, '黄金戒指', 3, 2),
(102, 10, '翡翠戒指', 3, 3),
(110, 11, '钻石项链', 3, 1),
(111, 11, '珍珠项链', 3, 2),
(112, 11, '黄金项链', 3, 3),
(120, 12, '黄金手镯', 3, 1),
(121, 12, '铂金手镯', 3, 2),
(130, 13, '钻石耳环', 3, 1),
(131, 13, '珍珠耳环', 3, 2),
(140, 14, '翡翠吊坠', 3, 1),
(141, 14, '钻石吊坠', 3, 2);
-- 2.2 产品数据（50条）
INSERT INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES
(100, '永恒之心钻戒', 'R-DIA-001', '铂金', '钻石', 1.00, 58888.00, 5),
(100, '星光钻戒', 'R-DIA-002', '18K金', '钻石', 0.50, 28800.00, 12),
(100, '经典六爪钻戒', 'R-DIA-003', '铂金', '钻石', 0.80, 42800.00, 8),
(101, '福字黄金戒指', 'R-GOL-001', '黄金', '无', 0.00, 8900.00, 30),
(101, '古法金戒指', 'R-GOL-002', '黄金', '无', 0.00, 12600.00, 20),
(102, '翡翠玉戒指', 'R-JAD-001', '黄金', '翡翠', 0.00, 15800.00, 10),
(110, '海洋之泪项链', 'N-DIA-001', '铂金', '钻石', 2.00, 128000.00, 3),
(110, '锁骨链', 'N-DIA-002', '18K金', '钻石', 0.30, 15800.00, 25),
(111, '南洋珍珠项链', 'N-PEA-001', '黄金', '珍珠', 0.00, 36800.00, 8),
(111, '淡水珍珠项链', 'N-PEA-002', '银', '珍珠', 0.00, 2800.00, 50),
(112, '龙凤黄金项链', 'N-GOL-001', '黄金', '无', 0.00, 22800.00, 15),
(120, '古法金手镯', 'B-GOL-001', '黄金', '无', 0.00, 35800.00, 10),
(120, '传承金手镯', 'B-GOL-002', '黄金', '无', 0.00, 28600.00, 18),
(121, '铂金光面手镯', 'B-PLA-001', '铂金', '无', 0.00, 18900.00, 12),
(130, '钻石耳钉', 'E-DIA-001', '铂金', '钻石', 0.50, 22800.00, 20),
(130, '钻石耳环', 'E-DIA-002', '18K金', '钻石', 1.00, 45800.00, 6),
(131, '珍珠耳坠', 'E-PEA-001', '黄金', '珍珠', 0.00, 5800.00, 35),
(140, '帝王绿翡翠吊坠', 'P-JAD-001', '黄金', '翡翠', 0.00, 68000.00, 4),
(140, '冰种翡翠吊坠', 'P-JAD-002', '18K金', '翡翠', 0.00, 28800.00, 12),
(141, '钻石吊坠', 'P-DIA-001', '铂金', '钻石', 0.60, 32800.00, 15),
(100, '群镶钻戒', 'R-DIA-004', '18K金', '钻石', 0.40, 19800.00, 18),
(101, '转运珠戒指', 'R-GOL-003', '黄金', '无', 0.00, 6800.00, 40),
(110, '心形钻石项链', 'N-DIA-003', '铂金', '钻石', 1.50, 88000.00, 5),
(112, '足金项链', 'N-GOL-002', '黄金', '无', 0.00, 18600.00, 22),
(120, '花丝金手镯', 'B-GOL-003', '黄金', '无', 0.00, 42000.00, 6),
(130, '群镶钻石耳环', 'E-DIA-003', '铂金', '钻石', 0.80, 36800.00, 10),
(141, '水滴钻石吊坠', 'P-DIA-002', '18K金', '钻石', 0.40, 18800.00, 20),
(100, '玫瑰金钻戒', 'R-DIA-005', '18K金', '钻石', 0.60, 25800.00, 14),
(111, 'Akoya珍珠项链', 'N-PEA-003', '铂金', '珍珠', 0.00, 28800.00, 10),
(140, '飘花翡翠吊坠', 'P-JAD-003', '黄金', '翡翠', 0.00, 38800.00, 8),
(101, '花戒', 'R-GOL-004', '黄金', '无', 0.00, 5600.00, 50),
(121, '钻石铂金手镯', 'B-PLA-002', '铂金', '钻石', 0.30, 52800.00, 4),
(131, '金珠珍珠耳环', 'E-PEA-002', '黄金', '珍珠', 0.00, 3800.00, 45),
(112, '十字金刚杵项链', 'N-GOL-003', '黄金', '无', 0.00, 15800.00, 20),
(141, '群镶钻石吊坠', 'P-DIA-003', '铂金', '钻石', 1.20, 68000.00, 3),
(100, '排钻戒', 'R-DIA-006', '铂金', '钻石', 0.90, 38800.00, 10),
(110, 'Y字钻石项链', 'N-DIA-004', '18K金', '钻石', 0.20, 12800.00, 30),
(120, '推拉金手镯', 'B-GOL-004', '黄金', '无', 0.00, 16800.00, 25),
(130, '珍珠钻石耳环', 'E-DIA-004', '18K金', '钻石', 0.25, 16800.00, 18),
(140, '满绿翡翠吊坠', 'P-JAD-004', '黄金', '翡翠', 0.00, 128000.00, 2),
(101, '开口黄金戒指', 'R-GOL-005', '黄金', '无', 0.00, 7800.00, 35),
(111, '巴洛克珍珠项链', 'N-PEA-004', '银', '珍珠', 0.00, 1800.00, 60),
(121, '珐琅铂金手镯', 'B-PLA-003', '铂金', '无', 0.00, 22800.00, 8),
(131, '大溪地珍珠耳环', 'E-PEA-003', '铂金', '珍珠', 0.00, 12800.00, 12),
(141, '心形钻石吊坠', 'P-DIA-004', '铂金', '钻石', 0.50, 28800.00, 10),
(100, '扭臂钻戒', 'R-DIA-007', '18K金', '钻石', 0.70, 32800.00, 12),
(110, '锁骨钻石项链', 'N-DIA-005', '铂金', '钻石', 0.40, 22800.00, 18),
(120, '珐琅金手镯', 'B-GOL-005', '黄金', '无', 0.00, 38800.00, 8),
(130, '流苏钻石耳环', 'E-DIA-005', '18K金', '钻石', 0.60, 28800.00, 15),
(140, '紫罗兰翡翠吊坠', 'P-JAD-005', '18K金', '翡翠', 0.00, 48800.00, 6);
-- 2.3 客户数据（30条）
INSERT INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES
('张美玲', '13800138001', 'zhangml@email.com', '钻石', '深圳', '2022-03-15', 286000.00),
('李华', '13800138002', 'lihua@email.com', '金卡', '广州', '2022-06-20', 128000.00),
('王芳', '13800138003', 'wangfang@email.com', '黑金', '北京', '2021-01-10', 568000.00),
('赵强', '13800138004', 'zhaoq@email.com', '银卡', '上海', '2023-02-28', 45000.00),
('陈静', '13800138005', 'chenj@email.com', '钻石', '深圳', '2022-08-15', 328000.00),
('刘洋', '13800138006', 'liuyang@email.com', '普通', '成都', '2023-05-01', 12800.00),
('周婷', '13800138007', 'zhout@email.com', '金卡', '杭州', '2022-11-20', 98000.00),
('吴磊', '13800138008', 'wulei@email.com', '普通', '武汉', '2023-08-10', 8900.00),
('郑雪', '13800138009', 'zhengx@email.com', '银卡', '南京', '2023-01-05', 36800.00),
('孙丽', '13800138010', 'sunli@email.com', '钻石', '深圳', '2021-09-18', 428000.00),
('马丽', '13800138011', 'mali@email.com', '金卡', '广州', '2022-04-22', 158000.00),
('朱伟', '13800138012', 'zhuw@email.com', '普通', '重庆', '2023-06-15', 22800.00),
('胡敏', '13800138013', 'hum@email.com', '银卡', '西安', '2022-12-01', 58800.00),
('林涛', '13800138014', 'lint@email.com', '黑金', '深圳', '2021-05-20', 688000.00),
('何燕', '13800138015', 'hey@email.com', '金卡', '长沙', '2022-09-10', 118000.00),
('高飞', '13800138016', 'gaof@email.com', '普通', '郑州', '2023-03-25', 15800.00),
('罗琳', '13800138017', 'luol@email.com', '钻石', '北京', '2021-11-08', 358000.00),
('梁军', '13800138018', 'liangj@email.com', '银卡', '天津', '2023-04-12', 42800.00),
('宋佳', '13800138019', 'songj@email.com', '金卡', '苏州', '2022-07-30', 138000.00),
('韩梅', '13800138020', 'hanm@email.com', '普通', '青岛', '2023-09-05', 6800.00),
('唐勇', '13800138021', 'tangy@email.com', '钻石', '深圳', '2021-08-15', 488000.00),
('冯娟', '13800138022', 'fengj@email.com', '金卡', '东莞', '2022-10-20', 168000.00),
('曹阳', '13800138023', 'caoy@email.com', '普通', '佛山', '2023-07-18', 19800.00),
('许娜', '13800138024', 'xuna@email.com', '银卡', '珠海', '2022-05-08', 68800.00),
('邓超', '13800138025', 'dengc@email.com', '黑金', '广州', '2021-03-22', 728000.00),
('萧红', '13800138026', 'xiaoh@email.com', '金卡', '厦门', '2022-08-28', 148000.00),
('程亮', '13800138027', 'chengl@email.com', '普通', '合肥', '2023-10-01', 12800.00),
('潘婷', '13800138028', 'pant@email.com', '钻石', '深圳', '2021-12-15', 398000.00),
('袁丽', '13800138029', 'yuanl@email.com', '银卡', '南昌', '2023-02-14', 48800.00),
('蒋文', '13800138030', 'jiangw@email.com', '金卡', '宁波', '2022-11-05', 108000.00);
-- 2.4 订单数据（30条）
INSERT INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES
(1, 1, '2024-01-05 10:30:00', '线下门店', 1, 58888.00, 0.95, '已完成'),
(2, 2, '2024-01-08 14:20:00', '天猫旗舰店', NULL, 28800.00, 1.00, '已完成'),
(3, 3, '2024-01-12 09:15:00', '线下门店', 2, 128000.00, 0.90, '已完成'),
(4, 4, '2024-01-15 16:45:00', '微信小程序', NULL, 8900.00, 1.00, '已完成'),
(5, 5, '2024-01-20 11:00:00', '抖音直播', NULL, 15800.00, 0.88, '已完成'),
(6, 6, '2024-01-25 13:30:00', '线下门店', 1, 22800.00, 1.00, '已完成'),
(7, 7, '2024-02-01 10:00:00', '天猫旗舰店', NULL, 36800.00, 0.92, '已完成'),
(8, 8, '2024-02-05 15:20:00', '微信小程序', NULL, 5800.00, 1.00, '已完成'),
(9, 9, '2024-02-10 09:30:00', '线下门店', 3, 45800.00, 0.95, '已完成'),
(10, 10, '2024-02-14 14:00:00', '天猫旗舰店', NULL, 89999.00, 0.85, '已完成'),
(11, 11, '2024-02-20 11:30:00', '线下门店', 1, 18900.00, 1.00, '已完成'),
(12, 12, '2024-03-01 10:15:00', '抖音直播', NULL, 28800.00, 0.90, '已完成'),
(13, 13, '2024-03-05 16:00:00', '微信小程序', NULL, 6800.00, 1.00, '已完成'),
(14, 14, '2024-03-10 09:00:00', '线下门店', 2, 68000.00, 0.92, '已完成'),
(15, 15, '2024-03-15 14:30:00', '天猫旗舰店', NULL, 15800.00, 1.00, '已完成'),
(16, 16, '2024-03-20 11:00:00', '线下门店', 1, 32800.00, 0.95, '已完成'),
(17, 17, '2024-04-01 10:30:00', '经销商', NULL, 128000.00, 0.80, '已完成'),
(18, 18, '2024-04-05 15:00:00', '天猫旗舰店', NULL, 22800.00, 1.00, '已完成'),
(19, 19, '2024-04-10 09:45:00', '线下门店', 3, 42800.00, 0.90, '已完成'),
(20, 20, '2024-04-15 14:00:00', '微信小程序', NULL, 5800.00, 1.00, '已完成'),
(21, 21, '2024-05-01 10:00:00', '线下门店', 1, 52800.00, 0.95, '已完成'),
(22, 22, '2024-05-05 13:30:00', '天猫旗舰店', NULL, 18600.00, 1.00, '已完成'),
(23, 23, '2024-05-10 16:00:00', '抖音直播', NULL, 38800.00, 0.88, '已完成'),
(24, 24, '2024-05-15 11:00:00', '线下门店', 2, 28800.00, 0.92, '已完成'),
(25, 25, '2024-06-01 09:30:00', '经销商', NULL, 98000.00, 0.78, '已完成'),
(26, 26, '2024-06-05 14:00:00', '天猫旗舰店', NULL, 12800.00, 1.00, '已完成'),
(27, 27, '2024-06-10 10:30:00', '线下门店', 1, 35800.00, 0.95, '已完成'),
(28, 28, '2024-06-15 15:30:00', '微信小程序', NULL, 8900.00, 1.00, '已完成'),
(29, 29, '2024-07-01 09:00:00', '线下门店', 3, 48800.00, 0.90, '已完成'),
(30, 30, '2024-07-05 14:30:00', '天猫旗舰店', NULL, 15800.00, 1.00, '已完成');
-- 2.5 订单明细数据
INSERT INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES
(1, 1, 1, 58888.00, 0.95),
(2, 2, 2, 28800.00, 1.00),
(3, 3, 7, 128000.00, 0.90),
(4, 4, 4, 8900.00, 1.00),
(5, 5, 8, 15800.00, 0.88),
(6, 6, 11, 22800.00, 1.00),
(7, 7, 9, 36800.00, 0.92),
(8, 8, 17, 5800.00, 1.00),
(9, 9, 18, 45800.00, 0.95),
(10, 10, 3, 89999.00, 0.85),
(11, 11, 14, 18900.00, 1.00),
(12, 12, 23, 28800.00, 0.90),
(13, 13, 21, 6800.00, 1.00),
(14, 14, 18, 68000.00, 0.92),
(15, 15, 8, 15800.00, 1.00),
(16, 16, 20, 32800.00, 0.95),
(17, 17, 3, 128000.00, 0.80),
(18, 18, 24, 22800.00, 1.00),
(19, 19, 25, 42800.00, 0.90),
(20, 20, 17, 5800.00, 1.00),
(21, 21, 32, 52800.00, 0.95),
(22, 22, 24, 18600.00, 1.00),
(23, 23, 31, 38800.00, 0.88),
(24, 24, 20, 28800.00, 0.92),
(25, 25, 3, 98000.00, 0.78),
(26, 26, 44, 12800.00, 1.00),
(27, 27, 13, 35800.00, 0.95),
(28, 28, 17, 8900.00, 1.00),
(29, 29, 18, 48800.00, 0.90),
(30, 30, 8, 15800.00, 1.00);
-- 2.7 完成提示
SELECT '02_data.sql 执行完成' AS result;
-- ============================================================================
-- 文件：03_queries.sql
-- 说明：MySQL 9.0 珠宝行业查询设计模式实战
-- 覆盖模块：高效JOIN / 分页优化 / 搜索优化 / 窗口函数 / 递归CTE
-- 执行顺序：第 3 步（需先执行 01_schema.sql 和 02_data.sql）
-- 适用版本：MySQL 9.0+
-- ============================================================================
USE JewelryDemo;
-- ============================================================================
-- 模块一：高效JOIN连接查询
-- ============================================================================
SELECT '========== 模块一：高效JOIN连接查询 ==========' AS section;
-- 1.1 小表驱动大表 + Hash Join（MySQL 8.0+ 引入，9.0 优化器自动选择）
-- 场景：查询金卡及以上客户的订单明细
-- 优化点：先过滤小结果集再关联，避免大表全量扫描
EXPLAIN FORMAT=TREE
SELECT c.customer_name, c.membership, o.order_id, o.total_amount
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.membership IN ('金卡', '钻石')
AND o.order_date >= '2024-01-01';
-- 1.2 延迟关联（Deferred Join）优化深分页关联查询
-- 场景：查询第50001-50010条已完成订单的客户与商品详情
-- 传统写法会回表50010次，优化后仅回表10次
SELECT o.order_id, o.order_date, o.total_amount,
c.customer_name, p.product_name
FROM orders o
-- 子查询仅通过覆盖索引获取主键，避免回表
INNER JOIN (
SELECT order_id
FROM orders
WHERE status = '已完成'
ORDER BY order_date DESC
LIMIT 50000, 10
) AS tmp ON o.order_id = tmp.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id;
-- 1.3 半连接优化：用 EXISTS 替代 IN（大数据量场景）
-- 场景：查询购买过钻石类商品的客户
-- EXISTS 在子查询结果集大时性能优于 IN
SELECT customer_id, customer_name, membership
FROM customers c
WHERE EXISTS (
SELECT 1 FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.customer_id = c.customer_id
AND p.cat_id IN (100, 101, 102, 110, 111, 112, 120, 121, 130, 131, 140, 141)
);
-- 1.4 自连接：查询同一客户间隔30天内的复购订单
SELECT
o1.order_id AS first_order,
o2.order_id AS repeat_order,
o1.customer_id,
o1.order_date AS first_date,
o2.order_date AS repeat_date,
DATEDIFF(o2.order_date, o1.order_date) AS days_gap
FROM orders o1
INNER JOIN orders o2
ON o1.customer_id = o2.customer_id
AND o2.order_date > o1.order_date
AND o2.order_date <= DATE_ADD(o1.order_date, INTERVAL 30 DAY)
WHERE o1.status = '已完成' AND o2.status = '已完成'
ORDER BY days_gap ASC
LIMIT 20;
-- 1.5 CTE提前过滤优化复杂多表关联
-- 场景：查询各品类销售TOP10商品（先过滤再关联）
WITH filtered_orders AS (
SELECT order_id, customer_id, order_date
FROM orders
WHERE status = '已完成'
AND order_date >= '2024-01-01'
),
product_sales AS (
SELECT
p.product_id,
p.product_name,
jc.cat_name AS category,
SUM(oi.quantity * oi.unit_price * oi.discount) AS total_sales,
COUNT(DISTINCT fo.order_id) AS order_count
FROM filtered_orders fo
INNER JOIN order_items oi ON fo.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
GROUP BY p.product_id, p.product_name, jc.cat_name
)
SELECT * FROM product_sales
ORDER BY total_sales DESC
LIMIT 10;
-- ============================================================================
-- 模块二：分页优化（Pagination）
-- ============================================================================
SELECT '========== 模块二：分页优化 ==========' AS section;
-- 2.1 游标分页（Cursor-based）：适用于瀑布流/无限滚动场景
-- 场景：小程序商品列表连续翻页，记录上一页最后一条的ID
-- 性能：O(1)复杂度，无论翻到第几页都毫秒级响应
SET @last_product_id = 5;
SET @page_size = 10;
SELECT product_id, product_name, unit_price, stock_qty
FROM products
WHERE product_id > @last_product_id
AND status = '在售'
ORDER BY product_id ASC
LIMIT @page_size;
-- 2.2 延迟关联分页：适用于后台管理系统跳页场景
-- 场景：后台订单列表跳转到第8000页
-- 原理：子查询通过覆盖索引定位主键，再回表取完整数据
SET @page_num = 8000;
SET @page_size = 20;
SELECT o.order_id, o.order_date, o.total_amount, o.status,
c.customer_name, c.phone
FROM orders o
INNER JOIN (
SELECT order_id
FROM orders
WHERE status = '已完成'
ORDER BY order_date DESC
LIMIT (@page_num - 1) * @page_size, @page_size
) AS tmp ON o.order_id = tmp.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
ORDER BY o.order_date DESC;
-- 2.3 窗口函数分页：适用于复杂排序条件的分页
-- 场景：按客户消费总额排名后分页，取第101-110名
WITH customer_ranking AS (
SELECT
customer_id,
customer_name,
total_spent,
ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS rn
FROM customers
)
SELECT customer_id, customer_name, total_spent, rn
FROM customer_ranking
WHERE rn BETWEEN 101 AND 110;
-- ============================================================================
-- 模块三：搜索优化查询（Search-Optimized Queries）
-- ============================================================================
SELECT '========== 模块三：搜索优化查询 ==========' AS section;
-- 3.1 全文搜索：商品名称模糊查询
-- 场景：客户搜索含"钻石"或"金"的商品
-- 优势：LIKE '%keyword%' 无法利用索引，FULLTEXT索引性能高
-- 先创建全文索引（如果尚未创建）
ALTER TABLE products ADD FULLTEXT INDEX ft_product_name (product_name);
SELECT product_id, product_name, unit_price, cat_id,
MATCH(product_name) AGAINST('钻石 金' IN NATURAL LANGUAGE MODE) AS relevance
FROM products
WHERE MATCH(product_name) AGAINST('钻石 金' IN NATURAL LANGUAGE MODE)
ORDER BY relevance DESC;
-- 3.2 覆盖索引优化：仅查询索引列避免回表
-- 场景：统计各会员等级的客户数量与平均消费
-- 创建覆盖索引
ALTER TABLE customers ADD INDEX idx_membership_spent (membership, total_spent);
-- 查询直接从索引树获取数据，无需回表
SELECT membership,
COUNT(*) AS cust_count,
ROUND(AVG(total_spent), 2) AS avg_spent
FROM customers
GROUP BY membership;
-- 3.3 前缀索引优化长文本字段
-- 场景：商品描述字段较长，建立前缀索引节省空间
ALTER TABLE products ADD COLUMN product_desc VARCHAR(500) DEFAULT '';
UPDATE products SET product_desc = CONCAT(product_name, '-精美珠宝工艺品') WHERE product_id <= 10;
-- 计算合适的前缀长度
SELECT
COUNT(DISTINCT LEFT(product_desc, 10)) / COUNT(*) AS sel_10,
COUNT(DISTINCT LEFT(product_desc, 20)) / COUNT(*) AS sel_20,
COUNT(DISTINCT product_desc) / COUNT(*) AS sel_full
FROM products;
-- 建立前缀索引（注意：前缀索引不支持 ORDER BY/GROUP BY）
ALTER TABLE products ADD INDEX idx_desc_prefix (product_desc(30));
-- 3.4 动态参数化搜索（多条件可选）
-- 场景：后台商品搜索，支持按品类/金属类型/价格范围多条件组合
SET @cat_id = NULL;
SET @metal_type = NULL;
SET @min_price = 0;
SET @max_price = 100000;
SELECT p.product_id, p.product_name, p.unit_price, p.metal_type,
jc.cat_name AS category
FROM products p
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE (@cat_id IS NULL OR p.cat_id = @cat_id)
AND (@metal_type IS NULL OR p.metal_type = @metal_type)
AND p.unit_price >= @min_price
AND p.unit_price <= @max_price
ORDER BY p.unit_price DESC
LIMIT 20;
-- ============================================================================
-- 模块四：窗口函数（Window Functions）
-- ============================================================================
SELECT '========== 模块四：窗口函数实战 ==========' AS section;
-- 4.1 排名函数：各销售渠道的订单金额排名
-- 场景：统计各销售人员的业绩排名（含并列）
SELECT
sales_channel,
order_id,
total_amount,
ROW_NUMBER() OVER (PARTITION BY sales_channel ORDER BY total_amount DESC) AS row_rank,
RANK() OVER (PARTITION BY sales_channel ORDER BY total_amount DESC) AS rank_gap,
DENSE_RANK() OVER (PARTITION BY sales_channel ORDER BY total_amount DESC) AS dense_rank_no_gap
FROM orders
WHERE status = '已完成'
AND order_date >= '2024-01-01'
ORDER BY sales_channel, row_rank
LIMIT 30;
-- 4.2 LAG/LEAD：计算每日销售额环比
-- 场景：分析门店每日销售额变化趋势
WITH daily_sales AS (
SELECT
DATE(order_date) AS sale_date,
SUM(total_amount) AS daily_amount,
COUNT(*) AS order_count
FROM orders
WHERE status = '已完成'
AND order_date >= '2024-01-01'
GROUP BY DATE(order_date)
)
SELECT
sale_date,
daily_amount,
order_count,
LAG(daily_amount, 1) OVER (ORDER BY sale_date) AS prev_day_amount,
ROUND((daily_amount - LAG(daily_amount, 1) OVER (ORDER BY sale_date))
/ LAG(daily_amount, 1) OVER (ORDER BY sale_date) * 100, 2) AS day_over_day_pct,
LEAD(daily_amount, 1) OVER (ORDER BY sale_date) AS next_day_amount
FROM daily_sales
ORDER BY sale_date
LIMIT 30;
-- 4.3 累计求和与移动平均
-- 场景：计算客户累计消费金额与3日移动平均订单量
WITH daily_stats AS (
SELECT
DATE(order_date) AS sale_date,
SUM(total_amount) AS daily_amount,
COUNT(*) AS order_count
FROM orders
WHERE status = '已完成'
GROUP BY DATE(order_date)
)
SELECT
sale_date,
daily_amount,
SUM(daily_amount) OVER (ORDER BY sale_date) AS cumulative_amount,
ROUND(AVG(order_count) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
), 2) AS moving_avg_3day
FROM daily_stats
ORDER BY sale_date
LIMIT 30;
-- 4.4 NTILE分桶：客户价值分层（RFM模型简化版）
-- 场景：将客户按消费金额分为5个等级
SELECT
customer_id,
customer_name,
total_spent,
NTILE(5) OVER (ORDER BY total_spent DESC) AS spend_tier,
CASE NTILE(5) OVER (ORDER BY total_spent DESC)
WHEN 1 THEN '高价值客户'
WHEN 2 THEN '中高价值客户'
WHEN 3 THEN '中等价值客户'
WHEN 4 THEN '中低价值客户'
ELSE '低价值客户'
END AS tier_label
FROM customers
ORDER BY spend_tier, total_spent DESC
LIMIT 30;
-- 4.5 FIRST_VALUE/LAST_VALUE：每个品类的首单和末单
-- 场景：分析各品类的首次成交和最近成交客户
WITH order_ranked AS (
SELECT
p.product_name,
jc.cat_name AS category,
c.customer_name,
o.order_date,
o.total_amount,
FIRST_VALUE(c.customer_name) OVER (
PARTITION BY jc.cat_name ORDER BY o.order_date ASC
) AS first_customer,
LAST_VALUE(c.customer_name) OVER (
PARTITION BY jc.cat_name ORDER BY o.order_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_customer,
FIRST_VALUE(o.total_amount) OVER (
PARTITION BY jc.cat_name ORDER BY o.order_date ASC
) AS first_order_amount,
LAST_VALUE(o.total_amount) OVER (
PARTITION BY jc.cat_name ORDER BY o.order_date ASC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_order_amount
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
)
SELECT DISTINCT category, first_customer, last_customer, first_order_amount, last_order_amount
FROM order_ranked
ORDER BY category;
-- ============================================================================
-- 模块五：递归CTE（Recursive CTEs）
-- ============================================================================
SELECT '========== 模块五：递归CTE实战 ==========' AS section;
-- 5.1 珠宝分类树展开：查询"珠宝首饰"下的所有子分类（任意层级）
WITH RECURSIVE category_tree AS (
-- 锚点：根节点
SELECT cat_id, parent_id, cat_name, cat_level,
CAST(cat_name AS CHAR(200)) AS path
FROM jewelry_category
WHERE cat_id = 1
UNION ALL
-- 递归：查找子节点
SELECT c.cat_id, c.parent_id, c.cat_name, c.cat_level,
CONCAT(ct.path, ' > ', c.cat_name)
FROM jewelry_category c
INNER JOIN category_tree ct ON c.parent_id = ct.cat_id
)
SELECT cat_id, parent_id, cat_name, cat_level, path
FROM category_tree
ORDER BY cat_level, cat_id;
-- 5.2 计算分类深度：查询每个分类的完整层级路径
WITH RECURSIVE category_path AS (
SELECT cat_id, parent_id, cat_name, cat_level,
CAST(cat_name AS CHAR(500)) AS full_path
FROM jewelry_category
WHERE parent_id IS NULL -- 根节点
UNION ALL
SELECT c.cat_id, c.parent_id, c.cat_name, c.cat_level,
CONCAT(cp.full_path, ' / ', c.cat_name)
FROM jewelry_category c
INNER JOIN category_path cp ON c.parent_id = cp.cat_id
)
SELECT cat_id, parent_id, cat_name, cat_level, full_path
FROM category_path
ORDER BY cat_level, cat_id;
-- 5.3 递归CTE实战：查找某品类下的所有子孙品类及其商品
-- 场景：查询"戒指"分类下的所有商品（包括子分类）
WITH RECURSIVE relevant_cats AS (
-- 锚点：戒指分类及其子分类
SELECT cat_id FROM jewelry_category WHERE cat_name = '戒指'
UNION ALL
SELECT jc.cat_id FROM jewelry_category jc
INNER JOIN relevant_cats rc ON jc.parent_id = rc.cat_id
)
SELECT p.product_id, p.product_name, p.unit_price, jc.cat_name AS category
FROM products p
INNER JOIN relevant_cats rc ON p.cat_id = rc.cat_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
ORDER BY p.unit_price DESC;
-- 5.4 递归CTE实战：员工层级树（珠宝连锁店组织架构）
-- 先创建门店员工表
CREATE TABLE IF NOT EXISTS store_staff (
staff_id INT AUTO_INCREMENT PRIMARY KEY,
staff_name VARCHAR(50) NOT NULL,
parent_id INT DEFAULT NULL COMMENT '上级ID',
position VARCHAR(50) NOT NULL COMMENT '职位',
store_id INT DEFAULT NULL,
INDEX idx_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 插入示例数据
INSERT INTO store_staff (staff_name, parent_id, position, store_id) VALUES
('张总', NULL, '总经理', 1),
('李经理', 1, '门店经理', 1),
('王主管', 2, '销售主管', 1),
('赵销售', 3, '销售员', 1),
('钱销售', 3, '销售员', 1),
('孙会计', 2, '会计', 1),
('刘总', NULL, '总经理', 2),
('陈经理', 6, '门店经理', 2),
('周主管', 7, '销售主管', 2),
('吴销售', 8, '销售员', 2);
-- 递归查询某门店的组织架构树
WITH RECURSIVE org_tree AS (
-- 锚点：根节点（总经理）
SELECT staff_id, staff_name, parent_id, position, store_id,
1 AS level,
CAST(staff_name AS CHAR(200)) AS tree_path
FROM store_staff
WHERE parent_id IS NULL AND store_id = 1
UNION ALL
-- 递归：查找下级
SELECT s.staff_id, s.staff_name, s.parent_id, s.position, s.store_id,
ot.level + 1,
CONCAT(ot.tree_path, ' -> ', s.staff_name)
FROM store_staff s
INNER JOIN org_tree ot ON s.parent_id = ot.staff_id
WHERE s.store_id = 1
)
SELECT staff_id, staff_name, position, level, tree_path
FROM org_tree
ORDER BY level, staff_id;
-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT '03_queries.sql 执行完成' AS result;
-- ============================================================================
-- 文件：04_pivot_grouping.sql
-- 说明：MySQL 9.0 珠宝行业 PIVOT/UNPIVOT 透视转换 + GROUPING SETS多级聚合实战
-- 执行顺序：第 4 步（需先执行 01_schema.sql 和 02_data.sql）
-- 适用版本：MySQL 9.0+
-- ============================================================================
USE JewelryDemo;
-- ============================================================================
-- 模块六：透视转换 PIVOT / UNPIVOT 实战
-- ============================================================================
SELECT '========== 模块六：PIVOT/UNPIVOT透视转换实战 ==========' AS section;
-- 6.1 行转列（PIVOT）：各品类按月销售额透视
-- 场景：管理层需要看各品类 2024年 各月的月度销售对比
-- MySQL 9.0 仍无原生 PIVOT 语法，使用条件聚合实现（与 Oracle/SQL Server 的 PIVOT 等价）
SELECT
cat_name AS '品类',
SUM(CASE WHEN sale_month = '2024-01' THEN month_amount ELSE 0 END) AS '1月',
SUM(CASE WHEN sale_month = '2024-02' THEN month_amount ELSE 0 END) AS '2月',
SUM(CASE WHEN sale_month = '2024-03' THEN month_amount ELSE 0 END) AS '3月',
SUM(CASE WHEN sale_month = '2024-04' THEN month_amount ELSE 0 END) AS '4月',
SUM(CASE WHEN sale_month = '2024-05' THEN month_amount ELSE 0 END) AS '5月',
SUM(CASE WHEN sale_month = '2024-06' THEN month_amount ELSE 0 END) AS '6月',
SUM(month_amount) AS '上半年合计'
FROM (
SELECT
jc.cat_name,
DATE_FORMAT(o.order_date, '%Y-%m') AS sale_month,
SUM(oi.quantity * oi.unit_price * oi.discount) AS month_amount
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND jc.cat_level = 2
AND o.order_date >= '2024-01-01' AND o.order_date < '2024-07-01'
GROUP BY jc.cat_name, DATE_FORMAT(o.order_date, '%Y-%m')
) AS monthly_sales
GROUP BY cat_name
ORDER BY '上半年合计' DESC;
-- 6.2 行转列（PIVOT）：各销售渠道季度业绩对比
-- 场景：对比"线下门店/天猫旗舰店/微信小程序"三个渠道 2024年 Q1-Q4 的业绩
SELECT
sales_channel AS '销售渠道',
SUM(CASE WHEN quarter = 'Q1' THEN q_amount ELSE 0 END) AS 'Q1',
SUM(CASE WHEN quarter = 'Q2' THEN q_amount ELSE 0 END) AS 'Q2',
SUM(CASE WHEN quarter = 'Q3' THEN q_amount ELSE 0 END) AS 'Q3',
SUM(CASE WHEN quarter = 'Q4' THEN q_amount ELSE 0 END) AS 'Q4',
SUM(q_amount) AS '年度总计'
FROM (
SELECT
sales_channel,
CONCAT('Q', QUARTER(order_date)) AS quarter,
SUM(total_amount) AS q_amount
FROM orders
WHERE status = '已完成'
AND YEAR(order_date) = 2024
GROUP BY sales_channel, QUARTER(order_date)
) AS quarterly_sales
GROUP BY sales_channel
ORDER BY '年度总计' DESC;
-- 6.3 动态行转列：品类不固定时的通用写法
-- 场景：珠宝品类可能新增，用动态SQL自动生成透视列
SET @pivot_sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE WHEN cat_name = ''', cat_name, ''' THEN month_amount ELSE 0 END) AS `', cat_name, '`'
)
) INTO @pivot_sql
FROM jewelry_category
WHERE cat_level = 2;
SET @pivot_sql = CONCAT(
'SELECT sale_month, ', @pivot_sql, ', SUM(month_amount) AS 合计
FROM (
SELECT jc.cat_name, DATE_FORMAT(o.order_date, "%Y-%m") AS sale_month,
SUM(oi.quantity * oi.unit_price * oi.discount) AS month_amount
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = "已完成"
AND o.order_date >= "2024-01-01" AND o.order_date < "2024-07-01"
GROUP BY jc.cat_name, DATE_FORMAT(o.order_date, "%Y-%m")
) AS t
GROUP BY sale_month
ORDER BY sale_month'
);
PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- 6.4 列转行（UNPIVOT）：宽表还原为长表
-- 场景：现有月度销售宽表，需还原为长表以便做趋势分析或导入BI工具
-- 先构建一张演示用的宽表
DROP TABLE IF EXISTS monthly_sales_wide;
CREATE TABLE monthly_sales_wide (
cat_name VARCHAR(50),
m_01 DECIMAL(12,2), m_02 DECIMAL(12,2), m_03 DECIMAL(12,2),
m_04 DECIMAL(12,2), m_05 DECIMAL(12,2), m_06 DECIMAL(12,2)
);
INSERT INTO monthly_sales_wide VALUES
('黄金', 120000, 98000, 135000, 110000, 128000, 142000),
('钻石', 85000, 72000, 91000, 88000, 95000, 102000),
('翡翠', 45000, 38000, 52000, 41000, 48000, 55000),
('珍珠', 22000, 19000, 25000, 21000, 23000, 27000);
-- MySQL 无原生 UNPIVOT，使用 UNION ALL 实现
SELECT cat_name AS '品类', '1月' AS '月份', m_01 AS '销售额' FROM monthly_sales_wide WHERE m_01 IS NOT NULL
UNION ALL
SELECT cat_name, '2月', m_02 FROM monthly_sales_wide WHERE m_02 IS NOT NULL
UNION ALL
SELECT cat_name, '3月', m_03 FROM monthly_sales_wide WHERE m_03 IS NOT NULL
UNION ALL
SELECT cat_name, '4月', m_04 FROM monthly_sales_wide WHERE m_04 IS NOT NULL
UNION ALL
SELECT cat_name, '5月', m_05 FROM monthly_sales_wide WHERE m_05 IS NOT NULL
UNION ALL
SELECT cat_name, '6月', m_06 FROM monthly_sales_wide WHERE m_06 IS NOT NULL
ORDER BY cat_name, '月份';
-- 6.5 动态列转行：列数不固定时的通用写法
-- 场景：宽表列数动态变化，用动态SQL自动生成UNION ALL
SET @unpivot_sql = NULL;
SELECT GROUP_CONCAT(
CONCAT(
'SELECT cat_name, ''', COLUMN_NAME, ''' AS month, ', COLUMN_NAME, ' AS amount FROM monthly_sales_wide WHERE ', COLUMN_NAME, ' IS NOT NULL'
)
SEPARATOR ' UNION ALL '
) INTO @unpivot_sql
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'JewelryDemo'
AND TABLE_NAME = 'monthly_sales_wide'
AND COLUMN_NAME LIKE 'm_%';
SET @unpivot_sql = CONCAT(@unpivot_sql, ' ORDER BY cat_name, month');
PREPARE stmt FROM @unpivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- ============================================================================
-- 模块七：多级聚合 GROUPING SETS / ROLLUP / CUBE 实战
-- ============================================================================
SELECT '========== 模块七：GROUPING SETS多级聚合实战 ==========' AS section;
-- 7.1 基础GROUPING SETS：多维度销售汇总
-- 场景：一份报表同时需要"按品类汇总"、"按渠道汇总"、"全公司总计"三种粒度
-- 传统写法需要3个SELECT + UNION ALL，GROUPING SETS只需扫描一次表
SELECT
jc.cat_name AS '品类',
o.sales_channel AS '渠道',
SUM(oi.quantity * oi.unit_price * oi.discount) AS '销售额',
COUNT(*) AS '订单数',
GROUPING(jc.cat_name) AS g_cat,
GROUPING(o.sales_channel) AS g_channel
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01'
GROUP BY GROUPING SETS (
(jc.cat_name, o.sales_channel),
(jc.cat_name),
(o.sales_channel),
()
)
ORDER BY g_cat, g_channel, '销售额' DESC;
-- 7.2 用COALESCE美化聚合行的NULL占位符
-- 场景：将聚合产生的NULL替换为"【合计】"等可读标签
SELECT
COALESCE(jc.cat_name, '【全品类】') AS '品类',
COALESCE(o.sales_channel, '【全渠道】') AS '渠道',
ROUND(SUM(oi.quantity * oi.unit_price * oi.discount), 2) AS '销售额',
COUNT(*) AS '订单数',
AVG(oi.unit_price * oi.discount) AS '平均客单价'
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01'
GROUP BY GROUPING SETS (
(jc.cat_name, o.sales_channel),
(jc.cat_name),
(o.sales_channel),
()
)
ORDER BY GROUPING(jc.cat_name), GROUPING(o.sales_channel);
-- 7.3 ROLLUP：层级汇总（珠宝分类树的小计+总计）
-- 场景：按"一级分类 > 二级分类"层级展示销售额，每级都有小计，最后有总计
-- ROLLUP(a, b) 等价于 GROUPING SETS((a, b), (a), ())
SELECT
COALESCE(parent_cat.cat_name, '【总计】') AS '一级分类',
COALESCE(child_cat.cat_name, '【小计】') AS '二级分类',
ROUND(SUM(oi.quantity * oi.unit_price * oi.discount), 2) AS '销售额',
COUNT(DISTINCT o.order_id) AS '订单数'
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category child_cat ON p.cat_id = child_cat.cat_id
LEFT JOIN jewelry_category parent_cat ON child_cat.parent_id = parent_cat.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01'
AND child_cat.cat_level = 2
GROUP BY ROLLUP (parent_cat.cat_name, child_cat.cat_name)
ORDER BY parent_cat.cat_name, child_cat.cat_name;
-- 7.4 CUBE：全维度交叉汇总
-- 场景：需要"品类x渠道x会员等级"所有可能的维度组合（2^3 = 8种分组）
-- CUBE(a, b, c) 等价于 GROUPING SETS 列出所有8种组合
SELECT
COALESCE(jc.cat_name, '【全品类】') AS '品类',
COALESCE(o.sales_channel, '【全渠道】') AS '渠道',
COALESCE(c.membership, '【全等级】') AS '会员等级',
ROUND(SUM(oi.quantity * oi.unit_price * oi.discount), 2) AS '销售额',
GROUPING_ID(jc.cat_name, o.sales_channel, c.membership) AS grouping_id
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01'
GROUP BY CUBE (jc.cat_name, o.sales_channel, c.membership)
ORDER BY grouping_id, '销售额' DESC
LIMIT 50;
-- 7.5 GROUPING SETS嵌套：复杂不对称分组
-- 场景：需要"品类x渠道"的ROLLUP（含小计），同时额外加上"按城市"的独立汇总
-- 这是 CUBE/ROLLUP 无法直接实现的，只有 GROUPING SETS 能精确控制
SELECT
COALESCE(jc.cat_name, '【品类小计】') AS '品类',
COALESCE(o.sales_channel, '【渠道小计】') AS '渠道',
COALESCE(c.city, '【城市汇总】') AS '城市',
ROUND(SUM(oi.quantity * oi.unit_price * oi.discount), 2) AS '销售额'
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01'
GROUP BY GROUPING SETS (
ROLLUP(jc.cat_name, o.sales_channel),
(c.city)
)
ORDER BY jc.cat_name, o.sales_channel, c.city
LIMIT 60;
-- 7.6 GROUPING SETS vs UNION ALL 性能对比验证
-- 场景：用EXPLAIN对比两种写法的执行计划
-- 传统UNION ALL写法（扫描4次表）
EXPLAIN FORMAT=TREE
SELECT jc.cat_name, NULL AS sales_channel, SUM(oi.quantity * oi.unit_price * oi.discount) AS total_amt
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成' AND o.order_date >= '2024-01-01'
GROUP BY jc.cat_name
UNION ALL
SELECT NULL, o.sales_channel, SUM(oi.quantity * oi.unit_price * oi.discount)
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = '已完成' AND o.order_date >= '2024-01-01'
GROUP BY o.sales_channel
UNION ALL
SELECT NULL, NULL, SUM(oi.quantity * oi.unit_price * oi.discount)
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = '已完成' AND o.order_date >= '2024-01-01';
-- GROUPING SETS写法（仅扫描1次表，优化器自动复用中间结果）
EXPLAIN FORMAT=TREE
SELECT jc.cat_name, o.sales_channel, SUM(oi.quantity * oi.unit_price * oi.discount) AS total_amt
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成' AND o.order_date >= '2024-01-01'
GROUP BY GROUPING SETS ((jc.cat_name), (o.sales_channel), ());
-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT '04_pivot_grouping.sql 执行完成' AS result;
-- ============================================================================
-- 文件：05_antipatterns.sql
-- 说明：MySQL 9.0 珠宝行业 反模式规避 + 综合实战报表
-- 执行顺序：第 5 步（需先执行 01_schema.sql 和 02_data.sql）
-- 适用版本：MySQL 9.0+
-- ============================================================================
USE JewelryDemo;
-- ============================================================================
-- 模块八：反模式规避（Anti-Patterns）
-- ============================================================================
SELECT '========== 模块八：反模式规避 ==========' AS section;
-- 8.1 反模式：在WHERE中对索引列使用函数（索引失效）
-- 错误写法：YEAR(order_date) = 2024 导致全表扫描
-- EXPLAIN SELECT * FROM orders WHERE YEAR(order_date) = 2024;
-- 正确写法：使用范围查询，可利用idx_order_date索引
EXPLAIN FORMAT=TREE
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01';
-- 8.2 反模式：SELECT * 导致覆盖索引失效
-- 错误写法：SELECT * 迫使回表取所有列
-- EXPLAIN SELECT * FROM orders WHERE status = '已完成';
-- 正确写法：只查需要的列，命中覆盖索引
EXPLAIN FORMAT=TREE
SELECT order_id, order_date, total_amount, status
FROM orders
WHERE status = '已完成';
-- 8.3 反模式：隐式类型转换导致索引失效
-- 错误写法：phone是VARCHAR，用数字比较会触发隐式转换
-- EXPLAIN SELECT * FROM customers WHERE phone = 13800138000;
-- 正确写法：字符串类型用字符串比较
EXPLAIN FORMAT=TREE
SELECT customer_id, customer_name, phone
FROM customers
WHERE phone = '13800138001';
-- 8.4 反模式：OR条件导致索引失效
-- 错误写法：OR连接不同列，优化器可能放弃索引
-- EXPLAIN SELECT * FROM orders WHERE status = '已完成' OR total_amount > 50000;
-- 正确写法：拆分为UNION ALL，各分支可独立用索引
EXPLAIN FORMAT=TREE
SELECT order_id, status, total_amount FROM orders WHERE status = '已完成'
UNION ALL
SELECT order_id, status, total_amount FROM orders WHERE total_amount > 50000 AND status != '已完成';
-- 8.5 反模式：不当使用DISTINCT去重
-- 场景：用DISTINCT去重但没加ORDER BY，可能导致排序文件表
-- 错误写法
-- SELECT DISTINCT customer_id, customer_name FROM customers ORDER BY customer_id;
-- 正确写法：先排序再DISTINCT，或用ROW_NUMBER()
WITH ranked_customers AS (
SELECT
customer_id,
customer_name,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY customer_id) AS rn
FROM customers
)
SELECT customer_id, customer_name
FROM ranked_customers
WHERE rn = 1
ORDER BY customer_id;
-- 8.6 反模式：在JOIN条件中使用函数
-- 错误写法：JOIN条件中对列使用函数导致索引失效
-- SELECT * FROM orders o JOIN customers c ON YEAR(o.order_date) = YEAR(c.reg_date);
-- 正确写法：将函数应用到常量侧或使用范围比较
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.order_date >= c.reg_date
AND o.order_date < DATE_ADD(c.reg_date, INTERVAL 1 YEAR);
-- 8.7 反模式：过度使用子查询而非JOIN
-- 场景：用子查询获取每个订单的客户名（N+1问题）
-- 错误写法（N+1查询）
-- SELECT order_id,
-- (SELECT customer_name FROM customers WHERE customer_id = o.customer_id) AS customer_name
-- FROM orders o;
-- 正确写法：使用JOIN
SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;
-- ============================================================================
-- 模块九：综合实战 - 珠宝业务全景报表
-- ============================================================================
SELECT '========== 模块九：综合实战-珠宝业务全景报表 ==========' AS section;
-- 9.1 多维度透视 + 多级聚合：2024年各品类x渠道x季度的销售矩阵
WITH quarterly_category_sales AS (
SELECT
jc.cat_name,
o.sales_channel,
CONCAT('Q', QUARTER(o.order_date)) AS quarter,
SUM(oi.quantity * oi.unit_price * oi.discount) AS sales_amount,
COUNT(DISTINCT o.order_id) AS order_count,
COUNT(DISTINCT o.customer_id) AS customer_count
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.order_id
INNER JOIN products p ON oi.product_id = p.product_id
INNER JOIN jewelry_category jc ON p.cat_id = jc.cat_id
WHERE o.status = '已完成'
AND o.order_date >= '2024-01-01' AND o.order_date < '2025-01-01'
GROUP BY jc.cat_name, o.sales_channel, QUARTER(o.order_date)
)
SELECT
cat_name AS '品类',
sales_channel AS '渠道',
quarter AS '季度',
sales_amount AS '销售额',
order_count AS '订单数',
customer_count AS '客户数'
FROM quarterly_category_sales
ORDER BY cat_name, quarter, sales_channel;
-- 9.2 客户RFM分析（Recency, Frequency, Monetary）
-- R：最近一次购买距今的天数
-- F：购买次数
-- M：消费总金额
WITH rfm_base AS (
SELECT
c.customer_id,
c.customer_name,
c.membership,
MAX(o.order_date) AS last_order_date,
DATEDIFF(CURRENT_DATE, MAX(o.order_date)) AS recency,
COUNT(DISTINCT o.order_id) AS frequency,
SUM(o.total_amount) AS monetary
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
GROUP BY c.customer_id, c.customer_name, c.membership
)
SELECT
customer_id,
customer_name,
membership,
recency,
frequency,
monetary,
NTILE(5) OVER (ORDER BY recency ASC) AS r_score,
NTILE(5) OVER (ORDER BY frequency ASC) AS f_score,
NTILE(5) OVER (ORDER BY monetary ASC) AS m_score,
CONCAT(NTILE(5) OVER (ORDER BY recency ASC),
NTILE(5) OVER (ORDER BY frequency ASC),
NTILE(5) OVER (ORDER BY monetary ASC)) AS rfm_tag,
CASE
WHEN NTILE(5) OVER (ORDER BY monetary ASC) >= 4
AND NTILE(5) OVER (ORDER BY frequency ASC) >= 4 THEN '高价值客户'
WHEN NTILE(5) OVER (ORDER BY monetary ASC) >= 3
AND NTILE(5) OVER (ORDER BY frequency ASC) >= 3 THEN '潜力客户'
WHEN recency > 365 THEN '流失客户'
ELSE '普通客户'
END AS customer_segment
FROM rfm_base
ORDER BY monetary DESC
LIMIT 30;
-- 9.3 销售趋势分析：月度同比/环比
WITH monthly_sales AS (
SELECT
DATE_FORMAT(order_date, '%Y-%m') AS sale_month,
SUM(total_amount) AS monthly_amount,
COUNT(*) AS monthly_orders,
COUNT(DISTINCT customer_id) AS monthly_customers
FROM orders
WHERE status = '已完成'
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
),
monthly_with_prev AS (
SELECT
sale_month,
monthly_amount,
monthly_orders,
monthly_customers,
LAG(monthly_amount) OVER (ORDER BY sale_month) AS prev_month_amount,
LAG(monthly_orders) OVER (ORDER BY sale_month) AS prev_month_orders,
SUM(monthly_amount) OVER (ORDER BY sale_month) AS cumulative_amount
FROM monthly_sales
)
SELECT
sale_month,
monthly_amount AS '本月销售额',
prev_month_amount AS '上月销售额',
ROUND((monthly_amount - prev_month_amount) / NULLIF(prev_month_amount, 0) * 100, 2) AS '环比增长率%',
monthly_orders AS '本月订单',
prev_month_orders AS '上月订单',
cumulative_amount AS '累计销售额'
FROM monthly_with_prev
ORDER BY sale_month;
-- 9.4 产品库存预警分析
-- 场景：识别库存不足或滞销的产品
SELECT
p.product_id,
p.product_name,
p.metal_type,
p.gem_type,
p.stock_qty,
p.unit_price,
COALESCE(SUM(oi.quantity), 0) AS total_sold,
COALESCE(SUM(oi.quantity * oi.unit_price * oi.discount), 0) AS total_revenue,
CASE
WHEN COALESCE(SUM(oi.quantity), 0) = 0 AND p.stock_qty > 5 THEN '滞销品'
WHEN p.stock_qty <= 3 THEN '库存预警'
WHEN p.stock_qty > 30 THEN '库存充足'
ELSE '正常'
END AS stock_status
FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.product_name, p.metal_type, p.gem_type,
p.stock_qty, p.unit_price
ORDER BY
CASE
WHEN COALESCE(SUM(oi.quantity), 0) = 0 AND p.stock_qty > 5 THEN 1
WHEN p.stock_qty <= 3 THEN 2
ELSE 3
END,
total_revenue DESC;
-- 9.5 门店销售排名（窗口函数实战）
WITH store_sales AS (
SELECT
store_id,
COUNT(*) AS order_count,
SUM(total_amount) AS total_sales,
AVG(total_amount) AS avg_order_value,
COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
WHERE status = '已完成' AND sales_channel = '线下门店'
GROUP BY store_id
)
SELECT
store_id,
order_count,
total_sales,
ROUND(avg_order_value, 2) AS avg_order_value,
unique_customers,
RANK() OVER (ORDER BY total_sales DESC) AS sales_rank,
ROUND(total_sales / SUM(total_sales) OVER () * 100, 2) AS sales_share_pct
FROM store_sales
ORDER BY sales_rank;
-- 9.6 会员等级消费分析
SELECT
c.membership AS '会员等级',
COUNT(DISTINCT c.customer_id) AS '客户数',
COUNT(DISTINCT o.order_id) AS '订单数',
ROUND(SUM(o.total_amount), 2) AS '总销售额',
ROUND(AVG(o.total_amount), 2) AS '平均客单价',
ROUND(SUM(o.total_amount) / COUNT(DISTINCT c.customer_id), 2) AS '人均消费'
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
GROUP BY c.membership
ORDER BY SUM(o.total_amount) DESC;
-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT '05_antipatterns.sql 执行完成' AS result;
哲学管理(学)人生, 文学艺术生活, 自动(计算机学)物理(学)工作, 生物(学)化学逆境, 历史(学)测绘(学)时间, 经济(学)数学金钱(理财), 心理(学)医学情绪, 诗词美容情感, 美学建筑(学)家园, 解构建构(分析)整合学习, 智商情商(IQ、EQ)运筹(学)生存.---Geovin Du(涂聚文）
浙公网安备 33010602011771号