Eseguire query su un data warehouse
Quando le tabelle delle dimensioni e dei fatti in un data warehouse sono state caricate con i dati, è possibile usare SQL per eseguire query sulle tabelle e analizzare i dati che contengono. La sintassi Transact-SQL usata per eseguire query sulle tabelle in un pool SQL dedicato di Synapse è simile alla sintassi SQL usata in SQL Server o nel database SQL di Azure.
Aggregazione delle misure in base agli attributi delle dimensioni
La maggior parte dell'analisi dei dati per un data warehouse prevede l'aggregazione di misure numeriche nelle tabelle dei fatti in base agli attributi nelle tabelle delle dimensioni. A causa della modalità di implementazione di uno schema star o snowflake, le query per eseguire questo tipo di aggregazione si basano sulle clausole JOIN per connettere le tabelle dei fatti alle tabelle delle dimensioni e su una combinazione di clausole GROUP BY e funzioni di aggregazione per definire le gerarchie di aggregazione.
Il codice SQL seguente, ad esempio, esegue una query sulle tabelle FactSales e DimDate in un data warehouse ipotetico per aggregare gli importi delle vendite per anno e trimestre:
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;
I risultati di questa query saranno simili alla tabella seguente:
| 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 |
| ... | ... | ... |
È possibile creare un join di tutte le tabelle delle dimensioni necessarie per calcolare le aggregazioni desiderate. Il codice seguente, ad esempio, estende l'esempio precedente per suddividere i totali delle vendite trimestrali per città in base ai dettagli degli indirizzi del cliente nella tabella 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;
Questa volta, i risultati includono il totale delle vendite trimestrali per ogni città:
| CalendarYear | CalendarQuarter | Città | TotalSales |
|---|---|---|---|
| 2020 | 1 | Amsterdam | 5982,53 |
| 2020 | 1 | Berlino | 2826,98 |
| 2020 | 1 | Chicago | 5372,72 |
| ... | ... | ... | .. |
| 2020 | 2 | Amsterdam | 7163,93 |
| 2020 | 2 | Berlino | 8191,12 |
| 2020 | 2 | Chicago | 2428,72 |
| ... | ... | ... | .. |
| 2020 | 3 | Amsterdam | 7261,92 |
| 2020 | 3 | Berlino | 4202,65 |
| 2020 | 3 | Chicago | 2287,87 |
| ... | ... | ... | .. |
| 2020 | 4 | Amsterdam | 8262,73 |
| 2020 | 4 | Berlino | 5373,61 |
| 2020 | 4 | Chicago | 7726,23 |
| ... | ... | ... | .. |
| 2021 | 1 | Amsterdam | 7261,28 |
| 2021 | 1 | Berlino | 3648,28 |
| 2021 | 1 | Chicago | 1027,27 |
| ... | ... | ... | .. |
Join in uno schema snowflake
Quando si usa uno schema snowflake, le dimensioni possono essere parzialmente normalizzate. In tal caso, sono necessari più join per correlare le tabelle dei fatti alle dimensioni snowflake. Si supponga, ad esempio, che il data warehouse includa una tabella delle dimensioni DimProduct da cui le categorie di prodotti sono state normalizzate in una tabella DimCategory separata. Una query per aggregare gli articoli venduti per categoria di prodotti potrebbe essere simile all'esempio seguente:
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;
I risultati di questa query includono il numero di articoli venduti per ogni categoria di prodotti:
| ProductCategory | ItemsSold |
|---|---|
| Accessori | 28271 |
| Oggetti vari | 5368 |
| ... | ... |
Nota
Le clausole JOIN per FactSales e DimProduct e per DimProduct e DimCategory sono entrambe obbligatorie, anche se nessun campo di DimProduct viene restituito dalla query.
Uso delle funzioni di rango
Un altro tipo comune di query analitica consiste nel partizionare i risultati in base a un attributo della dimensione e nel classificare i risultati in ordine di priorità all'interno di ogni partizione. È ad esempio possibile classificare i negozi ogni anno in base ai ricavi delle vendite. Per tale scopo, è possibile usare funzioni di classificazione Transact-SQL come ROW_NUMBER, RANK, DENSE_RANK e NTILE. Queste funzioni consentono di partizionare i dati tra più categorie, ciascuna delle quali restituisce un valore specifico che indica la posizione relativa di ogni riga all'interno della partizione:
- ROW_NUMBER restituisce un numero ordinale indicante la posizione della riga all'interno della partizione. Ad esempio, la prima riga ha il numero 1, la seconda il numero 2 e così via.
- RANK restituisce la posizione di ogni riga nei risultati ordinati in base alla priorità. Ad esempio, in una partizione di negozi ordinati in base al volume di vendite, il negozio con il volume di vendite più elevato viene classificato in prima posizione. Se più negozi hanno gli stessi volumi di vendite, verranno classificati allo stesso modo e la posizione assegnata ai negozi successivi rifletterà il numero di negozi con volumi di vendite più elevati, inclusi i casi di parità.
- DENSE_RANK classifica le righe in una partizione allo stesso modo di RANK, ma quando più righe hanno la stessa classificazione, le righe successive ignorano i casi di parità.
- NTILE restituisce il percentile specificato in base al quale viene posizionata la riga. Ad esempio, in una partizione di negozi ordinati per volume di vendite,
NTILE(4)restituisce il quartile in cui viene posizionato il negozio in base al volume di vendite.
Ad esempio, si consideri la query seguente:
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;
La query partiziona i prodotti in più gruppi in base alle relative categorie e, all'interno di ogni partizione di categoria, la posizione relativa di ogni prodotto viene determinata dal rispettivo prezzo di listino. I risultati di questa query potrebbero essere simili alla tabella seguente:
| ProductCategory | ProductName | ListPrice | RowNumber | Classifica | DenseRank | Quartile |
|---|---|---|---|---|---|---|
| Accessori | Widget | 8,99 | 1 | 1 | 1 | 1 |
| Accessori | Knicknak | 8.49 | 2 | 2 | 2 | 1 |
| Accessori | Sprocket | 5,99 | 3 | 3 | 3 | 2 |
| Accessori | Doodah | 5,99 | 4 | 3 | 3 | 2 |
| Accessori | Spangle | 2,99 | 5 | 5 | 4 | 3 |
| Accessori | Badabing | 0.25 | 6 | 6 | 5 | 4 |
| Oggetti vari | Flimflam | 7,49 | 1 | 1 | 1 | 1 |
| Oggetti vari | Snickity wotsit | 6,99 | 2 | 2 | 2 | 1 |
| Oggetti vari | Flange | 4.25 | 3 | 3 | 3 | 2 |
| ... | ... | ... | ... | ... | ... | ... |
Nota
I risultati dell'esempio illustrano la differenza tra RANK e DENSE_RANK. Si noti che nella categoria Accessori i prodotti Sprocket e Doodah hanno lo stesso prezzo di listino e sono entrambi classificati come terzo prodotto più costoso. Il prodotto più costoso successivo ha un valore di RANK pari a 5 (ci sono quattro prodotti più costosi di questo) e un valore di DENSE_RANK pari a 4 (ci sono tre prezzi più elevati).
Per altre informazioni sulle funzioni di classificazione, vedere Funzioni di classificazione (Transact-SQL) nella documentazione di Azure Synapse Analytics.
Recupero di un conteggio approssimativo
Anche se lo scopo di un data warehouse è principalmente quello di supportare report e modelli di dati analitici per l'azienda, gli analisti dei dati e i data scientist devono spesso eseguire un'esplorazione iniziale dei dati, semplicemente per determinare l'entità e la distribuzione di base dei dati.
La query seguente, ad esempio, usa la funzione COUNT per recuperare il numero di vendite per ogni anno in un ipotetico data warehouse:
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;
I risultati di questa query potrebbero essere simili alla tabella seguente:
| CalendarYear | Ordini |
|---|---|
| 2019 | 239870 |
| 2020 | 284741 |
| 2021 | 309272 |
| ... | ... |
A seconda del volume di dati in un data warehouse, anche semplici query per contare il numero di record che soddisfano i criteri specificati possono richiedere molto tempo per l'esecuzione. Molto spesso non è necessario un conteggio preciso, ma è sufficiente una stima approssimativa. In questi casi, è possibile usare la funzione APPROX_COUNT_DISTINCT, come illustrato nell'esempio seguente:
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;
La funzione APPROX_COUNT_DISTINCT usa un algoritmo HyperLogLog per recuperare un conteggio approssimativo. Nel risultato è garantita una percentuale di errore massima del 2% con probabilità del 97%. Pertanto, i risultati di questa query con gli stessi dati ipotetici dell'esempio precedente potrebbero essere simili alla tabella seguente:
| CalendarYear | ApproxOrders |
|---|---|
| 2019 | 235552 |
| 2020 | 290436 |
| 2021 | 304633 |
| ... | ... |
I conteggi sono meno precisi, ma comunque sufficienti per un confronto approssimativo delle vendite annuali. Con un volume elevato di dati, la query che usa la funzione APPROX_COUNT_DISTINCT viene completata più rapidamente e la precisione ridotta può offrire un compromesso accettabile durante l'esplorazione dei dati di base.
Nota
Per altri dettagli, vedere la documentazione relativa alla funzione APPROX_COUNT_DISTINCT.