Abfragen eines Data Warehouse
Wenn die Dimension- und Faktentabellen in einem Data Warehouse mit Daten geladen wurden, können Sie mit SQL die Tabellen abfragen und die darin enthaltenen Daten analysieren. Die Transact-SQL-Syntax, die zum Abfragen von Tabellen in einem dedizierten SQL-Pool von Synapse verwendet wird, ähnelt dem in SQL Server oder Azure SQL-Datenbank verwendeten SQL.
Aggregieren von Measures nach Dimensionsattributen
Die meisten Datenanalysen mit einem Data Warehouse umfassen das Aggregieren numerischer Measures in Faktentabellen nach Attributen in Dimensionstabellen. Aufgrund der Art und Weise, wie ein Stern- oder Schneeflockenschema implementiert ist, stützen sich Abfragen zur Durchführung dieser Art von Aggregation zur Verbindung von Faktentabellen mit Dimensionstabellen auf JOIN-Klauseln sowie zur Definition der Aggregationshierarchien auf eine Kombination von Aggregatfunktionen und GROUP BY-Klauseln.
Im folgenden SQL werden beispielsweise die Tabellen FactSales und DimDate in einem hypothetischen Data Warehouse abgefragt, um Umsatzbeträge nach Jahr und Quartal zu aggregieren:
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;
Die Ergebnisse dieser Abfrage würden ähnlich wie in der folgenden Tabelle aussehen:
| CalendarYear | CalendarQuarter | TotalSales |
|---|---|---|
| 2020 | 1 | 25.980,16 |
| 2020 | 2 | 27.453,87 |
| 2020 | 3 | 28.527,15 |
| 2020 | 4 | 31.083,45 |
| 2021 | 1 | 34.562,96 |
| 2021 | 2 | 36.162,27 |
| ... | ... | ... |
Sie können beliebig viele Dimensiontabellen verknüpfen, um die benötigten Aggregationen zu berechnen. Der folgende Code erweitert das vorherige Beispiel, um die Quartalsumsatzsummen basierend auf den Kundenadressdaten in der Tabelle DimCustomer nach Orten aufzuschlüsseln:
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;
Dieses Mal enthalten die Ergebnisse für jede Stadt eine Quartalsumsatzsumme:
| CalendarYear | CalendarQuarter | City | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amsterdam | 5.982,53 |
| 2020 | 1 | Berlin | 2.826,98 |
| 2020 | 1 | Chicago | 5.372,72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amsterdam | 7.163,93 |
| 2020 | 2 | Berlin | 8.191,12 |
| 2020 | 2 | Chicago | 2.428,72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amsterdam | 7.261,92 |
| 2020 | 3 | Berlin | 4.202,65 |
| 2020 | 3 | Chicago | 2.287,87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amsterdam | 8.262,73 |
| 2020 | 4 | Berlin | 5.373,61 |
| 2020 | 4 | Chicago | 7.726,23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7.261,28 |
| 2021 | 1 | Berlin | 3.648,28 |
| 2021 | 1 | Chicago | 1.027,27 |
| ... | ... | ... | .. |
Verknüpfungen in einem Schneeflockenschema
Bei Verwendung eines Schneeflockenschemas können Dimensionen teilweise normalisiert werden. Mehrere Verknüpfungen sind erforderlich, um Faktentabellen Schneeflockendimensionen zuzuordnen. Angenommen, Ihr Data Warehouse enthält eine Dimensionstabelle DimProduct, aus der die Produktkategorien in eine separate Tabelle DimCategory normalisiert wurden. Eine Abfrage zum Aggregieren von Elementen, die nach Produktkategorie verkauft werden, könnte dem folgenden Beispiel ähneln:
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;
Die Ergebnisse dieser Abfrage umfassen die Anzahl der für jede Produktkategorie verkauften Artikel:
| ProductCategory | ItemsSold |
|---|---|
| Accessories | 28271 |
| Bits and pieces | 5368 |
| ... | ... |
Hinweis
JOIN-Klauseln sind sowohl für FactSales und DimProduct als auch für DimProduct und DimCategory erforderlich, obwohl keine Felder von DimProduct von der Abfrage zurückgegeben werden.
Verwenden von Rangfolgefunktionen
Eine weitere gängige Analyseabfrage besteht darin, die Ergebnisse basierend auf einem Dimensionsattribut zu partitionieren und die Ergebnisse innerhalb jeder Partition zu bewerten. So können Sie beispielsweise jedes Jahr Filialen nach ihren Verkaufserlösen bewerten. Um dieses Ziel zu erreichen, können Sie Bewertungsfunktionen von Transact-SQL wie ROW_NUMBER, RANK, DENSE_RANK und NTILE verwenden. Mit diesen Funktionen können Sie die Daten Kategorien übergreifend partitionieren, wobei jede einen bestimmten Wert zurückgibt, der die relative Position jeder Zeile innerhalb der Partition angibt:
- ROW_NUMBER gibt die Ordnungsposition der Zeile innerhalb der Partition zurück. Beispielsweise wird die erste Zeile mit 1, die zweite mit 2 usw. nummeriert.
- RANK gibt die Rangposition jeder Zeile in den sortierten Ergebnissen zurück. Beispielsweise wird in einer Partition nach Umsatzvolumen sortierter Filialen die Filiale mit dem höchsten Umsatzvolumen mit 1 bewertet. Wenn das Umsatzvolumen mehrerer Filialen gleich ist, werden sie gleich bewertet, und der Rang, der den nachfolgenden Filialen zugewiesen wird, spiegelt die Anzahl der Filialen wider, deren Umsatzvolumen höher ist – Gleichstände eingeschlossen.
- DENSE_RANK bewertet Zeilen in einer Partition auf dieselbe Weise wie RANK, aber wenn mehrere Zeilen denselben Rang haben, sind nachfolgende Zeilen Rangpositionen. Gleichstände werden ignoriert.
- NTILE gibt das angegebene Perzentil zurück, in das die Zeile fällt. Beispielsweise gibt
NTILE(4)in einer Partition nach Umsatzvolumen sortierter Filialen das Quartil zurück, in dem das Umsatzvolumen einer Filiale platziert wird.
Angenommen, beispielsweise liegt die folgende Abfrage vor:
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;
Die Abfrage partitioniert Produkte auf ihren Kategorien basierend in Gruppierungen, und innerhalb jeder Kategoriepartition wird die relative Position jedes Produkts auf seinem Listenpreis basierend bestimmt. Die Ergebnisse dieser Abfrage würden ähnlich wie in der folgenden Tabelle aussehen:
| ProductCategory | ProductName | ListPrice | RowNumber | Rang | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Accessories | Widget | 8,99 | 1 | 1 | 1 | 1 |
| Accessories | Knicknak | 8,49 | 2 | 2 | 2 | 1 |
| Accessories | Sprocket | 5,99 | 3 | 3 | 3 | 2 |
| Accessories | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessories | Spangle | 2,99 | 5 | 5 | 4 | 3 |
| Accessories | Badabing | 0,25 | 6 | 6 | 5 | 4 |
| Bits and pieces | Flimflam | 7,49 | 1 | 1 | 1 | 1 |
| Bits and pieces | Snickity wotsit | 6,99 | 2 | 2 | 2 | 1 |
| Bits and pieces | Flange | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Hinweis
Die Beispielergebnisse veranschaulichen den Unterschied zwischen RANK und DENSE_RANK. Beachten Sie, dass in der Kategorie Accessories die Produkte Sprocket und Doodah denselben Listenpreis aufweisen; und beide sind als Produkte mit dem dritthöchsten Preis eingestuft. Das Produkt mit dem nächsthöchsten Preis hat einen RANK von 5 (vier Produkte sind teurer) und einen DENSE_RANK von 4 (es gibt drei höhere Preise).
Weitere Informationen zu Rangfolgefunktionen finden Sie in der Dokumentation zu Azure Synapse Analytics unter Rangfolgefunktionen (Transact-SQL).
Abrufen einer ungefähren Anzahl
Ein Data Warehouse soll zwar in erster Linie analytische Datenmodelle und Berichte für das Unternehmen unterstützen, aber Data Analysts und wissenschaftliche Fachkräfte für Daten müssen häufig erst anfängliche Datenforschung betreiben, nur um die grundlegende Skalierung und Verteilung der Daten zu bestimmen.
Die folgende Abfrage verwendet beispielsweise die COUNT-Funktion, um die Anzahl der Verkäufe für jedes Jahr in einem hypothetischen Data Warehouse abzurufen:
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;
Die Ergebnisse dieser Abfrage könnten in etwa der folgenden Tabelle entsprechen:
| CalendarYear | Orders |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
Das Datenvolumen in einem Data Warehouse kann bedeuten, dass sogar einfache Abfragen, um die Anzahl der Datensätze zählen, die bestimmte Kriterien erfüllen, erhebliche Zeit dauern können. In vielen Fällen ist eine genaue Anzahl nicht erforderlich – eine ungefähre Schätzung reicht aus. In solchen Fällen können Sie auch die APPROX_COUNT_DISTINCT-Funktion wie im folgenden Beispiel gezeigt verwenden:
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;
Die APPROX_COUNT_DISTINCT-Funktion verwendet einen HyperLogLog-Algorithmus, um eine ungefähre Anzahl abzurufen. Das Ergebnis hat eine garantierte maximale Fehlerquote von 2 % mit einer Wahrscheinlichkeit von 97 %, sodass die Ergebnisse dieser Abfrage mit denselben hypothetischen Daten wie zuvor ähnlich wie in der folgenden Tabelle aussehen könnten:
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
Die Anzahl ist weniger genau, aber immer noch ausreichend für einen ungefähren Vergleich des Jahresumsatzes. Mit einem großen Datenvolumen wird die Abfrage mit der APPROX_COUNT_DISTINCT-Funktion schneller abgeschlossen, und die verringerte Genauigkeit kann während der grundlegenden Datenuntersuchung ein akzeptabler Kompromiss sein.
Hinweis
Weitere Details finden Sie in der Dokumentation zur APPROX_COUNT_DISTINCT-Funktion.