SQL Server數(shù)據(jù)挖掘技術(shù)應(yīng)用研究

周勇強(qiáng)

摘要：使用SQL Server進(jìn)行數(shù)據(jù)挖掘時(shí)，透徹理解相關(guān)技術(shù)及產(chǎn)品的使用特點(diǎn)、工作機(jī)制對提升挖掘效果具有重要意義。遵循CRISP-DM標(biāo)準(zhǔn)數(shù)據(jù)挖掘流程，以UCI數(shù)據(jù)集Adult分類任務(wù)為案例，研究了在MicrosoftBI技術(shù)框架下實(shí)現(xiàn)SQL Serv-er數(shù)據(jù)挖掘的基本過程、方法和特點(diǎn)，探索了重要圖表工具的工作機(jī)制。實(shí)驗(yàn)表明SQLServer數(shù)據(jù)挖掘技術(shù)易于使用、性能良好，并能和SSIS等很好地協(xié)同工作。

關(guān)鍵詞：數(shù)據(jù)挖掘;挖掘模型;決策樹;提升圖;分類矩陣;混淆矩陣;SQLServer;CRISP-DM

中圖分類號：TP311.13 文獻(xiàn)標(biāo)識碼：A

文章編號：1009-3044（2019）32-0027-03

1概述

SQL Server數(shù)據(jù)挖掘因具易于使用且效果良好等特點(diǎn)而受到歡迎，但易于使用的背后隱藏著復(fù)雜的內(nèi)部機(jī)制，要獲得最佳挖掘效果，需要對相關(guān)技術(shù)和產(chǎn)品的使用特點(diǎn)、工作機(jī)制等有較為透徹的理解，但目前這方面的研究較為缺乏。本文通過實(shí)例研究在Microsoft BI技術(shù)框架下實(shí)現(xiàn)SQL Server數(shù)據(jù)挖掘的基本過程、方法和特點(diǎn)，研究數(shù)據(jù)為UCI機(jī)器學(xué)習(xí)數(shù)據(jù)庫中的Aduh數(shù)據(jù)集，研究內(nèi)容貫穿整個CRISP-DM標(biāo)準(zhǔn)定義的數(shù)據(jù)挖掘項(xiàng)目生命周期（業(yè)務(wù)理解、數(shù)據(jù)理解、數(shù)據(jù)準(zhǔn)備、建模、評估、部署），重點(diǎn)探索數(shù)據(jù)準(zhǔn)備、建模、評估階段的內(nèi)容及重要圖表工具的工作機(jī)制，最后對SQL Server數(shù)據(jù)挖掘的易用性、挖掘性能、與SSIS技術(shù)的配合等方面進(jìn)行評價(jià)。

2業(yè)務(wù)理解、數(shù)據(jù)理解

本文數(shù)據(jù)挖掘任務(wù)是根據(jù)人口普查數(shù)據(jù)預(yù)測一個人的年收入是否超過5萬美元。所選數(shù)據(jù)為UCI機(jī)器學(xué)習(xí)數(shù)據(jù)庫中的Adult數(shù)據(jù)集，該數(shù)據(jù)集從美國1994年人口普查數(shù)據(jù)庫中抽取而來，共48842條記錄，已經(jīng)劃分為訓(xùn)練數(shù)據(jù)和測試數(shù)據(jù)，分別存儲于平面文件adult.data（32561條）、adult.test（16281條）

該數(shù)據(jù)集屬于分類數(shù)據(jù)集，可預(yù)測列為incomef年收入是否超過5萬美元），可用作輸入列的屬性共有14個：age（年齡）、workclass（T作類別）、fnlwgt（序號）、education（受教育程度）、edu-cation_num（受教育時(shí)間）、marital_status（婚姻狀況）、occupation（職業(yè)）、relationship（家庭關(guān)系）、race（種族）、sex?。ㄐ詣e）、capital_gain（資本收益）、capital_loss（資本損失）、hours_per_week（每周工作小時(shí)數(shù)）、naive_country（原籍）。

本文預(yù)測結(jié)果可用于很多場合，如：為政府研究居民收入狀況并完善稅收、社會保障以及各種民生制度提供一定的參考，針對高收入人群定制高端商業(yè)活動等。

3數(shù)據(jù)準(zhǔn)備

首先將數(shù)據(jù)導(dǎo)入SQL Server數(shù)據(jù)庫，然后進(jìn)行數(shù)據(jù)預(yù)處理。

1）建立SQL Server數(shù)據(jù)庫

建立SQL Server數(shù)據(jù)庫，在庫中建立保存訓(xùn)練數(shù)據(jù)和測試數(shù)據(jù)的表Adult及Adult_test，表結(jié)構(gòu)和數(shù)據(jù)源結(jié)構(gòu)一一對應(yīng)，另外增加IDENTITY自增型字段id用作表的主鍵。因?yàn)槊總€人的id不能重復(fù)，因此兩張表id字段的標(biāo)識種子需要根據(jù)數(shù)據(jù)量設(shè)置足夠大的間隔。

2）數(shù)據(jù)導(dǎo)入及預(yù)處理

使用SSDT（SQL Server Data Toolsl建立Integration Services項(xiàng)目，該項(xiàng)目包含二個SSIS包，分別用于訓(xùn)練數(shù)據(jù)和測試數(shù)據(jù)的導(dǎo)人及預(yù)處理，流程見圖1。

（1）將數(shù)據(jù)導(dǎo)人SQL Server數(shù)據(jù)庫。名為“數(shù)據(jù)導(dǎo)人”的控制流節(jié)點(diǎn)是一個數(shù)據(jù)流任務(wù)。首先使用“平面文件流”控件連接要導(dǎo)入的平面文件adult.data（adult.test），然后通過“數(shù)據(jù)轉(zhuǎn)換”控件將其中的文本列轉(zhuǎn)換為Unicode字符串，最后使用“OLE DB目標(biāo)”控件將數(shù)據(jù)導(dǎo)入表Adult（Adult_test）。

（2）數(shù)據(jù)預(yù)處理。通過對數(shù)據(jù)的瀏覽及探索，了解數(shù)據(jù)的分布及質(zhì)量情況，然后針對發(fā)現(xiàn)的問題對數(shù)據(jù)進(jìn)行清洗、轉(zhuǎn)換、規(guī)格化等處理。

①刪除字符串前后空格。用于刪除income字段數(shù)據(jù)的前導(dǎo)空格。

②處理缺失項(xiàng)。缺失值已用“？”代替，直接刪除有缺失值的記錄。

③處理不一致.1生數(shù)據(jù)。Adult_test表中income字段值是“>50K”及“<=50K.”，比Adult表多了一個小數(shù)點(diǎn)，刪除小數(shù)點(diǎn)使二者一致。

④刪除重復(fù)記錄。對于所有輸入列都重復(fù)的記錄，如果mcome數(shù)據(jù)都相同，則僅保留一條記錄;

⑤刪除沖突記錄。對于所有輸入列都重復(fù)的記錄，如果income數(shù)據(jù)不完全一致，則刪除所有這些記錄。

4建模

使用SSDT建立“Analysis Services多維和數(shù)據(jù)挖掘項(xiàng)目”。建模流程見圖2。

1）建立數(shù)據(jù)源。數(shù)據(jù)源定義到數(shù)據(jù)的連接。本文數(shù)據(jù)源是在數(shù)據(jù)準(zhǔn)備階段建立的數(shù)據(jù)庫。

2）建立數(shù)據(jù)源視圖。數(shù)據(jù)源視圖定義數(shù)據(jù)源中可在挖掘結(jié)構(gòu)中使用的數(shù)據(jù)子集。本文數(shù)據(jù)源視圖包含數(shù)據(jù)表Adult、Adult_test。

3）建立挖掘結(jié)構(gòu)。指定數(shù)據(jù)源視圖中可為挖掘模型所用的表和列。本文指定事例表為Adult，其中id列為鍵列，其它列均指定為挖掘結(jié)構(gòu)列。

4）特征選擇。特征選擇的目的是從挖掘結(jié)構(gòu)列中指定挖掘模型輸入列，需排除無關(guān)列和冗余列。輸人列和可預(yù)測列的關(guān)聯(lián)性可以參考數(shù)據(jù)挖掘向?qū)е小疤峁┫嚓P(guān)列建議”界面為每個列提供的“分?jǐn)?shù)”以及輸人列選用建議。本案例中race、native_country、workclass、sex列因分?jǐn)?shù)過低而未被挖掘向?qū)Ыㄗh選作輸入列;冗余列主要考察可能存在相關(guān)性的列f如education和education_num）。特征選擇方法：

①建立挖掘模型。選擇Microsoft決策樹算法，指定income列為可預(yù)測列（predictOnlv），基于相同的挖掘結(jié)構(gòu)建立一系列挖掘模型，分別忽略上述列及其組合。

②處理挖掘結(jié)構(gòu)和挖掘模型。處理挖掘結(jié)構(gòu)時(shí)，SSAS查詢數(shù)據(jù)并生成數(shù)據(jù)的統(tǒng)計(jì)信息，保存在緩存中。處理挖掘模型時(shí)，SSAS從挖掘結(jié)構(gòu)獲取數(shù)據(jù)，使用指定的挖掘算法分析這些數(shù)據(jù)，得到挖掘結(jié)果（如數(shù)據(jù)中的規(guī)則和模式），將挖掘結(jié)果填充到挖掘模型中，從而完成挖掘模型的定型。

③交叉驗(yàn)證。所有模型都使用訓(xùn)練集進(jìn)行10折交叉驗(yàn)證。訓(xùn)練得到每個模型每折的性能度量值并計(jì)算平均值和標(biāo)準(zhǔn)偏差，通過比較這些指標(biāo)找到最佳模型。本文特征選擇結(jié)果：剔除education_num列。

5）超參數(shù)尋優(yōu)。本案例需要尋優(yōu)的超參數(shù)有COMPLEXI-TY_PENALTY、MINIMUM_SUPPORT、SCORE_METHOD、SPLIT_METHOD等Microsoft決策樹算法參數(shù)。方法是基于經(jīng)過特征列選擇的最佳模型建立一系列挖掘模型，每個模型配置不同的超參數(shù)組合，處理后使用交叉驗(yàn)證評估模型優(yōu)劣，選出最佳模型。本文超參數(shù)尋優(yōu)結(jié)果：設(shè)置SCORE_METHOD=1，MINIMUM_SUPPORT=25，其他按系統(tǒng)默認(rèn)設(shè)置。

6）瀏覽挖掘模型。圖3為最終的決策樹模型。（局部視圖）

在決策樹中，任何事例都從根節(jié)點(diǎn)出發(fā)，經(jīng)過中間節(jié)點(diǎn)（如果有）到達(dá)葉節(jié)點(diǎn)。非葉結(jié)點(diǎn)對應(yīng)于屬性測試，其包含的樣本集合根據(jù)屬性測試的結(jié)果被劃分到子節(jié)點(diǎn)中;葉節(jié)點(diǎn)對應(yīng)于決策結(jié)果。挖掘圖例顯示了模型中與所選節(jié)點(diǎn)有關(guān)的信息：一是該節(jié)點(diǎn)的節(jié)點(diǎn)規(guī)則（從根節(jié)點(diǎn)到該節(jié)點(diǎn)的路徑）;二是該節(jié)點(diǎn)的事例總計(jì)以及各類事例（正例、負(fù)例、缺失事例）的計(jì)數(shù)及預(yù)測概率。葉節(jié)點(diǎn)中預(yù)測概率最大的類別作為該葉節(jié)點(diǎn)的預(yù)測值。每個葉節(jié)點(diǎn)對應(yīng)一條分類規(guī)則，包括節(jié)點(diǎn)規(guī)則、預(yù)測值、預(yù)測概率。所有葉節(jié)點(diǎn)的分類規(guī)則組成決策樹模型的分類規(guī)則集。

圖中所選葉節(jié)點(diǎn)的節(jié)點(diǎn)規(guī)則為：Relationship='Unmarriedand Occupation='Adm-clerical，該葉節(jié)點(diǎn)包含事例672個f其中正例16個、負(fù)例656個、缺失值0個），計(jì)算預(yù)測概率時(shí)，正例數(shù)量和負(fù)例數(shù)量都會加1（目的是通過這種調(diào)整使模型可以適當(dāng)?shù)靥幚砣笔е担?，因此正例預(yù)測概率=（16+1）/（672+2）=2.52%，負(fù)例預(yù)測概率=（656+1）/（672+2）=97.48%。由于負(fù)例預(yù)測概率高于正例預(yù)測概率，因此該節(jié)點(diǎn)的預(yù)測值為負(fù)例（即“<=50K”）。該節(jié)點(diǎn)中占2.52%的正例被錯誤地預(yù)測成了負(fù)例，而其中占97.48%的負(fù)例都預(yù)測正確，可以下鉆瀏覽這些事例。

5評估

首先通過提升圖、分類矩陣等工具將模型應(yīng)用于測試數(shù)據(jù)集，來評估模型預(yù)測正確性。最后需從業(yè)務(wù)角度對模型進(jìn)行評估，確認(rèn)達(dá)到業(yè)務(wù)需求。

5.1提升圖

提升圖顯示對可預(yù)測列進(jìn)行預(yù)測的執(zhí)行情況。圖4顯示了對可預(yù)測列所有狀態(tài)進(jìn)行預(yù)測的執(zhí)行情況。

提升圖顯示兩條線，分別代表理想模型和實(shí)際模型。為了將實(shí)際模型和理想模型進(jìn)行對比，首先將測試數(shù)據(jù)集中所有事例都按預(yù)測概率從大到小排序，然后從預(yù)測概率大的一端抽取數(shù)據(jù)，逐步增加抽取數(shù)據(jù)的比例（X軸），同步檢查其中正確預(yù)測的比例（Y軸）。對于理想模型，預(yù)測總是正確的，所以提升圖是一條45度的直線;對于實(shí)際模型，隨著抽取數(shù)據(jù)比例的增加，預(yù)測概率逐步降低，因此和理想模型相比，提升圖逐步向下彎曲，和理想模型直線之間的距離愈來越遠(yuǎn)。提升圖使用“分?jǐn)?shù)”表示模型預(yù)測性能，分?jǐn)?shù)越高，模型預(yù)測性能越好，提升圖越接近理想模型，本文提升圖分?jǐn)?shù)為0.93。

圖中當(dāng)前度量位置位于總體50%處，此處理想模型的總體正確百分比是50%（預(yù)測完全正確），實(shí)際模型為48.87%。當(dāng)將度量位置移動到100%處時(shí)，理想模型的總體正確百分比是也100%，而實(shí)際模型為85.65%。

5.2分類矩陣

分類矩陣（或稱混淆矩陣）通過比較預(yù)測值與實(shí)際值的匹配情況，將所有事例分為不同類別并分類計(jì)數(shù)。本案例的分類矩陣見圖5。計(jì)算典型分類指標(biāo)：

上述指標(biāo)顯示本文模型對負(fù)例的預(yù)測能力高于正例，總體預(yù)測準(zhǔn)確率為85.65%。

有些指標(biāo)往往是有沖突的（如RecMl和Precision），選用什么評價(jià)指標(biāo)取決于業(yè)務(wù)需求。如需盡可能找到正例，選RecMl;如需盡可能避免將負(fù)例預(yù)測為正例，則選Precision;如果以上兩種要求重要性沒有明顯差別，則Accuracy和F1越高越好。

6部署

挖掘模型評估通過后，即可部署使用了。模型有多種使用方式，例如通過DMX將模型應(yīng)用于新數(shù)據(jù)創(chuàng)建預(yù)測、分析人員直接分析模型包含的規(guī)則提出方案建議。模型經(jīng)常被嵌入到應(yīng)用程序中，成為業(yè)務(wù)過程的組成部分。

隨著時(shí)間的推移，數(shù)據(jù)的分布在發(fā)生變化，輸入列和可預(yù)測列之間的關(guān)系也在發(fā)生變化，因此模型的預(yù)測精度將降低，需不斷對模型進(jìn)行監(jiān)控、維護(hù)和更新。

7結(jié)論

本文遵循CRISP-DM標(biāo)準(zhǔn)的指引，在Microsoft BI技術(shù)框架下為UCl分類數(shù)據(jù)集Adult建立了基于Microsoft決策樹的挖掘模型，并對決策樹模型、提升圖、分類矩陣等重要圖表工具進(jìn)行了探索。實(shí)驗(yàn)表明，SQL Server數(shù)據(jù)挖掘易于使用、性能良好，并能和SSIS等同類BI技術(shù)很好地協(xié)同工作，所創(chuàng)建的挖掘模型的準(zhǔn)確率（Accuracv）達(dá)到了85.65%，這和Adult數(shù)據(jù)集描述文檔中記錄的實(shí)驗(yàn)準(zhǔn)確率是一致的（例如C4.5算法是84.46%）。