MySQL GROUP BY ja HAVING-lauseke esimerkkeineen

Mikä on SQL GROUP BY -lauseke?

GROUP BY -lause on SQL-komento, johon on totuttu ryhmittele rivit, joilla on samat arvotSe kirjoitetaan SELECT-lausekkeen sisälle, ja sitä käytetään yleensä yhdessä koostefunktioiden kanssa yhteenvetoraporttien luomiseen tietokannasta.

Sitä se tekee: se tiivistää tiedot tietokannassa. GROUP BY -lauseen sisältäviä kyselyitä kutsutaan ryhmitellyiksi kyselyiksi, ja ne palauttavat yhden rivin jokaista ryhmiteltyä alkiota kohden.

SQL GROUP BY Syntaksi

Nyt kun lausekkeen tarkoitus on selvä, tarkastellaan yksinkertaisen ryhmitellyn kyselyn syntaksia.

SELECT statements... GROUP BY column_name1[, column_name2, ...] [HAVING condition];

TÄÄLTÄ

• "SELECT-lausekkeet…"on standardi SQL SELECT komentokysely.
• "GROUP BY sarakkeen_nimi1” on lauseke, joka suorittaa ryhmänping perustuu sarakkeen_nimi1:een.
• "[, sarakkeen_nimi2, …]” on valinnainen ja edustaa muita sarakenimiä, kun ryhmäping tehdään useammalla kuin yhdellä sarakkeella.
• "[ON ehto]” on valinnainen ja sitä käytetään rajoittamaan GROUP BY -lausekkeen vaikutuspiiriin kuuluvia rivejä. Se on samanlainen kuin WHERE-lauseke, paitsi että sitä käytetään maanpinnan jälkeenping.

Grouping Yhden sarakkeen käyttäminen

Nopein tapa nähdä SQL GROUP BY -lauseen vaikutus on verrata ryhmittelemätöntä kyselyä ryhmiteltyyn kyselyyn. Aloita yksinkertaisella kyselyllä, joka palauttaa kaikki sukupuolimerkinnät jäsentaulukossa.

SELECT `gender` FROM `members`;

Palautetaan yhdeksän riviä ja jokainen arvo toistetaan. Oletetaan, että haluamme sen sijaan sukupuolen yksilölliset arvot. Alla oleva kysely lisää GROUP BY -lauseen.

SELECT `gender` FROM `members` GROUP BY `gender`;

Suoritetaan yllä oleva komentosarja MySQL Työpöytä myflixdb:tä vastaan ​​antaa meille seuraavat tulokset.

Huomaa, että palautettiin vain kaksi riviä, koska taulukossa on vain kaksi sukupuolityyppiä. GROUP BY -lauseke ryhmitteli kaikki "Mies"-jäsenet yhteen ja palautti heille yhden rivin, ja se teki saman "Nais"-jäsenten kanssa.

Grouping Useiden sarakkeiden käyttäminen

Grouping Yhden sarakkeen funktio on usein liian karkea varsinaiseen raporttiin. GROUP BY hyväksyy pilkulla erotetun sarakeluettelon, ja niiden arvojen yhdistelmä määrittää kunkin ryhmän.

Oletetaan, että haluamme listan elokuvien category_id-arvoista ja vastaavista julkaisuvuosista. Tarkastellaan ensin tämän yksinkertaisen kyselyn tulosta.

SELECT `category_id`, `year_released` FROM `movies`;

Korostetut rivit osoittavat, että tulos sisältää kaksoiskappaleita. Saman kyselyn suorittaminen GROUP BY -lausekkeella poistaa ne.

SELECT `category_id`, `year_released` FROM `movies` GROUP BY `category_id`, `year_released`;

Suoritetaan yllä oleva komentosarja MySQL Workbenchin vertailu myflixdb-tiedostoa vastaan ​​antaa meille seuraavat tulokset.

GROUP BY -lauseke toimii sekä category_id- että year_released-tietojen perusteella tunnistaakseen unique rivit. Vuoden 2007 kategorian 6 kaksi päällekkäistä riviä yhdistettiin yhdeksi.

Nyrkkisääntö: Jos luokkatunnus on sama, mutta julkaisuvuosi on eri, riviä käsitellään yksilöllisenä. Jos luokkatunnus ja julkaisuvuosi ovat samat useammalla kuin yhdellä rivillä, rivit ovat kaksoiskappaleita ja vain toinen niistä näytetään.

Grouping ja aggregaattifunktiot

Kaksoiskappaleiden poistaminen on hyödyllistä, mutta ryhmittelyn todellinen voimaping tulee näkyviin, kun se on yhdistetty kokonaisfunktiotKoostefunktio laskee yhden arvon kullekin ryhmälle: COUNT laskee rivit, SUMMA laskee arvot yhteen ja AVG, MIN ja MAX kuvaavat hajautusta.

Oletetaan, että haluamme tietokannan mies- ja naisjäsenten kokonaismäärän. Alla oleva skripti tekee sen.

SELECT `gender`, COUNT(`membership_number`) FROM `members` GROUP BY `gender`;

Suoritetaan yllä oleva komentosarja MySQL Workbenchin vertailu myflixdb-tiedostoa vastaan ​​antaa seuraavat tulokset.

Rivit ryhmitellään jokaisen yksilöllisen sukupuoliarvon mukaan, ja kunkin ryhmän sisällä olevien rivien lukumäärä lasketaan COUNT-koostefunktiolla. Yhdeksän jäsentietuetta kutistetaan kahdeksi yhteenvetoriviksi.

Kyselytulosten rajoittaminen HAVING-lauseen avulla

Groupings-lausekkeita ei aina haluta jokaiselle taulukon riville. Joskus raportti on rajoitettava tiettyyn kriteeriin, ja se on HAVING-lauseen tehtävä.

Oletetaan, että haluamme tietää kaikki elokuvakategorian 8 julkaisuvuodet. Alla oleva skripti saavuttaa tämän tuloksen.

SELECT * FROM `movies` GROUP BY `category_id`, `year_released` HAVING `category_id` = 8;

Suoritetaan yllä oleva komentosarja MySQL Workbenchin vertailu myflixdb-tiedostoa vastaan ​​antaa meille seuraavat tulokset.

HAVING-ehto on säilyttänyt vain elokuvat, joiden kategoria-ID on 8.

Varoitus: MySQL 5.7 ja uudemmat versiot ottavat oletusarvoisesti käyttöön ONLY_FULL_GROUP_BY-tilan, ja tässä tilassa SELECT * GROUP BY -lausekkeella hylätään, koska elokuvan_tunnusta, otsikkoa ja ohjaajaa ei ole ryhmitelty eikä aggregoitu. Tuotannossa ryhmitellyt sarakkeet nimetään eksplisiittisesti, esimerkiksi SELECT category_id, year_release FROM elokuvat GROUP BY category_id, year_release HAVING category_id = 8;

MISSÄ vs. OMISTAA vs. RYHMÄLLÄ vs. LAJITTELUN MUKAAN

Aloittelijat sekoittavat usein nämä neljä lauseketta, koska ne kaikki muokkaavat tulosjoukkoa. Ero on siinä, että kun MySQL käyttää niitä: WHERE suoritetaan ennen rivien ryhmittelyä, HAVING sen jälkeen ja ORDER BY viimeisenä.

Käytännön seuraus on suorituskykyyn liittyvä. WHERE-suodatus poistaa rivit ennen ryhmäänping työ alkaa, joten ehto, joka ei ole riippuvainen koostetusta tuloksesta, kuuluu WHERE-luokkaan eikä HAVING-luokkaan.

UKK