Kõik, mida peate teadma SQL GROUP BY avalduse kohta

Kõik, mida peate teadma SQL GROUP BY avalduse kohta

Suur osa relatsiooniliste andmebaaside võimsusest tuleb andmete filtreerimisest ja tabelite ühendamisest. Seetõttu esindamegi neid suhteid. Kuid kaasaegsed andmebaasisüsteemid pakuvad veel ühte väärtuslikku tehnikat: rühmitamist.





Rühmitamine võimaldab andmebaasist koondteavet hankida. See võimaldab tulemusi kombineerida, et luua kasulikke statistilisi andmeid. Rühmitamine säästab teid koodi kirjutamisest tavalisteks juhtumiteks, näiteks numbrite loendite keskmistamiseks. Ja see võib luua tõhusamaid süsteeme.





Mida teeb klausel GROUP BY?

GROUP BY, nagu nimigi ütleb, rühmitab tulemused väiksemaks. Tulemused koosnevad ühest reast rühmitatud veeru iga erineva väärtuse kohta. Selle kasutamist saame näidata, kui vaatame mõningaid ühiseid väärtusi jagavate ridadega näidisandmeid.





teil on lauaarvuti, mida soovite uuendada. lisate mitu sisemist komponenti

Järgnev on väga lihtne andmebaas, kus on kaks tabelit, mis esindavad plaadialbumeid. Sellise andmebaasi saate seadistada põhiskeemi kirjutamine teie valitud andmebaasisüsteemi jaoks. The albumid tabelis on üheksa rida primaarvõtmega id veerg ja veerud nime, esitaja, väljalaskeaasta ja müügi kohta:

+----+---------------------------+-----------+--------------+-------+
| id | name | artist_id | release_year | sales |
+----+---------------------------+-----------+--------------+-------+
| 1 | Abbey Road | 1 | 1969 | 14 |
| 2 | The Dark Side of the Moon | 2 | 1973 | 24 |
| 3 | Rumours | 3 | 1977 | 28 |
| 4 | Nevermind | 4 | 1991 | 17 |
| 5 | Animals | 2 | 1977 | 6 |
| 6 | Goodbye Yellow Brick Road | 5 | 1973 | 8 |
| 7 | 21 | 6 | 2011 | 25 |
| 8 | 25 | 6 | 2015 | 22 |
| 9 | Bat Out of Hell | 7 | 1977 | 28 |
+----+---------------------------+-----------+--------------+-------+

The kunstnikud tabel on veelgi lihtsam. Sellel on seitse rida id ja nime veergudega:



+----+---------------+
| id | name |
+----+---------------+
| 1 | The Beatles |
| 2 | Pink Floyd |
| 3 | Fleetwood Mac |
| 4 | Nirvana |
| 5 | Elton John |
| 6 | Adele |
| 7 | Meat Loaf |
+----+---------------+

Sellise lihtsa andmekogumi abil saate aru saada GROUP BY erinevatest aspektidest. Loomulikult oleks reaalses andmekogus palju-palju rohkem ridu, kuid põhimõtted jäävad samaks.

Rühmitamine ühe veeru järgi

Oletame, et tahame teada saada, mitu albumit meil on iga esitaja kohta. Alustage tüüpilisest VALI päring veergu artist_id toomiseks:





SELECT artist_id FROM albums

See tagastab ootuspäraselt kõik üheksa rida:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 5 |
| 6 |
| 6 |
| 7 |
+-----------+

Nende tulemuste rühmitamiseks kunstniku järgi lisage fraas GROUP BY artist_id :





SELECT artist_id FROM albums GROUP BY artist_id

Mis annab järgmised tulemused:

+-----------+
| artist_id |
+-----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
+-----------+

Tulemuskomplektis on seitse rida, mis on vähendatud üheksa reast albumid tabel. Igaüks ainulaadne artist_id sellel on üks rida. Lõpuks, et saada tegelik arv, lisage COUNT (*) valitud veergudele:

SELECT artist_id, COUNT(*)
FROM albums
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 1 | 1 |
| 2 | 2 |
| 3 | 1 |
| 4 | 1 |
| 5 | 1 |
| 6 | 2 |
| 7 | 1 |
+-----------+----------+

Tulemused rühmitavad ID -dega kunstnike jaoks kaks rida 2 ja 6 . Igaühel on meie andmebaasis kaks albumit.

Seotud: Olulised SQL -käskude petuleht algajatele

Kuidas kogutud funktsiooniga grupeeritud andmetele juurde pääseda

Võimalik, et kasutasite COUNT funktsiooni enne, eriti COUNT (*) kujul, nagu eespool näha. See toob välja tulemuste arvu komplektis. Selle abil saate tabelis olevate kirjete koguarvu:

SELECT COUNT(*) FROM albums
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

COUNT on koondfunktsioon. See termin viitab funktsioonidele, mis teisendavad mitmelt realt pärinevad väärtused üheks väärtuseks. Neid kasutatakse sageli koos avaldusega GROUP BY.

Ridade arvu loendamise asemel saame rühmitatud väärtustele rakendada koondfunktsiooni:

SELECT artist_id, SUM(sales)
FROM albums
GROUP BY artist_id
+-----------+------------+
| artist_id | SUM(sales) |
+-----------+------------+
| 1 | 14 |
| 2 | 30 |
| 3 | 28 |
| 4 | 17 |
| 5 | 8 |
| 6 | 47 |
| 7 | 28 |
+-----------+------------+

Ülaltoodud artiklite 2 ja 6 kogumüük on nende mitme albumi müük kokku:

SELECT artist_id, sales
FROM albums
WHERE artist_id IN (2, 6)
+-----------+-------+
| artist_id | sales |
+-----------+-------+
| 2 | 24 |
| 2 | 6 |
| 6 | 25 |
| 6 | 22 |
+-----------+-------+

Rühmitamine mitme veeru järgi

Saate rühmitada rohkem kui ühe veeru järgi. Lisage lihtsalt mitu veergu või avaldist, eraldades need komadega. Tulemused rühmitatakse vastavalt nende veergude kombinatsioonile.

SELECT release_year, sales, count(*)
FROM albums
GROUP BY release_year, sales

Tavaliselt annab see rohkem tulemusi kui ühe veeru järgi rühmitamine:

+--------------+-------+----------+
| release_year | sales | count(*) |
+--------------+-------+----------+
| 1969 | 14 | 1 |
| 1973 | 24 | 1 |
| 1977 | 28 | 2 |
| 1991 | 17 | 1 |
| 1977 | 6 | 1 |
| 1973 | 8 | 1 |
| 2011 | 25 | 1 |
| 2015 | 22 | 1 |
+--------------+-------+----------+

Pange tähele, et meie väikeses näites on vaid kahel albumil sama väljalaskeaasta ja müüginumber (1977. aastal 28).

Kasulikud koondfunktsioonid

Lisaks COUNT -le töötavad GROUPiga mitmed funktsioonid hästi. Iga funktsioon tagastab väärtuse, mis põhineb iga tulemusrühma kirjetel.

  • COUNT () tagastab vastavate kirjete koguarvu.
  • SUM () tagastab kõigi antud veeru kõigi väärtuste summa kokku.
  • MIN () tagastab antud veeru väikseima väärtuse.
  • MAX () tagastab antud veeru suurima väärtuse.
  • AVG () tagastab keskmise keskmise. See on samaväärne SUM () / COUNT ().

Neid funktsioone saate kasutada ka ilma GROUP klauslita:

mis tüüpi ram võib süsteemi jõudlust aeglustada
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

GROUP BY kasutamine WHERE klausliga

Nii nagu tavalise SELECTi puhul, saate tulemuste komplekti filtreerimiseks kasutada ka WHERE:

SELECT artist_id, COUNT(*)
FROM albums
WHERE release_year > 1990
GROUP BY artist_id
+-----------+----------+
| artist_id | COUNT(*) |
+-----------+----------+
| 4 | 1 |
| 6 | 2 |
+-----------+----------+

Nüüd on teil ainult need albumid, mis on välja antud pärast 1990. aastat, rühmitatud esitaja järgi. Ühendust saab kasutada ka WHERE klausliga, sõltumata GROUP BY:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
+---------+--------+
| name | albums |
+---------+--------+
| Nirvana | 1 |
| Adele | 2 |
+---------+--------+

Pange aga tähele, et kui proovite filtreerida koondatud veeru alusel, tehke järgmist.

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND albums > 2
GROUP BY artist_id;

Saate vea:

ERROR 1054 (42S22): Unknown column 'albums' in 'where clause'

Koondandmetel põhinevad veerud ei ole klausli WHERE jaoks saadaval.

Kasutades klauslit HAVING

Niisiis, kuidas filtreerida tulemuste kogumit pärast rühmitamist? The Omades klausel käsitleb seda vajadust:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
GROUP BY artist_id
HAVING albums > 1;

Pange tähele, et klausel HAVING tuleb GROUP BY järel. Vastasel juhul on see sisuliselt WHERE lihtne asendamine HAVING -iga. Tulemused on järgmised:

+------------+--------+
| name | albums |
+------------+--------+
| Pink Floyd | 2 |
| Adele | 2 |
+------------+--------+

Enne rühmitamist saate tulemuste filtreerimiseks kasutada tingimust WHERE. See töötab koos HAVING klausliga filtreerimiseks pärast rühmitamist:

SELECT r.name, COUNT(*) AS albums
FROM albums l, artists r
WHERE artist_id=r.id
AND release_year > 1990
GROUP BY artist_id
HAVING albums > 1;

Vaid üks artist meie andmebaasis andis pärast 1990. aastat välja rohkem kui ühe albumi:

+-------+--------+
| name | albums |
+-------+--------+
| Adele | 2 |
+-------+--------+

Tulemuste kombineerimine GROUP BY -ga

Lause GROUP BY on SQL keele uskumatult kasulik osa. See võib anda andmete kokkuvõtlikku teavet, näiteks sisulehe jaoks. See on suurepärane alternatiiv suurte andmemahtude hankimiseks. Andmebaas saab selle lisakoormusega hästi hakkama, kuna selle disain muudab selle töö jaoks optimaalseks.

Kui olete rühmitamisest ja mitme tabeliga liitumisest aru saanud, saate kasutada enamikku relatsiooniandmebaasi võimsusest.

Jaga Jaga Piiksuma E -post Kuidas küsida SQL -i ühenduste abil korraga mitut andmebaasitabelit

Siit saate teada, kuidas kasutada SQL -i liitumisi päringute sujuvamaks muutmiseks, aja kokkuhoiuks ja SQL -i energiatarbijana tundmiseks.

kuidas emaplaati otsida
Loe edasi Seotud teemad
  • Programmeerimine
  • SQL
Autori kohta Bobby Jack(58 artiklit avaldatud)

Bobby on tehnoloogiahuviline, kes töötas enam kui kaks aastakümmet tarkvaraarendajana. Ta on kirglik mängude vastu, töötab ajakirja Switch Player ülevaadete toimetajana ning on süvenenud kõikidesse veebipõhiste avaldamise ja veebiarenduse aspektidesse.

Veel Bobby Jackilt

Telli meie uudiskiri

Liituge meie uudiskirjaga, et saada tehnilisi näpunäiteid, ülevaateid, tasuta e -raamatuid ja eksklusiivseid pakkumisi!

Tellimiseks klõpsake siin