库内机器学习实战：用SQL在数据库内完成模型训练与预测

1. “数据出库+外部建模”的隐性代价：这一趟数据迁徙到底亏在哪

做数据的人基本都经历过这套流程：业务方提了个需求，要建一个贷款违约预测模型，你打开生产库导出一张几千万行的宽表，CSV 落盘，再想办法搬到建模服务器，用 Python 做数据清洗、特征工程、模型训练，调完参得到一版 pkl 或 joblib 文件，最后把预测概率写回数据库，供业务系统调用。

这套流程看起来顺理成章，但有一个问题被长期忽略了：数据移动本身才是最大的成本。

1.1 数据出库：隐性成本不在“导出”这一步

导数据听起来简单，COPY一条命令就完事。但真实链路远不止如此：数据从 Oracle/GP 导出要分配存储空间，传输到建模环境要走专线或对象存储，到了 Python 端还要做类型转换、字符编码清洗、时间格式统一。这个过程中几千万行的数据量会让网络和磁盘 IO 成为瓶颈，我见过最夸张的一次，导一张 3 亿行的行为表花了将近两个小时，而建模训练本身只有四十分钟。也就是说，整个项目周期里，真正的算法工作只占一小部分，大部分时间都耗在了数据的“搬家”上。

这是第一层成本：时间。

第二层成本是数据新鲜度。从导出快照到模型训练完、再回写结果，中间隔了大几个小时甚至隔夜。对营销响应、实时风控这类场景来说，模型用的是几个钟头甚至一天前的数据，业务效果会明显打折。你辛苦调出来的模型，输入数据已经“过期”了。

第三层成本是合规与安全。很多数据表里的敏感字段按规定是不能原样导出到外部环境的，每次出库都要走审批、脱敏、加密流程。建模的人急着要数据，安全部门死活不放行，两边来回拉扯的事我见过太多。每一轮“数据出库”都意味着暴露面和审计成本的上升。

第四层是工程复杂度。数据搬出去后，特征口径的全责就落在算法工程师手里。业务库的字段变更、预处理逻辑的版本漂移、训练环境和生产环境依赖不一致，这些在跨系统协作时都会被放大。传统流程里，模型上线后回写结果经常出现“字段对不上”“概率分布和训练时不一样”这类问题，根源就是数据在多个系统之间流转时丢失了上下文。

1.2 库内机器学习：让 SQL 直接成为训练指令

“库内机器学习”解决的就是上面一整串问题。它的思路很直接：既然数据已经躺在数据库里，为什么不让模型训练也在数据库里完成？

这里的“库内”不是说在数据库旁边开个 Notebook 再连上去，而是指训练算法以 SQL 函数、UDF 或数据库内置过程的形式运行在数据库引擎内部。像 Apache MADlib、SQL Server Machine Learning Services、Oracle Machine Learning，都是这种思路的代表实现。建模的人写一条 SQL，指定训练表、标签列、特征表达式和模型表名，数据库就直接把训练跑完，模型结果作为一个数据库对象（通常是一张表或一行二进制数据）落到库中。

这就彻底改变了数据的流动方式：特征不用导出，训练不用搬家，预测结果不用回传。数据从头到尾都留在库里，SQL 既是取数语言，也是训练和推理的执行语言。

我个人的体会是，库内机器学习最大的价值不是“炫技”，而是把机器学习的工程链路压缩到了极致。传统流程从提需求到上线预测结果，往往以周为单位；库里建模从建特征视图到模型入库，几个小时就能跑完一轮。更关键的是，特征口径、训练数据、模型参数、预测结果全部落在同一套数据库体系内，审计、回滚、版本管理都能用数据库原生的能力去做。

2. 三条主流技术路线拆解：MADlib、SQL Server ML Services、PL/Python 怎么选

库内 ML 不是某一个产品的专有名词，不同数据库生态下的实现思路差别很大。我建议你在动手前先搞清楚自己所在的环境能走哪条路线，再决定架构和实践方式。

2.1 MADlib：SQL 本身就是算法语言

MADlib 是 Apache 基金会下面的开源机器学习库，最早是 Greenplum 团队和高校合作的项目，现在是 PostgreSQL 和 Greenplum 生态里最主流的库内机器学习方案。它把逻辑回归、线性回归、决策树、随机森林、K-Means、朴素贝叶斯等算法封装成数据库函数，直接跑在数据库引擎内部。

在 Greenplum 这种分布式数据库里，MADlib 的训练函数会自动利用 MPP 架构做并行计算。几千万行训练数据，逻辑回归几分钟就能跑完，因为每个 segment 节点只处理自己那份数据，最后汇总梯度更新参数。这是它和单机 Python 训练最本质的差别：数据不需要汇聚到一台机器，算力跟着数据走。

单机 PostgreSQL 也能装 MADlib，说明文档和编译包都支持主流版本，只是大数据量下没有 MPP 加速，算力上限受限于单机配置。所以我的建议是：如果生产环境已经是 Greenplum，MADlib 是首选；如果只是单机 PG，可以做小数据量的轻量建模，但别指望它能替代专业训练集群。

2.2 SQL Server ML Services：把 Python/R 搬进数据库

微软的路线和 MADlib 完全不同。SQL Server 从 2017 年开始提供 Machine Learning Services，核心思路是在数据库实例内部嵌入 Python 和 R 运行时，通过sp_execute_external_script存过过程把外部脚本送进数据库执行。你可以直接在脚本里用 scikit-learn、xgboost 训练模型，然后把训练好的模型序列化成二进制数据存进数据库表。

这种方案的优点是对 Python 生态的兼容性极好，想用什么算法都行，不受数据库内置函数限制。缺点是训练过程本质上仍然是单进程 Python 在跑，数据库只是提供了一个“安全壳子”，并没有像 MADlib 那样把算子下推到分布式引擎。数据量大了之后，内存和 CPU 依然是瓶颈。

从工程角度看，SQL Server 的做法把“模型入库”这个概念落实得比较具体：模型以VARBINARY(MAX)类型存在表里，用PREDICT函数直接对线上数据做评分，整个链路都不需要把数据导出数据库。

2.3 PL/Python：轻量赛道的 DIY 方案

如果你用的是普通 PostgreSQL，又不想安装 MADlib，或者训练逻辑特别个性化，可以考虑 PL/Python 扩展。PostgreSQL 支持用 Python 写存储过程和函数，你可以通过CREATE FUNCTION定义自定义训练函数，在函数内部用 COPY 或 SELECT 把库内数据读成 Python 的数据结构，再调用 sklearn 训练。

这就是“换了个地方写 Python”，数据没有真正导出，但内存里还是发生了数据传输。它的好处是灵活，scikit-learn 全家桶随便用；坏处是性能上限低，而且模型对象需要自己管理——通常要把 pickle 序列化结果塞进一张表里，预测时再反序列化。

2.4 三条路线的真实选型建议

选路线的核心逻辑就一句话：先看数据在哪，再看数据量多大，最后才看你想用什么算法。数据在 Greenplum 就别硬上 Python 导出，数据在 SQL Server 就好好用它的 ML Services，数据在普通 PG 且量不大，PL/Python 反而是最省事的。

3. 一次完整的库内建模：用 SQL 完成特征工程、训练和评估

理论说多了没用，我直接用一个贷款违约预测的案例把整条链路跑一遍。这个案例是我在实际项目中用过的简化版，所有操作都能在 Greenplum + MADlib 环境下复现。

3.1 准备原始表：把“脏数据”变成特征矩阵

假设我们有一张客户申请表loan_application，字段包括：客户 ID、年龄、年收入、信用卡使用率、负债率、历史逾期次数、开户时间、教育水平、是否违约。这张表就是最原始的“脏”数据，字符串、数值、空值混在一起，直接拿去训练是不行的。

库内建模的第一步是建一个特征视图，把原始字段转换成算法需要的数值矩阵。这一步和 Python 里的特征工程逻辑完全一样，只是用 SQL 表达：

CREATE VIEW loan_features AS SELECT is_default AS label, age::float8 AS age, annual_income::float8 AS income, credit_util_rate::float8 AS credit_util, debt_ratio::float8 AS debt_ratio, overdue_count::float8 AS overdue_count, EXTRACT(YEAR FROM age(current_date, open_date)) AS account_age, CASE WHEN education_level = 'high_school' THEN 1 ELSE 0 END AS edu_high_school, CASE WHEN education_level = 'bachelor' THEN 1 ELSE 0 END AS edu_bachelor, CASE WHEN education_level = 'master' THEN 1 ELSE 0 END AS edu_master FROM loan_application;

这里有几个容易踩的细节。教育水平是字符串类型，MADlib 不会帮你做编码，必须手动建哑变量；age字段如果本身是整数，也要显式转成float8，避免训练时类型不一致报错；连续型特征有空值的话，MADlib 会直接忽略整行，所以要么在视图里用COALESCE补默认值，要么接受丢弃这些样本。我的习惯是优先在视图层处理干净，宁可多写几行 SQL，也别让空值悄悄减少训练样本。

3.2 数据划分：训练集和测试集也要在库里完成

Python 里一个train_test_split就搞定的事情，在库里得用 SQL 实现。我常用基于哈希取模的方式，保证同一用户永远落在同一边，避免信息泄漏：

CREATE VIEW loan_train AS SELECT * FROM loan_features WHERE mod(hashtext(user_id::text), 10) < 7; CREATE VIEW loan_test AS SELECT * FROM loan_features WHERE mod(hashtext(user_id::text), 10) >= 7;

用hashtext对用户 ID 取哈希再取模，好处是分布均匀、可复现，而且在增量训练场景下新用户也能按同样的规则落到训练集或测试集。千万别直接用random()划分，那样每次跑出来的结果都不一样，模型评估和调优的时候会非常痛苦。

3.3 训练逻辑回归：一条 SQL 完成

特征视图和数据划分准备好之后，训练就是一条 SQL：

DROP TABLE IF EXISTS loan_model; SELECT madlib.logregr_train( 'loan_train', -- 训练数据视图 'loan_model', -- 输出模型表 'label', -- 标签列 'ARRAY[1, age, income, credit_util, debt_ratio, overdue_count, account_age, edu_high_school, edu_bachelor, edu_master]', NULL, -- 分组列，NULL 表示整体建模 NULL, -- 权重列 30 -- 最大迭代次数 );

注意特征表达式里有一个醒目的1，这是截距项（intercept），必须显式写进去，漏掉的话模型就没有偏置，拟合效果会大打折扣。这是我见过新手犯得最多的错误之一。

跑完后看一眼模型表：

SELECT * FROM loan_model;

MADlib 输出的模型表里有coef、log_likelihood、std_err、z_stats、p_values等列。其中coef是一个数组，顺序和特征表达式完全一致，第一个数就是截距。这一步相当于 Python 里model.coef_和model.intercept_的合体。

3.4 评估模型：直接在测试集上算指标

模型训练完，评估也在 SQL 里做。先用madlib.logregr_predict对测试集打分：

CREATE VIEW loan_test_pred AS SELECT label, madlib.logregr_predict(coef, ARRAY[1, age, income, credit_util, debt_ratio, overdue_count, account_age, edu_high_school, edu_bachelor, edu_master]) AS prob FROM loan_test, loan_model;

然后算 AUC。MADlib 提供了一个很实用的函数：

SELECT madlib.area_under_curve(prob, label) AS auc FROM loan_test_pred;

输出的 AUC 值就是模型的区分能力。第一次跑如果 AUC 不到 0.7，通常说明特征没做透或者变量筛选有问题，回到视图层继续调特征就行。

交叉验证也有现成函数madlib.cross_validation_general，可以把训练数据按比例切成 K 折，但需要把数据表整理成 MADlib 要求的格式，操作起来略繁琐。我个人的做法是：先把单次划分的 train/test 流程跑通，快速迭代特征；特征基本稳定之后再上交叉验证做精细评估，避免把时间浪费在前期探索阶段。

4. “模型入库”不是存个文件：模型对象的管理、版本和复用

训练完成只是开始。模型真正“入库”的含义，远比“把 pkl 文件存到数据库”要复杂得多。这一章我说说怎么把模型当成一个可管理的数据库资产。

4.1 模型到底是什么形态

在 MADlib 方案中，训练结束后loan_model实际上是一张普通表，里面存着模型的系数、统计量和元信息。也就是说，模型本身变成了数据库里的一个表对象，可以像普通表一样被查询、备份、授权、复制。

这就带来一个很自然的好处：模型的出处清晰可溯。loan_model表里可以看到训练时间戳、数据来源表名、算法类型，配合训练时同步写入的日志表，任何一次模型更新都有据可查。这在传统 Python 建模里是比较麻烦的事情——你很难说清楚某个 pkl 文件是哪份数据、哪段代码、哪个超参数组合训出来的。

对于 PL/Python 或 SQL Server 方案，模型是一个二进制对象，通常存在一张注册表里：

CREATE TABLE models ( model_id serial PRIMARY KEY, model_name text, model_version text, trained_at timestamp default now(), model_data bytea );

每次训练完把序列化字节码塞进model_data，预测时读出来反序列化。这种方式更接近“文件入库”的感觉，灵活性高，但需要自己保证版本和元信息的完整，MADlib 那种“表即模型”的方式反而更省心。

4.2 模型版本管理：用一张总表统一登记

模型一旦开始频繁重训，没有版本管理一定会乱。我给生产环境设计的模式很简单：每次训练后，把模型的关键信息登记到一张模型注册表里：

CREATE TABLE model_catalog ( model_id serial PRIMARY KEY, model_name text NOT NULL, model_table text NOT NULL, version text, train_view text, auc float8, trained_at timestamp default now(), status text default 'active' );

训练完系数之后，立即插入一条记录：

INSERT INTO model_catalog (model_name, model_table, version, train_view, auc) SELECT 'loan_glm', 'loan_model', 'v1.0', 'loan_train', auc FROM (SELECT madlib.area_under_curve(prob, label) AS auc FROM loan_test_pred) t;

以后想切换回旧模型，只需要更新status字段，把旧版本置为archived，新版本置为active。生产环境读取预测函数时，从model_catalog里查status='active'对应的model_table，动态引用即可。这套设计把模型的“生命周期”纳入了数据库的事务管理，好处是回滚只需要一条 UPDATE，不用翻文件时间戳。

4.3 备份、迁移和权限控制

模型作为表对象，备份迁移就简单了：

pg_dump -t loan_model -t model_catalog your_database > model_backup.dump

恢复时pg_restore一把梭。但如果模型表里存的是 PL/Python 的二进制对象，还需要保证目标环境的 Python 依赖版本一致，否则反序列化可能失败。这个细节看起来小，实际踩坑的概率很高，尤其是 pickle 跨版本兼容问题。

权限控制也要提前想清楚。模型表里存的是从训练数据里学到的统计信息，有些业务指标严格来说也属于敏感数据，不能谁都查。建议把模型表的 SELECT 权限单独授予给算法团队和数据应用团队，不要放开给所有人。

5. 把入库模型接进生产链路：从评分到落库的实际调用方式

模型入了库，最终要在业务里发挥作用。这里说的“生产预测”，不是把测试集跑一遍看指标，而是要在一个可持续运行的链路上，对每天新增的数据做评分。

5.1 三种预测调用方式

MADlib 最直接的预测方式是在查询里调用madlib.logregr_predict，把模型系数和特征拼成数组传进去。生成评分结果并落库：

INSERT INTO loan_prediction_result (user_id, predict_dt, score) SELECT user_id, current_date, madlib.logregr_predict( m.coef, ARRAY[1, age, income, credit_util, debt_ratio, overdue_count, account_age, edu_high_school, edu_bachelor, edu_master] ) AS score FROM loan_application a CROSS JOIN loan_model m;

注意这里用CROSS JOIN loan_model m，因为模型表在 MADlib 中通常只有一行，这样就把模型系数并到每条数据上。

如果是 SQL Server 的二进制模型方案，预测前需要先在存过过程里把模型反序列化出来，再调用PREDICT函数。性能上比 MADlib 这种纯 SQL 函数要差一些，因为要加载模型对象、初始化 Python 运行时。

PL/Python 方案则更“裸”：自定义一个预测函数，每次调用时从模型表读二进制，反序列化后对一行数据做predict_proba。这种写法灵活但性能最差，适合低频的小批量预测。

5.2 性能优化：永远别逐行调用预测函数

直觉上，给每行数据打分最自然的写法是写一个存储过程，循环遍历，逐行调用预测函数。如果你真的这么做了，我劝你马上停手。PostgreSQL 和 Greenplum 的逐行函数调用开销极大，几百万行数据跑下来，速度慢到让人怀疑人生。

正确做法是基于集合的批量计算。上面INSERT INTO ... SELECT的写法就是典型例子，数据库把特征数组按行批量传给 MADlib 的预测函数，一次扫描集级完成。如果评分结果更新到原表，用 UPDATE 关联子查询：

UPDATE loan_application a SET default_prob = p.prob FROM ( SELECT user_id, madlib.logregr_predict(coef, ARRAY[1, ...]) AS prob FROM loan_application, loan_model ) p WHERE a.user_id = p.user_id;

数据量大到单次 UPDATE 锁表时间太长时，可以考虑按user_id哈希拆分区段，分批执行。生产环境里我常用的做法是：把评分逻辑写成一个视图，业务系统直接从视图取数；底层数据表更新了，视图结果自动变，不需要显式写回。

5.3 线上评分的稳定性和监控

模型上生产之后，有两件事必须盯住：分数分布漂移和特征缺失率。库内方案的一个好处是，这些监控也可以用 SQL 定时算。

SELECT current_date AS stat_dt, avg(score) AS avg_score, count(*) AS score_cnt, sum(CASE WHEN score IS NULL THEN 1 ELSE 0 END)::float8 / count(*) AS missing_rate FROM loan_prediction_result WHERE predict_dt = current_date;

把这条 SQL 丢进调度任务，每天跑一次，分数均值波动超过 10% 就报警。实际项目中，业务方最怕的是模型今天还能用、明天结果就飘了。漂移往往不是模型退化，而是上游字段口径变了——这种问题在库内方案里面最容易排查，因为数据和模型在同一个系统里，追根溯源只要查特征视图的改动记录就行。

6. 一趟实践下来踩过的坑：库内机器学习不是万能药

整个流程跑通之后，我再回头总结几个典型的坑。这些坑官方文档很少提，但实际项目中几乎都会撞上。

6.1 特征工程的“维度爆炸”问题

MADlib 这类库内置模型，特征表达式是一个固定长度的数组，类别变量需要手动展开成哑变量。如果类别很多，比如省市有 300 个取值，你会在特征视图里写出 300 个 CASE WHEN，这不仅让 SQL 变得极其冗长，还会导致模型数组维度爆炸，训练内存飙升。

我处理这类问题的方式是“高频类别粗粒度化”：先统计类别频次，只对 top 10 的类别建哑变量，其他全部归入 other 类。这样既控制了维度，又保留了主要信息。这个思路其实在 Python 建模里也一样成立，只是在 SQL 里写起来更麻烦，所以更要提前规划特征表达方式。

6.2 缺省值会让样本悄悄“消失”

MADlib 的训练函数遇到 NULL 特征值时，行为是整行忽略。如果你的原始数据里某个特征的缺失率是 15%，那训练样本就会无声无息少掉 15%。更麻烦的是，不同特征的缺失分布如果有关联，实际留下的样本可能只剩 70%。

所以特征视图里一定要显式处理缺失值：数值型用COALESCE归到中位数或 0，类别型用专门的“未知”标签。这个步骤不能依赖训练函数帮你兜底，它是特征工程的一部分。

6.3 什么时候应该放弃库内 ML

库内 ML 不是银弹。我总结了几种不太适合的场景：

第一，深度学习相关的需求。图像、文本、序列数据，主流的做法还是 PyTorch/TensorFlow 那套，数据库里没有对应的算力，硬塞进去没有任何优势。

第二，特征工程极其复杂、需要大量自定义代码的场景。比如你要做各种时间窗口的滑动统计、嵌套交叉验证、复杂的样本权重逻辑，用 SQL 表达的成本会高到不值得，这时候把特征算好放到外部建模反而更实际。

第三，需要频繁试验超参数组合的场景。虽然 MADlib 支持参数调整，但每次训练都要写 SQL、看结果，交互体验远不如 Notebook 里GridSearchCV来得流畅。我的经验是：先用库内 ML 快速跑一个 baseline，确定数据可行性和大致效果，再决定要不要把模型迁到外部做精细调参。库内方案的最优定位是“基线快跑 + 生产落地”，而不是“研究探索平台”。

6.4 多模型融合在库内一样能做

最后说一个进阶思路。库内 ML 不是只能训单个模型，多个模型的结果也能在库内融合。最简单的方式是把逻辑回归、随机森林、朴素贝叶斯各训一份，然后写 SQL 把三个模型的概率字段按业务权重做加权平均，融合结果继续落库。这本质上就是一个不需要出库的模型集成流程。

我个人做完这套改造之后最大的体会是：机器学习的工程链路里，最费劲的从来不是调参，而是数据搬运和模型落地。库内机器学习把这两件事都省了，让算法工程师能把精力放回特征和业务本身。如果你所在的环境是 Greenplum 或 PostgreSQL 体系，强烈建议先拿一个真实业务需求试跑一遍，从特征视图到模型入库到生产评分，半天就能感受到效率的差距。跑通之后再逐步扩展模型类型和监控机制，你会发现这条路比传统的“导出-训练-回写”省心得多。