2. SQL 基础回顾：核心语法

在进入模型解释性分析之前，必须牢固掌握 SQL 的核心数据操作语法。本章将系统回顾 SELECT、WHERE、JOIN、GROUP BY、HAVING、ORDER BY、LIMIT 这七类最常用的子句，并重点说明它们在特征工程、数据清洗和模型输入准备中的典型用法。

2.1 语法执行逻辑与书写顺序

SQL 语句的书写顺序与执行顺序并不相同。理解执行顺序是优化查询和调试错误的关键。

书写顺序 子句 执行顺序 作用阶段
1 SELECT 5 投影最终列
2 FROM 1 确定数据源
3 JOIN 2 关联表
4 WHERE 3 行级过滤
5 GROUP BY 4 分组聚合
6 HAVING 6 组级过滤
7 ORDER BY 7 排序
8 LIMIT 8 限制行数
执行顺序记忆口诀： FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT。WHERE 无法直接引用 SELECT 中的别名，因为 WHERE 在 SELECT 之前执行。

2.2 SELECT 与 WHERE：特征筛选与行过滤

SELECT 用于指定需要返回的列，支持列名、表达式、聚合函数和别名。在模型解释性分析中，SELECT 常用于提取特征列或构造衍生特征。

-- 基础特征提取
SELECT 
    customer_id,
    age,
    income / 1000 AS income_k,
    CASE WHEN credit_score > 700 THEN 'good' ELSE 'bad' END AS credit_level
FROM customers;

WHERE 在行级别过滤数据，常用于剔除缺失值、异常值或选择特定时间窗口的数据。

-- 过滤缺失值和异常值
SELECT *
FROM loan_applications
WHERE loan_amount IS NOT NULL
  AND loan_amount > 0
  AND application_date >= '2024-01-01';
常见陷阱： WHERE 中不能使用聚合函数（如 AVG、SUM）。若需基于聚合结果过滤，必须使用 HAVING。

2.3 JOIN：多表特征关联

模型特征往往分散在多个表中。JOIN 是整合特征的核心手段。最常用的 JOIN 类型包括 INNER JOIN、LEFT JOIN 和 RIGHT JOIN。

JOIN 类型 返回行 典型场景
INNER JOIN 两表匹配的行 只保留有完整特征记录的样本
LEFT JOIN 左表全部 + 右表匹配行 保留所有主表样本，缺失特征用 NULL 填充
RIGHT JOIN 右表全部 + 左表匹配行 较少使用，通常可用 LEFT JOIN 改写 
-- 将用户基本信息与交易行为特征关联
SELECT 
    u.user_id,
    u.age,
    u.gender,
    t.total_transactions,
    t.avg_amount
FROM users u
LEFT JOIN (
    SELECT 
        user_id,
        COUNT(*) AS total_transactions,
        AVG(amount) AS avg_amount
    FROM transactions
    GROUP BY user_id
) t ON u.user_id = t.user_id;
模型特征整合最佳实践： 优先使用 LEFT JOIN 保留主表所有样本，避免因 JOIN 导致样本量意外减少。对于缺失的特征值，可在后续步骤中决定填充策略（均值、中位数或模型预测）。

2.4 GROUP BY 与 HAVING：特征聚合与组级过滤

GROUP BY 将数据按一个或多个列分组，配合聚合函数（COUNT、SUM、AVG、MAX、MIN）生成统计特征。这是构造用户画像、行为聚合特征的基础。

-- 按用户分组，生成行为聚合特征
SELECT 
    user_id,
    COUNT(order_id) AS order_count,
    SUM(amount) AS total_spend,
    AVG(amount) AS avg_order_value,
    MAX(order_date) AS last_order_date,
    MIN(order_date) AS first_order_date
FROM orders
GROUP BY user_id;

HAVING 用于过滤分组后的结果，作用类似于 WHERE 但作用于组级别。HAVING 可以使用聚合函数，而 WHERE 不能。

-- 只保留活跃用户（订单数 > 5 且总消费 > 1000）
SELECT 
    user_id,
    COUNT(order_id) AS order_count,
    SUM(amount) AS total_spend
FROM orders
GROUP BY user_id
HAVING COUNT(order_id) > 5 
   AND SUM(amount) > 1000;
性能提示： 尽量在 WHERE 中过滤掉不需要的行，减少 GROUP BY 处理的数据量。HAVING 只用于无法用 WHERE 表达的聚合条件。

2.5 ORDER BY 与 LIMIT：排序与样本截取

ORDER BY 按指定列排序，默认升序（ASC），可指定降序（DESC）。在模型分析中常用于查看特征分布、识别异常值或提取 Top-N 样本。

LIMIT 限制返回的行数，常用于快速预览数据、构建小批量测试集或提取特定排名的样本。

-- 查看消费最高的 10 个用户
SELECT 
    user_id,
    total_spend
FROM user_aggregated
ORDER BY total_spend DESC
LIMIT 10;

-- 随机抽取 1000 条样本用于快速模型测试
SELECT *
FROM features
ORDER BY RANDOM()
LIMIT 1000;
注意： 不同的数据库系统对 LIMIT 的语法略有差异。MySQL/PostgreSQL 使用 LIMIT，SQL Server 使用 TOP，Oracle 使用 ROWNUM 或 FETCH FIRST。本课程以 PostgreSQL 语法为准。

2.6 核心语法综合流程

下图展示了从原始数据到模型特征矩阵的完整 SQL 处理流程，涵盖了本章回顾的所有核心语法。

SQL 核心语法综合流程：从原始数据到特征矩阵 原始表 (FROM) 行过滤 (WHERE) 表关联 (JOIN) 分组聚合 (GROUP BY) 组过滤 (HAVING) 列投影 (SELECT) 排序 (ORDER BY) 限制行数 (LIMIT) 模型特征矩阵 (最终输出) 执行顺序说明 ① FROM → ② JOIN ③ WHERE → ④ GROUP BY ⑤ HAVING → ⑥ SELECT ⑦ ORDER BY → ⑧ LIMIT

2.7 模型分析中的常见 SQL 模式

以下列表总结了在模型解释性分析中最常用的 SQL 模式，可直接应用于特征工程和数据分析任务。

  • 缺失值检测： SELECT col, COUNT(*) - COUNT(col) AS missing_count FROM table GROUP BY col;
  • 特征分布统计： SELECT col, COUNT(*), AVG(col), STDDEV(col), MIN(col), PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) AS median FROM table GROUP BY col;
  • 时间窗口特征： SELECT user_id, SUM(CASE WHEN date >= CURRENT_DATE - INTERVAL '30 days' THEN amount ELSE 0 END) AS spend_30d FROM transactions GROUP BY user_id;
  • 特征分箱： SELECT CASE WHEN age < 30 THEN 'young' WHEN age BETWEEN 30 AND 50 THEN 'mid' ELSE 'senior' END AS age_group, COUNT(*) FROM users GROUP BY age_group;
  • Top-N 特征提取： SELECT feature, importance FROM feature_importance ORDER BY importance DESC LIMIT 20;
← 上一章 📖 返回目录 下一章 →