Eseguire query su un data warehouse

• 10 minuti

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
...	...

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
...	...	...	...	...	...	...

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.