Vse, kar morate vedeti o izjavi SQL GROUP BY

Vse, kar morate vedeti o izjavi SQL GROUP BY

Velik del relacijskih baz podatkov prihaja iz filtriranja podatkov in združevanja tabel. Zato te odnose sploh predstavljamo. Toda sodobni sistemi zbirk podatkov ponujajo še eno dragoceno tehniko: združevanje v skupine.





Združevanje vam omogoča, da izvlečete povzetke iz zbirke podatkov. Omogoča združevanje rezultatov za ustvarjanje uporabnih statističnih podatkov. Združevanje vas prihrani pri pisanju kode za običajne primere, kot je povprečenje seznamov številk. In lahko naredi učinkovitejše sisteme.



Kaj počne klavzula GROUP BY?

GROUP BY, kot že ime pove, združi rezultate v manjši niz. Rezultati so sestavljeni iz ene vrstice za vsako posebno vrednost združenega stolpca. Njegovo uporabo lahko prikažemo tako, da pogledamo nekaj vzorčnih podatkov z vrsticami, ki imajo skupne vrednosti.





kaj proge pomenijo pri pošiljanju sporočil

Spodaj je zelo preprosta zbirka podatkov z dvema tabelama, ki predstavljata albume plošč. Takšno bazo podatkov lahko nastavite z pisanje osnovne sheme za izbrani sistem zbirk podatkov. The albumi tabela ima devet vrstic s primarnim ključem id stolpec in stolpci za ime, izvajalca, leto izdaje in prodajo:

+----+---------------------------+-----------+--------------+-------+
| 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 umetniki miza je še enostavnejša. Ima sedem vrstic s stolpci id in imenom:



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

S preprostim naborom podatkov, kot je ta, lahko razumete različne vidike GROUP BY. Seveda bi imel nabor podatkov iz resničnega življenja veliko, veliko več vrstic, vendar načela ostajajo enaka.

Združevanje po enem stolpcu

Recimo, da želimo ugotoviti, koliko albumov imamo za vsakega izvajalca. Začnite s tipičnim IZBERI poizvedba za pridobitev stolpca artist_id:



SELECT artist_id FROM albums

To vrne vseh devet vrstic, kot je bilo pričakovano:

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

Če želite te rezultate združiti po izvajalcu, dodajte frazo GROUP BY artist_id :



SELECT artist_id FROM albums GROUP BY artist_id

Kar daje naslednje rezultate:

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

V naboru rezultatov je sedem vrstic, zmanjšanih od skupaj devetih v albumi miza. Vsak edinstven artist_id ima eno vrstico. Nazadnje, če želite dobiti dejansko število, dodajte COUNT (*) na izbrane stolpce:

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 |
+-----------+----------+

Rezultati združujejo dva para vrstic za izvajalce z ID -ji 2 in 6 . Vsak ima v svoji bazi po dva albuma.

Povezano: Osnovna vrstica ukazov SQL za začetnike

Kako dostopati do združenih podatkov s funkcijo združevanja

Morda ste uporabili COUNT funkcijo prej, zlasti v COUNT (*) obliki, kot je prikazano zgoraj. Pridobi število rezultatov v nizu. Z njim lahko dobite skupno število zapisov v tabeli:

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

COUNT je zbirna funkcija. Ta izraz se nanaša na funkcije, ki prevajajo vrednosti iz več vrstic v eno samo vrednost. Pogosto se uporabljajo skupaj z stavkom GROUP BY.

Namesto štetja števila vrstic lahko za združene vrednosti uporabimo združeno funkcijo:

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 |
+-----------+------------+

Skupna prodaja, prikazana zgoraj za izvajalce 2 in 6, je skupaj njihova prodaja več albumov:

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

Razvrščanje po več stolpcih

Lahko se združite po več stolpcih. Samo vključite več stolpcev ali izrazov, ločenih z vejicami. Rezultati bodo združeni glede na kombinacijo teh stolpcev.

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

To bo običajno prineslo več rezultatov kot združevanje v en sam stolpec:

+--------------+-------+----------+
| 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 |
+--------------+-------+----------+

Upoštevajte, da imata v našem majhnem primeru le dva albuma enako leto izdaje in število prodaj (28 leta 1977).

Koristne funkcije združevanja

Poleg COUNT z GROUP dobro deluje več funkcij. Vsaka funkcija vrne vrednost na podlagi zapisov, ki pripadajo vsaki skupini rezultatov.

  • COUNT () vrne skupno število ujemajočih se zapisov.
  • SUM () vrne seštevek vseh vrednosti v danem stolpcu.
  • MIN () vrne najmanjšo vrednost v danem stolpcu.
  • MAX () vrne največjo vrednost v danem stolpcu.
  • AVG () vrne povprečno povprečje. To je enakovredno SUM () / COUNT ().

Te funkcije lahko uporabite tudi brez klavzule GROUP:

kako narediti sliko s prozornim ozadjem
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Uporaba GROUP BY s klavzulo WHERE

Tako kot pri običajnem SELECT lahko še vedno uporabite WHERE za filtriranje nabora rezultatov:

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

Zdaj imate samo tiste albume, izdane po letu 1990, razvrščene po izvajalcih. Združitev lahko uporabite tudi s klavzulo WHERE, neodvisno od 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 |
+---------+--------+

Upoštevajte pa, da če poskusite filtrirati na podlagi združenega stolpca:

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

Prišlo bo do napake:

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

Stolpci, ki temeljijo na zbirnih podatkih, niso na voljo v klavzuli WHERE.

Uporaba klavzule HAVING

Kako torej filtrirate nabor rezultatov po opravljenem razvrščanju v skupine? The IMATI klavzula obravnava to potrebo:

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

Upoštevajte, da klavzula HAVING prihaja za GROUP BY. V nasprotnem primeru gre v bistvu za preprosto zamenjavo WHERE s HAVING. Rezultati so naslednji:

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

Za filtriranje rezultatov pred združevanjem lahko še vedno uporabite pogoj WHERE. Deloval bo skupaj z določbo HAVING za filtriranje po združevanju:

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;

Samo en izvajalec v naši bazi je po letu 1990 izdal več kot en album:

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

Združevanje rezultatov z GROUP BY

Stavek GROUP BY je neverjetno uporaben del jezika SQL. Zagotavlja lahko povzetek podatkov, na primer za stran z vsebino. Je odlična alternativa pridobivanju velikih količin podatkov. Baza podatkov dobro obvladuje to dodatno obremenitev, saj je zaradi svoje zasnove optimalna za delo.

Ko boste razumeli združevanje v skupine in kako se pridružite več tabelam, boste lahko izkoristili večino moči relacijske baze podatkov.

Deliti Deliti Cvrkutati E-naslov Kako hkrati poizvedovati o več tabelah zbirk podatkov s pridružitvami SQL

Naučite se uporabljati združevanja SQL za poenostavitev poizvedb, prihranite čas in se počutite kot močan uporabnik SQL.

svojega Amazon paketa nisem prejel
Preberite Naprej Sorodne teme
  • Programiranje
  • SQL
O avtorju Bobby Jack(58 objavljenih člankov)

Bobby je tehnološki navdušenec, ki je več kot dve desetletji delal kot razvijalec programske opreme. Navdušen je nad igranjem iger, dela kot urednik recenzij v reviji Switch Player in je potopljen v vse vidike spletnega založništva in spletnega razvoja.

Več od Bobbyja Jacka

Naročite se na naše novice

Pridružite se našemu glasilu za tehnične nasvete, ocene, brezplačne e -knjige in ekskluzivne ponudbe!

Kliknite tukaj, da se naročite