Esercizio - Usare le funzioni finestra

  • 8 minuti

Uno dei componenti chiave delle funzioni finestra è la clausola OVER. Questa clausola determina il partizionamento e l'ordinamento di un set di righe prima dell'applicazione della funzione finestra associata. In altre parole, la clausola OVER definisce una finestra o un set di righe specificato dall'utente all'interno di un set di risultati della query. Una funzione finestra calcola quindi un valore per ogni riga della finestra. È possibile utilizzare la clausola OVER con le funzioni per calcolare i valori aggregati, ad esempio medie mobili, aggregazioni cumulative, totali parziali o i primi N risultati per gruppo.

  1. Aprire Synapse Studio (https://web.azuresynapse.net/).

  2. Selezionare l'hub Sviluppo.

    Accessing the Develop hub

  3. Nel menu Sviluppo selezionare il pulsante + (1) e scegliere Script SQL (2) dal menu di scelta rapida.

    Creating a SQL Script in the Develop hub

  4. Nel menu della barra degli strumenti connettersi al database del pool SQL per eseguire la query.

    Connect to item in the toolbar

  5. Nella finestra di query sostituire lo script con il codice seguente per usare la clausola OVER con i dati della tabella wwi_security.Sale:

    SELECT
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY Quantity DESC) AS "Row Number",
Product,
Quantity,
Region
FROM wwi_security.Sale
WHERE Quantity <> 0  
ORDER BY Region;

  6. Scegliere Esegui dal menu della barra degli strumenti per eseguire il comando SQL.

    Run item in the toolbar

Quando si usa PARTITION BY con la clausola OVER (1), il set di risultati della query viene diviso in partizioni. La funzione finestra viene applicata a ogni singola partizione e il calcolo viene riavviato per ogni partizione.

Using a window function with the row_number function

Lo script eseguito usa la clausola OVER con funzione ROW_NUMBER (1) per visualizzare un numero di riga per ogni riga all'interno di una partizione. La partizione in questo caso è la colonna Region. La clausola ORDER BY (2) specificata nella clausola OVER ordina le righe in ogni partizione in base alla colonna Quantity. La clausola ORDER BY nell'istruzione SELECT determina l'ordine in cui viene restituito l'intero set di risultati della query.

Scorrere verso il basso nella visualizzazione dei risultati fino a quando il conteggio dei numeri di riga (3) non inizia con un'area diversa (4). Poiché la partizione è impostata su Region, il ROW_NUMBER viene reimpostato quando l'area viene modificata. In sostanza, la partizione è stata creata in base all'area e un set di risultati è identificato dal numero di righe in tale area.

Funzioni di aggregazione

Ora, usare le funzioni di aggregazione con la finestra espandendo la query che usa la clausola OVER.

  1. Nella finestra di query sostituire lo script con il codice seguente per aggiungere funzioni di aggregazione:

    SELECT
ROW_NUMBER() OVER(PARTITION BY Region ORDER BY Quantity DESC) AS "Row Number",
Product,
Quantity,
SUM(Quantity) OVER(PARTITION BY Region) AS Total,  
AVG(Quantity) OVER(PARTITION BY Region) AS Avg,  
COUNT(Quantity) OVER(PARTITION BY Region) AS Count,  
MIN(Quantity) OVER(PARTITION BY Region) AS Min,  
MAX(Quantity) OVER(PARTITION BY Region) AS Max,
Region
FROM wwi_security.Sale
WHERE Quantity <> 0  
ORDER BY Region;

  2. Scegliere Esegui dal menu della barra degli strumenti per eseguire il comando SQL.

    Run item in the toolbar

Nella query sono state aggiunte le funzioni di aggregazione SUM, AVG, COUNT, MIN e MAX. L'uso della clausola OVER è più efficace rispetto all'uso di sottoquery.

Using a window function with aggregate functions

Funzioni analitiche

Le funzioni analitiche calcolano un valore di aggregazione basato su un gruppo di righe. A differenza delle funzioni di aggregazione, tuttavia, le funzioni analitiche sono in grado di restituire più righe per ogni gruppo. Usare le funzioni di analisi per calcolare medie mobili, totali parziali, percentuali o i primi N risultati all'interno di un gruppo.

Tailwind Traders dispone di dati sulle vendite dei libri che importa dal suo store online e vuole calcolare le percentuali di download dei libri in base alla categoria.

A tale scopo, si decide di creare funzioni finestra che usano le funzioni PERCENTILE_CONT e PERCENTILE_DISC.

  1. Nella finestra di query sostituire lo script con il codice seguente per aggiungere funzioni di aggregazione:

    -- PERCENTILE_CONT, PERCENTILE_DISC
SELECT DISTINCT c.Category  
,PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY bc.Downloads)
                    OVER (PARTITION BY Category) AS MedianCont  
,PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY bc.Downloads)
                    OVER (PARTITION BY Category) AS MedianDisc  
FROM dbo.Category AS c  
INNER JOIN dbo.BookList AS bl
    ON bl.CategoryID = c.ID
INNER JOIN dbo.BookConsumption AS bc  
    ON bc.BookID = bl.ID
ORDER BY Category

  2. Scegliere Esegui dal menu della barra degli strumenti per eseguire il comando SQL.

    Run item in the toolbar

    Using a window function with analytical functions

In questa query vengono usate le funzioni PERCENTILE_CONT (1) e PERCENTILE_DISC (2) per trovare il valore mediano di download in ogni categoria di libri. È possibile che queste funzioni non restituiscano lo stesso valore. PERCENTILE_CONT esegue l'interpolazione del valore appropriato, che può esistere o meno nel set di dati, mentre PERCENTILE_DISC restituisce sempre un valore effettivo dal set. Per spiegare meglio, PERCENTILE_DISC calcola un percentile specifico per i valori ordinati in un intero set di righe o all'interno di partizioni distinte di un set di righe.

Il valore 0,5 passato alle funzioni percentile (1 e 2) calcola il 50° percentile, o mediana, dei download.

L'espressione WITHIN GROUP (3) specifica un elenco di valori da ordinare e su cui calcolare il percentile. È consentita una sola espressione ORDER BY e l'ordinamento predefinito è quello crescente.

La clausola OVER (4) divide il set di risultati della clausola FROM in partizioni, in questo caso in base alla categoria. A queste partizioni viene applicata la funzione di percentile.

  1. Nella finestra di query sostituire lo script con il codice seguente per usare la funzione di analisi LAG:

    --LAG Function
SELECT ProductId,
    [Hour] AS SalesHour,
    TotalAmount AS CurrentSalesTotal,
    LAG(TotalAmount, 1,0) OVER (ORDER BY [Hour]) AS PreviousSalesTotal,
    TotalAmount - LAG(TotalAmount,1,0) OVER (ORDER BY [Hour]) AS Diff
FROM [wwi_perf].[Sale_Index]
WHERE ProductId = 3848 AND [Hour] BETWEEN 8 AND 20;

Tailwind Traders vuole confrontare i totali delle vendite di un prodotto nel tempo su base oraria, mostrando la differenza nel valore.

A questo scopo si usa la funzione di analisi LAG. Questa funzione accede ai dati da una riga precedente nello stesso set di risultati senza l'utilizzo di un self-join. LAG fornisce l'accesso a una riga situata a una distanza fisica specificata e precedente rispetto alla riga corrente. Questa funzione di analisi viene usata per confrontare i valori nella riga corrente con i valori in una riga precedente.

  1. Scegliere Esegui dal menu della barra degli strumenti per eseguire il comando SQL.

    Run item in the toolbar

    Viewing window function with analytical functions results

In questa query viene usata la funzione LAG (1) per restituire la differenza nelle vendite (2) di un prodotto specifico negli orari di vendita di picco (8-20). Viene inoltre calcolata la differenza nelle vendite da una riga alla successiva (3). Si noti che poiché non è presente alcun valore di ritardo disponibile per la prima riga, viene restituita l'impostazione predefinita zero (0).

Clausola ROWS

Le clausole ROWS e RANGE limitano ulteriormente le righe nella partizione specificando i punti di inizio e di fine all'interno della partizione. A tale scopo, è necessario specificare un intervallo di righe rispetto alla riga corrente in base all'associazione logica o all'associazione fisica. L'associazione fisica viene eseguita mediante la clausola ROWS.

Tailwind Traders vuole trovare i libri con il minor numero di download per paese/regione, visualizzando il numero totale di download di ogni libro in ogni paese/regione in ordine crescente.

A questo scopo, si usa ROWS in combinazione con UNBOUNDED PRECEDING per limitare le righe all'interno della partizione Country, specificando che la finestra inizia con la prima riga della partizione.

  1. Nella finestra di query sostituire lo script con il codice seguente per aggiungere funzioni di aggregazione:

    -- ROWS UNBOUNDED PRECEDING
SELECT DISTINCT bc.Country, b.Title AS Book, bc.Downloads
    ,FIRST_VALUE(b.Title) OVER (PARTITION BY Country  
        ORDER BY Downloads ASC ROWS UNBOUNDED PRECEDING) AS FewestDownloads
FROM dbo.BookConsumption AS bc
INNER JOIN dbo.Books AS b
    ON b.ID = bc.BookID
ORDER BY Country, Downloads

  2. Scegliere Esegui dal menu della barra degli strumenti per eseguire il comando SQL.

    Run item in the toolbar

    Viewing window function with rows unbounded preceding results

In questa query viene usata la funzione di analisi FIRST_VALUE per recuperare il titolo del libro con il minor numero di download, come indicato dalla clausola ROWS UNBOUNDED PRECEDING sulla partizione Country (1). L'opzione UNBOUNDED PRECEDING imposta l'inizio della finestra sulla prima riga della partizione, restituendo il titolo del libro con il minor numero di download per il paese/regione all'interno della partizione.

Nel set di risultati è possibile scorrere l'elenco dei libri per paese/regione, ordinato in base al numero di download in ordine crescente.