Flask + MySQL + ECharts 实现电商用户行为数据分析平台（PV/UV/转化漏斗）

wyouk334

343人浏览 · 2026-08-08 20:17:58

wyouk334 · 2026-08-08 20:17:58 发布

Flask + MySQL + ECharts 电商用户行为数据分析平台

这是一个非常适合作为毕业设计的选题，技术栈主流、数据可视化直观、业务逻辑清晰

📊 核心分析指标

指标	含义	SQL计算方式
PV (Page View)	页面访问量	`COUNT(*)`
UV (Unique Visitor)	独立访客数	`COUNT(DISTINCT user_id)`
转化漏斗	浏览→加购→下单→支付	各阶段人数逐层统计

🗄️ 数据库设计 (MySQL)

-- 用户行为表
CREATE TABLE user_behavior (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id VARCHAR(50),
    item_id INT,
    category_id INT,
    behavior_type ENUM('pv','cart','fav','buy'),
    timestamp DATETIME,
    INDEX idx_user (user_id),
    INDEX idx_time (timestamp)
);

-- 商品表
CREATE TABLE items (
    item_id INT PRIMARY KEY,
    category_id INT,
    price DECIMAL(10,2)
);

🔧 Flask后端核心代码

1. 项目结构

ecommerce_analysis/
├── app.py              # Flask主程序
├── models.py           # 数据库操作
├── templates/
│   ├── index.html      # 仪表盘主页
│   └── funnel.html     # 漏斗图页面
└── static/
    └── js/             # ECharts配置

2. API接口示例 (app.py)

from flask import Flask, jsonify, render_template
from flask_sqlalchemy import SQLAlchemy
from datetime import datetime, timedelta

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:password@localhost/ecommerce'
db = SQLAlchemy(app)

class UserBehavior(db.Model):
    __tablename__ = 'user_behavior'
    id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.String(50))
    behavior_type = db.Column(db.String(20))
    timestamp = db.Column(db.DateTime)

@app.route('/api/pv_uv')
def get_pv_uv():
    """获取每日PV/UV数据"""
    today = datetime.now().date()
    results = db.session.execute("""
        SELECT 
            DATE(timestamp) as date,
            COUNT(*) as pv,
            COUNT(DISTINCT user_id) as uv
        FROM user_behavior
        WHERE timestamp >= :start_date
        GROUP BY DATE(timestamp)
        ORDER BY date
    """, {'start_date': today - timedelta(days=30)}).fetchall()
    
    return jsonify([{
        'date': str(r.date),
        'pv': r.pv,
        'uv': r.uv
    } for r in results])

@app.route('/api/funnel')
def get_funnel():
    """获取转化漏斗数据"""
    stages = ['pv', 'cart', 'fav', 'buy']
    funnel_data = []
    
    for stage in stages:
        count = db.session.execute("""
            SELECT COUNT(DISTINCT user_id) 
            FROM user_behavior 
            WHERE behavior_type = :stage
        """, {'stage': stage}).scalar()
        funnel_data.append({'stage': stage, 'count': count})
    
    return jsonify(funnel_data)

@app.route('/')
def dashboard():
    return render_template('index.html')

if __name__ == '__main__':
    app.run(debug=True)

📈 ECharts前端可视化

折线图 - PV/UV趋势 (templates/index.html)

<!DOCTYPE html>
<html>
<head>
    <script src="https://cdn.jsdelivr.net/npm/echarts@5/dist/echarts.min.js"></script>
</head>
<body>
    <div id="pvuvChart" style="width: 100%; height: 400px;"></div>
    <div id="funnelChart" style="width: 600px; height: 450px;"></div>

    <script>
        // PV/UV折线图
        fetch('/api/pv_uv')
            .then(res => res.json())
            .then(data => {
                const chart = echarts.init(document.getElementById('pvuvChart'));
                chart.setOption({
                    title: { text: '30天PV/UV趋势' },
                    tooltip: { trigger: 'axis' },
                    legend: { data: ['PV', 'UV'] },
                    xAxis: { type: 'category', data: data.map(d => d.date) },
                    yAxis: [
                        { type: 'value', name: 'PV' },
                        { type: 'value', name: 'UV' }
                    ],
                    series: [
                        { name: 'PV', type: 'line', data: data.map(d => d.pv), smooth: true },
                        { name: 'UV', type: 'line', data: data.map(d => d.uv), yAxisIndex: 1, smooth: true }
                    ]
                });
            });

        // 漏斗图
        fetch('/api/funnel')
            .then(res => res.json())
            .then(data => {
                const chart = echarts.init(document.getElementById('funnelChart'));
                chart.setOption({
                    title: { text: '用户转化漏斗' },
                    tooltip: { trigger: 'item', formatter: '{b} : {c}' },
                    series: [{
                        type: 'funnel',
                        left: '10%',
                        top: 60,
                        bottom: 40,
                        width: '80%',
                        min: 0,
                        max: Math.max(...data.map(d => d.count)),
                        minSize: '0%',
                        maxSize: '100%',
                        sort: 'descending',
                        gap: 2,
                        label: { show: true, position: 'inside' },
                        data: data.map(d => ({
                            name: d.stage === 'pv' ? '浏览' :
                                  d.stage === 'cart' ? '加入购物车' :
                                  d.stage === 'fav' ? '收藏' : '购买',
                            value: d.count
                        }))
                    }]
                });
            });
    </script>
</body>
</html>

🚀 扩展功能建议（加分项）

功能模块	实现思路	技术点
实时监控	WebSocket推送最新数据	Flask-SocketIO
用户画像	RFM模型分析用户价值	Pandas聚合计算
热力图	按小时+星期展示活跃度	ECharts热力图
导出报告	生成PDF分析报告	ReportLab/PyPDF2
预测趋势	ARIMA/LSTM预测未来流量	Prophet/Sklearn