Примеры запросов модели дерева принятия решений

Применимо к: SQL Server 2019 и более ранних версий Analysis Services Azure Analysis Services Fabric/Power BI Premium

При создании запроса к модели интеллектуального анализа данных можно создать запрос содержимого, который содержит сведения о шаблонах, обнаруженных в анализе, или создать прогнозирующий запрос, который использует шаблоны в модели для прогнозирования новых данных. Например, запрос содержимого для модели дерева принятия решений может содержать статистику о количестве случаев на каждом уровне дерева или правилах, которые различают варианты. Кроме того, прогнозирующий запрос сопоставляет модель с новыми данными для создания рекомендаций, классификаций и т. д. Вы также можете получить метаданные о модели с помощью запроса.

В этом разделе объясняется, как создавать запросы для моделей, основанных на алгоритме деревьев принятия решений Майкрософт.

Запросы содержимого

Получение параметров модели из набора строк схемы интеллектуального анализа данных

Получение сведений о деревьях в модели с помощью 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

Следующий запрос возвращает некоторые основные сведения о деревьях принятия решений, созданных при создании модели в учебнике по интеллектуальному анализу данных уровня "Базовый". Каждая структура дерева хранится в собственном узле. Так как эта модель содержит один прогнозируемый атрибут, существует только один узел дерева. Однако при создании модели сопоставления с помощью алгоритма дерева принятия решений может быть сотни деревьев, по одному для каждого продукта.

Этот запрос возвращает все узлы типа 2, которые являются узлами верхнего уровня дерева, представляющего определенный прогнозируемый атрибут.

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

Пример результатов:

Что эти результаты говорят вам? В модели дерева решений кардинальность определенного узла указывает, сколько прямых потомков у этого узла. Кратность для этого узла составляет 5, что означает, что модель разделяла целевую популяцию потенциальных покупателей велосипедов на 5 подгрупп.

Следующий связанный запрос возвращает дочерние элементы для этих пяти подгрупп, а также распределение атрибутов и значений в дочерних узлах. Так как статистические данные, такие как поддержка, вероятность и дисперсия, хранятся в вложенной таблице, NODE_DISTRIBUTION, в этом примере используется FLATTENED ключевое слово для вывода вложенных столбцов таблицы.

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'  

Пример результатов:

Из этих результатов вы можете заключить, что среди клиентов, которые купили велосипед ([Покупатель велосипеда] = 1), у 1067 из них не имели автомобилей, а у 473 было 3 автомобиля.

Пример запроса 3. Получение поддерев из модели

Предположим, вы хотели узнать больше о клиентах, которые купили велосипед. Для просмотра дополнительных сведений о любом из поддеревьев можно использовать функцию IsDescendant (DMX) в запросе, как показано в следующем примере. Запрос возвращает количество покупателей велосипедов, извлекая конечные узлы (NODE_TYPE = 4) из дерева, содержащего клиентов, которые старше 42 лет. Запрос ограничивает строки из вложенной таблицы тем, где 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  

Пример результатов:

Создание прогнозов с помощью модели деревьев принятия решений

Так как деревья принятия решений можно использовать для различных задач, включая классификацию, регрессию и даже связь, при создании прогнозирующего запроса на модель дерева принятия решений у вас есть множество вариантов. Необходимо понять назначение, для которого была создана модель, чтобы понять результаты прогнозирования. В следующих примерах запросов показаны три различных сценария:

• Возврат прогноза для модели классификации вместе с вероятностью правильности прогнозирования, а затем фильтрация результатов по вероятности;

• Создание однотонного запроса для прогнозирования связей;

• Получение формулы регрессии для части дерева принятия решений, где связь между входными и выходными данными является линейной.

Пример запроса 4. Возврат прогнозов с вероятностями

В следующем примере запроса используется модель дерева принятия решений, созданная в учебнике по интеллектуальному анализу данных уровня "Базовый". Запрос передает новый набор примеров данных из таблицы dbo.ProspectiveBuyers в 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. Вы можете изменить эту метку, указав псевдоним возвращаемого столбца. При этом псевдоним (в данном случае результаты) используется как заголовок столбца, так и как значение в вложенной таблице. Чтобы просмотреть результаты, необходимо развернуть вложенную таблицу.

Пример результатов с покупателем велосипедов = 1:

Если поставщик не поддерживает иерархические наборы строк, например приведенные здесь, можно использовать ключевое слово FLATTENED в запросе для возврата результатов в виде таблицы, содержащей значения NULL вместо повторяющихся значений столбцов. Дополнительные сведения см. в разделе "Вложенные таблицы" (Analysis Services — интеллектуальный анализ данных) или в разделе "Общие сведения о операторе DMX Select".

Пример запроса 5. Прогнозирование ассоциаций из модели деревьев принятия решений

Следующий пример запроса основан на структуре ассоциативного анализа. Чтобы следовать этому примеру, можно добавить новую модель в эту структуру интеллектуального анализа данных и выбрать деревья принятия решений Майкрософт в качестве алгоритма. Дополнительные сведения о создании структуры интеллектуального анализа данных ассоциации см. в уроке 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  

Ожидаемые результаты:

Результаты сообщают вам три лучших продукта, которые рекомендуют клиентам, которые приобрели продукт Пакета исправлений. Вы также можете предоставить несколько продуктов в качестве входных данных при выполнении рекомендаций, введя значения или используя диалоговое окно "Входные данные однотонного запроса " и добавляя или удаляя значения. В следующем примере запроса показано, как предоставляются несколько значений, на которых выполняется прогнозирование. Значения связаны предложением UNION в инструкции SELECT, определяющей входные значения.

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  

Ожидаемые результаты:

Пример запроса 6. Получение формулы регрессии из модели деревьев принятия решений

При создании модели дерева принятия решений, содержащей регрессию в непрерывном атрибуте, можно использовать формулу регрессии для прогнозирования или извлечь сведения о формуле регрессии. Дополнительные сведения о запросах на модели регрессии см. в примерах запросов модели линейной регрессии.

Если модель дерева принятия решений содержит смесь узлов регрессии и узлов, разделенных на дискретные атрибуты или диапазоны, можно создать запрос, возвращающий только узел регрессии. Таблица NODE_DISTRIBUTION содержит сведения о формуле регрессии. В этом примере столбцы сглажены, а таблица NODE_DISTRIBUTION имеет псевдоним для упрощения просмотра. Однако в этой модели не нашли регрессоры, связывающие доход с другими непрерывными атрибутами. В таких случаях SQL Server Analysis Services возвращает среднее значение атрибута и общую дисперсию атрибута в модели.

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

Пример результатов:

Дополнительные сведения о типах значений и статистике, используемых в моделях регрессии, см. в разделе Содержимое модели линейной регрессии (Analysis Services - Data Mining).

Список функций прогнозирования

Все алгоритмы Майкрософт поддерживают общий набор функций. Однако алгоритм дерева принятия решений Майкрософт поддерживает дополнительные функции, перечисленные в следующей таблице.

Список функций, которые являются общими для всех алгоритмов Майкрософт, см. в разделе "Общие функции прогнозирования" (DMX). Сведения о синтаксисе конкретных функций см. в справочнике по расширению интеллектуального анализа данных (DMX).

См. также

Запросы интеллектуального анализа данных
Алгоритм деревьев принятия решений Майкрософт
Технический справочник по алгоритму деревьев принятия решений Майкрософт
Содержимое модели интеллектуального анализа данных для моделей дерева принятия решений (службы Analysis Services — интеллектуальный анализ данных)