一、项目概述

这个项目模拟真实电商场景的数据分析全流程，从底层数据库设计开始，到业务SQL查询、数据可视化，最终落地到用户流失预测的机器学习建模。

技术栈：

• 数据层：MySQL 8.0，8张核心表，完整索引设计

• 查询层：复杂SQL（窗口函数、CTE、GROUPING SETS、递归查询）

• 分析层：Python + pandas + SQLAlchemy + PyMySQL

• 可视化：matplotlib + seaborn

• 建模层：scikit-learn（逻辑回归、随机森林、梯度提升）

• 工程化：GitHub Actions CI，自动启动MySQL服务并运行全量测试

---

二、数据库设计

2.1 Schema设计

参考真实电商系统结构，设计8张核心表：

表名	说明	核心字段	数据量级
users	用户表	user_id, username, gender, city, user_level, register_time	10万+
categories	商品分类	category_id, category_name, parent_id（递归层级）	数百
products	商品表	product_id, category_id, price, stock, status	1万+
orders	订单表	order_id, user_id, order_date, pay_amount, status	100万+
order_items	订单项	order_item_id, order_id, product_id, quantity, price	500万+
payments	支付表	payment_id, order_id, payment_method, amount, pay_time	100万+
reviews	评价表	review_id, order_id, product_id, rating, review_text	50万+
cart	购物车	cart_id, user_id, product_id, quantity	20万+

2.2 索引设计

针对高频查询场景设计索引：

-- 订单表：按用户查询、按日期范围查询
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_date ON orders(order_date);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);
​
-- 订单项：按订单查询、按商品查询
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
​
-- 商品表：按分类查询
CREATE INDEX idx_products_category_id ON products(category_id);
​
-- 评价表：按商品查询评分
CREATE INDEX idx_reviews_product_id ON reviews(product_id);

设计原则：

• 等值查询字段建B+树索引（user_id, category_id）

• 范围查询字段建索引（order_date）

• 联合索引遵循最左前缀原则（user_id, order_date）

• 覆盖索引：对于高频查询，将查询字段都包含在索引中，避免回表

---

三、SQL能力展示

3.1 六大类查询

类别	包含内容	技术要点
销售分析	每日/月度销售额、环比增长、GROUPING SETS、销售漏斗、退款率	聚合、多维度分组、时间序列
用户分析	RFM分群、用户生命周期、复购率、地域分布、等级转化	窗口函数、CTE、自连接
商品分析	热销TOP N、品类贡献度、关联分析、库存预警、评价分析	排名函数、子查询、HAVING
窗口函数	ROW_NUMBER/RANK/DENSE_RANK、累计求和、移动平均、同环比	OVER()、PARTITION BY、ORDER BY
CTE高级查询	递归CTE（分类层级）、多层嵌套CTE、临时表复用	WITH RECURSIVE、WITH链式
性能优化	索引设计、EXPLAIN分析、慢查询优化、覆盖索引、查询改写	EXPLAIN、索引选择性、查询计划

3.2 典型查询示例

月度销售环比增长（CTE + LAG窗口函数）：

WITH monthly_sales AS (
    SELECT
        DATE_FORMAT(order_date, '%Y-%m') AS order_month,
        COUNT(DISTINCT order_id) AS order_count,
        SUM(pay_amount) AS total_revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT
    order_month,
    total_revenue,
    LAG(total_revenue) OVER (ORDER BY order_month) AS prev_month_revenue,
    ROUND(
        (total_revenue - LAG(total_revenue) OVER (ORDER BY order_month))
        / NULLIF(LAG(total_revenue) OVER (ORDER BY order_month), 0) * 100,
        2
    ) AS revenue_growth_rate
FROM monthly_sales
ORDER BY order_month;

要点：

• CTE先做月度聚合，避免重复计算

• LAG() OVER()取上一行的值，计算环比

• NULLIF()处理除零（第一个月没有上月数据）

分组TOP N（ROW_NUMBER窗口函数）：

WITH product_sales AS (
    SELECT
        p.category_id,
        p.product_id,
        p.product_name,
        SUM(oi.quantity * oi.price) AS total_sales,
        ROW_NUMBER() OVER (PARTITION BY p.category_id ORDER BY SUM(oi.quantity * oi.price) DESC) AS rn
    FROM products p
    JOIN order_items oi ON p.product_id = oi.product_id
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.status = 'completed'
    GROUP BY p.category_id, p.product_id, p.product_name
)
SELECT category_id, product_id, product_name, total_sales
FROM product_sales
WHERE rn <= 5
ORDER BY category_id, rn;

ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY total_sales DESC)在每个品类内按销售额排名，外层筛选TOP 5。

递归CTE（商品分类层级）：

WITH RECURSIVE category_tree AS (
    -- 基础查询：一级分类
    SELECT category_id, category_name, parent_id, 1 AS level
    FROM categories WHERE parent_id IS NULL
    UNION ALL
    -- 递归查询：子分类
    SELECT c.category_id, c.category_name, c.parent_id, ct.level + 1
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.category_id
)
SELECT * FROM category_tree ORDER BY level, category_id;

递归CTE由两部分组成：基础查询（锚点）和递归查询（引用CTE自身），用UNION ALL连接。MySQL 8.0开始支持递归CTE。

---

四、业务分析可视化

4.1 月度销售趋势

柱状图展示月度销售额，折线图展示订单数，绿色标注是环比增长率。可以看到：

• 整体呈上升趋势，从1月的120万增长到12月的310万

• 4月和7月有小幅回落（-3.3%和-2.8%），可能与季节性因素有关

• 下半年增长加速，11月和12月增长率分别达13.0%和19.2%，与双11、双12促销吻合

4.2 品类销售占比

左图是饼图，右图是环形图（中心显示总增长率）。数码电子（28%）和服装鞋帽（22%）是最大的两个品类，合计占50%。食品饮料（15%）和家居日用（12%）次之。

从增长角度看，美妆个护（+20%）和运动户外（+18%）增长最快，是潜力品类；而其他品类（-2%）出现下滑，需要关注。

4.3 RFM用户分群

RFM模型通过三个维度对用户分群：

• Recency（R）：最近一次购买距今天数，越小越好

• Frequency（F）：购买频次，越大越好

• Monetary（M）：消费金额，越大越好

图中横轴是R，纵轴是F，点的颜色对应分群：

• 重要价值客户（绿色，n=101）：高频近期购买，是核心用户，需要重点维护

• 一般价值客户（蓝色，n=177）：中频中近期，是基本盘

• 低价值客户（橙色，n=144）：低频远期，需要激活

• 流失客户（红色，n=78）：低频且很久没买，流失风险高

虚线是R=30天和F=5次的分界线，左上区域是优质客户，右下区域是流失风险客户。

---

五、用户流失预测建模

5.1 特征工程

从用户的订单行为中提取9个特征：

特征	说明	计算方式
total_orders	历史订单总数	COUNT(order_id)
total_spend	消费总金额	SUM(pay_amount)
avg_order_value	平均客单价	total_spend / total_orders
max_order_value	最大单笔消费	MAX(pay_amount)
customer_lifespan_days	用户生命周期天数	最后购买日 - 注册日
avg_days_between_orders	平均购买间隔	订单日期间隔均值
user_level	用户等级	users表
gender_encoded	性别编码	Label编码
city_encoded	城市编码	Label编码

标签定义：用户在最近90天内没有购买行为则标记为流失（1），否则为未流失（0）。

5.2 三模型对比

对比逻辑回归、随机森林、梯度提升三个模型：

=== Model Comparison ===
              model  accuracy  precision   recall       f1      auc
Logistic Regression      0.75   0.666667 0.571429 0.615385 0.901099
      Random Forest      0.90   1.000000 0.714286 0.833333 0.972527
  Gradient Boosting      0.85   1.000000 0.571429 0.727273 0.978022
​
Best model: GradientBoostingClassifier (AUC: 0.9780)

梯度提升的AUC最高（0.978），随机森林的准确率最高（0.90）。逻辑回归作为基线模型，AUC 0.901也有不错的表现。

5.3 最佳模型评估

选择梯度提升作为最终模型，完整评估报告：

Accuracy:  0.8500
Precision: 1.0000
Recall:    0.5714
F1 Score:  0.7273
ROC AUC:   0.9780
​
Confusion Matrix:
[[13  0]
 [ 3  4]]

• 精确率100%：模型预测为流失的用户全部真的流失了，没有误报

• 召回率57%：7个真实流失用户中识别出了4个，有3个漏报

• AUC 0.978：模型区分流失和未流失用户的能力很强

在实际业务中，可以根据运营成本调整分类阈值：如果召回流失用户的收益很高（比如发优惠券挽回），可以降低阈值提高召回率；如果误报成本高（比如给未流失用户也发了优惠券），就保持高精确率。

5.4 训练过程分析

训练集准确率持续上升（从0.88到0.955），测试集准确率在40轮左右达到峰值（0.90），之后开始轻微下降，说明出现了轻微过拟合。

实际使用时可以：

• 设置n_estimators=40（早停）

• 降低学习率（如0.05），增加迭代次数

• 增加子采样比例（subsample=0.8）

• 限制树的深度（max_depth=3）

5.5 特征重要性分析

                feature  importance
 customer_lifespan_days    0.443346   ← 最重要，占44.3%
        avg_order_value    0.225468   ← 第二重要，占22.5%
            total_spend    0.088055
avg_days_between_orders    0.074736
             user_level    0.069061
           total_orders    0.037065
        max_order_value    0.036311
           city_encoded    0.014391
         gender_encoded    0.011567

关键发现：

1. 用户生命周期天数是最重要的特征（44.3%），注册很久但最近不活跃的用户流失概率高

2. 平均客单价第二重要（22.5%），高价值用户的流失行为与低价值用户有显著差异

3. 行为特征（生命周期、客单价、消费金额、购买间隔）合计占比超过83%，是预测流失的核心

4. 人口统计特征（性别、城市）合计不到3%，几乎没有预测能力

这说明用户的行为模式比人口属性更能预测流失，运营策略应该基于用户行为而非人口属性来制定。

---

六、业务洞察与建议

基于以上分析，可以得出以下业务建议：

1. 用户分层运营：

   • 重要价值客户（高频近期）：提供专属客服、会员特权，维持满意度

   • 一般价值客户：推送个性化推荐，提升购买频次

   • 低价值客户：发送优惠券激活，提高客单价

   • 流失风险客户：主动触达（短信/APP推送），提供回归优惠

2. 流失预警：

   • 重点关注生命周期长但近期（>30天）未购买的用户

   • 对高客单价用户的流失预警优先级更高

   • 建立自动化预警机制，当用户满足流失特征时自动触发运营动作

3. 品类策略：

   • 数码电子和服装鞋帽是基本盘，保持供应链稳定

   • 美妆个护和运动户外增长快，加大资源投入

   • 下滑品类分析原因，考虑调整选品或促销策略

---

七、工程实现

7.1 数据库连接

使用PyMySQL + SQLAlchemy双引擎：

• PyMySQL：原生SQL执行，适合复杂查询和批量操作

• SQLAlchemy：ORM层，适合数据读取和pandas交互

import pymysql
from sqlalchemy import create_engine
​
# PyMySQL连接
conn = pymysql.connect(host='localhost', user='root', password='xxx', database='ecommerce')
​
# SQLAlchemy连接（用于pandas read_sql）
engine = create_engine('mysql+pymysql://root:xxx@localhost/ecommerce')
df = pd.read_sql('SELECT * FROM orders', engine)

7.2 CI自动化测试

GitHub Actions配置：

1. 启动MySQL 8.0 service（健康检查等待就绪）

2. 安装Python依赖（pymysql, sqlalchemy, pandas, scikit-learn, matplotlib, seaborn）

3. 执行建表脚本（01_create_database, 02_create_tables, 03_create_indexes）

4. 验证所有SQL文件语法（用pymysql执行EXPLAIN）

5. 运行Python单元测试（数据库连接、特征工程、模型训练、评估）

6. flake8代码规范检查

---

八、项目地址

📦 GitHub：https://github.com/CodeHearth-hub/mysql-data-analysis

包含完整SQL脚本、Python源码、测试代码和文档。欢迎Star，有问题欢迎提Issue。