Fråga ett informationslager
När dimensions- och faktatabellerna i ett informationslager har lästs in med data kan du använda SQL för att köra frågor mot tabellerna och analysera de data de innehåller. Transact-SQL-syntaxen som används för att fråga efter tabeller i en synapse-dedikerad SQL-pool liknar SQL som används i SQL Server eller Azure SQL Database.
Aggregera mått efter dimensionsattribut
De flesta dataanalyser med ett informationslager omfattar aggregering av numeriska mått i faktatabeller efter attribut i dimensionstabeller. På grund av hur ett star- eller snowflake-schema implementeras förlitar sig frågor för att utföra den här typen av aggregering på JOIN satser för att ansluta faktatabeller till dimensionstabeller och en kombination av aggregerade funktioner och GROUP BY satser för att definiera aggregeringshierarkierna.
Följande SQL frågar till exempel tabellerna FactSales och DimDate i ett hypotetiskt informationslager för att aggregera försäljningsbelopp per år och kvartal:
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;
Resultatet från den här frågan skulle se ut ungefär som i följande tabell:
| CalendarYear | CalendarQuarter | TotalSales |
|---|---|---|
| 2020 | 1 | 25980.16 |
| 2020 | 2 | 27453.87 |
| 2020 | 3 | 28527.15 |
| 2020 | 4 | 31083.45 |
| 2021 | 1 | 34562.96 |
| 2021 | 2 | 36162.27 |
| ... | ... | ... |
Du kan ansluta så många dimensionstabeller som behövs för att beräkna de sammansättningar du behöver. Följande kod utökar till exempel föregående exempel för att dela upp de kvartalsvisa försäljningssummorna per stad baserat på kundens adressinformation i tabellen 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;
Den här gången innehåller resultatet en kvartalsvis försäljningssumma för varje stad:
| CalendarYear | CalendarQuarter | Ort | 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 | 4 | Amsterdam | 8262.73 |
| 2020 | 4 | Berlin | 5373.61 |
| 2020 | 4 | Chicago | 7726.23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7261.28 |
| 2021 | 1 | Berlin | 3648.28 |
| 2021 | 1 | Chicago | 1027.27 |
| ... | ... | ... | .. |
Ansluter till ett snowflake-schema
När du använder ett snowflake-schema kan dimensioner delvis normaliseras. kräver flera kopplingar för att relatera faktatabeller till snowflake-dimensioner. Anta till exempel att ditt informationslager innehåller en DimProduct-dimensionstabell från vilken produktkategorierna har normaliserats till en separat DimCategory-tabell . En fråga för att aggregera objekt som säljs efter produktkategori kan se ut ungefär som i följande exempel:
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;
Resultatet från den här frågan inkluderar antalet sålda objekt för varje produktkategori:
| ProductCategory | ObjektSålda |
|---|---|
| Accessoarer | 28271 |
| Bitar och bitar | 5368 |
| ... | ... |
Kommentar
JOIN-satser för FactSales och DimProduct och dimproduct och DimCategory krävs, även om inga fält från DimProduct returneras av frågan.
Använda rangordningsfunktioner
En annan vanlig typ av analysfråga är att partitioneras resultaten baserat på ett dimensionsattribut och rangordna resultaten inom varje partition. Du kanske till exempel vill rangordna butiker varje år efter deras försäljningsintäkter. För att uppnå det här målet kan du använda Transact-SQL-rankningsfunktioner som ROW_NUMBER, RANK, DENSE_RANKoch NTILE. Med de här funktionerna kan du partitionera data över kategorier, var och en returnerar ett specifikt värde som anger den relativa positionen för varje rad i partitionen:
- ROW_NUMBER returnerar ordningstalet för raden i partitionen. Till exempel är den första raden numrerad 1, den andra 2 och så vidare.
- RANK returnerar den rangordnade positionen för varje rad i de ordnade resultaten. I en partition av butiker som sorteras efter försäljningsvolym rangordnas till exempel butiken med den högsta försäljningsvolymen 1. Om flera butiker har samma försäljningsvolymer rangordnas de på samma sätt och rangordningen som tilldelas efterföljande butiker återspeglar antalet butiker som har högre försäljningsvolymer – inklusive band.
- DENSE_RANK rangordnar rader i en partition på samma sätt som RANK, men när flera rader har samma rangordning ignoreras banden i efterföljande rader.
- NTILE returnerar den angivna percentilen där raden faller. I en partition av butiker som sorteras efter försäljningsvolym
NTILE(4)returnerar till exempel kvartilen där en butiks försäljningsvolym placerar den.
Tänk dig följande fråga:
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;
Frågan partitionerar produkter i grupper baserat på deras kategorier, och inom varje kategoripartition bestäms den relativa positionen för varje produkt baserat på listpriset. Resultatet från den här frågan kan se ut ungefär som i följande tabell:
| ProductCategory | ProductName | ListPrice | Radnummer | Rangordning | DenseRank | Kvartil |
|---|---|---|---|---|---|---|
| Accessoarer | Widget | 8.99 | 1 | 1 | 1 | 1 |
| Accessoarer | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Accessoarer | Sprocket | 5.99 | 3 | 3 | 3 | 2 |
| Accessoarer | Doodah | 5.99 | 4 | 3 | 3 | 2 |
| Accessoarer | Spangle | 2.99 | 5 | 5 | 4 | 3 |
| Accessoarer | Badabing | 0.25 | 6 | 6 | 5 | 4 |
| Bitar och bitar | Flimflam | 7.49 | 1 | 1 | 1 | 1 |
| Bitar och bitar | Snickity wotsit | 6.99 | 2 | 2 | 2 | 1 |
| Bitar och bitar | Fläns | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Kommentar
Exempelresultaten visar skillnaden mellan RANK och DENSE_RANK. Observera att i kategorin Tillbehör har Sprocket- och Doodah-produkterna samma listpris och är båda rankade som den tredje högst prissatta produkten. Den näst högst prissatta produkten har en RANK på 5 (det finns fyra produkter dyrare än den) och en DENSE_RANK på 4 (det finns tre högre priser).
Mer information om rangordningsfunktioner finns i Rankningsfunktioner (Transact-SQL) i dokumentationen för Azure Synapse Analytics.
Hämtar ett ungefärligt antal
Syftet med ett informationslager är främst att stödja analysdatamodeller och rapporter för företaget. dataanalytiker och dataforskare behöver ofta utföra viss inledande datautforskning, bara för att fastställa den grundläggande skalan och distributionen av data.
Följande fråga använder COUNT till exempel funktionen för att hämta antalet försäljningar för varje år i ett hypotetiskt informationslager:
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;
Resultatet av den här frågan kan se ut ungefär som i följande tabell:
| CalendarYear | Ordrar |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
Mängden data i ett informationslager kan innebära att även enkla frågor för att räkna antalet poster som uppfyller angivna kriterier kan ta lång tid att köra. I många fall krävs inte ett exakt antal – en ungefärlig uppskattning räcker. I sådana fall kan du använda APPROX_COUNT_DISTINCT funktionen enligt följande exempel:
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;
Funktionen APPROX_COUNT_DISTINCT använder en HyperLogLog-algoritm för att hämta ett ungefärligt antal. Resultatet kommer garanterat att ha en maximal felfrekvens på 2 % med 97 % sannolikhet, så resultatet av den här frågan med samma hypotetiska data som tidigare kan se ut ungefär som i följande tabell:
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
Antalet är mindre exakt, men räcker fortfarande för en ungefärlig jämförelse av den årliga försäljningen. Med en stor mängd data slutförs frågan med APPROX_COUNT_DISTINCT funktionen snabbare och den minskade noggrannheten kan vara en acceptabel kompromiss under grundläggande datautforskning.
Kommentar
Mer information finns i dokumentationen om APPROX_COUNT_DISTINCT funktion.