Sve što trebate znati o SQL GROUP BY izjavi

Sve što trebate znati o SQL GROUP BY izjavi

Veliki dio moći relacijskih baza podataka dolazi od filtriranja podataka i spajanja tablica. Zbog toga mi i predstavljamo te odnose. No, moderni sustavi baza podataka pružaju još jednu vrijednu tehniku: grupiranje.





Grupiranje vam omogućuje da izvučete sažete podatke iz baze podataka. Omogućuje vam kombiniranje rezultata za stvaranje korisnih statističkih podataka. Grupiranje vas štedi od pisanja koda za uobičajene slučajeve, poput prosječnog popisa slika. I to može učiniti učinkovitijim sustavima.





Čemu služi klauzula GROUP BY?

GROUP BY, kako naziv govori, grupira rezultate u manji skup. Rezultati se sastoje od jednog retka za svaku zasebnu vrijednost grupiranog stupca. Njegovu uporabu možemo pokazati gledajući neke uzorke podataka s retcima koji dijele neke zajedničke vrijednosti.





youtube stalno preporučuje iste videozapise

Slijedi vrlo jednostavna baza podataka s dvije tablice koje predstavljaju albume ploča. Takvu bazu podataka možete postaviti tako da pisanje osnovne sheme za odabrani sustav baze podataka. The albumima tablica ima devet redaka s primarnim ključem iskaznica stupac i stupci za ime, izvođača, godinu izdanja i prodaju:

+----+---------------------------+-----------+--------------+-------+
| 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 umjetnici stol je još jednostavniji. Ima sedam redaka s stupcima ID -a i imena:



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

Možete razumjeti različite aspekte GROUP BY pomoću jednostavnog skupa podataka poput ovog. Naravno, skup podataka iz stvarnog života imao bi mnogo, mnogo više redaka, ali principi ostaju isti.

Grupiranje po jednom stupcu

Recimo da želimo saznati koliko albuma imamo za svakog izvođača. Počnite s tipičnim ODABERI upit za dohvaćanje stupca artist_id:





SELECT artist_id FROM albums

Ovo vraća svih devet redaka, očekivano:

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

Da biste grupirali ove rezultate prema izvođaču, dodajte izraz GROUP BY artist_id :





SELECT artist_id FROM albums GROUP BY artist_id

Što daje sljedeće rezultate:

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

U skupu rezultata nalazi se sedam redaka, umanjeno od ukupno devet u albumima stol. Svaki jedinstven artist_id ima jedan red. Na kraju, da biste dobili stvarne brojeve, dodajte RAČUNATI(*) u odabrane stupce:

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 grupiraju dva para redaka za umjetnike s identifikacijskim oznakama 2 i 6 . Svaki ima dva albuma u našoj bazi podataka.

Povezano: Osnovne SQL naredbe Cheat Sheet za početnike

Kako pristupiti grupiranim podacima s agregatnom funkcijom

Možda ste koristili RAČUNATI funkciju prije, osobito u RAČUNATI(*) obliku kao što je gore vidljivo. Dohvaća broj rezultata u skupu. Pomoću njega možete dobiti ukupan broj zapisa u tablici:

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

COUNT je zbirna funkcija. Ovaj se izraz odnosi na funkcije koje prevode vrijednosti iz više redaka u jednu vrijednost. Često se koriste zajedno s izrazom GROUP BY.

Umjesto da samo brojimo redove, možemo primijeniti skupnu funkciju na grupirane vrijednosti:

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

Gore prikazana ukupna prodaja za izvođače 2 i 6 zajedno je prodaja njihovih više albuma:

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

Grupiranje po više stupaca

Možete grupirati prema više stupaca. Samo unesite više stupaca ili izraza odvojenih zarezima. Rezultati će se grupirati prema kombinaciji ovih stupaca.

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

To će obično proizvesti više rezultata nego grupiranje po jednom stupcu:

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

Imajte na umu da u našem malom primjeru samo dva albuma imaju istu godinu izdavanja i prodaju (28 u 1977.).

Korisne agregatne funkcije

Osim COUNT, nekoliko funkcija dobro funkcionira s GROUP -om. Svaka funkcija vraća vrijednost na temelju zapisa koji pripadaju svakoj grupi rezultata.

  • COUNT () vraća ukupan broj odgovarajućih zapisa.
  • SUM () vraća zbroj svih vrijednosti u danom stupcu.
  • MIN () vraća najmanju vrijednost u danom stupcu.
  • MAX () vraća najveću vrijednost u danom stupcu.
  • AVG () vraća prosjek. To je ekvivalent SUM () / COUNT ().

Ove funkcije možete koristiti i bez klauzule GROUP:

zašto moj tvrdi disk radi na 100 windows 10
SELECT AVG(sales) FROM albums
+------------+
| AVG(sales) |
+------------+
| 19.1111 |
+------------+

Korištenje GROUP BY s odredbom WHERE

Kao i kod normalnog SELECT -a, još uvijek možete upotrijebiti WHERE za filtriranje skupa rezultata:

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

Sada imate samo one albume objavljene nakon 1990., grupirane po izvođačima. Također možete koristiti pridruživanje s klauzulom WHERE, neovisno o 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 |
+---------+--------+

Međutim, imajte na umu da ako pokušate filtrirati na temelju zbirnog stupca:

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

Dobit ćete pogrešku:

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

Stupci temeljeni na zbirnim podacima nisu dostupni klauzuli WHERE.

Korištenje HAVING klauzule

Dakle, kako filtrirate skup rezultata nakon što je izvršeno grupiranje? The IMAJUĆI klauzula se bavi ovom potrebom:

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

Imajte na umu da klauzula HAVING dolazi iza GROUP BY. Inače, to je u biti jednostavna zamjena WHERE sa HAVING. Rezultati su sljedeći:

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

I dalje možete upotrijebiti uvjet WHERE za filtriranje rezultata prije grupiranja. Radit će zajedno s klauzulom HAVING za filtriranje nakon grupiranja:

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 je jedan izvođač u našoj bazi izdao više od jednog albuma nakon 1990 .:

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

Kombiniranje rezultata s GROUP BY

Naredba GROUP BY nevjerojatno je koristan dio jezika SQL. Može pružiti sažete podatke, za stranicu sa sadržajem, na primjer. Izvrsna je alternativa za preuzimanje velikih količina podataka. Baza podataka dobro podnosi ovo dodatno opterećenje jer ga sam dizajn čini optimalnim za posao.

Kad shvatite grupiranje i način pridruživanja više tablica, moći ćete iskoristiti većinu snage relacijske baze podataka.

Udio Udio Cvrkut E -pošta Kako odjednom zatražiti više tablica baza podataka s pridruživanjem SQL -a

Naučite kako koristiti SQL pridruživanja za pojednostavljivanje upita, uštedjeti vrijeme i učiniti da se osjećate kao snažni korisnik SQL -a.

spojen na internet, ali nema pristup
Pročitajte Dalje Povezane teme
  • Programiranje
  • SQL
O autoru Bobby Jack(Objavljeno 58 članaka)

Bobby je entuzijast u tehnologiji koji je skoro dva desetljeća radio kao programer softvera. Oduševljen je igranjem igara, radi kao urednik recenzija u Switch Player Magazinu, a uronjen je u sve aspekte online izdavaštva i web razvoja.

Više od Bobbyja Jacka

Pretplatite se na naše obavijesti

Pridružite se našem biltenu za tehničke savjete, recenzije, besplatne e -knjige i ekskluzivne ponude!

Kliknite ovdje za pretplatu