DMX-SQL SERVER 数据挖掘简介一

SQL SEVER中， DMX可以用于：

1 SSMS

2 SSDT

3 EXCEL DATAMINGING ADDIN

 

基本句法如下：

创建STRUCTURE

CREATE MINING STRUCTURE [People1]
(
[CustID] LONG KEY,
[Name] TEXT DISCRETE,
[Gender] TEXT DISCRETE,
[Age] LONG CONTINUOUS,
[CarMake] TEXT DISCRETE,
[CarModel] TEXT DISCRETE
)

创建MODEL

ALTER MINING STRUCTURE [People]
ADD MINING MODEL [PredictGender-Tree]
(
[CustID],
[Gender] PREDICT,
[Age],
[CarModel]
) USING Microsoft Decision Trees

进行TRAIN

INSERT INTO MINING STRUCTURE [People]
([CustID], [Name], [Gender], [Age], [CarMake],[CarModel])
OPENQUERY(Chapter3Data,
’SELECT [Key], Name, Gender, Age, CarMake, CarModel
FROM People’)

查询STRUCTURE

SELECT * FROM MINING STRUCTURE People.CASES WHERE IsTestCase()

查询MODEL

SELECT * FROM ClusterDrillthrough.CASES WHERE IsInNode(’001’)

 

预测查询

SELECT t.[Name], Predict([Gender]) AS PredictedGender
FROM [PredictGender-Bayes]
PREDICTION JOIN
OPENQUERY(Chapter3Data,
’SELECT [Key], [Name], [Age], [CarModel]
FROM [NewPeople]’) AS t
ON [PredictGender-Bayes].[Age] = t.[Age] AND
[PredictGender-Bayes].[CarModel] = t.[CarModel]