Come posso scoprire il numero di modelli di PC prodotti da un particolare fornitore? Come determinare il prezzo medio dei computer che hanno lo stesso specifiche? Queste e molte altre domande relative ad alcuni informazioni statistiche, puoi ottenere risposte usando funzioni di riepilogo (aggregate).. La norma prevede le seguenti funzioni aggregate:

Tutte queste funzioni restituiscono un singolo valore. Allo stesso tempo, le funzioni CONTEGGIO, MIN e MASSIMO si applicano a qualsiasi tipo di dati, mentre SOMMA e AVG utilizzato solo per i campi numerici. Differenza tra funzione CONTANO(*) e CONTANO(<имя поля>) è che il secondo non tiene conto dei valori NULL durante il calcolo.

Esempio. Trova il prezzo minimo e massimo per i personal computer:

Esempio. Trova il numero disponibile di computer prodotti dal produttore A:

Esempio. Se siamo interessati alla quantità vari modelli prodotto dal produttore A, allora la query può essere formulata come segue (sfruttando il fatto che ogni modello è registrato una volta nella tabella Prodotto):

Esempio. Trovare il numero di diversi modelli disponibili prodotti dal produttore A. La query è simile alla precedente, in cui era necessario determinare numero totale modelli prodotti dal produttore A. Qui è inoltre necessario trovare il numero di diversi modelli nella tabella PC (ovvero disponibili in commercio).

Al fine di garantire che vengano utilizzati solo valori univoci quando si ottengono indicatori statistici, quando argomento della funzione aggregata può essere utilizzata parametro DISTINTO. Altro parametro TUTTIè l'impostazione predefinita e prevede di contare tutti i valori restituiti nella colonna. Operatore,

Se abbiamo bisogno di ottenere il numero di modelli di PC prodotti da ogni produttore, sarà necessario utilizzare Offerta GRUPPO PER, segue sintatticamente dopo clausole DOVE.

Offerta GRUPPO DI

Offerta GRUPPO PER viene utilizzato per definire gruppi di righe di output a cui possono essere applicati funzioni aggregate (COUNT, MIN, MAX, AVG e SUM). Se questa clausola è mancante e vengono utilizzate funzioni aggregate, tutte le colonne con i nomi citati in SELEZIONARE, dovrebbe essere incluso in funzioni aggregate e queste funzioni si applicheranno all'intero set di righe che soddisfano il predicato della query. In caso contrario, tutte le colonne SELEZIONA elenco, non incluso in funzioni aggregate, deve essere specificato nella clausola GROUP BY. Di conseguenza, tutte le righe di output della query vengono divise in gruppi caratterizzati dalle stesse combinazioni di valori in queste colonne. Successivamente, le funzioni aggregate verranno applicate a ciascun gruppo. Si noti che per GROUP BY, tutti i valori NULL vengono trattati come uguali, ad es. quando si raggruppa per un campo contenente valori NULL, tutte queste righe rientreranno in un gruppo.
Se se esiste una clausola GROUP BY, nella clausola SELECT nessuna funzione aggregata, la query restituirà semplicemente una riga da ciascun gruppo. Questa funzione, insieme alla parola chiave DISTINCT, può essere utilizzata per eliminare le righe duplicate in un set di risultati.
Considera un semplice esempio:

SELECT modello, COUNT(modello) AS Qty_model, AVG(prezzo) AS Avg_price DA PC GROUP BY modello;

In questa query, per ogni modello di PC, viene determinato il loro numero e costo medio. Tutte le righe con lo stesso valore del modello (numero del modello) formano un gruppo e l'output SELECT calcola il numero di valori e i valori del prezzo medio per ciascun gruppo. Il risultato della query sarà la seguente tabella:

modello	Qtà_modello	Prezzo_media
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Se ci fosse una colonna con una data in SELECT, allora sarebbe possibile calcolare questi indicatori per ogni data specifica. Per fare ciò, è necessario aggiungere la data come colonna di raggruppamento, quindi le funzioni aggregate verrebbero calcolate per ogni combinazione di valori (data-modello).

Ci sono diversi specifici regole per l'esecuzione di funzioni aggregate:

• Se a seguito della richiesta nessuna riga ricevuta(o più di una riga per questo gruppo), non ci sono dati iniziali per il calcolo di nessuna delle funzioni aggregate. In questo caso, il risultato dell'esecuzione delle funzioni COUNT sarà zero e il risultato di tutte le altre funzioni sarà NULL.
• Discussione funzione aggregata non può contenere funzioni aggregate(funzione da funzione). Quelli. in una richiesta è impossibile, diciamo, ricevere un massimo di valori medi.
• Il risultato dell'esecuzione della funzione COUNT è numero intero(NUMERO INTERO). Altre funzioni aggregate ereditano i tipi di dati dei valori elaborati.
• Se, durante l'esecuzione della funzione SOMMA, si è ottenuto un risultato che supera il valore massimo del tipo di dati utilizzato, a sbaglio.

Quindi, se la richiesta non contiene Offerte GRUPPO PER, poi funzioni aggregate incluso in clausola SELECT, vengono eseguiti su tutte le stringhe di query risultanti. Se la richiesta contiene Offerta GRUPPO PER, ogni insieme di righe che ha gli stessi valori della colonna o del gruppo di colonne specificato in offerta GRUPPO PER, costituisce un gruppo, e funzioni aggregate eseguita separatamente per ogni gruppo.

AVERE offerta

Se Dove la clausola definisce un predicato per il filtraggio delle stringhe, quindi clausola AVERE applicato dopo il raggruppamento per definire un predicato simile filtrando i gruppi in base ai valori funzioni aggregate. Questa clausola è necessaria per validare i valori che si ottengono con funzione aggregata non da righe separate dell'origine record definita in clausola FROM, e da gruppi di tali linee. Pertanto, un tale controllo non può essere contenuto in Dove la clausola.

Per riassumere le informazioni contenute nel database, SQL fornisce funzioni di aggregazione. Una funzione di aggregazione prende un'intera colonna di dati come argomento e restituisce un singolo valore che riassume quella colonna in qualche modo.

Ad esempio, aggregare Funzione MEDIA() prende una colonna di numeri come argomento e ne calcola la media.

Per calcolare il reddito medio pro capite di un residente di Zelenograd, è necessaria la seguente query:

SELEZIONA 'RENDITO MEDIO=', AVG(UMD)

SQL ha sei funzioni aggregate che ti consentono di ottenere diversi tipi informazioni di sintesi (Fig. 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 in una colonna;

– COUNT(*) conta il numero di righe nella tabella dei risultati della query.

L'argomento della funzione di aggregazione può essere un semplice nome di colonna, come nell'esempio precedente, o un'espressione, come nella query seguente che specifica il calcolo dell'imposta pro capite:

SELEZIONA MEDIA(SUMD*0.13)

Questa query crea una colonna temporanea contenente i valori (SUMD*0.13) per ogni riga della tabella PERSON, quindi calcola la media della colonna temporanea.

La somma dei redditi di tutti i residenti di Zelenograd può essere calcolata utilizzando la funzione aggregata SOMMA:

SELEZIONA SOMMA (SOMMA) DA PERSONA

Una funzione di aggregazione può essere utilizzata anche per calcolare i totali di una tabella dei risultati ottenuta unendo più tabelle di origine. Ad esempio, si può calcolare importo totale reddito percepito dai residenti da una fonte denominata "Borsa di studio":

SELEZIONA SOMMA(SOLDI)

DA PROFITTO, HAVE_D

WHERE PROFIT.ID=HAVE_D.ID

AND PROFIT.SOURCE='Borsa di studio'

Funzioni aggregate MIN() e MAX() ti 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 percepito dai residenti e l'imposta più alta pagabile:

SELEZIONARE MIN(SOMMA), MASSIMA(SOMMA*0.13)

b) le date di nascita del residente più anziano e più giovane:

SELEZIONA MIN(RDATE), MAX(RDATE)

(c) cognomi, nomi e patronimici dei primi e degli ultimi residenti nell'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 di data precedenti sono considerati più piccoli di quelli successivi), gli intervalli di tempo vengono confrontati in base alla loro durata.

Quando si utilizzano le funzioni MIN() e MAX() con dati di stringa, il risultato del confronto di due stringhe dipende dalla tabella di codifica dei caratteri utilizzata.

La funzione di aggregazione COUNT() conta il numero di valori in una colonna di qualsiasi tipo:

(a) quanti appartamenti ci sono nel 1° microdistretto?

SELEZIONA COUNT(ADR) DA FLAT DOVE ADR LIKE "%, 1_ _-%"

(b) quanti residenti hanno fonti di reddito?

SELEZIONA COUNT(DISTINCT 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 contati i valori non ripetuti in una colonna).

La speciale funzione di aggregazione COUNT(*) conta le righe nella tabella dei risultati, non i valori dei dati:

(a) quanti appartamenti ci sono nel 2° microdistretto?

SELEZIONA COUNT(*) DA APPARTAMENTO DOVE ADR LIKE "%, 2__-%"

(b) quante fonti di reddito ha Ivanov Ivan Ivanovich?

SELECT COUNT(*) DA PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovich" E PERSON.NOM=HAVE_D.NOM

(c) quante persone vivono in un appartamento a un determinato indirizzo?

SELECT COUNT(*) DA PERSONA DOVE ADR="Zelenograd, 1001-45"

Un modo per capire come vengono eseguite le query di riepilogo con funzioni aggregate consiste nel pensare all'esecuzione della query come divisa in due parti. Innanzitutto, viene determinato come funzionerebbe la query senza funzioni di aggregazione, restituendo più righe di risultati. Le funzioni di aggregazione vengono quindi applicate ai risultati della query, restituendo un'unica riga di riepilogo.

Ad esempio, considera la seguente complessa query: trova il reddito totale pro capite medio, la somma del reddito totale dei residenti e il reddito medio della fonte come percentuale del reddito totale del residente. La risposta è data dall'operatore

SELECT AVG(SUMD), SUM(SUMD), (100*AVG(SOLDI/SUMD)) DA PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID

Senza funzioni aggregate, la query sarebbe simile a questa:

SELECT 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 della tabella dei risultati della query per produrre una tabella a una riga con risultati di riepilogo.

È possibile specificare una funzione di aggregazione nella stringa delle colonne restituita anziché in qualsiasi nome di colonna. Ad esempio, può essere parte di un'espressione che aggiunge o sottrae i valori di due funzioni aggregate:

SELEZIONARE MAX(SUMD)-MIN(SUMD) DALLA PERSONA

Tuttavia, una funzione aggregata non può essere un argomento per un'altra funzione aggregata, ad es. le funzioni aggregate nidificate sono vietate.

Inoltre, non è possibile utilizzare contemporaneamente funzioni aggregate e nomi di colonne regolari nell'elenco delle colonne restituite, perché non ha senso, ad esempio:

SELEZIONA FIO, SOMMA (SOMMA) DA PERSONA

Qui, il primo elemento dell'elenco dice al DBMS di creare una tabella che sarà composta da più righe e conterrà una riga per ogni abitante. Il secondo elemento della lista chiede al DBMS di restituire un unico valore di risultato, che è la somma dei valori nella colonna SUMD. Queste due direzioni si contraddicono a vicenda, risultando in un errore.

Quanto sopra non si applica ai casi di elaborazione di sottoquery e query con raggruppamento.

Offerta GRUPPO PER(istruzione SELECT) consente di raggruppare i dati (righe) in base al valore di una colonna o di più colonne o espressioni. Il risultato sarà un insieme di righe di riepilogo.

Ciascuna colonna nell'elenco di selezione deve essere presente nella clausola GROUP BY, ad eccezione delle costanti e delle colonne che sono operandi di funzioni aggregate.

Una tabella può essere raggruppata in base a qualsiasi combinazione delle sue colonne.

Funzioni aggregate vengono utilizzati per ottenere un singolo valore di riepilogo da un gruppo di righe. Tutte le funzioni di aggregazione eseguono calcoli su un singolo argomento, che può essere una colonna o un'espressione. Il risultato di qualsiasi funzione di aggregazione è un valore costante visualizzato in una colonna di risultati separata.

Le funzioni aggregate sono specificate nell'elenco di colonne dell'istruzione SELECT, che può contenere anche una clausola GROUP BY. Se non è presente alcuna clausola GROUP BY nell'istruzione SELECT e l'elenco di colonne select contiene, da almeno, una funzione aggregata, quindi non dovrebbe contenere colonne semplici. D'altra parte, un elenco di selezione di colonne può contenere nomi di colonna che non sono argomenti per una funzione di aggregazione se tali colonne sono argomenti per la clausola GROUP BY.

Se la query contiene una clausola WHERE, le funzioni di aggregazione calcolano un valore per i risultati della selezione.

Funzioni aggregate MIN e MAX calcola il più piccolo e valore più alto rispettivamente colonne. Gli argomenti possono essere numeri, stringhe e date. Tutti i valori NULL vengono rimossi prima del calcolo (cioè non vengono presi in considerazione).

Funzione aggregata SOMMA calcola la somma totale dei valori della colonna. Gli argomenti possono essere solo numeri. L'utilizzo dell'opzione DISTINCT elimina tutti i valori duplicati nella colonna prima di applicare la funzione SOMMA. Allo stesso modo, tutti i valori NULL vengono rimossi prima di applicare questa funzione di aggregazione.

Funzione di aggregazione AVG restituisce la media di tutti i valori in una colonna. Gli argomenti possono anche essere solo numeri e tutti i valori NULL vengono rimossi prima della valutazione.

Funzione aggregata COUNT ha due forme diverse:

• COUNT( col_name) - conta il numero di valori nella colonna col_name, i valori NULL vengono ignorati
• COUNT(*) - conta il numero di righe nella tabella, vengono presi in considerazione anche i valori NULL

Se la richiesta utilizza parola chiave DISTINCT, tutti i valori di colonna duplicati vengono rimossi prima di applicare la funzione COUNT.

COUNT_BIG Funzione simile alla funzione COUNT. L'unica differenza tra loro è il tipo di risultato che restituiscono: la funzione COUNT_BIG restituisce sempre valori BIGINT, mentre la funzione COUNT restituisce valori di dati INTEGER.

IN AVERE offerta definisce una condizione che si applica a un gruppo di righe. Per i gruppi di righe ha lo stesso significato della clausola WHERE per il contenuto della tabella corrispondente (WHERE si applica prima del raggruppamento, HAVING dopo).

• Funzioni aggregate sono usati come i nomi dei campi in Istruzione SELEZIONA, ma con un'eccezione: prendono il nome del campo come argomento. Con caratteristiche SOMMA e AVG può essere utilizzato solo campi numerici. Con caratteristiche CONTEGGIO, MAX e MIN possono essere utilizzati sia campi numerici che caratteri. Se utilizzato con campi di caratteri MASSIMO e MIN li tradurrà in un codice ASCII equivalente e li elaborerà in ordine alfabetico. Alcuni DBMS consentono aggregati nidificati, ma questa è una deviazione dallo standard ANSI, con tutte le sue implicazioni.

Ad esempio, puoi calcolare il numero di studenti che hanno sostenuto gli esami in ciascuna disciplina. Per fare ciò, è necessario eseguire una query raggruppata per il campo "Oggetto" e visualizzare come risultato il nome della disciplina e il numero di righe nel gruppo per questa disciplina. Usare il carattere * come argomento della funzione COUNT significa contare tutte le righe del gruppo.

SELEZIONA R1. Disciplina, COUNT(*)

GRUPPO PER R1 Disciplina;

Risultato:

SELEZIONA R1.Disciplina, CONTEGGIO (*)

DOVE R1. La valutazione NON È NULLA

GRUPPO PER R1 Disciplina;

Risultato:

non rientrerà nell'insieme delle tuple prima del raggruppamento, quindi il numero di tuple nel gruppo per la disciplina "Teoria dell'informazione" sarà 1 in meno.

Un risultato simile può essere ottenuto se la query è scritta nel modo seguente:

SELEZIONA R1. Disciplina, COUNT(R1. Punteggio)

GRUPPO PER R1. Disciplina;

Funzione COUNT (NOME ATTRIBUTO) conta il numero di valori definiti in un gruppo, a differenza di una funzione CONTANO(*), che conta il numero di righe in un gruppo. Infatti, nel gruppo con la disciplina "Teoria dell'informazione" ci saranno 4 righe, ma solo 3 determinati valori dell'attributo "Valutazione".

Regole di elaborazione valori NULL in funzioni aggregate

Se tutti i valori in una colonna sono uguali NULLO quando si calcola il risultato della funzione, sono esclusi.

Se tutti i valori in una colonna sono uguali NULLO, poi Massimo Somma minima media = NULLA, conteggio = 0 (zero).

Se il tavolo è vuoto, conteggio(*) = 0 .

Le funzioni aggregate possono essere utilizzate anche senza un'operazione di pre-raggruppamento, nel qual caso l'intera relazione viene trattata come un gruppo e per questo gruppo può essere calcolato un valore per gruppo.

Regole per l'interpretazione delle funzioni aggregate

Le funzioni aggregate possono essere incluse nell'elenco di output e quindi applicate all'intera tabella.

SELEZIONA MAX(Punteggio) da R1 assegnerà il massimo dei voti alla sessione;

SELEZIONA SOMMA (Punteggio) da R1 darà la somma di tutti i voti per la sessione;

SELEZIONA AVG(Punteggio) da R1 darà un punteggio medio su tutta la sessione.

2; Risultato: "larghezza="640"

Facendo sempre riferimento alla banca dati “Sessioni” (tabelle R1), troviamo il numero di esami superati con esito positivo:

SELEZIONA CONTEGGIO(*) Affittato _ esami

DOVE punteggio 2;

Risultato:

Le funzioni aggregate possono assumere singole colonne di tabelle come argomenti. Per calcolare, ad esempio, il numero significati diversi alcune colonne del gruppo, è necessario utilizzare la parola chiave DISTINCT insieme al nome della colonna. Calcoliamo il numero di diversi voti ricevuti in ciascuna disciplina:

SELEZIONA R1.Disciplina, COUNT (DISTINTA R1.Punteggio)

DOVE R1. La valutazione NON È NULLA

GRUPPO PER R1 Disciplina;

Risultato:

Lo stesso risultato si ottiene se viene esclusa la condizione esplicita nella parte WHERE, nel qual caso la query sarà simile a questa:

SELEZIONA R1. Disciplina, COUNT(DISTINCT R1. Punteggio)

GRUPPO PER R1. Disciplina;

Funzione COUNT(DISTINTO R1.Punteggio) conta solo certo vari i valori.

Per ottenere in questo caso risultato desiderato, è necessario effettuare una conversione preliminare del tipo di dati della colonna "Punteggio", trasformandola in un tipo reale, quindi il risultato del calcolo della media non sarà un 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.Subject, Count(*) come Total, AVG(cast(Score as decimal(3,1))) come Average_Score

Da R1,R2

dove R1. Nome completo = R2. Nome completo e R1. la valutazione non è nulla

e R1. Grado 2

Raggruppa per R2. Gruppo, R1. Disciplina;

Qui la funzione LANCIO() converte la colonna Punteggio in un tipo di dati valido.

Non è possibile utilizzare funzioni di aggregazione in una clausola WHERE perché le condizioni in questa sezione vengono valutate in termini di una singola riga, mentre le funzioni di aggregazione vengono valutate in termini di gruppi di righe.

La clausola GROUP BY consente di definire un sottoinsieme di valori in un determinato campo in termini di un altro campo e di applicare una funzione di aggregazione al sottoinsieme. Ciò consente di combinare campi e funzioni di aggregazione in un'unica clausola SELECT. Le funzioni aggregate possono essere utilizzate sia in un'espressione di output dei risultati SELEZIONA righe, e nell'espressione della condizione di elaborazione per i 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 del gruppo.

Costruiamo una query che mostri i gruppi in cui è stato ricevuto più di un due in una disciplina negli esami:

uno; Risultato: "larghezza="640"

SELEZIONA R2. Gruppo

DA R1, R2

DOVE R1. Nome completo = R2. Nome completo E

Punteggio R1 = 2

GRUPPO PER R2.Gruppo, R1.Disciplina

AVENDO conteggio(*) 1;

Risultato:

Abbiamo un database "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 dei conti nelle filiali. È possibile effettuare una query separata per ciascuno di essi selezionando SUM (Saldo) dalla tabella di ogni ramo, ma l'operazione GRUPPO BY ti consentirà di metterli tutti in un unico comando:

SELEZIONARE Ramo , SOMMA( Resto )

GRUPPO PER Filiale;

RAGGRUPPA PER applica le funzioni aggregate indipendentemente per ogni gruppo identificato dal valore del campo Ramo. Il gruppo è composto da linee con lo stesso valore campi Ramo e funzione SOMMA viene applicato separatamente per ciascuno di tali 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, proprio come il risultato di una funzione aggregata.

5000; Gli argomenti nella clausola HAVING seguono le stesse regole della clausola SELECT in cui viene utilizzato GROUP BY. Devono avere un valore per gruppo di output. "larghezza="640"

Si supponga di selezionare solo le filiali il cui saldo totale del conto è maggiore di $ 5.000, nonché i saldi totali delle filiali selezionate. Per visualizzare le filiali con saldi totali superiori a $ 5.000, utilizzare la clausola HAVING. La clausola HAVING specifica i criteri utilizzati per rimuovere determinati gruppi dall'output, proprio come fa la clausola WHERE per le singole righe.

Il comando corretto sarebbe:

SELEZIONA Filiale, SOMMA(Saldo)

RAGGRUPPA PER Ramo

AVENDO SOMMA ( Resto ) 5 000;

Argomenti in una frase AVENDO soggetto alle stesse regole della proposta SELEZIONARE dove viene utilizzato RAGGRUPPA PER. Devono avere un valore per gruppo di output.

Il seguente comando sarà bannato:

SELEZIONA Ramo,SOMMA(Saldo)

GRUPPO PER Filiale

AVENDO 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 output. Per evitare questa situazione, la proposta AVENDO dovrebbe fare riferimento solo agli aggregati e ai campi selezionati RAGGRUPPA PER. A disposizione Il modo giusto fare la richiesta di cui sopra:

SELEZIONA Ramo,SOMMA(Saldo)

DOVE OpenDate = '27/12/2004'

GRUPPO PER Filiale;

Significato richiesta successivo: trova la somma dei saldi per ciascuna filiale aperta il 27 dicembre 2004.

Come affermato in precedenza, HAVING può accettare solo argomenti che hanno un valore per gruppo di output. In pratica i riferimenti alle funzioni di aggregazione sono i più comuni, ma valgono anche i campi selezionati con GROUP BY. Ad esempio, vogliamo vedere i saldi totali sui conti delle filiali di San Pietroburgo, Pskov e Uryupinsk:

SELEZIONA Filiale, 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 nella relazione risultante, altrimenti otterremo una relazione vuota. "larghezza="640"

Pertanto, nelle espressioni aritmetiche dei predicati inclusi nella clausola di selezione della clausola HAVING, possono essere utilizzate direttamente solo le specifiche delle colonne specificate come colonne di raggruppamento nella clausola GROUP BY. Le colonne rimanenti possono essere specificate solo all'interno delle specifiche delle funzioni aggregate COUNT, SUM, AVG, MIN e MAX che restituiscono questo caso alcuni valore aggregato per l'intero gruppo di righe. Il risultato dell'esecuzione della sezione HAVING è una tabella raggruppata contenente solo quei gruppi di righe per i quali il risultato del calcolo della condizione di selezione nella parte HAVING è TRUE. In particolare, se una clausola HAVING è presente in una query che non contiene un GROUP BY, il risultato della sua esecuzione sarà o tavola vuota, o il risultato delle sezioni precedenti espressione tabulare A , considerato come un unico gruppo senza colonne di raggruppamento. Considera un esempio. Supponiamo di voler visualizzare l'importo totale dei saldi per tutte le filiali, ma solo se è superiore a $ 100.000. In questo caso, la nostra query non conterrà un'operazione di raggruppamento, ma conterrà una sezione HAVING e sarà simile a questa:

SELEZIONA SOMMA( Resto )

AVERE SOMMA( Resto ) 100 000;

Se il saldo totale è superiore a $ 100.000, lo vedremo nella relazione risultante, altrimenti otterremo una relazione vuota.

dal valore della colonna Disciplina . Otterremo 4 gruppi, per i quali possiamo calcolare alcuni valori di gruppo, come il numero di tuple nel gruppo, il valore massimo o minimo della colonna Punteggio. Tabella 5.7. Funzioni aggregate

Funzione	Risultato
CONTANO	Numero di righe o valori di campo non vuoti selezionati dalla query
SOMMA	Somma di tutti i valori selezionati di un determinato campo
AVG	La media aritmetica di tutti i valori selezionati in un determinato campo
MIN	Il più piccolo di tutti i valori selezionati per questo campo
MASSIMO	Il più grande di tutti i valori selezionati per questo campo

R1
	Nome e cognome	Disciplina	Grado
Gruppo 1	Petrov FI	Banca dati	5
	Sidorov K.A.	Banca dati	4
	Mironov AV	Banca dati	2
	Stepanova K. E.	Banca dati	2
	Krylova T.S.	Banca dati	5
	Vladimirov V.A.	Banca dati	5
Gruppo 2	Sidorov K.A.	Teoria dell'informazione	4
	Stepanova K. E.	Teoria dell'informazione	2
	Krylova T.S.	Teoria dell'informazione	5
	Mironov AV	Teoria dell'informazione	Nullo
Gruppo 3	Trofimov P.A.	Reti e telecomunicazioni	4
	Ivanova E.A.	Reti e telecomunicazioni	5
	Utkina N.V.	Reti e telecomunicazioni	5
Gruppo 4	Vladimirov V.A.	lingua inglese	4
	Trofimov P.A.	lingua inglese	5
	Ivanova E.A.	lingua inglese	3
	Petrov FI	lingua inglese	5

Funzioni aggregate sono usati come nomi di campo in un'istruzione SELECT, con un'eccezione: prendono il nome del campo come argomento. Solo i campi numerici possono essere utilizzati con le funzioni SOMMA e MEDIA. Sia i campi numerici che quelli di caratteri possono essere utilizzati con le funzioni COUNT , MAX e MIN. Se utilizzati con i campi di caratteri, MAX e MIN li tradurranno nel loro equivalente ASCII e li elaboreranno in ordine alfabetico. Alcuni DBMS consentono aggregati nidificati, ma questa è una deviazione dallo standard ANSI, con tutte le sue implicazioni.

Ad esempio, puoi calcolare il numero di studenti che hanno sostenuto gli esami in ciascuna disciplina. Per fare ciò, è necessario eseguire una query raggruppata per il campo "Oggetto" e visualizzare il nome della disciplina e il numero di righe nel gruppo per questa disciplina come risultato. Usare il carattere * come argomento della funzione COUNT significa contare tutte le righe del gruppo.

SELEZIONA R1.Disciplina, CONTEGGIO(*) DA R1 GRUPPO PER R1.Disciplina

Risultato:

Se vogliamo contare il numero di studenti che hanno superato l'esame in qualsiasi disciplina, è necessario escludere i valori nulli dal rapporto originale prima del raggruppamento. In questo caso, la richiesta sarà simile a questa:

Otteniamo il risultato:

In questo caso, la linea con lo studente

Mironov AV	Teoria dell'informazione	Nullo

non cadrà nell'insieme delle tuple prima del raggruppamento, quindi il numero di tuple nel gruppo per la disciplina " Teoria dell'informazione" sarà 1 in meno.

Può essere applicato funzioni aggregate anche senza l'operazione di preraggruppamento, nel qual caso l'intera relazione viene considerata come un gruppo e per questo gruppo può essere calcolato un valore per gruppo.

Facendo sempre riferimento alla banca dati "Sessione" (tabelle R1, R2, R3), troviamo il numero di esami superati con esito positivo:

Questo è ovviamente diverso dalla selezione di un campo, poiché viene sempre restituito un unico valore, indipendentemente dal numero di righe presenti nella tabella. Discussione funzioni aggregate ci possono essere colonne separate di tabelle. Ma per calcolare, ad esempio, il numero di valori diversi di una determinata colonna in un gruppo, devi utilizzare la parola chiave DISTINCT insieme al nome della colonna. Calcoliamo il numero di diversi voti ricevuti in ciascuna disciplina:

Risultato:

Il risultato può includere un valore di campo di raggruppamento e diversi funzioni aggregate e più campi possono essere utilizzati nelle condizioni di raggruppamento. In questo caso, i gruppi vengono formati in base a un insieme di campi di raggruppamento specificati. Le operazioni di aggregazione possono essere applicate per unire più tabelle di origine. Poniamoci ad esempio la domanda: determiniamo per ogni gruppo e per ogni disciplina il numero di coloro che hanno superato con successo l'esame e il punteggio medio per la disciplina.

Risultato:

Non possiamo usare funzioni aggregate nella clausola WHERE, perché i predicati sono valutati in termini di una singola riga, e funzioni aggregate- in termini di gruppi di righe.

La clausola GROUP BY consente di definire un sottoinsieme di valori in un determinato campo in termini di un altro campo e di applicare una funzione di aggregazione al sottoinsieme. Ciò consente di combinare campi e funzioni aggregate in una singola clausola SELECT. Funzioni aggregate può essere utilizzato sia nell'espressione di output dei risultati della riga SELECT, sia nell'espressione della condizione di elaborazione per i gruppi formati HAVING . In questo caso, ogni funzione aggregata viene calcolata per ogni gruppo selezionato. Valori risultanti dal calcolo funzioni aggregate, può essere utilizzato per visualizzare i risultati corrispondenti o per condizionare la selezione dei gruppi.

Costruiamo una query che mostra i gruppi in cui è stato ricevuto più di un due in una disciplina negli esami:

In futuro, ad esempio, lavoreremo non con il database "Session", ma con il database "Banca", costituito da una tabella F , che memorizza la relazione F contenente informazioni sui conti nelle filiali di una determinata banca:

F = (N, Nome completo, Filiale, Data di apertura, Data di chiusura, Saldo); Q = (Filiale, Città);

perché su questa base è possibile illustrare più chiaramente il lavoro con funzioni aggregate e raggruppamenti.

Ad esempio, supponiamo di voler trovare il saldo totale dei conti presso le filiali. È possibile effettuare una query separata per ciascuno di essi selezionando SUM(Balance) dalla tabella per ogni ramo. GROUP BY , tuttavia, li metterà tutti in un unico comando:

SELEZIONA Filiale, SOMMA(Saldo) DA F GRUPPO PER Filiale;

Si applica GROUP BY funzioni aggregate indipendentemente per ogni gruppo definito dal valore del campo Ramo. Il gruppo è costituito da righe con lo stesso valore nel campo Ramo e