Wykonywanie zapytań względem magazynu danych
Gdy tabele wymiarów i faktów w magazynie danych zostały załadowane z danymi, możesz użyć języka SQL do wykonywania zapytań dotyczących tabel i analizowania danych, które zawierają. Składnia języka Transact-SQL używana do wykonywania zapytań dotyczących tabel w dedykowanej puli SQL usługi Synapse jest podobna do języka SQL używanego w programie SQL Server lub usłudze Azure SQL Database.
Agregowanie miar według atrybutów wymiaru
Większość analiz danych w magazynie danych obejmuje agregowanie miar liczbowych w tabelach faktów według atrybutów w tabelach wymiarów. Ze względu na sposób implementacji schematu gwiazdy lub płatka śniegu zapytania do wykonywania tego rodzaju agregacji polegają na JOIN klauzulach łączących tabele faktów z tabelami wymiarów oraz kombinację funkcji agregujących i GROUP BY klauzul w celu zdefiniowania hierarchii agregacji.
Na przykład następujące dane SQL wysyła zapytania do tabel FactSales i DimDate w hipotetycznym magazynie danych w celu agregowania kwot sprzedaży według roku i kwartału:
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;
Wyniki tego zapytania będą wyglądać podobnie do poniższej tabeli:
| CalendarYear | CalendarQuarter | TotalSales |
|---|---|---|
| 2020 | 1 | 25980.16 |
| 2020 | 2 | 27453.87 |
| 2020 | 3 | 28527.15 |
| 2020 | 100 | 31083.45 |
| 2021 | 1 | 34562.96 |
| 2021 | 2 | 36162.27 |
| ... | ... | ... |
Możesz połączyć dowolną liczbę tabel wymiarów, aby obliczyć potrzebne agregacje. Na przykład poniższy kod rozszerza poprzedni przykład, aby podzielić kwartalne sumy sprzedaży według miasta na podstawie szczegółów adresu klienta w tabeli 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;
Tym razem wyniki obejmują kwartalną sumę sprzedaży dla każdego miasta:
| CalendarYear | CalendarQuarter | City | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amsterdam | 5982.53 |
| 2020 | 1 | Berlin | 2826.98 |
| 2020 | 1 | 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 | 100 | Amsterdam | 8262.73 |
| 2020 | 100 | Berlin | 5373.61 |
| 2020 | 100 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7261.28 |
| 2021 | 1 | Berlin | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Sprzężenia w schemacie płatka śniegu
W przypadku korzystania ze schematu płatka śniegu wymiary mogą być częściowo znormalizowane; wymaganie wielu sprzężeń w celu powiązania tabel faktów z wymiarami płatka śniegu. Załóżmy na przykład, że magazyn danych zawiera tabelę wymiarów DimProduct , z której kategorie produktów zostały znormalizowane do oddzielnej tabeli DimCategory . Zapytanie dotyczące agregowania elementów sprzedanych według kategorii produktów może wyglądać podobnie do poniższego przykładu:
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;
Wyniki tego zapytania obejmują liczbę sprzedanych przedmiotów dla każdej kategorii produktów:
| ProductCategory | ElementyPrzedaj |
|---|---|
| Akcesoria | 28271 |
| Bity i kawałki | 5368 |
| ... | ... |
Uwaga
Klauzule JOIN dla tabel FactSales i DimProduct oraz DimProduct i DimCategory są wymagane, mimo że żadne pola z DimProduct nie są zwracane przez zapytanie.
Korzystanie z funkcji klasyfikacji
Innym typowym rodzajem zapytania analitycznego jest partycjonowanie wyników na podstawie atrybutu wymiaru i klasyfikacja wyników w ramach każdej partycji. Na przykład możesz chcieć sklasyfikować sklepy każdego roku według przychodów ze sprzedaży. Aby osiągnąć ten cel, możesz użyć funkcji klasyfikacji języka Transact-SQL, takich jak ROW_NUMBER, RANK, DENSE_RANKi NTILE. Te funkcje umożliwiają partycjonowanie danych na kategorie, z których każda zwraca określoną wartość, która wskazuje względną pozycję każdego wiersza w partycji:
- ROW_NUMBER zwraca położenie porządkowe wiersza w partycji. Na przykład pierwszy wiersz jest numerowany 1, drugi 2 itd.
- Funkcja RANK zwraca pozycję sklasyfikowaną każdego wiersza w uporządkowanych wynikach. Na przykład w partycji sklepów uporządkowanych według wielkości sprzedaży magazyn o najwyższej wielkości sprzedaży jest w rankingu 1. Jeśli wiele sklepów ma te same woluminy sprzedaży, zostaną one sklasyfikowane tak samo, a ranga przypisana do kolejnych sklepów odzwierciedla liczbę sklepów, które mają wyższe wolumeny sprzedaży - w tym więzi.
- DENSE_RANK umieszcza wiersze w partycji w taki sam sposób jak RANK, ale jeśli wiele wierszy ma tę samą rangę, kolejne wiersze są pozycjami klasyfikacji ignorują powiązania.
- FUNKCJA NTILE zwraca określony percentyl, w którym wiersz spada. Na przykład w partycji sklepów uporządkowanych według wielkości sprzedaży zwraca kwartyl,
NTILE(4)w którym znajduje się wielkość sprzedaży sklepu.
Rozważmy na przykład następujące zapytanie:
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;
Zapytanie dzieli produkty na grupy na podstawie ich kategorii, a w ramach każdej partycji kategorii względna pozycja każdego produktu jest określana na podstawie ceny katalogowej. Wyniki tego zapytania mogą wyglądać podobnie do poniższej tabeli:
| ProductCategory | ProductName | ListPrice | Rownumber | Ranga | DenseRank | Kwartyl |
|---|---|---|---|---|---|---|
| Akcesoria | Widget | 8.99 | 1 | 1 | 1 | 1 |
| Akcesoria | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Akcesoria | Zębatki | 5,99 | 3 | 3 | 3 | 2 |
| Akcesoria | Doodah | 5,99 | 100 | 3 | 3 | 2 |
| Akcesoria | Spangle | 2,99 | 5 | 5 | 100 | 3 |
| Akcesoria | Badabing | 0.25 | 6 | 6 | 5 | 100 |
| Bity i kawałki | Flimflam | 7.49 | 1 | 1 | 1 | 1 |
| Bity i kawałki | Snickity wotsit | 6.99 | 2 | 2 | 2 | 1 |
| Bity i kawałki | Kołnierz | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Uwaga
W przykładowych wynikach pokazano różnicę między elementami RANK i DENSE_RANK. Należy pamiętać, że w kategorii Akcesoria produkty Sprocket i Doodah mają taką samą cenę, a oba są klasyfikowane jako trzeci najwyższy produkt. Następny najwyższy produkt ma rangę 5 (są cztery produkty droższe niż) i DENSE_RANK 4 (są trzy wyższe ceny).
Aby dowiedzieć się więcej na temat funkcji klasyfikacji, zobacz Ranking Functions (Transact-SQL) w dokumentacji usługi Azure Synapse Analytics.
Pobieranie przybliżonej liczby
Chociaż celem magazynu danych jest przede wszystkim obsługa modeli danych analitycznych i raportów dla przedsiębiorstwa; analitycy danych i analitycy danych często muszą przeprowadzić początkową eksplorację danych, aby określić podstawową skalę i rozkład danych.
Na przykład następujące zapytanie używa COUNT funkcji do pobierania liczby sprzedaży dla każdego roku w hipotetycznym magazynie danych:
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;
Wyniki tego zapytania mogą wyglądać podobnie do poniższej tabeli:
| CalendarYear | Zamówienia |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
Ilość danych w magazynie danych może oznaczać, że nawet proste zapytania do zliczenia liczby rekordów spełniających określone kryteria mogą zająć dużo czasu. W wielu przypadkach dokładna liczba nie jest wymagana — przybliżone oszacowanie wystarczy. W takich przypadkach można użyć APPROX_COUNT_DISTINCT funkcji, jak pokazano w poniższym przykładzie:
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;
Funkcja APPROX_COUNT_DISTINCT używa algorytmu HyperLogLog, aby pobrać przybliżoną liczbę. Wynik ma gwarancję maksymalnej szybkości błędów wynoszącej 2% z prawdopodobieństwem 97%, więc wyniki tego zapytania z tymi samymi hipotetycznymi danymi, jak poprzednio, mogą wyglądać podobnie do poniższej tabeli:
| CalendarYear | Przybliżone zamówienia |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
Liczby są mniej dokładne, ale nadal wystarczające do przybliżonego porównania sprzedaży rocznej. W przypadku dużej ilości danych zapytanie korzystające z APPROX_COUNT_DISTINCT funkcji kończy się szybciej, a zmniejszona dokładność może być akceptowalnym kompromisem podczas podstawowej eksploracji danych.
Uwaga
Aby uzyskać więcej informacji, zobacz dokumentację funkcji APPROX_COUNT_DISTINCT .