决策树模型查询示例

项目
12/23/2023

适用于：SQL Server 2019 及更早版本的 Analysis Services Azure Analysis Services Fabric/Power BI Premium

重要

数据挖掘在 SQL Server 2017 Analysis Services 中已弃用，现在在 SQL Server 2022 Analysis Services 中已停止使用。对于已弃用和停止使用的功能，文档不会更新。若要了解详细信息，请参阅 Analysis Services 后向兼容性。

在创建针对数据挖掘模型的查询时，您既可以创建内容查询，也可以创建预测查询。内容查询提供有关分析过程中发现的模式的详细信息，而预测查询则使用模型中的模式对新数据进行预测。例如，决策树模型的内容查询可能提供有关树在每个级别上的事例数的统计信息或者区分事例的规则。而预测查询则是将模型映射到新数据，以生成建议、分类等等。您还可以使用查询来检索有关模型的元数据。

本部分介绍如何为基于 Microsoft 决策树算法的模型创建查询。

内容查询

通过数据挖掘架构行集检索模型参数

使用 DMX 获取与模型中的树有关的详细信息

预测查询

查找有关决策树模型的信息

若要针对决策树模型内容创建有意义的查询，您应该了解模型内容的结构以及哪些节点类型存储哪类信息。有关详细信息，请参阅决策树模型的挖掘模型内容 (Analysis Services - 数据挖掘) 。

示例查询 1：从数据挖掘架构行集中检索模型参数

通过查询数据挖掘架构行集，您可以找到有关模型的元数据，如模型创建时间、上次处理模型的时间、模型基于的挖掘结构的名称以及用作可预测属性的列的名称。您还可以返回首次创建模型时所使用的参数。

select MINING_PARAMETERS   
from $system.DMSCHEMA_MINING_MODELS  
WHERE MODEL_NAME = 'TM_Decision Tree'

示例结果：

MINING_PARAMETERS

COMPLEXITY_PENALTY=0.5, MAXIMUM_INPUT_ATTRIBUTES=255,MAXIMUM_OUTPUT_ATTRIBUTES=255,MINIMUM_SUPPORT=10,SCORE_METHOD=4,SPLIT_METHOD=3,FORCE_REGRESSOR=

示例查询 2：通过使用 DMX 返回有关模型内容的详细信息

下面的查询返回有关在 Basic Data Mining Tutorial中生成模型时创建的决策树的一些基本信息。每个树状结构都存储在自己的节点中。由于此模型包含单个可预测属性，因此只有一个树节点。但是，如果使用决策树算法创建关联模型，则可能有成百上千的树，每种产品对应一个树。

此查询返回类型 2 的所有节点，这些节点是表示特定可预测属性的树的顶级节点。

注意

必须将 CHILDREN_CARDINALITY 列括在括号中，以便将它与同名的 MDX 保留关键字区分开来。

SELECT MODEL_NAME, NODE_NAME, NODE_CAPTION,   
NODE_SUPPORT, [CHILDREN_CARDINALITY]  
FROM TM_DecisionTrees.CONTENT  
WHERE NODE_TYPE = 2

示例结果：

MODEL_NAME	NODE_NAME	NODE_CAPTION	NODE_SUPPORT	CHILDREN_CARDINALITY
TM_DecisionTree	000000001	全部	12939	5

这些结果是什么意思？在决策树模型中，特定节点的基数指明该节点有多少个直属子级。此节点的基数是 5，这意味着该模型将潜在的自行车购买者的目标总体分成了 5 个子组。

下面的相关查询返回这 5 个子组的子级以及子节点中属性和值的分布。由于统计信息（如支持、概率和方差）存储在嵌套表 NODE_DISTRIBUTION 中，因此本示例使用 FLATTENED 关键字输出嵌套表列。

注意

必须将嵌套表列 SUPPORT 括在括号中，以便将它与同名保留关键字区分开来。

SELECT FLATTENED NODE_NAME, NODE_CAPTION,  
(SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT]  
FROM NODE_DISTRIBUTION) AS t  
FROM TM_DecisionTree.CONTENT  
WHERE [PARENT_UNIQUE_NAME] = '000000001'

示例结果：

NODE_NAME	NODE_CAPTION	T.ATTRIBUTE_NAME	T.ATTRIBUTE_VALUE	Support
00000000100	Number Cars Owned = 0	Bike Buyer	Missing	0
00000000100	Number Cars Owned = 0	Bike Buyer	0	1067
00000000100	Number Cars Owned = 0	Bike Buyer	1	1875
00000000101	Number Cars Owned = 3	Bike Buyer	Missing	0
00000000101	Number Cars Owned = 3	Bike Buyer	0	678
00000000101	Number Cars Owned = 3	Bike Buyer	1	473

根据这些结果，你可以知道购买了自行车的客户数 ([Bike Buyer] = 1)，1067 个客户拥有 0 辆汽车，473 个客户拥有 3 辆汽车。

示例查询 3：从模型中检索子树

假定您希望查找有关购买了自行车的客户的更多信息。可以使用查询中的 IsDescendant (DMX) 函数查看任何子树的其他详细信息，如以下示例所示。通过从包含 42 岁以上的客户的树中检索叶节点 (NODE_TYPE = 4)，查询将返回自行车购买者的计数。查询将嵌套表中的行限定为其中 Bike Buyer = 1 的行。

SELECT FLATTENED NODE_NAME, NODE_CAPTION,NODE_TYPE,  
(  
SELECT [SUPPORT] FROM NODE_DISTRIBUTION WHERE ATTRIBUTE_NAME = 'Bike Buyer' AND ATTRIBUTE_VALUE = '1'  
) AS t  
FROM TM_DecisionTree.CONTENT  
WHERE ISDESCENDANT('0000000010001')  
AND NODE_TYPE = 4

示例结果：

NODE_NAME	NODE_CAPTION	t.SUPPORT
000000001000100	年收入 >= 26000 和 < 42000	266
00000000100010100	Total Children = 3	75
0000000010001010100	Number Children At Home = 1	75

使用决策树模型进行预测

由于决策树可用于各种任务（包括分类、回归以及关联），因此，在针对决策树模型创建预测查询时，您可以使用许多选项。必须了解创建模型的目的才能明白预测的结果。以下查询示例说明了三种不同的情况：

返回分类模型的预测以及正确预测的概率，然后根据该概率筛选结果；
创建一个单独查询来预测关联；
检索部分决策树的回归公式，其中输入和输出之间的关系是线性的。

示例查询 4：返回具有概率的预测

下面的示例查询使用在 Basic Data Mining Tutorial中创建的决策树模型。查询从表 dbo 传入一组新的示例数据。AdventureWorks2012 DW 中的潜在购买者，用于预测新数据集中的哪些客户将购买自行车。

该查询使用预测函数 PredictHistogram (DMX) ，该函数返回一个嵌套表，其中包含有关模型发现的概率的有用信息。该查询的最后的 WHERE 子句将筛选结果，以便仅返回预测购买自行车的概率大于 0% 的那些潜在客户。

SELECT  
  [TM_DecisionTree].[Bike Buyer],  
  PredictHistogram([Bike Buyer]) as Results  
From  
  [TM_DecisionTree]  
PREDICTION JOIN  
  OPENQUERY([Adventure Works DW Multidimensional 2012],  
    'SELECT  
      [FirstName],  
      [LastName],  
      [MaritalStatus],  
      [Gender],  
      [YearlyIncome],  
      [TotalChildren],  
      [NumberChildrenAtHome],  
      [HouseOwnerFlag],  
      [NumberCarsOwned]  
    FROM  
      [dbo].[ProspectiveBuyer]  
    ') AS t  
ON  
  [TM_DecisionTree].[First Name] = t.[FirstName] AND  
  [TM_DecisionTree].[Last Name] = t.[LastName] AND  
  [TM_DecisionTree].[Marital Status] = t.[MaritalStatus] AND  
  [TM_DecisionTree].[Gender] = t.[Gender] AND  
  [TM_DecisionTree].[Yearly Income] = t.[YearlyIncome] AND  
  [TM_DecisionTree].[Total Children] = t.[TotalChildren] AND  
  [TM_DecisionTree].[Number Children At Home] = t.[NumberChildrenAtHome] AND  
  [TM_DecisionTree].[House Owner Flag] = t.[HouseOwnerFlag] AND  
  [TM_DecisionTree].[Number Cars Owned] = t.[NumberCarsOwned]  
WHERE [Bike Buyer] = 1  
AND PredictProbability([Bike Buyer]) >'.05'

默认情况下，SQL Server Analysis Services返回带有列标签 Expression 的嵌套表。可通过对返回的列使用别名来更改此标签。如果使用别名，则该别名（本例中为 Results）将用作嵌套表中的列标题和值。必须展开嵌套表才能查看结果。

Bike Buyer = 1 的示例结果：

Bike Buyer	$SUPPORT	$PROBABILITY	$ADJUSTEDPROBABILITY
1	2540	0.634849242045644	0.013562168281562
0	1460	0.364984174579377	0.00661336932550915
	0	0.000166583374979177	0.000166583374979177

如果提供程序不支持分层行集（如此处显示的结果），则可以在查询中使用 FLATTENED 关键字将结果返回为包含 Null（替代重复的列值）的表。有关详细信息，请参阅嵌套表 (Analysis Services - 数据挖掘) 或了解 DMX Select 语句。

示例查询 5：根据决策树模型预测关联

下面的示例查询基于关联挖掘结构。为了按照此示例内容进行操作，您可以在此挖掘结构中添加一个新模型，并且选择 Microsoft 决策树作为算法。有关如何创建关联挖掘结构的详细信息，请参阅第 3 课：构建市场篮方案 (中间数据挖掘教程) 。

以下示例查询是一个单一查询，可以通过选择字段，然后从下拉列表中选择这些字段的值，在SQL Server Data Tools轻松创建该查询。

SELECT PredictAssociation([DT_Association].[v Assoc Seq Line Items],3)  
FROM  
  [DT_Association]  
NATURAL PREDICTION JOIN  
(SELECT (SELECT 'Patch kit' AS [Model]) AS [v Assoc Seq Line Items]) AS t

预期的结果：

型号
Mountain-200
Mountain Tire Tube
Touring Tire Tube

结果告诉您要向已购买 Patch Kit 产品的客户推荐的三款最佳产品。也可以在您进行推荐时提供多款产品作为输入，方法是键入值，或使用 “单独查询输入” 对话框，然后添加或删除值。以下示例查询演示如何提供用来进行预测的多个值。这些值用定义输入值的 SELECT 语句中的 UNION 子句来连接。

SELECT PredictAssociation([DT_Association].[v Assoc Seq Line Items],3)  
From  
  [DT_Association]  
NATURAL PREDICTION JOIN  
(SELECT (SELECT 'Racing Socks' AS [Model]  
  UNION SELECT 'Women''s Mountain Shorts' AS [Model]) AS [v Assoc Seq Line Items]) AS t

预期的结果：

型号
Long-Sleeve Logo Jersey
Mountain-400-W
Classic Vest

示例查询 6：从决策树模型中检索回归公式

在创建包含连续属性的回归的决策树模型时，可以使用回归公式进行预测，也可以提取有关回归公式的信息。有关回归模型的查询的详细信息，请参阅线性回归模型查询示例。

如果决策树模型由回归节点和根据离散属性或范围进行拆分的节点混合组成，则可以创建仅返回回归节点的查询。 NODE_DISTRIBUTION 表包含回归公式的详细信息。在本示例中，对列进行了平展，对 NODE_DISTRIBUTION 表使用了别名，以便于查看。但在此模型中，找不到将 Income 与其他连续属性相关联的回归量。在这种情况下，SQL Server Analysis Services返回特性的平均值和该属性的模型中的总方差。

SELECT FLATTENED NODE_DISTRIBUTION AS t  
FROM DT_Predict. CONTENT  
WHERE NODE_TYPE = 25

示例结果：

T.ATTRIBUTE_NAME	t.ATTRIBUTE_VALUE	t.SUPPORT	t.PROBABILITY	t.VARIANCE	t.VALUETYPE
Yearly Income	Missing	0	0.000457142857142857	0	1
Yearly Income	57220.8876687257	17484	0.999542857142857	1041275619.52776	3
	57220.8876687257	0	0	1041216662.54387	11

有关回归模型中使用的值类型和统计信息的详细信息，请参阅线性回归模型的挖掘模型内容 (Analysis Services - 数据挖掘) 。

预测函数的列表

所有 Microsoft 算法都支持一组通用函数。但是，Microsoft 决策树算法支持下表中列出的其他函数。

预测函数	使用情况
IsDescendant (DMX)	确定一个节点是否是模型中另一个节点的子节点。
IsInNode (DMX)	指示指定的节点是否包含当前事例。
PredictAdjustedProbability (DMX)	返回加权的概率。
PredictAssociation (DMX)	预测关联数据集中的成员身份。
PredictHistogram (DMX)	返回与当前预测值相关的值的表。
PredictNodeId (DMX)	返回每个事例的 Node_ID。
PredictProbability (DMX)	返回预测值的概率。
PredictStdev (DMX)	返回指定列的预测标准偏差。
PredictSupport (DMX)	返回指定状态的支持值。
PredictVariance (DMX)	返回指定列的方差。

有关所有 Microsoft 算法通用的函数的列表，请参阅常规预测函数 (DMX) 。有关特定函数的语法，请参阅 DMX (数据挖掘扩展) 函数参考。

另请参阅

数据挖掘查询
 Microsoft 决策树算法
 Microsoft 决策树算法技术参考
 决策树模型的挖掘模型内容（Analysis Services - 数据挖掘）

通过

决策树模型查询示例

查找有关决策树模型的信息

示例查询 1：从数据挖掘架构行集中检索模型参数

示例查询 2：通过使用 DMX 返回有关模型内容的详细信息

示例查询 3：从模型中检索子树

使用决策树模型进行预测

示例查询 4：返回具有概率的预测

示例查询 5：根据决策树模型预测关联

示例查询 6：从决策树模型中检索回归公式

预测函数的列表

另请参阅

反馈

其他资源