Nella lezione di oggi, imparerai a raggruppare i risultati restituiti dalle tue query utilizzando la clausola SQL GROUP BY.
Gli obiettivi della lezione di oggi sono:
- Imparare a raggruppare i risultati usando GROUP BY
- Utilizzare le funzioni aggregate per eseguire calcoli
- Capire come filtrare i gruppi usando la clausola HAVING
Importante! Si prega di seguire e fare gli esempi nel database. Se non l’hai già fatto, iscriviti alla mia Guida per iniziare con SQL Server., Ottieni istruzioni su come installare gli strumenti gratuiti e il database di esempio.
SQL GROUP BY Clause
La clausola SQL GROUP BY viene utilizzata per generare una riga tra i valori di colonna specificati. In genere viene utilizzato in combinazione con funzioni aggregate come SOMMA o Conteggio per riassumere i valori. In SQL gruppi sono combinazioni uniche di campi. Invece di restituire ogni riga in una tabella, quando i valori vengono raggruppati, vengono restituite solo le combinazioni univoche.
La clausola GROUP BY viene aggiunta all’istruzione SQL dopo la clausola WHERE., Ecco un esempio in cui stiamo elencando SalesOrderID, escluse le quantità superiori a 100.
Ci sono un paio di cose da notare. Innanzitutto, le colonne che vogliamo riassumere sono elencate, separate da virgole, nella clausola GROUP BY. In secondo luogo, questo stesso elenco di colonne deve essere elencato nell’istruzione select; altrimenti l’istruzione non riesce.,
Quando viene eseguita questa istruzione, non vengono restituite tutte le righe filtrate. Solo le combinazioni uniche di SalesOrderID sono incluse nel risultato. Questa istruzione è molto simile a
Ma c’è una differenza fondamentale., Il modificatore DISTINTO si ferma all’output di una combinazione univoca di righe, mentre, con l’istruzione GROUP BY, possiamo calcolare i valori in base alle righe filtrate sottostanti per ogni combinazione univoca.
In altre parole, usando il nostro esempio, con GROUP BY, possiamo calcolare il numero o i dettagli dell’ordine per ordine come segue:
COUNT è un esempio di una funzione aggregata, questi sono ciò che realmente danno al GRUPPO PER istruzione il suo valore speciale.
Funzioni SQL Aggregate
Alcune funzioni, come SUM, vengono utilizzate per eseguire calcoli su un gruppo di righe, chiamate funzioni aggregate. Nella maggior parte dei casi queste funzioni operano su un gruppo di valori definiti utilizzando la clausola GROUP BY., Quando non esiste una clausola GROUP BY, generalmente si intende che la funzione aggregate si applica a tutti i risultati filtrati.
Alcune delle funzioni aggregate più comuni includono:
| AVG(expression) | Calcola la media dell’espressione. |
| COUNT(expression) | Conta le occorrenze di valori non nulli restituiti dall’espressione. |
| COUNT (*) | Conta tutte le righe nella tabella specificata., |
| MIN(expression) | Trova il valore minimo di espressione. |
| MAX(expression) | Trova il valore massimo di espressione. |
| SUM(expression) | Calcola la somma dell’espressione. |
Queste funzioni possono essere utilizzate da sole in combinazione con la clausola GROUP BY. Da soli, operano su tutta la tabella; tuttavia, quando vengono utilizzati con GROUP BY, i loro calcoli vengono “reimpostati” ogni volta che il raggruppamento cambia., In questo modo agiscono come subtotali.
Funzione di aggregazione semplice
Quando si utilizza la funzione di aggregazione è possibile calcolare il risultato su tutti i valori o valori distinti. Ad esempio, per contare tutti i record di SalesOrderDetails potremmo usare l’espressione:
SELECT COUNT(SalesOrderID)
FROM Sales.SalesOrderDetail
Questo si traduce in un conteggio di 121317 restituito.
Per contare il distinto degli ordini che compongono i dettagli useremmo quanto segue:
SELECT COUNT(Distinct SalesOrderID)
FROM Sales.SalesOrderDetail
Il conteggio ora è 31465.,
SQL GROUP BY and Aggregate Functions
Per aggregare significa creare interi da singole parti. Le funzioni aggregate sono funzioni che funzionano su più di una riga per restituire un risultato.
AVG e SUM
La funzione SUM totalizza i valori restituiti, in modo simile AVG calcola la media.
Vediamo se possiamo calcolare l’importo totale dell’ordine da OrderDetails., Nelle lezioni precedenti abbiamo sapere come si calcola l’importo totale per ogni dettaglio, come:
SELECT SalesOrderID,
ProductID,
OrderQty* UnitPrice As ExtendedPrice
FROM Sales.SalesOrderDetail
Dal momento che siamo in grado di applicare la funzione di aggregazione, di espressioni, siamo in grado di impostare un raggruppamento in Idordine per calcolare il prezzo totale per ordine
SELECT SalesOrderID, SUM(OrderQty * UnitPrice) AS TotalPriceFROM Sales.SalesOrderDetailGROUP BY SalesOrderID
Si può anche ordinare il totale per ottenere il top ordini prima
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY TotalPrice DESC
allo stesso modo, siamo in grado di calcolare la media di dettaglio ordine utilizzando AVG. Perché lo provi qui sotto! Se ti trovi in difficoltà alternare la risposta.
Per il curioso, dato che in media è calcolata come la somma del campione diviso per il conteggio del campione, poi, utilizzando AVG nella dichiarazione di cui sopra è la stessa:
SELECT SalesOrderID,
SUM(OrderQty * UnitPrice) / COUNT(SalesOrderID) AS AvgOrderAmount FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Abbiamo coperto un sacco in questa sezione., Ecco alcuni punti chiave da ricordare:
- Una funzione aggregata può valutare un’espressione come SUM(A + B)
- Dovresti alias le funzioni aggregate, quindi i nomi delle colonne sono significativi
- Quando si lavora con funzioni aggregate e GROUP BY, a volte è più facile pensare prima ai dettagli, cioè scrivere una semplice istruzione SELECT, ispezionare i risultati, quindi aggiungere le cose fantasiose. Copro questo in Come scrivere query migliori.
SQL COUNT
La funzione COUNT viene utilizzata quando è necessario sapere quanti record esistono in una tabella o all’interno di un gruppo., COUNT(*) conterà ogni record nel raggruppamento; mentre COUNT(expression) conta ogni record in cui il risultato dell’espressione non è nullo. È inoltre possibile utilizzare Distinct with COUNT per trovare il numero di valori univoci all’interno di un gruppo.
Per trovare il numero di SalesOrderDetail Righe per ordine
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
Per trovare il numero di ordini di vendita per prodotto
SELECT ProductID,
COUNT(DISTINCT SalesOrderID)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
MIN e MAX
il MIN e MAX, per trovare il più piccolo e il più grande dei valori, rispettivamente, all’interno di una tabella o di un gruppo.,
Ad esempio, per trovare le quantità di prodotto più piccole e più grandi ordinate all’interno di un ordine, provare
SELECT SalesOrderID,
Min(OrderQty) AS MinQuantity,
Max(OrderQty) AS MaxQuantity
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
È anche possibile trovare il valore MINIMO o MASSIMO di un calcolo. Qui troviamo la quantità di prodotto più alta ordinata all’interno di un prodotto:
SELECT SalesOrderID,
MAX(UnitPrice * OrderQty) as MaxAmount
FROM Sales.SalesOrderDetail GROUP BY SalesOrderID
Clausola HAVING
La clausola HAVING SQL viene utilizzata per filtrare i gruppi in base ai risultati delle funzioni aggregate. Ciò consente di risolvere problemi come selezionare tutti gli ordini che hanno più di due righe di dettaglio dell’ordine.,
questo esempio sembra
SELECT SalesOrderID,
COUNT(SalesOrderDetailID)
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING Count(SalesOrderDetailID) > 2
Se volessimo trovare tutti gli ordini superiori a $1000 possiamo scrivere
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING SUM(UnitPrice * OrderQty) > 1000
ORDER BY TotalPrice DESC
si noti che è possibile utilizzare l’alias TotalPrice nella clausola ORDER BY, ma poi avendo una clausola di utilizzare l’espressione.
Per martellare casa AVENDO, voglio mostrare un ultimo esempio. Qui vedrai che l’istruzione HAVING include una funzione aggregata che non è nell’elenco SELECT.,
SELECT SalesOrderID,
SUM(UnitPrice * OrderQty) AS TotalPrice
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
HAVING AVG(UnitPrice * OrderQty) > 500
ORDER BY TotalPrice DESC
Nella query di cui sopra stiamo ottenendo il prezzo totale per gli ordini in cui l’importo medio di SalesOrderDetail è maggiore di $500.00.
Dichiarazione finale sull’AVERE
Sebbene svolgano una funzione simile, esiste una distinzione chiave tra la clausola WHERE e HAVING. La clausola WHERE per filtrare i singoli record; mentre, la clausola HAVING filtra sui gruppi.
Per tenerlo dritto nella mia testa mi piace pensare alla clausola WHERE che fa il suo lavoro prima che si verifichino i raggruppamenti, e quindi alla clausola HAVING che prende il sopravvento dopo che i gruppi si sono formati.,
Esercizi
È importante esercitarsi! Utilizzare il database di esempio per rispondere a queste domande.
- HR vuole un rapporto del numero di dipendenti attivi per titolo di lavoro. Quale SQL useresti?
- Visualizza la quantità minima, massima e media ordinata per ciascun prodotto in SalesOrderDetails.
- Elenca tutti i titoli di lavoro dei dipendenti e il numero di dipendenti in cui il numero medio di ore di congedo per malattia è inferiore o uguale a quaranta.
- Per un titolo di lavoro restituito in #3 sopra, il conteggio è lo stesso per il risultato della risposta del titolo di lavoro corrispondente #1?
Le risposte sono qui!,
Leave a Reply