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 funzioni	Risultato
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

• Le funzioni aggregate, dette anche funzioni di colonna, eseguono un calcolo sui valori di un insieme di righe selezionate. I valori da utilizzare nel calcolo vengono specificati codificando un'espressione per l'argomento della funzione. In molti casi, l'espressione è solo il nome di una colonna.
• Un'istruzione SELECT che include una funzione aggregata può essere chiamata query di riepilogo.
• L'espressione specificata per le funzioni AVG e SUM deve avere come risultato un valore numerico. L'espressione per le funzioni MIN, MAX e COUNT può avere come risultato un valore numerico, una data o una stringa.
• Per impostazione predefinita, tutti i valori vengono inclusi nel calcolo, indipendentemente dal fatto che siano duplicati. Se si desidera omettere i valori duplicati, codificare la parola chiave DISTINCT. Questa parola chiave viene usata di solito solo con la funzione COUNT.
• Tutte le funzioni aggregate, tranne COUNT(*), ignorano i valori nulli.
• Le funzioni aggregate vengono spesso utilizzate con la clausola GROUP BY dell'istruzione SELECT, che serve a raggruppare le righe in un insieme di risultati. Per ulteriori informazioni, vedere l'esempio successivo.
• Se si codifica una funzione aggregata nella clausola SELECT, tale clausola non può includere colonne non aggregate dalla tabella di base, a meno che la colonna non sia specificata in una clausola GROUP BY o la clausola OVER sia inclusa per ogni funzione aggregata. Vedere gli esempi successivi per maggiori informazioni sulla clausola OVER.

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 2015-09-01 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 9/1/2015' AS SelectionDate, COUNT ( * ) AS NumberOfinvoices,
        AVG (InvoiceTotal ) AS AverageinvoiceAmount,
        SUM (InvoiceTotal ) AS TotalinvoiceAmount
    FROM Invoices
    WHERE InvoiceDate > '2015-09-01'
                

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 > '2012-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

• Se si desidera contare solo le righe con valori univoci in una colonna specifica, è possibile codificare la funzione COUNT con la parola chiave DISTINCT seguita dal nome della colonna, come illustrato nel quarto esempio.

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
                

---