Ultima actualizare: 19.07.2017

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

SELECTAȚI coloanele din tabel

A SE GRUPA CU

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 grupa după 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

Grupuri de filtrare. 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.

Are în arsenalul său multe instrumente puternice pentru manipularea datelor stocate sub formă de tabele.

Fără îndoială, abilitatea de a grupa datele atunci când le prelevează după un anumit criteriu este unul dintre aceste instrumente. HAVING, împreună cu operatorul WHERE, vă permite să determinați condițiile de selectare a datelor care au fost deja grupate într-un fel.

HAVING SQL parametru: descriere

În primul rând, este de remarcat faptul că acest parametru este opțional și este utilizat exclusiv împreună cu parametrul GROUP BY. După cum vă amintiți, GROUP BY este folosit atunci când funcțiile agregate sunt utilizate în SELECT, iar rezultatele calculelor lor trebuie să fie obținute pentru anumite grupuri. Dacă WHERE vă permite să setați condiții de selecție înainte ca datele să fie grupate, atunci HAVING conține condiții legate de date direct în grupurile în sine. Pentru o mai bună înțelegere, să ne uităm la exemplul cu circuitul prezentat în figura de mai jos.

Acesta este un exemplu excelent care oferă HAVING SQL o descriere. Este dat un tabel cu o listă de nume de produse, companii care le produc și alte câmpuri. În interogarea din colțul din dreapta sus, încercăm să obținem informații despre câte articole de produs produce fiecare companie, iar în rezultat dorim să afișăm doar acele companii care produc mai mult de 2 articole. Parametrul GROUP BY a format trei grupuri corespunzatoare denumirilor de firme, pentru fiecare dintre acestea fiind calculat numarul de produse (randuri). Dar parametrul HAVING, cu condiția sa, a tăiat un grup din proba rezultată, deoarece nu a îndeplinit condiția. Ca urmare, obținem două grupe corespunzătoare companiilor cu 5 și 3 cantități de producție.

S-ar putea întreba de ce folosiți HAVING când SQL are WHERE. Dacă am folosi WHERE, s-ar uita la numărul total de rânduri din tabel, nu pe grupuri, iar condiția nu ar avea sens în acest caz. Cu toate acestea, destul de des ele coexistă perfect într-o singură cerere.

În exemplul de mai sus, putem vedea cum datele sunt selectate mai întâi după numele angajaților specificate în parametrul WHERE, iar apoi rezultatul grupat în GROUP BY este supus unei verificări suplimentare asupra sumei salariului pentru fiecare angajat.

Parametrul SQL HAVING: exemple, sintaxă

Să ne uităm la câteva caracteristici ale sintaxei HAVING SQL. Descrierea acestui parametru este destul de simplă. În primul rând, după cum sa menționat deja, este utilizat exclusiv împreună cu parametrul GROUP BY și este specificat imediat după acesta și înainte de ORDER BY, dacă există unul în cerere. Acest lucru este de înțeles, deoarece HAVING definește condiții pentru datele deja grupate. În al doilea rând, numai funcțiile și câmpurile agregate specificate în parametrul GROUP BY pot fi utilizate în condiția acestui parametru. Toate condițiile din acest parametru sunt specificate exact în același mod ca și în cazul UNDE.

Concluzie

După cum puteți vedea, nu este nimic complicat în acest operator. Din punct de vedere semantic, este folosit în același mod ca WHERE. Este important de înțeles că WHERE este utilizat cu privire la toate datele selectate, iar HAVING este utilizat numai cu privire la grupurile definite în parametrul GROUP BY. Am prezentat o descriere cuprinzătoare a HAVING SQL, care este suficientă pentru a lucra cu încredere.

Cum pot afla numărul de modele de PC produse de un anumit furnizor? Cum se determină prețul mediu al calculatoarelor cu aceleași caracteristici tehnice? Acestea și multe alte întrebări legate de unele informații statistice pot fi răspuns folosind funcții finale (agregate).. Standardul oferă următoarele funcții agregate:

Toate aceste funcții returnează o singură valoare. În același timp, funcțiile COUNT, MINȘi MAX aplicabil oricărui tip de date, în timp ce SUMĂȘi AVG sunt utilizate numai pentru câmpurile numerice. Diferența între funcție NUMARA(*)Și NUMARA(<имя поля>) este că al doilea nu ia în considerare valorile NULL la calcul.

Exemplu. Găsiți prețul minim și maxim pentru computerele personale:

Exemplu. Găsiți numărul disponibil de computere produse de producătorul A:

Exemplu. Dacă ne interesează numărul de modele diferite produse de producătorul A, atunci interogarea poate fi formulată după cum urmează (folosind faptul că în tabelul Produs fiecare model este înregistrat o dată):

Exemplu. Găsiți numărul de modele diferite disponibile produse de producătorul A. Interogarea este similară cu cea anterioară, în care era necesar să se determine numărul total de modele produse de producătorul A. Aici trebuie să găsiți și numărul de modele diferite în masa PC (adică cele disponibile pentru vânzare).

Pentru a se asigura că numai valorile unice sunt utilizate la obținerea indicatorilor statistici, când argumentul funcţiilor agregate poate fi folosit parametru DISTINCT. O alta parametrul ALL este implicit și presupune că toate valorile returnate în coloană sunt numărate. Operator,

Dacă trebuie să obținem numărul de modele de PC produse toata lumea producător, va trebui să utilizați Clauza GROUP BY, urmând sintactic după clauze WHERE.

Clauza GROUP BY

Clauza GROUP BY folosit pentru a defini grupuri de șiruri de ieșire cărora li se pot aplica funcții agregate (COUNT, MIN, MAX, AVG și SUM). Dacă această clauză lipsește și sunt utilizate funcții de agregare, atunci toate coloanele cu nume menționate în SELECTAȚI, ar trebui incluse în funcții agregate, iar aceste funcții vor fi aplicate întregului set de rânduri care satisfac predicatul de interogare. În caz contrar, toate coloanele din lista SELECT nu este inclusîn agregat trebuie specificate funcţiile în clauza GROUP BY. Ca urmare, toate rândurile de interogare de ieșire sunt împărțite în grupuri caracterizate prin aceleași combinații de valori în aceste coloane. După aceasta, funcțiile agregate vor fi aplicate fiecărui grup. Vă rugăm să rețineți că pentru GROUP BY toate valorile NULL sunt tratate ca fiind egale, de exemplu. la gruparea după un câmp care conține valori NULL, toate aceste rânduri vor intra într-un singur grup.
Dacă dacă există o clauză GROUP BY, în clauza SELECT fără funcții agregate, atunci interogarea va returna pur și simplu un rând din fiecare grup. Această caracteristică, împreună cu cuvântul cheie DISTINCT, poate fi utilizată pentru a elimina rândurile duplicate dintr-un set de rezultate.
Să ne uităm la un exemplu simplu:

SELECT model, COUNT(model) AS Cantitate_model, AVG(preț) AS Avg_price DE LA PC GROUP BY model;

În această solicitare, pentru fiecare model de PC se determină numărul și costul mediu al acestora. Toate rândurile cu aceeași valoare a modelului formează un grup, iar rezultatul SELECT calculează numărul de valori și prețurile medii pentru fiecare grup. Rezultatul interogării va fi următorul tabel:

model	Cantitate_model	Avg_price
1121	3	850.0
1232	4	425.0
1233	3	843.33333333333337
1260	1	350.0

Dacă SELECT ar avea o coloană de dată, atunci ar fi posibil să se calculeze acești indicatori pentru fiecare dată specifică. Pentru a face acest lucru, trebuie să adăugați data ca coloană de grupare, iar apoi funcțiile agregate vor fi calculate pentru fiecare combinație de valori (model-data).

Sunt mai multe specifice reguli pentru îndeplinirea funcţiilor agregate:

• Dacă în urma cererii nu au primit rânduri(sau mai mult de un rând pentru un anumit grup), atunci nu există date sursă pentru calcularea vreuneia dintre funcțiile agregate. În acest caz, rezultatul funcțiilor COUNT va fi zero, iar rezultatul tuturor celorlalte funcții va fi NULL.
• Argument functie de agregat nu poate conține în sine funcții agregate(funcție din funcție). Acestea. într-o singură interogare este imposibil, să zicem, să se obțină maximul de valori medii.
• Rezultatul executării funcției COUNT este întreg(ÎNTREG). Alte funcții agregate moștenesc tipurile de date ale valorilor pe care le procesează.
• Dacă funcția SUM produce un rezultat care este mai mare decât valoarea maximă a tipului de date utilizat, eroare.

Deci, dacă cererea nu conține clauze GROUP BY, Acea funcții agregate inclus în clauza SELECT, sunt executate pe toate rândurile de interogare rezultate. Dacă cererea conţine Clauza GROUP BY, fiecare set de rânduri care are aceleași valori ale unei coloane sau ale unui grup de coloane specificate în Clauza GROUP BY, alcătuiește un grup și funcții agregate sunt efectuate pentru fiecare grupă separat.

AVÂND oferta

Dacă clauza WHERE definește un predicat pentru filtrarea rândurilor, apoi AVÂND oferta se aplică după grupare pentru a defini un predicat similar care filtrează grupurile după valori funcții agregate. Această clauză este necesară pentru a valida valorile care se obțin folosind functie de agregat nu din rândurile individuale ale sursei de înregistrare definite în clauza FROM, și de la grupuri de astfel de linii. Prin urmare, un astfel de control nu poate fi inclus în clauza WHERE.

În articolul anterior ne-am uitat. Acolo am scris că această construcție vă permite să selectați grupuri separate și pentru fiecare grup să calculați funcțiile specificate ulterior SELECTAȚI. A AVÂND permite, în funcție de rezultatele executării funcțiilor, să se filtreze rândurile inutile din grupuri. Să ne uităm la asta mai detaliat.

Să ne amintim de problema noastră anterioară, în care am calculat prețul mediu al laptelui pentru un anumit lanț de supermarketuri. Să nu ne uităm doar la prețul mediu, ci să enumeram doar acele lanțuri de supermarketuri unde preț mediu sub 38.

Pentru această filtrare bazată pe rezultatele executării funcției de agregare, folosim în comanda SQL HAVING:

SELECTAȚI `shop_id`, AVG(`price`) FROM `table` GROUP BY `shop_id` AVG AVG(`price`)< 38

Drept urmare, în loc de 4 vom avea doar linii 3 :

shop_id	AVG(`preț`)
1	37.5
2	36.0
3	37.0

Dacă desene A SE GRUPA CU nu va fi atunci AVÂND nu se va aplica unui anumit grup, ci întregului eșantion. Aceasta înseamnă că dacă condiția AVÂND va fi executat, nu va avea niciun efect. Și dacă nu este executat, atunci nu va fi un singur rând rezultat.

Clauza HAVING este folosită în combinație cu clauza GROUP BY. Poate fi folosit într-o instrucțiune SELECT pentru a filtra înregistrările returnate de clauza GROUP BY.

Sintaxa clauzei HAVING

funcţie_agregată poate fi o funcție precum SUM, COUNT, MIN sau MAX.

Exemplu de utilizare a funcției SUM
De exemplu, puteți utiliza funcția SUM pentru a căuta numele departamentului și valoarea vânzărilor (pentru departamentele relevante). Oferta HAVING poate selecta doar acele departamente ale căror vânzări sunt mai mari de 1000 USD.

SELECTARE departament, SUM(vânzări) AS "Vânzări totale" FROM order_details GROUP BY departament HAVING SUM(sales) > 1000 ;

Exemplu de utilizare a funcției COUNT
De exemplu, puteți utiliza funcția COUNT pentru a prelua numele departamentului și numărul de angajați (din departamentul relevant) care au câștigat mai mult de 25.000 USD pe an. Propunerea HAVING va selecta doar acele departamente în care există mai mult de 10 astfel de angajați.

Exemplu de utilizare a funcției MIN
De exemplu, puteți utiliza funcția MIN pentru a returna numele departamentului și venitul minim pentru acel departament. Propunerea HAVING va returna numai acele departamente ale căror venituri încep de la 35.000 USD.

SELECTARE departament, MIN(salariu) AS „Cel mai mic salariu” FROM angajați GRUP PE departament HAVING MIN(salariu) = 35000 ;

Exemplu de utilizare a funcției MAX
De exemplu, puteți utiliza și funcția pentru a prelua numele departamentului și venitul maxim al departamentului. Propunerea HAVING va returna numai acele departamente al căror venit maxim este mai mic de 50.000 USD.

SELECTAȚI departamentul, MAX(salariu) CA „Cel mai mare salariu” FROM angajații GRUP DUPĂ departament HAVING MAX(salariu)< 50000 ;