sql: Query Design Patterns using Oracle 21C

 

-- ============================================================================
-- 文件: oracle21_full.sql
-- 说明: Oracle 21c 珠宝行业查询设计模式实战 - 完整版 (合并所有模块)
-- 适用版本: Oracle 21c (兼容 12c+ 所有特性)
-- 执行方式: sqlplus jewelry/jewelry @oracle21_full.sql
--          或在 SQL Developer / DBeaver / Navicat 中全选执行
-- ============================================================================
-- 覆盖模块:
-- 01. 数据库初始化与基础表结构
-- 02. 批量测试数据生成 (分类/产品/客户/订单/明细/流水)
-- 03. 高效JOIN连接查询 (CTE提前过滤 / 派生表优化)
-- 04. 高效分页查询 (Keyset游标分页 vs OFFSET-FETCH)
-- 05. 搜索优化查询 (前缀匹配 / 动态参数化搜索 / 正则搜索)
-- 06. 窗口函数实战 (排名 / 累计 / 移动平均 / 分档 / 分位数)
-- 07. 递归CTE (分类路径构建 / 子分类查找 / 组织架构树)
-- 08. 透视转换 PIVOT/UNPIVOT (原生语法 + 条件聚合)
-- 09. 多级聚合 GROUPING SETS/ROLLUP/CUBE
-- 10. 反模式规避 (索引失效 / 隐式转换 / OR陷阱)
-- 11. 综合实战: 珠宝业务全景报表 (RFM/趋势/库存/MATCH_RECOGNIZE)
-- ============================================================================
-- ============================================================================
-- 文件: oracle21_schema.sql
-- 说明: Oracle 21c 珠宝行业 - 创建用户、表空间、表结构、序列、索引、注释
-- 执行顺序: 第 1 步
-- 适用版本: Oracle 21c (兼容 12c+ 所有特性)
-- ============================================================================

-- 1.0 创建表空间和用户 (以 SYSDBA 身份执行)
-- 如果已存在表空间和用户，可跳过此段
/*
CREATE TABLESPACE jewelry_ts
    DATAFILE '/u01/app/oracle/oradata/ORCL/jewelry_ts.dbf'
    SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER jewelry IDENTIFIED BY jewelry
    DEFAULT TABLESPACE jewelry_ts
    TEMPORARY TABLESPACE temp
    QUOTA UNLIMITED ON jewelry_ts;

GRANT CONNECT, RESOURCE, DBA TO jewelry;
*/

-- 切换用户
-- CONNECT jewelry/jewelry;

-- 如果已存在则删除对象 (按依赖顺序)
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE order_items CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE sales_flow CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE store_staff CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE orders CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE customers CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE products CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE jewelry_category CASCADE CONSTRAINTS';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE monthly_sales_wide';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_category';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_product';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_customer';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_order';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_order_item';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/
BEGIN
    EXECUTE IMMEDIATE 'DROP SEQUENCE seq_sales_flow';
EXCEPTION WHEN OTHERS THEN NULL;
END;
/

-- ============================================================================
-- 第1部分: 基础表结构创建 (Oracle 21c)
-- ============================================================================

-- 1.1 珠宝分类表 (邻接表层次模型)
CREATE TABLE jewelry_category (
    cat_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    parent_id   NUMBER,
    cat_name    VARCHAR2(50) NOT NULL,
    cat_level   NUMBER(1) NOT NULL DEFAULT 1,
    sort_order  NUMBER DEFAULT 0,
    is_active   NUMBER(1) DEFAULT 1,
    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.parent_id IS '父分类ID';
COMMENT ON COLUMN jewelry_category.cat_name IS '分类名称';
COMMENT ON COLUMN jewelry_category.cat_level IS '层级: 1=一级 2=二级 3=三级';
COMMENT ON COLUMN jewelry_category.sort_order IS '排序顺序';
COMMENT ON COLUMN jewelry_category.is_active IS '是否启用: 1=启用 0=禁用';

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   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    cat_id       NUMBER NOT NULL,
    product_name VARCHAR2(100) NOT NULL,
    product_code VARCHAR2(30) NOT NULL,
    metal_type   VARCHAR2(10) DEFAULT '黄金' CONSTRAINT chk_metal CHECK (metal_type IN ('黄金','铂金','18K金','银','其他')),
    gem_type     VARCHAR2(10) DEFAULT '无' CONSTRAINT chk_gem CHECK (gem_type IN ('钻石','红宝石','蓝宝石','翡翠','珍珠','无')),
    carat_weight NUMBER(8,2) DEFAULT 0.00,
    unit_price   NUMBER(12,2) NOT NULL,
    stock_qty    NUMBER DEFAULT 0,
    is_active    NUMBER(1) DEFAULT 1,
    create_time  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_product_category
        FOREIGN KEY (cat_id) REFERENCES jewelry_category(cat_id),
    CONSTRAINT uk_product_code UNIQUE (product_code)
);

COMMENT ON TABLE products IS '珠宝产品表';
COMMENT ON COLUMN products.cat_id IS '分类ID';
COMMENT ON COLUMN products.product_name IS '产品名称';
COMMENT ON COLUMN products.product_code IS '款号';
COMMENT ON COLUMN products.metal_type IS '金属类型';
COMMENT ON COLUMN products.gem_type IS '宝石类型';
COMMENT ON COLUMN products.carat_weight IS '克拉重量';
COMMENT ON COLUMN products.unit_price IS '单价';
COMMENT ON COLUMN products.stock_qty IS '库存数量';
COMMENT ON COLUMN products.is_active IS '是否在售: 1=在售 0=停售';
COMMENT ON COLUMN products.create_time 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);
CREATE INDEX idx_product_price ON products(unit_price);

-- 1.3 客户表
CREATE TABLE customers (
    customer_id  NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(50) NOT NULL,
    phone        VARCHAR2(20) NOT NULL,
    email        VARCHAR2(100),
    membership   VARCHAR2(10) DEFAULT '普通' CONSTRAINT chk_membership CHECK (membership IN ('普通','银卡','金卡','钻石','黑金')),
    city         VARCHAR2(30) DEFAULT '深圳',
    reg_date     DATE DEFAULT TRUNC(SYSDATE),
    total_spent  NUMBER(14,2) DEFAULT 0.00,
    CONSTRAINT uk_phone UNIQUE (phone)
);

COMMENT ON TABLE customers IS '客户表';
COMMENT ON COLUMN customers.customer_id IS '客户ID';
COMMENT ON COLUMN customers.customer_name IS '客户姓名';
COMMENT ON COLUMN customers.phone IS '手机号';
COMMENT ON COLUMN customers.email IS '邮箱';
COMMENT ON COLUMN customers.membership IS '会员等级';
COMMENT ON COLUMN customers.city IS '城市';
COMMENT ON COLUMN customers.reg_date 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_city_name ON customers(city, customer_name);

-- 1.4 订单主表
CREATE TABLE orders (
    order_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id   NUMBER NOT NULL,
    order_date    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sales_channel VARCHAR2(20) DEFAULT '线下门店' CONSTRAINT chk_channel CHECK (sales_channel IN ('线下门店','天猫旗舰店','微信小程序','抖音直播','经销商')),
    store_id      NUMBER,
    total_amount  NUMBER(14,2) NOT NULL DEFAULT 0.00,
    discount      NUMBER(4,2) DEFAULT 1.00,
    status        VARCHAR2(20) DEFAULT '待付款' CONSTRAINT chk_status CHECK (status IN ('待付款','已付款','已发货','已完成','已取消','已退货')),
    remark        VARCHAR2(200),
    CONSTRAINT fk_order_customer
        FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

COMMENT ON TABLE orders IS '订单主表';
COMMENT ON COLUMN orders.order_id IS '订单ID';
COMMENT ON COLUMN orders.customer_id IS '客户ID';
COMMENT ON COLUMN orders.order_date IS '下单时间';
COMMENT ON COLUMN orders.sales_channel IS '销售渠道';
COMMENT ON COLUMN orders.store_id IS '门店ID(线下渠道)';
COMMENT ON COLUMN orders.total_amount IS '订单总金额';
COMMENT ON COLUMN orders.discount IS '整单折扣';
COMMENT ON COLUMN orders.status IS '订单状态';
COMMENT ON COLUMN orders.remark 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);
CREATE INDEX idx_order_status_date ON orders(status, order_date);

-- 1.5 订单明细表
CREATE TABLE order_items (
    item_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id    NUMBER NOT NULL,
    product_id  NUMBER NOT NULL,
    quantity    NUMBER NOT NULL DEFAULT 1,
    unit_price  NUMBER(12,2) NOT NULL,
    discount    NUMBER(4,2) DEFAULT 1.00,
    subtotal    NUMBER(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.item_id IS '明细ID';
COMMENT ON COLUMN order_items.order_id IS '订单ID';
COMMENT ON COLUMN order_items.product_id IS '产品ID';
COMMENT ON COLUMN order_items.quantity 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_order_item_order ON order_items(order_id);
CREATE INDEX idx_order_item_product ON order_items(product_id);
CREATE INDEX idx_order_item_order_product ON order_items(order_id, product_id);

-- 1.6 销售流水表 (用于增量ETL演示)
CREATE TABLE sales_flow (
    flow_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id     NUMBER NOT NULL,
    item_id      NUMBER NOT NULL,
    product_id   NUMBER NOT NULL,
    customer_id  NUMBER NOT NULL,
    sale_date    DATE NOT NULL,
    sale_amount  NUMBER(14,2) NOT NULL,
    quantity     NUMBER NOT NULL,
    channel      VARCHAR2(20) NOT NULL,
    create_time  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

COMMENT ON TABLE sales_flow IS '销售流水表';
COMMENT ON COLUMN sales_flow.flow_id IS '流水ID';
COMMENT ON COLUMN sales_flow.sale_date IS '销售日期';
COMMENT ON COLUMN sales_flow.sale_amount IS '销售金额';

CREATE INDEX idx_sales_flow_date ON sales_flow(sale_date);
CREATE INDEX idx_sales_flow_product ON sales_flow(product_id);
CREATE INDEX idx_sales_flow_customer ON sales_flow(customer_id);

-- 1.7 门店员工表 (用于递归CTE组织架构演示)
CREATE TABLE store_staff (
    staff_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    staff_name VARCHAR2(50) NOT NULL,
    parent_id  NUMBER,
    position   VARCHAR2(50) NOT NULL,
    store_id   NUMBER,
    CONSTRAINT fk_staff_parent FOREIGN KEY (parent_id) REFERENCES store_staff(staff_id)
);

COMMENT ON TABLE store_staff IS '门店员工表';
COMMENT ON COLUMN store_staff.staff_id IS '员工ID';
COMMENT ON COLUMN store_staff.staff_name IS '员工姓名';
COMMENT ON COLUMN store_staff.parent_id IS '上级员工ID';
COMMENT ON COLUMN store_staff.position IS '职位';
COMMENT ON COLUMN store_staff.store_id IS '所属门店ID';

CREATE INDEX idx_staff_parent ON store_staff(parent_id);

-- 1.8 全文搜索索引 (Oracle Text CONTEXT)
-- 需要先创建文本索引权限: GRANT CTXAPP TO jewelry;
-- CREATE INDEX idx_ft_product_name ON products(product_name)
-- INDEXTYPE IS CTXSYS.CONTEXT;

-- 1.9 覆盖索引 (用于搜索优化演示)
CREATE INDEX idx_customer_membership_spent ON customers(membership, total_spent);

-- 1.10 产品描述列 (用于前缀索引演示)
ALTER TABLE products ADD product_desc VARCHAR2(500) DEFAULT '';
-- 函数索引实现前缀索引
CREATE INDEX idx_product_desc_prefix ON products(LOWER(SUBSTR(product_desc, 1, 30)));

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'oracle21_schema.sql 执行完成' AS result FROM DUAL;
\n-- ============================================================================
-- 文件: oracle21_data.sql
-- 说明: Oracle 21c 珠宝行业 - 插入分类、产品、客户、订单、明细测试数据
-- 执行顺序: 第 2 步 (需先执行 oracle21_schema.sql)
-- 适用版本: Oracle 21c
-- ============================================================================

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

-- 2.1 分类数据 (三级树形结构)
INSERT ALL
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (1, NULL, '珠宝首饰', 1, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (2, NULL, '投资金条', 1, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (3, NULL, '定制服务', 1, 3)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (10, 1, '戒指', 2, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (11, 1, '项链', 2, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (12, 1, '手镯', 2, 3)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (13, 1, '耳环', 2, 4)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (14, 1, '吊坠', 2, 5)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (15, 1, '胸针', 2, 6)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (100, 10, '钻石戒指', 3, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (101, 10, '黄金戒指', 3, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (102, 10, '翡翠戒指', 3, 3)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (110, 11, '钻石项链', 3, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (111, 11, '珍珠项链', 3, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (112, 11, '黄金项链', 3, 3)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (120, 12, '黄金手镯', 3, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (121, 12, '铂金手镯', 3, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (130, 13, '钻石耳环', 3, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (131, 13, '珍珠耳环', 3, 2)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (140, 14, '翡翠吊坠', 3, 1)
INTO jewelry_category (cat_id, parent_id, cat_name, cat_level, sort_order) VALUES (141, 14, '钻石吊坠', 3, 2)
SELECT 1 FROM DUAL;

-- 2.2 产品数据 (50条)
INSERT ALL
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)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '星光钻戒', 'R-DIA-002', '18K金', '钻石', 0.50, 28800.00, 12)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '经典六爪钻戒', 'R-DIA-003', '铂金', '钻石', 0.80, 42800.00, 8)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (101, '福字黄金戒指', 'R-GOL-001', '黄金', '无', 0.00, 8900.00, 30)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (101, '古法金戒指', 'R-GOL-002', '黄金', '无', 0.00, 12600.00, 20)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (102, '翡翠玉戒指', 'R-JAD-001', '黄金', '翡翠', 0.00, 15800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (110, '海洋之泪项链', 'N-DIA-001', '铂金', '钻石', 2.00, 128000.00, 3)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (110, '锁骨链', 'N-DIA-002', '18K金', '钻石', 0.30, 15800.00, 25)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (111, '南洋珍珠项链', 'N-PEA-001', '黄金', '珍珠', 0.00, 36800.00, 8)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (111, '淡水珍珠项链', 'N-PEA-002', '银', '珍珠', 0.00, 2800.00, 50)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (112, '龙凤黄金项链', 'N-GOL-001', '黄金', '无', 0.00, 22800.00, 15)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (120, '古法金手镯', 'B-GOL-001', '黄金', '无', 0.00, 35800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (120, '传承金手镯', 'B-GOL-002', '黄金', '无', 0.00, 28600.00, 18)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (121, '铂金光面手镯', 'B-PLA-001', '铂金', '无', 0.00, 18900.00, 12)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (130, '钻石耳钉', 'E-DIA-001', '铂金', '钻石', 0.50, 22800.00, 20)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (130, '钻石耳环', 'E-DIA-002', '18K金', '钻石', 1.00, 45800.00, 6)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (131, '珍珠耳坠', 'E-PEA-001', '黄金', '珍珠', 0.00, 5800.00, 35)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (140, '帝王绿翡翠吊坠', 'P-JAD-001', '黄金', '翡翠', 0.00, 68000.00, 4)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (140, '冰种翡翠吊坠', 'P-JAD-002', '18K金', '翡翠', 0.00, 28800.00, 12)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (141, '钻石吊坠', 'P-DIA-001', '铂金', '钻石', 0.60, 32800.00, 15)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '群镶钻戒', 'R-DIA-004', '18K金', '钻石', 0.40, 19800.00, 18)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (101, '转运珠戒指', 'R-GOL-003', '黄金', '无', 0.00, 6800.00, 40)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (110, '心形钻石项链', 'N-DIA-003', '铂金', '钻石', 1.50, 88000.00, 5)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (112, '足金项链', 'N-GOL-002', '黄金', '无', 0.00, 18600.00, 22)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (120, '花丝金手镯', 'B-GOL-003', '黄金', '无', 0.00, 42000.00, 6)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (130, '群镶钻石耳环', 'E-DIA-003', '铂金', '钻石', 0.80, 36800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (141, '水滴钻石吊坠', 'P-DIA-002', '18K金', '钻石', 0.40, 18800.00, 20)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '玫瑰金钻戒', 'R-DIA-005', '18K金', '钻石', 0.60, 25800.00, 14)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (111, 'Akoya珍珠项链', 'N-PEA-003', '铂金', '珍珠', 0.00, 28800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (140, '飘花翡翠吊坠', 'P-JAD-003', '黄金', '翡翠', 0.00, 38800.00, 8)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (101, '花戒', 'R-GOL-004', '黄金', '无', 0.00, 5600.00, 50)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (121, '钻石铂金手镯', 'B-PLA-002', '铂金', '钻石', 0.30, 52800.00, 4)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (131, '金珠珍珠耳环', 'E-PEA-002', '黄金', '珍珠', 0.00, 3800.00, 45)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (112, '十字金刚杵项链', 'N-GOL-003', '黄金', '无', 0.00, 15800.00, 20)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (141, '群镶钻石吊坠', 'P-DIA-003', '铂金', '钻石', 1.20, 68000.00, 3)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '排钻戒', 'R-DIA-006', '铂金', '钻石', 0.90, 38800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (110, 'Y字钻石项链', 'N-DIA-004', '18K金', '钻石', 0.20, 12800.00, 30)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (120, '推拉金手镯', 'B-GOL-004', '黄金', '无', 0.00, 16800.00, 25)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (130, '珍珠钻石耳环', 'E-DIA-004', '18K金', '钻石', 0.25, 16800.00, 18)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (140, '满绿翡翠吊坠', 'P-JAD-004', '黄金', '翡翠', 0.00, 128000.00, 2)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (101, '开口黄金戒指', 'R-GOL-005', '黄金', '无', 0.00, 7800.00, 35)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (111, '巴洛克珍珠项链', 'N-PEA-004', '银', '珍珠', 0.00, 1800.00, 60)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (121, '珐琅铂金手镯', 'B-PLA-003', '铂金', '无', 0.00, 22800.00, 8)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (131, '大溪地珍珠耳环', 'E-PEA-003', '铂金', '珍珠', 0.00, 12800.00, 12)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (141, '心形钻石吊坠', 'P-DIA-004', '铂金', '钻石', 0.50, 28800.00, 10)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (100, '扭臂钻戒', 'R-DIA-007', '18K金', '钻石', 0.70, 32800.00, 12)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (110, '锁骨钻石项链', 'N-DIA-005', '铂金', '钻石', 0.40, 22800.00, 18)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (120, '珐琅金手镯', 'B-GOL-005', '黄金', '无', 0.00, 38800.00, 8)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (130, '流苏钻石耳环', 'E-DIA-005', '18K金', '钻石', 0.60, 28800.00, 15)
INTO products (cat_id, product_name, product_code, metal_type, gem_type, carat_weight, unit_price, stock_qty) VALUES (140, '紫罗兰翡翠吊坠', 'P-JAD-005', '18K金', '翡翠', 0.00, 48800.00, 6)
SELECT 1 FROM DUAL;

-- 2.3 客户数据 (30条)
INSERT ALL
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('张美玲', '13800138001', 'zhangml@email.com', '钻石', '深圳', DATE '2022-03-15', 286000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('李华', '13800138002', 'lihua@email.com', '金卡', '广州', DATE '2022-06-20', 128000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('王芳', '13800138003', 'wangfang@email.com', '黑金', '北京', DATE '2021-01-10', 568000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('赵强', '13800138004', 'zhaoq@email.com', '银卡', '上海', DATE '2023-02-28', 45000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('陈静', '13800138005', 'chenj@email.com', '钻石', '深圳', DATE '2022-08-15', 328000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('刘洋', '13800138006', 'liuyang@email.com', '普通', '成都', DATE '2023-05-01', 12800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('周婷', '13800138007', 'zhout@email.com', '金卡', '杭州', DATE '2022-11-20', 98000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('吴磊', '13800138008', 'wulei@email.com', '普通', '武汉', DATE '2023-08-10', 8900.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('郑雪', '13800138009', 'zhengx@email.com', '银卡', '南京', DATE '2023-01-05', 36800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('孙丽', '13800138010', 'sunli@email.com', '钻石', '深圳', DATE '2021-09-18', 428000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('马丽', '13800138011', 'mali@email.com', '金卡', '广州', DATE '2022-04-22', 158000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('朱伟', '13800138012', 'zhuw@email.com', '普通', '重庆', DATE '2023-06-15', 22800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('胡敏', '13800138013', 'hum@email.com', '银卡', '西安', DATE '2022-12-01', 58800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('林涛', '13800138014', 'lint@email.com', '黑金', '深圳', DATE '2021-05-20', 688000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('何燕', '13800138015', 'hey@email.com', '金卡', '长沙', DATE '2022-09-10', 118000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('高飞', '13800138016', 'gaof@email.com', '普通', '郑州', DATE '2023-03-25', 15800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('罗琳', '13800138017', 'luol@email.com', '钻石', '北京', DATE '2021-11-08', 358000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('梁军', '13800138018', 'liangj@email.com', '银卡', '天津', DATE '2023-04-12', 42800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('宋佳', '13800138019', 'songj@email.com', '金卡', '苏州', DATE '2022-07-30', 138000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('韩梅', '13800138020', 'hanm@email.com', '普通', '青岛', DATE '2023-09-05', 6800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('唐勇', '13800138021', 'tangy@email.com', '钻石', '深圳', DATE '2021-08-15', 488000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('冯娟', '13800138022', 'fengj@email.com', '金卡', '东莞', DATE '2022-10-20', 168000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('曹阳', '13800138023', 'caoy@email.com', '普通', '佛山', DATE '2023-07-18', 19800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('许娜', '13800138024', 'xuna@email.com', '银卡', '珠海', DATE '2022-05-08', 68800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('邓超', '13800138025', 'dengc@email.com', '黑金', '广州', DATE '2021-03-22', 728000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('萧红', '13800138026', 'xiaoh@email.com', '金卡', '厦门', DATE '2022-08-28', 148000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('程亮', '13800138027', 'chengl@email.com', '普通', '合肥', DATE '2023-10-01', 12800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('潘婷', '13800138028', 'pant@email.com', '钻石', '深圳', DATE '2021-12-15', 398000.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('袁丽', '13800138029', 'yuanl@email.com', '银卡', '南昌', DATE '2023-02-14', 48800.00)
INTO customers (customer_name, phone, email, membership, city, reg_date, total_spent) VALUES ('蒋文', '13800138030', 'jiangw@email.com', '金卡', '宁波', DATE '2022-11-05', 108000.00)
SELECT 1 FROM DUAL;

-- 2.4 订单数据 (30条)
INSERT ALL
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (1, 1, TIMESTAMP '2024-01-05 10:30:00', '线下门店', 1, 58888.00, 0.95, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (2, 2, TIMESTAMP '2024-01-08 14:20:00', '天猫旗舰店', NULL, 28800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (3, 3, TIMESTAMP '2024-01-12 09:15:00', '线下门店', 2, 128000.00, 0.90, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (4, 4, TIMESTAMP '2024-01-15 16:45:00', '微信小程序', NULL, 8900.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (5, 5, TIMESTAMP '2024-01-20 11:00:00', '抖音直播', NULL, 15800.00, 0.88, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (6, 6, TIMESTAMP '2024-01-25 13:30:00', '线下门店', 1, 22800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (7, 7, TIMESTAMP '2024-02-01 10:00:00', '天猫旗舰店', NULL, 36800.00, 0.92, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (8, 8, TIMESTAMP '2024-02-05 15:20:00', '微信小程序', NULL, 5800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (9, 9, TIMESTAMP '2024-02-10 09:30:00', '线下门店', 3, 45800.00, 0.95, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (10, 10, TIMESTAMP '2024-02-14 14:00:00', '天猫旗舰店', NULL, 89999.00, 0.85, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (11, 11, TIMESTAMP '2024-02-20 11:30:00', '线下门店', 1, 18900.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (12, 12, TIMESTAMP '2024-03-01 10:15:00', '抖音直播', NULL, 28800.00, 0.90, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (13, 13, TIMESTAMP '2024-03-05 16:00:00', '微信小程序', NULL, 6800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (14, 14, TIMESTAMP '2024-03-10 09:00:00', '线下门店', 2, 68000.00, 0.92, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (15, 15, TIMESTAMP '2024-03-15 14:30:00', '天猫旗舰店', NULL, 15800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (16, 16, TIMESTAMP '2024-03-20 11:00:00', '线下门店', 1, 32800.00, 0.95, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (17, 17, TIMESTAMP '2024-04-01 10:30:00', '经销商', NULL, 128000.00, 0.80, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (18, 18, TIMESTAMP '2024-04-05 15:00:00', '天猫旗舰店', NULL, 22800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (19, 19, TIMESTAMP '2024-04-10 09:45:00', '线下门店', 3, 42800.00, 0.90, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (20, 20, TIMESTAMP '2024-04-15 14:00:00', '微信小程序', NULL, 5800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (21, 21, TIMESTAMP '2024-05-01 10:00:00', '线下门店', 1, 52800.00, 0.95, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (22, 22, TIMESTAMP '2024-05-05 13:30:00', '天猫旗舰店', NULL, 18600.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (23, 23, TIMESTAMP '2024-05-10 16:00:00', '抖音直播', NULL, 38800.00, 0.88, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (24, 24, TIMESTAMP '2024-05-15 11:00:00', '线下门店', 2, 28800.00, 0.92, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (25, 25, TIMESTAMP '2024-06-01 09:30:00', '经销商', NULL, 98000.00, 0.78, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (26, 26, TIMESTAMP '2024-06-05 14:00:00', '天猫旗舰店', NULL, 12800.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (27, 27, TIMESTAMP '2024-06-10 10:30:00', '线下门店', 1, 35800.00, 0.95, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (28, 28, TIMESTAMP '2024-06-15 15:30:00', '微信小程序', NULL, 8900.00, 1.00, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (29, 29, TIMESTAMP '2024-07-01 09:00:00', '线下门店', 3, 48800.00, 0.90, '已完成')
INTO orders (order_id, customer_id, order_date, sales_channel, store_id, total_amount, discount, status) VALUES (30, 30, TIMESTAMP '2024-07-05 14:30:00', '天猫旗舰店', NULL, 15800.00, 1.00, '已完成')
SELECT 1 FROM DUAL;

-- 2.5 订单明细数据
INSERT ALL
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (1, 1, 1, 58888.00, 0.95)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (2, 2, 2, 28800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (3, 3, 7, 128000.00, 0.90)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (4, 4, 4, 8900.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (5, 5, 8, 15800.00, 0.88)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (6, 6, 11, 22800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (7, 7, 9, 36800.00, 0.92)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (8, 8, 17, 5800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (9, 9, 18, 45800.00, 0.95)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (10, 10, 3, 89999.00, 0.85)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (11, 11, 14, 18900.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (12, 12, 23, 28800.00, 0.90)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (13, 13, 21, 6800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (14, 14, 18, 68000.00, 0.92)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (15, 15, 8, 15800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (16, 16, 20, 32800.00, 0.95)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (17, 17, 3, 128000.00, 0.80)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (18, 18, 24, 22800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (19, 19, 25, 42800.00, 0.90)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (20, 20, 17, 5800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (21, 21, 32, 52800.00, 0.95)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (22, 22, 24, 18600.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (23, 23, 31, 38800.00, 0.88)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (24, 24, 20, 28800.00, 0.92)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (25, 25, 3, 98000.00, 0.78)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (26, 26, 44, 12800.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (27, 27, 13, 35800.00, 0.95)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (28, 28, 17, 8900.00, 1.00)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (29, 29, 18, 48800.00, 0.90)
INTO order_items (order_id, product_id, quantity, unit_price, discount) VALUES (30, 30, 8, 15800.00, 1.00)
SELECT 1 FROM DUAL;

-- 2.6 门店员工数据 (递归CTE演示)
INSERT ALL
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('张总', NULL, '总经理', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('李经理', 1, '门店经理', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('王主管', 2, '销售主管', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('赵销售', 3, '销售员', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('钱销售', 3, '销售员', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('孙会计', 2, '会计', 1)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('刘总', NULL, '总经理', 2)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('陈经理', 6, '门店经理', 2)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('周主管', 7, '销售主管', 2)
INTO store_staff (staff_name, parent_id, position, store_id) VALUES ('吴销售', 8, '销售员', 2)
SELECT 1 FROM DUAL;

-- 2.7 月度销售宽表 (UNPIVOT演示用)
CREATE TABLE monthly_sales_wide AS
SELECT '黄金' AS cat_name, 120000 AS m_01, 98000 AS m_02, 135000 AS m_03, 110000 AS m_04, 128000 AS m_05, 142000 AS m_06 FROM DUAL
UNION ALL SELECT '钻石', 85000, 72000, 91000, 88000, 95000, 102000 FROM DUAL
UNION ALL SELECT '翡翠', 45000, 38000, 52000, 41000, 48000, 55000 FROM DUAL
UNION ALL SELECT '珍珠', 22000, 19000, 25000, 21000, 23000, 27000 FROM DUAL;

-- 2.8 产品描述填充 (前缀索引演示)
BEGIN
    FOR rec IN (SELECT product_id FROM products WHERE product_id <= 10) LOOP
        EXECUTE IMMEDIATE 'UPDATE products SET product_desc = :1 WHERE product_id = :2'
        USING '精美珠宝工艺品-' || (SELECT product_name FROM products WHERE product_id = rec.product_id), rec.product_id;
    END LOOP;
END;
/

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'oracle21_data.sql 执行完成' AS result FROM DUAL;
\n-- ============================================================================
-- 文件: oracle21_queries.sql
-- 说明: Oracle 21c 珠宝行业查询设计模式实战
-- 覆盖模块: 高效JOIN / 分页优化 / 搜索优化 / 窗口函数 / 递归CTE
-- 执行顺序: 第 3 步 (需先执行 oracle21_schema.sql 和 oracle21_data.sql)
-- 适用版本: Oracle 21c (兼容 12c+ 所有特性)
-- ============================================================================

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

-- 1.1 小表驱动大表 + 哈希连接 (Oracle 优化器自动选择)
-- 场景: 查询金卡及以上客户的订单明细
-- 优化点: 先过滤小结果集再关联, 避免大表全量扫描
-- 使用HINT提示优化器使用哈希连接
EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(c o) */ 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 >= TIMESTAMP '2024-01-01 00:00:00';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 1.2 延迟关联 (Deferred Join) 优化深分页关联查询
-- 场景: 查询已完成订单的客户与商品详情 (分页第50001-50010条)
-- 传统写法会回表50010次, 优化后仅回表10次
-- Oracle 12c+ 使用 FETCH FIRST / OFFSET 语法
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
) 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天内的复购订单
-- Oracle中日期相减直接得到天数差
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 - 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 <= o1.order_date + 30
WHERE o1.status = '已完成' AND o2.status = '已完成'
ORDER BY days_gap ASC
FETCH FIRST 20 ROWS ONLY;

-- 1.5 CTE提前过滤优化复杂多表关联
-- 场景: 查询各品类销售TOP10商品 (先过滤再关联)
-- 使用MATERIALIZED HINT提示Oracle将CTE物化
WITH /*+ MATERIALIZE */ filtered_orders AS (
    SELECT order_id, customer_id, order_date
    FROM orders
    WHERE status = '已完成'
      AND order_date >= TIMESTAMP '2024-01-01 00:00:00'
),
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
FETCH FIRST 10 ROWS ONLY;

-- 1.6 HASH JOIN 反连接 (Anti-Join) 优化NOT EXISTS场景
-- 场景: 查询从未下过订单的客户
-- 使用ANTI JOIN替代NOT IN (避免NULL陷阱)
SELECT /*+ USE_HASH(c) */ c.customer_id, c.customer_name, c.membership
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status != '已取消'
WHERE o.order_id IS NULL;

-- ============================================================================
-- 模块二: 分页优化 (Pagination)
-- ============================================================================
SELECT '========== 模块二: 分页优化 ==========' AS section FROM DUAL;

-- 2.1 游标分页 (Keyset Pagination): 适用于瀑布流/无限滚动场景
-- 场景: 小程序商品列表连续翻页, 记录上一页最后一条的ID
-- 性能: O(1)复杂度, 无论翻到第几页都毫秒级响应
-- Oracle 12c+ 使用 FETCH FIRST / OFFSET 语法
SELECT product_id, product_name, unit_price, stock_qty
FROM products
WHERE product_id > 5
  AND is_active = 1
ORDER BY product_id ASC
FETCH FIRST 10 ROWS ONLY;

-- 2.2 延迟关联分页: 适用于后台管理系统跳页场景
-- 场景: 后台订单列表跳转到第8000页
-- 原理: 子查询通过覆盖索引定位主键, 再回表取完整数据
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
    OFFSET 159980 ROWS FETCH NEXT 20 ROWS ONLY
) 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;

-- 2.4 基于时间戳的游标分页 (适用于无主键排序场景)
-- 场景: 按下单时间分页获取订单
SELECT order_id, order_date, total_amount, customer_id
FROM orders
WHERE order_date < TIMESTAMP '2024-03-01 00:00:00'
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

-- ============================================================================
-- 模块三: 搜索优化查询 (Search-Optimized Queries)
-- ============================================================================
SELECT '========== 模块三: 搜索优化查询 ==========' AS section FROM DUAL;

-- 3.1 全文搜索: 商品名称模糊查询 (Oracle Text)
-- 场景: 客户搜索含"钻石"或"金"的商品
-- 优势: LIKE '%keyword%' 无法利用索引, Oracle Text CONTEXT索引性能高
-- 前提: 需先创建文本索引 (已在schema中注释)
-- CREATE INDEX idx_ft_product_name ON products(product_name) INDEXTYPE IS CTXSYS.CONTEXT;
/*
SELECT product_id, product_name, unit_price, cat_id,
       SCORE(1) AS relevance
FROM products
WHERE CONTAINS(product_name, '钻石 OR 金', 1) > 0
ORDER BY relevance DESC
FETCH FIRST 20 ROWS ONLY;
*/

-- 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 前缀索引优化长文本字段 (Oracle函数索引)
-- 场景: 商品描述字段较长, 建立前缀索引节省空间
-- Oracle通过函数索引实现前缀匹配
SELECT
    COUNT(DISTINCT LOWER(SUBSTR(product_desc, 1, 10))) / COUNT(*) AS sel_10,
    COUNT(DISTINCT LOWER(SUBSTR(product_desc, 1, 20))) / COUNT(*) AS sel_20,
    COUNT(DISTINCT product_desc) / COUNT(*) AS sel_full
FROM products;

-- 3.4 动态参数化搜索 (多条件可选)
-- 场景: 后台商品搜索, 支持按品类/金属类型/价格范围多条件组合
-- Oracle中使用绑定变量实现
DECLARE
    l_cat_id     NUMBER := NULL;
    l_metal_type VARCHAR2(10) := NULL;
    l_min_price  NUMBER := 0;
    l_max_price  NUMBER := 100000;
BEGIN
    FOR rec IN (
        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 (l_cat_id IS NULL OR p.cat_id = l_cat_id)
          AND (l_metal_type IS NULL OR p.metal_type = l_metal_type)
          AND p.unit_price >= l_min_price
          AND p.unit_price <= l_max_price
        ORDER BY p.unit_price DESC
        FETCH FIRST 20 ROWS ONLY
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.product_id || '|' || rec.product_name || '|' || rec.unit_price);
    END LOOP;
END;
/

-- 3.5 正则表达式搜索 (Oracle REGEXP_LIKE)
-- 场景: 搜索手机号匹配特定模式的客户
SELECT customer_id, customer_name, phone, city
FROM customers
WHERE REGEXP_LIKE(phone, '^13[8-9]')
  AND city IN ('深圳', '广州', '北京')
FETCH FIRST 20 ROWS ONLY;

-- ============================================================================
-- 模块四: 窗口函数 (Window Functions)
-- ============================================================================
SELECT '========== 模块四: 窗口函数实战 ==========' AS section FROM DUAL;

-- 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 EXTRACT(YEAR FROM order_date) = 2024
ORDER BY sales_channel, row_rank
FETCH FIRST 30 ROWS ONLY;

-- 4.2 LAG/LEAD: 计算每日销售额环比
-- 场景: 分析门店每日销售额变化趋势
WITH daily_sales AS (
    SELECT
        TRUNC(order_date) AS sale_date,
        SUM(total_amount) AS daily_amount,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = '已完成'
      AND EXTRACT(YEAR FROM order_date) = 2024
    GROUP BY TRUNC(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))
          / 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
FETCH FIRST 30 ROWS ONLY;

-- 4.3 累计求和与移动平均
-- 场景: 计算累计销售额与3日移动平均订单量
WITH daily_stats AS (
    SELECT
        TRUNC(order_date) AS sale_date,
        SUM(total_amount) AS daily_amount,
        COUNT(*) AS order_count
    FROM orders
    WHERE status = '已完成'
    GROUP BY TRUNC(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
FETCH FIRST 30 ROWS ONLY;

-- 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
FETCH FIRST 30 ROWS ONLY;

-- 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/DISC: 分位数分析
-- 场景: 计算各会员等级的消费分位数 (中位数、90分位、95分位)
SELECT
    membership,
    COUNT(*) AS cust_count,
    ROUND(AVG(total_spent), 2) AS avg_spent,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY total_spent) AS median_spent,
    PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY total_spent) AS p90_spent,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY total_spent) AS p95_spent,
    MAX(total_spent) AS max_spent
FROM customers
GROUP BY membership
ORDER BY membership;

-- 4.7 RATIO_TO_REPORT: 占比分析
-- 场景: 各销售渠道占总销售额的比例
SELECT
    sales_channel,
    SUM(total_amount) AS channel_total,
    ROUND(SUM(total_amount) * RATIO_TO_REPORT(SUM(total_amount)) OVER () * 100, 2) AS pct_of_total
FROM orders
WHERE status = '已完成'
GROUP BY sales_channel
ORDER BY channel_total DESC;

-- 4.8 KEEP (DENSE_RANK FIRST/LAST): 聚合函数配合排名
-- 场景: 获取每个品类销售额最高的产品名称
WITH product_ranked AS (
    SELECT
        jc.cat_name AS category,
        p.product_name,
        SUM(oi.quantity * oi.unit_price * oi.discount) AS total_sales,
        DENSE_RANK() OVER (PARTITION BY jc.cat_name ORDER BY SUM(oi.quantity * oi.unit_price * oi.discount) DESC) AS rn
    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 = '已完成'
    GROUP BY jc.cat_name, p.product_name
)
SELECT category, product_name, total_sales
FROM product_ranked
WHERE rn = 1
ORDER BY category;

-- ============================================================================
-- 模块五: 递归CTE (Recursive CTEs)
-- ============================================================================
SELECT '========== 模块五: 递归CTE实战 ==========' AS section FROM DUAL;

-- 5.1 珠宝分类树展开: 查询"珠宝首饰"下的所有子分类 (任意层级)
-- Oracle 11g+ 支持递归CTE (WITH ... CONNECT BY 的替代方案)
WITH RECURSIVE category_tree AS (
    -- 锚点: 根节点
    SELECT cat_id, parent_id, cat_name, cat_level,
           CAST(cat_name AS VARCHAR2(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,
           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 VARCHAR2(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,
           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,
           CAST(staff_name AS VARCHAR2(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,
           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 传统CONNECT BY写法 (Oracle特有, 兼容旧版本)
-- 场景: 使用CONNECT BY查询分类树 (Oracle传统层次查询)
SELECT cat_id, parent_id, cat_name, cat_level,
       LEVEL AS tree_level,
       SYS_CONNECT_BY_PATH(cat_name, '>') AS path,
       CONNECT_BY_ROOT cat_name AS root_name
FROM jewelry_category
START WITH parent_id IS NULL
CONNECT BY parent_id = PRIOR cat_id
ORDER BY cat_level, cat_id;

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'oracle21_queries.sql 执行完成' AS result FROM DUAL;
\n-- ============================================================================
-- 文件: oracle21_pivot_grouping.sql
-- 说明: Oracle 21c 珠宝行业 PIVOT/UNPIVOT 透视转换 + GROUPING SETS多级聚合实战
-- 执行顺序: 第 4 步 (需先执行 oracle21_schema.sql 和 oracle21_data.sql)
-- 适用版本: Oracle 21c (原生PIVOT/UNPIVOT, GROUPING SETS/ROLLUP/CUBE)
-- ============================================================================

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

-- 6.1 原生PIVOT: 各品类按月销售额透视
-- 场景: 管理层需要看各品类 2024年 各月的月度销售对比
-- Oracle 11g+ 原生PIVOT语法 (比条件聚合更简洁)
SELECT * 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 >= TIMESTAMP '2024-01-01 00:00:00'
      AND o.order_date < TIMESTAMP '2024-07-01 00:00:00'
    GROUP BY jc.cat_name, TO_CHAR(o.order_date, 'YYYY-MM')
)
PIVOT (
    SUM(month_amount) FOR sale_month IN (
        '2024-01' AS "1月", '2024-02' AS "2月", '2024-03' AS "3月",
        '2024-04' AS "4月", '2024-05' AS "5月", '2024-06' AS "6月"
    )
)
ORDER BY "1月" + "2月" + "3月" + "4月" + "5月" + "6月" DESC;

-- 6.2 原生PIVOT: 各销售渠道季度业绩对比
-- 场景: 对比各渠道 2024年 Q1-Q4 的业绩
SELECT * FROM (
    SELECT
        sales_channel,
        'Q' || TO_CHAR(order_date, 'Q') AS quarter,
        SUM(total_amount) AS q_amount
    FROM orders
    WHERE status = '已完成'
      AND EXTRACT(YEAR FROM order_date) = 2024
    GROUP BY sales_channel, TO_CHAR(order_date, 'Q')
)
PIVOT (
    SUM(q_amount) FOR quarter IN (
        'Q1' AS Q1, 'Q2' AS Q2, 'Q3' AS Q3, 'Q4' AS Q4
    )
);

-- 6.3 条件聚合实现PIVOT (多聚合函数)
-- 场景: 同时透视销售额和订单数
SELECT
    cat_name AS "品类",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-01' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "1月销售额",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-02' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "2月销售额",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-03' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "3月销售额",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-04' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "4月销售额",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-05' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "5月销售额",
    SUM(CASE WHEN TO_CHAR(o.order_date, 'YYYY-MM') = '2024-06' THEN oi.quantity * oi.unit_price * oi.discount ELSE 0 END) AS "6月销售额",
    SUM(oi.quantity * 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 jc.cat_level = 2
  AND o.order_date >= TIMESTAMP '2024-01-01 00:00:00'
  AND o.order_date < TIMESTAMP '2024-07-01 00:00:00'
GROUP BY jc.cat_name
ORDER BY "上半年合计" DESC;

-- 6.4 原生UNPIVOT: 宽表还原为长表
-- 场景: 现有月度销售宽表, 需还原为长表以便做趋势分析或导入BI工具
-- Oracle 11g+ 原生UNPIVOT语法
SELECT cat_name AS "品类", month_val AS "月份", amount_val AS "销售额"
FROM monthly_sales_wide
UNPIVOT (
    amount_val FOR month_val IN (m_01, m_02, m_03, m_04, m_05, m_06)
)
ORDER BY cat_name, month_val;

-- 6.5 条件聚合实现UNPIVOT (排除NULL值)
-- 场景: 还原为长表并过滤空值
SELECT cat_name AS "品类", month_col AS "月份", amount_val AS "销售额"
FROM (
    SELECT cat_name, m_01, m_02, m_03, m_04, m_05, m_06
    FROM monthly_sales_wide
)
UNPIVOT INCLUDE NULLS (
    amount_val FOR month_col IN (m_01, m_02, m_03, m_04, m_05, m_06)
)
WHERE amount_val IS NOT NULL
ORDER BY cat_name, month_col;

-- 6.6 PIVOT多列聚合
-- 场景: 同时透视销售额和订单数
SELECT * FROM (
    SELECT
        jc.cat_name,
        TO_CHAR(o.order_date, 'YYYY-MM') AS sale_month,
        SUM(oi.quantity * oi.unit_price * oi.discount) AS sale_amount,
        COUNT(*) AS order_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 jc.cat_level = 2
      AND o.order_date >= TIMESTAMP '2024-01-01 00:00:00'
      AND o.order_date < TIMESTAMP '2024-07-01 00:00:00'
    GROUP BY jc.cat_name, TO_CHAR(o.order_date, 'YYYY-MM')
)
PIVOT (
    SUM(sale_amount) AS amt, COUNT(order_count) AS cnt
    FOR sale_month IN ('2024-01', '2024-02', '2024-03', '2024-04', '2024-05', '2024-06')
);

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

-- 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 >= TIMESTAMP '2024-01-01 00:00:00'
GROUP BY GROUPING SETS (
    (jc.cat_name, o.sales_channel),
    (jc.cat_name),
    (o.sales_channel),
    ()
)
ORDER BY g_cat, g_channel, SUM(oi.quantity * oi.unit_price * oi.discount) 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 >= TIMESTAMP '2024-01-01 00:00:00'
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 >= TIMESTAMP '2024-01-01 00:00:00'
  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 >= TIMESTAMP '2024-01-01 00:00:00'
GROUP BY CUBE (jc.cat_name, o.sales_channel, c.membership)
ORDER BY grouping_id, SUM(oi.quantity * oi.unit_price * oi.discount) DESC
FETCH FIRST 50 ROWS ONLY;

-- 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 >= TIMESTAMP '2024-01-01 00:00:00'
GROUP BY GROUPING SETS (
    ROLLUP(jc.cat_name, o.sales_channel),
    (c.city)
)
ORDER BY jc.cat_name, o.sales_channel, c.city
FETCH FIRST 60 ROWS ONLY;

-- 7.6 GROUPING SETS vs UNION ALL 性能对比
-- 场景: 用EXPLAIN PLAN对比两种写法的执行计划
-- 传统UNION ALL写法 (扫描多次表)
EXPLAIN PLAN FOR
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 >= TIMESTAMP '2024-01-01 00:00:00'
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 >= TIMESTAMP '2024-01-01 00:00:00'
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 >= TIMESTAMP '2024-01-01 00:00:00';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- GROUPING SETS写法 (仅扫描1次表, 优化器自动复用中间结果)
EXPLAIN PLAN FOR
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 >= TIMESTAMP '2024-01-01 00:00:00'
GROUP BY GROUPING SETS ((jc.cat_name), (o.sales_channel), ());

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'oracle21_pivot_grouping.sql 执行完成' AS result FROM DUAL;
\n-- ============================================================================
-- 文件: oracle21_antipatterns.sql
-- 说明: Oracle 21c 珠宝行业 反模式规避 + 综合实战报表
-- 执行顺序: 第 5 步 (需先执行 oracle21_schema.sql 和 oracle21_data.sql)
-- 适用版本: Oracle 21c
-- ============================================================================

-- ============================================================================
-- 模块八: 反模式规避 (Anti-Patterns)
-- ============================================================================
SELECT '========== 模块八: 反模式规避 ==========' AS section FROM DUAL;

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

-- 正确写法: 使用范围查询, 可利用idx_order_date索引
EXPLAIN PLAN FOR
SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= TIMESTAMP '2024-01-01 00:00:00'
  AND order_date < TIMESTAMP '2025-01-01 00:00:00';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 8.2 反模式: SELECT * 导致覆盖索引失效
-- 错误写法: SELECT * 迫使回表取所有列
-- EXPLAIN PLAN FOR SELECT * FROM orders WHERE status = '已完成';

-- 正确写法: 只查需要的列, 命中覆盖索引
EXPLAIN PLAN FOR
SELECT order_id, order_date, total_amount, status
FROM orders
WHERE status = '已完成';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 8.3 反模式: 隐式类型转换导致索引失效
-- 错误写法: phone是VARCHAR2, 用数字比较会触发隐式转换
-- EXPLAIN PLAN FOR SELECT * FROM customers WHERE phone = 13800138001;

-- 正确写法: 字符串类型用字符串比较
EXPLAIN PLAN FOR
SELECT customer_id, customer_name, phone
FROM customers
WHERE phone = '13800138001';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

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

-- 正确写法: 拆分为UNION ALL, 各分支可独立用索引
EXPLAIN PLAN FOR
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 != '已完成';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 8.5 反模式: 不当使用DISTINCT去重
-- 场景: 用DISTINCT去重但没加ORDER BY, 可能导致排序文件表
-- 错误写法
-- SELECT DISTINCT customer_id, customer_name FROM customers ORDER BY customer_id;

-- 正确写法: 用ROW_NUMBER()替代DISTINCT
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 + 365;

-- 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 反模式: 过度聚合导致数据膨胀
-- 场景: 在不必要的层级做GROUP BY再JOIN
-- 错误写法: 先聚合再JOIN导致中间结果集过大
-- WITH aggregated AS (
--     SELECT customer_id, SUM(total_amount) AS total
--     FROM orders GROUP BY customer_id
-- )
-- SELECT c.*, a.total FROM customers c JOIN aggregated a ON c.customer_id = a.customer_id;

-- 正确写法: 直接在JOIN后聚合
SELECT c.customer_id, c.customer_name,
       SUM(o.total_amount) AS total_spent
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
ORDER BY total_spent DESC
FETCH FIRST 20 ROWS ONLY;

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

-- 9.1 多维度透视 + 多级聚合: 2024年各品类x渠道x季度的销售矩阵
WITH quarterly_category_sales AS (
    SELECT
        jc.cat_name,
        o.sales_channel,
        'Q' || TO_CHAR(o.order_date, 'Q') 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 >= TIMESTAMP '2024-01-01 00:00:00'
      AND o.order_date < TIMESTAMP '2025-01-01 00:00:00'
    GROUP BY jc.cat_name, o.sales_channel, TO_CHAR(o.order_date, 'Q')
)
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,
        TRUNC(SYSDATE) - 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,
    CAST(NTILE(5) OVER (ORDER BY recency ASC) AS VARCHAR2(10)) ||
    CAST(NTILE(5) OVER (ORDER BY frequency ASC) AS VARCHAR2(10)) ||
    CAST(NTILE(5) OVER (ORDER BY monetary ASC) AS VARCHAR2(10)) 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
FETCH FIRST 30 ROWS ONLY;

-- 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 LISTAGG: 客户购买产品列表汇总
-- 场景: 查询每个客户购买过的所有产品名称 (行转字符串)
SELECT
    c.customer_id,
    c.customer_name,
    LISTAGG(p.product_name, ', ') WITHIN GROUP (ORDER BY p.product_name) AS purchased_products
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.status = '已完成'
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
FETCH FIRST 15 ROWS ONLY;

-- 9.8 MATCH_RECOGNIZE (Oracle 12c+): 销售模式识别
-- 场景: 识别连续上涨/下跌的销售趋势模式
-- 这是 Oracle 21c 的亮点功能, 类似SQL中的模式匹配
WITH daily_revenue AS (
    SELECT
        TRUNC(order_date) AS sale_date,
        SUM(total_amount) AS daily_amount
    FROM orders
    WHERE status = '已完成'
    GROUP BY TRUNC(order_date)
    ORDER BY sale_date
)
SELECT * FROM daily_revenue
MATCH_RECOGNIZE (
    ORDER BY sale_date
    PARTITION BY 1
    MEASURES
        FIRST(sale_date) AS trend_start,
        LAST(sale_date) AS trend_end,
        CLASSIFIER() AS trend_type
    ONE ROW PER MATCH
    PATTERN (UPUPUP|DOWNDOWNDOWN|STABLE+)
    DEFINE
        UP AS UP.daily_amount > PREV(UP.daily_amount),
        DOWN AS DOWN.daily_amount < PREV(DOWN.daily_amount)
);

-- ============================================================================
-- 执行完成提示
-- ============================================================================
SELECT 'oracle21_antipatterns.sql 执行完成' AS result FROM DUAL;
\n