Condiția de căutare pentru această secțiune specifică o condiție pentru un grup de rânduri din tabelul grupat. În mod formal, o clauză HAVING poate apărea și într-o expresie de tabel care nu conține un GROUP BY. În acest caz, se presupune că rezultatul calculului secțiunilor anterioare este un tabel grupat format dintr-un singur grup fără coloanele de grupare selectate.

Condiția de căutare a clauzei HAVING urmează aceleași reguli de sintaxă ca și condiția de căutare a clauzei WHERE și poate include aceleași predicate.

Cu toate acestea, există restricții sintactice speciale cu privire la modul de utilizare a condiției de căutare pentru specificațiile coloanei de tabel din clauza FROM a unei expresii de tabel date. Aceste restricții rezultă din faptul că condiția de căutare a clauzei HAVING specifică o condiție pentru un întreg grup, nu pentru șiruri individuale.

Prin urmare, în expresiile aritmetice ale predicatelor incluse în condiția de selecție a clauzei HAVING, puteți utiliza direct numai specificațiile coloanelor specificate ca grupare a coloanelor în clauza GROUP BY. Restul coloanelor pot fi specificate numai în cadrul specificațiilor funcțiilor agregate COUNT, SUM, AVG, MIN și MAX, care în acest caz calculează o anumită valoare agregată pentru întregul grup de rânduri. Situația este similară cu subinterogările incluse în predicatele condiției de selecție a secțiunii HAVING: dacă subinterogarea folosește caracteristica grupului curent, atunci poate fi specificată numai referindu-se la coloanele de grupare.

Permiteți o interogare a formularului (ca tabel de bază, vezi Fig. 4.22):

SELECTează Sr_id, Data, MAX ((Suma))

DE la Declarație1

GROUP BY Id_sotr, Data;

trebuie clarificat astfel încât să fie afișate numai plățile care depășesc 1000.

Cu toate acestea, standardul nu are voie să utilizeze o funcție agregată într-o clauză WHERE (cu excepția cazului în care utilizați o subinterogare, descrisă mai târziu), deoarece predicatele sunt evaluate în termeni de un singur rând, iar funcțiile agregate sunt evaluate în termeni de grup de rânduri .

Următoarea propoziție ar fi incorectă:

SELECTează Sr_id, Data, MAX (Suma)

DE la Declarație1

UNDE MAX ((Suma))> 1000 GRUPARE DE ID_sotr, Data;

Propoziția corectă ar fi:

SELECTează Sr_id, Data, MAX ((Suma))

Gruparea datelor vă permite să împărțiți toate datele în seturi logice, făcând posibilă efectuarea calculelor statistice separat în fiecare grup.

1. Crearea grupurilor (GROUP BY)

Grupurile sunt create folosind o propunere A SE GRUPA CU operator SELECTAȚI... Să vedem un exemplu.

SELECTAȚI Produs, SUM (Cantitate) CA Număr_produs din Sumproduct GRUP PE PRODUS

Cu această solicitare, am extras informații despre numărul de produse vândute în fiecare lună. Operator SELECTAȚI ordinele de afișare a două coloane Produs- numele produsului și Număr_produs- câmpul calculat pe care l-am creat pentru a afișa numărul de produse vândute (formula pentru câmpul SUMĂ (Cantitate)). Oferi A SE GRUPA CU spune SGBD-ului să grupeze datele pe coloane Produs... De asemenea, merită menționat acest lucru A SE GRUPA CU ar trebui să vină după propoziție UNDE si inainte COMANDA DE.

2. Grupuri de filtrare (AVÂND)

La fel cum am filtrat rândurile din tabel, putem filtra după datele grupate. Pentru aceasta în SQL există un operator AVÂND... Să luăm exemplul anterior și să adăugăm filtrarea grupului.

SELECTAȚI Produs, SUM (Cantitate) CA Număr_produs din Sumproduct GRUP PE PRODUS CU SUMĂ (Cantitate)> 4000

Vedem că după ce a fost calculat numărul de bunuri vândute în contextul fiecărui produs, SGBD a „tăiat” acele produse din care s-au vândut mai puțin de 4.000.

După cum puteți vedea, operatorul AVÂND foarte asemănător cu operatorul UNDE cu toate acestea, au o diferență semnificativă între ele: UNDE filtrează datele înainte de a fi grupate și AVÂND- efectuează filtrarea după grupare. Astfel, liniile care au fost înlăturate prin sentință UNDE NU va fi inclus în grup. Deci operatorii UNDEși AVÂND poate fi folosit într-o singură propoziție. Să luăm în considerare un exemplu:

SELECTAȚI Produs, SUMĂ (Cantitate) CA Număr_produs din Sumproduct UNDE Produs<>"Schiuri lungi" GRUP PE PRODUS CU SUMĂ (Cantitate)> 4000

Am adăugat operatorul la exemplul anterior UNDE unde ați indicat mărfurile Schiuri lungi, care la rândul său a influențat gruparea de către operator AVÂND... Drept urmare, vedem că produsul Schiuri lungi nu a fost inclus în lista grupurilor cu un număr de produse vândute care depășește 4000 buc.

3. Gruparea și sortarea

Ca și în cazul selecției normale de date, putem sorta grupurile după grupare după operator AVÂND... Pentru a face acest lucru, putem folosi operatorul deja familiar COMANDA DE... În această situație, aplicarea sa este similară cu exemplele anterioare. De exemplu:

SELECTAȚI Produs, SUMĂ (Cantitate) CA Număr_produs din Sumproduct GRUP PE PRODUS CU SUMĂ (Cantitate)> 3000 COMANDĂ SUMĂ (Cantitate)

sau doar indicați numărul câmpului în ordinea în care dorim să sortăm:

SELECTAȚI Produs, SUMĂ (Cantitate) CA Număr_produs din Sumproduct GRUP PE PRODUS CU SUMĂ (Cantitate)> 3000 COMANDĂ CU 2

Vedem că pentru a sorta rezultatele rezumate, trebuie doar să scriem propoziții cu COMANDA DE după operator AVÂND... Cu toate acestea, există o singură avertisment. Accesați SGBD nu acceptă sortarea grupurilor după aliasuri de coloană, adică, în exemplul nostru, pentru a sorta valorile, nu putem scrie COMANDĂ DE Număr_produs.

În acest articol, vă voi spune despre cum sunt grupate datele, despre cum să utilizați corect gruparea și de a avea interioare interogări SQL folosind exemplul mai multor interogări.

Majoritatea informațiilor din bazele de date sunt stocate în formă detaliată. Cu toate acestea, este adesea necesar să se obțină rezumate. De exemplu, aflați numărul total de comentarii ale utilizatorilor sau poate numărul de bunuri din depozite. Există o mulțime de sarcini similare. Prin urmare, limbajul SQL specific pentru astfel de cazuri oferă grupului prin și având construcții, care permit, respectiv, gruparea și filtrarea grupurilor de date rezultate.

Cu toate acestea, aplicația lor provoacă o mulțime de probleme autorilor începători ai creațiilor software. Ei nu interpretează corect rezultatele obținute și mecanismul de grupare a datelor în sine. Prin urmare, să ne dăm seama în practică ce și cum se întâmplă.

De exemplu, voi lua în considerare doar un singur tabel. Motivul este simplu, acești operatori sunt deja aplicați la selectarea datelor obținute (după combinarea rândurilor de tabel și filtrarea acestora). Așadar, adăugarea de clauze unde și join nu schimbă esența.

Să ne imaginăm un exemplu abstract. Să presupunem că aveți un tabel rezumat al utilizatorilor forumului. Să-i spunem userstat și arată așa. Un punct important, credem că un utilizator nu poate fi decât într-un singur grup.

user_name - nume de utilizator

forum_group - numele grupului

mess_count - numărul de mesaje

is_have_social_profile - există un link către o pagină de pe o rețea socială din profilul forumului

După cum puteți vedea, tabelul este simplu și puteți calcula multe lucruri singur pe calculator. Cu toate acestea, acesta este doar un exemplu și există doar 10 intrări. În bazele de date reale, înregistrările pot fi măsurate în mii. Deci, să trecem la întrebări.

Grupare pură cu grupare după

Să ne imaginăm că trebuie să aflăm valoarea fiecărui grup, și anume ratingul mediu al utilizatorilor din grup și numărul total de postări rămase în forum.

În primul rând, o mică descriere verbală pentru a facilita înțelegerea interogării SQL. Deci, trebuie să găsiți valorile calculate după grupul de forumuri. În consecință, trebuie să împărțiți toate aceste zece linii în trei grupuri diferite: admin, moder, utilizator. Pentru a face acest lucru, trebuie să adăugați gruparea după valorile câmpului forum_group la sfârșitul interogării. Și adăugați, de asemenea, expresii calculate pentru a selecta folosind așa-numitele funcții de agregare.

Specificați câmpurile și coloanele calculate selectați forum_group, avg (raiting) ca avg_raiting, sum (mess_count) ca total_mess_count - Specificați tabelul din userstat - Specificați gruparea după grupul de câmpuri după forum_group

Vă rugăm să rețineți că, după ce ați utilizat grupul prin clauză în interogarea dvs., puteți utiliza numai acele câmpuri din selectare care au fost specificate după grup, fără a utiliza funcții agregate. Restul câmpurilor trebuie specificate în cadrul funcțiilor agregate.

De asemenea, am folosit două funcții agregate. AVG - Calculează media. Și SUM - calculează suma.

1. În primul rând, toate rândurile din tabelul original au fost împărțite în trei grupuri în funcție de valorile câmpului forum_group. De exemplu, în grupul de administratori erau trei utilizatori. Există, de asemenea, 3 linii în interiorul modului. Și în interiorul grupului de utilizatori erau 4 linii (patru utilizatori).

2. Apoi, au fost aplicate funcții agregate pentru fiecare grup. De exemplu, pentru grupul de administratori, evaluarea medie a fost calculată ca (2 + 5 + 5) / 3 = 4. Numărul de postări a fost calculat ca (10 + 15 + 25) = 50.

După cum puteți vedea, nimic complicat. Cu toate acestea, am aplicat o singură condiție de grupare și nu am aplicat filtrarea de grup. Deci, să trecem la următorul exemplu.

Gruparea cu grupul după și filtrarea grupurilor cu având

Acum, să analizăm un exemplu mai complex de grupare a datelor. Să presupunem că trebuie să evaluăm eficacitatea acțiunilor pentru a atrage utilizatorii către activități sociale. Pentru a o simplifica, aflați câți utilizatori din grupuri au lăsat linkuri către profilurile lor și câți mesaje de poștă ignorate și așa mai departe. Cu toate acestea, în viața reală pot exista multe astfel de grupuri, deci trebuie să filtrăm acele grupuri care pot fi neglijate (de exemplu, prea puțini oameni nu au lăsat o legătură; de ce s-a aglomerat raportul complet). În exemplul meu, acestea sunt grupuri cu un singur utilizator.

În primul rând, să descriem verbal ce trebuie făcut în interogarea SQL. Trebuie să separăm toate rândurile din tabelul userstat original în conformitate cu următoarele criterii: numele grupului și prezența unui profil social. În consecință, este necesar să grupați datele tabelului în câmpurile forum_group și is_have_social_profile. Cu toate acestea, nu ne interesează grupurile cu o singură persoană. Prin urmare, astfel de grupuri trebuie filtrate.

Notă: Merită să știm că această problemă ar putea fi rezolvată prin gruparea unui singur câmp. Dacă folosiți carcasă. Cu toate acestea, în cadrul acestui exemplu, sunt prezentate posibilitățile de grupare.

Aș dori, de asemenea, să clarific imediat un punct important. Puteți filtra folosind având numai atunci când utilizați funcții agregate, nu pe câmpuri individuale. Cu alte cuvinte, aceasta nu este o clauză unde, ci un filtru de grupuri de rânduri, nu înregistrări individuale. Deși condițiile din interior sunt stabilite într-un mod similar folosind "sau" și "și".

Așa va arăta interogarea SQL

Specificați câmpurile și coloanele calculate selectați forum_group, is_have_social_profile, count (*) ca total - Specificați un tabel din userstat - Specificați gruparea după câmpuri grupați după forum_group, is_have_social_profile - Specificați un filtru de grup cu număr (*)> 1

Rețineți că câmpurile după gruparea prin clauză sunt separate prin virgule. Câmpurile sunt specificate în selectare în același mod ca în exemplul anterior. Am folosit și funcția de agregare de numărare, care calculează numărul de rânduri din grupuri.

Iată rezultatul:

Să aruncăm o privire la modul în care a apărut acest rezultat.

1. Inițial, au fost primite 6 grupuri. Fiecare dintre grupuri după forum_group a fost împărțit în două subgrupuri de valorile câmpului is_have_social_profile. Cu alte cuvinte, grupuri: ,,,,,.

Notă: Apropo, grupurile nu s-ar fi dovedit neapărat a fi 6. Deci, de exemplu, dacă toți administratorii ar completa profilul, ar exista 5 grupuri, deoarece câmpul is_have_social_profile ar avea o singură valoare pentru utilizatorii grup de administratori.

2. Apoi condiția de filtrare a fost aplicată fiecărui grup. Prin urmare, au fost excluse următoarele grupuri: ,,. Deoarece în cadrul fiecărui astfel de grup exista doar un rând din tabelul original.

3. După aceea, au fost calculate datele necesare și s-a obținut rezultatul.

După cum puteți vedea, nu este nimic dificil de utilizat.

Merită să știm că, în funcție de baza de date, capacitățile acestor constructe pot diferi. De exemplu, pot exista mai multe funcții agregate sau puteți specifica coloane calculate mai degrabă decât câmpuri individuale ca grupare. Aceste informații ar trebui deja analizate în caietul de sarcini.

Acum, știți cum să grupați datele cu grupul după și cum să filtrați grupurile folosind având.

Traduce o interogare SELECT într-un „plan de interogare” intern, care poate diferi chiar și pentru interogări sintactice identice și de un SGBD specific.

Instrucțiunea SELECT constă din mai multe clauze (secțiuni):

• SELECT definește lista coloanelor returnate (atât existente, cât și calculate), numele acestora, restricții privind unicitatea rândurilor din setul returnat, restricții privind numărul de rânduri din setul returnat;
• FROM specifică o expresie de tabel care definește setul de date subiacent pentru a aplica operațiuni definite în alte clauze de instrucțiuni;
• WHERE stabilește limita pe rândurile expresiei tabelului din clauza FROM;
• GROUP BY concatenează rânduri care au aceeași proprietate utilizând funcții de agregare
• AVÂND selectează dintre grupurile specificate de parametrul GROUP BY
• ORDER BY specifică criteriile de sortare pentru rânduri; liniile sortate sunt transmise apelantului.

Structura operatorului

Instrucțiunea SELECT are următoarea structură:

SELECT [DISTINCT | DISTINCTROW | ALL] select_expression, ... FROM table_references [WHERE where_definition] [GROUP BY (unsigned_integer | col_name | formula)] [AVÂND where_definition] [ORDER BY (unsigned_integer | col_name | formula) [ASC | DESC], ...]

Parametrii operatorului

COMANDA DE

ORDER BY - parametru operator opțional (opțional) SELECTAȚIși UNION, care înseamnă operatori SELECTAȚI, UNION returnează un set de rânduri sortate după valorile uneia sau mai multor coloane. Poate fi aplicat atât coloanelor numerice, cât și șirurilor. În acest din urmă caz, sortarea va avea loc alfabetic.

Utilizarea clauzei ORDER BY este singura modalitate de sortare a setului de rânduri rezultat. Fără această clauză, SGBD poate returna rânduri în orice ordine. Dacă este necesară comanda, ORDER BY trebuie să fie prezent în SELECT, UNION.

Sortarea poate fi efectuată atât în ​​ordine crescătoare, cât și descendentă a valorilor.

• Parametrul ASC (implicit) setează ordinea de sortare în ordine crescătoare, de la cel mai mic la cel mai mare.
• Parametrul DESC setează ordinea de sortare în ordine descrescătoare, de la cel mai mare la cel mai mic.

Exemple de

SELECT * DIN T;

SELECTEAZĂ C1 DIN T;

Pentru interogarea tabelului T

SELECT * DIN T;

va returna toate coloanele tuturor rândurilor din tabelul dat. Pentru același tabel, interogarea

SELECTEAZĂ C1 DIN T;

va returna valorile coloanei C1 a tuturor rândurilor din tabel - în termeni de algebră relațională proiecție... Pentru același tabel, interogarea

va returna valorile tuturor coloanelor din toate rândurile din tabel pentru care valoarea câmpului C1 este „1” - în termeni de algebră relațională, putem spune că a fost executat probă deoarece cuvântul cheie WHERE este prezent. Ultima cerere

SELECT * DIN T ORDINĂ DE C1 DESC;

va returna aceleași rânduri ca primul, dar rezultatul va fi sortat în ordine inversă (Z-A) datorită utilizării cuvântului cheie ORDER BY cu C1 ca câmp de sortare. Această interogare nu conține cuvântul cheie WHERE, deci va returna totul din tabel. Mai multe elemente ORDER BY pot fi specificate separate prin virgule [de ex. COMANDĂ DE C1 ASC, C2 DESC] pentru o sortare mai precisă.

Selectează toate rândurile în care numele_coloană este una dintre valorile listate value1, value2, ...

Returnează o listă cu ID-urile departamentului cu vânzări care depășesc 1.000 USD la 1 ianuarie 2000, împreună cu totalurile vânzărilor pentru ziua respectivă:

Restricționarea rândurilor returnate

Conform ISO SQL: 2003, setul de date returnat poate fi limitat cu:

• introducere funcțiile ferestrei la instrucțiunea SELECT

Funcția fereastră ROW_NUMBER ()

Există diverse funcțiile ferestrei... ROW_NUMBER () OVER poate fi folosit pentru simplă limitare numărul de rânduri returnate. De exemplu, pentru a returna cel mult zece linii:

ROW_NUMBER poate fi nedeterminist: dacă cheie nu este unic, de fiecare dată când executați o interogare, este posibil să atribuiți numere diferite liniilor în care cheie chibrituri. Cand cheie este unic, fiecare linie va primi întotdeauna un număr unic de linie.

Funcția de fereastră RANK ()

Funcția RANK () OVER funcționează la fel ca ROW_NUMBER, dar poate reveni mai mult de n linii în anumite condiții. De exemplu, pentru a obține primii 10 tineri:

Acest cod poate returna mai mult de 10 linii. De exemplu, dacă există două persoane cu aceeași vârstă, acesta va returna 11 rânduri.

Sintaxă non-standard

Nu toate DBMS acceptă funcțiile de fereastră de mai sus. În același timp, mulți au sintaxă non-standard pentru rezolvarea acelorași probleme. Mai jos sunt opțiunile simplă limitare mostre pentru diverse SGBD: