SQL Server数据挖掘技术应用研究

周勇强

摘要：使用SQL Server进行数据挖掘时，透彻理解相关技术及产品的使用特点、工作机制对提升挖掘效果具有重要意义。遵循CRISP-DM标准数据挖掘流程，以UCI数据集Adult分类任务为案例，研究了在MicrosoftBI技术框架下实现SQL Serv-er数据挖掘的基本过程、方法和特点，探索了重要图表工具的工作机制。实验表明SQLServer数据挖掘技术易于使用、性能良好，并能和SSIS等很好地协同工作。

关键词：数据挖掘;挖掘模型;决策树;提升图;分类矩阵;混淆矩阵;SQLServer;CRISP-DM

中图分类号：TP311.13 文献标识码：A

文章编号：1009-3044（2019）32-0027-03

1概述

SQL Server数据挖掘因具易于使用且效果良好等特点而受到欢迎，但易于使用的背后隐藏着复杂的内部机制，要获得最佳挖掘效果，需要对相关技术和产品的使用特点、工作机制等有较为透彻的理解，但目前这方面的研究较为缺乏。本文通过实例研究在Microsoft BI技术框架下实现SQL Server数据挖掘的基本过程、方法和特点，研究数据为UCI机器学习数据库中的Aduh数据集，研究内容贯穿整个CRISP-DM标准定义的数据挖掘项目生命周期（业务理解、数据理解、数据准备、建模、评估、部署），重点探索数据准备、建模、评估阶段的内容及重要图表工具的工作机制，最后对SQL Server数据挖掘的易用性、挖掘性能、与SSIS技术的配合等方面进行评价。

2业务理解、数据理解

本文数据挖掘任务是根据人口普查数据预测一个人的年收入是否超过5万美元。所选数据为UCI机器学习数据库中的Adult数据集，该数据集从美国1994年人口普查数据库中抽取而来，共48842条记录，已经划分为训练数据和测试数据，分别存储于平面文件adult.data（32561条）、adult.test（16281条）

该数据集属于分类数据集，可预测列为incomef年收入是否超过5万美元），可用作输入列的属性共有14个：age（年龄）、workclass（T作类别）、fnlwgt（序号）、education（受教育程度）、edu-cation_num（受教育时间）、marital_status（婚姻状况）、occupation（职业）、relationship（家庭关系）、race（种族）、sex！（性别）、capital_gain（资本收益）、capital_loss（资本损失）、hours_per_week（每周工作小时数）、naive_country（原籍）。

本文预测结果可用于很多场合，如：为政府研究居民收入状况并完善税收、社会保障以及各种民生制度提供一定的参考，针对高收入人群定制高端商业活动等。

3数据准备

首先将数据导入SQL Server数据库，然后进行数据预处理。

1）建立SQL Server数据库

建立SQL Server数据库，在库中建立保存训练数据和测试数据的表Adult及Adult_test，表结构和数据源结构一一对应，另外增加IDENTITY自增型字段id用作表的主键。因为每个人的id不能重复，因此两张表id字段的标识种子需要根据数据量设置足够大的间隔。

2）数据导入及预处理

使用SSDT（SQL Server Data Toolsl建立Integration Services项目，该项目包含二个SSIS包，分别用于训练数据和测试数据的导人及预处理，流程见图1。

（1）将数据导人SQL Server数据库。名为“数据导人”的控制流节点是一个数据流任务。首先使用“平面文件流”控件连接要导入的平面文件adult.data（adult.test），然后通过“数据转换”控件将其中的文本列转换为Unicode字符串，最后使用“OLE DB目标”控件将数据导入表Adult（Adult_test）。

（2）数据预处理。通过对数据的浏览及探索，了解数据的分布及质量情况，然后针对发现的问题对数据进行清洗、转换、规格化等处理。

①删除字符串前后空格。用于删除income字段数据的前导空格。

②处理缺失项。缺失值已用“？”代替，直接删除有缺失值的记录。

③处理不一致.1生数据。Adult_test表中income字段值是“>50K”及“<=50K.”，比Adult表多了一个小数点，删除小数点使二者一致。

④删除重复记录。对于所有输入列都重复的记录，如果mcome数据都相同，则仅保留一条记录;

⑤删除冲突记录。对于所有输入列都重复的记录，如果income数据不完全一致，则删除所有这些记录。

4建模

使用SSDT建立“Analysis Services多维和数据挖掘项目”。建模流程见图2。

1）建立数据源。数据源定义到数据的连接。本文数据源是在数据准备阶段建立的数据库。

2）建立数据源视图。数据源视图定义数据源中可在挖掘结构中使用的数据子集。本文数据源视图包含数据表Adult、Adult_test。

3）建立挖掘结构。指定数据源视图中可为挖掘模型所用的表和列。本文指定事例表为Adult，其中id列为键列，其它列均指定为挖掘结构列。

4）特征选择。特征选择的目的是从挖掘结构列中指定挖掘模型输入列，需排除无关列和冗余列。输人列和可预测列的关联性可以参考数据挖掘向导中“提供相关列建议”界面为每个列提供的“分数”以及输人列选用建议。本案例中race、native_country、workclass、sex列因分數过低而未被挖掘向导建议选作输入列;冗余列主要考察可能存在相关性的列f如education和education_num）。特征选择方法：

①建立挖掘模型。选择Microsoft决策树算法，指定income列为可预测列（predictOnlv），基于相同的挖掘结构建立一系列挖掘模型，分别忽略上述列及其组合。

②处理挖掘结构和挖掘模型。处理挖掘结构时，SSAS查询数据并生成数据的统计信息，保存在缓存中。处理挖掘模型时，SSAS从挖掘结构获取数据，使用指定的挖掘算法分析这些数据，得到挖掘结果（如数据中的规则和模式），将挖掘结果填充到挖掘模型中，从而完成挖掘模型的定型。

③交叉验证。所有模型都使用训练集进行10折交叉验证。训练得到每个模型每折的性能度量值并计算平均值和标准偏差，通过比较这些指标找到最佳模型。本文特征选择结果：剔除education_num列。

5）超参数寻优。本案例需要寻优的超参数有COMPLEXI-TY_PENALTY、MINIMUM_SUPPORT、SCORE_METHOD、SPLIT_METHOD等Microsoft决策树算法参数。方法是基于经过特征列选择的最佳模型建立一系列挖掘模型，每个模型配置不同的超参数组合，处理后使用交叉验证评估模型优劣，选出最佳模型。本文超参数寻优结果：设置SCORE_METHOD=1，MINIMUM_SUPPORT=25，其他按系统默认设置。

6）浏览挖掘模型。图3为最终的决策树模型。（局部视图）

在决策树中，任何事例都从根节点出发，经过中间节点（如果有）到达叶节点。非叶结点对应于属性测试，其包含的样本集合根据属性测试的结果被划分到子节点中;叶节点对应于决策结果。挖掘图例显示了模型中与所选节点有关的信息：一是该节点的节点规则（从根节点到该节点的路径）;二是该节点的事例总计以及各类事例（正例、负例、缺失事例）的计数及预测概率。叶节点中预测概率最大的类别作为该叶节点的预测值。每个叶节点对应一条分类规则，包括节点规则、预测值、预测概率。所有叶节点的分类规则组成决策树模型的分类规则集。

图中所选叶节点的节点规则为：Relationship='Unmarriedand Occupation='Adm-clerical，该叶节点包含事例672个f其中正例16个、负例656个、缺失值0个），计算预测概率时，正例数量和负例数量都会加1（目的是通过这种调整使模型可以适当地处理缺失值），因此正例预测概率=（16+1）/（672+2）=2.52%，负例预测概率=（656+1）/（672+2）=97.48%。由于负例预测概率高于正例预测概率，因此该节点的预测值为负例（即“<=50K”）。该节点中占2.52%的正例被错误地预测成了负例，而其中占97.48%的负例都预测正确，可以下钻浏览这些事例。

5评估

首先通过提升图、分类矩阵等工具将模型应用于测试数据集，来评估模型预测正确性。最后需从业务角度对模型进行评估，确认达到业务需求。

5.1提升图

提升图显示对可预测列进行预测的执行情况。图4显示了对可预测列所有状态进行预测的执行情况。

提升图显示两条线，分别代表理想模型和实际模型。为了将实际模型和理想模型进行对比，首先将测试数据集中所有事例都按预测概率从大到小排序，然后从预测概率大的一端抽取数据，逐步增加抽取数据的比例（X轴），同步检查其中正确预测的比例（Y轴）。对于理想模型，预测总是正确的，所以提升图是一条45度的直线;对于实际模型，随着抽取数据比例的增加，预测概率逐步降低，因此和理想模型相比，提升图逐步向下弯曲，和理想模型直线之间的距离愈来越远。提升图使用“分数”表示模型预测性能，分数越高，模型预测性能越好，提升图越接近理想模型，本文提升图分数为0.93。

图中当前度量位置位于总体50%处，此处理想模型的总体正确百分比是50%（预测完全正确），实际模型为48.87%。当将度量位置移动到100%处时，理想模型的总体正确百分比是也100%，而实际模型为85.65%。

5.2分类矩阵

分类矩阵（或称混淆矩阵）通过比较预测值与实际值的匹配情况，将所有事例分为不同类别并分类计数。本案例的分类矩阵见图5。计算典型分类指标：

上述指标显示本文模型对负例的预测能力高于正例，总体预测准确率为85.65%。

有些指标往往是有冲突的（如RecMl和Precision），选用什么评价指标取决于业务需求。如需尽可能找到正例，选RecMl;如需尽可能避免将负例预测为正例，则选Precision;如果以上两种要求重要性没有明显差别，则Accuracy和F1越高越好。

6部署

挖掘模型评估通过后，即可部署使用了。模型有多种使用方式，例如通过DMX将模型应用于新数据创建预测、分析人员直接分析模型包含的规则提出方案建议。模型经常被嵌入到应用程序中，成为业务过程的组成部分。

随着时间的推移，数据的分布在发生变化，输入列和可预测列之间的关系也在发生变化，因此模型的预测精度将降低，需不断对模型进行监控、维护和更新。

7结论

本文遵循CRISP-DM标准的指引，在Microsoft BI技术框架下为UCl分类数据集Adult建立了基于Microsoft决策树的挖掘模型，并对决策树模型、提升图、分类矩阵等重要图表工具进行了探索。实验表明，SQL Server数据挖掘易于使用、性能良好，并能和SSIS等同类BI技术很好地协同工作，所创建的挖掘模型的準确率（Accuracv）达到了85.65%，这和Adult数据集描述文档中记录的实验准确率是一致的（例如C4.5算法是84.46%）。