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.

La lezione tratterà l'argomento sql rinominare una colonna (campi) utilizzando la parola funzione AS; trattato anche il tema delle funzioni aggregate in sql. Saranno analizzati esempi specifici di richieste

È possibile rinominare i nomi delle colonne nelle query. Questo rende i risultati più leggibili.

In SQL, la ridenominazione dei campi è associata all'uso di parola chiave AS che viene utilizzato per rinominare i nomi dei campi nei set di risultati

Sintassi:

SELEZIONARE<имя поля>COME<псевдоним>A PARTIRE DAL ...

Diamo un'occhiata a un esempio di ridenominazione in SQL:

Esempio DB "Istituto": Visualizza i nomi degli insegnanti e i loro stipendi, per quegli insegnanti i cui stipendi sono inferiori a 15000, rinomina il campo zarplata in "Bassi salari"

Soluzione:

Rinominare le colonne in SQL è spesso necessario quando si calcolano i valori associati a più campi tabelle. Consideriamo un esempio:

Esempio DB "Istituto": Dalla tabella insegnanti, emettere il campo nome e calcolare l'importo dello stipendio e del bonus, nominando il campo "Premio_stipendio"

Soluzione:

1 2	SELEZIONA nome, (zarplata + premia) AS zarplata_premia FROM insegnanti;

SELEZIONA nome, (zarplata + premia) AS zarplata_premia FROM insegnanti;

Risultato:

Funzioni aggregate in SQL

Le funzioni aggregate in sql vengono utilizzate per ottenere i totali e calcolare le espressioni:

Tutte le funzioni aggregate restituiscono un singolo valore.

Le funzioni COUNT, MIN e MAX si applicano a qualsiasi tipo di dati.

Le funzioni SOMMA e AVG vengono utilizzate solo per i campi numerici.
C'è una differenza tra le funzioni COUNT (*) e COUNT (): la seconda non tiene conto dei valori NULL durante il conteggio.

Importante: quando si lavora con funzioni aggregate in SQL, viene utilizzata una parola speciale COME

Esempio DB "Istituto": Ottieni il valore dello stipendio più alto tra gli insegnanti, visualizza il risultato come "Max_zp"

Soluzione:

SELEZIONA MAX (zarplata) AS max_zp DA insegnanti;

SELEZIONA MAX (zarplata) AS max_zp DA insegnanti;

Risultati:

Consideriamo un esempio più complesso di utilizzo di funzioni aggregate in sql.

Soluzione:

Clausola GROUP BY in SQL

La clausola group by in sql viene solitamente utilizzata in combinazione con le funzioni aggregate.

Le funzioni aggregate vengono eseguite su tutte le righe di query risultanti. Se la query contiene una clausola GROUP BY, ogni insieme di righe specificato nella clausola GROUP BY costituisce un gruppo e vengono eseguite le funzioni aggregate per ogni gruppo separatamente.

Considera un esempio con la tabella delle lezioni:

Esempio:

Importante: Pertanto, come risultato dell'utilizzo di GROUP BY, tutte le righe di output della query sono divise in gruppi caratterizzati dalle stesse combinazioni di valori in queste colonne (ovvero, le funzioni aggregate vengono eseguite separatamente per ciascun gruppo).

Va tenuto presente che quando raggruppati da un campo contenente valori NULL, tutti i record di questo tipo rientreranno in un gruppo.

Per i vari tipi di stampanti, determinarne il costo medio e la quantità (cioè separatamente per laser, inkjet e matrice). Utilizzare le funzioni aggregate. Il risultato dovrebbe essere simile a questo:

Avere un'istruzione SQL

La clausola HAVING in SQL è necessaria per convalidare i valori, che si ottengono utilizzando la funzione di aggregazione dopo il raggruppamento(dopo aver utilizzato GROUP BY). un tale controllo non può essere contenuto in una clausola WHERE.

Esempio: Negozio di computer DB... Calcola il prezzo medio dei computer con la stessa velocità del processore. Calcola solo per quei gruppi il cui prezzo medio è inferiore a 30.000.

• 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.

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.

Come posso scoprire il numero di modelli di PC prodotti da un determinato fornitore? Come determinare il prezzo medio per computer con le stesse specifiche? Queste e molte altre domande relative ad alcune informazioni statistiche possono essere risolte utilizzando funzioni riassuntive (aggregate)... La norma prevede le seguenti funzioni aggregate:

Tutte queste funzioni restituiscono un singolo valore. In questo caso, le funzioni CONTE, MIN e MAX sono applicabili a qualsiasi tipo di dati, mentre SOMMA e AVG vengono utilizzati solo per i campi numerici. Differenza tra funzione CONTARE (*) e CONTARE (<имя поля>) è 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 di computer disponibili dal produttore A:

Esempio. Se siamo interessati al numero di diversi modelli prodotti dal produttore A, allora la query può essere formulata come segue (sfruttando il fatto che nella tabella Prodotto ogni modello viene registrato una volta):

Esempio. Trova il numero di modelli diversi disponibili dal produttore A. La query è simile alla precedente, in cui era richiesto di determinare il numero totale di modelli prodotti dal produttore A. Qui devi anche trovare il numero di modelli diversi nel PC da tavolo (cioè disponibile in commercio).

Per garantire che vengano utilizzati solo valori univoci quando si ottengono indicatori statistici, quando argomento delle funzioni aggregate può essere utilizzato Parametro DISTINCT... Altro parametro ALLè l'impostazione predefinita e presuppone che vengano conteggiati tutti i valori restituiti nella colonna. Operatore,

Se abbiamo bisogno di ottenere il numero di modelli di PC prodotti ogni produttore, sarà necessario utilizzare Clausola GROUP BY segue sintatticamente clausole WHERE.

Clausola GROUP BY

Clausola GROUP BY utilizzato per definire gruppi di linee di output a cui può essere applicato funzioni aggregate (COUNT, MIN, MAX, AVG e SUM)... Se questa clausola è mancante e vengono utilizzate funzioni aggregate, allora tutte le colonne con i nomi menzionati in SELEZIONARE dovrebbe essere incluso in funzioni aggregate e queste funzioni verranno applicate all'intero set di righe che soddisfano il predicato della query. Altrimenti, tutte le colonne dell'elenco SELECT, non incluso nelle funzioni aggregate, deve essere specificato nella clausola GROUP BY... Di conseguenza, tutte le righe di output della query sono suddivise in gruppi caratterizzati dalle stesse combinazioni di valori in queste colonne. Successivamente, le funzioni aggregate verranno applicate a ciascun gruppo. Nota che per GROUP BY tutti i valori NULL sono trattati come uguali, ad es. quando raggruppate da un campo contenente valori NULL, tutte queste righe rientreranno in un gruppo.
Se se c'è una clausola GROUP BY, nella clausola SELECT nessuna funzione aggregata, la query restituirà semplicemente una riga da ciascun gruppo. Questa funzionalità, insieme alla parola chiave DISTINCT, può essere utilizzata per eliminare le righe duplicate nel set di risultati.
Diamo un'occhiata a un semplice esempio:

SELEZIONA modello, COUNT (modello) AS Qty_model, AVG (price) AS Avg_price DA PC Modello GROUP BY;

In questa richiesta, per ogni modello di PC, vengono determinati il ​​loro numero e il costo medio. Tutte le righe con gli stessi valori del modello formano un gruppo e l'output SELECT calcola il numero di valori e i valori del prezzo medio per ciascun gruppo. La query risulterà nella seguente tabella:

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

Se il SELECT avesse una colonna con una data, sarebbe possibile calcolare questi indicatori per ogni data specifica. Per fare ciò, è necessario aggiungere una data come colonna di raggruppamento, quindi le funzioni aggregate verranno calcolate per ogni combinazione di valori (model-date).

Ci sono diversi specifici regole per lo svolgimento di funzioni aggregate:

• Se come risultato della query nessuna linea ricevuta(o più di una riga per un dato gruppo), i dati iniziali per il calcolo di una qualsiasi delle funzioni aggregate sono assenti. 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 di per sé funzioni aggregate(funzione da funzione). Quelli. in una singola query, non puoi, ad esempio, ottenere il massimo delle medie.
• Il risultato dell'esecuzione della funzione COUNT è numero intero(NUMERO INTERO). Altre funzioni aggregate ereditano i tipi di dati dei valori in elaborazione.
• Se, durante l'esecuzione della funzione SOMMA, si è ottenuto un risultato che supera il valore massimo del tipo di dato utilizzato, viene errore.

Quindi, se la richiesta non contiene Clausole GROUP BY, poi funzioni aggregate incluso in clausola SELECT, vengono eseguiti su tutte le righe di query risultanti. Se la richiesta contiene Clausola GROUP BY, ogni set di righe che ha gli stessi valori di colonna o gruppo di colonne specificati in Clausola GROUP BY, costituisce un gruppo, e funzioni aggregate vengono eseguite separatamente per ogni gruppo.

clausola HAVING

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