PHPPamokos.lt


7. Duomenų grupavimas - GROUP BY ir HAVING

Taigi, šiam momentui mokame ištraukti duomenis jau iš kelių lentelių. Šioje pamokoje pažiūrėsime, kaip galima grupuoti duomenis, norint iš rezultato ištraukti dar tikslesnį rezultatą.

DISTINCT - pasikartojantys rezultatai

Sakykime, kad turime lentelę orders su tokiais duomenimis:

Pažiūrėkime į užklausą:

SELECT customer_id FROM orders;

Paprasta užklausa, išvardinanti klientų ID numerius. Bet jie pasikartos - juk klientai gali būti padarę daugiau negu vieną užsakymą.

Kaip atfiltruoti tik unikalias customer_id reikšmes? Tam skirtas raktažodis DISTINCT, prirašomas iškart po SELECT:

SELECT DISTINCT customer_id FROM orders;

Kaip matome, visai kitas rezultatas. Taip pat galime naudoti DISTINCT ir su keliais laukais - tada rezultatai bus sugrupuojami pagal tų kelių laukų unikalumą:

SELECT DISTINCT customer_id, product_id FROM orders;

GROUP BY ir naudingos funkcijos

Kitas būdas grupuoti rezultatų duomenis yra komanda GROUP BY - ji skirta naudojimui su tam tikromis matematinėmis funkcijomis. Žvelgiant į tą pačią klientų ir užsakymų lentelę, ji mums gali atsakyti į tokius klausimus. Kiek vidutiniškai išleidžia vienas klientas? Kiek užsakymų padaro vidutiniškai? Kuris klientas padarė daugiausiai užsakymų? Ir panašiai - apie viską iš eilės.

GROUP BY yra SELECT komandos dalis, pridedama po WHERE dalies. Nurodoma, pagal kokius laukus grupuoti - jie greičiausiai bus tie patys, kurie išvardinami po SELECT - su vienu skirtumu: paskutinis laukas turi būti funkcija, kuri ir atliks skaičiavimus. Skamba sudėtingai, tad pažiūrėkime į pavyzdį.

SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

Ši paprasta ir trumpa užklausa išves skirtingų customer_id reikšmes, ir prie jų - kiek kartų kiekviena iš jų kartojasi lentelėje, tą suskaičiuoja funkcija COUNT(), kurios parametras yra žvaidždutė, reiškianti visus laukus. Kitais žodžiais - kiek užsakymų yra padaręs kiekvienas klientas.

Kitas pavyzdys šiek tiek sudėtingesnis - ta pati užklausa su JOIN, juk mums pravarčiau žinoti klientų vardus o ne jų ID numerius:

SELECT customers.first_name, COUNT(*) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name;

Ir dar ženkime žingsnį į priekį - sugrupuokime pagal kelis laukus: vardą ir pavardę:

SELECT customers.first_name, customers.last_name, COUNT(*) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name;

Ir paskutinis etapas - surūšiuokime pagal aktyviausius pirkėjus - nuo didžiausio iki mažiausio užsakymų kiekio. Tiesiog pridedame ORDER BY.

SELECT customers.first_name, customers.last_name, COUNT(*) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name
ORDER BY COUNT(*) DESC;

Iki šiol visi grupavimo pavyzdžiai buvo su funkcija COUNT(), kuri suskaičiuoja eilučių skaičių bet yra ir dar daugiau naudingų. Sakykime, jei norime sužinoti, kiek pinigų išleido kiekvienas klientas, galime panaudoti funkciją SUM(X), kur vietoje X įrašome norimo lauko pavadinimą:

SELECT customers.first_name, customers.last_name, SUM(orders.price) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name;

Analogiškai - egzistuoja funkcija AVG(X), skaičiuojanti visų X lauko reikšmių vidurkį:

SELECT customers.first_name, customers.last_name, AVG(orders.price) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name;

Norite pamatyti didžiausią ar mažiausią konkretaus kliento užsakymą? Prašom - yra funkcijos MAX(X) ir MIN(X):

SELECT customers.first_name, customers.last_name, MAX(orders.price) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name;

Štai tiek naudingų grupavimo funkcijų. Bet ir tai dar ne viskas.

HAVING - sugrupuotų rezultatų filtravimas

Iškart pavyzdys - ką daryti, kai norite sugrupuotus rezultatus dar ir atfiltruoti? Sakykime, jei norite iš atrinktų klientų su jų užsakymais atfiltruoti tik tuos, kurie yra užsakę daugiau nei už 300 eurų. Tam po GROUP BY struktūros galime rašyti HAVING sąlygą, kuri veikia panašiai kaip WHERE bet naudoja jau grupavimo funkcijas. Štai taip:

SELECT customers.first_name, customers.last_name, SUM(orders.price) 
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name
HAVING SUM(orders.price) > 300;

Kaip matote, atfiltruojamas tik vienas įrašas iš dviejų, nes kitam klientui užsakymų suma ne viršija 300.

Kas būtų paprasčiau po to dirbti su duomenimis (juos rodyti ir pan.), mes galime suteikti funkcijos rezultato stulpeliui pavadinimą su žodeliu AS:

SELECT customers.first_name, customers.last_name, SUM(orders.price) as orders_amount
FROM orders
JOIN customers ON orders.customer_id = customers.id
GROUP BY customers.first_name, customers.last_name
HAVING orders_amount > 300;

Pats rezultatas, kaip matote, nepasikeitė, bet stulpelio pavadinimas rezultate jau vadinasi orders_amount - jį kaip lauką jau galime naudoti ir HAVING sąlygoje.

Svarbu: nepainiokite HAVING sąlygos su WHERE sąlyga. Nors jos atrodo panašios, bet skirtumas esminis - WHERE atfiltruoja rezultatus prieš grupavimą, o HAVING gali veikti tik po grupavimo ir niekaip kitaip. Dėl to jei parašysite WHERE po GROUP BY - gausite klaidą. Tiesa, WHERE ir HAVING gali būti naudojamos viename sakinyje - sakykime, mes norime atfiltruoti pradžioje tik tam tikros prekės užsakymus, o po to jau juos sugrupuoti ir išvesti klientus, apsipirkusius daugiau nei už 100 eurų.

SELECT customers.first_name, customers.last_name, SUM(orders.price) as orders_amount
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.product_id = 2
GROUP BY customers.first_name, customers.last_name
HAVING orders_amount > 100;

Apibendrinimas

Štai tiek apie grupavimą. Jis reikalingas labiau ataskaitoms ir grafikams braižyti, kai jau yra daug duomenų ir juos reikia sugrupuoti patogiam atvaizdavimui.



(c) 2015-2018. Visais klausimais kreipkitės povilas@laraveldaily.com