Clauza GROUP BY(instrucțiunea SELECT) vă permite să grupați datele (rândurile) după valoarea unei coloane sau a mai multor coloane sau expresii. Rezultatul va fi un set de rânduri rezumative.
Fiecare coloană din lista de selectare trebuie să fie prezentă în clauza GROUP BY, singurele excepții sunt constantele și coloanele - operanzi ai funcțiilor agregate.
Un tabel poate fi grupat după orice combinație a coloanelor sale.
Funcții agregate sunt folosite pentru a obține o singură valoare totală dintr-un grup de rânduri. Toate funcțiile agregate efectuează calcule pe un singur argument, care poate fi fie o coloană, fie o expresie. Orice funcție agregată evaluează la o valoare constantă care este afișată într-o coloană separată în rezultat.
Funcțiile agregate sunt specificate în lista de coloane a unei instrucțiuni SELECT, care poate conține și o clauză GROUP BY. Dacă instrucțiunea SELECT nu conține o clauză GROUP BY, iar lista de coloane select conține cel puțin o funcție de agregare, atunci nu trebuie să conțină coloane simple. Pe de altă parte, o listă selectată de coloane poate conține nume de coloane care nu sunt argumente pentru funcția de agregare dacă acele coloane sunt folosite ca argumente pentru clauza GROUP BY.
Dacă interogarea conține o clauză WHERE, atunci funcțiile agregate calculează o valoare pentru rezultatele selecției.
Funcțiile agregate MIN și MAX se calculează cele mai mici și, respectiv, cele mai mari valori ale coloanei. Argumentele pot fi numere, șiruri și date. Toate valorile NULL sunt eliminate înainte de calcul (adică, nu sunt luate în considerare).
Funcția agregată SUM calculează suma totală a valorilor coloanei. Numai numerele pot fi argumente. Utilizarea parametrului DISTINCT elimină orice valori duplicate din coloană înainte de a aplica funcția SUM. De asemenea, eliminați toate valorile NULL înainte de a aplica această funcție de agregare.
Funcția agregată AVG returnează media tuturor valorilor dintr-o coloană. Argumentele pot fi, de asemenea, doar numere, iar toate valorile NULL sunt eliminate înainte de evaluare.
Funcția agregată COUNT are două forme diferite:
- COUNT (col_name) - numără numărul de valori din coloana col_name, valorile NULL nu sunt numărate
- COUNT (*) - numără numărul de rânduri din tabel, se iau în considerare și valorile NULL
Dacă interogarea folosește cuvântul cheie DISTINCT, toate valorile coloanelor duplicat sunt eliminate înainte ca funcția COUNT să fie utilizată.
COUNT_BIG funcție aceeași funcție ca COUNT. Singura diferență dintre cele două este tipul de rezultat pe care îl returnează: COUNT_BIG returnează întotdeauna valori BIGINT, în timp ce COUNT returnează valori de date INTEGER.
V AVÂND oferta definește o condiție care se aplică unui grup de rânduri. Are aceeași semnificație pentru grupurile de rânduri ca și clauza WHERE pentru conținutul tabelului corespunzător (UNDE se aplică înainte de grupare, HAVING după).
Lecția va acoperi subiectul sql redenumirea unei coloane (câmpuri) folosind cuvântul funcției AS; a acoperit, de asemenea, subiectul funcțiilor agregate în sql. Vor fi analizate exemple specifice de cereri
Puteți redenumi numele coloanelor în interogări. Acest lucru face rezultatele mai lizibile.
În SQL, redenumirea câmpurilor este asociată cu utilizarea lui cuvânt cheie AS care este folosit pentru a redenumi numele câmpurilor din seturile de rezultate
Sintaxă:
SELECTAȚI<имя поля>LA FEL DE<псевдоним>DIN...
Să ne uităm la un exemplu de redenumire în SQL:
Exemplu DB „Institut”: Afișați numele profesorilor și salariile acestora, pentru acei profesori ale căror salarii sunt sub 15000, redenumiți câmpul zarplata în "Salariu mic"
✍ Soluție:
Redenumirea coloanelor în SQL este adesea necesară la calcularea valorilor asociate cu mai multe câmpuri Mese. Să luăm în considerare un exemplu:
Exemplu DB „Institut”: Din tabelul profesorilor, scoateți câmpul de nume și calculați suma salariului și a bonusului, denumind câmpul „Salariu_ premiu”
✍ Soluție:
| 1 2 | SELECT numele, (zarplata + premia) AS zarplata_premia FROM profesori; |
SELECT numele, (zarplata + premia) AS zarplata_premia FROM profesori;
Rezultat:
Funcții agregate în SQL
Funcțiile agregate în sql sunt folosite pentru a obține totaluri și a calcula expresii:
Toate funcțiile agregate returnează o singură valoare.
Funcțiile COUNT, MIN și MAX se aplică oricărui tip de date.
Funcțiile SUM și AVG sunt utilizate numai pentru câmpurile numerice.
Există o diferență între funcțiile COUNT (*) și COUNT (): a doua nu ia în considerare valorile NULL la numărare.
Important: atunci când lucrați cu funcții agregate în SQL, se folosește un cuvânt special LA FEL DE
Exemplu DB „Institut”: Obțineți valoarea celui mai mare salariu dintre profesori, afișați rezultatul ca „Max_zp”
✍ Soluție:
| SELECT MAX (zarplata) AS max_zp FROM profesori; |
SELECT MAX (zarplata) AS max_zp FROM profesori;
Rezultate:
Să luăm în considerare un exemplu mai complex de utilizare a funcțiilor agregate în sql.
✍ Soluție:
Clauza GROUP BY în SQL
Clauza group by din sql este de obicei folosită împreună cu funcțiile de agregare.
Funcțiile agregate sunt efectuate pe toate rândurile de interogare rezultate. Dacă interogarea conține o clauză GROUP BY, fiecare set de rânduri specificat în clauza GROUP BY constituie un grup și funcțiile de agregare sunt executate pentru fiecare grup separat.
Luați în considerare un exemplu cu tabelul de lecții: 
Exemplu:
Important: Astfel, ca urmare a utilizării GROUP BY, toate rândurile de ieșire ale interogării sunt împărțite în grupuri caracterizate de aceleași combinații de valori în aceste coloane (adică, funcțiile de agregare sunt efectuate pentru fiecare grup separat).
Trebuie avut în vedere că atunci când sunt grupate după un câmp care conține valori NULL, toate astfel de înregistrări se vor încadra într-un singur grup.
Pentru diferite tipuri de imprimante, determinați costul și cantitatea lor medie (adică separat pentru laser, cu jet de cerneală și matrice). Utilizați funcții agregate. Rezultatul ar trebui să arate astfel:
Având instrucțiune SQL
Clauza HAVING din SQL este necesară pentru a valida valori, care se obţin folosind funcţia de agregare după grupare(după folosirea GROUP BY). O astfel de verificare nu poate fi inclusă într-o clauză WHERE.
Exemplu: Magazin de calculatoare DB... Calculați prețul mediu al computerelor cu aceeași viteză a procesorului. Calculați numai pentru acele grupuri al căror preț mediu este mai mic de 30.000.
- Funcții agregate sunt folosite ca numele câmpurilor din instrucțiunea SELECT, cu o singură excepție: iau numele câmpului ca argument. Cu functii SUMĂși AVG pot fi folosite doar câmpuri numerice. Cu functii COUNT, MAX și MIN pot fi utilizate atât câmpuri numerice, cât și câmpuri de caractere. Când este utilizat cu câmpuri de caractere MAXși MIN le va traduce în echivalentul ASCII și le va procesa în 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 promovat examene la fiecare disciplină. Pentru a face acest lucru, trebuie să executați o interogare grupată după câmpul „Disciplina” și să afișați ca rezultat numele disciplinei și numărul de linii din grup pentru această disciplină. Utilizarea caracterului * ca argument pentru funcția COUNT înseamnă că toate liniile din grup sunt numărate.
SELECTAȚI R1. Disciplina, COUNT (*)
GRUPARE PRIN R1.Disciplina;
Rezultat:
SELECTARE R1 Disciplina, COUNT (*)
UNDE R1. IS NOT NULL scor
GRUPARE PRIN R1.Disciplina;
Rezultat:
nu vor fi incluse în setul de tupluri înainte de grupare, astfel încât numărul de tupluri din grupa la disciplina „Teoria informației” va fi cu 1 mai mic.
Un rezultat similar poate fi obținut dacă scrieți cererea î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 definite dintr-un grup, spre deosebire de o funcție NUMARA (*), care numără numărul de linii din grup. Într-adevăr, în grupa cu disciplina „Teoria informației” vor fi 4 rânduri, dar doar 3 valori specifice atributului „Evaluare”.
Reguli pentru manipularea valorilor nule în funcțiile agregate
Dacă orice valori din coloană sunt egale NUL sunt excluse la calcularea rezultatului funcției.
Dacă toate valorile dintr-o coloană sunt egale NUL, atunci Max Min Sum Mediu = NULL, numărare = 0 (zero).
Dacă masa este goală, număr (*) = 0 .
Funcțiile agregate pot fi utilizate și fără operația de grupare preliminară, în acest caz întreaga relație este considerată ca un grup și poate fi calculată o valoare per grup pentru acest 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 (Scor) din R1 va acorda ratingul maxim la sesiune;
SELECTAȚI SUMA din R1 va da suma tuturor evaluărilor pe sesiune;
SELECTAȚI AVG (Scor) din R1 va acorda un punctaj mediu pentru întreaga sesiune.
2; Rezultat: "width =" 640 " Referindu-ne din nou la baza de date Session (tabelele R1), vom afla numărul de examene promovate cu succes:
SELECTARE NUMĂR (*) As Închiriat _ examene
UNDE Clasa 2;
Rezultat:
Argumentul funcțiilor agregate poate fi coloane separate de tabele. Pentru a calcula, de exemplu, numărul de valori distincte ale unei anumite coloane dintr-un grup, este necesar să folosiți cuvântul cheie DISTINCT împreună cu numele coloanei. Să calculăm numărul de note diferite primite pentru fiecare disciplină:
SELECTARE R1 Disciplina, COUNT (Evaluare DISTINCT R1)
UNDE R1. IS NOT NULL scor
GRUPARE PRIN R1.Disciplina;
Rezultat:
Același rezultat se obține dacă excludeți 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) consideră doar sigur variat valorile.
Pentru ca rezultatul dorit să se obțină în acest caz, este necesar să se facă o transformare preliminară a tipului de date al coloanei „Scor”, aducându-l la un tip real, apoi rezultatul calculării mediei nu va fi un întreg. În acest caz, cererea va arăta astfel:
2 Grupați după R2. Grupa, R1. Disciplina; Aici, funcția CAST () convertește coloana „Scor” într-un tip de date valid. "lățime =" 640 " Selectați R2.Group, R1.Discipline, Count (*) ca Total, AVG (distribuit (Scor zecimal (3,1))) ca Average_point
De la R1, R2
unde R1. Nume complet = R2. Nume și R1. scorul nu este nul
și R1. Clasa 2
Grupați după R2. Grupa, R1. Disciplina;
Aici funcția DISTRIBUIE () convertește coloana Scor într-un tip de date valid.
Nu puteți utiliza funcții agregate în clauza 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 de agregare pot fi utilizate atât în expresia pentru afișarea rezultatelor rândului SELECT, cât și în expresia pentru procesarea grupurilor HAVING generate. În acest caz, fiecare funcție agregată este calculată pentru fiecare grup selectat. Valorile obținute la calcularea funcțiilor agregate pot fi utilizate pentru afișarea rezultatelor corespunzătoare sau pentru starea de selecție a grupurilor.
Să construim o interogare care să afișeze grupurile în care s-au obținut mai mult de un deuce la o disciplină la examene:
unu; Rezultat: "width =" 640 " SELECTARE R2. grup
DE LA R1, R2
UNDE R1. Nume complet = R2. Nume AND
Scorul R1 = 2
GRUPA DE R2. Grupa, R1. Disciplina
AVÂND număr (*) 1;
Rezultat:
Avem o DB „Banca”, formată dintr-un tabel F, care stochează relația F, care conține informații despre conturile din sucursalele unei anumite bănci:
Găsiți soldul total al contului în sucursale. Este posibil să se facă o interogare separată pentru fiecare dintre ele selectând SUM din tabel pentru fiecare ramură, dar operația GROUP BY le va pune pe toate într-o singură comandă:
SELECTAȚI Ramura , SUMA ( Rest )
GRUP PE Filiala;
A SE GRUPA CU aplică funcții agregate în mod independent pentru fiecare grup identificat prin valoarea câmpului Filială. Grupul este format din linii cu aceeași valoare de câmp Branch ș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, are, prin definiție, o singură valoare per grup de ieșire, la fel ca rezultatul unei funcții agregate.
5.000; Argumentele din clauza HAVING urmează aceleași reguli ca și în clauza SELECT, care utilizează GROUP BY. Acestea trebuie să aibă o valoare pentru fiecare grup de ieșiri. "lățime =" 640 " Să presupunem că selectați doar acele ramuri, ale căror valori totale ale soldurilor conturilor depășesc 5.000 USD, precum și soldurile totale pentru ramurile selectate. Pentru a afișa sucursale cu solduri totale de peste 5.000 USD, trebuie să utilizați clauza HAVING. Clauza HAVING definește criteriile folosite pentru a elimina anumite grupuri din rezultat, la fel ca clauza WHERE pentru rândurile individuale.
Comanda corectă ar fi următoarea:
SELECTARE Sucursala, SUMA (Sold)
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ă:
SELECTARE Sucursala, SUMA (Sold)
GRUP 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 pe grup de ieșiri. Pentru a evita o astfel de situație, propunerea AVÂND ar trebui să se refere numai la agregate și câmpuri selectate A SE GRUPA CU... Există o modalitate corectă de a face interogarea de mai sus:
SELECTARE Sucursala, SUMA (Sold)
WHERE Data deschiderii = '27 / 12/2004 '
GRUP PE Filiala;
Sensul acestei interogări este următorul: găsiți suma soldurilor pentru fiecare ramură de conturi deschisă la 27 decembrie 2004.
După cum sa menționat mai devreme, HAVING poate folosi doar argumente care au aceeași valoare pentru fiecare grup de ieșire. În practică, referințele la funcțiile agregate sunt cele mai generale, dar câmpurile selectate cu GROUP BY sunt și ele valabile. De exemplu, dorim să vedem soldurile totale ale conturilor sucursalelor din Sankt Petersburg, Pskov și Uryupinsk:
SELECTARE Sucursala, SUMA (Sold)
DE LA F, Q
UNDE F. Filiala = Q. Filiala
GRUP 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 raportul rezultat, altfel vom obține un raport gol. "lățime =" 640 "
Prin urmare, în expresiile aritmetice ale predicatelor incluse în condiția de selecție a clauzei HAVING, puteți utiliza direct doar specificațiile coloanelor specificate ca coloane de grupare în clauza GROUP BY. Restul coloanelor pot fi specificate doar în specificațiile funcțiilor agregate COUNT, SUM, AVG, MIN și MAX, care în acest caz calculează o anumită valoare agregată pentru întregul grup de rânduri. Rezultatul executării clauzei HAVING este un tabel grupat care conține doar acele grupuri de rânduri pentru care rezultatul evaluării condiției de selecție în partea HAVING este TRUE. În special, dacă o clauză HAVING este prezentă într-o interogare care nu conține un GROUP BY, atunci rezultatul execuției sale va fi fie un tabel gol, fie rezultatul secțiunilor anterioare ale expresiei tabelului, considerat ca un grup fără gruparea coloanelor. Să ne uităm la un exemplu. Să presupunem că vrem să afișăm suma totală a soldurilor pentru toate ramurile, dar numai dacă aceasta 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:
SELECTAȚI SUMA ( Rest )
AVÂND SUMA ( Rest ) 100 000;
Dacă soldul total este mai mare de 100.000 USD, atunci îl vom vedea în raportul rezultat, altfel vom obține un raport gol.
Următoarele subsecțiuni descriu alte clauze SELECT care pot fi utilizate în interogări, precum și funcții agregate și seturi de instrucțiuni. Permiteți-mi să vă reamintesc că până acum am acoperit utilizarea clauzei WHERE, iar în acest articol ne vom uita la clauzele GROUP BY, ORDER BY și HAVING și vom oferi câteva exemple de utilizare a acestor clauze în combinație cu funcțiile agregate. care sunt acceptate în Transact-SQL.
Clauza GROUP BY
Propoziție A SE GRUPA CU grupează setul de rânduri selectat pentru a obține un set de rânduri rezumative pe baza valorilor uneia sau mai multor coloane sau expresii. Un caz de utilizare simplu pentru clauza GROUP BY este prezentat în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE Job FROM Works_On GROUP BY Job;
În acest exemplu, posturile de angajați sunt eșantionate și grupate.
În exemplul de mai sus, clauza GROUP BY creează un grup separat pentru toate valorile posibile (inclusiv NULL) pentru coloana Job.
Utilizarea coloanelor în clauza GROUP BY trebuie să îndeplinească anumite condiții. În special, fiecare coloană din lista de selecție a interogării trebuie să apară și în clauza GROUP BY. Această cerință nu se aplică constantelor și coloanelor care fac parte dintr-o funcție de agregare. (Funcțiile agregate sunt discutate în subsecțiunea următoare.) Acest lucru are sens deoarece numai coloanele din clauza GROUP BY au o valoare garantată per grup.
Un tabel poate fi grupat după orice combinație a coloanelor sale. Exemplul de mai jos demonstrează gruparea rândurilor din tabelul Works_on pe două coloane:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, Job FROM Works_On GROUP BY ProjectNumber, Job;
Rezultatul acestei interogări:
Pe baza rezultatelor interogării, puteți vedea că există nouă grupuri cu diferite combinații de număr și titlu de proiect. Secvența de nume de coloane din clauza GROUP BY nu trebuie să fie aceeași cu cea din lista de coloane SELECT.
Funcții agregate
Funcțiile agregate sunt folosite pentru a obține valorile sumei. Toate funcțiile agregate pot fi împărțite în următoarele categorii:
funcții agregate obișnuite;
funcții agregate statistice;
funcții agregate definite de utilizator;
funcţii agregate analitice.
Aici ne vom uita la primele trei tipuri de funcții agregate.
Funcții agregate obișnuite
Transact-SQL acceptă următoarele șase funcții agregate: MIN, MAX, SUMĂ, AVG, NUMARA, COUNT_BIG.
Toate funcțiile agregate efectuează calcule pe un singur argument, care poate fi fie o coloană, fie o expresie. (Singura excepție este a doua formă a două funcții, COUNT și COUNT_BIG, și anume COUNT (*) și, respectiv, COUNT_BIG (*).) Orice funcție agregată evaluează la o valoare constantă care apare într-o coloană separată de rezultat.
Funcțiile agregate sunt specificate în lista de coloane a unei instrucțiuni SELECT, care poate conține și o clauză GROUP BY. Dacă instrucțiunea SELECT nu include o clauză GROUP BY, iar lista de coloane de selecție conține cel puțin o funcție de agregare, atunci nu trebuie să conțină coloane simple (altele decât coloanele utilizate ca argumente pentru funcția de agregare). Prin urmare, codul din exemplul de mai jos este incorect:
UTILIZAȚI SampleDb; SELECTAȚI Nume, MIN (Id) FROM Angajat;
Aici, coloana LastName din tabelul Employee nu ar trebui să fie în lista de coloane select, deoarece nu este un argument pentru funcția de agregare. Pe de altă parte, o listă selectată de coloane poate conține nume de coloane care nu sunt argumente pentru funcția de agregare dacă acele coloane sunt folosite ca argumente pentru clauza GROUP BY.
Un argument de funcție agregată poate fi precedat de unul dintre cele două cuvinte cheie posibile:
TOATEIndică faptul că calculele sunt efectuate pentru toate valorile din coloană. Aceasta este valoarea implicită.
DISTINCTSpecifică faptul că numai valorile unice ale coloanei sunt utilizate pentru calcule.
Funcțiile agregate MIN și MAX
Funcțiile agregate MIN și MAX calculează cele mai mici și, respectiv, cele mai mari valori dintr-o coloană. Dacă interogarea conține o clauză WHERE, funcțiile MIN și MAX returnează cele mai mici și cele mai mari valori ale rândului care corespund condițiilor specificate. Exemplul de mai jos demonstrează utilizarea funcției de agregare MIN:
UTILIZAȚI SampleDb; - Returnează 2581 SELECT MIN (Id) AS „Valoarea minimă a ID” FROM Employee;
Rezultatul returnat în exemplul de mai sus nu este foarte informativ. De exemplu, numele de familie al angajatului care deține acest număr este necunoscut. Dar nu puteți obține acest nume de familie în mod obișnuit, deoarece, așa cum am menționat mai devreme, nu puteți specifica în mod explicit coloana LastName. Pentru a obține numele de familie al acestui angajat împreună cu cel mai mic număr de personal al angajatului, se folosește o subinterogare. Exemplul de mai jos demonstrează utilizarea unei astfel de subinterogări, în care subinterogarea conține instrucțiunea SELECT din exemplul anterior:
Rezultatul executării interogării:
Utilizarea funcției de agregare MAX este prezentată în exemplul de mai jos:
Funcțiile MIN și MAX pot accepta șiruri și date ca argumente. În cazul unui argument șir, valorile sunt comparate folosind ordinea reală de sortare. Pentru toate argumentele de date temporare de tip dată, cea mai mică valoare a coloanei este data cea mai veche, iar valoarea cea mai mare este cea mai recentă.
Cuvântul cheie DISTINCT poate fi folosit cu funcțiile MIN și MAX. Toate valorile NULL sunt eliminate din coloanele lor de argument înainte de a utiliza funcțiile agregate MIN și MAX.
Funcția agregată SUM
Agregat Funcția SUM calculează suma totală a valorilor coloanei. Argumentul acestei funcții agregate trebuie să fie întotdeauna de tip de date numerice. Utilizarea funcției de agregare SUM este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECT SUM (Buget) „Buget total” FROM Proiect;
Acest exemplu calculează bugetele totale pentru toate proiectele. Rezultatul executării interogării:
În acest exemplu, funcția agregată grupează toate valorile bugetului proiectului și determină totalul acestora. Din acest motiv, interogarea conține o funcție implicită de grupare (ca toate interogările similare). Funcția de grupare implicită din exemplul de mai sus poate fi specificată în mod explicit, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECT SUM (Buget) „Bugetul total” FROM Project GROUP BY ();
Utilizarea parametrului DISTINCT elimină orice valori duplicate din coloană înainte de a aplica funcția SUM. De asemenea, eliminați toate valorile NULL înainte de a aplica această funcție de agregare.
Funcția agregată AVG
Agregat Funcția AVG returnează media aritmetică a tuturor valorilor dintr-o coloană. Argumentul acestei funcții agregate trebuie să fie întotdeauna de tip de date numerice. Toate valorile NULL sunt eliminate din argument înainte ca funcția AVG să fie aplicată.
Utilizarea funcției de agregare AVG este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează 133833 SELECT AVG (Buget) „Buget mediu pentru un proiect” FROM Proiect;
Aici se calculează media aritmetică a bugetului pentru toate bugetele.
Agregați funcțiile COUNT și COUNT_BIG
Agregat Funcția COUNT are două forme diferite:
COUNT (nume_coloană) COUNT (*)
Prima formă a funcției numără numărul de valori din coloana col_name. Dacă interogarea folosește cuvântul cheie DISTINCT, toate valorile coloanelor duplicat sunt eliminate înainte ca funcția COUNT să fie utilizată. Această formă a funcției COUNT nu ia în considerare valorile NULL atunci când se numără numărul de valori ale coloanei.
Utilizarea primei forme a funcției de agregare COUNT este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, COUNT (DISTINCT Job) "Jobs in Project" FROM Works_on GROUP BY ProjectNumber;
Aici este numărat numărul de poziții diferite pentru fiecare proiect. Rezultatul acestei interogări:
După cum puteți vedea din interogarea din exemplu, valorile NULL nu au fost luate în considerare de funcția COUNT. (Suma tuturor valorilor din coloana jobului este 7, nu 11 așa cum ar trebui să fie.)
A doua formă a funcției COUNT, adică. funcția COUNT (*) numără numărul de rânduri dintr-un tabel. Și dacă instrucțiunea SELECT a interogării cu funcția COUNT (*) conține o clauză WHERE cu o condiție, funcția returnează numărul de rânduri care îndeplinesc condiția specificată. Spre deosebire de prima versiune a funcției COUNT, a doua formă nu ignoră valorile NULL, deoarece această funcție operează pe șiruri, nu pe coloane. Exemplul de mai jos demonstrează utilizarea funcției COUNT (*):
UTILIZAȚI SampleDb; SELECTARE Job CA „Tipul locului de muncă”, COUNT (*) „Este nevoie de muncitori” FROM Works_on GROUP BY Job;
Aici este numărat numărul de postări din toate proiectele. Rezultatul executării interogării:
COUNT_BIG funcție aceeași funcție ca COUNT. Singura diferență dintre cele două este tipul de rezultat pe care îl returnează: COUNT_BIG returnează întotdeauna valori BIGINT, în timp ce COUNT returnează valori de date INTEGER.
Funcții agregate statistice
Următoarele funcții alcătuiesc grupul de funcții agregate statistice:
VARCalculează varianța statistică a tuturor valorilor dintr-o coloană sau expresie.
VARPCalculează varianța statistică a populației tuturor valorilor dintr-o coloană sau expresie.
STDEVCalculează abaterea standard (care este calculată ca rădăcină pătrată a varianței corespunzătoare) a tuturor valorilor dintr-o coloană sau expresie.
STDEVPCalculează abaterea standard a colecției tuturor valorilor dintr-o coloană sau expresie.
Funcții agregate definite de utilizator
Motorul de baze de date suportă, de asemenea, implementarea funcțiilor definite de utilizator. Această capacitate permite utilizatorilor să suplimenteze funcțiile agregate ale sistemului cu funcții pe care le pot implementa și instala ei înșiși. Aceste funcții reprezintă o clasă specială de funcții definite de utilizator și sunt discutate în detaliu mai târziu.
clauza HAVING
Într-o propoziție AVÂND definește o condiție care se aplică unui grup de rânduri. Astfel, această clauză are aceeași semnificație pentru grupurile de rânduri ca și clauza WHERE pentru conținutul tabelului corespunzător. Sintaxa pentru clauza HAVING este următoarea:
AVÂND stare
Aici parametrul condiție reprezintă o condiție și conține funcții sau constante agregate.
Utilizarea clauzei HAVING împreună cu funcția de agregare COUNT (*) este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează „p3” SELECT ProjectNumber FROM Works_on GROUP BY ProjectNumber HAVING COUNT (*)
În acest exemplu, sistemul folosește clauza GROUP BY pentru a grupa toate rândurile după valorile din coloana ProjectNumber. Numărul de rânduri din fiecare grup este apoi numărat și sunt selectate grupuri care conțin mai puțin de patru rânduri (trei sau mai puține).
Clauza HAVING poate fi folosită și fără funcții agregate, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Va returna „Consultant” SELECTARE Job FROM Works_on GROUP BY Job HAVING Job LIKE „К%”;
Acest exemplu grupează rândurile tabelului Works_on după titlul postului și elimină joburile care nu încep cu litera „K”.
Clauza HAVING poate fi folosită și fără clauza GROUP BY, deși aceasta nu este o practică obișnuită. În acest caz, toate rândurile tabelului sunt returnate în același grup.
clauza ORDER BY
Propoziție COMANDA PENTRU determină ordinea de sortare a rândurilor din setul de rezultate returnat de interogare. Această clauză are următoarea sintaxă:
Ordinea de sortare este specificată în parametrul col_name. Col_number este un indicator alternativ al ordinii de sortare care identifică coloanele în ordinea în care apar în lista de selectare a instrucțiunii SELECT (1 este prima coloană, 2 este a doua coloană și așa mai departe). Parametrul ASC definește o ordine crescătoare și parametrul DESC- în aval. Valoarea implicită este ASC.
Numele coloanelor din clauza ORDER BY nu trebuie să fie neapărat în lista de coloane selectate. Dar acest lucru nu se aplică interogărilor precum SELECT DISTINCT, deoarece în astfel de interogări, numele coloanelor specificate în clauza ORDER BY trebuie să apară și în lista coloanelor selectate. În plus, această clauză nu poate conține nume de coloane din tabele care nu sunt specificate în clauza FROM.
După cum puteți vedea din sintaxa clauzei ORDER BY, sortarea setului de rezultate se poate face pe mai multe coloane. Această sortare este prezentată în exemplul de mai jos:
Acest exemplu selectează numere de departamente și nume de familie și prenume ale angajaților pentru angajații ale căror numere de personal sunt mai mici de 20.000, precum și sortate după nume și prenume. Rezultatul acestei interogări:
Coloanele din clauza ORDER BY pot fi specificate nu după numele lor, ci în ordine în lista de selecție. În consecință, propoziția din exemplul de mai sus poate fi rescrisă după cum urmează:
Această modalitate alternativă de a specifica coloanele după poziția lor în loc de nume este utilizată dacă criteriul de ordonare conține o funcție de agregare. (O altă modalitate este să folosiți numele coloanelor, care apar apoi în clauza ORDER BY.) Cu toate acestea, în clauza ORDER BY, se recomandă să specificați coloanele după numele lor, mai degrabă decât după numere, pentru a facilita actualizarea interogării. dacă trebuie să adăugați sau să eliminați coloane din lista de selectare. Specificarea coloanelor din clauza ORDER BY după numerele lor este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTARE ProjectNumber, COUNT (*) „Număr de angajați” FROM Works_on GROUP BY ProjectNumber ORDER BY 2 DESC;
Aici, pentru fiecare proiect, se selectează numărul proiectului și numărul de angajați care participă la acesta, ordonând rezultatul în ordine descrescătoare după numărul de angajați.
Transact-SQL plasează valorile NULL în partea de sus a listei când sunt sortate în ordine crescătoare, iar valorile NULL la sfârșitul listei când sunt sortate în ordine descrescătoare.
Utilizarea clauzei ORDER BY pentru paginarea rezultatelor
Afișarea rezultatelor interogării pe pagina curentă poate fi fie implementată într-o aplicație personalizată, fie puteți solicita serverului de baze de date să facă acest lucru. În primul caz, toate rândurile bazei de date sunt trimise către aplicație, a cărei sarcină este să selecteze rândurile necesare și să le afișeze. În al doilea caz, numai rândurile necesare pentru pagina curentă sunt preluate și afișate din partea serverului. După cum v-ați putea aștepta, crearea de pagini pe partea serverului oferă de obicei cea mai bună performanță, deoarece doar liniile necesare pentru afișare sunt trimise clientului.
Pentru a sprijini crearea paginilor pe partea serverului, SQL Server 2012 introduce două noi clauze SELECT: OFFSET și FETCH. Aplicarea acestor două propoziții este demonstrată în exemplul de mai jos. Aici, din baza de date AdventureWorks2012 (pe care o găsiți în sursă), extrage ID-ul companiei, titlul postului și ziua de naștere a tuturor angajatelor, sortate după titlul postului în ordine crescătoare. Setul de rânduri rezultat este împărțit în pagini de 10 rânduri și este afișată o a treia pagină:
Într-o propoziție DECALAJ specifică numărul de linii de rezultat care trebuie sărit în rezultatul afișat. Această sumă este calculată după ce rândurile sunt sortate cu clauza ORDER BY. Într-o propoziție FETCH NEXT specifică numărul de WHERE și rândurile sortate de returnat. Parametrul acestei clauze poate fi o constantă, o expresie sau rezultatul unei alte interogări. Clauza FETCH NEXT este similară cu clauza FETCH MAINTI.
Scopul principal atunci când se creează pagini pe partea serverului este acela de a putea implementa formulare comune de pagină folosind variabile. Această sarcină poate fi realizată prin pachetul SQL Server.
Declarația SELECT și proprietatea IDENTITY
proprietatea IDENTITATE vă permite să determinați valorile pentru o anumită coloană a tabelului sub forma unui contor incremental automat. Coloanele cu un tip de date numerice, cum ar fi TINYINT, SMALLINT, INT și BIGINT pot avea această proprietate. Pentru o astfel de coloană de tabel, Motorul de bază de date generează automat valori secvențiale începând cu valoarea de pornire specificată. Prin urmare, proprietatea IDENTITATE poate fi utilizată pentru a genera valori numerice cu o singură cifră pentru coloana selectată.
Un tabel poate conține o singură coloană cu proprietatea IDENTITY. Proprietarul tabelului are capacitatea de a specifica valoarea inițială și creșterea, așa cum se arată în exemplul de mai jos:
UTILIZAȚI SampleDb; CREATE TABLE Product (Id INT IDENTITY (10000, 1) NOT NULL, Name NVARCHAR (30) NOT NULL, Price MONEY) INSERT INTO Product (Nume, Price) VALUES ("Produs1", 10), ("Product2", 15) , ("Produs3", 8), ("Produs4", 15), ("Produs5", 40); - Va returna 10004 SELECT IDENTITYCOL FROM Product WHERE Nume = "Product5"; - Analog cu declarația anterioară SELECT $ identity FROM Product WHERE Nume = "Product5";
Acest exemplu creează mai întâi un tabel Product care conține o coloană Id cu o proprietate IDENTITY. Valorile din coloana Id sunt create automat de sistem, începând de la 10.000 și crescând într-un pas pentru fiecare valoare ulterioară: 10.000, 10.001, 10.002 etc.
Mai multe funcții de sistem și variabile sunt asociate cu proprietatea IDENTITY. De exemplu, codul de exemplu folosește variabila de sistem $ identitate... După cum puteți vedea din rezultatele rulării acestui cod, această variabilă este referită automat la proprietatea IDENTITY. În schimb, puteți utiliza și funcția de sistem IDENTITYCOL.
Valoarea inițială și incrementul coloanei cu proprietatea IDENTITATE pot fi găsite folosind funcțiile IDENT_SEEDși IDENT_INCR respectiv. Aceste funcții se aplică după cum urmează:
UTILIZAȚI SampleDb; SELECT IDENT_SEED ("Produs"), IDENT_INCR ("Produs")
După cum sa menționat, valorile IDENTITATE sunt setate automat de către sistem. Dar utilizatorul își poate specifica în mod explicit valorile pentru anumite șiruri de caractere prin alocarea parametrului IDENTITY_INSERT valoarea ON înainte de a introduce valoarea explicită:
SETARE IDENTITATE INSERT numele tabelului
Deoarece parametrul IDENTITY_INSERT poate fi utilizat pentru a seta orice valoare, inclusiv valori duplicate, pe o coloană cu proprietatea IDENTITY, proprietatea IDENTITY de obicei nu impune unicitatea valorilor coloanei. Prin urmare, constrângerile UNIQUE sau PRIMARY KEY ar trebui aplicate pentru a impune unicitatea valorilor coloanei.
Când inserați valori într-un tabel după ce ați activat IDENTITY_INSERT, sistemul creează următoarea valoare în coloana IDENTITY, incrementând cea mai mare valoare curentă din acea coloană.
Instrucțiunea CREATE SEQUENCE
Utilizarea proprietății IDENTITY are câteva dezavantaje semnificative, dintre care cele mai semnificative sunt:
proprietatea este limitată la tabelul specificat;
noua valoare a coloanei nu poate fi obtinuta in nici un alt mod, decat prin aplicarea acesteia;
proprietatea IDENTITY poate fi specificată numai la crearea unei coloane.
Din aceste motive, SQL Server 2012 introduce secvențe care au aceeași semantică ca proprietatea IDENTITY, dar fără dezavantajele enumerate anterior. În acest context, o secvență se referă la funcționalitatea unei baze de date care vă permite să specificați valori de contor pentru diferite obiecte de bază de date, cum ar fi coloane și variabile.
Secvențele sunt create folosind instrucțiunea CREAȚI SECVENȚA... Instrucțiunea CREATE SEQUENCE este definită în standardul SQL și este suportată de alte sisteme de baze de date relaționale, cum ar fi IBM DB2 și Oracle.
Exemplul de mai jos arată cum să creați o secvență în SQL Server:
UTILIZAȚI SampleDb; CREATE SECVENCE dbo.Sequence1 AS INT START CU 1 INCREMENTARE CU 5 MINVALUE 1 MAXVALUE 256 CYCLE;
În exemplul de mai sus, valorile Sequence1 sunt generate automat de sistem, începând de la o valoare de 1 și în trepte de 5 pentru fiecare valoare succesivă. Astfel, în clauza START este indicată valoarea inițială, iar în Oferta INCREMENTARE- Etapa. (Pasul poate fi fie pozitiv, fie negativ.)
În următoarele două propoziții opționale MINVALUEși MAXVALUE specifică valoarea minimă și maximă a obiectului secvență. (Rețineți că MINVALUE trebuie să fie mai mic sau egal cu valoarea inițială, iar MAXVALUE nu poate fi mai mare decât limita superioară a tipului de date specificat pentru secvență.) Într-o propoziție CICLU indică faptul că secvența se repetă de la început atunci când valoarea maximă (sau minimă pentru o secvență cu pas negativ) este depășită. În mod implicit, această clauză este setată la NO CYCLE, ceea ce înseamnă că depășirea valorii maxime sau minime a secvenței ridică o excepție.
Caracteristica principală a secvențelor este independența lor față de tabele, adică. ele pot fi utilizate cu orice obiect de bază de date, cum ar fi coloanele de tabel sau variabile. (Această proprietate are un efect pozitiv asupra stocării și, prin urmare, asupra performanței. O anumită secvență nu trebuie să fie stocată; doar ultima ei valoare este stocată.)
Sunt create noi valori ale secvenței cu NEXT VALUE FOR expresii, a cărui aplicare este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; - Returnează 1 SELECT NEXT VALUE FOR dbo.sequence1; - Returnează 6 (pasul următor) SELECT NEXT VALUE FOR dbo.sequence1;
Puteți folosi expresia NEXT VALUE FOR pentru a atribui rezultatul unei secvențe unei variabile sau unei celule de coloană. Exemplul de mai jos ilustrează utilizarea acestei expresii pentru a atribui rezultate unei coloane:
UTILIZAȚI SampleDb; CREATE TABLE Product (Id INT NOT NULL, Name NVARCHAR (30) NOT NULL, Price MONEY) INSERT INTO Product VALUES (NEXT VALUE FOR dbo.sequence1, "Product1", 10); INSERT INTO Product VALUES (URMĂTORUL VALOARE PENTRU dbo.sequence1, „Product2”, 15); -...
Exemplul de mai sus creează mai întâi un tabel numit Produs cu patru coloane. Apoi, două instrucțiuni INSERT inserează două rânduri în acest tabel. Primele două celule ale primei coloane vor fi 11 și 16.
Exemplul de mai jos demonstrează utilizarea vizualizării catalogului sys.secvente pentru a vizualiza valoarea curentă a unei secvențe fără a o folosi:
De obicei, expresia NEXT VALUE FOR este utilizată într-o instrucțiune INSERT pentru a instrui sistemul să insereze valorile generate. Această expresie poate fi folosită și ca parte a unei interogări pe mai multe linii folosind clauza OVER.
Pentru a modifica o proprietate a unei secvențe existente, aplicați Instrucțiunea ALTER SEQUENCE... Una dintre cele mai importante utilizări pentru această declarație este opțiunea RESTART WITH, care resetează secvența specificată. Exemplul de mai jos demonstrează utilizarea instrucțiunii ALTER SEQUENCE pentru a reseta aproape toate proprietățile Sequence1:
UTILIZAȚI SampleDb; ALTER SEQUENCE dbo.sequence1 RESTART CU 100 INCREMENTARE CU 50 MINVALUE 50 MAXVALUE 200 FĂRĂ CICLU;
Secvența este ștearsă folosind instrucțiunea SECVENȚA DE CĂDERARE.
Setați operatori
Pe lângă operatorii discutați mai devreme, Transact-SQL acceptă alți trei operatori de set: UNION, INTERSECT și EXCEPT.
operator UNION
operator UNION combină rezultatele a două sau mai multe interogări într-un singur set de rezultate care include toate rândurile care aparțin tuturor interogărilor din uniune. În consecință, rezultatul unirii celor două tabele este un tabel nou care conține toate rândurile din unul sau ambele tabele originale.
Forma generală a operatorului UNION arată astfel:
select_1 UNION select_2 (select_3])...
Opțiunile select_1, select_2, ... sunt instrucțiuni SELECT care creează unirea. Dacă se utilizează parametrul ALL, sunt afișate toate rândurile, inclusiv duplicatele. În instrucțiunea UNION, parametrul ALL are aceeași semnificație ca și în SELECT, dar cu o diferență: acesta este implicit pentru un SELECT, dar trebuie specificat explicit pentru UNION.
În forma sa originală, SampleDb nu este potrivit pentru demonstrarea utilizării operatorului UNION. Prin urmare, această secțiune creează un nou tabel EmployeeEnh care este identic cu tabelul Employee existent, dar are o coloană suplimentară Oraș. Această coloană indică locul de reședință al angajaților.
Crearea tabelului EmployeeEnh ne oferă o oportunitate de a demonstra utilizarea clauzei. ÎNîn instrucțiunea SELECT. Instrucțiunea SELECT INTO efectuează două operații. Mai întâi, este creat un nou tabel cu coloanele listate în lista SELECT. Apoi, rândurile din tabelul original sunt inserate în noul tabel. Numele noului tabel este specificat în clauza INTO, iar numele tabelului sursă este specificat în clauza FROM.
Exemplul de mai jos arată crearea tabelului EmployeeEnh din tabelul Employee:
UTILIZAȚI SampleDb; SELECT * INTO EmployeeEnh FROM Employee; ALTER TABLE EmployeeEnh ADD City NCHAR (40) NULL;
În acest exemplu, instrucțiunea SELECT INTO creează tabelul EmployeeEnh, inserează toate rândurile din tabelul sursă Employee în acesta, iar apoi instrucțiunea ALTER TABLE adaugă coloana City la noul tabel. Dar coloana Oraș adăugată nu conține nicio valoare. Valorile pot fi inserate în această coloană prin Management Studio sau folosind următorul cod:
UTILIZAȚI SampleDb; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 2581; UPDATE EmployeeEnh SET City = „Moscova” WHERE Id = 9031; UPDATE EmployeeEnh SET City = "Ekaterinburg" WHERE Id = 10102; UPDATE EmployeeEnh SET City = "Sankt Petersburg" WHERE Id = 18316; UPDATE EmployeeEnh SET City = "Krasnodar" WHERE Id = 25348; UPDATE EmployeeEnh SET City = "Kazan" WHERE Id = 28559; UPDATE EmployeeEnh SET City = "Perm" WHERE Id = 29346;
Acum suntem gata să demonstrăm utilizarea declarației UNION. Exemplul de mai jos arată o interogare pentru a crea o îmbinare între tabelele EmployeeEnh și Department folosind această declarație:
UTILIZAȚI SampleDb; SELECTAȚI Orașul AS „ORAS” FROM EmployeeEnh UNION SELECTAȚI Locația FROM Departament;
Rezultatul acestei interogări:
Numai tabelele compatibile pot fi alăturate folosind declarația UNION. Prin tabele compatibile, înțelegem că ambele liste de coloane din selecție trebuie să conțină același număr de coloane, iar coloanele corespunzătoare trebuie să aibă tipuri de date compatibile. (În ceea ce privește compatibilitatea, tipurile de date INT și SMALLINT nu sunt compatibile.)
Rezultatul concatenării poate fi ordonat numai folosind clauza ORDER BY din ultima instrucțiune SELECT, așa cum se arată în exemplul de mai jos. Clauzele GROUP BY și HAVING pot fi utilizate cu instrucțiuni individuale SELECT, dar nu în cadrul uniunii în sine.
Interogarea din acest exemplu selectează angajați care fie lucrează în departamentul d1, fie care au început să lucreze la un proiect înainte de 1 ianuarie 2008.
Operatorul UNION acceptă opțiunea ALL. Această opțiune nu elimină duplicatele din setul de rezultate. Un operator OR poate fi utilizat în locul operatorului UNION dacă toate instrucțiunile SELECT unite de unul sau mai mulți operatori UNION se referă la același tabel. În acest caz, setul de instrucțiuni SELECT este înlocuit cu o singură instrucțiune SELECT cu un set de instrucțiuni OR.
Operatorii INTERSECT și EXCEPT
Alți doi operatori pentru lucrul cu seturi, INTERSECTși CU EXCEPTIA, definiți intersecția și, respectiv, diferența. Sub intersecție, în acest context, există un set de rânduri care aparțin ambelor tabele. Și diferența dintre două tabele este definită ca toate valorile care aparțin primului tabel și nu sunt prezente în al doilea. Exemplul de mai jos demonstrează utilizarea operatorului INTERSECT:
Transact-SQL nu acceptă utilizarea parametrului ALL cu operatorii INTERSECT sau EXCEPT. Utilizarea operatorului EXCEPT este prezentată în exemplul de mai jos:
Amintiți-vă că acești trei operatori de set au o prioritate de execuție diferită: INTERSECT are cea mai mare prioritate, urmată de EXCEPT și UNION are cea mai mică prioritate. Neacordarea atenției priorității execuției atunci când se utilizează mai mulți operatori de set diferiți poate duce la rezultate neașteptate.
expresii CASE
În domeniul programării aplicațiilor de baze de date, uneori este necesară modificarea prezentării datelor. De exemplu, oamenii pot fi subdivizați în funcție de clasa lor socială, folosind valorile 1, 2 și 3, desemnând bărbați, femei și, respectiv, copii. Această tehnică de programare poate reduce timpul necesar implementării programului. expresie CASE Transact-SQL vă permite să implementați cu ușurință acest tip de codificare.
Spre deosebire de majoritatea limbajelor de programare, CASE nu este o declarație, ci o expresie. Prin urmare, o expresie CASE poate fi folosită aproape oriunde Transact-SQL permite utilizarea expresiilor. Expresia CASE are două forme:
expresie CASE simplă;
expresia de căutare CASE.
Sintaxa pentru o expresie CASE simplă este următoarea:
O instrucțiune cu o expresie CASE simplă caută mai întâi în lista tuturor expresiilor din clauza CÂND prima expresie care se potrivește cu expresia_1 și apoi execută cea corespunzătoare THEN clauza... Dacă nu există o expresie potrivită în lista CÂND, atunci clauza ELSE.
Sintaxa pentru o expresie de căutare CASE este următoarea:
În acest caz, caută prima condiție de calificare și apoi execută clauza THEN corespunzătoare. Dacă niciuna dintre condiții nu îndeplinește cerințele, se execută clauza ELSE. Utilizarea expresiei de căutare CASE este prezentată în exemplul de mai jos:
UTILIZAȚI SampleDb; SELECTAȚI Nume Proiect, CAZUL CÂND Buget> 0 ȘI Buget 100000 ȘI Buget 150000 ȘI Buget
Rezultatul acestei interogări:
Acest exemplu ponderează bugetele tuturor proiectelor și afișează ponderile calculate împreună cu numele proiectelor corespunzătoare.
Exemplul de mai jos arată o altă modalitate de a utiliza o expresie CASE în care clauza WHEN conține subinterogări care fac parte din expresie:
UTILIZAȚI SampleDb; SELECTează Numele Proiectului, CAZUL CÂND p1.Buget (SELECTARE AVG (p2.Buget) FROM Proiectul p2) THEN „peste medie” END „Categoria bugetară” FROM Proiectul p1;
Rezultatul acestei interogări este următorul:
Pentru a rezuma informațiile conținute în baza de date, SQL oferă funcții agregate. O funcție agregată ia ca argument o întreagă coloană de date și returnează o singură valoare care rezumă acea coloană într-un mod specific.
De exemplu, funcția agregată AVG () ia o coloană de numere ca argument ș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 „VENIT MEDIU =”, AVG (SUMD)
SQL are șase funcții agregate care oferă diferite tipuri de rezumate (Figura 1):
- SUM () calculează suma tuturor valorilor conținute în coloană;
- AVG () calculează media dintre valorile 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 în 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 calculează impozitul 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 veniturilor tuturor locuitorilor din Zelenograd poate fi calculată folosind funcția agregată SUM:
SELECTAȚI SUMA (SUMA) FROM PERSON
Funcția de agregare poate fi folosită și pentru a calcula totalurile pentru un tabel de rezultate obținute prin unirea mai multor tabele sursă. De exemplu, puteți calcula suma totală a venitului pe care rezidenții îl primesc dintr-o sursă numită „Bursă”:
SELECTAȚI SUMA (BANI)
DIN PROFIT, HAVE_D
WHERE PROFIT.ID = HAVE_D.ID
AND PROFIT.SOURCE = „Bursă”
Funcțiile agregate MIN () și MAX () vă permit să găsiți, respectiv, cele mai mici și, respectiv, cele mai mari valori din tabel. Cu toate acestea, 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 realizat 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:
SELECTARE MIN (RDATE), MAX (RDATE)
(c) numele de familie, prenumele și patronimele primului și ultimului locuitor din listă, ordonate alfabetic:
SELECTARE MIN (FIO), MAX (FIO)
Când aplicați aceste funcții agregate, trebuie să rețineți că datele numerice sunt comparate conform regulilor aritmetice, datele sunt comparate secvenţial (valorile anterioare ale datei sunt considerate mai mici decât cele ulterioare), intervalele de timp sunt comparate în funcție de durata lor.
Când utilizați MIN () și MAX () cu date șiruri, rezultatul comparării celor 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 NUMĂRUL (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 numărate valorile neduplicate din coloană).
Funcția de agregare specială COUNT (*) contorizează 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 LIKE „%, 2 __-%”
(b) câte surse de venit are Ivan Ivanovici?
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 CONT (*) FROM PERSON WHERE ADR = "Zelenograd, 1001-45"
O modalitate de a înțelege cum sunt executate interogările agregate este să ne gândim la execuția interogărilor în două părți. În primul rând, se determină 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 rezumat.
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 venitul mediu al sursei ca procent din venitul total al locuitorului. Răspunsul este dat de operator
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 folosesc coloanele din tabelul cu rezultate al acestei interogări pentru a produce un tabel cu un singur rând cu rezultatele rezumate.
În rândul coloanelor returnate, puteți specifica o funcție de agregare în loc de orice nume de coloană. De exemplu, poate fi inclus într-o expresie care adaugă sau scade valorile a două funcții agregate:
SELECTAȚI MAX (SUMD) -MIN (SUMD) FROM PERSON
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.
În plus, funcțiile agregate și numele de coloane obișnuite nu pot fi utilizate în lista coloanelor returnate în același timp, deoarece nu are rost în acest sens, de exemplu:
SELECTAȚI FIO, SUMA (SUMD) FROM PERSON
Aici, primul element al listei indică 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 din listă cere DBMS să obțină o valoare de rezultat, care este suma valorilor din coloana SUMD. Aceste două linii directoare 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.
