7.4 案例：销售数据统计分析

      通过前面几节的学习，你已经掌握了聚合函数、分组和筛选的核心技能。现在，让我们将这些知识应用到真实的业务场景中——对一个电商销售数据表进行全面的统计分析。通过这个案例，你将学会如何从原始数据中提取有价值的业务洞察。

一、案例背景与数据准备

假设我们是一家电商公司，需要分析2024年上半年的销售数据。我们有一张 sales 表，记录了每笔订单的详细信息。

创建销售表

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_date DATE NOT NULL,           -- 订单日期
    customer_id INT NOT NULL,            -- 客户ID
    customer_name VARCHAR(50) NOT NULL,  -- 客户姓名
    product_category VARCHAR(50) NOT NULL, -- 产品类别
    product_name VARCHAR(100) NOT NULL,   -- 产品名称
    quantity INT NOT NULL,                -- 销售数量
    unit_price DECIMAL(10,2) NOT NULL,    -- 单价
    total_amount DECIMAL(10,2) NOT NULL,  -- 总金额（quantity * unit_price）
    region VARCHAR(50) NOT NULL           -- 销售区域
);

插入模拟数据

我们插入一些典型数据，涵盖不同月份、产品类别和区域。

INSERT INTO sales (order_date, customer_id, customer_name, product_category, product_name, quantity, unit_price, total_amount, region) VALUES
('2024-01-05', 101, '张三', '电子产品', '智能手机', 2, 2999.00, 5998.00, '华东'),
('2024-01-12', 102, '李四', '家居用品', '沙发', 1, 3999.00, 3999.00, '华南'),
('2024-01-18', 103, '王五', '电子产品', '笔记本电脑', 1, 6999.00, 6999.00, '华东'),
('2024-02-03', 104, '赵六', '服装', '羽绒服', 3, 599.00, 1797.00, '华北'),
('2024-02-15', 101, '张三', '电子产品', '智能手表', 2, 1299.00, 2598.00, '华东'),
('2024-02-20', 105, '钱七', '家居用品', '床垫', 1, 2999.00, 2999.00, '西南'),
('2024-03-02', 106, '孙八', '服装', '运动鞋', 2, 899.00, 1798.00, '华南'),
('2024-03-10', 102, '李四', '电子产品', '平板电脑', 1, 3999.00, 3999.00, '华南'),
('2024-03-22', 103, '王五', '家居用品', '餐桌', 1, 2499.00, 2499.00, '华东'),
('2024-04-04', 104, '赵六', '服装', '牛仔裤', 4, 299.00, 1196.00, '华北'),
('2024-04-15', 105, '钱七', '电子产品', '耳机', 3, 799.00, 2397.00, '西南'),
('2024-04-25', 106, '孙八', '家居用品', '台灯', 2, 199.00, 398.00, '华南'),
('2024-05-06', 101, '张三', '服装', 'T恤', 5, 99.00, 495.00, '华东'),
('2024-05-18', 102, '李四', '电子产品', '显示器', 1, 1899.00, 1899.00, '华南'),
('2024-05-28', 103, '王五', '家居用品', '书架', 1, 899.00, 899.00, '华东'),
('2024-06-07', 104, '赵六', '电子产品', '手机', 2, 2599.00, 5198.00, '华北'),
('2024-06-19', 105, '钱七', '服装', '裙子', 2, 399.00, 798.00, '西南'),
('2024-06-30', 106, '孙八', '家居用品', '收纳盒', 4, 49.00, 196.00, '华南');

二、基础统计：整体销售概览

首先，我们需要了解上半年的整体销售情况。

1. 总销售额、总订单数、总销量

SELECT 
    COUNT(*) AS total_orders,
    SUM(quantity) AS total_quantity,
    SUM(total_amount) AS total_revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM sales;

结果解读：

• 总订单数：18笔

• 总销量：38件商品

• 总销售额：约46,161元

• 平均订单价值：约2,564.5元

2. 每月销售额和订单数

时间维度分析：按月统计

业务上通常需要了解每个月的销售趋势。

SELECT 
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(total_amount) AS monthly_revenue
FROM sales
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY month;

结果：

可以看出1月份销售额最高，可能与新年促销有关。

3. 计算月度环比增长率

我们可以用自连接或窗口函数计算增长率（这里用自连接演示）：

SELECT 
    curr.month,
    curr.revenue,
    ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 2) AS growth_rate
FROM
    (SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_amount) AS revenue
     FROM sales GROUP BY month) curr
LEFT JOIN
    (SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total_amount) AS revenue
     FROM sales GROUP BY month) prev
     ON DATE_ADD(STR_TO_DATE(CONCAT(curr.month, '-01'), '%Y-%m-%d'), INTERVAL -1 MONTH) = STR_TO_DATE(CONCAT(prev.month, '-01'), '%Y-%m-%d')
ORDER BY curr.month;

此查询稍复杂，初学者可跳过，但可作为进阶练习。

4. 各类别的销售额、销量和订单数

产品类别分析

按产品类别统计，找出热销品类。

SELECT 
    product_category,
    COUNT(*) AS order_count,
    SUM(quantity) AS total_quantity,
    SUM(total_amount) AS total_revenue,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM sales
GROUP BY product_category
ORDER BY total_revenue DESC;

结果：

电子产品贡献了超过一半的销售额，客单价也最高。

5. 使用 HAVING 筛选销售额超过 10000 的类别

SELECT 
    product_category,
    SUM(total_amount) AS total_revenue
FROM sales
GROUP BY product_category
HAVING total_revenue > 10000;

只有电子产品（30288）符合条件。

6. 客户消费排行（前3名）

客户分析

找出最有价值的客户（高消费客户）。

SELECT 
    customer_id,
    customer_name,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM sales
GROUP BY customer_id, customer_name
ORDER BY total_spent DESC
LIMIT 3;

结果：

李四和王五的消费额较高。

7. 统计每位客户的平均订单金额，并筛选出平均订单金额高于整体平均值（2564.5）的客户

SELECT 
    customer_name,
    COUNT(*) AS order_count,
    ROUND(AVG(total_amount), 2) AS avg_order_value
FROM sales
GROUP BY customer_name
HAVING AVG(total_amount) > (SELECT AVG(total_amount) FROM sales)
ORDER BY avg_order_value DESC;

8. 各区域销售额占比

不同区域的销售表现。

SELECT 
    region,
    COUNT(*) AS order_count,
    SUM(total_amount) AS region_revenue,
    ROUND(SUM(total_amount) * 100.0 / (SELECT SUM(total_amount) FROM sales), 2) AS revenue_percent
FROM sales
GROUP BY region
ORDER BY region_revenue DESC;

结果：

华东地区贡献最大，西南地区还有提升空间。

9.商品维度分析

SELECT 
    product_name,
    product_category,
    SUM(quantity) AS total_sold,
    SUM(total_amount) AS total_revenue
FROM sales
GROUP BY product_name, product_category
ORDER BY total_sold DESC
LIMIT 5;

结果：

T恤销量最高，但销售额不高；耳机销售额较高。

10. 价格分析：找出单价高于平均单价的产品

先计算平均单价（所有订单的单价平均值）：

SELECT AVG(unit_price) FROM sales;
-- 结果为约 1717.61

然后找出单价高于平均单价的商品及其订单：

SELECT 
    product_name,
    unit_price
FROM sales
GROUP BY product_name, unit_price
HAVING unit_price > (SELECT AVG(unit_price) FROM sales)
ORDER BY unit_price DESC;

11. 每月销售额最高的商品

可以使用子查询或窗口函数。这里用窗口函数（MySQL 8.0+）：

WITH monthly_product AS (
    SELECT 
        DATE_FORMAT(order_date, '%Y-%m') AS month,
        product_name,
        SUM(total_amount) AS product_monthly_revenue,
        ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(order_date, '%Y-%m') ORDER BY SUM(total_amount) DESC) AS rn
    FROM sales
    GROUP BY month, product_name
)
SELECT month, product_name, product_monthly_revenue
FROM monthly_product
WHERE rn = 1
ORDER BY month;

此查询展示了每个月最畅销的商品，对于制定促销策略很有帮助。

三、综合练习

请根据上面的 sales 表，独立完成以下查询：

1. 统计每个季度的销售额和订单数。

2. 找出消费次数最多的客户（下单次数最多）。

3. 计算每个产品类别的平均单价（注意平均单价 = 总金额 / 总销量）。

4. 找出电子产品中销售额占比超过 50% 的商品。

5. 分析华东地区每个月的销售额变化趋势。

这些技能是数据分析的基础，也是后续学习更高级分析（如用户行为分析、RFM模型）的基石。请务必在本地环境中亲手执行这些查询，并尝试修改条件或添加新的维度，加深理解。