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%）。