Dotazování datového skladu
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_NUMBER
jsou , DENSE_RANK
RANK
, 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 .