Pentru a rezuma informațiile conținute în baza de date, SQL oferă funcții agregate. O funcție de agregare ia o întreagă coloană de date ca argument și returnează o singură valoare care rezumă acea coloană într-un mod specific.
De exemplu, agregat Funcția AVG() ia ca argument o coloană de numere și calculează media acestora.
Pentru a calcula venitul mediu pe cap de locuitor al unui rezident din Zelenograd, aveți nevoie de următoarea interogare:
SELECTAȚI „VENITUL MEDIU PE CAP DE CAPITAL=”, AVG(SUMD)
SQL are șase funcții agregate care vă permit să obțineți tipuri diferite informații rezumative (fig. 1):
– SUM() calculează suma tuturor valorilor conținute într-o coloană;
– AVG() calculează media valorilor conținute în coloană;
– MIN() găsește cea mai mică dintre toate valorile conținute în coloană;
– MAX() găsește cea mai mare dintre toate valorile conținute în coloană;
– COUNT() numără numărul de valori conținute într-o coloană;
– COUNT(*) numără numărul de rânduri din tabelul cu rezultatele interogării.
Argumentul unei funcții agregate poate fi un simplu nume de coloană, ca în exemplul anterior, sau o expresie, ca în următoarea interogare, care specifică calculul impozitului mediu pe cap de locuitor:
SELECTAȚI AVG(SUMD*0,13)
Această interogare creează o coloană temporară care conține valorile (SUMD*0,13) pentru fiecare rând din tabelul PERSONA, apoi calculează media coloanei temporare.
Suma venitului pentru toți rezidenții din Zelenograd poate fi calculată folosind funcția agregată SUM:
SELECTAȚI SUMA(SUMA) DE LA PERSOANĂ
O funcție de agregare poate fi, de asemenea, utilizată pentru a calcula totaluri dintr-un tabel cu rezultate obținute prin unirea mai multor tabele sursă. De exemplu, puteți calcula valoare totală venituri primite de rezidenți dintr-o sursă numită „Bursă”:
SELECTAȚI SUMA(BANI)
DIN PROFIT, HAVE_D
WHERE PROFIT.ID=HAVE_D.ID
AND PROFIT.SOURCE=’Bursă’
Funcții agregate MIN() și MAX() vă permit să găsiți cele mai mici și, respectiv, cele mai mari valori din tabel. Coloana poate conține valori numerice sau șir, sau valori de dată sau oră.
De exemplu, puteți defini:
(a) cel mai mic venit total primit de rezidenți și cel mai mare impozit de plătit:
SELECTARE MIN(SUMD), MAX(SUMD*0,13)
(b) datele de naștere a celui mai în vârstă și a celui mai tânăr rezident:
SELECTAȚI MIN(RDATE), MAX(RDATE)
(c) numele de familie, prenumele și patronimele primului și ultimilor rezidenți din listă, ordonate alfabetic:
SELECTARE MIN(FIO), MAX(FIO)
Când utilizați aceste funcții agregate, trebuie să vă amintiți că datele numerice sunt comparate folosind reguli aritmetice, datele sunt comparate secvenţial (valorile anterioare ale datei sunt considerate mai mici decât cele ulterioare), iar intervalele de timp sunt comparate în funcție de durata lor.
Când utilizați funcțiile MIN() și MAX() cu date șiruri, rezultatul comparării a două șiruri depinde de tabelul de codificare a caracterelor utilizat.
Funcția agregată COUNT() numără numărul de valori dintr-o coloană de orice tip:
(a) câte apartamente sunt în primul microdistrict?
SELECTAȚI COUNT(ADR) DIN PLAT WHERE ADR LIKE „%, 1_ _-%”
(b) câți rezidenți au surse de venit?
SELECTAȚI NUMĂR (NUMĂR DISTINCT) DIN HAVE_D
(c) câte surse de venit sunt folosite de rezidenți?
SELECT COUNT(DISTINCT ID) FROM HAVE_D (Cuvântul cheie DISTINCT specifică faptul că sunt contorizate valorile neduplicate din coloană).
Funcția de agregare specială COUNT(*) numără rândurile din tabelul cu rezultate, nu valorile datelor:
(a) câte apartamente sunt în al 2-lea microdistrict?
SELECTAȚI NUMĂRUL(*) DIN PLAT WHERE ADR CA „%, 2__-%”
(b) câte surse de venit are Ivan Ivanovici Ivanov?
SELECTAȚI COUNT(*) FROM PERSON, HAVE_D WHERE FIO="Ivanov Ivan Ivanovich" AND PERSON.NOM=HAVE_D.NOM
(c) câți rezidenți locuiesc într-un apartament la o anumită adresă?
SELECTARE COUNT(*) FROM PERSON WHERE ADR="Zelenograd, 1001-45"
O modalitate de a înțelege cum sunt executate interogările rezumate cu funcții agregate este să ne gândim la execuția interogării ca fiind împărțită în două părți. În primul rând, determinăm cum ar funcționa interogarea fără funcții agregate, returnând mai multe rânduri de rezultate. Funcțiile agregate sunt apoi aplicate rezultatelor interogării, returnând un singur rând rezultat.
De exemplu, luați în considerare următoarea interogare complexă: găsiți venitul total mediu pe cap de locuitor, suma venitului total al rezidenților și randamentul mediu al sursei ca procent din venitul total al rezidentului. Operatorul dă răspunsul
SELECTAȚI AVG(SUMD), SUM(SUMD), (100*AVG(MONEY/SUMD)) FROM PERSON, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM AND HAVE_D.ID=PROFIT.ID
Fără funcții agregate, interogarea ar arăta astfel:
SELECTAȚI SUMD, SUMD, BANI/SUMD DE LA PERSOANĂ, PROFIT, HAVE_D WHERE PERSON.NOM=HAVE_D.NOM ȘI HAVE_D.ID=PROFIT.ID
și ar returna un rând de rezultate pentru fiecare rezident și sursă specifică de venit. Funcțiile agregate utilizează coloanele din tabelul cu rezultatele interogării pentru a produce un tabel cu un singur rând cu rezultatele rezumate.
Puteți specifica o funcție de agregare în rândul de coloană returnat în loc de orice nume de coloană. De exemplu, poate face parte dintr-o expresie care adaugă sau scade valorile a două funcții agregate:
SELECTAȚI MAX(SUMD)-MIN(SUMD) DE LA PERSOANĂ
Cu toate acestea, o funcție agregată nu poate fi un argument pentru o altă funcție agregată, de exemplu. Funcțiile agregate imbricate sunt interzise.
De asemenea, lista coloanelor returnate nu poate folosi funcții agregate și nume de coloane obișnuite în același timp, deoarece acest lucru nu are sens, de exemplu:
SELECTARE FIO, SUM(SUMD) FROM PERSON
Aici, primul element al listei instruiește SGBD să creeze un tabel care va consta din mai multe rânduri și va conține câte un rând pentru fiecare rezident. Al doilea element al listei solicită SGBD să obțină o singură valoare de rezultat, care este suma valorilor din coloana SUMD. Aceste două instrucțiuni se contrazic reciproc, rezultând o eroare.
Cele de mai sus nu se aplică cazurilor de procesare a subinterogărilor și interogărilor cu grupare.
Utilizarea funcțiilor agregate
SQL definește multe funcții încorporate de diferite categorii, printre care un loc special este ocupat de funcțiile agregate, care operează pe valorile coloanelor de mai multe rânduri și returnează o singură valoare. Argumentele pentru agregarea funcțiilor pot fi atât coloane de tabel, cât și rezultatele expresiilor peste acestea. Funcțiile agregate în sine pot fi incluse în alte expresii aritmetice. Următorul tabel prezintă cele mai frecvent utilizate funcții agregate unare standard.
Formatul general al unei funcții agregate unare este următorul:
function_name([TOATE | DISTINCT] expresie)
unde DISTINCT specifică că funcția ar trebui să ia în considerare numai valori distincte ale argumentului, iar ALL specifică toate valorile, inclusiv duplicatele (acesta este implicit). De exemplu, funcția AVG cu cuvântul cheie DISTINCT pentru rândurile de coloane cu valorile 1, 1, 1 și 3 va returna 2, iar dacă cuvântul cheie ALL este prezent, va returna 1.5.
Funcțiile agregate sunt utilizate în clauzele SELECT și HAVING. Aici ne vom uita la utilizarea lor în clauza SELECT. În acest caz, expresia din argumentul funcției se aplică tuturor rândurilor din tabelul de intrare al clauzei SELECT. În plus, o clauză SELECT nu poate folosi atât funcții agregate, cât și coloane de tabel (sau expresii cu acestea) în absența unei clauze GROUP BY, pe care o vom analiza în secțiunea următoare.
Funcția COUNT are două formate. În primul caz, este returnat numărul de rânduri din tabelul de intrare; în al doilea caz, este returnat numărul de valori ale argumentului din tabelul de intrare:
- NUMARA(*)
- COUNT(expresie)
Cel mai simplu mod de a utiliza această funcție este să numărați numărul de rânduri din tabel (toate sau satisfăcătoare condiție specificată). Pentru aceasta, se folosește prima opțiune de sintaxă.
Interogare: numărul de tipuri de produse pentru care sunt disponibile informații în baza de date.
SELECTAȚI COUNT(*) AS „Numărul de tipuri de produse”
DE LA Produs
A doua versiune a sintaxei funcției COUNT poate lua numele unei singure coloane ca argument. În acest caz, se numără fie numărul tuturor valorilor din această coloană a tabelului de intrare, fie numai al celor care nu se repetă (folosind cuvântul cheie DISTINCT).
Interogare: Numărul de nume distincte conținute în tabelul Client.
SELECTAȚI NUMĂR (FNAME DISTINCT)
DE LA Client
Utilizarea altor funcții agregate unare este similară cu COUNT, cu excepția faptului că functii MINși MAX folosind cuvintele cheie DISTINCT și ALL nu are sens. Cu funcțiile COUNT, MAX și MIN, pe lângă câmpurile numerice, pot fi folosite și câmpuri de caractere. Dacă argumentul funcției agregate nu conține valori, funcția COUNT returnează 0 și toate celelalte revin Valoare NULL.
SELECTARE MAX (Data Comanda)
DIN
WHERE OrdDate"1.09.2010"
Misiunea pentru muncă independentă: Formulați interogări în SQL pentru a prelua următoarele date:
- Costul total al tuturor comenzilor;
- Numărul de orașe diferite conținut în tabelul Client.
- Funcții agregate folosit în mod similar cu numele câmpurilor în instrucțiunea SELECT, dar cu o singură excepție: iau numele câmpului ca argument. Cu functii SUMĂȘi AVG poate fi folosit doar câmpuri numerice. Cu functii COUNT, MAX și MIN Pot fi utilizate atât câmpurile numerice, cât și câmpurile de caractere. Când este utilizat cu câmpuri de caractere MAXȘi MIN le va traduce în echivalentul codului ASCII și le va procesa ordine alfabetică. Unele SGBD-uri permit utilizarea agregatelor imbricate, dar aceasta este o abatere de la standardul ANSI cu toate consecințele care decurg.
De exemplu, puteți calcula numărul de studenți care au susținut examene la fiecare disciplină. Pentru a face acest lucru, trebuie să rulați o interogare grupată după câmpul „Disciplina” și să afișați ca rezultat numele disciplinei și numărul de rânduri din grup pentru această disciplină. Folosirea caracterului * ca argument pentru funcția COUNT înseamnă numărarea tuturor rândurilor din grup.
SELECTAȚI R1. Disciplina, COUNT(*)
GRUPARE PE R1.Disciplina;
Rezultat:
SELECTARE R1.Disciplina, COUNT (*)
UNDE R1. Evaluarea NU ESTE NULĂ
GRUPARE PE R1.Disciplina;
Rezultat:
nu vor fi incluse în setul de tupluri înainte de grupare, astfel încât numărul de tupluri din grup pentru disciplina „Teoria informației” va fi cu 1 mai mic.
Un rezultat similar poate fi obținut dacă scrieți interogarea în felul următor:
SELECTAȚI R1. Disciplina, COUNT(R1. Evaluare)
GRUPARE PRIN R1. Disciplina;
Funcţie COUNT (NUME ATRIBUT) numără numărul de valori specifice dintr-un grup, spre deosebire de o funcție NUMARA(*), care numără numărul de rânduri dintr-un grup. Într-adevăr, într-o grupă cu disciplina „Teoria informației” vor exista 4 rânduri, dar doar 3 valori specifice pentru atributul „Evaluare”.
Reguli pentru manipularea valorilor NULL în funcțiile agregate
Dacă orice valori din coloană sunt egale NUL La calcularea rezultatului funcției, acestea sunt excluse.
Dacă toate valorile dintr-o coloană sunt egale NUL, Acea Max Min Sumă medie = NULL numărare = 0 (zero).
Dacă masa este goală, numărare(*) = 0 .
De asemenea, puteți utiliza funcții de agregare fără operația de pregrupare, caz în care întreaga relație este tratată ca un singur grup și pentru acest grup puteți calcula o valoare per grup.
Reguli de interpretare a funcţiilor agregate
Funcțiile agregate pot fi incluse în lista de rezultate și apoi sunt aplicate întregului tabel.
SELECT MAX (Evaluare) din R1 va acorda evaluarea maximă în timpul sesiunii;
SELECT SUM (Scor) din R1 va da suma tuturor notelor pentru sesiune;
SELECTAȚI AVG(Evaluare) din R1 va acorda un punctaj mediu pentru întreaga sesiune.
2; Rezultat: " width="640" Revenind din nou la baza de date „Sesiune” (tabelul R1), găsim numărul de examene promovate cu succes:
SELECTARE COUNT(*) As Livrat _ examene
WHERE Scorul 2;
Rezultat:
Argumentele pentru agregarea funcțiilor pot fi coloane individuale de tabel. Pentru a calcula, de exemplu, numărul sensuri diferite unele coloane din grup, trebuie să aplicați cuvânt cheie DISTINCT împreună cu numele coloanei. Să calculăm numărul de note diferite primite la fiecare disciplină:
SELECTARE R1.Disciplina, NUMĂRARE (DIstinct R1.Evaluare)
UNDE R1. Evaluarea NU ESTE NULĂ
GRUPARE PE R1.Disciplina;
Rezultat:
Același rezultat se obține dacă excludem condiția explicită din partea WHERE, caz în care interogarea va arăta astfel:
SELECTAȚI R1. Disciplina, COUNT(DIstinct R1. Evaluare)
GRUPARE PRIN R1. Disciplina;
Funcţie COUNT (DIstinct R1.Evaluare) contează doar sigur variat sensuri.
Pentru ca în acest caz să se obţină rezultatul dorit, este necesar să se facă o conversie preliminară a tipului de date al coloanei „Evaluare”, aducând-o la un tip real, apoi rezultatul calculării mediei nu va fi un număr întreg. În acest caz, cererea va arăta astfel:
2 Grupați după R2. Grupa, R1. Disciplina; Aici, funcția CAST() convertește coloana Score într-un tip de date valid. "width="640" Selectați R2.Group, R1.Discipline, Count(*) ca Total, AVG(cast(Scor as decimal(3,1))) as Average_score
De la R1,R2
unde R1. Nume complet = R2. Numele complet și R1. scorul nu este nul
și R1. Scor 2
Grupați după R2. Grupa, R1. Disciplina;
Iată funcția CAST() Convertește coloana Evaluare într-un tip de date valid.
Nu puteți utiliza funcții agregate într-o clauză WHERE deoarece condițiile din această secțiune sunt evaluate în termeni de un singur rând, iar funcțiile agregate sunt evaluate în termeni de grupuri de rânduri.
Clauza GROUP BY vă permite să definiți un subset de valori dintr-un anumit câmp în termenii unui alt câmp și să aplicați o funcție de agregare subsetului. Acest lucru face posibilă combinarea câmpurilor și a funcțiilor agregate într-o singură clauză SELECT. Funcțiile agregate pot fi utilizate atât în expresia de ieșire SELECTează linii, iar în exprimarea condiţiei de prelucrare a grupărilor formate AVÂND. În acest caz, fiecare funcție agregată este calculată pentru fiecare grup selectat. Valorile obținute din calculul funcțiilor agregate pot fi folosite pentru afișarea rezultatelor corespunzătoare sau pentru a condiționa selecția grupurilor.
Să construim o interogare care să afișeze grupuri în care s-a primit mai mult de o notă negativă la o disciplină la examene:
1; Rezultat: " width="640" SELECTARE R2. grup
DE LA R1,R2
UNDE R1. Nume complet = R2. Numele complet AND
R1.Scor = 2
GRUP DE R2.Grup, R1.Disciplina
AVÂND numărare(*) 1;
Rezultat:
Avem o bază de date „Banca”, formată dintr-un tabel F, care stochează relația F care conține informații despre conturile din sucursalele unei anumite bănci:
Aflați soldul total al conturilor din sucursale. Puteți face o interogare separată pentru fiecare dintre ele selectând SUM (Sold) din tabel pentru fiecare ramură, dar operația grupari GROUP BY vă va permite să le puneți pe toate într-o singură comandă:
SELECTAȚI Ramura , SUM( Rest )
GRUP PE Filiala;
A SE GRUPA CU aplică funcții agregate în mod independent fiecărui grup identificat prin valoarea câmpului Branch. Grupul este format din linii cu aceeași valoare Câmpuri Ramura și funcția SUMĂ se aplică separat pentru fiecare astfel de grup, adică soldul total al contului este calculat separat pentru fiecare sucursală. Valoarea câmpului căruia i se aplică A SE GRUPA CU, prin definiție, are o singură valoare per grup de ieșire, la fel ca rezultatul unei funcții agregate.
5.000; Argumentele dintr-o clauză HAVING urmează aceleași reguli ca și într-o clauză SELECT care utilizează GROUP BY . Acestea trebuie să aibă o valoare pentru fiecare grup de ieșiri. "width="640" Să presupunem că selectăm numai acele sucursale ale căror solduri totale ale conturilor depășesc 5.000 USD, precum și soldurile totale pentru sucursalele selectate. Pentru a afișa rezultate pentru sucursale cu solduri totale de peste 5.000 USD, trebuie să utilizați AVÂND oferta. Clauza HAVING specifică criteriile folosite pentru a elimina anumite grupuri din rezultat, la fel ca clauza WHERE pentru rândurile individuale.
Comanda corectă ar fi:
SELECTARE Sucursala, SUM(Rămas)
A SE GRUPA CU Ramura
AVÂND SUMA ( Rest ) 5 000;
Argumente într-o propoziție AVÂND respectați aceleași reguli ca în propoziție SELECTAȚI unde este folosit A SE GRUPA CU. Acestea trebuie să aibă o valoare pentru fiecare grup de ieșiri.
Următoarea comandă va fi interzisă:
SELECT Branch, SUM(Rămas)
GRUPA PE Filiala
AVÂND Data Deschiderii = 27/12/2004 ;
Camp Data deschiderii nu poate fi folosit într-o propoziție AVÂND, deoarece poate avea mai mult de o valoare per grup de ieșire. Pentru a evita o astfel de situație, sugestia AVÂND ar trebui să facă referire numai la agregatele și câmpurile selectate A SE GRUPA CU. Disponibil Calea cea buna faceti cererea de mai sus:
SELECT Branch, SUM(Rămas)
WHEREOpenDate = „27/12/2004”
GRUP PE Filiala;
Sens a acestei cereriîn continuare: găsiți suma soldurilor pentru fiecare ramură de conturi deschisă la 27 decembrie 2004.
După cum sa menționat mai devreme, HAVING poate lua doar argumente care au o singură valoare per grup de ieșire. În practică, referințele la funcțiile agregate sunt cele mai comune, dar câmpurile selectate folosind GROUP BY sunt și ele valide. De exemplu, dorim să vedem soldurile totale din conturile sucursalelor din Sankt Petersburg, Pskov și Uryupinsk:
SELECTARE Sucursala, SUM(Rămas)
DE LA F,Q
UNDE F. Filiala = Q. Filiala
GRUPA PE Filiala
HAVING Branch IN („Sankt Petersburg”, „Pskov”, „Uriupinsk”);
100.000; Dacă soldul total este mai mare de 100.000 USD, atunci îl vom vedea în relația rezultată, altfel vom obține o relație goală. "width="640"
Prin urmare, numai specificațiile coloanelor specificate ca coloane de grupare în clauza GROUP BY pot fi utilizate direct în expresiile aritmetice predicate incluse în clauza de selecție a clauzei HAVING. Coloanele rămase pot fi specificate numai în specificațiile funcțiilor agregate COUNT, SUM, AVG, MIN și MAX, care calculează în în acest caz, niste valoare agregată pentru întregul grup de rânduri. Rezultatul executării secțiunii HAVING este un tabel grupat care conține doar acele grupuri de rânduri pentru care rezultatul calculării condiției de selecție în secțiunea HAVING este TRUE. În special, dacă o clauză HAVING este prezentă într-o interogare care nu conține un GROUP BY, atunci rezultatul executării acesteia va fi fie masă goală, sau rezultatul executării secțiunilor anterioare expresie de tabel, tratat ca un singur grup fără gruparea coloanelor. Să ne uităm la un exemplu. Să presupunem că vrem să afișăm soldurile totale pentru toate filialele, dar numai dacă este mai mare de 100 000 USD. În acest caz, interogarea noastră nu va conține operațiuni de grupare, ci va conține o secțiune HAVING și va arăta astfel:
SELECTARE SUMA( Rest )
AVÂND SUM( Rest ) 100 000;
Dacă soldul total este mai mare de 100.000 USD, atunci îl vom vedea în relația rezultată, altfel vom obține o relație goală.
Poate produce generalizat procesare de grup valorile câmpului. Acest lucru se face folosind funcții agregate. Funcțiile agregate produc o singură valoare pentru un întreg grup de tabele. ÎN SQL Sunt furnizate următoarele funcții agregate:
- NUMARA– numără numărul de rânduri de tabel cu valori non-NULL ale câmpului specificat ca argument.
- SUMĂ– calculează suma aritmetică a tuturor valorilor selectate pentru un anumit câmp.
- AVG– face media tuturor valorilor selectate din acest câmp.
- MAX– afișaje cea mai mare valoare din toate valorile selectate ale acestui câmp.
- MIN– afișaje cea mai mică valoare din toate valorile selectate ale acestui câmp.
Utilizarea funcțiilor agregate
Funcțiile agregate sunt folosite ca numele câmpurilor într-o clauză SELECTAȚI cerere, dar cu o singură excepție: iau numele de câmpuri ca argument. Numai câmpurile numerice pot fi utilizate cu SUMĂȘi AVG. CU NUMARA, MAX, Și MIN Pot fi utilizate atât câmpurile numerice, cât și câmpurile de caractere. Când este utilizat cu câmpuri de caractere MAXȘi MIN le va traduce în echivalentul ASCII. Înseamnă că MINîl va alege pe primul și MAX ultima valoare în ordine alfabetică.
Pentru a găsi valoarea totală a vânzărilor în tabelul de vânzări, trebuie să scriem următoarea interogare:
SELECTAȚI SUM(Sum) FROM Vânzări
Ca rezultat obținem:
Această interogare a numărat numărul de valori non-nule din câmpul SNum din tabelul Sells. Dacă rescriem interogarea după cum urmează:
SELECTAȚI COUNT(SDate) FROM Sells
Ca rezultat obținem:
COUNT OF SDdate 4 Se obțin rezultate diferite de interogare atunci când se calculează aparent același lucru, deoarece una dintre valorile câmpului SDate are valoare goală (NUL). Fiți atenți când utilizați astfel de interogări.
