3. 特征工程与SQL：使用SQL进行特征提取、特征聚合、特征变换

在模型可解释性工程中，特征工程的质量直接决定了模型能否“说人话”。SQL 作为数据预处理的核心语言，能够高效完成特征提取、聚合与变换三大任务。本节将聚焦于如何用 SQL 构建可解释的特征，并确保每个特征在后续的 SHAP、LIME 或部分依赖图中都能被清晰追溯。

3.1 特征提取：从原始字段中挖掘可解释信号

特征提取是将原始数据中的非结构化或半结构化信息转化为结构化数值特征的过程。SQL 中的字符串函数、日期函数和条件表达式是主要工具。

函数类别 典型函数 可解释特征示例
字符串提取 SUBSTRING, SPLIT_PART, REGEXP_EXTRACT 从邮箱中提取域名、从地址中提取城市
日期特征 EXTRACT, DATE_PART, DATEDIFF 星期几、是否周末、距上次购买天数
条件标记 CASE WHEN, IF 是否高价值用户、是否夜间交易 
-- 示例：从交易日志中提取可解释特征
SELECT
  user_id,
  EXTRACT(DOW FROM transaction_time) AS day_of_week,          -- 星期几（0=周日）
  CASE WHEN EXTRACT(HOUR FROM transaction_time) BETWEEN 22 AND 23
       OR EXTRACT(HOUR FROM transaction_time) BETWEEN 0 AND 5
       THEN 1 ELSE 0 END AS is_night_transaction,             -- 是否夜间交易
  DATEDIFF(day, last_purchase_date, CURRENT_DATE) AS days_since_last_purchase, -- 距上次购买天数
  SPLIT_PART(email, '@', 2) AS email_domain                   -- 邮箱域名
FROM transactions
WHERE transaction_date >= '2024-01-01';
可解释性要点： 提取的特征应保持业务语义明确。例如 day_of_week 比 dow_numeric 更易被业务方理解；is_night_transaction 作为二值特征，在 SHAP 值中可直接解释为“夜间交易对预测的贡献”。
特征提取流程：从原始字段到可解释特征 原始字段 transaction_time, email SQL 提取函数 EXTRACT / SPLIT_PART 可解释特征 day_of_week, email_domain 可解释性检查清单 1. 特征名称是否包含业务含义？（如 is_night_transaction 优于 flag_1） 2. 特征值域是否可理解？（如 0/1 二值优于连续时间戳） 3. 特征是否可被领域专家直接验证？（如 email_domain 可归类为“个人邮箱/企业邮箱”） 4. 提取逻辑是否可逆？（给定特征值能否反推出原始数据？）

3.2 特征聚合：用 SQL 构建群体统计特征

聚合特征（如用户过去30天的平均消费金额、最近7天的登录次数）是模型中最常见的可解释特征之一。SQL 的 GROUP BY 配合窗口函数可以高效构建这些特征。

3.2.1 基础聚合：GROUP BY + 聚合函数

-- 用户级聚合特征：过去30天行为统计
SELECT
  user_id,
  COUNT(*) AS transaction_count_30d,           -- 交易次数
  AVG(amount) AS avg_amount_30d,               -- 平均金额
  SUM(amount) AS total_amount_30d,             -- 总金额
  MAX(amount) - MIN(amount) AS amount_range_30d, -- 金额波动范围
  COUNT(DISTINCT merchant_category) AS category_diversity_30d -- 消费类目多样性
FROM transactions
WHERE transaction_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id;

3.2.2 滑动窗口聚合：窗口函数 + 分区

窗口聚合可以构建时间序列特征，且不丢失行级粒度，这对时序模型的可解释性至关重要。

-- 滑动窗口聚合：过去7天滚动平均金额
SELECT
  user_id,
  transaction_date,
  amount,
  AVG(amount) OVER (
    PARTITION BY user_id
    ORDER BY transaction_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_avg_amount_7d,                  -- 7日滚动平均
  COUNT(*) OVER (
    PARTITION BY user_id
    ORDER BY transaction_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_count_7d                        -- 7日滚动次数
FROM transactions
ORDER BY user_id, transaction_date;
聚合特征的可解释性陷阱： 聚合窗口大小（如30天 vs 90天）会显著影响特征含义。在模型解释时，必须记录聚合窗口的定义。建议在特征元数据表中存储 feature_name、aggregation_window、aggregation_function 三列，以便 SHAP 分析时追溯。

3.3 特征变换：标准化、分箱与编码

特征变换是将原始特征或聚合特征映射到更适合模型学习的空间。SQL 可以完成大部分变换操作，且变换逻辑透明可审计。

变换类型 SQL 实现方式 可解释性影响
标准化 (Z-score) (value - avg) / stddev 保留原始分布形状，系数可解释为“每增加一个标准差”
Min-Max 归一化 (value - min) / (max - min) 值域固定为 [0,1]，适合比较不同量纲特征
等宽分箱 WIDTH_BUCKET 或 CASE WHEN 将连续特征离散化，便于生成部分依赖图
等频分箱 NTILE 窗口函数 每个箱体样本量相等，避免稀疏箱体
One-Hot 编码 CASE WHEN 多分支 每个类别独立特征，SHAP 值直接对应类别贡献 
-- 特征变换综合示例
WITH stats AS (
  SELECT
    AVG(amount) AS avg_amount,
    STDDEV(amount) AS std_amount,
    MIN(amount) AS min_amount,
    MAX(amount) AS max_amount
  FROM transactions
)
SELECT
  t.user_id,
  t.amount,
  (t.amount - s.avg_amount) / s.std_amount AS amount_zscore,           -- Z-score 标准化
  (t.amount - s.min_amount) / (s.max_amount - s.min_amount) AS amount_normalized, -- Min-Max 归一化
  CASE
    WHEN t.amount < s.avg_amount - s.std_amount THEN 'low'
    WHEN t.amount BETWEEN s.avg_amount - s.std_amount AND s.avg_amount + s.std_amount THEN 'medium'
    ELSE 'high'
  END AS amount_category,                                              -- 业务分箱
  CASE WHEN t.merchant_category = 'grocery' THEN 1 ELSE 0 END AS is_grocery, -- One-Hot 编码
  CASE WHEN t.merchant_category = 'electronics' THEN 1 ELSE 0 END AS is_electronics
FROM transactions t
CROSS JOIN stats s;
特征变换路径与可解释性映射 原始特征 amount: 0~10000 Z-score Min-Max 分箱 One-Hot 可解释特征 amount_zscore: -2.3 ~ +3.1 可解释特征 amount_normalized: 0.00 ~ 1.00 可解释特征 amount_category: low/medium/high 可解释特征 is_grocery: 0/1 关键原则：变换必须是可逆的（或可解释的）。Z-score 和 Min-Max 可逆；分箱和 One-Hot 不可逆，但业务含义更直观。

3.4 特征工程的可解释性元数据管理

为了在模型解释阶段能够追溯每个特征的来源和变换逻辑，建议在 SQL 特征工程流程中同步维护元数据表。

-- 特征元数据表结构示例
CREATE TABLE feature_metadata (
  feature_name VARCHAR(100) PRIMARY KEY,
  source_table VARCHAR(100),
  source_column VARCHAR(100),
  extraction_logic TEXT,           -- 如 "EXTRACT(DOW FROM transaction_time)"
  aggregation_window VARCHAR(50),  -- 如 "30 days", "7 days"
  transformation_type VARCHAR(50), -- 如 "z-score", "one-hot", "binning"
  business_meaning TEXT,           -- 如 "过去30天平均交易金额"
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 插入示例元数据
INSERT INTO feature_metadata VALUES
('day_of_week', 'transactions', 'transaction_time',
 'EXTRACT(DOW FROM transaction_time)', NULL, 'raw',
 '交易发生的星期几（0=周日，6=周六）', CURRENT_TIMESTAMP),
('rolling_avg_amount_7d', 'transactions', 'amount',
 'AVG(amount) OVER (PARTITION BY user_id ORDER BY transaction_date ROWS 6 PRECEDING)',
 '7 days', 'rolling_window',
 '过去7天（含当天）的滚动平均交易金额', CURRENT_TIMESTAMP),
('amount_category', 'transactions', 'amount',
 'CASE WHEN amount < avg-std THEN low ... END', NULL, 'binning',
 '基于均值和标准差的业务分箱：low/medium/high', CURRENT_TIMESTAMP);
最佳实践： 在每次模型迭代时，将特征工程 SQL 脚本和元数据表一同纳入版本控制（如 Git）。当模型解释出现异常特征贡献时，可通过 feature_metadata 快速定位特征来源和变换逻辑，大幅降低排查成本。

3.5 本章小结

  • 特征提取：利用 SQL 字符串/日期函数从原始字段中提取业务语义明确的特征，确保特征名称可读、值域可解释。
  • 特征聚合：GROUP BY 构建静态聚合特征，窗口函数构建滑动窗口特征，聚合窗口大小必须记录在元数据中。
  • 特征变换：标准化、归一化、分箱、One-Hot 编码各有适用场景，变换逻辑必须透明且可审计。
  • 元数据管理：建立 feature_metadata 表，记录每个特征的来源、逻辑、窗口和业务含义，这是模型可解释性的基础设施。
← 上一章 📖 返回目录 下一章 →