Corso SQL Server

SQL Server lezione #05 Come codificare query di riepilogo

 

Come codificare query di riepilogo

In questa lezione si apprende come codificare le query che riassumono i dati. Ad esempio, è possibile utilizzare query di riepilogo per riportare i totali delle vendite per fornitore o stato, oppure per ottenere un conteggio del numero di fatture elaborate ogni giorno del mese. Imparerete anche a usare un tipo speciale di funzione, chiamata funzione aggregate. Le funzioni aggregate consentono di eseguire facilmente operazioni come calcolare medie o totali o trovare il valore più alto per una determinata colonna. Le utilizzerete spesso nelle vostre query di riepilogo.

Come lavorare con le funzioni aggregate

In questo capitolo imparerete a usare le funzioni aggregate, che operano su una serie di valori e restituiscono un singolo valore di riepilogo. Poiché le funzioni aggregate operano tipicamente sui valori delle colonne, vengono talvolta chiamate funzioni di colonna. Una query che contiene una o più funzioni aggregate viene generalmente chiamata query di riepilogo.

Come codificare le funzioni aggregate

L'esempio qui sotto presenta la sintassi delle funzioni aggregate più comuni. Poiché lo scopo di queste funzioni si spiega da sé, mi concentrerò principalmente sul loro utilizzo. Tutte le funzioni, tranne una, operano su un'espressione. Nella query di questo esempio, l'espressione codificata per la funzione SUM calcola il saldo di una fattura utilizzando le colonne lnvoiceTotal, PaymentTotal e CreditTotal. Il risultato è un singolo valore che rappresenta l'importo totale di tutte le fatture selezionate. Se si esamina la clausola WHERE di questo esempio, si può notare che include solo le fatture con un saldo da pagare. Oltre a un'espressione, è possibile codificare la parola chiave ALL o DISTINCT in queste funzioni. ALL è l'opzione predefinita, che significa che tutti i valori sono inclusi nel calcolo. L'eccezione è rappresentata dai valori nulli, che sono sempre esclusi da queste funzioni. Se non si desidera includere i valori duplicati, è possibile utilizzare la parola chiave DISTINCT. Nella maggior parte dei casi, si usa DISTINCT solo con la funzione COUNT. Non si usa con MIN o MAX perché non ha alcun effetto su queste funzioni. E di solito non ha senso usarla con le funzioni AVG e SUM. A differenza delle altre funzioni di aggregazione, non è possibile utilizzare le parole chiave ALL o DISTINCT o un'espressione con COUNT(*). Questa funzione deve essere codificata esattamente come mostrato nella sintassi. Il valore restituito da questa funzione è il numero di righe nella tabella di base che soddisfano la condizione di ricerca della query, incluse le righe con valori nulli. La funzione COUNT(*) nella query di questa figura, ad esempio, indica che la tabella Fatture contiene 11 fatture con un saldo da pagare.

La sintassi delle funzioni aggregate

Funzioni aggregate
Sintassi delle funzioniRisultato
AVG ([ALL | DISTINCT] expression)La media dei valori non nulli dell'espressione.
SUM ([ALL | DISTINCT] expression)Il totale dei valori non nulli nell'espressione.
MIN ([ALL | DISTINCT] expression)Il valore non nullo più basso dell'espressione.
MAX ([ALL | DISTINCT] expression)Il valore non nullo più alto dell'espressione.
COUNT ( [ALL | DISTINCT] expression)Il numero di valori non nulli nell'espressione.
COUNT (*)Il numero di righe selezionate dalla query.

Una query di riepilogo che conta le fatture non pagate e calcola il totale dovuto

    USE AP
    GO                    
    SELECT COUNT (*) AS NumberOfinvoices,
        SUM (InvoiceTotal - PaymentTotal - CreditTotal ) AS TotalDue
    FROM Invoices
    WHERE (InvoiceTotal - PaymentTotal - CreditTotal) > 0

Descrizione

Query che utilizzano funzioni aggregate

L'esempio qui sotto presenta altre quattro query che utilizzano funzioni aggregate. Prima di descrivere queste query, è necessario sapere che, con tre eccezioni, una clausola SELECT che contiene una funzione aggregata può contenere solo funzioni aggregate. La prima eccezione è se la specifica della colonna risulta in un valore letterale. Questo è illustrato dalla prima colonna nelle prime due query di questa figura. La seconda eccezione è se la query include una clausola GROUP BY. In questo caso, la clausola SELECT può includere tutte le colonne specificate nella clausola GROUP BY. La terza eccezione è se le funzioni aggregate includono la clausola OVER. In questo caso, la clausola SELECT può includere qualsiasi colonna delle tabelle di base. Si vedrà come si usano le clausole GROUP BY e OVER più avanti in questa lezione. Le prime due query di questa figura utilizzano la funzione COUNT(*) per contare il numero di righe della tabella Fatture che soddisfano la condizione di ricerca. In entrambi i casi, solo le fatture con data di fatturazione successiva al 15/12/2022 sono incluse nel conteggio. Inoltre, la prima query utilizza la funzione AVG per calcolare l'importo medio delle fatture e la funzione SUM per calcolare l'importo totale delle fatture. La seconda query, invece, utilizza le funzioni MIN e MAX per calcolare l'importo minimo e massimo delle fatture. Sebbene le funzioni MIN, MAX e COUNT siano tipicamente utilizzate per le colonne che contengono dati numerici, possono essere utilizzate anche su colonne contenenti dati di carattere o di data. Nella terza query, ad esempio, vengono usate sulla colonna VendorName della tabella Vendors. In questo caso, la funzione MIN restituisce il nome del fornitore che si trova più in basso nella sequenza di ordinamento, la funzione MAX restituisce il nome del fornitore che si trova più in alto nella sequenza di ordinamento e la funzione COUNT restituisce il numero totale di fornitori. Si noti che, poiché la colonna VendorName non può contenere valori nulli, la funzione COUNT(*) avrebbe restituito lo stesso risultato. La quarta query illustra come l'uso della parola chiave DISTINCT possa influenzare il risultato di una funzione COUNT. Qui, la prima funzione COUNT utilizza la parola chiave DISTINCT per contare il numero di fornitori che hanno fatture datate 2015-09-01 o successive nella tabella Fatture. Per farlo, cerca valori distinti nella colonna VendorID. la colonna VendorID. Al contrario, poiché la seconda funzione COUNT non include la parola chiave DISTINCT, conta tutte le fatture successive al 2015-09-01. Naturalmente è possibile ottenere lo stesso risultato utilizzando la funzione COUNT(*). Ho usato COUNT(VendorlD) solo per illustrare la differenza tra la codifica e la non codifica della parola chiave DISTINCT.

Query di riepilogo che utilizza le funzioni COUNT(*), AVG e SUM

    USE AP
    GO
    SELECT 'After 15/12/2022' AS SelectionDate,
        COUNT (*) AS NumberOfinvoices,
        AVG (InvoiceTotal) AS AverageinvoiceAmount,
        SUM (InvoiceTotal) AS TotalinvoiceAmount
    FROM Invoices
    WHERE InvoiceDate > '2022-12-15'

Una query di riepilogo che utilizza le funzioni MIN e MAX

    USE AP
    GO
    SELECT 'After 15/12/2022' AS SelectionDate,
        COUNT (*) AS NumberOfinvoices,
        MAX (InvoiceTotal) AS HighestinvoiceTotal,
        MIN (InvoiceTotal) AS LowestinvoiceTotal
    FROM Invoices
    WHERE InvoiceDate > '2022-12-15'

Una query di riepilogo che funziona su colonne non numeriche

    USE AP
    GO
    SELECT MIN (VendorName) AS Firstvendor,
        MAX (VendorName) AS Lastvendor,
        COUNT (VendorName) AS NumberOfVendors
    FROM Vendors

Una query di riepilogo che utilizza la parola chiave DISTINCT

    USE AP
    GO                    
    SELECT COUNT (DISTINCT VendorID) AS NumberOfVendors,
        COUNT (VendorID) AS NumberOfinvoices,
        AVG (InvoiceTotal) AS AverageinvoiceAmount,
        SUM (InvoiceTotal) AS TotalinvoiceAmount
    FROM Invoices
    WHERE InvoiceDate > '2022-12-15'

Note

Come raggruppare e riassumere i dati

Ora che avete capito come funzionano le funzioni aggregate, siete pronti per imparare a raggruppare i dati e a usare le funzioni aggregate per riassumere i dati di ciascun gruppo. Per farlo, è necessario conoscere altre due clausole dell'istruzione SELECT: GROUP BY e HAVING.

Come codificare le clausole GROUP BY e HAVING

L'esempio qui sotto presenta la sintassi dell'istruzione SELECT con le clausole GROUP BY e HAVING. La clausola GROUP BY determina come vengono raggruppate le righe selezionate e la clausola HAVING determina quali gruppi sono inclusi nei risultati finali. Come si può notare, queste clausole sono codificate dopo la clausola WHERE ma prima della clausola ORDER BY. Questo ha senso perché la condizione di ricerca nella clausola WHERE viene applicata prima che le righe vengano raggruppate. La sequenza di ordinamento nella clausola ORDER BY viene applicata dopo il raggruppamento delle righe. Nella clausola GROUP BY, si elencano una o più colonne o espressioni separate da virgole. Quindi, le righe che soddisfano la condizione di ricerca nella clausola WHERE vengono raggruppate in base a queste colonne o espressioni in ordine crescente. Ciò significa che viene restituita una singola riga per ogni serie unica di valori nelle colonne GROUP BY. Ciò avrà più senso quando si vedranno gli esempi nella prossima figura che raggruppano per due colonne. Per ora, guardate l'esempio di questa figura che raggruppa per una sola colonna. Questo esempio calcola l'importo medio delle fatture per ogni fornitore che ha fatture nella tabella Fatture di importo medio superiore a 2.000 dollari. Per farlo, raggruppa le le fatture per VendorID. Quindi, la funzione AVG calcola la media della colonna lnvoiceTotal. Poiché la query include una clausola GROUP BY, questa funzione calcola il totale medio delle fatture per ogni gruppo e non per l'intero set di risultati. In questo caso, la funzione aggregata è chiamata aggregato vettoriale. Al contrario, le funzioni di aggregazione come quelle viste in precedenza in questo capitolo che restituiscono un singolo valore per tutte le righe di un insieme di risultati sono chiamate aggregati scalari. L'esempio di questa figura include anche una clausola HAVING. La condizione di ricerca questa clausola specifica che solo i fornitori con fatture che superano in media i 2.000 dollari. Si noti che questa condizione deve essere applicata dopo che le righe sono state raggruppate e la media per ogni gruppo è stata calcolata. Oltre alla funzione AVG, la clausola SELECT include la colonna VendorID. Questo ha senso dato che le righe sono raggruppate in base a questa colonna. Tuttavia, le colonne utilizzate nella clausola GROUP BY non devono essere incluse nella clausola SELECT.

La sintassi dell'istruzione SELECT con le clausole GROUP BY e HAVING

    SELECT select_list
    FROM table_source
    [WHERE search_condition]
    [GROUP BY group_by_list]
    [HAVING search_condition]
    [ORDER BY order_by_list]

Una query di riepilogo che calcola l'importo medio delle fatture per fornitore

    USE AP
    GO
    SELECT VendorID, AVG(InvoiceTotal) AS AverageinvoiceAmount
    FROM Invoices
    GROUP BY VendorID
    HAVING AVG(InvoiceTotal) > 2000
    ORDER BY AverageinvoiceAmount DESC

Descrizione

Le query che utilizzano le clausole GROUP BY e le clausole HAVING

L'esempio qui sotto presenta altre tre query che raggruppano i dati. Se si è compresa la query della figura precedente, non si dovrebbero avere problemi a capire come funziona la prima query di questa figura. Raggruppa le righe della tabella Fatture per VendorID e restituisce un conteggio del numero di fatture per ogni fornitore. La seconda query di questa figura illustra come sia possibile raggruppare per più di una colonna. In questo caso, si utilizza un join per combinare le colonne VendorState e VendorCity della tabella Vendors con il conteggio e la media delle fatture della tabella Invoices. Poiché le righe sono raggruppate per stato e città, viene restituita una riga per ogni combinazione di stato e città. Quindi, la clausola ORDER BY ordina le righe per città all'interno dello stato. Senza questa clausola, le righe verrebbero restituite in nessuna sequenza particolare. La terza query è identica alla seconda, ma include la clausola HAVING. Questa clausola utilizza la funzione COUNT per limitare i gruppi di stati e città inclusi nell'insieme dei risultati a quelli che hanno due o più fatture. In altre parole, esclude i gruppi che hanno una sola fattura.

Una query di riepilogo che conta il numero di fatture per fornitore

    USE AP
    GO
    SELECT VendorID, COUNT(*) AS InvoiceQty
    FROM Invoices
    GROUP BY VendorID

Una query di riepilogo che calcola il numero di fatture e l'importo medio delle fatture per i fornitori di ogni stato e città.

    USE AP
    GO
    SELECT VendorState, VendorCity, COUNT(*) AS InvoiceQty,
        AVG(InvoiceTotal) AS InvoiceAvg
    FROM Invoices JOIN Vendors
        ON Invoices.VendorID = Vendors.VendorID
    GROUP BY VendorState, VendorCity
    ORDER BY VendorState, VendorCity

Una query di riepilogo che limita i gruppi a quelli che hanno due o più fatture

    USE AP
    GO
    SELECT VendorState, VendorCity, COUNT(* ) AS InvoiceQty,
        AVG(InvoiceTotal) AS InvoiceAvg
    FROM Invoices JOIN Vendors
        ON Invoices.VendorID = Vendors.VendorID
    GROUP BY VendorState, VendorCity
    HAVING COUNT(*) >= 2
    ORDER BY VendorState, VendorCity

Note

Come la clausola HAVING si confronta con la clausola WHERE

Come si è visto, è possibile limitare i gruppi inclusi in un insieme di risultati codificando una condizione di ricerca nella clausola HAVING. Inoltre, è possibile applicare una condizione di ricerca a ogni riga prima che venga inclusa in un gruppo. A tale scopo, si codifica la condizione condizione di ricerca nella clausola WHERE come per qualsiasi istruzione SELECT. Per essere sicuri di comprendere le differenze tra le condizioni di ricerca codificate nelle clausole HAVING e WHERE, la figura successiva presenta due esempi. Nel primo esempio, le fatture della tabella Invoices sono raggruppate in base al nome del fornitore e per ogni gruppo vengono calcolati il conteggio e l'importo medio della fattura. Poi, la clausola HAVING limita i gruppi nel set di risultati a quelli che hanno un totale medio delle fatture superiore a 500 dollari. Al contrario, il secondo esempio include una condizione di ricerca nella clausola WHERE che limita le fatture incluse nei gruppi a quelle che hanno un totale della fattura superiore a 500 dollari. In altre parole, la condizione di ricerca in questo esempio viene applicata a ogni riga. Nell'esempio precedente, invece, è stata applicata a ogni gruppo di righe. Oltre a questo, esistono anche due differenze nelle espressioni che si possono includere nelle clausole WHERE e HAVING. Innanzitutto, la clausola HAVING può includere funzioni di aggregazione, come si è visto nel primo esempio di questa figura, ma la clausola WHERE non può. Questo perché la condizione di ricerca in una clausola WHERE viene applicata prima che le righe vengano raggruppate. In secondo luogo, sebbene la clausola WHERE può fare riferimento a qualsiasi colonna delle tabelle di base, la clausola HAVING può fare riferimento solo a colonne incluse nella clausola SELECT o GROUP BY. Questo perché filtra l'insieme dei risultati riassunti definito dalle clausole SELECT, FROM, WHERE e GROUP BY. In altre parole, non filtra le tabelle di base.

Una query di riepilogo con una condizione di ricerca nella clausola HAVING

    USE AP
    GO
    SELECT VendorName, COUNT(*) AS InvoiceQty,
        AVG(InvoiceTotal) AS InvoiceAvg
    FROM Vendors JOIN Invoices
        ON Vendors.VendorID = Invoices.VendorID
    GROUP BY VendorName
    HAVING AVG(InvoiceTotal) > 500
    ORDER BY InvoiceQty DESC

Una query di riepilogo con una condizione di ricerca nella clausola WHERE

    USE AP
    GO
    SELECT VendorName, COUNT(*) AS InvoiceQty,
        AVG(InvoiceTotal) AS InvoiceAvg
    FROM Vendors JOIN Invoices
        ON Vendors.VendorID = Invoices.VendorID
    WHERE InvoiceTotal > 500
    GROUP BY VendorName
    ORDER BY InvoiceQty DESC

Descrizione

Come codificare condizioni di ricerca complesse

È possibile codificare condizioni di ricerca composte in una clausola HAVING proprio come in una clausola WHERE. Questo è illustrato dalla prima query degli esempi che seguono. Questa raggruppa le fatture per data di fatturazione e calcola il conteggio delle fatture e la somma dei totali delle fatture per ogni data. Inoltre, la clausola HAVING specifica tre condizioni. In primo luogo, la data della fattura deve essere compresa tra 2022-12-15 e 2023-03-15. In secondo luogo, il conteggio delle fatture deve essere maggiore di 1. Infine, la somma dei totali delle fatture deve essere superiore a 100 dollari. Dato che la seconda e la terza condizione nella clausola HAVING di questa query includono funzioni aggregate, devono essere codificate nella clausola HAVING. La prima condizione, invece, non include una funzione aggregata, quindi può essere codificata sia nella clausola HAVING che in quella WHERE. La seconda istruzione in questa figura, ad esempio, mostra questa condizione codificata nella clausola WHERE. Si noti che restituisce lo stesso insieme di risultati indipendentemente dalla codifica di questa condizione. Come si fa a sapere dove codificare una condizione di ricerca? In generale, credo che il codice sia più facile da leggere se si includono tutte le condizioni di ricerca nella clausola HAVING. Se, invece, si preferisce codificare le condizioni di ricerca non aggregate nella clausola WHERE, va bene lo stesso. Poiché una condizione di ricerca nella clausola WHERE viene applicata prima del raggruppamento delle righe raggruppate, mentre una condizione di ricerca nella clausola HAVING viene applicata solo dopo il raggruppamento. Ci si potrebbe aspettare un vantaggio in termini di prestazioni codificando tutte le condizioni di ricerca nella clausola HAVING. Tuttavia, SQL Server si occupa di questo problema di prestazioni quando ottimizza la query. A tal fine, sposta automaticamente le condizioni di ricerca nella clausola che garantisce le migliori prestazioni, purché ciò non modifichi la logica della query. Di conseguenza, è possibile codificare le condizioni di ricerca dove il codice risulta più leggibile senza preoccuparsi delle prestazioni del sistema.

Una query di riepilogo con una condizione composta nella clausola HAVING

    USE AP
    GO
    SELECT InvoiceDate, COUNT (*) AS InvoiceQty, SUM (InvoiceTotal) AS InvoiceSum
    FROM Invoices
    GROUP BY InvoiceDate
    HAVING InvoiceDate BETWEEN '2022-12-15' AND '2023-03-15'
        AND COUNT (*) > 1
        AND SUM (InvoiceTotal) > 100
    ORDER BY InvoiceDate DESC

La stessa query codificata con una clausola WHERE

    USE AP
    GO
    SELECT InvoiceDate, COUNT (*) AS InvoiceQty, SUM (InvoiceTotal ) AS InvoiceSum
    FROM Invoices
    WHERE InvoiceDate BETWEEN '2022-12-15' AND '2023-03-15'
    GROUP BY InvoiceDate
    HAVING COUNT (*) > 1
        AND SUM (InvoiceTotal ) > 100
    ORDER BY InvoiceDate DESC

Descrizione

Come riassumere i dati utilizzando le estensioni di SQL Server

Finora in questa lezione sono state trattate le parole chiave e le funzioni standard di SQL. Tuttavia, è necessario conoscere anche le quattro estensioni che SQL Server mette a disposizione per il riepilogo dei dati: gli operatori ROLLUP, CUBE e GROUPING SETS e la clausola OVER.

Come utilizzare l'operatore ROLLUP

È possibile utilizzare l'operatore ROLLUP per aggiungere una o più righe di riepilogo a un insieme di risultati che utilizza raggruppamenti e aggregati. I due esempi che seguono illustrano come funziona. Il primo esempio mostra come funziona l'operatore ROLLUP quando si raggruppa per una singola colonna. In questo caso, le fatture nella tabella Fatture sono raggruppate per VendorID e per ogni fornitore vengono calcolati il conteggio e il totale delle fatture. Si noti che, poiché la frase WITH ROLLUP è inclusa nella clausola GROUP BY, viene aggiunta un'ulteriore riga alla fine dell'insieme dei risultati. Questa riga riepiloga tutte le colonne aggregate nel set di risultati. In questo caso, riassume le colonne lnvoiceCount e lnvoiceTotal. Poiché la colonna VendorID non può essere riassunta, le viene assegnato un valore nullo. La seconda query in questa figura mostra come funziona l'operatore ROLLUP quando si raggruppa per due colonne. Questa query raggruppa i venditori nella tabella Vendors per stato e città e conta il numero di venditori in ciascun gruppo. Si noti che, oltre a una riga di riepilogo alla fine del set di risultati, sono incluse righe di riepilogo per ogni stato. Si noti anche la clausola ORDER BY di questa query. Essa fa sì che le righe del set di risultati siano ordinate per città in sequenza decrescente e per stato in sequenza decrescente. Il motivo per cui queste colonne sono ordinate in sequenza decrescente è che l'ordinamento viene eseguito dopo che le righe di riepilogo sono state aggiunte all'insieme dei risultati e queste righe hanno valori nulli nella colonna VendorCity. Inoltre, la riga di riepilogo finale ha un valore nullo nella colonna VendorState. Quindi, se si ordinassero queste colonne in ordine crescente, le righe con valori nulli apparirebbero prima delle righe che riassumono, il che non è quello che si vuole. È inoltre possibile utilizzare un'altra funzione, la funzione GROUPING (raggruppamento), per lavorare con le colonne nulle in una riga di riepilogo. Tuttavia, questa funzione viene tipicamente utilizzata assieme alla funzione CASE, di cui si parlerà nella lezione 9. Quindi la funzione GROUPING verrà presentata in quella lezione. SQL Server 2008 ha introdotto una sintassi alternativa che può essere utilizzata per lavorare con l'operatore ROLLUP, ed è mostrata sotto entrambi gli esempi. Per utilizzare questa sintassi, si codifica l'operatore ROLLUP subito dopo le parole chiave GROUP BY. Quindi, si codificano le colonne all'interno di una serie di parentesi. Il vantaggio di questa sintassi è che è più coerente con le altre estensioni di SQL Server. Lo svantaggio è che questa sintassi non è supportata dalle versioni precedenti di SQL Server come SQL Server 2005.

Una query di riepilogo che include una riga di riepilogo finale

    USE AP
    GO                    
    SELECT VendorID, COUNT (*) AS InvoiceCount,
        SUM (InvoiceTotal) AS InvoiceTotal
    FROM Invoices
    WHERE InvoiceDate BETWEEN '2022-12-15' AND '2023-03-15'    
    GROUP BY VendorID WITH ROLLUP

Un altro modo per codificare la clausola GROUP BY (SQL Server 2008 o successivo)

    GROUP BY ROLLUP (VendorID)

Una query di riepilogo che include una riga di riepilogo per ogni livello di raggruppamento

    USE AP
    GO
    SELECT VendorState, VendorCity, COUNT (*) AS QtyVendors
    FROM Vendors
    WHERE VendorState IN ( 'IA', 'NJ' )
    GROUP BY VendorState, VendorCity WITH ROLLUP
    ORDER BY VendorState DESC, VendorCity DESC

Un altro modo per codificare la clausola GROUP BY (SQL Server 2008 o successivo)

    GROUP BY ROLLUP (VendorState, VendorCity)

Descrizione

Come utilizzare l'operatore CUBE

L'esempio che segue mostra come utilizzare l'operatore CUBE. Questo operatore è simile all'operatore ROLLUP, ma aggiunge righe di riepilogo per ogni combinazione di gruppi. Questo è illustrato dai due esempi della figura. Come si può notare, questi esempi sono identici a quelli precedenti, tranne che per l'uso dell'operatore CUBE al posto dell'operatore ROLLUP. Nel primo esempio, il set di risultati è raggruppato per una singola colonna. In questo caso, alla fine del set di risultati viene aggiunta una riga di riepilogo che riassume tutti i gruppi. In altre parole, il funzionamento è identico a quello dell'operatore ROLLUP. Nel secondo esempio, invece, si può notare come CUBE differisca da ROLLUP quando si raggruppa per due o più colonne. In questo caso, il set di risultati include una riga di riepilogo per ogni stato, proprio come quando è stato utilizzato l'operatore ROLLUP. Inoltre, include una riga di riepilogo per ogni città. L'ottava riga in questa figura, ad esempio, indica che ci sono due venditori nelle città di nome Washington. Se si osservano la prima e la quinta riga dell'insieme di risultati, si vedrà che uno di questi venditori si trova a Washington, New Jersey e uno a Washington, Iowa. Lo stesso vale per la città di Fairfield. Ci sono anche due fornitori nella città di East Brunswick, ma entrambi si trovano nel New Jersey. Come per l'operatore ROLLUP, SQL Server 2008 ha introdotto una sintassi alternativa che è possibile utilizzare per lavorare con l'operatore CUBE. Questa sintassi è mostrata di seguito a entrambi gli esempi. Se avete capito come funziona la sintassi per l'operatore ROLLUP, non ci dovrebbero avere problemi a capire come funziona per l'operatore CUBE. Anche se questa sintassi non funziona per le versioni di SQL Server precedenti alla 2008, è possibile utilizzarla per i nuovi sviluppi. Ora che avete visto come funziona l'operatore CUBE, vi starete chiedendo quando utilizzarlo. Il fatto è che probabilmente non lo userete se non per aggiungere una riga di riepilogo a un insieme di risultati raggruppati per una singola colonna. E in quel caso, si potrebbe usare l'operatore ROLLUP. In alcuni casi particolari, tuttavia, l'operatore CUBE può fornire informazioni utili che non si possono ottenere in altro modo.

Una query di riepilogo che include una riga di riepilogo finale

    USE AP
    GO
    SELECT VendorID, COUNT (*) AS InvoiceCount,
        SUM (InvoiceTotal) AS InvoiceTotal
    FROM Invoices
    WHERE InvoiceDate BETWEEN '2022-12-15' AND '2023-03-15'    
    GROUP BY VendorID WITH CUBE

Un altro modo per codificare la clausola GROUP BY (SQL Server 2008 o successivo)

    GROUP BY CUBE (VendorID)

Una query di riepilogo che include una riga di riepilogo per ogni gruppo di gruppi.

    USE AP
    GO
    SELECT VendorState, VendorCity, COUNT ( * ) AS QtyVendors
    FROM Vendors
    WHERE VendorState IN ( 'IA', 'NJ' )
    GROUP BY VendorState, VendorCity WITH CUBE
    ORDER BY VendorState DESC, VendorCity DESC

Un altro modo per codificare la clausola GROUP BY (SQL Server 2008 o successivo)

    GROUP BY CUBE (VendorState, VendorCity)

Descrizione

Come utilizzare l'operatore GROUPING SETS

L'esempio che segue mostra come utilizzare l'operatore GROUPING SETS, introdotto con SQL Server 2008. Questo operatore è simile agli operatori ROLLUP e CUBE. Tuttavia, l'operatore GROUPING SETS include solo le righe di riepilogo per ogni gruppo specificato e utilizza una sintassi simile a quella del 2008 per gli operatori ROLLUP e CUBE. Il primo esempio di questa figura è simile al secondo esempio presentato nelle due figure precedenti. Tuttavia, questo esempio utilizza l'operatore GROUPING SETS invece dell'operatore CUBE o ROLLUP. Qui, il set di risultati include solo righe di riepilogo per i due elementi di raggruppamento: stato e città. Per iniziare, vengono visualizzate le righe di riepilogo per gli stati. Quindi vengono visualizzate le righe di riepilogo per le città. Il secondo esempio in questa figura mostra alcune funzioni aggiuntive che sono disponibili quando si utilizza l'operatore GROUPING SETS. Per cominciare, all'interno delle parentesi dopo l'operatore GROUPING SETS, è possibile aggiungere altri gruppi di parentesi per creare gruppi composti da più colonne. Inoltre, è possibile aggiungere un insieme vuoto di parentesi per aggiungere un gruppo per una riga di riepilogo che riassume l'intero risultato. In questo esempio, il primo gruppo è costituito dallo Stato e città del fornitore, il secondo gruppo è il codice postale del fornitore e il terzo gruppo è un insieme vuoto di parentesi, che aggiunge una riga di riepilogo per l'intero set di risultati. Quando si utilizzano i gruppi compositi, si deve sapere che è possibile aggiungere altre righe di riepilogo aggiuntive applicando gli operatori ROLLUP e CUBE a un gruppo composito. A tale scopo, si codifica l'operatore ROLLUP o CUBE prima del gruppo composito. In questa figura, ad esempio, il terzo esempio mostra una clausola GROUPING SETS che applica l'operatore ROLLUP al gruppo composito stato/città. Questo aggiunge una riga di riepilogo per ogni stato e una riga di riepilogo per l'intero set di risultati. Anche se all'inizio può sembrare confuso, con un po' di sperimentazione si può ottenere l'insieme dei risultati desiderato.

Una query di riepilogo con due gruppi

    USE AP
    GO                    
    SELECT VendorState, VendorCity, COUNT(*) AS QtyVendors
    FROM Vendors
    WHERE VendorState IN ('IA', 'NJ')
    GROUP BY GROUPING SETS(VendorState, VendorCity)
    ORDER BY VendorState DESC, VendorCity DESC

Una query di riepilogo con due gruppi

    USE AP
    GO
    SELECT VendorState, VendorCity, VendorZipCode,
    COUNT(*) AS QtyVendors
    FROM Vendors
    WHERE VendorState IN ('IA', 'NJ')
    GROUP BY GROUPING SETS((VendorState, VendorCity), VendorZipCode, ())
    ORDER BY VendorState DESC, VendorCity DESC

Una query di riepilogo con un gruppo che utilizza l'operatore ROLLUP

    GROUP BY GROUPING SETS(ROLLUP(VendorState, VendorCity), VendorZipCode)

Descrizione

Come utilizzare la clausola OVER

Finora, in questa lezione, si è appreso come codificare le query di riepilogo che restituiscono solo i dati riassunti. Ma cosa succede se si vogliono restituire le singole righe che sono state utilizzate per calcolare i riepiloghi insieme ai dati del riepilogo? Per farlo, è possibile utilizzare la clausola OVER, come mostrato nell'esempio che segue. Nella sintassi in cima a questa figura, si può notare che la clausola OVER viene codificata dopo la funzione aggregata, seguita da una serie di parentesi. All'interno delle parentesi, è possibile codificare una clausola PARTITION BY, una clausola ORDER BY o entrambe. Questo è illustrato dai tre esempi di questa figura. Il primo esempio calcola il totale, il conteggio e la media delle fatture della tabella Fatture. In questo caso, si è utilizzata la clausola PARTITION BY per indicare che le fatture devono essere raggruppate per data della fattura. Se si osservano i risultati di questa query, si noterà che la tabella Fatture contiene una sola fattura per le prime tre date. Per questo motivo, le colonne Totale, Conteggio e Media per queste date sono calcolate sulla base di un'unica fattura. Al contrario, le tre fatture successive si riferiscono alla stessa data. In questo caso, le colonne totale, conteggio e media sono calcolate in base a tutte e tre le fatture. Il secondo esempio è simile, ma utilizza la clausola ORDER BY invece della clausola PARTITION BY. Per questo motivo, i calcoli non sono raggruppati in base alla data della fattura come nel primo esempio. Invece, i riepiloghi si accumulano da una data all'altra. Ad esempio, le colonne totale e media per la prima fattura sono uguali al totale della fattura, perché sono calcolati solo su quel totale. Le colonne totale e media per la seconda fattura, invece, sono calcolate in base sia alla prima che alla seconda fattura. Le colonne totale e la media della terza fattura sono calcolate in base alla prima, alla seconda e alla terza fattura. E così via. Inoltre, la colonna del conteggio indica la sequenza delle date delle fatture all'interno del set di risultati. Un totale che si accumula in questo modo si chiama totale cumulativo e una media calcolata sulla base di un totale cumulativo si chiama media mobile. Se il set di risultati contiene più di una fattura per la stessa data, i valori di riepilogo di tutte le fatture vengono accumulati contemporaneamente. Questo è illustrato dalla quarta, quinta e sesta fattura di questo esempio. In questo caso, la colonna totale cumulativo per ogni fattura include i totali delle fatture per tutte e tre le fatture della stessa data. Poiché il totale cumulativo ora include sei righe, la colonna del conteggio è impostata a 6. In altre parole, i valori di riepilogo per tutte e tre le righe sono gli stessi. L'ultimo esempio di questa figura utilizza entrambe le clausole PARTITION BY e ORDER BY. In questo caso, le fatture sono raggruppate per TermsID e ordinate per data fattura. Per questo motivo, i riepiloghi per ogni data di fattura sono accumulati separatamente all'interno di ciascun TermsID. Quando un TermsID termina e inizia il successivo, l'accumulo ricomincia.

La sintassi della clausola OVER

    aggregate_function OVER ([partition_by_clause] [order_by_clause])

Una query che raggruppa i dati di riepilogo per data

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
        SUM(InvoiceTotal) OVER (PARTITION BY InvoiceDate) AS DateTotal,
        COUNT(InvoiceTotal) OVER (PARTITION BY InvoiceDate) AS DateCount,
        AVG(InvoiceTotal) OVER (PARTITION BY InvoiceDate) AS DateAvg
    FROM Invoices

Una query che calcola un totale cumulativo e una media mobile

    USE AP
    GO
    SELECT InvoiceNumber, InvoiceDate, InvoiceTotal,
        SUM(InvoiceTotal) OVER (ORDER BY InvoiceDate) AS CumTotal,
        COUNT(InvoiceTotal) OVER (ORDER BY InvoiceDate) AS Count,
        AVG(InvoiceTotal) OVER (ORDER BY InvoiceDate) AS MovingAvg
    FROM Invoices

La stessa query raggruppata per TermslD

    USE AP
    GO
    SELECT InvoiceNumber, TermsID, InvoiceDate, InvoiceTotal,
        SUM(InvoiceTotal)
            OVER (PARTITION BY TermsID ORDER BY InvoiceDate) AS CumTotal,
        COUNT(InvoiceTotal)
            OVER (PARTITION BY TermsID ORDER BY InvoiceDate) AS Count,
        AVG(InvoiceTotal)
            OVER (PARTITION BY TermsID ORDER BY InvoiceDate) AS MovingAvg
    FROM Invoices

Descrizione

Prospettiva

In questa lezione si è appreso come codificare le query che raggruppano e riassumono i dati. Nella maggior parte dei casi, si potranno usare le tecniche presentate qui per ottenere le informazioni di riepilogo necessarie. In caso contrario, si consiglia di informarsi su un altro strumento fornito da SQL Server 2016, chiamato Analysis Services. Questo strumento offre un'interfaccia grafica che consente di creare modelli di dati complessi basati su cubi. Quindi, è possibile utilizzare tali modelli per analizzare il database utilizzando modelli e correlazioni complesse. Per saperne di più su questo strumento, cercate “Analysis Services” nella documentazione di SQL Server.

Esercizi

Se non diversamente indicato, utilizzare la sintassi di unione esplicita

    USE AP
    GO
    SELECT VendorID, SUM(PaymentTotal) AS PaymentSum
    FROM Invoices
    GROUP BY VendorID
                        

    USE AP
    GO
    SELECT TOP 10 VendorName, SUM(PaymentTotal) AS PaymentSum
    FROM Vendors v 
      JOIN Invoices i
        ON v.VendorID = i.VendorID
    GROUP BY VendorName
    ORDER BY PaymentSum DESC
                        

    USE AP
    GO
    SELECT VendorName, COUNT(*) AS InvoiceCount,
            SUM(InvoiceTotal) AS InvoiceSum
    FROM Vendors v 
        JOIN Invoices i
            ON v.VendorID = i.VendorID
    GROUP BY VendorName
    ORDER BY InvoiceCount DESC
                        

    USE AP
    GO
    SELECT gl.AccountDescription, COUNT(*) AS LineItemCount,
            SUM(InvoiceLineItemAmount) AS LineItemSum
    FROM GLAccounts gl
        JOIN InvoiceLineItems li
            ON gl.AccountNo = li.AccountNo
    GROUP BY gl.AccountDescription
    HAVING COUNT(*) > 1
    ORDER BY LineItemCount DESC
                        

    USE AP
    GO
    SELECT gl.AccountDescription, COUNT(*) AS LineItemCount,
            SUM(InvoiceLineItemAmount) AS LineItemSum
    FROM GLAccounts gl
        JOIN InvoiceLineItems li
            ON gl.AccountNo = li.AccountNo
        JOIN Invoices i
            ON li.InvoiceID = i.InvoiceID
    WHERE InvoiceDate BETWEEN '2022-10-01' AND '2022-12-31'
    GROUP BY gl.AccountDescription
    HAVING COUNT(*) > 1
    ORDER BY LineItemCount DESC
                        

    USE AP
    GO
    SELECT AccountNo, SUM(InvoiceLineItemAmount) AS LineItemSum
    FROM InvoiceLineItems
    GROUP BY AccountNo WITH ROLLUP
                        

    USE AP
    GO
    SELECT VendorName, AccountDescription, COUNT(*) AS LineItemCount,
            SUM(InvoiceLineItemAmount) AS LineItemSum
    FROM Vendors v 
        JOIN Invoices i
            ON v.VendorID = i.VendorID
        JOIN InvoiceLineItems li
            ON i.InvoiceID = li.InvoiceID
        JOIN GLAccounts gl
            ON li.AccountNo = gl.AccountNo
    GROUP BY VendorName, AccountDescription
    ORDER BY VendorName, AccountDescription
                        

    USE AP
    GO
    SELECT VendorName,
            COUNT(DISTINCT li.AccountNo) AS [# of Accounts]
    FROM Vendors v 
        JOIN Invoices i
            ON v.VendorID = i.VendorID
        JOIN InvoiceLineItems li
            ON i.InvoiceID = li.InvoiceID
    GROUP BY VendorName
    HAVING COUNT(DISTINCT li.AccountNo) > 1
    ORDER BY VendorName
                        

    USE AP
    GO
    SELECT VendorID, InvoiceDate, InvoiceTotal,
        SUM(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorTotal,
        COUNT(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorCount,
        AVG(InvoiceTotal) OVER (PARTITION BY VendorID) AS VendorAvg
    FROM Invoices
Per visualizzare le soluzioni degli esercizi devi inserire la password fornita dal docente