Een query uitvoeren op een datawarehouse
Wanneer de dimensie- en feitentabellen in een datawarehouse zijn geladen met gegevens, kunt u SQL gebruiken om query's uit te voeren op de tabellen en de gegevens te analyseren die ze bevatten. De Transact-SQL-syntaxis die wordt gebruikt om query's uit te voeren op tabellen in een toegewezen Synapse SQL-pool, is vergelijkbaar met SQL die wordt gebruikt in SQL Server of Azure SQL Database.
Metingen aggregeren op dimensiekenmerken
De meeste gegevensanalyses met een datawarehouse bestaan uit het samenvoegen van numerieke metingen in feitentabellen op kenmerken in dimensietabellen. Vanwege de manier waarop een ster- of snowflake-schema wordt geïmplementeerd, zijn query's om dit soort aggregatie uit te voeren afhankelijk JOIN van componenten om feitentabellen te verbinden met dimensietabellen en een combinatie van statistische functies en GROUP BY componenten om de aggregatiehiërarchieën te definiëren.
De volgende SQL voert bijvoorbeeld query's uit op de tabellen FactSales en DimDate in een hypothetisch datawarehouse om de verkoopbedragen per jaar en kwartaal samen te voegen:
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;
De resultaten van deze query zien er ongeveer als volgt uit:
| CalendarYear | CalendarQuarter | Totaalomzet |
|---|---|---|
| 2020 | 1 | 25980.16 |
| 2020 | 2 | 27453.87 |
| 2020 | 3 | 28527.15 |
| 2020 | 4 | 31083.45 |
| 2021 | 1 | 34562.96 |
| 2021 | 2 | 36162.27 |
| ... | ... | ... |
U kunt zoveel dimensietabellen samenvoegen als nodig is om de aggregaties te berekenen die u nodig hebt. Met de volgende code wordt bijvoorbeeld het vorige voorbeeld uitgebreid om de totale kwartaalverkoop per plaats op te splitsen op basis van de adresgegevens van de klant in de tabel 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;
Deze keer bevatten de resultaten een kwartaalomzettotaal voor elke stad:
| CalendarYear | CalendarQuarter | Plaats | Totaalomzet |
|---|---|---|---|
| 2020 | 1 | Amsterdam | 5982.53 |
| 2020 | 1 | Berlijn | 2826.98 |
| 2020 | 1 | Chicago | 5372.72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amsterdam | 7163.93 |
| 2020 | 2 | Berlijn | 8191.12 |
| 2020 | 2 | Chicago | 2428.72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amsterdam | 7261.92 |
| 2020 | 3 | Berlijn | 4202.65 |
| 2020 | 3 | Chicago | 2287.87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amsterdam | 8262.73 |
| 2020 | 4 | Berlijn | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7261.28 |
| 2021 | 1 | Berlijn | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Joins in een snowflake-schema
Wanneer u een snowflake-schema gebruikt, kunnen dimensies gedeeltelijk worden genormaliseerd; meerdere joins vereisen om feitentabellen te relateren aan sneeuwvlokdimensies. Stel dat uw datawarehouse een dimensietabel DimProduct bevat waaruit de productcategorieën zijn genormaliseerd in een afzonderlijke DimCategory-tabel . Een query voor het aggregeren van artikelen die per productcategorie worden verkocht, kan er ongeveer uitzien als in het volgende voorbeeld:
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;
De resultaten van deze query bevatten het aantal verkochte artikelen voor elke productcategorie:
| ProductCategory | Items verkocht |
|---|---|
| Accessoires | 28271 |
| Bits en stukjes | 5368 |
| ... | ... |
Notitie
JOIN-componenten voor FactSales en DimProduct en voor DimProduct en DimCategory zijn beide vereist, ook al worden er geen velden van DimProduct geretourneerd door de query.
Classificatiefuncties gebruiken
Een ander veelvoorkomend type analytische query is het partitioneren van de resultaten op basis van een dimensiekenmerk en het rangschikken van de resultaten binnen elke partitie. U kunt bijvoorbeeld elk jaar winkels rangschikken op basis van hun omzet. Om dit doel te bereiken, kunt u Transact-SQL-classificatiefuncties zoals ROW_NUMBER, RANK, DENSE_RANKen NTILE. Met deze functies kunt u de gegevens partitioneren over categorieën, die elk een specifieke waarde retourneren die de relatieve positie van elke rij binnen de partitie aangeeft:
- ROW_NUMBER retourneert de rangtelpositie van de rij binnen de partitie. De eerste rij is bijvoorbeeld genummerd 1, de tweede 2, enzovoort.
- RANK retourneert de gerangschikte positie van elke rij in de geordende resultaten. In een partitie van winkels die zijn besteld op verkoopvolume, wordt de winkel met het hoogste verkoopvolume bijvoorbeeld gerangschikt op 1. Als meerdere winkels dezelfde verkoopvolumes hebben, krijgen ze dezelfde rangschikking en wordt de rangschikking die aan volgende winkels is toegewezen, het aantal winkels met hogere verkoopvolumes, inclusief banden.
- DENSE_RANK rijen in een partitie op dezelfde manier rangschikt als RANK, maar als meerdere rijen dezelfde rang hebben, worden de volgende rijen rangschikkingen genegeerd.
- NTILE retourneert het opgegeven percentiel waarin de rij valt. In een partitie van winkels die zijn besteld op verkoopvolume,
NTILE(4)wordt bijvoorbeeld het kwartiel geretourneerd waarin het verkoopvolume van een winkel deze plaatst.
Kijk bijvoorbeeld eens naar de volgende query:
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;
De query partitioneert producten in groeperingen op basis van hun categorieën, en binnen elke categoriepartitie wordt de relatieve positie van elk product bepaald op basis van de catalogusprijs. De resultaten van deze query kunnen er ongeveer uitzien als in de volgende tabel:
| ProductCategory | ProductName | ListPrice | Rijnummer | Positie | DenseRank | Kwartiel |
|---|---|---|---|---|---|---|
| Accessoires | Widget | 8.99 | 1 | 1 | 1 | 1 |
| Accessoires | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Accessoires | Sprocket | 5,99 | 3 | 3 | 3 | 2 |
| Accessoires | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessoires | Spangle | 2.99 | 5 | 5 | 4 | 3 |
| Accessoires | Slechteabing | 0.25 | 6 | 6 | 5 | 4 |
| Bits en stukjes | Flimflam | 7.49 | 1 | 1 | 1 | 1 |
| Bits en stukjes | Snickity wotsit | 6.99 | 2 | 2 | 2 | 1 |
| Bits en stukjes | Flens | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Notitie
In de voorbeeldresultaten ziet u het verschil tussen RANK en DENSE_RANK. Houd er rekening mee dat in de categorie Accessoires de producten Sprocket en Doodah dezelfde catalogusprijs hebben; en beide zijn gerangschikt als het 3e hoogste geprijsde product. Het eerstvolgende hoogste geprijsde product heeft een RANG van 5 (er zijn vier producten duurder dan het) en een DENSE_RANK van 4 (er zijn drie hogere prijzen).
Zie Classificatiefuncties (Transact-SQL) in de documentatie van Azure Synapse Analytics voor meer informatie over classificatiefuncties.
Een geschatte telling ophalen
Hoewel het doel van een datawarehouse voornamelijk is om analytische gegevensmodellen en -rapporten voor de onderneming te ondersteunen; gegevensanalisten en gegevenswetenschappers moeten vaak wat initiële gegevensverkenning uitvoeren, alleen om de basisschaal en distributie van de gegevens te bepalen.
De volgende query gebruikt bijvoorbeeld de COUNT functie om het aantal verkopen voor elk jaar op te halen in een hypothetisch datawarehouse:
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;
De resultaten van deze query kunnen er ongeveer uitzien als in de volgende tabel:
| CalendarYear | Orders |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
Het aantal gegevens in een datawarehouse kan betekenen dat zelfs eenvoudige query's om het aantal records te tellen dat voldoet aan de opgegeven criteria, veel tijd in beslag nemen. In veel gevallen is een nauwkeurige telling niet vereist. Een geschatte schatting is voldoende. In dergelijke gevallen kunt u de APPROX_COUNT_DISTINCT functie gebruiken, zoals wordt weergegeven in het volgende voorbeeld:
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;
De APPROX_COUNT_DISTINCT functie maakt gebruik van een HyperLogLog-algoritme om een geschatte telling op te halen. Het resultaat heeft gegarandeerd een maximale foutsnelheid van 2% met 97% waarschijnlijkheid, dus de resultaten van deze query met dezelfde hypothetische gegevens als voorheen kunnen er ongeveer als in de volgende tabel uitzien:
| CalendarYear | CaOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
De aantallen zijn minder nauwkeurig, maar nog steeds voldoende voor een geschatte vergelijking van de jaarlijkse verkoop. Met een groot aantal gegevens wordt de query met behulp van de APPROX_COUNT_DISTINCT functie sneller voltooid en kan de verminderde nauwkeurigheid een acceptabele afweging zijn tijdens het verkennen van basisgegevens.
Notitie
Zie de documentatie APPROX_COUNT_DISTINCT functie voor meer informatie.