Per riassumere le informazioni contenute nel database, SQL fornisce funzioni aggregate. Una funzione di aggregazione accetta un'intera colonna di dati come argomento e restituisce un singolo valore che riepiloga quella colonna in un modo specifico.
Ad esempio, la funzione di aggregazione AVG() prende come argomento una colonna di numeri e ne calcola la media.
Per calcolare il reddito medio pro capite di un residente di Zelenograd, è necessaria la seguente query:
SELEZIONARE 'REDDITO MEDIO =', AVG (SUMD)
SQL ha sei funzioni aggregate che forniscono diversi tipi di riepiloghi (Figura 1):
- SUM() calcola la somma di tutti i valori contenuti nella colonna;
- AVG() calcola la media tra i valori contenuti nella colonna;
- MIN() trova il più piccolo tra tutti i valori contenuti nella colonna;
- MAX() trova il più grande tra tutti i valori contenuti nella colonna;
- COUNT() conta il numero di valori contenuti nella colonna;
- COUNT (*) conta il numero di righe nella tabella dei risultati della query.
L'argomento di una funzione aggregata può essere un semplice nome di colonna, come nell'esempio precedente, o un'espressione, come nella query seguente che calcola l'imposta pro capite:
SELEZIONA MEDIA (SOMMA * 0,13)
Questa query crea una colonna temporanea contenente i valori (SUMD * 0,13) per ogni riga nella tabella PERSON, quindi calcola la media della colonna temporanea.
La somma del reddito di tutti i residenti di Zelenograd può essere calcolata utilizzando la funzione di aggregazione SOMMA:
SELEZIONA SOMMA (SOMMA) DA PERSONA
La funzione di aggregazione può essere utilizzata anche per calcolare i totali per una tabella di risultati ottenuti unendo più tabelle di origine. Ad esempio, puoi calcolare l'importo totale del reddito che i residenti ricevono da una fonte chiamata "Borsa di studio":
SELEZIONA SOMMA (DENARO)
DAL PROFITTO, HAVE_D
DOVE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = 'Borsa di studio'
Le funzioni aggregate MIN() e MAX() consentono di trovare, rispettivamente, i valori più piccoli e più grandi nella tabella. Tuttavia, la colonna può contenere valori numerici o stringa oppure valori di data o ora.
Ad esempio, puoi definire:
(a) il reddito totale più basso guadagnato dai residenti e l'imposta più alta da pagare:
SELEZIONA MIN (SUMD), MAX (SUMD * 0,13)
(b) date di nascita del residente più anziano e più giovane:
SELEZIONA MIN (RDATE), MAX (RDATE)
(c) cognomi, nomi e patronimici dei primissimi e degli ultimissimi abitanti dell'elenco, in ordine alfabetico:
SELEZIONA MIN (FIO), MAX (FIO)
Quando si applicano queste funzioni aggregate, è necessario ricordare che i dati numerici vengono confrontati secondo regole aritmetiche, le date vengono confrontate in sequenza (i valori delle date precedenti sono considerati inferiori a quelli successivi), gli intervalli di tempo vengono confrontati in base alla loro durata.
Quando si utilizzano MIN() e MAX() con dati stringa, il risultato del confronto tra le due stringhe dipende dalla tabella di codifica dei caratteri utilizzata.
La funzione aggregata COUNT() conta il numero di valori in una colonna di qualsiasi tipo:
(a) quanti appartamenti ci sono nel 1° microdistretto?
SELEZIONA CONTEGGIO (ADR) DA APPARTAMENTO DOVE ADR LIKE "%, 1_ _-%"
(b) quanti residenti hanno fonti di reddito?
SELEZIONA COUNT (DISTINTO NOM) DA HAVE_D
(c) quante fonti di reddito sono utilizzate dai residenti?
SELECT COUNT (DISTINCT ID) FROM HAVE_D (La parola chiave DISTINCT specifica che vengono conteggiati i valori non duplicati nella colonna).
La funzione di aggregazione speciale COUNT (*) conta le righe nella tabella dei risultati, non i valori dei dati:
(a) quanti appartamenti ci sono nel 2° microdistretto?
SELEZIONA CONTEGGIO (*) DA APPARTAMENTO DOVE ADR COME "%, 2 __-%"
(b) quante fonti di reddito ha Ivan Ivanovich?
SELEZIONA COUNT (*) FROM PERSON, HAVE_D WHERE FIO = "Ivanov Ivan Ivanovich" AND PERSON.NOM = HAVE_D.NOM
(c) quanti residenti vivono in un appartamento a un determinato indirizzo?
SELEZIONA CONTEGGIO (*) DA PERSONA DOVE ADR = "Zelenograd, 1001-45"
Un modo per capire come vengono eseguite le query aggregate è pensare all'esecuzione della query in due parti. Innanzitutto, viene determinato come funzionerebbe la query senza funzioni aggregate, restituendo più righe di risultati. Le funzioni aggregate vengono quindi applicate ai risultati della query, restituendo una singola riga di riepilogo.
Ad esempio, si consideri la seguente query complessa: trovare il reddito totale medio pro capite, la somma del reddito totale dei residenti e il reddito medio della fonte come percentuale del reddito totale dell'abitante. La risposta è data dall'operatore
SELECT AVG (SUMD), SUM (SUMD), (100 * AVG (MONEY / SUMD)) FROM PERSON, PROFIT, HAVE_D DOVE PERSON.NOM = HAVE_D.NOM AND HAVE_D.ID = PROFIT.ID
Senza funzioni aggregate, la query sarebbe simile a questa:
SELEZIONA SUMD, SUMD, MONEY / SUMD FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM = HAVE_D.NOM E HAVE_D.ID = PROFIT.ID
e restituirebbe una riga di risultati per ogni residente e specifica fonte di reddito. Le funzioni di aggregazione utilizzano le colonne nella tabella dei risultati di questa query per produrre una tabella a riga singola con i risultati di riepilogo.
Nella riga delle colonne restituite, puoi specificare una funzione di aggregazione invece di qualsiasi nome di colonna. Ad esempio, può essere incluso in un'espressione che aggiunge o sottrae i valori di due funzioni aggregate:
SELEZIONA MAX (SUMD) -MIN (SUMD) DA PERSON
Tuttavia, una funzione aggregata non può essere un argomento per un'altra funzione aggregata, ad es. le funzioni aggregate nidificate sono vietate.
Inoltre, le funzioni aggregate e i nomi di colonne regolari non possono essere utilizzati contemporaneamente nell'elenco delle colonne restituite, poiché non ha senso in questo, ad esempio:
SELEZIONA FIO, SOMMA (SUMD) DA PERSON
Qui, il primo elemento dell'elenco indica al DBMS di creare una tabella che sarà composta da più righe e conterrà una riga per ogni residente. Il secondo elemento nell'elenco chiede al DBMS di ottenere un valore di risultato, che è la somma dei valori nella colonna SUMD. Queste due linee guida si contraddicono, risultando in un errore.
Quanto sopra non si applica ai casi di elaborazione di sottoquery e query con raggruppamento.
Utilizzo di funzioni aggregate
SQL definisce molte funzioni integrate di varie categorie, tra le quali un posto speciale è occupato da funzioni aggregate che operano sui valori delle colonne in un insieme di righe e restituiscono un singolo valore. Gli argomenti delle funzioni aggregate possono essere sia colonne di tabella che i risultati di espressioni su di esse. Le funzioni aggregate possono esse stesse essere incluse in altre espressioni aritmetiche. La tabella seguente elenca le funzioni aggregate unari standard più comunemente utilizzate.
Il formato generale di una funzione aggregata unaria è il seguente:
nome_funzione (espressione [ALL | DISTINCT])
dove DISTINCT indica che la funzione deve considerare solo valori di argomento diversi e ALL - tutti i valori, inclusi i valori duplicati (questa è l'impostazione predefinita). Ad esempio, la funzione AVG con la parola chiave DISTINCT per le righe di colonna con i valori 1, 1, 1 e 3 restituirà 2 e, se è presente la parola chiave ALL, restituirà 1.5.
Le funzioni aggregate vengono utilizzate nelle clausole SELECT e HAVING. Qui esamineremo il loro uso nella clausola SELECT. In questo caso, l'espressione nell'argomento della funzione si applica a tutte le righe nella tabella di input della clausola SELECT. Inoltre, le funzioni aggregate e le colonne della tabella (o le espressioni con esse) non possono essere utilizzate nella clausola SELECT a meno che non sia assente la clausola GROUP BY, di cui parleremo nella prossima sezione.
La funzione COUNT ha due formati. Nel primo caso viene restituito il numero di righe nella tabella di input, nel secondo caso il numero di valori degli argomenti nella tabella di input:
- CONTARE (*)
- CONTE (espressione)
Il modo più semplice per utilizzare questa funzione è contare il numero di righe in una tabella (tutte o che soddisfano una condizione specificata). Per questo, viene utilizzata la prima sintassi.
Query: il numero di tipi di prodotti, le cui informazioni sono disponibili nel database.
SELEZIONA CONTEGGIO (*) AS "Numero di tipi di prodotti"
DA Prodotto
Nella seconda variante della sintassi per la funzione COUNT, il nome di una singola colonna può essere utilizzato come argomento. In questo caso, viene conteggiato il numero di tutti i valori in questa colonna della tabella di input o solo i valori non ripetuti (quando si utilizza la parola chiave DISTINCT).
Query: il numero di nomi distinti contenuti nella tabella Customer.
SELEZIONA CONTEGGIO (FNAME DISTINTO)
DA Cliente
L'uso del resto delle funzioni aggregate unarie è lo stesso di COUNT, tranne che per le funzioni MIN e MAX, l'uso delle parole chiave DISTINCT e ALL è privo di significato. Oltre ai campi numerici, i campi carattere possono essere utilizzati con le funzioni COUNT, MAX e MIN. Se l'argomento della funzione di aggregazione non contiene valori, la funzione COUNT restituisce 0 e tutte le altre restituiscono NULL.
SELEZIONA MAX (OrdDate)
A PARTIRE DAL
DOVE OrdDate "1.09.2010"
Assegnazione di autoapprendimento: formulare query in SQL per selezionare i seguenti dati:
- Il costo totale di tutti gli ordini;
- Il numero di città distinte contenute nella tabella Cliente.
- Funzioni aggregate sono usati come i nomi dei campi nell'istruzione SELECT, con un'eccezione: prendono il nome del campo come argomento. Con funzioni SOMMA e AVG possono essere utilizzati solo campi numerici. Con funzioni CONTEGGIO, MAX e MIN possono essere utilizzati sia campi numerici che caratteri. Se utilizzato con i campi di caratteri MAX e MIN li tradurrà nell'equivalente ASCII e li elaborerà in ordine alfabetico. Alcuni DBMS consentono l'utilizzo di aggregati nidificati, ma questa è una deviazione dallo standard ANSI con tutte le conseguenze che ne conseguono.
Ad esempio, puoi calcolare il numero di studenti che hanno superato gli esami in ciascuna disciplina. Per fare ciò, è necessario eseguire una query raggruppata per il campo "Disciplina" e visualizzare come risultato il nome della disciplina e il numero di righe nel gruppo per questa disciplina. L'utilizzo del carattere * come argomento della funzione COUNT significa che vengono contate tutte le righe del gruppo.
SELEZIONA R1. Disciplina, CONTE (*)
GRUPPO PER R1 Disciplina;
Risultato:
SELEZIONA R1 Disciplina, CONTA (*)
DOVE R1. NON È NULL punteggio
GRUPPO PER R1 Disciplina;
Risultato:
non saranno incluse nell'insieme di tuple prima del raggruppamento, quindi il numero di tuple nel gruppo per la disciplina "Teoria dell'informazione" sarà 1 in meno.
Un risultato simile si può ottenere scrivendo la richiesta nel seguente modo:
SELEZIONA R1. Disciplina, COUNT (R1. Valutazione)
GRUPPO PER R1. Disciplina;
Funzione CONTEGGIO (NOME ATTRIBUTO) conta il numero di valori definiti in un gruppo, al contrario di una funzione CONTARE (*), che conta il numero di righe nel gruppo. Infatti, nel gruppo con la disciplina "Teoria dell'informazione" ci saranno 4 righe, ma solo 3 valori specifici dell'attributo "Valutazione".
Regole per la gestione dei valori null nelle funzioni aggregate
Se alcuni valori nella colonna sono uguali NULLO sono esclusi nel calcolo del risultato della funzione.
Se tutti i valori in una colonna sono uguali NULLO, poi Max Min Somma Avg = NULLO, conteggio = 0 (zero).
Se la tabella è vuota, conteggio (*) = 0 .
Le funzioni aggregate possono essere utilizzate anche senza l'operazione di raggruppamento preliminare, in questo caso l'intera relazione viene considerata come un gruppo e per questo gruppo è possibile calcolare un valore per gruppo.
Regole per interpretare le funzioni aggregate
Le funzioni aggregate possono essere incluse nell'elenco di output e quindi applicate all'intera tabella.
SELEZIONA MAX (Punteggio) da R1 darà il voto massimo alla sessione;
SELEZIONA SOMMA da R1 fornirà la somma di tutte le valutazioni per sessione;
SELEZIONA MEDIA (Punteggio) da R1 darà un punteggio medio per l'intera sessione.
2; Risultato: "larghezza =" 640 " Facendo nuovamente riferimento al database della Sessione (tabelle R1), troveremo il numero di esami superati con successo:
SELEZIONA CONTEGGIO (*) As affittato _ esami
DOVE Grado 2;
Risultato:
L'argomento delle funzioni aggregate può essere colonne separate di tabelle. Per calcolare, ad esempio, il numero di valori distinti di una determinata colonna in un gruppo, è necessario utilizzare la parola chiave DISTINCT insieme al nome della colonna. Calcoliamo il numero di voti diversi ricevuti per ogni disciplina:
SELECT R1 Disciplina, COUNT (DISTINCT R1 Evaluation)
DOVE R1. NON È NULL punteggio
GRUPPO PER R1 Disciplina;
Risultato:
Lo stesso risultato si ottiene escludendo la condizione esplicita nella parte WHERE, nel qual caso la query sarà simile a questa:
SELEZIONA R1. Disciplina, CONTE (DISTINTO R1. Valutazione)
GRUPPO PER R1. Disciplina;
Funzione CONTEGGIO (DISTINTO R1.Valutazione) considera solo certo vari i valori.
Per ottenere il risultato desiderato in questo caso, è necessario effettuare una trasformazione preliminare del tipo di dati della colonna "Punteggio", portandolo in un tipo reale, quindi il risultato del calcolo della media non sarà un numero intero. In questo caso, la richiesta sarà simile a questa:
2 Raggruppa per R2. Gruppo, R1. Disciplina; Qui, la funzione CAST() converte la colonna "Punteggio" in un tipo di dati valido. "larghezza =" 640 " Seleziona R2.Group, R1.Discipline, Count (*) come Total, AVG (cast (punteggio come decimal (3,1))) come Average_point
Da R1, R2
dove R1. Nome completo = R2. Nome e R1. il punteggio non è nullo
e R1. Grado 2
Raggruppa per R2. Gruppo, R1. Disciplina;
Ecco la funzione LANCIO () converte la colonna Punteggio in un tipo di dati valido.
Non è possibile utilizzare funzioni aggregate nella clausola WHERE perché le condizioni in questa sezione vengono valutate in termini di una singola riga e le funzioni aggregate vengono valutate in termini di gruppi di righe.
La clausola GROUP BY consente di definire un sottoinsieme dei valori in un determinato campo in termini di un altro campo e applicare una funzione di aggregazione al sottoinsieme. Ciò rende possibile combinare campi e funzioni aggregate in un'unica clausola SELECT. Le funzioni aggregate possono essere utilizzate sia nell'espressione per l'output dei risultati della riga SELECT, sia nell'espressione per l'elaborazione dei gruppi HAVING generati. In questo caso, ogni funzione aggregata viene calcolata per ogni gruppo selezionato. I valori ottenuti durante il calcolo delle funzioni aggregate possono essere utilizzati per visualizzare i risultati corrispondenti o per la condizione di selezione dei gruppi.
Costruiamo una query che visualizzi i gruppi in cui è stato ottenuto più di un deuce in una disciplina negli esami:
uno; Risultato: "larghezza =" 640 " SELEZIONA R2. Gruppo
DA R1, R2
DOVE R1. Nome completo = R2. Nome AND
Punteggio R1 = 2
RAGGRUPPARE PER R2.Gruppo, R1.Disciplina
AVERE conteggio (*) 1;
Risultato:
Abbiamo un DB "Banca", costituito da una tabella F, che memorizza la relazione F, contenente informazioni sui conti nelle filiali di una determinata banca:
Trova il saldo totale del conto nelle filiali. È possibile fare una query separata per ognuno di essi selezionando SUM dalla tabella per ogni ramo, ma l'operazione GROUP BY li metterà tutti in un comando:
SELEZIONARE Ramo , SOMMA ( Resto )
GRUPPO PER Filiale;
RAGGRUPPA PER applica le funzioni aggregate in modo indipendente per ogni gruppo identificato dal valore del campo Ramo. Il gruppo è composto da righe con lo stesso valore di campo Ramo e la funzione SOMMA si applica separatamente per ciascuno di questi gruppi, ovvero il saldo totale del conto viene calcolato separatamente per ciascuna filiale. Il valore del campo a cui si applica RAGGRUPPA PER, ha, per definizione, un solo valore per gruppo di output, così come il risultato di una funzione aggregata.
5.000; Gli argomenti nella clausola HAVING seguono le stesse regole della clausola SELECT, che utilizza GROUP BY. Devono avere un valore per gruppo di output. "larghezza =" 640 " Supponiamo di selezionare solo quelle filiali, i cui valori totali dei saldi dei conti superano $ 5.000, nonché i saldi totali per le filiali selezionate. Per visualizzare le filiali con saldi totali superiori a $ 5.000, è necessario utilizzare la clausola HAVING. La clausola HAVING definisce i criteri utilizzati per rimuovere gruppi specifici dall'output, proprio come fa la clausola WHERE per le singole righe.
Il comando corretto sarebbe il seguente:
SELEZIONA Ramo, SOMMA (Saldo)
RAGGRUPPA PER Ramo
AVENDO SOMMA ( Resto ) 5 000;
Argomenti in una frase AVENDO obbedire alle stesse regole della frase SELEZIONARE dove si usa? RAGGRUPPA PER... Devono avere un valore per gruppo di output.
Il seguente comando sarà proibito:
SELEZIONA Ramo, SOMMA (Saldo)
GRUPPO PER Filiale
AVERE Data di apertura = 27/12/2004 ;
Campo Data di apertura non può essere usato in una frase AVENDO perché può avere più di un valore per gruppo di visualizzazione. Per evitare una situazione del genere, la proposta AVENDO dovrebbe fare riferimento solo agli aggregati e ai campi selezionati RAGGRUPPA PER... C'è un modo corretto per fare la query di cui sopra:
SELEZIONA Ramo, SOMMA (Saldo)
DOVE Data di apertura = '27 / 12/2004 '
GRUPPO PER Filiale;
Il significato di questa query è il seguente: trovare l'importo dei saldi per ogni ramo di conti aperto il 27 dicembre 2004.
Come affermato in precedenza, HAVING può utilizzare solo argomenti che hanno lo stesso valore per gruppo di output. In pratica i riferimenti alle funzioni aggregate sono i più generici, ma sono validi anche i campi selezionati con GROUP BY. Ad esempio, vogliamo vedere i saldi totali dei conti delle filiali a San Pietroburgo, Pskov e Uryupinsk:
SELEZIONA Ramo, SOMMA (Saldo)
DA F, Q
DOVE F. Ramo = Q. Ramo
GRUPPO PER Filiale
HAVING Branch IN ("San Pietroburgo", "Pskov", "Uryupinsk");
100.000; Se il saldo totale è superiore a $ 100.000, lo vedremo nel rapporto risultante, altrimenti otterremo un rapporto vuoto. "larghezza =" 640 "
Pertanto, nelle espressioni aritmetiche dei predicati inclusi nella condizione di selezione della clausola HAVING, è possibile utilizzare direttamente solo le specifiche delle colonne specificate come colonne di raggruppamento nella clausola GROUP BY. Il resto delle colonne può essere specificato solo all'interno delle specifiche delle funzioni aggregate COUNT, SUM, AVG, MIN e MAX, che in questo caso calcolano un valore aggregato per l'intero gruppo di righe. Il risultato dell'esecuzione della clausola HAVING è una tabella raggruppata contenente solo quei gruppi di righe per i quali il risultato della valutazione della condizione di selezione nella parte HAVING è TRUE. In particolare, se la clausola HAVING è presente in una query che non contiene un GROUP BY, allora il risultato della sua esecuzione sarà una tabella vuota, oppure il risultato delle sezioni precedenti dell'espressione di tabella, considerata come un gruppo senza colonne di raggruppamento. Diamo un'occhiata a un esempio. Supponiamo di voler visualizzare l'importo totale dei saldi per tutti i rami, ma solo se è superiore a $ 100.000. In questo caso, la nostra query non conterrà operazioni di raggruppamento, ma conterrà una sezione HAVING e avrà il seguente aspetto:
SELEZIONA SOMMA ( Resto )
AVENDO SOMMA ( Resto ) 100 000;
Se il saldo totale è superiore a $ 100.000, lo vedremo nel rapporto risultante, altrimenti otterremo un rapporto vuoto.
Può eseguire l'elaborazione batch generalizzata dei valori dei campi. Questo viene fatto utilizzando funzioni aggregate. Le funzioni aggregate producono un singolo valore per l'intero gruppo della tabella. V SQL sono previste le seguenti funzioni aggregate:
- CONTARE- conta il numero di righe della tabella con valori non NULL del campo specificato come argomento.
- SOMMA- calcola la somma aritmetica di tutti i valori selezionati del dato campo.
- AVG- esegue la media di tutti i valori selezionati di questo campo.
- MAX- visualizza il valore più grande di tutti i valori selezionati per questo campo.
- MIN- Emette il valore più piccolo di tutti i valori selezionati per questo campo.
Utilizzo di funzioni aggregate
Le funzioni aggregate vengono utilizzate come i nomi dei campi in una frase SELEZIONARE query, ma con un'eccezione: prendono i nomi dei campi come argomenti. È possibile utilizzare solo campi numerici con SOMMA e AVG... CON CONTARE, MAX, e MIN possono essere utilizzati sia campi numerici che caratteri. Se utilizzato con i campi di caratteri MAX e MIN li tradurrà nell'equivalente ASCII. Significa che MIN sceglierà il primo e MAX l'ultimo valore in ordine alfabetico.
Per trovare le vendite totali nella tabella delle vendite, dobbiamo scrivere la seguente query:
SELEZIONA SOMMA (Sum) DA Vendite
Di conseguenza, otteniamo:
Questa query ha contato il numero di valori non vuoti nel campo SNum della tabella Sells. Se riscriviamo la richiesta come segue:
SELEZIONA COUNT (SDate) FROM Sells
Quindi come risultato otteniamo:
COUNT OF SDate 4 Risultati di query diversi durante il calcolo apparentemente uguali si ottengono perché uno dei valori del campo SDate ha un valore vuoto ( NULLO). Fare attenzione quando si utilizzano tali richieste.
