综合实战：电商订单查询系统

-- 用户表
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100),
    phone VARCHAR(20),
    register_date DATE,
    level INT DEFAULT 1  -- 用户等级
);

-- 商品表
CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    stock INT DEFAULT 0,
    sales INT DEFAULT 0
);

-- 订单表
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50) UNIQUE,
    user_id INT,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),  -- pending, paid, shipped, completed, cancelled
    created_at DATETIME,
    paid_at DATETIME,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

-- 订单明细表
CREATE TABLE order_items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT,
    product_id INT,
    quantity INT,
    price DECIMAL(10,2),  -- 下单时价格
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

-- 查询用户的所有订单
SELECT u.username,
       o.order_no,
       o.total_amount,
       o.status,
       DATE_FORMAT(o.created_at, '%Y-%m-%d %H:%i') AS order_time
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE u.id = 1
ORDER BY o.created_at DESC;

-- 查询订单明细（包含商品信息）
SELECT o.order_no,
       p.name AS product_name,
       oi.quantity,
       oi.price,
       oi.quantity * oi.price AS subtotal
FROM orders o
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_no = 'ORD20241121000001';

-- 统计每个用户的消费情况
SELECT u.id,
       u.username,
       u.level,
       COUNT(o.id) AS order_count,
       IFNULL(SUM(o.total_amount), 0) AS total_spent,
       ROUND(AVG(o.total_amount), 2) AS avg_order_value,
       MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.username, u.level
ORDER BY total_spent DESC;

-- 销量Top 10商品
SELECT p.name,
       p.category,
       SUM(oi.quantity) AS total_sales,
       SUM(oi.quantity * oi.price) AS total_revenue
FROM products p
INNER JOIN order_items oi ON p.id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'completed'
GROUP BY p.id, p.name, p.category
ORDER BY total_sales DESC
LIMIT 10;

-- 每月销售额趋势
SELECT DATE_FORMAT(created_at, '%Y-%m') AS month,
       COUNT(*) AS order_count,
       COUNT(DISTINCT user_id) AS customer_count,
       SUM(total_amount) AS total_sales,
       ROUND(AVG(total_amount), 2) AS avg_order_value
FROM orders
WHERE status = 'completed'
  AND created_at >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

-- 分析用户首次购买、最近购买、购买频次
SELECT u.username,
       u.register_date,
       MIN(o.created_at) AS first_order_date,
       MAX(o.created_at) AS last_order_date,
       COUNT(o.id) AS order_count,
       TIMESTAMPDIFF(DAY, u.register_date, MIN(o.created_at)) AS days_to_first_order,
       TIMESTAMPDIFF(DAY, MIN(o.created_at), MAX(o.created_at)) AS active_days
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.username, u.register_date
HAVING order_count > 0;

-- 计算复购率（购买2次及以上的用户占比）
SELECT
    COUNT(DISTINCT user_id) AS total_customers,
    COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) AS repeat_customers,
    ROUND(COUNT(DISTINCT CASE WHEN order_count >= 2 THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2) AS repeat_rate
FROM (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
) t;

-- RFM分析：Recency（最近购买）、Frequency（购买频次）、Monetary（消费金额）
SELECT user_id,
       username,
       DATEDIFF(NOW(), last_order_date) AS recency,
       order_count AS frequency,
       total_amount AS monetary,
       CASE
           WHEN DATEDIFF(NOW(), last_order_date) <= 30 AND order_count >= 5 AND total_amount >= 10000 THEN 'VIP客户'
           WHEN DATEDIFF(NOW(), last_order_date) <= 90 AND order_count >= 3 THEN '活跃客户'
           WHEN DATEDIFF(NOW(), last_order_date) > 180 THEN '流失客户'
           ELSE '普通客户'
       END AS customer_segment
FROM (
    SELECT u.id AS user_id,
           u.username,
           MAX(o.created_at) AS last_order_date,
           COUNT(o.id) AS order_count,
           SUM(o.total_amount) AS total_amount
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
    GROUP BY u.id, u.username
) t
ORDER BY monetary DESC;

-- 统计下单未支付的订单（购物车）
SELECT
    COUNT(*) AS cart_count,
    SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_count,
    ROUND(SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2) AS conversion_rate
FROM orders
WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 查找经常一起购买的商品
SELECT p1.name AS product1,
       p2.name AS product2,
       COUNT(*) AS co_purchase_count
FROM order_items oi1
INNER JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
INNER JOIN products p1 ON oi1.product_id = p1.id
INNER JOIN products p2 ON oi2.product_id = p2.id
GROUP BY p1.id, p1.name, p2.id, p2.name
ORDER BY co_purchase_count DESC
LIMIT 10;

-- 库存不足且销售较好的商品
SELECT name,
       category,
       stock,
       sales,
       CASE
           WHEN stock = 0 THEN '缺货'
           WHEN stock < sales * 0.1 THEN '严重不足'
           WHEN stock < sales * 0.3 THEN '库存紧张'
           ELSE '正常'
       END AS stock_status
FROM products
WHERE stock < sales * 0.3
ORDER BY stock_status, sales DESC;

-- 为常用查询字段创建索引
CREATE INDEX idx_user_id ON orders(user_id);
CREATE INDEX idx_status ON orders(status);
CREATE INDEX idx_created_at ON orders(created_at);
CREATE INDEX idx_order_id ON order_items(order_id);
CREATE INDEX idx_product_id ON order_items(product_id);

-- 联合索引
CREATE INDEX idx_status_user_id ON orders(status, user_id);
CREATE INDEX idx_status_created_at ON orders(status, created_at);

-- 用户订单统计视图
CREATE VIEW v_user_order_stats AS
SELECT u.id AS user_id,
       u.username,
       u.level,
       COUNT(o.id) AS order_count,
       IFNULL(SUM(o.total_amount), 0) AS total_spent,
       MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
GROUP BY u.id, u.username, u.level;

-- 使用视图
SELECT * FROM v_user_order_stats WHERE total_spent > 10000;