SQL - Lecția 8. Gruparea înregistrărilor și funcția COUNT().
Să ne amintim ce mesaje și în ce subiecte avem. Pentru a face acest lucru, puteți utiliza interogarea obișnuită:
Dacă trebuie doar să aflăm câte mesaje sunt pe forum. Pentru a face acest lucru, puteți utiliza funcția încorporată NUMARA(). Această funcție numără numărul de rânduri. În plus, dacă * este folosit ca argument pentru această funcție, atunci toate rândurile tabelului sunt numărate. Și dacă un nume de coloană este specificat ca argument, atunci sunt numărate numai acele rânduri care au o valoare în coloana specificată.
În exemplul nostru, ambele argumente vor da același rezultat, deoarece toate coloanele din tabel NU sunt NULL. Să scriem o interogare folosind coloana id_topic ca argument:
SELECTAȚI COUNT(id_topic) FROM postări;
Deci, există 4 mesaje în subiectele noastre. Dar dacă vrem să știm câte postări sunt în fiecare subiect. Pentru a face acest lucru, va trebui să ne grupăm mesajele după subiect și să calculăm numărul de mesaje pentru fiecare grup. Pentru a grupa în SQL, utilizați operatorul A SE GRUPA CU. Solicitarea noastră va arăta acum astfel:
SELECTează id_topic, COUNT(id_topic) FROM postări GROUP BY id_topic;
Operator A SE GRUPA CU spune SGBD să grupeze datele după coloana id_topic (adică, fiecare subiect este un grup separat) și să numere numărul de rânduri pentru fiecare grup:
Ei bine, in topicul cu id=1 avem 3 mesaje, iar cu id=4 - unul. Apropo, dacă valorile lipsă ar fi posibile în câmpul id_topic, atunci astfel de rânduri ar fi combinate într-un grup separat cu valoarea NULL.
Să presupunem că ne interesează doar acele grupuri care au mai mult de două mesaje. Într-o interogare normală, vom specifica condiția folosind operatorul UNDE, dar acest operator poate lucra doar cu șiruri, iar pentru grupuri aceleași funcții sunt îndeplinite de operator AVÂND:
SELECTează id_topic, COUNT(id_topic) FROM postări GROUP BY id_topic HAVING COUNT(id_topic) > 2;
Ca rezultat avem:
În lecția 4 ne-am uitat la ce condiții pot fi stabilite de către operator UNDE, aceleasi conditii pot fi specificate de catre operator AVÂND, trebuie doar să-ți amintești asta UNDE filtrează șiruri și AVÂND- grupuri.
Așa că astăzi am învățat cum să creăm grupuri și cum să numărăm numărul de rânduri dintr-un tabel și în grupuri. În general, împreună cu operatorul A SE GRUPA CU Puteți folosi alte funcții încorporate, dar le vom studia mai târziu.
Să învățăm să rezumam. Nu, acestea nu sunt rezultatele studiului SQL, ci rezultatele valorilor coloanelor tabelelor bazei de date. Funcțiile agregate SQL operează pe valorile unei coloane pentru a produce o singură valoare rezultată. Cele mai frecvent utilizate funcții de agregare SQL sunt SUM, MIN, MAX, AVG și COUNT. Este necesar să se facă distincția între două cazuri de utilizare a funcțiilor agregate. În primul rând, funcțiile agregate sunt utilizate pe cont propriu și returnează o singură valoare rezultată. În al doilea rând, funcțiile de agregare sunt utilizate cu clauza SQL GROUP BY, adică gruparea după câmpuri (coloane) pentru a obține valorile rezultate în fiecare grup. Să luăm în considerare mai întâi cazurile de utilizare a funcțiilor agregate fără grupare.
Funcția SQL SUM
Funcția SQL SUM returnează suma valorilor dintr-o coloană a tabelului bazei de date. Poate fi aplicat numai coloanelor ale căror valori sunt numere. Interogările SQL pentru a obține suma rezultată încep astfel:
SELECTAȚI SUMA (COLUMN_NAME)...
Această expresie este urmată de FROM (TABLE_NAME) și apoi poate fi specificată o condiție folosind clauza WHERE. În plus, numele coloanei poate fi precedat de DISTINCT, ceea ce înseamnă că vor fi numărate numai valorile unice. În mod implicit, toate valorile sunt luate în considerare (pentru aceasta puteți specifica în mod specific nu DISTINCT, ci TOATE, dar cuvântul TOATE nu este necesar).
Exemplul 1. Există o bază de date a companiei cu date despre diviziile și angajații săi. Tabelul Personal are și o coloană cu date despre salariile angajaților. Selecția din tabel arată astfel (pentru a mări imaginea, faceți clic pe ea cu butonul stâng al mouse-ului):
Pentru a obține suma tuturor salariilor, utilizați următoarea interogare:
SELECTAȚI SUMA (Salariu) FROM Personal
Această interogare va returna valoarea 287664.63.
Si acum . În exerciții începem deja să complicăm sarcinile, apropiindu-le de cele întâlnite în practică.
Funcția SQL MIN
Funcția SQL MIN operează și pe coloane ale căror valori sunt numere și returnează minimul tuturor valorilor din coloană. Această funcție are o sintaxă similară cu cea a funcției SUM.
Exemplul 3. Baza de date și tabelul sunt aceleași ca în exemplul 1.
Trebuie să aflăm salariul minim pentru angajații departamentului numărul 42. Pentru a face acest lucru, scrieți următoarea solicitare:
Interogarea va returna valoarea 10505.90.
Și din nou exercițiu pentru auto-rezolvare. În acest exercițiu și în alte câteva exerciții, veți avea nevoie nu numai de tabelul Staff, ci și de tabelul Org, care conține date despre diviziile companiei:
Exemplul 4. Tabelul Org este adăugat la tabelul Staff, care conține date despre departamentele companiei. Tipăriți numărul minim de ani lucrați de un angajat într-un departament situat în Boston.
Funcția SQL MAX
Funcția SQL MAX funcționează în mod similar și are o sintaxă similară, care este utilizată atunci când trebuie să determinați valoarea maximă dintre toate valorile dintr-o coloană.
Exemplul 5.
Trebuie să aflați salariul maxim al angajaților departamentului numărul 42. Pentru a face acest lucru, scrieți următoarea solicitare:
Interogarea va returna valoarea 18352.80
Este timpul exerciții pentru rezolvare independentă.
Exemplul 6. Lucrăm din nou cu două mese - Staff și Org. Afișează numele departamentului și valoarea maximă a comisionului primit de un angajat din departamentul aparținând grupului de departamente (Divizia) Est. Utilizare JOIN (aderarea la mese) .
Funcția SQL AVG
Ceea ce se spune cu privire la sintaxa pentru funcțiile descrise anterior este valabil și pentru funcția SQL AVG. Această funcție returnează media tuturor valorilor dintr-o coloană.
Exemplul 7. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.
Să presupunem că doriți să aflați vechimea medie a angajaților din departamentul numărul 42. Pentru a face acest lucru, scrieți următoarea interogare:
Rezultatul va fi 6,33
Exemplul 8. Lucrăm cu o singură masă - Personal. Afișați salariul mediu al angajaților cu 4 până la 6 ani de experiență.
Funcția SQL COUNT
Funcția SQL COUNT returnează numărul de înregistrări dintr-un tabel de bază de date. Dacă specificați SELECT COUNT(COLUMN_NAME) ... în interogare, rezultatul va fi numărul de înregistrări fără a lua în considerare acele înregistrări în care valoarea coloanei este NULL (nedefinită). Dacă utilizați un asterisc ca argument și începeți o interogare SELECT COUNT(*) ..., rezultatul va fi numărul tuturor înregistrărilor (rândurilor) din tabel.
Exemplul 9. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.
Vrei să știi numărul tuturor angajaților care primesc comisioane. Numărul de angajați ale căror valori ale coloanei Comm nu sunt NULL va fi returnat de următoarea interogare:
SELECTARE NUMĂR (Comm) FROM Staff
Rezultatul va fi 11.
Exemplul 10. Baza de date și tabelul sunt aceleași ca în exemplele anterioare.
Dacă doriți să aflați numărul total de înregistrări din tabel, atunci utilizați o interogare cu un asterisc ca argument pentru funcția COUNT:
SELECTAȚI NUMĂRUL (*) DIN Personal
Rezultatul va fi 17.
În urmatoarele exercițiu pentru soluție independentă va trebui să utilizați o subinterogare.
Exemplul 11. Lucrăm cu o singură masă - Personal. Afișați numărul de angajați din departamentul de planificare (Plains).
Funcții de agregare cu SQL GROUP BY
Acum să ne uităm la utilizarea funcțiilor agregate împreună cu instrucțiunea SQL GROUP BY. Instrucțiunea SQL GROUP BY este utilizată pentru a grupa valorile rezultate pe coloane într-un tabel al bazei de date. Site-ul are o lecție dedicată separat acestui operator .
Exemplul 12. Există o bază de date a portalului de reclame. Are un tabel de anunțuri care conține date despre anunțurile trimise pentru săptămână. Coloana Categorie conține date despre categorii mari de anunțuri (de exemplu, Imobiliare), iar coloana Piese conține date despre părți mai mici incluse în categorii (de exemplu, părțile Apartamente și Case de vară fac parte din categoria Imobiliare). Coloana Unități conține date despre numărul de reclame trimise, iar coloana Bani conține date despre suma de bani primită pentru trimiterea reclamelor.
| Categorie | Parte | Unități | Bani |
| Transport | Mașini | 110 | 17600 |
| Imobiliare | Apartamente | 89 | 18690 |
| Imobiliare | Dachas | 57 | 11970 |
| Transport | Motociclete | 131 | 20960 |
| Materiale de construcție | Scânduri | 68 | 7140 |
| Inginerie Electrică | televizoare | 127 | 8255 |
| Inginerie Electrică | Frigidere | 137 | 8905 |
| Materiale de construcție | Regips | 112 | 11760 |
| Timp liber | Cărți | 96 | 6240 |
| Imobiliare | Acasă | 47 | 9870 |
| Timp liber | Muzică | 117 | 7605 |
| Timp liber | Jocuri | 41 | 2665 |
Folosind instrucțiunea SQL GROUP BY, găsiți suma de bani câștigată prin postarea de anunțuri în fiecare categorie. Scriem următoarea cerere:
SELECTARE Categoria, SUMA (Bani) CA Bani FROM Anunțuri GROUP BY Category
Exemplul 13. Baza de date și tabelul sunt aceleași ca în exemplul anterior.
Folosind instrucțiunea SQL GROUP BY, aflați care parte din fiecare categorie a avut cele mai multe listări. Scriem următoarea cerere:
SELECT Category, Part, MAX (Unități) AS Maximum FROM Ads GROUP BY Category
Rezultatul va fi următorul tabel:
Valorile totale și individuale pot fi obținute într-un singur tabel combinarea rezultatelor interogării folosind operatorul UNION .
Baze de date relaționale și limbaj SQL
Am o cerere de genul:
SELECT i.*, COUNT(*) AS valute, SUM(ig.cantity) AS total, SUM(g.price * ig.cantity) AS preț, c.briefly AS cname FROM invoice AS i, invoice_goods AS ig, good g LEFT JOIN currency c ON (c.id = g.currency) WHERE ig.invoice_id = i.id AND g.id = ig.good_id GROUP BY g.currency ORDER BY i.date DESC;
acestea. este selectată o listă de comenzi, în care sunt calculate costurile totale ale mărfurilor în diferite valute (valuta este setată pentru produs, coloana cname din rezultat este numele monedei)
trebuie să obțineți numărul de înregistrări cu același i.id în coloana rezultat al valutelor, cu toate acestea, experimentele cu parametrii COUNT() nu au condus la nimic - returnează întotdeauna 1
Întrebare: Este posibil să obțineți valoarea adevărată în coloana valutelor? Acestea. dacă mărfurile sunt comandate cu prețuri în 3 valute diferite, valute=3 ?
Cu toate acestea, MySQL își ia prea multe libertăți în ceea ce privește SQL. De exemplu, ce înseamnă i.* în contextul acestei selecții? Toate coloanele din tabelul de facturi? Deoarece nu li se aplică nicio funcție de grup, ar fi bine dacă ar fi listate în GROUP BY, altfel principiul grupării rândurilor nu este complet clar. Dacă trebuie să primiți toate bunurile pentru toate comenzile în funcție de monedă, acesta este un lucru, dacă trebuie să primiți toate mărfurile grupate după monedă pentru fiecare comandă, acest lucru este complet diferit.
Pe baza selecției dvs., putem presupune următoarea structură de date:
Tabelul facturii:
tabel invoice_goods:
Tabelul mărfurilor:
Tabel valutar:
Ce va returna selecția dvs. actuală? În teorie, va returna N-rânduri pentru fiecare comandă pentru fiecare monedă în care această comandă conține mărfuri. Dar din cauza faptului că nimic altceva decât g.currency nu este specificat în grup prin, acest lucru nu este evident :), mai mult, coloana c.briefly contribuie și la formarea implicită a grupurilor. Ceea ce avem ca rezultat, pentru fiecare combinație unică de i.*, g.currency și c.briefly, se va forma un grup la liniile cărora li se vor aplica funcțiile SUM și COUNT. Faptul că, ca urmare a jocului cu parametrul COUNT ați obținut întotdeauna 1 înseamnă că a existat o singură înregistrare în grupul rezultat (adică grupurile nu sunt formate așa cum ați putea solicita, puteți descrie cerințele mai detaliat?). Din întrebarea dvs. nu este clar ce ați dori să știți - câte valute diferite au fost implicate în comandă sau câte comenzi au fost într-o anumită monedă? În primul caz, sunt posibile mai multe opțiuni, totul depinde de capacitățile MySQL, în al doilea caz, trebuie să scrieți expresia selectată în mod diferit.
Cu toate acestea, MySQL își ia prea multe libertăți în ceea ce privește SQL. De exemplu, ce înseamnă i.* în contextul acestei selectări? Toate coloanele din tabelul de facturi?
Da exact. Dar asta nu joacă un rol important, pentru că... Nu există coloane utile printre ele în acest caz. Fie i.* să fie i.id . Pentru a fi concret.
Ce va returna selecția dvs. actuală? În teorie, va returna N-rânduri pentru fiecare comandă pentru fiecare monedă în care această comandă conține mărfuri. Dar din cauza faptului că group by nu specifică altceva decât g.currency, acest lucru nu este evident :),
Exact.
Va returna următoarele (în acest exemplu, din i selectez doar id și nu toate coloanele):
| id | monede | total | Preț | cname |
|---|---|---|---|---|
| 33 | 1 | 1.00 | 198.00 | B.F. |
| 33 | 1 | 4.00 | 1548.04 | FRECA |
Mai mult, coloana c.briefly contribuie și la formarea implicită a grupurilor.
Cum? Tabelele sunt unite prin c.id=g.currency și grupate după g.currency .
Faptul că, ca urmare a jocului cu parametrul COUNT, ai primit întotdeauna 1 înseamnă că a existat o singură înregistrare în grupul rezultat
Nu, grupul a fost construit din 1înregistrări. Din câte înțeleg acest lucru, COUNT() returnează 1 din acest motiv (la urma urmei, coloanele care sunt diferite în grup (deși, cu excepția coloanei valută) sunt create de funcții agregate).
(adică grupurile nu sunt formate așa cum ați putea solicita, puteți descrie cerințele mai detaliat?).
Se formează grupuri după cum este necesar, fiecare grup -- Acest costul total al mărfurilor în fiecare monedă. Totuși, pe lângă asta, eu trebuie sa calculezi cat la fel elemente în acest grup.
Din întrebarea dvs. nu este clar ce ați dori să știți - câte valute diferite au fost implicate în comandă sau câte comenzi au fost într-o anumită monedă?
Da, am făcut niște bani. Doar primul.
dmig[dosar]
Prin participarea „implicita” la formarea unui grup, vreau să spun că, dacă coloana nu este specificată în GROUP BY și, în același timp, NU este un argument pentru funcția de grup, atunci rezultatul selectării va fi identic cu ce ar fi dacă acea coloană AR FI specificată în GROUP BY. Selectarea dvs. și selectarea de mai jos vor produce exact același rezultat (nu acordați atenție îmbinărilor, doar le-am adus într-un singur format de înregistrare):
Selectați i.id id, count(*) valute, sum(ig.quantity) total, SUM(g.price * ig.quantity) preț, c.briefly cname FROM invoice mă alătur invoice_goods ig on (ig.invoice_id = i. id) unește good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) grupează după i.id, c.pe scurt
Se pare că în fiecare rând al eșantionului rezultat există o singură monedă (dacă ar fi diferită, atunci ar fi două rânduri). Despre ce număr de elemente vorbim în acest caz? Despre articolele comandate? Atunci selecția dvs. este absolut corectă, doar pentru această monedă, există un singur articol în această ordine.
Să ne uităm la schema de date:
- Există multe articole (linii) într-o singură comandă, nu?
- Fiecare articol este un produs din directorul de mărfuri, nu?
- Fiecare produs are o monedă specifică (și doar una), asta rezultă din c.id = g.currency, nu?
Câte valute sunt în ordine? Există tot atâtea puncte în el cu DIFERITE valute.
Adăugarea g.price * ig.quantity are sens numai pentru puncte într-o singură monedă;) (deși se pot adăuga și kilometri cu ore :) Deci, ce nu ți se potrivește!? Spuneți că aveți nevoie de câte valute diferite au fost implicate în comandă
și în acest caz, a face acest lucru în cadrul aceleiași selectări fără tot felul de trucuri (pe care MySQL cel mai probabil nu le va face) nu va funcționa;(
Din păcate, nu sunt un expert MySQL. În Oracle puteți face acest lucru cu o singură selecție, dar vă va ajuta acest sfat? Cu greu;)
# Există multe articole (linii) într-o singură comandă, nu?
# Fiecare articol este un produs din directorul de mărfuri, nu?
# Fiecare produs are o monedă specifică (și doar una), aceasta rezultă din c.id = g.currency, nu?
Asa de.
O singură comandă: o înregistrare în tabelul de facturi, corespunde cu n(>0) înregistrări în invoice_goods, fiecare dintre acestea corespunde unei înregistrări în tabelul de mărfuri, înregistrarea „valută” în fiecare dintre care, la rândul său, corespunde Prima înregistrare în tabelul valutar ( LEFT JOIN - în cazul editării directorului valutar cu mâini strâmbe - tabelele precum MyISAM nu acceptă chei străine).
Câte valute sunt în ordine? Există tot atâtea puncte în el cu DIFERITE valute.
Da exact.
Adăugarea g.price * ig.quantity are sens numai pentru puncte într-o singură monedă;) (deși se pot adăuga și kilometri cu ore :)
Acesta este motivul pentru care gruparea se face după id-ul monedei (g.currency).
În Oracle puteți face acest lucru cu o singură selecție, dar vă va ajuta acest sfat?
M.b.
Am vorbit puțin cu Oracle și sunt familiarizat cu pl/sql.
Opțiunea 1.
Selectați a.*, numărați(*) peste (partiționare după a.id) valute din (selectați id i.id, sum(ig.cantity) total, SUM(g.price * ig.cantity) preț, c.scurt cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) grupează după i.id, c.pe scurt)a
Aceasta folosește așa-numitul functie analitica. Cu 99% probabilitate NU funcționează în MySQL.
Opțiunea #2.
Este creată o funcție, countCurrencies de exemplu, care, pe baza ID-ului comenzii, returnează numărul de valute care au participat la ea și apoi:
Selectați i.id id, countCurrencies(i.id) valute, sum(ig.quantity) total, SUM(g.price * ig.quantity) preț, c.briefly cname FROM invoice I join invoice_goods ig on (ig.invoice_id = i.id) join good g on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) grupează după i.id, c.pe scurt, countCurrencies(i.id)
Poate funcționa... dar va fi apelat pentru fiecare monedă din fiecare comandă. Nu știu dacă MySQL vă permite să faceți GROUP BY după funcție...
Opțiunea nr. 3
Selectați i.id id, agr.cnt valute, sum(ig.quantity) total, SUM(g.price * ig.quantity) preț, c.briefly cname FROM invoice i join invoice_goods ig on (ig.invoice_id = i.id ) join good go on (g.id = ig.good_id) LEFT OUTER JOIN currency c ON (c.id = g.currency) stânga outer join (selectați ii.id, count(distinct gg.currency) cnt din factură ii, invoce_goods iig, good gg where ii.id = iig.invoice_id and gg.id = iig.good_id group by ii.id) agr on (i.id = agr.id) group by i.id, c.briefly, agr. cnt
Probabil cea mai corectă... și foarte probabil cea mai funcțională opțiune dintre toate.
Cea mai rapidă este Opțiunea nr. 1. Nr. 2 este cel mai ineficient, pentru că Cu cât sunt mai multe valute în comandă, cu atât acestea sunt numărate mai des.
Nr. 3 nu este, de asemenea, în principiu, cel mai bun din punct de vedere al vitezei, dar cel puțin te poți baza pe caching în interiorul DBMS.
Rezultatul tuturor celor trei selecții va fi următorul:
| id | monede | total | Preț | cname | ||||
|---|---|---|---|---|---|---|---|---|
| 33 | 2 | 1.00 | 198.00 | B.F. | ||||
| 33 | 2 | 4.00 | 1548.04 | FRECA | ||||
pentru același id numărul din coloana valute va fi întotdeauna același, este ceea ce aveți nevoie?
Descrie utilizarea operatorilor aritmetici și construcția coloanelor calculate. Sunt luate în considerare funcțiile finale (agregate) COUNT, SUM, AVG, MAX, MIN. Oferă un exemplu de utilizare a operatorului GROUP BY pentru gruparea în interogări de selecție a datelor. Descrie utilizarea clauzei HAVING.Construirea câmpurilor calculate
În general, a crea câmp calculat (derivat). lista SELECT trebuie să conțină o expresie SQL. Aceste expresii folosesc operațiile aritmetice de adunare, scădere, înmulțire și împărțire, precum și funcții SQL încorporate. Puteți specifica numele oricărei coloane (câmp) a unui tabel sau a unei interogări, dar utilizați numai numele de coloană a tabelului sau a interogării care este listată în lista de clauze FROM a instrucțiunii corespunzătoare. Când construiți expresii complexe, pot fi necesare paranteze.
Standardele SQL vă permit să specificați în mod explicit numele coloanelor din tabelul rezultat, pentru care este utilizată clauza AS.
SELECTează Product.Name, Product.Price, Deal.Quantity, Product.Price*Deal.Quantity AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode Exemplul 6.1. Calculul costului total pentru fiecare tranzacție.
Exemplul 6.2. Obțineți o listă de companii care indică numele și inițialele clienților.
SELECTARE Companie, Nume+""+ Stânga(Prenumele,1)+"."+Stânga(Numele de mijloc,1)+"."CA Numele Complet FROM Client Exemplul 6.2. Obținerea unei liste de firme indicând numele și inițialele clienților.
Solicitarea folosește funcția încorporată Left, care vă permite să tăiați un caracter din stânga într-o variabilă text în acest caz.
Exemplul 6.3. Obțineți o listă de produse care indică anul și luna vânzării.
SELECT Product.Name, Year(Transaction.Date) AS Year, Month(Transaction.Date) AS Month FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Exemplul 6.3. Primirea unei liste de produse indicând anul și luna vânzării.
Interogarea folosește funcțiile încorporate An și Lună pentru a extrage anul și luna dintr-o dată.
Utilizarea funcțiilor de rezumat
Prin utilizarea funcții finale (agregate).în cadrul interogării SQL, puteți obține o serie de informații statistice generale despre setul de valori selectate ale setului de ieșire.
Utilizatorul are acces la următoarele elemente de bază funcții de rezumat:
- Count (Expression) - determină numărul de înregistrări din setul de ieșire al interogării SQL;
- Min/Max (Expresie) - determinați cea mai mică și cea mai mare dintre setul de valori într-un anumit câmp de solicitare;
- Avg (Expresie) - această funcție vă permite să calculați media unui set de valori stocate într-un anumit câmp de înregistrări selectat de o interogare. Este o medie aritmetică, adică suma valorilor împărțită la numărul lor.
- Suma (Expresie) - Calculează suma setului de valori conținute într-un anumit câmp al înregistrărilor selectate de interogare.
Cel mai adesea, numele coloanelor sunt folosite ca expresii. Expresia poate fi calculată și folosind valorile mai multor tabele.
Toate aceste funcții operează pe valori dintr-o singură coloană a unui tabel sau pe o expresie aritmetică și returnează o singură valoare. Funcțiile COUNT , MIN și MAX se aplică atât câmpurilor numerice, cât și nenumerice, în timp ce funcțiile SUM și AVG pot fi utilizate numai pentru câmpurile numerice, cu excepția COUNT(*) . La calcularea rezultatelor oricărei funcții, toate valorile nule sunt mai întâi eliminate, iar apoi operațiunea necesară este aplicată numai valorilor de coloană specifice rămase. Opțiunea COUNT(*) este un caz special de utilizare al funcției COUNT, scopul său este de a număra toate rândurile din tabelul rezultat, indiferent dacă conține valori nule, duplicate sau orice alte valori;
Dacă trebuie să eliminați valorile duplicate înainte de a utiliza o funcție generică, trebuie să precedați numele coloanei în definiția funcției cu cuvântul cheie DISTINCT. Nu are nicio semnificație pentru funcțiile MIN și MAX, dar utilizarea acestuia poate afecta rezultatele funcțiilor SUM și AVG, așa că trebuie să luați în considerare dacă ar trebui să fie prezentă în fiecare caz. În plus, cuvântul cheie DISTINCT poate fi specificat o singură dată în orice interogare.
Este foarte important să rețineți că funcții de rezumat poate fi folosit doar într-o listă într-o clauză SELECT și ca parte a unei clauze HAVING. În toate celelalte cazuri, acest lucru este inacceptabil. Dacă lista din clauza SELECT conține funcții de rezumat, iar textul de interogare nu conține o clauză GROUP BY, care prevede combinarea datelor în grupuri, atunci niciunul dintre elementele listei clauzei SELECT nu poate include nicio referință la câmpuri, cu excepția situației în care câmpurile acționează ca argumente funcții finale.
Exemplul 6.4. Determinați primul nume alfabetic al produsului.
SELECT Min(Product.Name) AS Min_Name FROM Product Exemplul 6.4. Determinarea primului nume alfabetic al produsului.
Exemplul 6.5. Determinați numărul de tranzacții.
SELECT Count(*) AS Number_of_deals FROM Deal Exemplul 6.5. Determinați numărul de tranzacții.
Exemplul 6.6. Determinați cantitatea totală de mărfuri vândute.
SELECTAȚI Suma(Deal.Quantity) AS Item_Quantity FROM Deal Exemplul 6.6. Determinarea cantității totale de mărfuri vândute.
Exemplul 6.7. Determinați prețul mediu al bunurilor vândute.
SELECTAȚI Avg(Product.Price) AS Avg_Price FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode; Exemplul 6.7. Determinarea prețului mediu al mărfurilor vândute.
SELECTAȚI Suma(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode Exemplul 6.8. Calcularea costului total al mărfurilor vândute.
Clauza GROUP BY
Interogările necesită adesea crearea de subtotaluri, ceea ce este de obicei indicat de apariția expresiei „pentru fiecare...” în interogare. O clauză GROUP BY este utilizată în instrucțiunea SELECT în acest scop. O interogare care conține GROUP BY se numește interogare de grupare deoarece grupează datele returnate de operația SELECT și apoi creează un singur rând rezumat pentru fiecare grup individual. Standardul SQL cere ca clauza SELECT și clauza GROUP BY să fie strâns legate. Când o instrucțiune SELECT conține o clauză GROUP BY, fiecare element de listă din clauza SELECT trebuie să aibă o singură valoare pentru întregul grup. În plus, clauza SELECT poate include doar următoarele tipuri de elemente: nume de câmpuri, funcții de rezumat, constante și expresii care includ combinații ale elementelor enumerate mai sus.
Toate numele câmpurilor enumerate în clauza SELECT trebuie să apară și în clauza GROUP BY - cu excepția cazului în care numele coloanei este utilizat în functie finala. Regula inversă nu este adevărată - clauza GROUP BY poate conține nume de coloane care nu sunt în lista clauzei SELECT.
Dacă o clauză WHERE este utilizată împreună cu GROUP BY, aceasta este procesată mai întâi și sunt grupate numai acele rânduri care îndeplinesc condiția de căutare.
Standardul SQL specifică că la grupare, toate valorile lipsă sunt tratate ca fiind egale. Dacă două rânduri de tabel din aceeași coloană de grupare conțin o valoare NULL și valori identice în toate celelalte coloane de grupare non-nule, acestea sunt plasate în același grup.
Exemplul 6.9. Calculați volumul mediu de achiziții efectuate de fiecare client.
SELECT Client.LastName, Avg(Transaction.Quantity) AS Average_Quantity FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.LastName Exemplul 6.9. Calculați volumul mediu de achiziții efectuate de fiecare client.
Expresia „fiecare client” este reflectată în interogarea SQL sub forma unei propoziții GROUP BY Client.Nume.
Exemplul 6.10. Stabiliți cu cât a fost vândut fiecare produs.
SELECT Product.Name, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name Exemplul 6.10. Determinarea cantității pentru care a fost vândut fiecare produs.
SELECT Client.Company, Count(Transaction.TransactionCode) AS Number_of_transactions FROM Client INNER JOIN Tranzacție ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company Exemplul 6.11. Numărarea numărului de tranzacții efectuate de fiecare firmă.
SELECT Customer.Company, Sum(Transaction.Quantity) AS Total_Quantity, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN (Customer INNER JOIN Tranzacție ON Customer.ClientCode=Transaction.CustomerCode) ON Product.ProductCode=Tranzacție .Cod produs GROUP BY Client.Companie Exemplul 6.12. Calculul cantității totale de bunuri achiziționate pentru fiecare companie și costul acesteia.
Exemplul 6.13. Determinați costul total al fiecărui produs pentru fiecare lună.
SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name, Month(Transaction.Date ) Exemplul 6.13. Determinarea costului total al fiecărui produs pentru fiecare lună.
Exemplul 6.14. Determinați costul total al fiecărui produs de primă clasă pentru fiecare lună.
SELECT Product.Name, Month(Transaction.Date) AS Month, Sum(Product.Price*Transaction.Quantity) AS Cost FROM Product INNER JOIN Transaction ON Product.ProductCode=Transaction.ProductCode WHERE Product.Grade="First" GROUP BY Product .Nume, Lună (Data tranzacției) Exemplul 6.14. Determinarea costului total al fiecărui produs de primă clasă pentru fiecare lună.
AVÂND oferta
Folosind HAVING, toate blocurile de date grupate anterior folosind GROUP BY care îndeplinesc condițiile specificate în HAVING sunt reflectate. Aceasta este o opțiune suplimentară pentru „filtrarea” setului de ieșire.
Condițiile din HAVING sunt diferite de condițiile din WHERE:
- HAVING exclude grupurile cu rezultate de valoare agregată din setul de date rezultat;
- UNDE exclude înregistrările care nu îndeplinesc condiția din calculul valorilor agregate prin grupare;
- Funcțiile agregate nu pot fi specificate în condiția de căutare WHERE.
Exemplul 6.15. Identificați companiile al căror număr total de tranzacții a depășit trei.
SELECT Client.Company, Count(Trade.Quantity) AS Number_of_deals FROM Client INNER JOIN Trade ON Client.ClientCode=Transaction.ClientCode GROUP BY Client.Company HAVING Count(Transaction.Quantity)>3 Exemplul 6.15. Identificarea firmelor al căror număr total de tranzacții a depășit trei.
Exemplul 6.16. Afișați o listă de mărfuri vândute pentru mai mult de 10.000 de ruble.
SELECTează Product.Name, Sum(Product.Price*Deal.Quantity) AS Cost FROM Product INNER JOIN Deal ON Product.ProductCode=Transaction.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Deal.Quantity)>10000 Exemplul 6.16. Afișarea unei liste de mărfuri vândute pentru mai mult de 10.000 de ruble.
Exemplul 6.17. Afișați o listă de produse vândute cu peste 10.000 fără a specifica suma.
SELECTează Product.Name FROM Product INNER JOIN Deal ON Product.ProductCode=Deal.ProductCode GROUP BY Product.Name HAVING Sum(Product.Price*Transaction.Quantity)>10000 Exemplul 6.17. Afișați o listă de produse vândute cu peste 10.000 fără a specifica suma.
În acest tutorial veți învăța cum să utilizați Funcția COUNTîn SQL Server (Transact-SQL) cu sintaxă și exemple.
Descriere
În SQL Server (Transact-SQL) Funcția COUNT returnează numărul de rânduri ale unui câmp sau expresie din setul de rezultate.
Sintaxă
Sintaxa pentru funcția COUNT în SQL Server (Transact-SQL) este:
SAU sintaxa pentru funcția COUNT atunci când se grupează rezultatele uneia sau mai multor coloane este:
Parametri sau Argumente
expresie1 , expresie2 , … expresie_n
Expresii care nu sunt incluse într-o funcție COUNT și trebuie incluse într-o clauză GROUP BY la sfârșitul instrucțiunii SQL.
aggregate_expression este coloana sau expresia ale cărei valori non-NULL vor fi numărate.
tabele - tabele din care doriți să obțineți înregistrări. Trebuie să existe cel puțin un tabel listat în clauza FROM.
Condiții WHERE - opțional. Acestea sunt condiții care trebuie îndeplinite pentru înregistrările selectate.
Inclusiv valorile non-NULL
Nu toată lumea înțelege acest lucru, dar funcția COUNT va număra doar acele înregistrări în care valoarea expresiei în COUNT (expresie_agregată) nu este NULL. Când o expresie conține o valoare NULL, aceasta nu este inclusă în contorul COUNT.
Să ne uităm la un exemplu de funcție COUNT care demonstrează modul în care valorile NULL sunt evaluate de funcția COUNT.
De exemplu, dacă aveți următorul tabel numit piețe:
Acest exemplu COUNT va returna 3 deoarece toate valorile market_id din setul de rezultate ale interogării NU sunt NULL.
Cu toate acestea, dacă ați rulat următoarea instrucțiune SELECT, care utilizează funcția COUNT:
Transact-SQL
SELECTARE NUMĂR (filiali) FROM piețe; --Rezultat: 1
Acest exemplu COUNT va returna doar 1, deoarece o singură valoare a filialelor din setul de rezultate ale interogării NU este NULL. Aceasta va fi prima linie care spune filials = „da”. Acesta este singurul rând care este inclus în calculul funcției COUNT.
Aplicație
Funcția COUNT poate fi utilizată în următoarele versiuni de SQL Server (Transact-SQL):
SQL Server vNext, SQL Server 2016, SQL Server 2015, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, SQL Server 2005
Exemplu cu un câmp
Să ne uităm la câteva exemple de funcții SQL Server COUNT pentru a înțelege cum să utilizați funcția COUNT în SQL Server (Transact-SQL).
De exemplu, puteți afla câte contacte are un utilizator cu last_name = „Rasputin”.
În acest exemplu de funcție COUNT, am specificat aliasul „Număr de contacte” expresiei COUNT (*). Prin urmare, setul de rezultate va afișa „Număr de contacte” ca nume de câmp.
Exemplu folosind DISTINCT
Puteți utiliza operatorul DISTINCT în funcția COUNT. De exemplu, instrucțiunea SQL de mai jos returnează numărul de departamente unice în care cel puțin un angajat are first_name = 'Samvel'.