如何在 SQL 中训练决策树分类器-程序员充电站

原文：towardsdatascience.com/how-to-train-a-decision-tree-classifier-in-sql-e29f37835f18

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/2c7f87cf022be929902d4eb32767f583.png

由 Resource Database 在 Unsplash 拍摄的图片

当谈到机器学习时，我热衷于攻击数据所在的地方。90% 以上的时间，那将是一个关系数据库，如果我们谈论的是监督机器学习的话。

Python 很棒，但每次您想要训练一个模型时，都要提取数十 GB 的数据，这是一个巨大的瓶颈，尤其是如果您需要频繁地重新训练它们。消除数据移动非常有意义。SQL 是您的朋友。

对于这篇文章，我将使用始终免费的 Oracle Database 21c，它在 Oracle Cloud 上配置。我不确定您是否可以将逻辑翻译到其他数据库供应商。Oracle 运行得很好，您配置的数据库不会让您花一分钱——永远都不会。

数据集加载和预处理

我会把 Python 与 Oracle 在大型数据集上机器学习比较留到其他时候。今天，一切回归基础。

我今天将使用以下数据集：

费舍尔，R.A.（1936）. 在分类问题中使用多种测量方法。加州大学欧文分校，信息与计算机科学学院。从archive.ics.uci.edu/ml/datasets/iris获取。许可协议下 Creative Commons Attribution 4.0。

因此，下载它以跟随，并确保您已经建立了与数据库实例的连接。像 SQL Developer 或 Visual Studio Code 这样的工具可以帮您做到这一点。

如何创建数据库表

以下代码片段将创建iris表。id列是强制性的，因为 Oracle 在幕后需要它：

create sequence seq_iris;create table iris(idnumber default seq_iris.nextval,sepal_length number(2,1),sepal_width number(2,1),petal_length number(2,1),petal_width number(2,1),species varchar2(15));

表创建完成后，就是时候加载数据了。

如何将 CSV 数据加载到表中

源数据是 CSV 格式，因此您需要使用像 SQL Developer 这样的工具将其加载到表中。右键单击您的表名，选择导入数据并提供您的 CSV 文件路径。

您需要将 CSV 列名映射到您的表列名：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/626cd0c744f68bc5a1f02cd14f8016ff.png

图像 1 – 将 CSV 文件加载到表中（图片由作者提供）

完成后，发出一个简单的select语句以验证数据是否成功加载：

select*fromiris;

这是你应该得到的结果：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/f5bcca1cb3c03e8417c8eb5f4ac07f4d.png

图像 2 – 存储在数据库中的 Iris 数据集（图片由作者提供）

所有可用数据现在都在一个表中。这是一个很好的起点，但不是你想要的机器学习模型。

训练/测试分割

在构建机器学习模型时，将数据分成两个子集非常有意义。你在一个子集（较大的）上训练模型，并在之前未见过的数据上评估它。

Oracle SQL 允许你使用随机种子采样数据的一部分，比如 75%。你可以使用这种技术创建一个训练集，然后使用minus集合运算符创建一个测试集：

--Trainsetcreateorreplace view v_iris_trainasselect*fromiris sample(75)seed(42);--Testsetcreateorreplace view v_iris_testasselect*fromiris minus select*fromv_iris_train;

要查看数据是如何分割的，你可以打印每个的行数：

select(select count(*)fromv_iris_train)asn_train_instances,(select count(*)fromv_iris_test)asn_test_instancesfromdual;

并非完美的 75:25 分割，但足够接近：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/5f04f6231103165c8f920e2e8c69c176.png

图像 3 – 训练/测试实例数量（图片由作者提供）

你现在拥有创建 SQL 中机器学习模型所需的一切。让我们直接开始吧！

如何在 SQL 中训练决策树分类器

注意：我正在演示如何训练决策树分类器，但这不是唯一可用的模型。你可以选择简单的线性模型、神经网络以及两者之间的任何模型。请参阅官方文档以获取所有可用的算法。

在 SQL 中训练第一个机器学习模型之前，你需要了解一些事情：

你必须命名你的模型– 这是一个任意的字符串，但如果已经存在具有给定名称的模型，则无法创建模型。我将首先向你展示如何删除它。
每个模型都有自己的设置– 所有这些都被传递到dbms_data_mining.setting_list中。你即将看到的设置是特定于决策树分类器模型的。请参阅官方文档以获取更多设置，或不同模型的设置。
训练函数– 建议使用dbms_data_mining.create_model2()函数来训练模型。名称中的2是不幸的命名，它是原始函数的修订版，现在它允许你直接传递模型设置，而不是首先将它们保存到表中。

至于模型，我将修改设置以使用熵作为度量标准，并设置最大深度为 5。该模型将命名为ml_model_iris，如果存在，将首先被删除。

代码的其余部分主要与错误处理有关：

declare v_setlist dbms_data_mining.setting_list;begin--1\.Decision tree specific settings v_setlist(dbms_data_mining.algo_name):=dbms_data_mining.algo_decision_tree;v_setlist(dbms_data_mining.tree_impurity_metric):=dbms_data_mining.tree_impurity_entropy;v_setlist(dbms_data_mining.tree_term_max_depth):='5';--2\.Drop the modelifit exists begin dbms_data_mining.drop_model('ml_model_iris');exception when others then--Model doesnotexist error codeifsqlcode=-40284then null;elsedbms_output.put_line('ERROR training the ML model');dbms_output.put_line('Reason: Unable to delete previous ML model.');dbms_output.put_line('Message: '||sqlerrm);dbms_output.put_line('--------------------------------------------------');endif;end;--3\.Create the model begin dbms_data_mining.create_model2(model_name=>'ml_model_iris',mining_function=>'CLASSIFICATION',data_query=>'select * from v_iris_train',set_list=>v_setlist,case_id_column_name=>'id',target_column_name=>'species');exception when others then dbms_output.put_line('ERROR training the ML model');dbms_output.put_line('Reason: Unable to train the model.');dbms_output.put_line('Message: '||sqlerrm);dbms_output.put_line('--------------------------------------------------');end;--4\.Other potential exceptions exception when others then dbms_output.put_line('ERROR training the ML model');dbms_output.put_line('Reason: Other.');dbms_output.put_line('Message: '||sqlerrm);dbms_output.put_line('--------------------------------------------------');end;/

运行上述 PL/SQL 片段将创建大量表。在我看来，这些应该从表视图中隐藏，因为它们引入了很多杂乱，尤其是如果你有多个模型版本的话：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/9ce7d22b3174005c1c4588775fe5b381.png

图像 4 – Oracle 创建的表（作者图片）

但是，这些表格包含了很多有用的信息。让我们接下来看看：

如何提取模型细节

例如，DM$PX<model-name>表显示了决策树模型的“做出的决策”。换句话说，它显示了哪些特征需要在何处分割以做出最佳预测：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/e87e1af09af73be0ce91d3be0fa38463.png

图像 5 – 决策树分类器的模型细节（作者图片）

其他表格的相关性较低，所以我将它们的调查留给你们。

如何在测试集上做出预测

现在转到预测。下面的代码片段创建了一个名为iris_predictions的表，该表包含测试集中每行的类预测和预测概率。

语法在 Oracle 数据库的机器学习任务中是相当特定的，因为您需要调用prediction_set()函数并传入模型：

create table iris_predictionsas(select s.id,t.prediction,t.probabilityfromv_iris_test s,table(prediction_set(ml_model_iris using*))t);

这将为您创建表格，让我们来看看里面有什么：

select*fromiris_predictions;

预测是基于类别的，这意味着对于单个实例，您将拥有与类别数量相同的记录。Iris 数据集有 3 个类别，所以iris_predictions表将包含 3 * 33 条记录：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/e993ca8aac26b12823f883e07f0da688.png

图像 6 – 每个类别的预测概率（作者图片）

通过一点 SQL 魔法，您可以提取出probability最高的那一行。分析row_number()函数与降序排序一起完成了大部分繁重的工作：

withranked_predictionsas(selectid,prediction,probability,row_number()over(partition byidorder by probability desc)asrnfromiris_predictions)select p.id,a.speciesasactual,p.prediction,p.probability,casewhen a.species=p.prediction then1else0endasis_correctfromranked_predictions p join v_iris_test a on a.id=p.idwhere p.rn=1;

我还添加了一个is_correct标志，这样您可以立即知道预测值是否与实际值匹配：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/b01e876709e661e10ed9dc1e6d9c6ef0.png

图像 7 – 带有 is_correct 标志的预测类别（作者图片）

正如你所见，情况并不总是如此：

https://github.com/OpenDocCN/towardsdatascience-blog-zh-2024/raw/master/docs/img/a04fcda68b7ed3a81371a9c6126eca58.png

图像 8— 带有 is_correct 标志的预测类别（2）（作者图片）

您现在有了预测结果集，但模型在测试集上的准确性呢？还有其他指标，比如混淆矩阵呢？这就是您接下来要实现的。

如何评估您的决策树分类器模型

不幸的是，在测试集和混淆矩阵上计算模型精度需要比在 Python 中做同样的事情更多的代码。

当计算混淆矩阵时，你可以免费获得准确度值，所以这是一个加分项。dbms_data_mining.confusion_matrix()函数要求你传入一打参数，所有这些参数都指定了不同的表名和表列名。混淆矩阵本身将被保存在由confusion_matrix_table_name参数指定的表中。

总体来说，这是一项大量工作：

declare cm_accuracy number;begin execute immediate'drop table iris_confusion_matrix';exception when others thenifsqlcode=-942then null;elsedbms_output.put_line('ERROR making evaluations');dbms_output.put_line('Reason: Unable to delete previous table containing confusion matrix.');dbms_output.put_line('Message: '||sqlerrm);dbms_output.put_line('--------------------------------------------------');endif;begin dbms_data_mining.compute_confusion_matrix(accuracy=>cm_accuracy,apply_result_table_name=>'iris_predictions',target_table_name=>'v_iris_test',case_id_column_name=>'id',target_column_name=>'species',confusion_matrix_table_name=>'iris_confusion_matrix',score_column_name=>'prediction',score_criterion_column_name=>'probability',cost_matrix_table_name=>null,apply_result_schema_name=>null,target_schema_name=>null,score_criterion_type=>'PROBABILITY');dbms_output.put_line('Accuracy: '||round(cm_accuracy*100,2)||'%');exception when others then dbms_output.put_line('ERROR making evaluations');dbms_output.put_line('Reason: Unable to create a confusion matrix table.');dbms_output.put_line('Message: '||sqlerrm);dbms_output.put_line('--------------------------------------------------');end;end;/