Příklady dotazů modelu rozhodovacích stromů

Platí pro: SQL Server 2019 a starší služby Analysis Services Azure Analysis Services Fabric/ Power BI Premium

Důležité

Dolování dat bylo v SQL Serveru 2017 Analysis Services zastaralé a nyní ukončeno ve službě SQL Server 2022 Analysis Services. Dokumentace se neaktualizuje pro zastaralé a ukončené funkce. Další informace najdete v tématu Zpětná kompatibilita služby Analysis Services.

Když vytváříte dotaz na model dolování dat, můžete vytvořit dotaz na obsah, který poskytuje podrobnosti o vzorech zjištěných při analýze, nebo můžete vytvořit prediktivní dotaz, který pomocí vzorů v modelu vytváří předpovědi pro nová data. Například dotaz na obsah pro model rozhodovacích stromů může poskytovat statistiky o počtu případů na každé úrovni stromu nebo pravidla, která rozlišují mezi případy. Případně dotaz předpovědi mapuje model na nová data, aby vygeneroval doporučení, klasifikace atd. Metadata o modelu můžete také načíst pomocí dotazu.

Tato část vysvětluje, jak vytvářet dotazy pro modely založené na algoritmu Microsoft Decision Trees.

Dotazy na obsah

Načítání parametrů modelu ze sady řádků schématu dolování dat

Získání podrobností o stromech v modelu pomocí DMX

Načítání podstromů z modelu

Prediktivní dotazy

Vrácení předpovědí s pravděpodobnostmi

Predikce přidružení z modelu rozhodovacích stromů

Načtení regresního vzorce z modelu rozhodovacích stromů

Vyhledání informací o modelu rozhodovacích stromů

Pokud chcete vytvořit smysluplné dotazy na obsah modelu rozhodovacích stromů, měli byste porozumět struktuře obsahu modelu a typům uzlů, do kterých se ukládají jaké informace. Další informace naleznete v tématu Obsah modelu dolování dat pro modely rozhodovacího stromu (Analysis Services - Data Mining).

Ukázkový dotaz 1: Načtení parametrů modelu ze sady řádků schématu dolování dat

Dotazováním na řádkovou sadu schématu data miningu můžete najít metadata o modelu, jako například kdy byl vytvořen, kdy byl model naposledy zpracován, název struktury dolování, na které je model založen, a název sloupce, který se používá jako atribut, který lze předvídat. Můžete také vrátit parametry, které se použily při prvním vytvoření modelu.

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

Ukázkové výsledky:

PARAMETRY TĚŽBY

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

Ukázkový dotaz 2: Vrácení podrobností o obsahu modelu pomocí DMX

Následující dotaz vrátí některé základní informace o rozhodovacích stromech, které byly vytvořeny při vytváření modelu v kurzu Základní dolování dat. Každá stromová struktura je uložena ve svém vlastním uzlu. Vzhledem k tomu, že tento model obsahuje jeden předvídatelný atribut, existuje pouze jeden uzel stromu. Pokud ale vytvoříte model přidružení pomocí algoritmu Rozhodovací stromy, může existovat stovky stromů, jeden pro každý produkt.

Tento dotaz vrátí všechny uzly typu 2, což jsou uzly nejvyšší úrovně stromu, které představují konkrétní předvídatelný atribut.

Poznámka:

Sloupec, CHILDREN_CARDINALITY, musí být uzavřený v hranatých závorkách, aby se odlišil od vyhrazeného klíčového slova MDX stejného názvu.

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

Příklady výsledků:

MODEL_NAME	NODE_NAME	NODE_CAPTION	NODE_SUPPORT	CHILDREN_CARDINALITY
TM_DecisionTree	000000001	Všechno	12939	5

Co vám tyto výsledky říkají? Kardinalita konkrétního uzlu v modelu rozhodovacích stromů udává, kolik okamžitých podřízených položek má daný uzel. Kardinalita pro tento uzel je 5, což znamená, že model rozdělil cílový počet potenciálních kupujících kol do 5 podskupin.

Následující související dotaz vrátí podřízené uzly těchto pěti podskupin a také rozdělení atributů a hodnot v těchto uzlech. Vzhledem k tomu, že jsou statistiky, jako je podpora, pravděpodobnost a rozptyl, uloženy v vnořené tabulce, NODE_DISTRIBUTION, tento příklad používá FLATTENED klíčové slovo k výstupu vnořených sloupců tabulky.

Poznámka:

Sloupec vnořené tabulky SUPPORT musí být uzavřený v závorkách, aby se odlišil od vyhrazeného klíčového slova stejného názvu.

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'  

Příklady výsledků:

NODE_NAME	NODE_CAPTION	T.ATTRIBUTE_NAME	T.ATTRIBUTE_VALUE	PODPORA
00000000100	Počet vlastněných aut = 0	Kupující na kole	Nepřítomný	0
00000000100	Počet vlastněných aut = 0	Kupující na kole	0	1067
00000000100	Počet vlastněných aut = 0	Kupující na kole	1	1875
00000000101	Počet vlastněných aut = 3	Kupující na kole	Nepřítomný	0
00000000101	Počet vlastněných aut = 3	Kupující na kole	0	678
00000000101	Počet vlastněných aut = 3	Kupující na kole	1	473

Z těchto výsledků můžete zjistit, že ze zákazníků, kteří koupili kolo ([Koupě Kola] = 1), měl 1067 zákazníků 0 aut a 473 zákazníků měli 3 auta.

Ukázkový dotaz 3: Načítání podstromů z modelu

Předpokládejme, že jste chtěli zjistit více o zákaznících, kteří si koupili kolo. Další podrobnosti pro kterýkoli z dílčích stromů můžete zobrazit pomocí funkce IsDescendant (DMX) v dotazu, jak je znázorněno v následujícím příkladu. Dotaz vrátí počet kupců kol získáním listových uzlů (NODE_TYPE = 4) ze stromu, který obsahuje zákazníky starší 42 let. Dotaz omezuje řádky z vnořené tabulky na ty, kde Kupující kola = 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  

Příklady výsledků:

NODE_NAME	NODE_CAPTION	t.SUPPORT
000000001000100	Roční příjem >= 26000 a < 42000	266
00000000100010100	Celkem dětí = 3	75
0000000010001010100	Číslo dětí doma = 1	75

Vytváření předpovědí pomocí modelu rozhodovacích stromů

Vzhledem k tomu, že rozhodovací stromy lze použít pro různé úlohy, včetně klasifikace, regrese a dokonce přidružení, při vytváření prediktivního dotazu na model rozhodovacího stromu máte k dispozici mnoho možností. Abyste porozuměli výsledkům předpovědi, musíte porozumět účelu, pro který byl model vytvořen. Následující ukázky dotazů ilustrují tři různé scénáře:

• Vrácení předpovědi klasifikačního modelu spolu s pravděpodobností správné předpovědi a následným filtrováním výsledků podle pravděpodobnosti;

• Vytvoření singleton dotazu pro předpovídání asociací.

• Načtení regresního vzorce pro část rozhodovacího stromu, kde je vztah mezi vstupem a výstupem lineární.

Ukázkový dotaz 4: Vrácení předpovědí s pravděpodobnostmi

Následující ukázkový dotaz používá model rozhodovacího stromu vytvořený v kurzu Základní dolování dat. Dotaz předá novou sadu ukázkových dat z tabulky dbo.ProspectiveBuyers v databázi AdventureWorks2012 DW, aby předpověděl, kteří z zákazníků v nové sadě dat si koupí kolo.

Dotaz používá funkci predikce PredictHistogram (DMX), která vrací vnořenou tabulku, která obsahuje užitečné informace o pravděpodobnostech zjištěných modelem. Konečná klauzule WHERE dotazu vyfiltruje výsledky tak, aby vrátila pouze ty zákazníky, kteří jsou předpovídali, že si koupí kolo s pravděpodobností větší než 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'  

Služba SQL Server Analysis Services ve výchozím nastavení vrací vnořené tabulky s popiskem sloupce Expression. Tento popisek můžete změnit aliasem vráceného sloupce. Pokud to uděláte, alias (v tomto případě Výsledky) se použije jako záhlaví sloupce i jako hodnota v vnořené tabulky. Pokud chcete zobrazit výsledky, musíte rozbalit vnořenou tabulku.

Příklad výsledků s Kupující kola = 1:

Kupující na kole	$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

Pokud váš poskytovatel nepodporuje hierarchické sady řádků, například ty, které jsou zde uvedené, můžete použít klíčové slovo FLATTENED v dotazu k vrácení výsledků jako tabulky, která obsahuje hodnoty null místo opakovaných hodnot sloupců. Další informace najdete v tématu Vnořené tabulky (Analysis Services – Dolování dat) nebo Vysvětlení příkazu DMX Select.

Ukázkový dotaz 5: Předpověď přidružení z modelu rozhodovacích stromů

Následující ukázkový dotaz je založený na struktuře dolování Asociace. Pokud chcete postupovat podle tohoto příkladu, můžete do této těžební struktury přidat nový model a vybrat Microsoftové rozhodovací stromy jako algoritmus. Další informace o tom, jak vytvořit strukturu dolování asociací, viz Lekce 3: Sestavení scénáře tržního košíku (Intermediate Data Mining Tutorial).

Následující ukázkový dotaz je jednoúčelový dotaz, který můžete snadno vytvořit v nástrojích SQL Server Data Tools výběrem polí a následným výběrem hodnot pro tato pole z rozevíracího seznamu.

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  

Očekávané výsledky:

Model
Mountain-200
Mountain Tire Tube
Touring Tire Tube

Výsledky vám řeknou tři nejlepší produkty, které doporučí zákazníkům, kteří si koupili produkt Patch Kit. Při zadávání doporučení můžete také poskytnout více produktů jako vstup, a to buď zadáním hodnot, nebo pomocí dialogového okna Zadávání dotazu Singleton a přidáním nebo odebráním hodnot. Následující ukázkový dotaz ukazuje, jak se poskytuje více hodnot, na kterých se má predikce provést. Hodnoty jsou spojené klauzulí UNION v příkazu SELECT, který definuje vstupní hodnoty.

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  

Očekávané výsledky:

Model
Tričko s dlouhým rukávem s logem
Mountain-400-W
Klasická vesta

Ukázkový dotaz 6: Načtení regresního vzorce z modelu rozhodovacích stromů

Když vytvoříte model rozhodovacího stromu, který obsahuje regresi u průběžného atributu, můžete k předpovědím použít vzorec regrese nebo extrahovat informace o regresním vzorci. Další informace o dotazech na regresní modely naleznete v tématu Příklady dotazů modelu lineární regrese.

Pokud model rozhodovacích stromů obsahuje kombinaci regresních uzlů a uzlů rozdělených na diskrétní atributy nebo rozsahy, můžete vytvořit dotaz, který vrátí pouze regresní uzel. Tabulka NODE_DISTRIBUTION obsahuje podrobnosti o regresním vzorci. V tomto příkladu jsou sloupce zploštěné a NODE_DISTRIBUTION tabulka je aliasována pro snadnější zobrazení. V tomto modelu se ale nenašly žádné regresory, které by souvisely s příjmem s jinými průběžnými atributy. V takových případech vrátí služba SQL Server Analysis Services střední hodnotu atributu a celkovou odchylku v modelu pro daný atribut.

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

Příklady výsledků:

t.ATTRIBUTE_NAME	t.ATTRIBUTE_VALUE	t.SUPPORT	t.PRAVDĚPODOBNOST	t.VARIANCE	t.VALUETYPE
Roční příjem	Nepřítomný	0	0.000457142857142857	0	1
Roční příjem	57220.8876687257	17484	0.999542857142857	1041275619.52776	3
	57220.8876687257	0	0	1041216662.54387	11

Další informace o hodnotových typech a statistikách použitých v regresních modelech naleznete v tématu Obsah modelu dolování pro lineární regresní modely (Analysis Services – Dolování dat).

Seznam prediktivních funkcí

Všechny algoritmy Microsoftu podporují společnou sadu funkcí. Algoritmus Rozhodovací stromy Microsoftu ale podporuje další funkce uvedené v následující tabulce.

Prediktivní funkce	Usage
IsDescendant (DMX)	Určuje, zda je jeden uzel podřízeným jiným uzlem v modelu.
IsInNode (DMX)	Určuje, zda zadaný uzel obsahuje aktuální případ.
PredictAdjustedProbability (DMX)	Vrátí váženou pravděpodobnost.
PredictAssociation (DMX)	Predikuje členství v asociativní datové sadě.
PredictHistogram (DMX)	Vrátí tabulku hodnot souvisejících s aktuální predikovanou hodnotou.
PredictNodeId (DMX)	Vrátí Node_ID pro každý případ.
PredictProbability (DMX)	Vrátí pravděpodobnost predikované hodnoty.
PredictStdev (DMX)	Vrátí předpokládanou směrodatnou odchylku zadaného sloupce.
PredictSupport (DMX)	Vrátí hodnotu podpory pro zadaný stav.
PredictVariance (DMX)	Vrátí odchylku zadaného sloupce.

Seznam funkcí, které jsou společné pro všechny algoritmy Microsoftu, najdete v tématu Obecné prediktivní funkce (DMX). Syntaxi konkrétních funkcí najdete v Odkazu na funkce Rozšíření dolování dat (DMX).

Viz také

Dotazy na dolování dat
Algoritmus rozhodovacích stromů Microsoftu
Technické reference k algoritmu Rozhodovací stromy Microsoftu
Obsah modelu dolování pro modely rozhodovacího stromu (Analysis Services – Dolování dat)