SQL 数据分析实战

从查询到分析——数据分析师必备的 SQL 技能

---

🎯 SQL 在数据分析中的角色

环节	用 SQL 做什么
数据提取	从数据库拉取分析所需的数据
数据清洗	WHERE 过滤、CASE WHEN 转换、JOIN 合并
聚合分析	GROUP BY + 窗口函数做描述统计
特征工程	在数据库层构建特征，减少 Python 端计算
数据验证	检查数据质量、发现异常值

原则：尽量在 SQL 层完成数据聚合，把结果集控制在 Python 能轻松处理的规模（< 10 万行）。

---

📐 基础：SELECT + WHERE + JOIN

单表查询

-- 取最近 30 天的订单
SELECT
    order_id,
    user_id,
    amount,
    status,
    created_at
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  AND status != 'cancelled'
ORDER BY created_at DESC
LIMIT 1000;

多表 JOIN

-- 用户 + 订单：每个用户的累计消费
SELECT
    u.user_id,
    u.name,
    u.registration_date,
    COUNT(o.order_id) AS order_count,
    COALESCE(SUM(o.amount), 0) AS total_spent,
    COALESCE(AVG(o.amount), 0) AS avg_order_value
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
                   AND o.status = 'completed'
WHERE u.registration_date >= '2024-01-01'
GROUP BY u.user_id, u.name, u.registration_date
ORDER BY total_spent DESC;

JOIN 类型速查：

JOIN	保留	典型场景
INNER JOIN	两表都有的行	已完成订单 + 用户信息
LEFT JOIN	左表全部 + 右表匹配	所有用户 + 他们的订单（含无订单用户）
RIGHT JOIN	右表全部 + 左表匹配	很少用（用 LEFT JOIN 换顺序即可）
FULL OUTER JOIN	两表全部	对账：两边差集一起看
CROSS JOIN	笛卡尔积	生成所有组合（小心数据量！）

---

🧮 聚合分析：GROUP BY + HAVING

-- 按渠道统计转化漏斗
SELECT
    channel,
    COUNT(DISTINCT user_id) AS visitors,
    COUNT(DISTINCT CASE WHEN event = 'signup' THEN user_id END) AS signups,
    COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) AS buyers,
    ROUND(
        COUNT(DISTINCT CASE WHEN event = 'purchase' THEN user_id END) * 100.0
        / NULLIF(COUNT(DISTINCT user_id), 0), 2
    ) AS conversion_rate
FROM events
WHERE event_date >= '2024-06-01'
GROUP BY channel
HAVING COUNT(DISTINCT user_id) >= 100  -- 过滤小样本
ORDER BY conversion_rate DESC;

---

🪟 窗口函数：SQL 的灵魂

窗口函数 = 在分组内做排序/累计/移动计算，但不折叠行。

ROW_NUMBER / RANK / DENSE_RANK

-- 每个用户的第一笔订单
SELECT *
FROM (
    SELECT
        user_id,
        order_id,
        amount,
        created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
) sub
WHERE rn = 1;

函数	同值处理	示例（分数 90, 90, 85）
ROW_NUMBER()	随机排序	1, 2, 3
RANK()	同值同排名，跳过	1, 1, 3
DENSE_RANK()	同值同排名，不跳过	1, 1, 2

累计和 / 移动平均

SELECT
    order_date,
    daily_revenue,
    -- 累计收入
    SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue,
    -- 7 日移动平均
    AVG(daily_revenue) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7d,
    -- 环比增长率
    ROUND(
        (daily_revenue - LAG(daily_revenue) OVER (ORDER BY order_date))
        * 100.0 / NULLIF(LAG(daily_revenue) OVER (ORDER BY order_date), 0), 2
    ) AS wow_change_pct
FROM daily_stats
ORDER BY order_date;

LAG / LEAD：拿前一行 / 后一行

-- 用户两次购买之间的间隔天数
SELECT
    user_id,
    order_id,
    created_at,
    LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_order_date,
    created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
        AS days_since_last_order
FROM orders
ORDER BY user_id, created_at;

NTILE：分桶

-- 将用户按消费金额分成 5 等份
SELECT
    user_id,
    total_spent,
    NTILE(5) OVER (ORDER BY total_spent DESC) AS spending_quintile
FROM user_stats;

---

📊 实战分析案例

RFM 分析（在 SQL 中完成）

WITH rfm AS (
    SELECT
        user_id,
        -- Recency: 距今天数
        CURRENT_DATE - MAX(order_date) AS recency,
        -- Frequency: 订单数
        COUNT(DISTINCT order_id) AS frequency,
        -- Monetary: 总消费
        SUM(amount) AS monetary
    FROM orders
    WHERE status = 'completed'
    GROUP BY user_id
),
rfm_scored AS (
    SELECT
        user_id,
        recency,
        frequency,
        monetary,
        -- R 分: 最近越好 → 分越高
        NTILE(5) OVER (ORDER BY recency ASC) AS r_score,
        -- F 分: 频率越高 → 分越高
        NTILE(5) OVER (ORDER BY frequency DESC) AS f_score,
        -- M 分: 金额越高 → 分越高
        NTILE(5) OVER (ORDER BY monetary DESC) AS m_score
    FROM rfm
)
SELECT
    user_id,
    r_score, f_score, m_score,
    CONCAT(r_score, f_score, m_score) AS rfm_cell,
    CASE
        WHEN r_score >= 4 AND f_score >= 4 AND m_score >= 4 THEN '高价值'
        WHEN r_score >= 4 AND f_score <= 2 THEN '新客户'
        WHEN r_score <= 2 AND f_score >= 4 THEN '流失风险'
        WHEN r_score <= 2 AND f_score <= 2 THEN '已流失'
        ELSE '普通'
    END AS segment
FROM rfm_scored;

留存分析

-- 周留存：注册后第 N 周还有活跃的用户比例
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('week', registration_date) AS cohort_week
    FROM users
),
active_weeks AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('week', activity_date) AS activity_week
    FROM user_activities
)
SELECT
    c.cohort_week,
    COUNT(DISTINCT c.user_id) AS cohort_size,
    -- Week 0（注册当周）
    ROUND(COUNT(DISTINCT CASE
        WHEN a.activity_week = c.cohort_week THEN c.user_id
    END) * 100.0 / COUNT(DISTINCT c.user_id), 1) AS week_0,
    -- Week 1
    ROUND(COUNT(DISTINCT CASE
        WHEN a.activity_week = c.cohort_week + INTERVAL '7 days'
        THEN c.user_id
    END) * 100.0 / COUNT(DISTINCT c.user_id), 1) AS week_1,
    -- Week 4
    ROUND(COUNT(DISTINCT CASE
        WHEN a.activity_week = c.cohort_week + INTERVAL '28 days'
        THEN c.user_id
    END) * 100.0 / COUNT(DISTINCT c.user_id), 1) AS week_4
FROM user_cohorts c
LEFT JOIN active_weeks a ON c.user_id = a.user_id
GROUP BY c.cohort_week
ORDER BY c.cohort_week;

---

⚡ 性能优化

1. 索引策略

-- 为常用查询条件创建索引
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
CREATE INDEX idx_orders_status ON orders(status) WHERE status = 'completed';

2. EXPLAIN 分析执行计划

EXPLAIN ANALYZE
SELECT ... FROM orders WHERE ...;
-- 看是否用了索引、是否全表扫描、最慢的步骤是什么

3. 避免常见性能杀手

问题	影响	改进
SELECT *	网络传输 + 内存	只选需要的列
WHERE YEAR(date) = 2024	无法用索引	WHERE date >= '2024-01-01' AND date < '2025-01-01'
WHERE col LIKE '%keyword%'	全表扫描	用全文索引或 Elasticsearch
大表 JOIN 小表	可以接受	小表放右边（或让优化器处理）
子查询代替 JOIN	可能更慢	用 CTE (WITH) + JOIN

4. CTE 让复杂查询可读

-- ❌ 嵌套子查询（难读）
SELECT ... FROM (
    SELECT ... FROM (
        SELECT ... FROM orders WHERE ...
    ) t1 JOIN users ON ...
) t2 WHERE ...

-- ✅ CTE（逐层构建）
WITH
recent_orders AS (
    SELECT * FROM orders WHERE created_at >= '2024-01-01'
),
user_order_stats AS (
    SELECT user_id, COUNT(*) as n, SUM(amount) as total
    FROM recent_orders
    GROUP BY user_id
)
SELECT u.*, s.n, s.total
FROM users u
LEFT JOIN user_order_stats s ON u.user_id = s.user_id;

---

🔄 SQL ↔ Python 协作

import pandas as pd
from sqlalchemy import create_engine

# 连接数据库
engine = create_engine('postgresql://user:pass@localhost:5432/db')

# SQL → DataFrame
df = pd.read_sql("""
    SELECT user_id, COUNT(*) as order_count, SUM(amount) as total
    FROM orders
    WHERE created_at >= '2024-01-01'
    GROUP BY user_id
""", engine)

# DataFrame → SQL
df.to_sql('user_agg', engine, if_exists='replace', index=False)

# 大查询分批取
for chunk in pd.read_sql("SELECT * FROM large_table", engine, chunksize=50000):
    process(chunk)

---

🔗 相关资源

• SQL 资源合集 — 进阶教程 + 面试题
• Pandas 实战 — SQL 到 Pandas 的对照
• 数据工程资源 — dbt、Airflow 等工程工具