sql: Query Design Patterns using postgreSQL 18

 

-- ============================================================================
-- PostgreSQL 18 珠宝行业查询设计模式实战完整脚本（单文件合并版）
-- 适用版本：PostgreSQL 18+
-- 覆盖模块：
-- 01. 数据库初始化与基础表结构
-- 02. 批量测试数据生成（分类/产品/客户/订单/明细/流水/员工/宽表）
-- 03. 高效JOIN连接查询（CTE提前过滤 / 延迟关联 / EXISTS优化）
-- 04. 高效分页查询（Keyset游标分页 / OFFSET-FETCH / 窗口函数分页）
-- 05. 搜索优化查询（GIN全文搜索 / Trigram模糊搜索 / 覆盖索引 / 前缀匹配）
-- 06. 窗口函数实战（排名 / 累计 / 移动平均 / 分档 / 分位数）
-- 07. 递归CTE（分类路径构建 / 子分类查找 / 组织架构树 / 订单链路）
-- 08. 透视转换 PIVOT/UNPIVOT（条件聚合 / crosstab / UNNEST / LATERAL）
-- 09. 多级聚合 GROUPING SETS/ROLLUP/CUBE
-- 10. 反模式规避（索引失效 / 隐式转换 / OR陷阱 / N+1查询）
-- 11. 综合实战：珠宝业务全景报表（RFM分析 / 销售趋势 / 库存预警 / JSON报表）
-- 执行方式：psql -U postgres -d postgres -f postgresql18_full.sql
--          或分步执行：schema -> data -> queries -> pivot_grouping -> antipatterns
-- ============================================================================

-- ============================================================================
-- 文件：postgresql18_schema.sql
-- 说明：创建数据库、基础表结构、索引、扩展
-- 执行顺序：第 1 步
-- 适用版本：PostgreSQL 18+
-- 执行方式：psql -U postgres -d postgres -f postgresql18_schema.sql
-- ============================================================================

-- 创建扩展（用于 crosstab/PIVOT 功能 + 模糊搜索）
CREATE EXTENSION IF NOT EXISTS tablefunc;
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建枚举类型
CREATE TYPE membership_type AS ENUM ('普通', '银卡', '金卡', '钻石', '黑金');
CREATE TYPE sales_channel_type AS ENUM ('线下门店', '天猫旗舰店', '微信小程序', '抖音直播', '经销商');
CREATE TYPE order_status_type AS ENUM ('待付款', '已付款', '已发货', '已完成', '已取消', '已退货');
CREATE TYPE metal_type AS ENUM ('黄金', '铂金', '18K金', '银', '其他');
CREATE TYPE gem_type AS ENUM ('钻石', '红宝石', '蓝宝石', '翡翠', '珍珠', '无');

-- 删除已存在的连接并重建数据库
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'JewelryDemo' AND pid <> pg_backend_pid();
DROP DATABASE IF EXISTS JewelryDemo;
CREATE DATABASE JewelryDemo WITH ENCODING 'UTF8' LC_COLLATE = 'zh_CN.UTF-8' LC_CTYPE = 'zh_CN.UTF-8' TEMPLATE = template0;

\c JewelryDemo

-- ============================================================================
-- 第1部分：基础表结构创建
-- ============================================================================
SELECT '========== 1. 基础表结构创建 ==========' AS section;

-- 1.1 珠宝分类表（邻接表层次模型）
CREATE TABLE jewelry_category (
    cat_id      INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    parent_id   INT DEFAULT NULL,
    cat_name    VARCHAR(50) NOT NULL,
    cat_level   SMALLINT NOT NULL DEFAULT 1,
    sort_order  INT DEFAULT 0,
    is_active   BOOLEAN DEFAULT TRUE,
    CONSTRAINT fk_category_parent FOREIGN KEY (parent_id) REFERENCES jewelry_category(cat_id) ON DELETE SET NULL
);

COMMENT ON TABLE jewelry_category IS '珠宝分类表';
COMMENT ON COLUMN jewelry_category.cat_id IS '分类ID';
COMMENT ON COLUMN jewelry_category.cat_name IS '分类名称';
COMMENT ON COLUMN jewelry_category.cat_level IS '层级：1=一级 2=二级 3=三级';

CREATE INDEX idx_category_parent ON jewelry_category(parent_id);
CREATE INDEX idx_category_level ON jewelry_category(cat_level);

-- 1.2 产品表
CREATE TABLE products (
    product_id    INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    cat_id        INT NOT NULL,
    product_name  VARCHAR(100) NOT NULL,
    product_code  VARCHAR(30) NOT NULL UNIQUE,
    metal_type    metal_type DEFAULT '黄金'::metal_type,
    gem_type      gem_type DEFAULT '无'::gem_type,
    carat_weight  DECIMAL(8,2) DEFAULT 0.00,
    unit_price    DECIMAL(12,2) NOT NULL,
    stock_qty     INT DEFAULT 0,
    is_active     BOOLEAN DEFAULT TRUE,
    create_time   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_category FOREIGN KEY (cat_id) REFERENCES jewelry_category(cat_id)
);

COMMENT ON TABLE products IS '珠宝产品表';
COMMENT ON COLUMN products.product_code IS '款号';
COMMENT ON COLUMN products.carat_weight IS '克拉重量';
COMMENT ON COLUMN products.unit_price IS '单价';
COMMENT ON COLUMN products.stock_qty IS '库存数量';

CREATE INDEX idx_product_category ON products(cat_id);
CREATE INDEX idx_product_code ON products(product_code);
CREATE INDEX idx_product_metal ON products(metal_type);
CREATE INDEX idx_product_active ON products(is_active);
-- 全文搜索GIN索引（替代MySQL FULLTEXT）
CREATE INDEX idx_product_name_gin ON products USING GIN (to_tsvector('simple', product_name));

-- 1.3 客户表
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    customer_name VARCHAR(50) NOT NULL,
    phone         VARCHAR(20) NOT NULL UNIQUE,
    email         VARCHAR(100),
    membership    membership_type DEFAULT '普通'::membership_type,
    city          VARCHAR(30) DEFAULT '深圳',
    reg_date      DATE DEFAULT CURRENT_DATE,
    total_spent   DECIMAL(14,2) DEFAULT 0.00
);

COMMENT ON TABLE customers IS '客户表';
COMMENT ON COLUMN customers.total_spent IS '累计消费（反范式冗余字段）';

CREATE INDEX idx_customer_phone ON customers(phone);
CREATE INDEX idx_customer_membership ON customers(membership);
CREATE INDEX idx_customer_city ON customers(city);
-- 覆盖索引
CREATE INDEX idx_customer_membership_spent ON customers(membership, total_spent);

-- 1.4 订单主表
CREATE TABLE orders (
    order_id       INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    customer_id    INT NOT NULL,
    order_date     TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sales_channel  sales_channel_type DEFAULT '线下门店'::sales_channel_type,
    store_id       INT DEFAULT NULL,
    total_amount   DECIMAL(14,2) NOT NULL DEFAULT 0.00,
    discount       DECIMAL(4,2) DEFAULT 1.00,
    status         order_status_type DEFAULT '待付款'::order_status_type,
    remark         VARCHAR(200) DEFAULT NULL,
    CONSTRAINT fk_order_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.store_id IS '门店ID（线下渠道）';
COMMENT ON COLUMN orders.discount IS '整单折扣';

CREATE INDEX idx_order_customer ON orders(customer_id);
CREATE INDEX idx_order_date ON orders(order_date);
CREATE INDEX idx_order_channel ON orders(sales_channel);
CREATE INDEX idx_order_status ON orders(status);
CREATE INDEX idx_order_channel_date ON orders(sales_channel, order_date);

-- 1.5 订单明细表
CREATE TABLE order_items (
    item_id      INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    order_id     INT NOT NULL,
    product_id   INT NOT NULL,
    quantity     INT NOT NULL DEFAULT 1,
    unit_price   DECIMAL(12,2) NOT NULL,
    discount     DECIMAL(4,2) DEFAULT 1.00,
    subtotal     DECIMAL(14,2) GENERATED ALWAYS AS (ROUND(quantity * unit_price * discount, 2)) STORED,
    CONSTRAINT fk_item_order FOREIGN KEY (order_id) REFERENCES orders(order_id),
    CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES products(product_id)
);

COMMENT ON TABLE order_items IS '订单明细表';
COMMENT ON COLUMN order_items.unit_price IS '成交单价（快照）';
COMMENT ON COLUMN order_items.discount IS '行折扣';
COMMENT ON COLUMN order_items.subtotal IS '行小计（自动生成）';

CREATE INDEX idx_item_order ON order_items(order_id);
CREATE INDEX idx_item_product ON order_items(product_id);
CREATE INDEX idx_item_order_product ON order_items(order_id, product_id);

-- 1.6 销售流水表（用于增量ETL演示）
CREATE TABLE sales_flow (
    flow_id      BIGINT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    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  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE sales_flow IS '销售流水表';

CREATE INDEX idx_flow_date ON sales_flow(sale_date);
CREATE INDEX idx_flow_product ON sales_flow(product_id);
CREATE INDEX idx_flow_customer ON sales_flow(customer_id);

-- 1.7 门店员工表（递归CTE组织架构演示）
CREATE TABLE store_staff (
    staff_id     INT PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    staff_name   VARCHAR(50) NOT NULL,
    parent_id    INT DEFAULT NULL,
    position     VARCHAR(50) NOT NULL,
    store_id     INT DEFAULT NULL,
    CONSTRAINT fk_staff_parent FOREIGN KEY (parent_id) REFERENCES store_staff(staff_id) ON DELETE SET NULL
);

COMMENT ON TABLE store_staff IS '门店员工表';
COMMENT ON COLUMN store_staff.parent_id IS '上级ID';
COMMENT ON COLUMN store_staff.position IS '职位';

CREATE INDEX idx_staff_parent ON store_staff(parent_id);

-- 1.8 月度销售宽表（UNPIVOT演示用）
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)
);

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'postgresql18_schema.sql 执行完成' AS result;


-- ============================================================================
-- 文件：postgresql18_data.sql
-- 说明：插入分类、产品、客户、订单、明细测试数据
-- 执行顺序：第 2 步（需先执行 postgresql18_schema.sql）
-- 适用版本：PostgreSQL 18+
-- 执行方式：psql -U postgres -d JewelryDemo -f postgresql18_data.sql
-- ============================================================================
\c JewelryDemo

-- ============================================================================
-- 第2部分：批量测试数据生成
-- ============================================================================

-- 2.1 分类数据（三级树形结构）
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', '钻石'::membership_type, '深圳', '2022-03-15', 286000.00),
('李华', '13800138002', 'lihua@email.com', '金卡'::membership_type, '广州', '2022-06-20', 128000.00),
('王芳', '13800138003', 'wangfang@email.com', '黑金'::membership_type, '北京', '2021-01-10', 568000.00),
('赵强', '13800138004', 'zhaoq@email.com', '银卡'::membership_type, '上海', '2023-02-28', 45000.00),
('陈静', '13800138005', 'chenj@email.com', '钻石'::membership_type, '深圳', '2022-08-15', 328000.00),
('刘洋', '13800138006', 'liuyang@email.com', '普通'::membership_type, '成都', '2023-05-01', 12800.00),
('周婷', '13800138007', 'zhout@email.com', '金卡'::membership_type, '杭州', '2022-11-20', 98000.00),
('吴磊', '13800138008', 'wulei@email.com', '普通'::membership_type, '武汉', '2023-08-10', 8900.00),
('郑雪', '13800138009', 'zhengx@email.com', '银卡'::membership_type, '南京', '2023-01-05', 36800.00),
('孙丽', '13800138010', 'sunli@email.com', '钻石'::membership_type, '深圳', '2021-09-18', 428000.00),
('马丽', '13800138011', 'mali@email.com', '金卡'::membership_type, '广州', '2022-04-22', 158000.00),
('朱伟', '13800138012', 'zhuw@email.com', '普通'::membership_type, '重庆', '2023-06-15', 22800.00),
('胡敏', '13800138013', 'hum@email.com', '银卡'::membership_type, '西安', '2022-12-01', 58800.00),
('林涛', '13800138014', 'lint@email.com', '黑金'::membership_type, '深圳', '2021-05-20', 688000.00),
('何燕', '13800138015', 'hey@email.com', '金卡'::membership_type, '长沙', '2022-09-10', 118000.00),
('高飞', '13800138016', 'gaof@email.com', '普通'::membership_type, '郑州', '2023-03-25', 15800.00),
('罗琳', '13800138017', 'luol@email.com', '钻石'::membership_type, '北京', '2021-11-08', 358000.00),
('梁军', '13800138018', 'liangj@email.com', '银卡'::membership_type, '天津', '2023-04-12', 42800.00),
('宋佳', '13800138019', 'songj@email.com', '金卡'::membership_type, '苏州', '2022-07-30', 138000.00),
('韩梅', '13800138020', 'hanm@email.com', '普通'::membership_type, '青岛', '2023-09-05', 6800.00),
('唐勇', '13800138021', 'tangy@email.com', '钻石'::membership_type, '深圳', '2021-08-15', 488000.00),
('冯娟', '13800138022', 'fengj@email.com', '金卡'::membership_type, '东莞', '2022-10-20', 168000.00),
('曹阳', '13800138023', 'caoy@email.com', '普通'::membership_type, '佛山', '2023-07-18', 19800.00),
('许娜', '13800138024', 'xuna@email.com', '银卡'::membership_type, '珠海', '2022-05-08', 68800.00),
('邓超', '13800138025', 'dengc@email.com', '黑金'::membership_type, '广州', '2021-03-22', 728000.00),
('萧红', '13800138026', 'xiaoh@email.com', '金卡'::membership_type, '厦门', '2022-08-28', 148000.00),
('程亮', '13800138027', 'chengl@email.com', '普通'::membership_type, '合肥', '2023-10-01', 12800.00),
('潘婷', '13800138028', 'pant@email.com', '钻石'::membership_type, '深圳', '2021-12-15', 398000.00),
('袁丽', '13800138029', 'yuanl@email.com', '银卡'::membership_type, '南昌', '2023-02-14', 48800.00),
('蒋文', '13800138030', 'jiangw@email.com', '金卡'::membership_type, '宁波', '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', '线下门店'::sales_channel_type, 1, 58888.00, 0.95, '已完成'::order_status_type),
(2, 2, '2024-01-08 14:20:00', '天猫旗舰店'::sales_channel_type, NULL, 28800.00, 1.00, '已完成'::order_status_type),
(3, 3, '2024-01-12 09:15:00', '线下门店'::sales_channel_type, 2, 128000.00, 0.90, '已完成'::order_status_type),
(4, 4, '2024-01-15 16:45:00', '微信小程序'::sales_channel_type, NULL, 8900.00, 1.00, '已完成'::order_status_type),
(5, 5, '2024-01-20 11:00:00', '抖音直播'::sales_channel_type, NULL, 15800.00, 0.88, '已完成'::order_status_type),
(6, 6, '2024-01-25 13:30:00', '线下门店'::sales_channel_type, 1, 22800.00, 1.00, '已完成'::order_status_type),
(7, 7, '2024-02-01 10:00:00', '天猫旗舰店'::sales_channel_type, NULL, 36800.00, 0.92, '已完成'::order_status_type),
(8, 8, '2024-02-05 15:20:00', '微信小程序'::sales_channel_type, NULL, 5800.00, 1.00, '已完成'::order_status_type),
(9, 9, '2024-02-10 09:30:00', '线下门店'::sales_channel_type, 3, 45800.00, 0.95, '已完成'::order_status_type),
(10, 10, '2024-02-14 14:00:00', '天猫旗舰店'::sales_channel_type, NULL, 89999.00, 0.85, '已完成'::order_status_type),
(11, 11, '2024-02-20 11:30:00', '线下门店'::sales_channel_type, 1, 18900.00, 1.00, '已完成'::order_status_type),
(12, 12, '2024-03-01 10:15:00', '抖音直播'::sales_channel_type, NULL, 28800.00, 0.90, '已完成'::order_status_type),
(13, 13, '2024-03-05 16:00:00', '微信小程序'::sales_channel_type, NULL, 6800.00, 1.00, '已完成'::order_status_type),
(14, 14, '2024-03-10 09:00:00', '线下门店'::sales_channel_type, 2, 68000.00, 0.92, '已完成'::order_status_type),
(15, 15, '2024-03-15 14:30:00', '天猫旗舰店'::sales_channel_type, NULL, 15800.00, 1.00, '已完成'::order_status_type),
(16, 16, '2024-03-20 11:00:00', '线下门店'::sales_channel_type, 1, 32800.00, 0.95, '已完成'::order_status_type),
(17, 17, '2024-04-01 10:30:00', '经销商'::sales_channel_type, NULL, 128000.00, 0.80, '已完成'::order_status_type),
(18, 18, '2024-04-05 15:00:00', '天猫旗舰店'::sales_channel_type, NULL, 22800.00, 1.00, '已完成'::order_status_type),
(19, 19, '2024-04-10 09:45:00', '线下门店'::sales_channel_type, 3, 42800.00, 0.90, '已完成'::order_status_type),
(20, 20, '2024-04-15 14:00:00', '微信小程序'::sales_channel_type, NULL, 5800.00, 1.00, '已完成'::order_status_type),
(21, 21, '2024-05-01 10:00:00', '线下门店'::sales_channel_type, 1, 52800.00, 0.95, '已完成'::order_status_type),
(22, 22, '2024-05-05 13:30:00', '天猫旗舰店'::sales_channel_type, NULL, 18600.00, 1.00, '已完成'::order_status_type),
(23, 23, '2024-05-10 16:00:00', '抖音直播'::sales_channel_type, NULL, 38800.00, 0.88, '已完成'::order_status_type),
(24, 24, '2024-05-15 11:00:00', '线下门店'::sales_channel_type, 2, 28800.00, 0.92, '已完成'::order_status_type),
(25, 25, '2024-06-01 09:30:00', '经销商'::sales_channel_type, NULL, 98000.00, 0.78, '已完成'::order_status_type),
(26, 26, '2024-06-05 14:00:00', '天猫旗舰店'::sales_channel_type, NULL, 12800.00, 1.00, '已完成'::order_status_type),
(27, 27, '2024-06-10 10:30:00', '线下门店'::sales_channel_type, 1, 35800.00, 0.95, '已完成'::order_status_type),
(28, 28, '2024-06-15 15:30:00', '微信小程序'::sales_channel_type, NULL, 8900.00, 1.00, '已完成'::order_status_type),
(29, 29, '2024-07-01 09:00:00', '线下门店'::sales_channel_type, 3, 48800.00, 0.90, '已完成'::order_status_type),
(30, 30, '2024-07-05 14:30:00', '天猫旗舰店'::sales_channel_type, NULL, 15800.00, 1.00, '已完成'::order_status_type);

-- 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.6 门店员工数据（递归CTE演示）
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);

-- 2.7 月度销售宽表数据（UNPIVOT演示）
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);

-- 2.8 完成提示
SELECT 'postgresql18_data.sql 执行完成' AS result;


-- ============================================================================
-- 文件：postgresql18_queries.sql
-- 说明：PostgreSQL 18 珠宝行业查询设计模式实战
-- 覆盖模块：高效JOIN / 分页优化 / 搜索优化 / 窗口函数 / 递归CTE
-- 执行顺序：第 3 步（需先执行 postgresql18_schema.sql 和 postgresql18_data.sql）
-- 适用版本：PostgreSQL 18+
-- 执行方式：psql -U postgres -d JewelryDemo -f postgresql18_queries.sql
-- ============================================================================
\c JewelryDemo

-- ============================================================================
-- 模块一：高效JOIN连接查询
-- ============================================================================
SELECT '========== 模块一：高效JOIN连接查询 ==========' AS section;

-- 1.1 小表驱动大表 + Hash Join（PostgreSQL 优化器自动选择）
-- 场景：查询金卡及以上客户的订单明细
-- 优化点：先过滤小结果集再关联，避免大表全量扫描
EXPLAIN (ANALYZE, FORMAT TEXT)
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）优化深分页关联查询
-- 场景：查询已完成订单的客户与商品详情（深分页优化）
-- 传统写法会回表大量次，优化后仅回表所需行数
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
    OFFSET 50000 ROWS FETCH NEXT 10 ROWS ONLY
) 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天内的复购订单
-- PostgreSQL中日期相减直接返回天数
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,
    (o2.order_date::date - o1.order_date::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 <= o1.order_date + INTERVAL '30 days'
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)复杂度，无论翻到第几页都毫秒级响应
SELECT product_id, product_name, unit_price, stock_qty
FROM products
WHERE product_id > 5
  AND is_active = TRUE
ORDER BY product_id ASC
LIMIT 10;

-- 2.2 标准 OFFSET/FETCH 分页：适用于后台管理系统跳页
-- 场景：后台订单列表跳转到第400页（每页20条）
-- PostgreSQL 18 推荐使用 OFFSET ... FETCH NEXT ... ROWS ONLY 语法
SELECT o.order_id, o.order_date, o.total_amount, o.status,
       c.customer_name, c.phone
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = '已完成'
ORDER BY o.order_date DESC
OFFSET 7980 ROWS FETCH NEXT 20 ROWS ONLY;

-- 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;

-- 2.4 Keyset Pagination（基于排序键的游标分页）
-- 场景：按订单金额排序的无限滚动分页
-- 比OFFSET性能更好，因为可以利用索引避免跳过已读行
SELECT order_id, order_date, total_amount, status
FROM orders
WHERE status = '已完成'
  AND (order_date, total_amount) < ('2024-05-15'::timestamp, 28800.00)
ORDER BY order_date DESC, total_amount DESC
LIMIT 20;

-- ============================================================================
-- 模块三：搜索优化查询（Search-Optimized Queries）
-- ============================================================================
SELECT '========== 模块三：搜索优化查询 ==========' AS section;

-- 3.1 全文搜索：使用GIN索引 + to_tsvector/to_tsquery
-- 场景：客户搜索含"钻石"或"金"的商品
-- PostgreSQL原生全文搜索，支持中文simple分词
SELECT product_id, product_name, unit_price, cat_id,
       ts_rank(to_tsvector('simple', product_name),
               to_tsquery('simple', '钻石 & 金')) AS relevance
FROM products
WHERE to_tsvector('simple', product_name) @@ to_tsquery('simple', '钻石 & 金')
ORDER BY relevance DESC;

-- 3.2 覆盖索引优化：仅查询索引列避免回表
-- 场景：统计各会员等级的客户数量与平均消费
-- 查询直接从idx_customer_membership_spent索引树获取数据，无需回表
SELECT membership,
       COUNT(*) AS cust_count,
       ROUND(AVG(total_spent), 2) AS avg_spent
FROM customers
GROUP BY membership;

-- 3.3 Trigram模糊搜索（pg_trgm扩展）
-- 场景：商品名称模糊查询，比LIKE '%keyword%'性能高得多
-- 先创建GIN trigram索引
CREATE INDEX IF NOT EXISTS idx_product_name_trgm ON products USING GIN (product_name gin_trgm_ops);

-- 使用 % 操作符（底层走GIN索引）
SELECT product_id, product_name, unit_price
FROM products
WHERE product_name % '钻石头饰'  -- % 表示similarity > 0.3
ORDER BY similarity(product_name, '钻石头饰') DESC
LIMIT 10;

-- 3.4 动态参数化搜索（多条件可选）
-- 场景：后台商品搜索，支持按品类/金属类型/价格范围多条件组合
-- 使用COALESCE/ANY实现可选条件（PostgreSQL写法）
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 COALESCE(NULL, p.cat_id) = p.cat_id  -- NULL表示不筛选
  AND COALESCE(NULL::metal_type, p.metal_type) = p.metal_type
  AND p.unit_price >= 0
  AND p.unit_price <= 100000
ORDER BY p.unit_price DESC
LIMIT 20;

-- 3.5 前缀匹配优化（B-tree索引天然支持前缀匹配）
-- 场景：按款号前缀搜索产品
SELECT product_id, product_name, product_code, unit_price
FROM products
WHERE product_code LIKE 'R-DIA-%'
ORDER BY unit_price DESC;

-- ============================================================================
-- 模块四：窗口函数（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
        order_date::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 order_date::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))
          / NULLIF(LAG(daily_amount, 1) OVER (ORDER BY sale_date), 0) * 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
        order_date::date AS sale_date,
        SUM(total_amount) AS daily_amount,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = '已完成'
    GROUP BY order_date::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;

-- 4.6 PERCENTILE_CONT/PERCENTILE_DISC：分位数分析（PostgreSQL亮点）
-- 场景：计算各渠道订单金额的中位数和90分位数
SELECT
    sales_channel,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 2) AS avg_amount,
    ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_amount), 2) AS median_amount,
    ROUND(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_amount), 2) AS p90_amount,
    ROUND(PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY total_amount), 2) AS median_disc,
    MIN(total_amount) AS min_amount,
    MAX(total_amount) AS max_amount
FROM orders
WHERE status = '已完成'
GROUP BY sales_channel
ORDER BY sales_channel;

-- ============================================================================
-- 模块五：递归CTE（Recursive CTEs）
-- ============================================================================
SELECT '========== 模块五：递归CTE实战 ==========' AS section;

-- 5.1 珠宝分类树展开：查询"珠宝首饰"下的所有子分类（任意层级）
-- PostgreSQL递归CTE语法与标准SQL一致
WITH RECURSIVE category_tree AS (
    -- 锚点：根节点
    SELECT cat_id, parent_id, cat_name, cat_level,
           cat_name::TEXT AS path
    FROM jewelry_category
    WHERE cat_id = 1

    UNION ALL

    -- 递归：查找子节点
    SELECT c.cat_id, c.parent_id, c.cat_name, c.cat_level,
           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,
           cat_name::TEXT 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,
           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实战：员工层级树（珠宝连锁店组织架构）
-- 递归查询某门店的组织架构树
WITH RECURSIVE org_tree AS (
    -- 锚点：根节点（总经理）
    SELECT staff_id, staff_name, parent_id, position, store_id,
           1 AS level,
           staff_name::TEXT 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,
           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;

-- 5.5 递归CTE实战：订单链路追溯（订单->明细->产品->分类）
-- 场景：递归展开订单的完整商品链路
WITH RECURSIVE order_chain AS (
    SELECT
        o.order_id,
        oi.item_id,
        oi.product_id,
        p.product_name,
        p.cat_id,
        jc.cat_name,
        jc.cat_level,
        1 AS chain_level
    FROM orders o
    INNER JOIN order_items oi ON o.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
    WHERE o.order_id = 1

    UNION ALL

    SELECT
        oc.order_id,
        oi.item_id,
        oi.product_id,
        p.product_name,
        p.cat_id,
        jc.cat_name,
        jc.cat_level,
        oc.chain_level + 1
    FROM order_chain oc
    LEFT JOIN order_items oi ON oc.order_id = oi.order_id AND oi.product_id != oc.product_id
    LEFT JOIN products p ON oi.product_id = p.product_id
    LEFT JOIN jewelry_category jc ON p.cat_id = jc.cat_id
    WHERE oc.chain_level < 3
)
SELECT * FROM order_chain ORDER BY order_id, chain_level;

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'postgresql18_queries.sql 执行完成' AS result;


-- ============================================================================
-- 文件：postgresql18_pivot_grouping.sql
-- 说明：PostgreSQL 18 珠宝行业 PIVOT/UNPIVOT 透视转换 + GROUPING SETS多级聚合实战
-- 执行顺序：第 4 步（需先执行 postgresql18_schema.sql 和 postgresql18_data.sql）
-- 适用版本：PostgreSQL 18+
-- 执行方式：psql -U postgres -d JewelryDemo -f postgresql18_pivot_grouping.sql
-- ============================================================================
\c JewelryDemo

-- ============================================================================
-- 模块六：透视转换 PIVOT / UNPIVOT 实战
-- ============================================================================
SELECT '========== 模块六：PIVOT/UNPIVOT透视转换实战 ==========' AS section;

-- 6.1 行转列（PIVOT）：各品类按月销售额透视
-- 场景：管理层需要看各品类 2024年 各月的月度销售对比
-- PostgreSQL使用条件聚合实现（与MySQL写法基本一致，但日期函数不同）
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,
        TO_CHAR(o.order_date, 'YYYY-MM') 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, TO_CHAR(o.order_date, 'YYYY-MM')
) AS monthly_sales
GROUP BY cat_name
ORDER BY "上半年合计" DESC;

-- 6.2 行转列（PIVOT）：各销售渠道季度业绩对比
-- 场景：对比各渠道 2024年 Q1-Q4 的业绩
-- EXTRACT(QUARTER FROM date) 获取季度
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,
        'Q' || EXTRACT(QUARTER FROM order_date) AS quarter,
        SUM(total_amount) AS q_amount
    FROM orders
    WHERE status = '已完成'
      AND EXTRACT(YEAR FROM order_date) = 2024
    GROUP BY sales_channel, EXTRACT(QUARTER FROM order_date)
) AS quarterly_sales
GROUP BY sales_channel
ORDER BY "年度总计" DESC;

-- 6.3 使用 tablefunc 扩展的 crosstab 函数（PostgreSQL原生PIVOT替代方案）
-- 场景：使用crosstab函数实现更优雅的行列转换
-- 先确保已创建扩展：CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
    'SELECT jc.cat_name,
            TO_CHAR(o.order_date, ''YYYY-MM'') AS sale_month,
            SUM(oi.quantity * oi.unit_price * oi.discount) AS month_amount
     FROM order_items oi
     JOIN orders o ON oi.order_id = o.order_id
     JOIN products p ON oi.product_id = p.product_id
     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, TO_CHAR(o.order_date, ''YYYY-MM'')
     ORDER BY jc.cat_name, sale_month',
    'SELECT DISTINCT TO_CHAR(order_date, ''YYYY-MM'')
     FROM orders
     WHERE status = ''已完成''
       AND order_date >= ''2024-01-01'' AND order_date < ''2024-07-01''
     ORDER BY 1'
) AS ct(cat_name text, "1月" numeric, "2月" numeric, "3月" numeric, "4月" numeric, "5月" numeric, "6月" numeric);

-- 6.4 列转行（UNPIVOT）：宽表还原为长表
-- 场景：现有月度销售宽表，需还原为长表以便做趋势分析或导入BI工具
-- PostgreSQL使用UNNEST + generate_series实现UNPIVOT
SELECT cat_name, month, amount
FROM monthly_sales_wide,
     UNNEST(
         ARRAY['1月','2月','3月','4月','5月','6月'],
         ARRAY[m_01,m_02,m_03,m_04,m_05,m_06]
     ) AS t(month, amount)
WHERE amount IS NOT NULL
ORDER BY cat_name, month;

-- 6.5 使用 LATERAL 实现更灵活的UNPIVOT
-- 场景：列名和列值动态映射
SELECT cat_name, month_name, month_value
FROM monthly_sales_wide,
     LATERAL (
         VALUES
             ('1月', m_01), ('2月', m_02), ('3月', m_03),
             ('4月', m_04), ('5月', m_05), ('6月', m_06)
     ) AS v(month_name, month_value)
WHERE month_value IS NOT NULL
ORDER BY cat_name, month_name;

-- 6.6 PostgreSQL 18 原生 PIVOT 语法（如果版本支持）
-- PostgreSQL 18 仍未引入原生PIVOT语法，但可以通过以下方式模拟：
-- 使用CASE聚合是最通用的做法，兼容所有版本

-- ============================================================================
-- 模块七：多级聚合 GROUPING SETS / ROLLUP / CUBE 实战
-- ============================================================================
SELECT '========== 模块七：GROUPING SETS多级聚合实战 ==========' AS section;

-- 7.1 基础GROUPING SETS：多维度销售汇总
-- 场景：一份报表同时需要"按品类汇总"、"按渠道汇总"、"全公司总计"三种粒度
-- PostgreSQL 9.5+ 支持 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（含小计），同时额外加上"按城市"的独立汇总
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写法（扫描多次表）
EXPLAIN (ANALYZE, FORMAT TEXT)
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 (ANALYZE, FORMAT TEXT)
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 'postgresql18_pivot_grouping.sql 执行完成' AS result;


-- ============================================================================
-- 文件：postgresql18_antipatterns.sql
-- 说明：PostgreSQL 18 珠宝行业 反模式规避 + 综合实战报表
-- 执行顺序：第 5 步（需先执行 postgresql18_schema.sql 和 postgresql18_data.sql）
-- 适用版本：PostgreSQL 18+
-- 执行方式：psql -U postgres -d JewelryDemo -f postgresql18_antipatterns.sql
-- ============================================================================
\c JewelryDemo

-- ============================================================================
-- 模块八：反模式规避（Anti-Patterns）
-- ============================================================================
SELECT '========== 模块八：反模式规避 ==========' AS section;

-- 8.1 反模式：在WHERE中对索引列使用函数（索引失效）
-- 错误写法：EXTRACT(YEAR FROM order_date) = 2024 导致全表扫描
-- EXPLAIN (ANALYZE) SELECT * FROM orders WHERE EXTRACT(YEAR FROM order_date) = 2024;

-- 正确写法：使用范围查询，可利用idx_order_date索引
EXPLAIN (ANALYZE, FORMAT TEXT)
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 (ANALYZE) SELECT * FROM orders WHERE status = '已完成';

-- 正确写法：只查需要的列，命中覆盖索引
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT order_id, order_date, total_amount, status
FROM orders
WHERE status = '已完成';

-- 8.3 反模式：隐式类型转换导致索引失效
-- 错误写法：phone是VARCHAR，用数字比较会触发隐式转换
-- EXPLAIN (ANALYZE) SELECT * FROM customers WHERE phone = 13800138001;

-- 正确写法：字符串类型用字符串比较
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT customer_id, customer_name, phone
FROM customers
WHERE phone = '13800138001';

-- 8.4 反模式：OR条件导致索引失效
-- 错误写法：OR连接不同列，优化器可能放弃索引
-- EXPLAIN (ANALYZE) SELECT * FROM orders WHERE status = '已完成' OR total_amount > 50000;

-- 正确写法：拆分为UNION ALL，各分支可独立用索引
EXPLAIN (ANALYZE, FORMAT TEXT)
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;
-- 正确写法：使用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 EXTRACT(YEAR FROM o.order_date) = EXTRACT(YEAR FROM 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 < 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;

-- 8.8 反模式：不必要的子查询嵌套
-- 场景：可以用JOIN替代的相关子查询
-- 错误写法
-- SELECT o.order_id, o.total_amount,
--        (SELECT AVG(total_amount) FROM orders) AS avg_amount
-- FROM orders o;

-- 正确写法：使用窗口函数
SELECT order_id, total_amount,
       ROUND(AVG(total_amount) OVER (), 2) AS avg_amount
FROM orders;

-- ============================================================================
-- 模块九：综合实战 - 珠宝业务全景报表
-- ============================================================================
SELECT '========== 模块九：综合实战-珠宝业务全景报表 ==========' AS section;

-- 9.1 多维度透视 + 多级聚合：2024年各品类x渠道x季度的销售矩阵
WITH quarterly_category_sales AS (
    SELECT
        jc.cat_name,
        o.sales_channel,
        'Q' || EXTRACT(QUARTER FROM 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, EXTRACT(QUARTER FROM 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,
        (CURRENT_DATE - MAX(o.order_date)::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)::TEXT,
           NTILE(5) OVER (ORDER BY frequency ASC)::TEXT,
           NTILE(5) OVER (ORDER BY monetary ASC)::TEXT) 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
        TO_CHAR(order_date, 'YYYY-MM') 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 TO_CHAR(order_date, 'YYYY-MM')
),
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) / NULLIF(COUNT(DISTINCT c.customer_id), 0), 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;

-- 9.7 使用STRING_AGG替代GROUP_CONCAT：客户购买商品汇总
-- 场景：每个客户购买的所有商品名称列表
SELECT
    c.customer_id,
    c.customer_name,
    STRING_AGG(DISTINCT p.product_name, ', ' ORDER BY p.product_name) AS purchased_products
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id
LIMIT 20;

-- 9.8 使用JSON聚合生成JSON报表（PostgreSQL亮点）
-- 场景：生成客户购买记录的JSON数组
SELECT
    c.customer_id,
    c.customer_name,
    c.membership,
    json_agg(
        json_build_object(
            'order_id', o.order_id,
            'order_date', o.order_date,
            'total_amount', o.total_amount,
            'products', (
                SELECT json_agg(
                    json_build_object(
                        'product_name', p.product_name,
                        'quantity', oi.quantity,
                        'subtotal', oi.subtotal
                    )
                )
                FROM order_items oi
                JOIN products p ON oi.product_id = p.product_id
                WHERE oi.order_id = o.order_id
            )
        )
    ) AS orders_json
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.membership
LIMIT 10;

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'postgresql18_antipatterns.sql 执行完成' AS result;



-- ============================================================================
-- 全部脚本执行完成
-- ============================================================================
SELECT 'postgresql18_full.sql 全部执行完成！' AS result;