🏅 SQL实战案例大全 - 从基础到高级的业务场景

🏢 业务场景分类

📊 电商平台场景

• 用户行为分析：用户生命周期、购买习惯、流失预警
• 商品运营：销量分析、库存管理、价格策略
• 营销分析：促销效果、用户画像、推荐系统

💰 金融业务场景

• 风控分析：异常交易检测、信用评估、风险预警
• 业务指标：资产质量、收益分析、客户价值
• 合规报告：监管指标、审计报告、风险敞口

🎓 教育培训场景

• 学习分析：学习进度、成绩分析、课程效果
• 运营指标：用户留存、付费转化、活跃度分析

🎯 初级案例：电商基础分析

案例1：用户消费行为分析

业务背景：电商平台需要分析用户的消费行为，识别高价值客户并制定差异化营销策略。

这个案例综合运用了06-SELECT语句结构|SELECT语句结构 和 15-JOIN连接|JOIN连接 和 12-GROUP BY分组|GROUP BY分组和19-窗口函数|窗口函数等核心技能。

数据表结构：

-- 用户表
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    username VARCHAR(50),
    email VARCHAR(100),
    registration_date DATE,
    age INT,
    city VARCHAR(50),
    gender ENUM('M', 'F', 'O')
);

-- 订单表
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    order_date DATE,
    total_amount DECIMAL(10,2),
    status VARCHAR(20),
    payment_method VARCHAR(20)
);

-- 订单明细表
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    unit_price DECIMAL(10,2)
);

-- 商品表
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2),
    brand VARCHAR(50)
);

分析需求：

1. 用户价值分层（RFM分析）
2. 复购率分析
3. 客户流失预警
4. 商品关联分析

SQL实现：

-- RFM分析：R-最近消费时间，F-消费频次，M-消费金额
-- 这里使用了CTE(通用表表达式)，详见现代SQL特性与数据库差异
WITH user_rfm AS (
    SELECT 
        u.user_id,
        u.username,
        -- R: Recency (最近消费距今天数)，使用日期函数
        DATEDIFF(CURDATE(), MAX(o.order_date)) AS recency_days,
        -- F: Frequency (消费频次)，使用聚合函数详解
        COUNT(DISTINCT o.order_id) AS frequency,
        -- M: Monetary (消费金额)，使用聚合函数详解
        SUM(o.total_amount) AS monetary
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id  -- 使用JOIN连接
    WHERE o.status = 'completed'  -- 使用WHERE条件筛选
    GROUP BY u.user_id, u.username  -- 使用GROUP BY分组
),
rfm_scores AS (
    SELECT 
        *,
        -- 计算RFM分数 (1-5分，5分最好)
        CASE 
            WHEN recency_days <= 30 THEN 5
            WHEN recency_days <= 60 THEN 4
            WHEN recency_days <= 90 THEN 3
            WHEN recency_days <= 180 THEN 2
            ELSE 1
        END AS r_score,
        
        CASE 
            WHEN frequency >= 10 THEN 5
            WHEN frequency >= 6 THEN 4
            WHEN frequency >= 3 THEN 3
            WHEN frequency >= 2 THEN 2
            ELSE 1
        END AS f_score,
        
        CASE 
            WHEN monetary >= 5000 THEN 5
            WHEN monetary >= 2000 THEN 4
            WHEN monetary >= 1000 THEN 3
            WHEN monetary >= 500 THEN 2
            ELSE 1
        END AS m_score
    FROM user_rfm
)
SELECT 
    user_id,
    username,
    recency_days,
    frequency,
    monetary,
    r_score,
    f_score,
    m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_segment,
    CASE 
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN '冠军客户'
        WHEN r_score >= 3 AND f_score >= 3 AND m_score >= 3 THEN '忠诚客户'
        WHEN r_score >= 4 AND f_score <= 2 THEN '新客户'
        WHEN r_score <= 2 AND f_score >= 3 THEN '流失风险客户'
        WHEN r_score <= 2 AND f_score <= 2 AND m_score >= 3 THEN '挽回客户'
        ELSE '普通客户'
    END AS customer_segment
FROM rfm_scores
ORDER BY monetary DESC;

-- 商品销售趋势和季节性分析
SELECT 
    p.category,
    p.product_name,
    DATE_FORMAT(o.order_date, '%Y-%m') AS sales_month,
    SUM(oi.quantity) AS total_quantity,
    SUM(oi.quantity * oi.unit_price) AS total_revenue,
    COUNT(DISTINCT o.user_id) AS unique_customers,
    AVG(oi.unit_price) AS avg_price,
    
    -- 计算环比增长率，使用窗口函数
    LAG(SUM(oi.quantity * oi.unit_price)) OVER (
        PARTITION BY p.product_id 
        ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')
    ) AS prev_month_revenue,
    
    ROUND(
        (SUM(oi.quantity * oi.unit_price) - 
         LAG(SUM(oi.quantity * oi.unit_price)) OVER (
             PARTITION BY p.product_id 
             ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')
         )) / LAG(SUM(oi.quantity * oi.unit_price)) OVER (
             PARTITION BY p.product_id 
             ORDER BY DATE_FORMAT(o.order_date, '%Y-%m')
         ) * 100, 2
    ) AS revenue_growth_rate
    
FROM products p
INNER JOIN order_items oi ON p.product_id = oi.product_id
INNER JOIN orders o ON oi.order_id = o.order_id
WHERE o.status = 'completed'
    AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY p.category, p.product_name, p.product_id, DATE_FORMAT(o.order_date, '%Y-%m')
ORDER BY p.category, sales_month, total_revenue DESC;

🚀 中级案例：金融风控分析

案例2：信用卡异常交易检测

业务背景：银行需要实时检测信用卡异常交易，防范欺诈风险。

数据表结构：

-- 信用卡表
CREATE TABLE credit_cards (
    card_id VARCHAR(20) PRIMARY KEY,
    customer_id INT,
    card_type VARCHAR(20),
    credit_limit DECIMAL(10,2),
    issued_date DATE
);

-- 交易表
CREATE TABLE transactions (
    transaction_id VARCHAR(30) PRIMARY KEY,
    card_id VARCHAR(20),
    transaction_date DATETIME,
    amount DECIMAL(10,2),
    merchant_category VARCHAR(50),
    merchant_location VARCHAR(100),
    transaction_type VARCHAR(20),
    status VARCHAR(20)
);

-- 客户表
CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    phone VARCHAR(20),
    email VARCHAR(100),
    address VARCHAR(200),
    risk_level VARCHAR(10)
);

分析需求：

1. 异常交易模式识别
2. 欺诈风险评分
3. 实时风险预警
4. 客户行为画像

SQL实现：

-- 多维度异常交易检测
WITH customer_behavior AS (
    -- 分析客户历史交易行为
    SELECT 
        c.customer_id,
        cc.card_id,
        AVG(t.amount) AS avg_transaction_amount,
        STDDEV(t.amount) AS stddev_transaction_amount,
        COUNT(*) AS total_transactions,
        COUNT(DISTINCT DATE(t.transaction_date)) AS active_days,
        COUNT(DISTINCT t.merchant_category) AS unique_categories,
        AVG(TIME_TO_SEC(TIME(t.transaction_date))) AS avg_transaction_time_seconds,
        
        -- 常用交易地点
        (SELECT merchant_location 
         FROM transactions t2 
         WHERE t2.card_id = cc.card_id 
         GROUP BY merchant_location 
         ORDER BY COUNT(*) DESC 
         LIMIT 1) AS most_frequent_location,
         
        -- 最常用商户类型
        (SELECT merchant_category 
         FROM transactions t3 
         WHERE t3.card_id = cc.card_id 
         GROUP BY merchant_category 
         ORDER BY COUNT(*) DESC 
         LIMIT 1) AS most_frequent_category
         
    FROM customers c
    INNER JOIN credit_cards cc ON c.customer_id = cc.customer_id
    INNER JOIN transactions t ON cc.card_id = t.card_id
    WHERE t.transaction_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
        AND t.status = 'completed'
    GROUP BY c.customer_id, cc.card_id
),
current_transactions AS (
    -- 当天交易数据
    SELECT 
        t.*,
        cb.avg_transaction_amount,
        cb.stddev_transaction_amount,
        cb.most_frequent_location,
        cb.most_frequent_category,
        cb.avg_transaction_time_seconds
    FROM transactions t
    INNER JOIN customer_behavior cb ON t.card_id = cb.card_id
    WHERE DATE(t.transaction_date) = CURDATE()
),
risk_analysis AS (
    SELECT 
        *,
        -- 风险指标计算
        CASE 
            WHEN amount > (avg_transaction_amount + 3 * stddev_transaction_amount) THEN 3
            WHEN amount > (avg_transaction_amount + 2 * stddev_transaction_amount) THEN 2
            WHEN amount > (avg_transaction_amount + stddev_transaction_amount) THEN 1
            ELSE 0
        END AS amount_risk_score,
        
        CASE 
            WHEN merchant_location != most_frequent_location THEN 2
            ELSE 0
        END AS location_risk_score,
        
        CASE 
            WHEN merchant_category != most_frequent_category THEN 1
            ELSE 0
        END AS category_risk_score,
        
        CASE 
            WHEN ABS(TIME_TO_SEC(TIME(transaction_date)) - avg_transaction_time_seconds) > 14400 THEN 2
            WHEN ABS(TIME_TO_SEC(TIME(transaction_date)) - avg_transaction_time_seconds) > 7200 THEN 1
            ELSE 0
        END AS time_risk_score,
        
        -- 当日交易频次检查
        (SELECT COUNT(*) 
         FROM transactions t2 
         WHERE t2.card_id = current_transactions.card_id 
            AND DATE(t2.transaction_date) = DATE(current_transactions.transaction_date)
            AND t2.transaction_date <= current_transactions.transaction_date) AS transactions_today
    FROM current_transactions
)
SELECT 
    transaction_id,
    card_id,
    transaction_date,
    amount,
    merchant_category,
    merchant_location,
    
    -- 综合风险评分
    (amount_risk_score + location_risk_score + category_risk_score + time_risk_score +
     CASE WHEN transactions_today > 10 THEN 3 WHEN transactions_today > 5 THEN 1 ELSE 0 END
    ) AS total_risk_score,
    
    -- 风险等级
    CASE 
        WHEN (amount_risk_score + location_risk_score + category_risk_score + time_risk_score +
              CASE WHEN transactions_today > 10 THEN 3 WHEN transactions_today > 5 THEN 1 ELSE 0 END
             ) >= 6 THEN '高风险'
        WHEN (amount_risk_score + location_risk_score + category_risk_score + time_risk_score +
              CASE WHEN transactions_today > 10 THEN 3 WHEN transactions_today > 5 THEN 1 ELSE 0 END
             ) >= 3 THEN '中风险'
        ELSE '低风险'
    END AS risk_level,
    
    -- 风险原因
    CONCAT_WS(', ',
        CASE WHEN amount_risk_score > 0 THEN '异常金额' END,
        CASE WHEN location_risk_score > 0 THEN '异常地点' END,
        CASE WHEN category_risk_score > 0 THEN '异常商户类型' END,
        CASE WHEN time_risk_score > 0 THEN '异常时间' END,
        CASE WHEN transactions_today > 10 THEN '频繁交易' END
    ) AS risk_reasons
    
FROM risk_analysis
WHERE (amount_risk_score + location_risk_score + category_risk_score + time_risk_score +
       CASE WHEN transactions_today > 10 THEN 3 WHEN transactions_today > 5 THEN 1 ELSE 0 END
      ) > 0
ORDER BY total_risk_score DESC, transaction_date DESC;

🏆 高级案例：多维度业务分析

案例3：教育平台学习效果分析

业务背景：在线教育平台需要分析学习效果，优化课程内容和推荐算法。

数据表结构：

-- 学生表
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    email VARCHAR(100),
    registration_date DATE,
    age INT,
    education_level VARCHAR(20)
);

-- 课程表
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(200),
    category VARCHAR(50),
    difficulty_level INT, -- 1-5级
    duration_hours INT,
    price DECIMAL(8,2)
);

-- 学习记录表
CREATE TABLE learning_sessions (
    session_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    session_date DATETIME,
    duration_minutes INT,
    completion_rate DECIMAL(5,2), -- 0-100%
    quiz_score DECIMAL(5,2) -- 0-100分
);

-- 考试成绩表
CREATE TABLE exam_results (
    exam_id INT PRIMARY KEY,
    student_id INT,
    course_id INT,
    exam_date DATE,
    score DECIMAL(5,2),
    total_questions INT,
    correct_answers INT
);

分析需求：

1. 学习效果评估
2. 课程质量分析
3. 学生学习路径优化
4. 个性化推荐算法

SQL实现：

-- 学生学习效果综合分析
WITH student_learning_stats AS (
    SELECT 
        s.student_id,
        s.student_name,
        s.education_level,
        COUNT(DISTINCT ls.course_id) AS courses_enrolled,
        COUNT(DISTINCT CASE WHEN ls.completion_rate >= 80 THEN ls.course_id END) AS courses_completed,
        AVG(ls.duration_minutes) AS avg_session_duration,
        SUM(ls.duration_minutes) AS total_study_time,
        AVG(ls.completion_rate) AS avg_completion_rate,
        AVG(ls.quiz_score) AS avg_quiz_score,
        COUNT(ls.session_id) AS total_sessions,
        
        -- 学习活跃度（最近30天）
        COUNT(CASE WHEN ls.session_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
                   THEN ls.session_id END) AS recent_sessions,
        
        -- 学习规律分析
        COUNT(DISTINCT DATE(ls.session_date)) AS study_days,
        DATEDIFF(MAX(DATE(ls.session_date)), MIN(DATE(ls.session_date))) + 1 AS study_span_days
        
    FROM students s
    LEFT JOIN learning_sessions ls ON s.student_id = ls.student_id
    WHERE s.registration_date >= DATE_SUB(CURDATE(), INTERVAL 180 DAY)
    GROUP BY s.student_id, s.student_name, s.education_level
),
course_performance AS (
    SELECT 
        c.course_id,
        c.course_name,
        c.category,
        c.difficulty_level,
        COUNT(DISTINCT ls.student_id) AS enrolled_students,
        AVG(ls.completion_rate) AS avg_completion_rate,
        AVG(ls.quiz_score) AS avg_quiz_score,
        AVG(er.score) AS avg_exam_score,
        
        -- 课程通过率
        COUNT(DISTINCT CASE WHEN er.score >= 60 THEN ls.student_id END) * 100.0 / 
        COUNT(DISTINCT ls.student_id) AS pass_rate,
        
        -- 课程表现的示例分级（基于完成率和分数，不等同于学生满意度）
        CASE 
            WHEN AVG(ls.completion_rate) >= 80 AND AVG(er.score) >= 75 THEN '优秀'
            WHEN AVG(ls.completion_rate) >= 60 AND AVG(er.score) >= 65 THEN '良好'
            WHEN AVG(ls.completion_rate) >= 40 AND AVG(er.score) >= 50 THEN '一般'
            ELSE '需改进'
        END AS course_quality
        
    FROM courses c
    LEFT JOIN learning_sessions ls ON c.course_id = ls.course_id
    LEFT JOIN exam_results er ON c.course_id = er.course_id AND ls.student_id = er.student_id
    GROUP BY c.course_id, c.course_name, c.category, c.difficulty_level
),
learning_path_analysis AS (
    SELECT 
        ls.student_id,
        c.category,
        AVG(ls.completion_rate) AS category_completion_rate,
        AVG(ls.quiz_score) AS category_quiz_score,
        COUNT(DISTINCT ls.course_id) AS category_courses,
        
        -- 计算学习路径的合理性
        AVG(c.difficulty_level) AS avg_difficulty,
        MIN(c.difficulty_level) AS min_difficulty,
        MAX(c.difficulty_level) AS max_difficulty,
        
        -- 学习进度一致性
        STDDEV(ls.completion_rate) AS completion_rate_stddev
        
    FROM learning_sessions ls
    INNER JOIN courses c ON ls.course_id = c.course_id
    GROUP BY ls.student_id, c.category
)
-- 综合分析结果
SELECT 
    sls.student_id,
    sls.student_name,
    sls.education_level,
    sls.courses_enrolled,
    sls.courses_completed,
    ROUND(sls.courses_completed * 100.0 / NULLIF(sls.courses_enrolled, 0), 2) AS completion_percentage,
    
    -- 学习效率评估
    ROUND(sls.avg_quiz_score, 2) AS avg_quiz_score,
    ROUND(sls.total_study_time / 60.0, 2) AS total_study_hours,
    ROUND(sls.avg_session_duration, 2) AS avg_session_minutes,
    
    -- 学习活跃度
    CASE 
        WHEN sls.recent_sessions >= 20 THEN '高活跃'
        WHEN sls.recent_sessions >= 10 THEN '中活跃'
        WHEN sls.recent_sessions >= 5 THEN '低活跃'
        ELSE '不活跃'
    END AS activity_level,
    
    -- 学习规律性
    ROUND(sls.study_days * 100.0 / NULLIF(sls.study_span_days, 0), 2) AS study_consistency,
    
    -- 学习建议
    CASE 
        WHEN sls.avg_completion_rate < 50 THEN '建议降低课程难度或增加学习时间'
        WHEN sls.avg_quiz_score < 60 THEN '建议加强基础知识复习'
        WHEN sls.recent_sessions < 5 THEN '建议提高学习频率'
        WHEN sls.courses_completed * 100.0 / NULLIF(sls.courses_enrolled, 0) < 60 THEN '建议专注完成已选课程'
        ELSE '学习状态良好，可考虑挑战更高难度课程'
    END AS learning_suggestion
    
FROM student_learning_stats sls
WHERE sls.courses_enrolled > 0
ORDER BY sls.avg_quiz_score DESC, sls.courses_completed DESC;

📊 实战练习与思考

练习1：电商客户流失预测 ⭐⭐⭐

场景描述： 某电商平台发现客户流失率较高，需要建立预警机制。

要求：

1. 定义客户流失标准（如90天未下单）
2. 分析流失客户的特征
3. 识别有流失风险的客户
4. 提出挽回策略建议

-- 客户流失分析
WITH customer_last_activity AS (
    SELECT 
        u.user_id,
        u.username,
        u.registration_date,
        MAX(o.order_date) AS last_order_date,
        COUNT(o.order_id) AS total_orders,
        SUM(o.total_amount) AS total_spent,
        AVG(o.total_amount) AS avg_order_value,
        DATEDIFF(CURDATE(), MAX(o.order_date)) AS days_since_last_order,
        
        -- 客户类型
        CASE 
            WHEN MAX(o.order_date) IS NULL THEN '从未下单'
            WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) > 90 THEN '已流失'
            WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) > 60 THEN '流失风险'
            WHEN DATEDIFF(CURDATE(), MAX(o.order_date)) > 30 THEN '需关注'
            ELSE '活跃'
        END AS customer_status
        
    FROM users u
    LEFT JOIN orders o ON u.user_id = o.user_id
    WHERE u.registration_date <= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY u.user_id, u.username, u.registration_date
)
SELECT 
    customer_status,
    COUNT(*) AS customer_count,
    AVG(total_orders) AS avg_orders,
    AVG(total_spent) AS avg_spent,
    AVG(days_since_last_order) AS avg_days_inactive
FROM customer_last_activity
GROUP BY customer_status
ORDER BY FIELD(customer_status, '活跃', '需关注', '流失风险', '已流失', '从未下单');

练习2：银行信贷风险评估 ⭐⭐⭐⭐

场景描述： 银行需要评估贷款申请人的信用风险，建立评分模型。

要求：

1. 分析历史贷款数据
2. 识别风险因子
3. 建立信用评分模型
4. 预测违约概率

练习3：医疗数据分析 ⭐⭐⭐⭐⭐

场景描述： 医院需要分析患者治疗效果，优化医疗资源配置。

要求：

1. 分析不同治疗方案的效果
2. 识别高风险患者群体
3. 优化医生排班和资源分配
4. 预测治疗成本和时间

🚦 实战经验总结

💡 最佳实践

⚠️ 常见陷阱

📈 进阶方向

• 大数据分析：学习Spark SQL、Presto等大数据查询引擎
• 实时分析：掌握流处理SQL（Flink 和 SQL 和 Kafka Streams）
• 机器学习：SQL结合机器学习（SQL Server ML、PostgreSQL ML）
• 图数据库：学习图查询语言（Cypher、Gremlin）

🔗 知识连接

学习路径：

• ⬅️ 前置：Hive/窗口函数与 | Presto/JOIN优化与子查询
• ➡️ 后续：PostgreSQL/PostgreSQL索引与性能优化 | BI商业智能系统
• 🔄 相关：专业术语词典/Python | 统计学

实战项目推荐：

• 🎯 统计学 - 完整的电商业务分析
• 🎯 金融数据分析 - 信贷风险评估系统
• 🎯 用户行为分析 - 电商数据分析 - 产品数据分析实践

创建时间：2024-06-29
最后更新：2024-06-29
字数统计：约12000字
阅读时长：80-100分钟

#SQL #实战案例 #数据分析 #业务场景 #电商分析 #金融风控 #教育数据 #最佳实践