3. SQL环境准备：搭建测试用数据库与批次偏移模拟表
在进入PSI计算与监控SQL编写之前，必须先搭建一个可复现的测试环境。本章将指导你完成两项核心任务：创建测试数据库（MySQL / PostgreSQL 二选一）和设计批次偏移模拟表结构。该表将作为后续所有SQL脚本的数据源，其字段设计直接决定了PSI计算的准确性与可追溯性。
3.1 环境选择与数据库创建
本课程同时支持MySQL 8.0+ 和 PostgreSQL 15+，两种数据库在PSI计算逻辑上完全一致，仅在数据类型和窗口函数语法上有细微差异。请根据你的实际环境选择其一执行。
| 对比维度 | MySQL 8.0+ | PostgreSQL 15+ |
|---|---|---|
| 推荐场景 | 已有MySQL基础设施、团队熟悉MySQL | 需要更严格的数据类型、复杂分析场景 |
| 数值精度 | DECIMAL(38,10) 足够 | NUMERIC(38,10) 更标准 |
| 窗口函数 | 支持，语法标准 | 支持，性能更优 |
| JSON支持 | JSON类型 | JSONB类型（推荐） |
推荐选择：如果你是初学者，建议使用 PostgreSQL，其数据类型更严格、错误提示更清晰，有助于培养规范的SQL编写习惯。本课程示例代码将同时标注两种数据库的语法差异。
3.1.1 创建测试数据库
使用以下SQL命令创建专用数据库。数据库名称建议使用 psi_monitor，便于后续章节统一引用。
-- MySQL 版本
CREATE DATABASE IF NOT EXISTS psi_monitor
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE psi_monitor;
-- PostgreSQL 版本
CREATE DATABASE psi_monitor
WITH ENCODING 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8';
\c psi_monitor;3.2 批次偏移模拟表结构设计
批次偏移模拟表是PSI监控的核心数据载体，其设计需满足以下业务需求：
- 按批次记录：每个模型或特征在每次评分时产生一条记录。
- 特征级粒度：支持多特征同时监控，每个特征独立计算PSI。
- 分箱统计：存储每个分箱的实际占比和预期占比，这是PSI计算的基础。
- 可追溯性：能够回溯任意批次、任意特征的偏移情况。
3.2.1 表字段定义
| 字段名 | 数据类型（MySQL） | 数据类型（PostgreSQL） | 说明 |
|---|---|---|---|
batch_id |
VARCHAR(32) | VARCHAR(32) | 批次唯一标识，格式如 20250101 或 BATCH_2025_01_01 |
feature_name |
VARCHAR(128) | VARCHAR(128) | 特征名称，如 age、income_level、credit_score |
feature_value |
VARCHAR(64) | VARCHAR(64) | 分箱标签，如 [0-30)、high、class_A |
actual_count |
DECIMAL(38,10) | NUMERIC(38,10) | 当前批次该分箱的实际样本数（或占比） |
expected_count |
DECIMAL(38,10) | NUMERIC(38,10) | 基准批次该分箱的预期样本数（或占比） |
关键设计决策：
actual_count 和 expected_count 使用高精度数值类型（38,10），而非整数类型。原因有二：一是PSI计算过程中需要频繁计算占比，使用小数可避免整数除法截断；二是某些场景下预期占比可能直接以百分比形式给出，小数类型更灵活。
3.2.2 创建表SQL
-- MySQL 版本
CREATE TABLE IF NOT EXISTS batch_psi_monitor (
batch_id VARCHAR(32) NOT NULL,
feature_name VARCHAR(128) NOT NULL,
feature_value VARCHAR(64) NOT NULL,
actual_count DECIMAL(38,10) NOT NULL DEFAULT 0,
expected_count DECIMAL(38,10) NOT NULL DEFAULT 0,
PRIMARY KEY (batch_id, feature_name, feature_value),
INDEX idx_batch_feature (batch_id, feature_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- PostgreSQL 版本
CREATE TABLE IF NOT EXISTS batch_psi_monitor (
batch_id VARCHAR(32) NOT NULL,
feature_name VARCHAR(128) NOT NULL,
feature_value VARCHAR(64) NOT NULL,
actual_count NUMERIC(38,10) NOT NULL DEFAULT 0,
expected_count NUMERIC(38,10) NOT NULL DEFAULT 0,
PRIMARY KEY (batch_id, feature_name, feature_value)
);
CREATE INDEX idx_batch_feature ON batch_psi_monitor (batch_id, feature_name);3.3 表结构关系与数据流
下图展示了批次偏移模拟表在PSI监控体系中的位置与数据流转关系：
3.4 插入模拟数据
为验证后续SQL脚本的正确性，需要插入一组具有代表性的模拟数据。以下示例包含两个特征（age 和 income_level）、两个批次（基准批次 B_202501 和当前批次 B_202502），每个特征分3个箱。
-- 插入模拟数据（MySQL / PostgreSQL 通用）
INSERT INTO batch_psi_monitor (batch_id, feature_name, feature_value, actual_count, expected_count) VALUES
-- 特征 age，基准批次
('B_202501', 'age', '[0-30)', 3000, 3000),
('B_202501', 'age', '[30-50)', 5000, 5000),
('B_202501', 'age', '[50+)', 2000, 2000),
-- 特征 age，当前批次（发生偏移：年轻群体增加，年长群体减少）
('B_202502', 'age', '[0-30)', 3800, 3000),
('B_202502', 'age', '[30-50)', 4900, 5000),
('B_202502', 'age', '[50+)', 1300, 2000),
-- 特征 income_level，基准批次
('B_202501', 'income_level', 'low', 4000, 4000),
('B_202501', 'income_level', 'medium', 4500, 4500),
('B_202501', 'income_level', 'high', 1500, 1500),
-- 特征 income_level，当前批次（轻微偏移）
('B_202502', 'income_level', 'low', 4200, 4000),
('B_202502', 'income_level', 'medium', 4400, 4500),
('B_202502', 'income_level', 'high', 1400, 1500);
数据设计意图：特征
age 的当前批次中，[0-30) 分箱的 actual_count（3800）明显高于 expected_count（3000），而 [50+) 分箱的 actual_count（1300）明显低于 expected_count（2000），这将导致较高的PSI值，便于后续章节验证偏移检测逻辑。特征 income_level 的偏移幅度较小，用于对比展示正常波动场景。
3.5 验证环境准备
执行以下查询，确认表结构正确且数据已成功插入：
-- 验证表结构
DESC batch_psi_monitor; -- MySQL
\d+ batch_psi_monitor -- PostgreSQL
-- 验证数据总量（预期12行）
SELECT COUNT(*) AS total_records FROM batch_psi_monitor;
-- 查看按批次和特征的汇总
SELECT batch_id, feature_name,
COUNT(*) AS num_bins,
SUM(actual_count) AS total_actual,
SUM(expected_count) AS total_expected
FROM batch_psi_monitor
GROUP BY batch_id, feature_name
ORDER BY batch_id, feature_name;