# 商品分类表
CREATE TABLE CATEGORY (
CATEGORY_ID VARCHAR(20) NOT NULL,
PARENT_CATEGORY_ID VARCHAR(20),
CATEGORY_NAME VARCHAR(100) NOT NULL,
CATEGORY_LEVEL INTEGER NOT NULL,
SORT_ORDER INTEGER,
CATEGORY_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_CATEGORY PRIMARY KEY (CATEGORY_ID)
) ORGANIZE BY ROW;

# 品牌表
CREATE TABLE BRAND (
BRAND_ID VARCHAR(20) NOT NULL,
BRAND_NAME VARCHAR(100) NOT NULL,
BRAND_LOGO VARCHAR(500),
BRAND_DESC VARCHAR(500),
BRAND_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_BRAND PRIMARY KEY (BRAND_ID)
) ORGANIZE BY ROW;

# 商品主表
CREATE TABLE PRODUCT (
PRODUCT_ID VARCHAR(32) NOT NULL,
PRODUCT_CODE VARCHAR(50) NOT NULL,
PRODUCT_NAME VARCHAR(200) NOT NULL,
SHORT_NAME VARCHAR(100),
CATEGORY_ID VARCHAR(20) NOT NULL,
BRAND_ID VARCHAR(20),
UNIT VARCHAR(20),
SPEC VARCHAR(200),
BARCODE VARCHAR(50),
ORIGIN_PRICE DECIMAL(10, 2),
SALE_PRICE DECIMAL(10, 2) NOT NULL,
MEMBER_PRICE DECIMAL(10, 2),
COST_PRICE DECIMAL(10, 2),
WEIGHT DECIMAL(10, 3),
IMAGE_URL VARCHAR(500),
PRODUCT_DESC CLOB,
PRODUCT_STATUS VARCHAR(10) NOT NULL,
IS_HOT CHAR(1) DEFAULT ‘N’,
IS_NEW CHAR(1) DEFAULT ‘N’,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID)
) ORGANIZE BY ROW;

# 商品SKU表
CREATE TABLE PRODUCT_SKU (
SKU_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
SKU_CODE VARCHAR(50) NOT NULL,
SKU_NAME VARCHAR(200) NOT NULL,
SKU_ATTRS VARCHAR(500),
BARCODE VARCHAR(50),
SALE_PRICE DECIMAL(10, 2) NOT NULL,
MEMBER_PRICE DECIMAL(10, 2),
COST_PRICE DECIMAL(10, 2),
SKU_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PRODUCT_SKU PRIMARY KEY (SKU_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_PRODUCT_CODE ON PRODUCT(PRODUCT_CODE);
CREATE INDEX IDX_PRODUCT_CATEGORY ON PRODUCT(CATEGORY_ID, PRODUCT_STATUS);
CREATE INDEX IDX_PRODUCT_BRAND ON PRODUCT(BRAND_ID, PRODUCT_STATUS);
CREATE INDEX IDX_PRODUCT_SKU_PRODUCT ON PRODUCT_SKU(PRODUCT_ID);
CREATE UNIQUE INDEX IDX_PRODUCT_SKU_CODE ON PRODUCT_SKU(SKU_CODE);

# 仓库表
CREATE TABLE WAREHOUSE (
WAREHOUSE_ID VARCHAR(20) NOT NULL,
WAREHOUSE_NAME VARCHAR(100) NOT NULL,
WAREHOUSE_TYPE VARCHAR(20),
ADDRESS VARCHAR(200),
CONTACT_PERSON VARCHAR(50),
CONTACT_PHONE VARCHAR(20),
WAREHOUSE_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_WAREHOUSE PRIMARY KEY (WAREHOUSE_ID)
) ORGANIZE BY ROW;

# 库存表
CREATE TABLE INVENTORY (
INVENTORY_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAREHOUSE_ID VARCHAR(20) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
QUANTITY INTEGER NOT NULL DEFAULT 0,
AVAILABLE_QUANTITY INTEGER NOT NULL DEFAULT 0,
LOCKED_QUANTITY INTEGER NOT NULL DEFAULT 0,
WARNING_QUANTITY INTEGER DEFAULT 10,
LAST_IN_TIME TIMESTAMP,
LAST_OUT_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_INVENTORY PRIMARY KEY (INVENTORY_ID)
) ORGANIZE BY ROW;

# 库存流水表
CREATE TABLE INVENTORY_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
WAREHOUSE_ID VARCHAR(20) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
LOG_TYPE VARCHAR(20) NOT NULL,
QUANTITY_BEFORE INTEGER NOT NULL,
QUANTITY_CHANGE INTEGER NOT NULL,
QUANTITY_AFTER INTEGER NOT NULL,
RELATED_ID VARCHAR(32),
RELATED_TYPE VARCHAR(20),
REMARK VARCHAR(500),
OPERATOR_ID VARCHAR(32),
OPERATOR_NAME VARCHAR(50),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_INVENTORY_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);

# 创建索引
CREATE UNIQUE INDEX IDX_INVENTORY_WH_SKU ON INVENTORY(WAREHOUSE_ID, SKU_ID);
CREATE INDEX IDX_INVENTORY_LOG_WH_SKU ON INVENTORY_LOG(WAREHOUSE_ID, SKU_ID, CREATE_TIME DESC) PARTITIONED;
CREATE INDEX IDX_INVENTORY_LOG_RELATED ON INVENTORY_LOG(RELATED_ID, RELATED_TYPE) PARTITIONED;

# 订单主表
CREATE TABLE ORDER (
ORDER_ID VARCHAR(32) NOT NULL,
ORDER_NO VARCHAR(50) NOT NULL,
MEMBER_ID VARCHAR(32),
STORE_ID VARCHAR(20),
ORDER_TYPE VARCHAR(20) NOT NULL,
ORDER_SOURCE VARCHAR(20),
TOTAL_AMOUNT DECIMAL(10, 2) NOT NULL,
DISCOUNT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
FREIGHT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
ACTUAL_AMOUNT DECIMAL(10, 2) NOT NULL,
PAYMENT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
PAYMENT_STATUS VARCHAR(20) NOT NULL,
PAYMENT_TIME TIMESTAMP,
ORDER_STATUS VARCHAR(20) NOT NULL,
RECEIVER_NAME VARCHAR(100),
RECEIVER_PHONE VARCHAR(20),
RECEIVER_ADDRESS VARCHAR(500),
REMARK VARCHAR(500),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
PAY_TIME TIMESTAMP,
DELIVERY_TIME TIMESTAMP,
RECEIVE_TIME TIMESTAMP,
CANCEL_TIME TIMESTAMP,
CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_ID)
) ORGANIZE BY ROW;

# 订单明细表
CREATE TABLE ORDER_DETAIL (
DETAIL_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
ORDER_ID VARCHAR(32) NOT NULL,
SKU_ID VARCHAR(32) NOT NULL,
PRODUCT_ID VARCHAR(32) NOT NULL,
PRODUCT_NAME VARCHAR(200) NOT NULL,
SKU_ATTRS VARCHAR(500),
QUANTITY INTEGER NOT NULL,
UNIT_PRICE DECIMAL(10, 2) NOT NULL,
DISCOUNT_AMOUNT DECIMAL(10, 2) DEFAULT 0,
TOTAL_AMOUNT DECIMAL(10, 2) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (DETAIL_ID)
) ORGANIZE BY ROW;

# 支付记录表
CREATE TABLE PAYMENT (
PAYMENT_ID VARCHAR(32) NOT NULL,
ORDER_ID VARCHAR(32) NOT NULL,
PAYMENT_NO VARCHAR(50) NOT NULL,
PAYMENT_TYPE VARCHAR(20) NOT NULL,
PAYMENT_AMOUNT DECIMAL(10, 2) NOT NULL,
PAYMENT_STATUS VARCHAR(20) NOT NULL,
THIRD_PARTY_NO VARCHAR(100),
PAYMENT_TIME TIMESTAMP,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_PAYMENT PRIMARY KEY (PAYMENT_ID)
) ORGANIZE BY ROW;

# 创建索引
CREATE UNIQUE INDEX IDX_ORDER_NO ON ORDER(ORDER_NO);
CREATE INDEX IDX_ORDER_MEMBER ON ORDER(MEMBER_ID, CREATE_TIME DESC);
CREATE INDEX IDX_ORDER_STATUS ON ORDER(ORDER_STATUS, CREATE_TIME DESC);
CREATE INDEX IDX_ORDER_DETAIL_ORDER ON ORDER_DETAIL(ORDER_ID);
CREATE INDEX IDX_PAYMENT_ORDER ON PAYMENT(ORDER_ID);

# 库存扣减存储过程
CREATE OR REPLACE PROCEDURE SP_DEDUCT_INVENTORY(
IN p_warehouse_id VARCHAR(20),
IN p_sku_id VARCHAR(32),
IN p_quantity INTEGER,
IN p_related_id VARCHAR(32),
IN p_related_type VARCHAR(20),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_quantity INTEGER;
DECLARE v_available_quantity INTEGER;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘库存扣减成功’;

SELECT QUANTITY, AVAILABLE_QUANTITY INTO v_quantity, v_available_quantity
FROM INVENTORY
WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id
WITH RS USE AND KEEP UPDATE LOCKS;

IF v_available_quantity < p_quantity THEN SET p_result_code = 'FAIL'; SET p_result_msg = '库存不足'; RETURN; END IF; UPDATE INVENTORY SET QUANTITY = QUANTITY - p_quantity, AVAILABLE_QUANTITY = AVAILABLE_QUANTITY - p_quantity, LAST_OUT_TIME = CURRENT TIMESTAMP, UPDATE_TIME = CURRENT TIMESTAMP WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id; INSERT INTO INVENTORY_LOG ( WAREHOUSE_ID, SKU_ID, LOG_TYPE, QUANTITY_BEFORE, QUANTITY_CHANGE, QUANTITY_AFTER, RELATED_ID, RELATED_TYPE ) VALUES ( p_warehouse_id, p_sku_id, 'DEDUCT', v_quantity, -p_quantity, v_quantity - p_quantity, p_related_id, p_related_type ); END; # 库存回滚存储过程 CREATE OR REPLACE PROCEDURE SP_ROLLBACK_INVENTORY( IN p_warehouse_id VARCHAR(20), IN p_sku_id VARCHAR(32), IN p_quantity INTEGER, IN p_related_id VARCHAR(32), IN p_related_type VARCHAR(20), OUT p_result_code VARCHAR(10), OUT p_result_msg VARCHAR(200) ) LANGUAGE SQL BEGIN DECLARE v_quantity INTEGER; SET p_result_code = 'SUCCESS'; SET p_result_msg = '库存回滚成功'; SELECT QUANTITY INTO v_quantity FROM INVENTORY WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id WITH RS USE AND KEEP UPDATE LOCKS; UPDATE INVENTORY SET QUANTITY = QUANTITY + p_quantity, AVAILABLE_QUANTITY = AVAILABLE_QUANTITY + p_quantity, LAST_IN_TIME = CURRENT TIMESTAMP, UPDATE_TIME = CURRENT TIMESTAMP WHERE WAREHOUSE_ID = p_warehouse_id AND SKU_ID = p_sku_id; INSERT INTO INVENTORY_LOG ( WAREHOUSE_ID, SKU_ID, LOG_TYPE, QUANTITY_BEFORE, QUANTITY_CHANGE, QUANTITY_AFTER, RELATED_ID, RELATED_TYPE ) VALUES ( p_warehouse_id, p_sku_id, 'ROLLBACK', v_quantity, p_quantity, v_quantity + p_quantity, p_related_id, p_related_type ); END;

# 创建订单存储过程
CREATE OR REPLACE PROCEDURE SP_CREATE_ORDER(
IN p_member_id VARCHAR(32),
IN p_store_id VARCHAR(20),
IN p_order_type VARCHAR(20),
IN p_warehouse_id VARCHAR(20),
OUT p_order_id VARCHAR(32),
OUT p_result_code VARCHAR(10),
OUT p_result_msg VARCHAR(200)
)
LANGUAGE SQL
BEGIN
DECLARE v_order_id VARCHAR(32);
DECLARE v_order_no VARCHAR(50);
DECLARE v_total_amount DECIMAL(10, 2) DEFAULT 0;
DECLARE v_actual_amount DECIMAL(10, 2) DEFAULT 0;
DECLARE v_sku_id VARCHAR(32);
DECLARE v_quantity INTEGER;
DECLARE v_unit_price DECIMAL(10, 2);
DECLARE v_not_found INTEGER DEFAULT 0;

DECLARE c1 CURSOR FOR
SELECT SKU_ID, QUANTITY, UNIT_PRICE FROM TEMP_ORDER_ITEMS;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_not_found = 1;

SET p_result_code = ‘SUCCESS’;
SET p_result_msg = ‘订单创建成功’;
SET v_order_id = ‘ORD’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISSFF6’);
SET v_order_no = ‘NO’ || TO_CHAR(CURRENT TIMESTAMP, ‘YYYYMMDDHH24MISS’);

START TRANSACTION;

OPEN c1;

FETCH_LOOP:
LOOP
FETCH c1 INTO v_sku_id, v_quantity, v_unit_price;

IF v_not_found = 1 THEN
LEAVE FETCH_LOOP;
END IF;

CALL SP_DEDUCT_INVENTORY(
p_warehouse_id, v_sku_id, v_quantity, v_order_id, ‘ORDER’,
?, ?
);

IF p_result_code <> ‘SUCCESS’ THEN
ROLLBACK;
RETURN;
END IF;

SET v_total_amount = v_total_amount + (v_unit_price * v_quantity);

END LOOP FETCH_LOOP;

CLOSE c1;

SET v_actual_amount = v_total_amount;

INSERT INTO ORDER (
ORDER_ID, ORDER_NO, MEMBER_ID, STORE_ID, ORDER_TYPE,
TOTAL_AMOUNT, ACTUAL_AMOUNT, PAYMENT_STATUS, ORDER_STATUS
) VALUES (
v_order_id, v_order_no, p_member_id, p_store_id, p_order_type,
v_total_amount, v_actual_amount, ‘UNPAID’, ‘PENDING_PAYMENT’
);

INSERT INTO ORDER_DETAIL (
ORDER_ID, SKU_ID, PRODUCT_ID, PRODUCT_NAME, SKU_ATTRS,
QUANTITY, UNIT_PRICE, TOTAL_AMOUNT
)
SELECT
v_order_id, SKU_ID, PRODUCT_ID, PRODUCT_NAME, SKU_ATTRS,
QUANTITY, UNIT_PRICE, UNIT_PRICE * QUANTITY
FROM TEMP_ORDER_ITEMS;

SET p_order_id = v_order_id;

COMMIT;

END;

# 会员表
CREATE TABLE MEMBER (
MEMBER_ID VARCHAR(32) NOT NULL,
MEMBER_NO VARCHAR(50) NOT NULL,
MEMBER_NAME VARCHAR(100),
PHONE VARCHAR(20),
EMAIL VARCHAR(100),
GENDER VARCHAR(10),
BIRTH_DATE DATE,
MEMBER_LEVEL_ID VARCHAR(20),
POINTS INTEGER DEFAULT 0,
TOTAL_POINTS INTEGER DEFAULT 0,
BALANCE DECIMAL(10, 2) DEFAULT 0,
TOTAL_ORDER_COUNT INTEGER DEFAULT 0,
TOTAL_ORDER_AMOUNT DECIMAL(10, 2) DEFAULT 0,
LAST_ORDER_TIME TIMESTAMP,
REGISTER_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
MEMBER_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEMBER PRIMARY KEY (MEMBER_ID)
) ORGANIZE BY ROW;

# 会员等级表
CREATE TABLE MEMBER_LEVEL (
LEVEL_ID VARCHAR(20) NOT NULL,
LEVEL_NAME VARCHAR(50) NOT NULL,
MIN_POINTS INTEGER,
DISCOUNT_RATE DECIMAL(5, 2),
LEVEL_STATUS VARCHAR(10) NOT NULL,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
UPDATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEMBER_LEVEL PRIMARY KEY (LEVEL_ID)
) ORGANIZE BY ROW;

# 积分流水表
CREATE TABLE POINTS_LOG (
LOG_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MEMBER_ID VARCHAR(32) NOT NULL,
LOG_TYPE VARCHAR(20) NOT NULL,
POINTS_BEFORE INTEGER NOT NULL,
POINTS_CHANGE INTEGER NOT NULL,
POINTS_AFTER INTEGER NOT NULL,
RELATED_ID VARCHAR(32),
RELATED_TYPE VARCHAR(20),
REMARK VARCHAR(500),
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_POINTS_LOG PRIMARY KEY (LOG_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);

# 会员消费记录表
CREATE TABLE MEMBER_CONSUME (
CONSUME_ID BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY,
MEMBER_ID VARCHAR(32) NOT NULL,
ORDER_ID VARCHAR(32),
CONSUME_TYPE VARCHAR(20) NOT NULL,
CONSUME_AMOUNT DECIMAL(10, 2) NOT NULL,
POINTS_EARNED INTEGER DEFAULT 0,
CREATE_TIME TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
CONSTRAINT PK_MEMBER_CONSUME PRIMARY KEY (CONSUME_ID)
) PARTITION BY RANGE (CREATE_TIME)
(
STARTING ‘2026-01-01-00.00.00.000000’
ENDING ‘2026-12-31-23.59.59.999999’
EVERY 1 MONTH
);

# 创建索引
CREATE UNIQUE INDEX IDX_MEMBER_NO ON MEMBER(MEMBER_NO);
CREATE INDEX IDX_MEMBER_PHONE ON MEMBER(PHONE);
CREATE INDEX IDX_MEMBER_LEVEL ON MEMBER(MEMBER_LEVEL_ID, MEMBER_STATUS);
CREATE INDEX IDX_POINTS_LOG_MEMBER ON POINTS_LOG(MEMBER_ID, CREATE_TIME DESC) PARTITIONED;
CREATE INDEX IDX_MEMBER_CONSUME_MEMBER ON MEMBER_CONSUME(MEMBER_ID, CREATE_TIME DESC) PARTITIONED;

# 销售统计MQT
CREATE TABLE MQT_SALES_DAILY AS (
SELECT
DATE(CREATE_TIME) AS SALE_DATE,
STORE_ID,
ORDER_TYPE,
COUNT(*) AS ORDER_COUNT,
COUNT(DISTINCT MEMBER_ID) AS MEMBER_COUNT,
SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT,
SUM(ACTUAL_AMOUNT) AS ACTUAL_AMOUNT
FROM ORDER
WHERE ORDER_STATUS IN (‘PAID’, ‘SHIPPED’, ‘COMPLETED’)
GROUP BY DATE(CREATE_TIME), STORE_ID, ORDER_TYPE
) DATA INITIALLY DEFERRED REFRESH DEFERRED
ORGANIZE BY COLUMN;

REFRESH TABLE MQT_SALES_DAILY;

# 商品销售分析
SELECT
p.PRODUCT_ID,
p.PRODUCT_NAME,
c.CATEGORY_NAME,
b.BRAND_NAME,
COUNT(*) AS SALE_COUNT,
SUM(d.QUANTITY) AS TOTAL_QUANTITY,
SUM(d.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM ORDER_DETAIL d
INNER JOIN ORDER o ON d.ORDER_ID = o.ORDER_ID
INNER JOIN PRODUCT p ON d.PRODUCT_ID = p.PRODUCT_ID
LEFT JOIN CATEGORY c ON p.CATEGORY_ID = c.CATEGORY_ID
LEFT JOIN BRAND b ON p.BRAND_ID = b.BRAND_ID
WHERE o.CREATE_TIME BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
AND o.ORDER_STATUS IN (‘PAID’, ‘SHIPPED’, ‘COMPLETED’)
GROUP BY p.PRODUCT_ID, p.PRODUCT_NAME, c.CATEGORY_NAME, b.BRAND_NAME
ORDER BY TOTAL_AMOUNT DESC
FETCH FIRST 50 ROWS ONLY;

# 会员消费分析
SELECT
m.MEMBER_ID,
m.MEMBER_NAME,
m.PHONE,
ml.LEVEL_NAME,
COUNT(*) AS ORDER_COUNT,
SUM(o.TOTAL_AMOUNT) AS TOTAL_AMOUNT,
SUM(o.ACTUAL_AMOUNT) AS ACTUAL_AMOUNT,
MAX(o.LAST_ORDER_TIME) AS LAST_ORDER_TIME
FROM MEMBER m
LEFT JOIN MEMBER_LEVEL ml ON m.MEMBER_LEVEL_ID = ml.LEVEL_ID
LEFT JOIN ORDER o ON m.MEMBER_ID = o.MEMBER_ID
WHERE o.CREATE_TIME BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
GROUP BY m.MEMBER_ID, m.MEMBER_NAME, m.PHONE, ml.LEVEL_NAME
ORDER BY TOTAL_AMOUNT DESC
FETCH FIRST 100 ROWS ONLY;