Clauza GROUP BY (ca și clauza HAVING) este necesară doar în interogările care utilizează funcții agregate.

Clauza GROUP BY este utilizată pentru a afișa o valoare agregată pe unul sau mai multe rânduri pe care le returnează instrucțiunea SELECT, pe baza uneia sau mai multor coloane neagregate numite coloane de grupare. De exemplu, mai jos este o interogare care determină câți oameni am angajat în fiecare an între 1999 și 2004.

SELECT hire_year, COUNT(emp_id) AS "nbr_emps" FROM employee WHERE status="ACTIVE" AND hire_year INTRE 1999 SI 2004 GROUP BY hire.year;

Rezultatele vor fi următoarele:

hire_year nbr_emps

Interogările care utilizează funcții agregate pot furniza multe tipuri de informații rezumative. Cele mai comune funcții agregate includ următoarele.

Returnează media tuturor valorilor care nu sunt goale din coloanele specificate.

AVG DISTINCT

Returnează media tuturor valorilor unice care nu sunt goale în coloanele specificate.

Returnează numărul tuturor valorilor care nu sunt goale din coloanele specificate.

COUNT DISTINCT

Returnează numărul tuturor valorilor unice non-nule din coloanele specificate.

COUNT (*)

Se calculează numărul total de înregistrări din tabel.

Returnează cea mai mare dintre valorile care nu sunt goale din coloanele specificate.

Returnează cea mai mică dintre valorile care nu sunt goale din coloanele specificate.

Suma tuturor valorilor care nu sunt goale din coloanele specificate.

SUM DISTINCT

Suma tuturor valorilor unice care nu sunt goale din coloanele specificate.

Unele interogări care folosesc agregate returnează o singură valoare. Agregatele care returnează o singură valoare sunt numite agregate scalare. Agregatele scalare nu necesită o clauză GROUP BY.

De exemplu:

SELECT AVG(preț) FROM titluri // Rezultat: 14. 77

Interogările care returnează atât valori obișnuite ale coloanei, cât și valori din funcțiile agregate sunt numite agregate vectoriale. Agregatele vectoriale folosesc o clauză GROUP BY și returnează unul sau mai multe rânduri. Există câteva reguli de urmat atunci când utilizați clauza GROUP BY.

• Plasează corect clauza GROUP BY în raport cu alte clauze - după clauza WHERE și înaintea clauzei ORDER BY.
• În clauza GROUP BY, toate coloanele trebuie să fie neagregate.
• Nu utilizați aliasuri de coloană în clauza GROUP BY, deși aliasurile de tabel sunt acceptabile.

De exemplu, să presupunem că doriți să obțineți suma totală pentru mai multe achiziții din tabelul OrderJDetails, care arată astfel.

Putem detalia agregate folosind mai multe coloane pentru grupare. Luați în considerare următoarea interogare, care va returna prețul mediu al produselor noastre, grupate mai întâi după nume și apoi după dimensiune.

În plus, clauza GROUP IN Y acceptă câteva clauze suplimentare foarte importante.

GROUP BY[(ROLLUP CUBE)] ([coloana_pentru_grupare [, ...]]) [, list_of_sets_for_grouping]

Valorile setului de rezultate agregate sunt grupate în funcție de una sau mai multe coloane de grupare. (Clauza GROUP BY (coloana_pentru_grupare [, ...]) fără clauzele ROLLUP și CUBE este cea mai simplă și mai des folosită formă a clauzei GROUP BY.

Pentru fiecare set de coloane de grupare, subtotalurile sunt create sub forma unui set de rezultate ierarhice. În acest caz, subtotalurile și totalurile generale sunt adăugate la seturile de rezultate într-o ordine ierarhică. Clauza ROLLUP returnează un rând pentru fiecare coloană de grupare, unde coloanele de grupare au valori nule și arată valorile agregate subtotal și total (pe care le vom ilustra în scurt timp).

Subtotalurile și totalurile încrucișate sunt create pentru toate coloanele de grupare. În esență, oferta CUBE vă permite să obțineți rapid seturi de rezultate multidimensionale din tabele relaționale standard, fără prea mult efort de programare. Clauza CUBE este utilă în special atunci când lucrați cu cantități mari de date. Ca și clauza ROLLUP, clauza CUBE creează subtotaluri pentru gruparea coloanelor, dar include și rânduri subtotale pentru toate combinațiile posibile de grupare coloane specificate în interogare.

GRUPARE PRIN GRUPARE SEȚURI [(ROLLUP CUBE)) ([coloană_pentru_grupare [, ...)]) [, list_of_sets_for_grouping)

Vă permite să utilizați grupuri agregate în mai multe seturi diferite de coloane de grupare într-o singură interogare. Această proprietate este utilă în special dacă doriți să preluați doar o parte din setul de rezultate agregate. Clauza GROUPING SETS vă permite, de asemenea, să selectați coloanele de grupare de comparat, în timp ce clauza CUBE returnează toate coloanele de grupare, iar ROLLUP returnează porțiunea ierarhică a coloanelor de grupare. După cum arată sintaxa, standardul ANSI permite și clauzei GROUPING SETS să fie combinate cu clauzele ROLLUP și CUBE.

Fiecare tip de clauză GROUP BY returnează propriul set de valori agregate și, în cazul ROLLUP și CUBE, totaluri și subtotaluri.

Principiul clauzelor ROLLUP CUBE și GROUPING SETS va fi mult mai clar dacă va fi explicat cu un exemplu. În exemplul următor, solicităm date reprezentând numărul de comenzi (sa!es_comenzi) pe an (data_comandă) și trimestru (trimerul comenzii).

Adăugarea coloanelor de grupare la o interogare produce un set de rezultate mai detaliat (cu mai multe subtotaluri). Vom modifica exemplul de mai sus pentru a adăuga o regiune la interogare (cu toate acestea, deoarece numărul de coloane crește, ne vom uita doar la primul și al doilea trimestru).

SELECT comanda_an AS year, order_quarter AS trimestru, regiune, COUNT (*) AS comenzi FROM order_details WHERE order_year IN (2003, 2004) AND order_quarter IN (1.2) AND region IN ("SUA", "CANADA") GROUP BY ROLLUP (anul_comanda) , sfert_comanda) ORDER BY an_comanda, trimestru_comanda;

Rezultatul va fi următorul.

an trimestru comenzi regiune

Clauza GROUP BY CUBE este utilă atunci când se efectuează analize multidimensionale asupra datelor agregate. Ca și în cazul clauzei GROUP BY ROLLUP, subtotalurile sunt returnate. Cu toate acestea, spre deosebire de GROUP BY ROLLUP, subtotalurile sunt returnate pentru toate combinațiile de coloane de grupare a interogărilor. (După cum veți vedea, această clauză poate crește numărul de rânduri din setul de date rezultat.)

În exemplul următor, solicităm date rezumative cu privire la numărul de comenzi (comenzi de vânzare) în funcție de an (data de comandă) și trimestru (trimestru de comandă).

SELECT comandă_an AS year, order_quarter AS trimestru, COUNT () AS order FROM order.details WHERE comandă.an IN (2003, 2004) GROUP BY id (order_year, order_quarter) ORDER BY order_year, order_quarter

Clauza GROUP BY GROUPING SETS vă permite să agregați mai multe grupuri într-o singură interogare. Pentru fiecare set de grupuri, interogarea returnează subtotaluri, unde coloana de grupare este desemnată ca NULL. În timp ce clauzele CUBE și ROLLUP plasează subtotaluri predefinite într-un set de rezultate, clauza GROUPING SETS vă permite să controlați ce subtotaluri sunt incluse în interogare. Clauza GROUPING SETS nu returnează un total general.

Folosind un exemplu similar cu exemplele de mai sus pentru clauzele ROLLUP și CUBE, de data aceasta vom selecta un subtotal pe an și trimestru, precum și separat pe an.

Un alt mod de a gândi clauza GROUPING SETS este ca o clauză UNION ALL aplicată mai multor interogări GROUP BY care fac referire la diferite părți ale acelorași date. Puteți forța sistemul să adauge subtotaluri la un GROUPING SET pur și simplu adăugând clauza ROLLUP sau CUBE sub care doriți să fie calculate subtotalurile.

Clauzele GROUPING SETS pot fi combinate pentru a genera un număr mare de grupări într-o declarație scurtă. Clauzele GROUPING SETS combinate produc produsul de grupare încrucișată al tuturor grupărilor tuturor sețiilor specificate în lista GROUPING SET. Clauzele combinate GROUPING SETS sunt compatibile cu clauzele ROLLUP și CUBE. Clauzele combinate GROUPING SETS, fiind produsul grupării încrucișate, vor crea un număr foarte mare de grupări finale chiar și dintr-un număr mic de combinate. De exemplu, dacă extindem 3.3 pentru a include referințe la seturi îmbinate de grupări, obținem 3.4.

În exemplul dat în 3.4, clauzele combinate GROUPING SETS creează un număr mare de grupări finale. Vă puteți imagina cât de mare ar fi setul de rezultate dacă clauzele combinate GROUPING SETS ar conține un număr mare de grupări! Cu toate acestea, informațiile obținute pot fi foarte valoroase și greu de obținut altfel.

Ultima actualizare: 19.07.2017

T-SQL utilizează instrucțiunile GROUP BY și HAVING pentru a grupa datele, folosind următoarea sintaxă formală:

SELECTAȚI coloanele din tabel

GROUP BY

Clauza GROUP BY determină modul în care vor fi grupate rândurile.

De exemplu, să grupăm produsele după producător

SELECTARE Producător, COUNT(*) AS ModelsCount FROM Products GROUP BY Producător

Prima coloană din instrucțiunea SELECT - Manufacturer reprezintă numele grupului, iar a doua coloană - ModelsCount reprezintă rezultatul funcției Count, care calculează numărul de rânduri din grup.

Merită să luați în considerare faptul că orice coloană care este utilizată într-o instrucțiune SELECT (fără a număra coloanele care stochează rezultatul funcțiilor agregate) trebuie specificată după clauza GROUP BY. Deci, de exemplu, în cazul de mai sus, coloana Producător este specificată în ambele clauze SELECT și GROUP BY.

Și dacă instrucțiunea SELECT selectează pe una sau mai multe coloane și folosește și funcții agregate, atunci trebuie să utilizați clauza GROUP BY. Astfel, următorul exemplu nu va funcționa deoarece nu conține o expresie de grupare:

SELECTează producător, COUNT(*) AS ModelsCount FROM Produse

Un alt exemplu, să adăugăm o grupare după numărul de produse:

SELECT Manufacturer, ProductCount, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer, ProductCount

Clauza GROUP BY poate fi grupată pe mai multe coloane.

Dacă coloana în care grupați conține o valoare NULL, rândurile cu valoarea NULL vor forma un grup separat.

Rețineți că clauza GROUP BY trebuie să apară după clauza WHERE, dar înaintea clauzei ORDER BY:

SELECTARE Producător, COUNT(*) AS ModelsCount FROM Produse WHERE Preț > 30000 GROUP BY Producător ORDER BY ModelsCount DESC

Filtrare de grup. AVÂND

Operator AVÂND determină ce grupuri vor fi incluse în rezultatul de ieșire, adică filtrează grupuri.

Utilizarea HAVING este în multe privințe similară cu utilizarea WHERE. Numai WHERE este folosit pentru a filtra rândurile, HAVING este folosit pentru a filtra grupuri.

De exemplu, să găsim toate grupurile de produse după producător pentru care este definit mai mult de un model:

SELECTARE Producător, COUNT(*) AS ModelsCount FROM Products GROUP BY Manufacturer HAVING COUNT(*) > 1

În acest caz, într-o singură comandă putem folosi expresiile WHERE și HAVING:

SELECTARE Producător, COUNT(*) AS ModelsCount FROM Products WHERE Preț * ProductCount > 80000 GROUP BY Manufacturer HAVING COUNT(*) > 1

Adică, în acest caz, rândurile sunt mai întâi filtrate: sunt selectate acele produse al căror cost total este mai mare de 80.000, apoi produsele selectate sunt grupate în funcție de producător. Și apoi grupurile în sine sunt filtrate - sunt selectate acele grupuri care conțin mai mult de 1 model.

Dacă este necesară sortarea, atunci expresia ORDER BY vine după expresia HAVING:

SELECTARE Producător, COUNT(*) AS Modele, SUM(ProductCount) AS Unități FROM Products WHERE Preț * ProductCount > 80000 GROUP BY Producător HAVING SUM(ProductCount) > 2 ORDER BY Units DESC

În acest caz, gruparea este pe producător, iar numărul de modele pentru fiecare producător (Modele) și numărul total de produse pentru toate aceste modele (Unități) sunt de asemenea selectate. La final, grupurile sunt sortate după numărul de produse în ordine descrescătoare.

Am arătat cum puteți calcula masa unui anumit produs furnizat de furnizori. Să presupunem că acum doriți să calculați masa totală a fiecăruia dintre produsele furnizate în prezent de furnizori. Acest lucru se poate face cu ușurință folosind o propoziție

SELECTARE PR, SUM(Cant.) FROM Supplies GROUP BY PR;

Rezultatul este prezentat în Fig. 2.3,a.

Orez. 2.3. Ilustrații pentru expresia GROUP BY

Clauza GROUP BY face ca tabelul specificat în FROM să fie rearanjat în grupuri, fiecare dintre ele având aceeași valoare în coloana specificată în GROUP BY. În exemplul luat în considerare, rândurile tabelului de consumabile sunt grupate astfel încât un grup să conțină toate rândurile pentru produsul cu PR = 1, celălalt pentru produsul cu PR = 2 etc. (vezi Fig. 2.3.b). În continuare, clauza SELECT este aplicată fiecărui grup. Fiecare expresie din această frază trebuie să aibă un singur sens pentru grup, adică. poate fi fie valoarea coloanei specificate în GROUP BY, fie o expresie aritmetică care implică acea valoare, fie o constantă, fie una dintre funcțiile SQL care operează pe toate valorile unei coloane dintr-un grup și le reduce valori la o singură valoare (de exemplu, o sumă).

Rețineți că clauza GROUP BY nu implică ORDER BY. Pentru a asigura ordonarea de către PR a rezultatului exemplului luat în considerare (Fig. 2.3,c), ar trebui să se facă o cerere

SELECTAȚI PR, SUMA(CANTITATE) FROM Supplies GROUP BY PR ORDER BY PR;

În cele din urmă, rețineți că rândurile unui tabel pot fi grupate după orice combinație a coloanelor sale. Da, la cerere

SELECTAȚI T, BL, NUMĂRARE(BL) DIN GRUPA COMANDĂ BY T, BL;

puteți afla codurile și numărul de porții de preparate comandate de oaspeții pensiunii (32 persoane) pentru fiecare dintre mesele din ziua următoare:

Dacă o interogare folosește clauze WHERE și GROUP BY, rândurile care nu îndeplinesc clauza WHERE sunt excluse înainte ca gruparea să fie efectuată.

De exemplu, pentru fiecare produs, emite codul acestuia și volumul total de posibile livrări, ținând cont de incapacitatea temporară a furnizorului cu PS = 2:

SELECTARE PR, SUM(Cant.) FROM Consumabile WHERE PS 2 GROUP BY PR;

Rezultatul prezentat în Fig. 2.3d diferă de rezultatul (Fig. 2.3a) al unei cereri similare pentru toți furnizorii în volumul livrărilor de produse cu codurile 15, 5 și 6.

Acum puteți crea predicate în termeni de relații SQL special definite. Puteți căuta valori într-un anumit interval (BETWEEN) sau într-un set numeric (IN), sau puteți căuta valori de caractere care se potrivesc cu textul din parametri (LIKE).

4.4. GROUP BY și funcții de agregare SQL

Rezultatul unei interogări poate fi o valoare generalizată de grup de câmpuri, la fel ca valoarea unui singur câmp. Acest lucru se realizează folosind funcții de agregare SQL standard, care sunt enumerate mai jos:

Cu excepția cazului special COUNT(*), fiecare dintre aceste funcții operează pe o colecție de valori într-o coloană a unui tabel și produce o singură valoare.

Argumentul tuturor funcțiilor cu excepția COUNT(*) poate fi precedat de cuvântul cheie DISTINCT, indicând faptul că valorile coloanei duplicate

trebuie exclus înainte ca funcția să poată fi aplicată. Funcția specială COUNT(*) este folosită pentru a număra toate rândurile din tabel fără excepție (inclusiv duplicatele).

Funcțiile agregate sunt utilizate în mod similar cu numele câmpurilor dintr-o clauză de interogare SELECT, cu o singură excepție: ele iau numele câmpurilor ca argumente. Numai câmpurile numerice pot fi utilizate cu SUM și AVG.

Atât câmpurile numerice, cât și cele de caractere pot fi utilizate cu COUNT, MAX și MIN. Când sunt utilizate cu câmpuri de caractere, MAX și MIN le vor traduce într-un echivalent de cod ASCII, care ar trebui să comunice că MIN va însemna prima și MAX ultima valoare în ordine alfabetică.

Pentru a găsi SUMA tuturor salariilor în tabelul DEPARTMENT_EMPLOYEE (Fig. 2.3), trebuie să introduceți următoarea interogare:

DIN Departament_ Angajati;

Și pe ecran vom vedea rezultatul: 46800 (tabelul va avea o coloană numită SUM).

Calcularea valorii salariului mediu este, de asemenea, simplă:

SELECTARE AVG ((Salariu))

DIN Departament_ Angajati;

Funcția COUNT este puțin diferită de toată lumea. Numărează numărul de valori dintr-o coloană dată sau numărul de rânduri dintr-un tabel. Când numără valorile unei coloane, este folosit cu DISTINCT pentru a număra numărul de valori unice dintr-un anumit câmp.

Tabelul are opt rânduri care conțin valori salariale diferite.

Rețineți că ultimele trei exemple iau în considerare și informații despre angajații disponibilizați.

Următoarea propoziție vă permite să determinați numărul de diviziuni pe

DISTINCT urmat de numele câmpului căruia i se aplică, cuprins între paranteze, cu COUNT aplicat coloanelor individuale.

SELECTARE NUMĂR (*)

DIN Departament_ Angajati;

COUNT(*) numără fiecare rând din tabel.

DISTINCT nu se aplică cu COUNT (*).

Să presupunem că tabelul PAYMENT_LIST (Fig. 2.4) are încă o coloană care stochează valoarea deducerilor efectuate (câmpul Deducere) pentru fiecare rând a extrasului. Apoi, dacă sunteți interesat de întreaga sumă, atunci conținutul coloanelor Sumă și Deducere trebuie adăugat împreună.

Dacă sunteți interesat de suma maximă, ținând cont de deduceri, conținută în declarație, atunci acest lucru se poate face folosind următoarea propoziție:

SELECTARE MAX (Suma + Deducere)

FROM Pay_sheet;

Pentru fiecare rând din tabel, această interogare va adăuga valoarea câmpului Sumă cu valoarea câmpului Deducere și va selecta cea mai mare valoare pe care o găsește.

GRUPARE DUPA CLAUZA (rearanjare, comanda)

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.

De exemplu, să presupunem că doriți să determinați câți angajați sunt în fiecare departament (răspunsul este prezentat în Figura 4.21):

SELECT Department_ID, COUNT (DISTINCT Department_ID) AS Număr de_angajați

Clauza GROUP BY lasă numai valori de coloană unice, sortate implicit în ordine crescătoare. Sub acest aspect, clauza GROUP BY diferă de clauza ORDER BY prin faptul că aceasta din urmă, deși sortează înregistrările în ordine crescătoare, nu înlătură valorile duplicat. În exemplul dat, interogarea grupează rândurile tabelului după valorile coloanei Department_ID (după numerele departamentului). Rândurile cu aceleași numere de departament sunt mai întâi combinate în grupuri, dar este afișat un singur rând pentru fiecare grup. A doua coloană afișează numărul de rânduri din fiecare grup, adică. numărul de angajați din fiecare departament.

Valoarea unui câmp căruia i se aplică GROUP BY are, prin definiție, o singură valoare per grup de ieșire, la fel ca o funcție de agregare. Rezultatul este compatibilitatea care permite combinarea agregatelor și câmpurilor în acest fel.

Să fie, de exemplu, tabelul PAYMENT_LIST să arate ca Fig. 4.22 și dorim să stabilim suma maximă plătită pe statul de plată fiecărui angajat.

Ca rezultat obținem.

Orez. 4.23. Funcția agregată cu AS

Gruparea se poate face după mai multe atribute:

DIN Foaia 1

GROUP BY Employee_id, Data;

Rezultat:

Orez. 4.24. Gruparea după mai multe atribute

Dacă este necesar să se limiteze numărul de grupuri obținute după GROUP BY, atunci folosind clauza HAVING, puteți implementa acest lucru.

4.5. Folosind expresia HAVING

Clauza HAVING joacă același rol pentru grupuri ca și clauza WHERE pentru rânduri: este folosită pentru a exclude grupuri, la fel cum este folosită WHERE pentru a exclude rânduri. Această frază este inclusă în propoziție

numai dacă există o clauză GROUP BY, iar expresia din HAVING trebuie să ia o singură valoare pentru grup.

De exemplu, să presupunem că trebuie să arătăm compoziția cantitativă a tuturor departamentelor (Fig. 2.3), excluzând departamentul numărul 3.

SELECT Department_ID, COUNT (DISTINCT Department_ID) AS Număr de _angajați

Ultimul element atunci când se evaluează o expresie de tabel este secțiunea HAVING (dacă este prezentă). Sintaxa pentru această secțiune este următoarea:

::=

AVÂND