Dotazování datového skladu

Dokončeno

Když jsou tabulky dimenzí a faktů v datovém skladu načtené s daty, můžete pomocí SQL dotazovat tabulky a analyzovat data, která obsahují. Syntaxe jazyka Transact-SQL použitá k dotazování tabulek ve vyhrazeném fondu Synapse SQL se podobá SQL použitému v SQL Serveru nebo Azure SQL Database.

Agregace měr podle atributů dimenze

Většina analýz dat s datovým skladem zahrnuje agregaci číselných měr v tabulkách faktů podle atributů v tabulkách dimenzí. Vzhledem ke způsobu implementace hvězdicového nebo sněhového schématu se dotazy na provedení tohoto typu agregace spoléhají na JOIN klauzule pro propojení tabulek faktů s tabulkami dimenzí a kombinaci agregačních funkcí a GROUP BY klauzulí k definování hierarchií agregace.

Například následující sql dotazuje tabulky FactSales a DimDate v hypotetické datovém skladu, aby agregované částky prodeje podle roku a čtvrtletí:

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter
ORDER BY dates.CalendarYear, dates.CalendarQuarter;

Výsledky z tohoto dotazu by vypadaly podobně jako v následující tabulce:

CalendarYear CalendarQuarter TotalSales
2020 0 25980.16
2020 2 27453.87
2020 3 28527.15
2020 4 31083.45
2021 0 34562.96
2021 2 36162.27
... ... ...

K výpočtu potřebných agregací můžete spojit tolik tabulek dimenzí, kolik potřebujete. Následující kód například rozšiřuje předchozí příklad tak, aby rozčlenil čtvrtletní tržby podle měst na základě podrobností o adrese zákazníka v tabulce DimCustomer :

SELECT  dates.CalendarYear,
        dates.CalendarQuarter,
        custs.City,
        SUM(sales.SalesAmount) AS TotalSales
FROM dbo.FactSales AS sales
JOIN dbo.DimDate AS dates ON sales.OrderDateKey = dates.DateKey
JOIN dbo.DimCustomer AS custs ON sales.CustomerKey = custs.CustomerKey
GROUP BY dates.CalendarYear, dates.CalendarQuarter, custs.City
ORDER BY dates.CalendarYear, dates.CalendarQuarter, custs.City;

Výsledky tentokrát zahrnují čtvrtletní prodej pro každé město:

CalendarYear CalendarQuarter Město TotalSales
2020 0 Amsterdam 5982.53
2020 0 Berlin 2826.98
2020 0 Chicago 5372.72
... ... ... ..
2020 2 Amsterdam 7163.93
2020 2 Berlin 8191.12
2020 2 Chicago 2428.72
... ... ... ..
2020 3 Amsterdam 7261.92
2020 3 Berlin 4202.65
2020 3 Chicago 2287.87
... ... ... ..
2020 4 Amsterdam 8262.73
2020 4 Berlin 5373.61
2020 4 Chicago 7726.23
... ... ... ..
2021 0 Amsterdam 7261.28
2021 0 Berlin 3648.28
2021 0 Chicago 1027.27
... ... ... ..

Spojení ve schématu snowflake

Při použití schématu sněhové vločky mohou být dimenze částečně normalizovány; vyžadování více spojení pro propojení tabulek faktů s dimenzemi sněhové vločky. Předpokládejme například, že váš datový sklad obsahuje tabulku dimenzí DimProduct , ze které byly kategorie produktů normalizovány do samostatné tabulky DimCategory . Dotaz na agregaci položek prodaných podle kategorie produktů může vypadat podobně jako v následujícím příkladu:

SELECT  cat.ProductCategory,
        SUM(sales.OrderQuantity) AS ItemsSold
FROM dbo.FactSales AS sales
JOIN dbo.DimProduct AS prod ON sales.ProductKey = prod.ProductKey
JOIN dbo.DimCategory AS cat ON prod.CategoryKey = cat.CategoryKey
GROUP BY cat.ProductCategory
ORDER BY cat.ProductCategory;

Výsledky tohoto dotazu zahrnují počet položek prodaných pro každou kategorii produktů:

ProductCategory ItemsSold
Accessories 28271
Bity a kusy 5368
... ...

Poznámka:

Klauzule JOIN pro FactSales a DimProduct a DimProduct a DimCategory jsou povinné, i když dotaz nevrací žádná pole z DimProductu.

Použití funkcí řazení

Dalším běžným druhem analytického dotazu je rozdělení výsledků na základě atributu dimenze a pořadí výsledků v rámci každého oddílu. Můžete například chtít řadit obchody každý rok podle výnosů z prodeje. K dosažení tohoto cíle můžete použít funkce řazení jazyka Transact-SQL, jako ROW_NUMBERjsou , DENSE_RANKRANK, a NTILE. Tyto funkce umožňují rozdělit data do kategorií, přičemž každá vrací určitou hodnotu, která označuje relativní pozici každého řádku v rámci oddílu:

  • ROW_NUMBER vrátí pořadové umístění řádku v rámci oddílu. Například první řádek je číslo 1, druhý 2 atd.
  • Funkce RANK vrátí seřazenou pozici každého řádku v seřazených výsledcích. Například v oddílu obchodů seřazených podle objemu prodeje je obchod s nejvyšším objemem prodeje seřazený jako 1. Pokud má více obchodů stejné objemy prodeje, budou seřazeny stejně a pořadí přiřazené k následným obchodům odráží počet obchodů s vyššími objemy prodeje – včetně vazeb.
  • DENSE_RANK seřadí řádky v oddílu stejným způsobem jako pořadí RANK, ale pokud má více řádků stejné pořadí, následující řádky budou pozice řazení ignorovat vazby.
  • Funkce NTILE vrátí zadaný percentil, ve kterém řádek spadá. Například v oddílu obchodů seřazených podle objemu prodeje vrátí kvartil, NTILE(4) ve kterém obchod umístí objem prodeje.

Zvažte například následující dotaz:

SELECT  ProductCategory,
        ProductName,
        ListPrice,
        ROW_NUMBER() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS RowNumber,
        RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Rank,
        DENSE_RANK() OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS DenseRank,
        NTILE(4) OVER
            (PARTITION BY ProductCategory ORDER BY ListPrice DESC) AS Quartile
FROM dbo.DimProduct
ORDER BY ProductCategory;

Dotaz rozděluje produkty do seskupení na základě jejich kategorií a v rámci každého oddílu kategorií je relativní pozice každého produktu určena na základě ceníkové ceny. Výsledky tohoto dotazu můžou vypadat podobně jako v následující tabulce:

ProductCategory ProductName ListPrice Rownumber Rank DenseRank Kvartil
Accessories Widget 8.99 1 1 1 1
Accessories Knicknak 8.49 2 2 2 0
Accessories Pastorek 5.99 3 3 3 2
Accessories Doodah 5.99 4 3 3 2
Accessories Spangle 2.99 5 5 4 3
Accessories Chybný odsad 0.25 6 6 5 4
Bity a kusy Flimflam 7.49 1 1 1 1
Bity a kusy Snickity wotsit 6.99 2 2 2 0
Bity a kusy Příruba 4.25 3 3 3 2
... ... ... ... ... ... ...

Poznámka:

Výsledky vzorku ukazují rozdíl mezi RANK a DENSE_RANK. Všimněte si, že v kategorii Příslušenství mají produkty Sprocket a Doodah stejnou ceníkovou cenu a oba jsou seřazené jako 3. nejvyšší cena produktu. Další produkt s nejvyšší cenou má RANK 5 (existují čtyři produkty dražší než to) a DENSE_RANK ze 4 (existují tři vyšší ceny).

Další informace o funkcích řazení najdete v tématu Funkce řazení (Transact-SQL) v dokumentaci k Azure Synapse Analytics.

Načtení přibližného počtu

Zatímco účelem datového skladu je především podpora analytických datových modelů a sestav pro podnik; datoví analytici a datoví vědci často potřebují provést počáteční zkoumání dat, jen aby mohli určit základní měřítko a distribuci dat.

Následující dotaz například pomocí COUNT funkce načte počet prodejů za každý rok v hypotetické datovém skladu:

SELECT dates.CalendarYear AS CalendarYear,
    COUNT(DISTINCT sales.OrderNumber) AS Orders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

Výsledky tohoto dotazu můžou vypadat podobně jako v následující tabulce:

CalendarYear Objednávky
2019 239870
2020 284741
2021 309272
... ...

Objem dat v datovém skladu může znamenat, že i jednoduché dotazy ke spočítání počtu záznamů, které splňují zadaná kritéria, mohou trvat poměrně dlouho. V mnoha případech není nutný přesný počet – bude stačit přibližný odhad. V takových případech můžete funkci použít APPROX_COUNT_DISTINCT , jak je znázorněno v následujícím příkladu:

SELECT dates.CalendarYear AS CalendarYear,
    APPROX_COUNT_DISTINCT(sales.OrderNumber) AS ApproxOrders
FROM FactSales AS sales
JOIN DimDate AS dates ON sales.OrderDateKey = dates.DateKey
GROUP BY dates.CalendarYear
ORDER BY CalendarYear;

Funkce APPROX_COUNT_DISTINCT používá algoritmus HyperLogLog k načtení přibližného počtu. Výsledkem je zaručená maximální míra chyb 2 % s 97% pravděpodobností, takže výsledky tohoto dotazu se stejnými hypotetickými daty jako předtím můžou vypadat podobně jako v následující tabulce:

CalendarYear Přibližné pořadí
2019 235552
2020 290436
2021 304633
... ...

Počty jsou méně přesné, ale přesto jsou dostatečné pro přibližné porovnání ročních prodejů. U velkého objemu dat se dotaz, který funkci používá APPROX_COUNT_DISTINCT , dokončí rychleji a nižší přesnost může být přijatelným kompromisem při základním zkoumání dat.

Poznámka:

Další podrobnosti najdete v dokumentaci k funkci APPROX_COUNT_DISTINCT .