Compartilhar via

Exemplos de consulta de modelo de árvores de decisão

  • Artigo

Aplica-se a: SQL Server 2019 e anteriores do Analysis Services Azure Analysis Services Fabric/Power BI Premium

Importante

A mineração de dados foi preterida no SQL Server 2017 Analysis Services e agora foi descontinuada no SQL Server 2022 Analysis Services. A documentação não foi atualizada para recursos preteridos e descontinuados. Para saber mais, confira Compatibilidade com versões anteriores do Analysis Services.

Ao criar uma consulta para um modelo de mineração de dados, você pode criar uma consulta de conteúdo que fornece detalhes de padrões encontrados em análises ou uma consulta de previsão que usa os padrões no modelo para fazer previsões para novos dados. Por exemplo, uma consulta de conteúdo para um modelo de árvores de decisão pode fornecer estatísticas sobre o número de casos de cada nível da árvore ou as regras que diferenciam os casos. Como alternativa, uma consulta de previsão mapeia o modelo para novos dados para gerar recomendações, classificações e assim sucessivamente. Você também pode recuperar metadados sobre o modelo usando uma consulta.

Esta seção explica como criar consultas para modelos baseados no algoritmo Árvores de Decisão da Microsoft.

Consultas de conteúdo

Recuperando parâmetros de modelo do conjunto de linhas do esquema de mineração de dados

Obtendo detalhes sobre árvores no modelo através do DMX

Recuperando subárvores do modelo

Consultas de previsão

Retornando previsões com probabilidades

Prevendo associações de um modelo de árvores de decisão

Recuperando uma fórmula de regressão de um modelo de árvores de decisão

Localizando informações sobre o modelo de árvores de decisão

Para criar consultas significativas no conteúdo de um modelo de árvores de decisão, você deve compreender a estrutura do conteúdo do modelo e quais tipos de nós armazenam quais tipos de informações. Para obter mais informações, consulte Conteúdo do modelo de mineração para modelos de árvore de decisão (Analysis Services – Mineração de dados).

Exemplo de consulta 1: recuperando parâmetros do conjunto de linhas do esquema de mineração de dados

Ao consultar um conjunto de linhas de esquema de mineração de dados, você pode encontrar metadados sobre o modelo, tais como quando ele foi criado, última vez em que foi processado, o nome da estrutura de mineração na qual o modelo é baseado e o nome da coluna usada como atributo previsível. Você também pode retornar os parâmetros que foram usados quando o modelo foi criado.

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

Resultados do exemplo:

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=

Consulta de exemplo 2: retornando detalhes sobre o conteúdo do modelo com o uso do DMX

A consulta a seguir retorna algumas informações básicas sobre as árvores de decisão que foram criadas quando o modelo foi construído no Tutorial de mineração de dados básico. Cada estrutura de árvore é armazenada em seu próprio nó. Como esse modelo contém um único atributo previsível, há só um nó de árvore. Entretanto, se você criar um modelo de associação usando o algoritmo Árvores de Decisão, pode haver centenas de árvores, uma para cada produto.

Essa consulta retorna todos os nós de tipo 2, que são os nós superiores de uma árvore que representa um atributo previsível particular.

Observação

A coluna CHILDREN_CARDINALITY deve estar entre parênteses para distingui-la da palavra-chave reservada MDX de mesmo nome.

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

Resultados do exemplo:

MODEL_NAME NODE_NAME NODE_CAPTION NODE_SUPPORT CHILDREN_CARDINALITY
TM_DecisionTree 000000001 Tudo 12939 5

O que esses resultados significam para você? Em um modelo de árvores de decisão, a cardinalidade de um nó específico indica quantos filhos imediatos aquele nó contém. A cardinalidade desse nó é 5, o que indica que o modelo dividiu a população alvo de consumidores de bicicleta em 5 subgrupos.

A consulta relacionada a seguir retorna os filhos desses cinco subgrupos, juntamente com a distribuição dos atributos e valores nos nós filho. Como as estatísticas, como suporte, probabilidade e variância, são armazenadas na tabela aninhada, NODE_DISTRIBUTION, esse exemplo usa a palavra-chave FLATTENED para gerar as colunas da tabela aninhada.

Observação

A coluna da tabela aninhada, SUPPORT, deve estar entre parênteses para distingui-la da palavra-chave reservada de mesmo nome.

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'

Resultados do exemplo:

NODE_NAME NODE_CAPTION T.ATTRIBUTE_NAME T.ATTRIBUTE_VALUE SUPPORT
00000000100 Número de Carros = 0 Bike Buyer Ausente 0
00000000100 Número de Carros = 0 Bike Buyer 0 1067
00000000100 Número de Carros = 0 Bike Buyer 1 1875
00000000101 Número de Carros = 3 Bike Buyer Ausente 0
00000000101 Número de Carros = 3 Bike Buyer 0 678
00000000101 Número de Carros = 3 Bike Buyer 1 473

Com esses resultados, é possível afirmar que dos clientes que compraram uma bicicleta ([Bike Buyer] = 1), 1067 clientes não tinham carro e 473 clientes tinham 3 carros.

Exemplo de consulta 3: recuperando subárvores do modelo

Suponha que você queira saber mais sobre os clientes que compraram uma bicicleta. Você pode exibir detalhes adicionais para qualquer uma das subárvores usando a função IsDescendant (DMX) na consulta, conforme mostrado no exemplo a seguir. A consulta retorna o cálculo dos compradores de bicicletas recuperando os nós folha (NODE_TYPE = 4) da árvore que contém os clientes acima de 42 anos de idade. A consulta restringe linhas da tabela aninhada para aquelas onde Comprador de Bicicleta = 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

Resultados do exemplo:

NODE_NAME NODE_CAPTION t.SUPPORT
000000001000100 Renda >Anual = 26000 e < 42000 266
00000000100010100 Total de Filhos = 3 75
0000000010001010100 Número de Crianças em Casa = 1 75

Fazendo previsões com o uso de um modelo de árvores de decisão

Como árvores de decisão podem ser usadas em várias tarefas, incluindo classificação, regressão e até mesmo associação, quando você cria uma consulta de previsão em um modelo de árvore de decisão, há muitas opções disponíveis. Você deve entender o propósito para o qual o modelo foi criado para entender os resultados da previsão. Os exemplos de consulta a seguir mostram três cenários diferentes:

  • Retornar uma previsão para um modelo de classificação, juntamente com a probabilidade de a previsão estar correta, e depois filtrar os resultados por probabilidade;

  • Criar uma consulta singleton para prever associações;

  • Recuperar a fórmula de regressão para parte de uma árvore de decisão onde a relação entre a entrada e a saída é linear.

Exemplo de consulta 4: retornando previsões com probabilidades

O exemplo de consulta a seguir usa o modelo de árvore de decisão criado no Tutorial de mineração de dados básico. A consulta passa em um novo conjunto de dados de exemplo, do dbo da tabela. ProspectiveBuyers no AdventureWorks2012 DW, para prever qual dos clientes no novo conjunto de dados comprará uma bicicleta.

A consulta usa a função de previsão PredictHistogram (DMX), que retorna uma tabela aninhada que contém informações úteis sobre as probabilidades descobertas pelo modelo. A cláusula WHERE final da consulta filtra os resultados para retornar apenas os clientes que foram previstos como prováveis compradores de uma bicicleta, com uma probabilidade maior que 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'

Por padrão, SQL Server Analysis Services retorna tabelas aninhadas com o rótulo de coluna Expression. Você pode alterar esse nome no alias da coluna que é retornada. Se fizer isso, o alias (nesse caso, Resultados) será usado no título da coluna e também como valor da tabela aninhada. Você deve expandir a tabela aninhada para verificar os resultados.

Exemplo de resultados com Bike Buyer = 1:

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

Se o seu provedor não der suporte a conjuntos de linhas hierárquicos, como os resultados exibidos aqui, você poderá utilizar a palavra-chave FLATTENED na consulta para retornar os resultados em uma tabela que contenha nulos em vez de valores de coluna repetidos. Para obter mais informações, consulte Tabelas aninhadas (Analysis Services – Data Mining) ou Noções básicas sobre a instrução DMX Select.

Exemplo de consulta 5: prevendo associações de um modelo de árvore de decisão

O exemplo de consulta a seguir é baseado na estrutura de mineração Associação. Para continuar com este exemplo, você pode adicionar um novo modelo a essa estrutura de mineração e selecionar Árvores de Decisão da Microsoft como o algoritmo. Para obter mais informações sobre como criar a estrutura de mineração de associação, consulte Lição 3: criando um cenário de cesta de mercado (Tutorial de mineração de dados intermediário).

A consulta de exemplo a seguir é uma consulta singleton, que você pode criar facilmente em SQL Server Data Tools escolhendo campos e selecionando valores para esses campos em uma lista suspensa.

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

Resultados esperados:

Modelar
Mountain-200
Mountain Tire Tube
Tubo de pneu para passeio

Os resultados indicam os três melhores produtos para serem recomendados a clientes que compraram o produto Patch Kit. Você também pode fornecer vários produtos como entrada ao fazer suas recomendações, tanto digitando os valores como usando a caixa de diálogo Entrada de Consulta Singleton e adicionando ou removendo valores. A consulta de exemplo a seguir mostra como vários valores são fornecidos, sob os quais uma previsão é feita. Os valores são conectados por uma cláusula UNION na instrução SELECT que define os valores de entrada.

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

Resultados esperados:

Modelar
Jersey Logo de manga longa
Mountain-400-W
Colete clássico

Exemplo de consulta 6: recuperando uma fórmula de regressão de um modelo de árvores de decisão

Ao criar um modelo de árvore de decisão que contém uma regressão em um atributo contínuo, você pode usar a fórmula de regressão para fazer previsões ou pode extrair informações precisas sobre a fórmula. Para obter mais informações sobre consultas em modelos de regressão, consulte Exemplos de consulta de modelo de regressão linear.

Se um modelo de árvores de decisão contiver uma mistura de nós de regressão e esses nós se dividirem em atributos discretos ou intervalos, você poderá criar uma consulta que retorne apenas o nó de regressão. A tabela NODE_DISTRIBUTION contém os detalhes da fórmula de regressão. Neste exemplo, as colunas são combinadas e a tabela NODE_DISTRIBUTION recebe um alias para facilitar a exibição. Porém, neste modelo, nenhum regressor foi localizado para relacionar Renda com outros atributos contínuos. Nesses casos, SQL Server Analysis Services retorna o valor médio do atributo e a variação total no modelo para esse atributo.

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

Resultados do exemplo:

T.ATTRIBUTE_NAME t.ATTRIBUTE_VALUE t.SUPPORT t.PROBABILITY t.VARIANCE t.VALUETYPE
Renda Anual Ausente 0 0.000457142857142857 0 1
Renda Anual 57220.8876687257 17484 0.999542857142857 1041275619.52776 3
57220.8876687257 0 0 1041216662.54387 11

Para obter mais informações sobre os tipos de valor e as estatísticas usadas em modelos de regressão, consulte Conteúdo do modelo de mineração para modelos de regressão linear (Analysis Services – Mineração de dados).

Lista de funções de previsão

Todos os algoritmos da Microsoft dão suporte a um conjunto comum de funções. No entanto, o algoritmo Árvores de Decisão da Microsoft dá suporte às funções adicionais listadas na tabela a seguir.

Função de previsão Uso
IsDescendant (DMX) Determina se um nó é um filho de outro nó no modelo.
IsInNode (DMX) Indica se o nó especificado contém o caso atual.
PredictAdjustedProbability (DMX) Retorna a probabilidade ponderada.
PredictAssociation (DMX) Prevê associação de membro em um conjunto de dados associativo.
PredictHistogram (DMX) Retorna uma tabela de valores relacionados ao valor previsto atual.
PredictNodeId (DMX) Retorna Node_ID para cada caso.
PredictProbability (DMX) Retorna a probabilidade para o valor previsto.
PredictStdev (DMX) Retorna o desvio padrão previsto para a coluna especificada.
PredictSupport (DMX) Retorna o valor de suporte para um estado especificado.
PredictVariance (DMX) Retorna a variação de uma coluna especificada.

Para obter uma lista das funções que são comuns a todos os algoritmos da Microsoft, consulte Funções de previsão geral (DMX). Para obter a sintaxe de funções específicas, consulte Referência de função DMX (Data Mining Extensions).

Consulte Também

Consultas de mineração de dados
Algoritmo Árvores de Decisão da Microsoft
Referência técnica do algoritmo Árvores de Decisão da Microsoft
Mining Model Content for Decision Tree Models (Analysis Services - Data Mining)