Примеры запросов к модели кластеризации
Применимо к:
SQL Server 2019 и более ранних версий Analysis Services 
Azure Analysis Services Fabric/Power BI Premium
Важно!
Интеллектуальный анализ данных не рекомендуется использовать в службах SQL Server 2017 Analysis Services, а в SQL Server 2022 Analysis Services его поддержка прекращена. Документация не обновляется для устаревших и неподдерживаемых функций. Дополнительные сведения см. в статье об обратной совместимости служб Analysis Services.
С помощью запроса к модели интеллектуального анализа данных можно получать метаданные о модели, а также, с помощью запросов к содержимому, подробную информацию о закономерностях, обнаруженных при анализе. Кроме того, можно создавать прогнозирующие запросы, которые на основе закономерностей модели создают прогнозы новых данных. Разные типы запросов предоставляют различные виды информации. Например, запрос к содержимому предоставит дополнительные сведения о найденных кластерах, а прогнозирующий запрос определит, к какому кластеру с наибольшей вероятностью будет относиться новая точка данных.
В этом разделе объясняется, как создавать запросы для моделей, основанных на алгоритме кластеризации Майкрософт.
Запросы содержимого
Получение метаданных модели с помощью расширений интеллектуального анализа данных
Получение метаданных модели из набора строк схемы
Возврат кластера или список кластеров
Возврат профиля кластера с помощью системных хранимых процедур
Нахождение отличительных признаков кластеров
Возврат вариантов, принадлежащих кластеру
Прогнозирующие запросы
Прогноз результатов на основании модели кластеризации
Определение принадлежности к кластеру
Возврат всех возможных кластеров, вероятности и расстояния
Поиск сведений о модели
Все модели интеллектуального анализа данных возвращают содержимое, полученное алгоритмом, в соответствии со стандартизованной схемой — набором строк схемы модели интеллектуального анализа данных. Запросы к набору строк схемы модели интеллектуального анализа данных создаются при помощи инструкций расширений интеллектуального анализа данных. В SQL Server 2017 г. можно также запрашивать наборы строк схемы непосредственно в виде системных таблиц.
Образец запроса 1. Получение метаданных модели с помощью расширений интеллектуального анализа данных
Следующий запрос возвращает основные метаданные модели кластеризации, TM_Clustering, созданной при выполнении заданий из учебника по основам интеллектуального анализа данных. Метаданные, доступные в родительском узле модели кластеризации, включают в себя имя модели, базу данных, где она хранится, и количество дочерних узлов модели. В этом запросе для получения метаданных из родительского узла модели применяется DMX-запрос содержимого.
SELECT MODEL_CATALOG, MODEL_NAME, NODE_CAPTION,
NODE_SUPPORT, [CHILDREN_CARDINALITY], NODE_DESCRIPTION
FROM TM_Clustering.CONTENT
WHERE NODE_TYPE = 1
Примечание
Имя столбца CHILDREN_CARDINALITY следует заключить в квадратные скобки, чтобы отличить его от одноименного зарезервированного ключевого слова языка многомерных выражений.
Пример результатов:
| Строка | Метаданные |
|---|---|
| MODEL_CATALOG | TM_Clustering |
| MODEL_NAME | Adventure Works DW |
| NODE_CAPTION | Кластерная модель |
| NODE_SUPPORT | 12939 |
| CHILDREN_CARDINALITY | 10 |
| NODE_DESCRIPTION | Все |
Определение того, что означают эти столбцы в модели кластеризация, см. в разделе Содержимое модели интеллектуального анализа данных для моделей кластеризации (службы Analysis Services — интеллектуальный анализ данных).
Образец запроса 2. Получение метаданных модели из набора строк схемы
Выполнение запроса набора строк схемы интеллектуального анализа данных позволяет получить те же сведения, которые возвращаются DMX-запросом содержимого. Однако в наборе строк схемы содержатся и дополнительные столбцы. Они содержат параметры, которые использовались при создании модели, дату и время последней обработки модели и сведения о владельце модели.
В следующем запросе возвращаются даты создания, изменения и последней обработки модели, а также параметры кластеризации, которые использовались при построении модели, и размер обучающего набора. Эти сведения полезны для документирования модели и выяснения того, какие параметры кластеризации использовались при создании существующей модели.
SELECT MODEL_NAME, DATE_CREATED, LAST_PROCESSED, PREDICTION_ENTITY, MINING_PARAMETERS
from $system.DMSCHEMA_MINING_MODELS
WHERE MODEL_NAME = 'TM_Clustering'
Пример результатов:
| Строка | Метаданные |
|---|---|
| MODEL_NAME | TM_Clustering |
| DATE_CREATED | 10/12/2007 19:42:51 |
| LAST_PROCESSED | 10/12/2007 20:09:54 |
| PREDICTION_ENTITY | Покупатель велосипеда |
| MINING_PARAMETERS | CLUSTER_COUNT=10, CLUSTER_SEED=0, CLUSTERING_METHOD=1, MAXIMUM_INPUT_ATTRIBUTES=255, MAXIMUM_STATES=100, MINIMUM_SUPPORT=1, MODELLING_CARDINALITY=10, SAMPLE_SIZE=50000, STOPPING_TOLERANCE=10 |
Поиск сведений о кластерах
Самые полезные запросы к содержимому моделей кластеризации обычно возвращают сведения, которые можно просмотреть с помощью Средства просмотра кластеров. Сюда входят профили, характеристики и сравнение кластеров. В данном разделе будут приведены примеры запросов, получающих эти сведения.
Образец запроса 3. Возврат кластера или списка кластеров
Поскольку у всех кластеров тип узла — 5, список кластеров легко получить, выбрав из содержимого модели узлы именно этого типа. Возвращенные узлы можно также фильтровать по вероятности или по мощности несущего множества, как показано в данном примере.
SELECT NODE_NAME, NODE_CAPTION ,NODE_SUPPORT, NODE_DESCRIPTION
FROM TM_Clustering.CONTENT
WHERE NODE_TYPE = 5 AND NODE_SUPPORT > 1000
Пример результатов:
| Строка | Метаданные |
|---|---|
| NODE_NAME | 002 |
| NODE_CAPTION | Кластер 2 |
| NODE_SUPPORT | 1649 |
| NODE_DESCRIPTION | Английское образование=Степень выпускника , 32 <=Возраст <=48 , Число автомобилей, принадлежащих=0 , 35964.0771121808 <=Годовой доход <=97407.7163393957 , English Occupation=Professional , Поездки Distance=2-5 Miles , Region=Северная Америка , Bike Buyer=1 , Number Children at Home=0 , Number Cars Owned=1 , Поездки Distance=0-1 Miles , English Education=Bachelors , Total Children=1 , Number Children At Home=2 , English Occupation=Skilled Manual , Marital Status=S , Total Children=0 , House Owner Flag=0 , Gender=F , Total Children=2 , Region=Pacific |
Атрибуты, определяющие кластер, можно найти в двух столбцах набора строк схемы интеллектуального анализа данных.
Столбец NODE_DESCRIPTION содержит список атрибутов с разделителями-запятыми. Обратите внимание, что для целей просмотра список атрибутов можно сократить.
Вложенная таблица в столбце NODE_DISTRIBUTION содержит полный список атрибутов кластера. Если клиент не поддерживает иерархические наборы строк, для возврата вложенной таблицы перед списком выбираемых столбцов нужно добавить ключевое слово FLATTENED. Дополнительные сведения об использовании ключевое слово FLATTENED см. в разделе SELECT FROM <model>. СОДЕРЖИМОЕ (РАСШИРЕНИЙ ИНТЕЛЛЕКТУАЛЬНОГО АНАЛИЗА ДАННЫХ).
Образец запроса 4. Возврат атрибутов кластера
Средство просмотра кластеров отображает профиль каждого кластера со списком атрибутов и их значений. Кроме того, это средство просмотра выводит гистограмму, представляющую распределение значений для всей совокупности значений элементов модели. При просмотре модели с помощью средства просмотра кластеров, гистограмму из условных обозначений интеллектуального анализа можно легко скопировать и вставить ее в электронную таблицу Excel или документ Word. Можно также использовать панель «Характеристики кластеров» средства просмотра кластеров для наглядного сравнения атрибутов различных кластеров.
Однако, если данные для этого приходится получать из нескольких кластеров одновременно, легче создать запрос к модели. Например, при просмотре модели оказалось, что два верхних кластера различаются значениями одного атрибута — Number Cars Owned. Значит, нужно извлечь значения этого атрибута для обоих кластеров.
SELECT TOP 2 NODE_NAME,
(SELECT ATTRIBUTE_VALUE, [PROBABILITY] FROM NODE_DISTRIBUTION WHERE ATTRIBUTE_NAME = 'Number Cars Owned')
AS t
FROM [TM_Clustering].CONTENT
WHERE NODE_TYPE = 5
Первая строка кода указывает, что требуются только два верхних кластера.
Примечание
По умолчанию кластеры сортируются по мощности несущего множества. Поэтому столбец NODE_SUPPORT можно исключить.
Вторая строка кода добавляет инструкцию подзапроса выборки, возвращающую только определенные столбцы из столбца вложенной таблицы. Кроме того, запрос выбирает только строки вложенной таблицы, связанные с целевым атрибутом Number Cars Owned. Для упрощения отображения вложенной таблице присваивается псевдоним.
Примечание
Имя столбца вложенной таблицы, PROBABILITY, следует заключить в квадратные скобки, так как его имя совпадает с именем зарезервированного ключевого слова из языка многомерных выражений.
Пример результатов:
| NODE_NAME | T.ATTRIBUTE_VALUE | T.PROBABILITY |
|---|---|---|
| 001 | 2 | 0.829207754 |
| 001 | 1 | 0.109354156 |
| 001 | 3 | 0.034481552 |
| 001 | 4 | 0.013503302 |
| 001 | 0 | 0.013453236 |
| 001 | Missing | 0 |
| 002 | 0 | 0.576980023 |
| 002 | 1 | 0.406623939 |
| 002 | 2 | 0.016380082 |
| 002 | 3 | 1.60E-05 |
| 002 | 4 | 0 |
| 002 | Missing | 0 |
Образец запроса 5. Возврат профиля кластера с помощью системных хранимых процедур
Вместо написания собственных запросов с помощью расширений интеллектуального анализа данных можно также вызвать системные хранимые процедуры, которые SQL Server Analysis Services использует для работы с кластерами. Далее приводится пример использования внутренних хранимых процедур для возврата профиля кластера с идентификатором 002.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterProfiles('TM_Clustering", '002',0.0005
Аналогично можно использовать системные хранимые процедуры для получения характеристик конкретного кластера, как показано в следующем примере.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterCharacteristics('TM_Clustering", '009',0.0005
Пример результатов:
| Атрибуты | Значения | Частота | Поддержка |
|---|---|---|---|
| Количество детей в доме | 0 | 0.999999829076798 | 899 |
| Регион | Северная Америка | 0.999852875241508 | 899 |
| Общее количество детей | 0 | 0.993860958572323 | 893 |
Примечание
Системные хранимые процедуры интеллектуального анализа данных предназначены для внутреннего использования, и корпорация Майкрософт оставляет за собой право изменять их при необходимости. Для применения в рабочей среде рекомендуется создавать запросы с помощью расширений интеллектуального анализа данных, объектов AMO или XML для аналитики.
Образец запроса 6. Поиск отличительных признаков кластеров
Вкладка Сравнение кластеров в Средстве просмотра кластеров позволяет легко сравнивать кластеры между собой, а также варианты из кластера с вариантами, в него не вошедшими (дополнением кластера).
Однако создание запросов для возврата этих сведений — довольно сложный процесс. Может понадобиться дополнительная обработка на клиенте для хранения временных результатов и сравнение результатов нескольких запросов. Для упрощения этой задачи можно использовать системные хранимые процедуры.
Следующий запрос возвращает единственную таблицу, в которой содержатся основные отличительные признаки кластеров с идентификаторами узлов 009 и 007. Для атрибутов с положительными значениями предпочтителен кластер 009, а с отрицательными — кластер 007
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterDiscrimination('TM_Clustering','009','007',0.0005,true)
Пример результатов:
| Атрибуты | Значения | Оценка |
|---|---|---|
| Регион | Северная Америка | 100 |
| English Occupation | Квалифицированный ручной труд | 94.9003803898654 |
| Регион | Европа | -72.5041051379789 |
| English Occupation | Вручную | -69.6503163202722 |
Эти же сведения будут представлены на диаграмме в средстве просмотра Сравнение кластеров , если выбрать кластер 9 из первого раскрывающегося списка и кластер 7 из второго. Чтобы сравнить кластер 9 с его дополнением, в качестве значения второго параметра используется пустая строка, как показано в следующем примере.
CALL System.Microsoft.AnalysisServices.System.DataMining.Clustering.GetClusterDiscrimination('TM_Clustering','009','',0.0005,true)
Примечание
Системные хранимые процедуры интеллектуального анализа данных предназначены для внутреннего использования, и корпорация Майкрософт оставляет за собой право изменять их при необходимости. Для применения в рабочей среде рекомендуется создавать запросы с помощью расширений интеллектуального анализа данных, объектов AMO или XML для аналитики.
Образец запроса 7. Возврат вариантов, принадлежащих кластеру
Если в модели интеллектуального анализа данных разрешена детализация, можно создавать запросы, возвращающие подробные сведения об использованных в модели вариантах. Кроме того, если в структуре интеллектуального анализа данных включена детализация, можно включить столбцы из базовой структуры с помощью функции StructureColumn (DMX).
В следующем примере возвращаются два столбца, которые использовались в модели, — Age и Region, и один столбец, не использовавшийся в модели, — First Name. Запрос возвращает только варианты, относящиеся к кластеру 1.
SELECT [Age], [Region], StructureColumn('First Name')
FROM [TM_Clustering].CASES
WHERE IsInNode('001')
Чтобы вернуть варианты, принадлежащие к кластеру, нужно знать его идентификатор. Идентификатор кластера можно узнать при просмотре модели с помощью средства просмотра. Чтобы к кластеру было легче обращаться, его можно переименовать, а затем обращаться к нему по имени вместо идентификатора. Однако следует помнить, что имена, присвоенные кластерам, теряются при повторной обработке модели.
Создание прогнозов с помощью модели
Хотя кластеризация обычно используется для описания и понимания данных, реализация Майкрософт также позволяет делать прогнозы о членстве в кластере и возвращать вероятности, связанные с прогнозом. В данном разделе приводятся примеры создания прогнозирующих запросов для моделей кластеризации. Можно строить прогнозы для нескольких вариантов, указывая табличный источник данных. Можно также вводить новые величины по одной с помощью одноэлементного запроса. Для простоты все примеры в этом разделе используют одноэлементные запросы.
Дополнительные сведения о создании прогнозирующих запросов с помощью расширений интеллектуального анализа данных (DMX) см. в разделе Средства запросов интеллектуального анализа данных.
Образец запроса 8. Прогноз результатов на основании модели кластеризации
Если создаваемая модель кластеризации содержит прогнозируемый атрибут, ее можно использовать для прогноза результатов. Однако модель будет обрабатывать прогнозируемый атрибут по-разному, в зависимости от значения, определяющего использование прогнозируемого столбца, — Predict или PredictOnly. Если для использования столбца задано значение Predict, значения этого атрибута добавляются в модель кластеризации и появляются в окончательной модели в виде атрибутов. Однако если для использования столбца задано значение PredictOnly, то его значения не используются для создания кластеров. Вместо этого по завершении модели алгоритм кластеризации создает для атрибута PredictOnly новые значения на основе кластеров, к которым принадлежат варианты.
В следующем запросе в модель добавляется один новый вариант, о котором ничего не известно, кроме пола и возраста. Инструкция SELECT указывает предсказуемую пару "атрибут-значение", которая вас интересует, а функция PredictProbability (DMX) сообщает вероятность того, что вариант с этими атрибутами будет иметь целевой результат.
SELECT
[TM_Clustering].[Bike Buyer], PredictProbability([Bike Buyer],1)
FROM
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender]) AS t
Пример результата, когда для использования установлено значение Predict:
| Покупатель велосипеда | Выражение |
|---|---|
| 1 | 0.592924735740338 |
Пример результата, когда для использования установлено значение PredictOnly , после повторной обработки модели:
| Покупатель велосипеда | Выражение |
|---|---|
| 1 | 0.55843544003102 |
В данном примере различие моделей несущественно. Однако, иногда бывает важно установить разницу между реальным распределением значений и распределением, предсказанным моделью. Функция PredictCaseLikelihood (DMX) полезна в этом сценарии, так как она сообщает, насколько вероятна ситуация, учитывая модель.
Значение, возвращаемое функцией PredictCaseLikelihood, является вероятностью, поэтому оно всегда находится в интервале от 0 до 1. Значение 0,5 означает равновероятный результат. Поэтому значение, меньшее 0,5, означает, что прогнозируемый вариант маловероятен для данной модели, а значение, большее 0,5, — что он скорее соответствует модели, чем не соответствует.
Например, следующий запрос вернет два значения, характеризующих вероятность появления нового образца. Ненормализованное значение представляет вероятность попадания в текущую модель. При использовании ключевого слова NORMALIZED значение вероятности, возвращаемое функцией, нормализуется делением вероятности попадания в модель на вероятность непопадания.
SELECT
PredictCaseLikelihood(NORMALIZED) AS [NormalizedValue], PredictCaseLikelihood(NONNORMALIZED) AS [NonNormalizedValue]
FROM
[TM_Clustering_PredictOnly]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender]) AS t
Пример результатов:
| NormalizedValue | NonNormalizedValue |
|---|---|
| 5.56438372679893E-11 | 8.65459953145182E-68 |
Обратите внимание, что для выражения результатов используется экспоненциальный формат.
Образец запроса 9. Определение принадлежности к кластеру
В этом примере функция Cluster (DMX) используется для возврата кластера, к которому, скорее всего, принадлежит новый вариант, и функция ClusterProbability (DMX) для возврата вероятности членства в этом кластере.
SELECT Cluster(), ClusterProbability()
FROM
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender],
'S' AS [Marital Status]) AS t
Пример результатов:
| $CLUSTER | Expression |
|---|---|
| Кластер 2 | 0.397918596951617 |
Примечание. По умолчанию функция ClusterProbability возвращает вероятность принадлежности к самому вероятному кластеру. Однако с помощью конструкции ClusterProbability('cluster name')можно задать другой кластер. Пользуясь этой конструкцией, имейте в виду, что работа любой прогнозирующей функции не зависит от других результатов. Поэтому вероятность, указанная во втором столбце, может относиться не к тому кластеру, который указан в первом столбце.
Образец запроса 10. Возврат всех возможных кластеров, вероятности и расстояния
В предыдущем примере вероятность была не очень высокой. Чтобы определить, есть ли лучший кластер, можно использовать функцию PredictHistogram (DMX) вместе с функцией Cluster (DMX) для возврата вложенной таблицы, которая включает все возможные кластеры, а также вероятность того, что новый вариант принадлежит каждому кластеру. Для большей наглядности иерархический набор строк можно превратить в плоскую таблицу с помощью ключевого слова FLATTENED.
SELECT FLATTENED PredictHistogram(Cluster())
From
[TM_Clustering]
NATURAL PREDICTION JOIN
(SELECT 40 AS [Age],
'F' AS [Gender],
'S' AS [Marital Status])
| Expression.$CLUSTER | Expression.$DISTANCE | Expression.$PROBABILITY |
|---|---|---|
| Кластер 2 | 0.602081403048383 | 0.397918596951617 |
| Кластер 10 | 0.719691686785675 | 0.280308313214325 |
| Кластер 4 | 0.867772590378791 | 0.132227409621209 |
| Кластер 5 | 0.931039872200985 | 0.0689601277990149 |
| Кластер 3 | 0.942359230072167 | 0.0576407699278328 |
| Кластер 6 | 0.958973668972756 | 0.0410263310272437 |
| Кластер 7 | 0.979081275926724 | 0.0209187240732763 |
| Кластер 1 | 0.999169044818624 | 0.000830955181376364 |
| Кластер 9 | 0.999831227795894 | 0.000168772204105754 |
| Кластер 8 | 1 | 0 |
По умолчанию результаты сортируются по вероятности. По результатам видно, что кластер 2, хоть его вероятность и не очень высока, все же наиболее подходит для новой точки данных.
Примечание. Дополнительный столбец, $DISTANCE, показывает расстояние от точки данных до кластера. По умолчанию алгоритм кластеризации Майкрософт использует масштабируемый кластеризация EM, который назначает несколько кластеров каждой точке данных и ранжирует возможные кластеры. Однако если модель кластеризации создавалась с помощью алгоритма К-средних, то каждой точке данных можно назначить только один кластер, а запрос вернет только одну строку. Понимание этих различий необходимо для интерпретации результатов функции PredictCaseLikelihood (DMX). Дополнительные сведения о различиях между кластеризацией с помощью масштабируемой максимизации ожидания и с помощью К-средних см. в разделе Технический справочник по алгоритму кластеризации (Майкрософт).
Список функций
Все алгоритмы Майкрософт поддерживают общий набор функций. Однако модели, созданные с помощью алгоритма кластеризации Майкрософт, поддерживают дополнительные функции, перечисленные в следующей таблице.
| прогнозирующую функцию | Использование |
|---|---|
| Cluster (расширения интеллектуального анализа данных) | Возвращает кластер, который с наибольшей вероятностью содержит входной вариант. |
| ClusterDistance (расширения интеллектуального анализа данных) | Возвращает расстояние до входного варианта от указанного кластера, а если кластер не указан, то расстояние до входного варианта от наиболее вероятного кластера. Возвращает вероятность того, что входной вариант принадлежит определенному кластеру. |
| ClusterProbability (расширения интеллектуального анализа данных) | Возвращает вероятность того, что входной вариант принадлежит определенному кластеру. |
| IsDescendant (расширения интеллектуального анализа данных) | Определяет, является ли узел дочерним для другого узла модели. |
| IsInNode (расширения интеллектуального анализа данных) | Указывает, содержит ли заданный узел текущий вариант. |
| PredictAdjustedProbability (расширения интеллектуального анализа данных) | Возвращает взвешенную вероятность. |
| PredictAssociation (расширения интеллектуального анализа данных) | Прогнозирует вхождение в ассоциативном наборе данных. |
| PredictCaseLikelihood (расширения интеллектуального анализа данных) | Возвращает достоверность, с которой входной вариант соответствует существующей модели. |
| PredictHistogram (расширения интеллектуального анализа данных) | Возвращает таблицу значений, связанную с текущим прогнозируемым значением. |
| PredictNodeId (расширения интеллектуального анализа данных) | Возвращает параметр Node_ID для каждого случая. |
| PredictProbability (расширения интеллектуального анализа данных) | Возвращает вероятность для прогнозируемого значения. |
| PredictStdev (расширения интеллектуального анализа данных) | Возвращает прогнозируемое среднеквадратичное отклонение для заданного столбца. |
| PredictSupport (расширения интеллектуального анализа данных) | Возвращает опорное значение для указанного состояния. |
| PredictVariance (расширения интеллектуального анализа данных) | Возвращает дисперсию указанного столбца. |
Синтаксис конкретных функций см. в справочнике по функциям расширений интеллектуального анализа данных.
См. также:
Запросы интеллектуального анализа данных
Технический справочник по алгоритму кластеризации (Майкрософт)
Алгоритм кластеризации (Майкрософт)
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по