Podstawowa składnia dyrektywy GROUP BY

1. Język SQL ma ciekawe możliwości tworzenia zapytań. Ciekawe możliwości daje dyrekytywa GROUP BY. Na przykładzie DBMS MySQL i bazy danych o strukturze zapożyczonej z projektu mediawiki oraz danych pl.wikibooks.org pokaże w jaki sposób można wykorzystać dyrektywe GROUP BY.

2. Podstawowa składnia dyrektywy GROUP BY • Dyrektywa GROUP BY występuje po dyrektywie FROM lub bezpośrednio po WHERE jeśli taka występuje. Prosta składnia opisana jest poniżej: • SELECT pola FROM tabla [WHERE warunki] GROUP BY kolumna,kolumna; • Doskonale wraz z tą dyrektywą spisują się wszelkie funkcje agregujące. Są nimi m.in. SUM, COUNT, MIN, MAX oraz AVG. Służą one kolejno do sumowania wartości, zliczania rekordów, wybierania miniumum oraz maksimum, oraz zliczania średniej.Dzięki użyciu GROUP BY możemy porgrupować te wartości. • Przykład - zliczanie artykułów • W aplikacjach typu wikiwiki najważniejszą funkcję pełnią artykuły. W systemie mediawiki zostały one podzielone na tak zwane przestrzenie nazw.Każda przestrzeń nazw odpowiada innej kategoriarykułów (np przestrzeń 0 - artykuły zwykłe, 1 - rozmowy, 2 - userzy, itd...). Pokaże teraz jak szybko zliczyć liczbę artykułów. Do tego służy poniższe zapytanie. • select cur_namespace, count(*) as liczba_artow from cur group by cur_namespace; • Zapytanie to, zwróci listę przestrzeni nazw wraz ze zliczonymi artykułami. Oto przykładowa wynik zapytania:

3. +---------------+--------------+ | cur_namespace | liczba_artow | +---------------+--------------+ | 0 | 1228 | | 1 | 79 | | 2 | 78 | | 3 | 179 | | 4 | 53 | | 5 | 9 | | 6 | 10 | | 7 | 2 | | 8 | 919 | | 10 | 404 | | 11 | 4 | | 12 | 5 | | 14 | 89 | | 15 | 2 | +---------------+--------------+ 14 rowsin set (0.00 sec)

4. Ładnie byłoby, gdyby udało się jeszcze posortować wszystko malejąco względem liczby artykułów. W tymceluwystarczydodaćklauzule ORDER BY. select cur_namespace, count(*) as liczba_artow from cur group by cur_namespace order by liczba_artow DESC; Oczywiście dyrektywa DESC zapewnia posortowanie malejąco. A co stało by się jeśli chcielibyśmy ograniczyć wyświetlane rekordy. Gdybyśmy chcieli wyświetlić tylko przestrzenie nazw o numerach mniejszych niż 5 można do tego posłużyć się dyrektywą where. Jednak jeśli chcemy posłużyć się funkcją agregującą (np. kolumną liczba_artow) MySQL zwróci błąd. Aby osiągnąć porządany rezultat należy użyć dyrektywy HAVING.

5. Dyrektywa HAVING • Dyrektywa HAVING zawsze występuje za dyrektywą GROUP BY. Składnia zapytania będzie wyglądała następująco: • SELECT kolumny FROM tabla GROUP BY kolumna HAVING warunek [ORDER BY kolumna [ASC|DESC]] • Wyświetlmy tylko te przestrzenie nazw, które mają więcej niż 50 artykułów. Oto zapytanie wraz ze zwróconym wynikiem. • selectcur_namespace, count(*) as liczba_artowfromcurwhere cur_namespace<5 group by cur_namespacehaving liczba_artow>50 order by liczba_artow DESC; +---------------+--------------+ | cur_namespace | liczba_artow | +---------------+--------------+ | 0 | 1228 | | 3 | 179 | | 1 | 79 | | 2 | 78 | | 4 | 53 | +---------------+--------------+ 5 rowsin set (0.01 sec) • Jak widać zapytanie zwróciło oczekiwane wartości.

6. Przykład ze zliczaniem znaków w tekscie • Załóżmy, że chcielibyśmy się dowiedzieć, która kategoria jest najobszerniejsza. Policzymy liczbę znaków w całej przestrzeni nazw. Aby to zrobić musimy zapoznać się z funkcją CHAR_LENGTH. Przyjmuje ona jako argument kolumnę, a zwraca liczbę znaków w danym polu. Wraz z funkcjąSUM i dyrektywą GROUP BY zliczymy całkowitą liczbę znaków w danej przestrzeni nazw. Wynik ograniczymy tylko do 5 największych używając klauzuli LIMIT. • select cur_namespace, sum(char_length(cur_text)) as sum_len from cur group by cur_namespace order by sum_len DESC LIMIT 5;+---------------+---------+ | cur_namespace | sum_len | +---------------+---------+ | 0 | 1476111 | | 4 | 243888 | | 3 | 224856 | | 1 | 96712 | | 8 | 55802 | +---------------+---------+5 rows in set (0.03 sec)

7. Zamiast funkcji SUM, możemy zastosować inne funkcje. Użycie funkcji MINzwróci nam długość najkrótszego artykułu w danej przestrzeni, MAX długość najdłuższego, a AVG średnią długość. • Od wersji MySQL 4.1.1 można zastosować także klauzulę WITH ROLLUP, która doda jeden dodatkowy rekord sumujący wszystkie wyswietlone wyżej rekordy. Jak taki przykład może wyglądać. • selectcur_namespace, sum(char_length(cur_text)) as sum_lenfromcur group by cur_namespacewithrollup order by sum_len DESC; +---------------+---------+ | cur_namespace | sum_len | +---------------+---------+ | 0 | 1476111 | | 4 | 243888 | | 3 | 224856 | | 1 | 96712 | | 8 | 55802 | | 2 | 32360 | | 10 | 29515 | | 12 | 15373 | | 5 | 14281 | | 14 | 6340 | | 11 | 2271 | | 7 | 1537 | | 15 | 769 | | 6 | 262 | | NULL | 2200077 | +---------------+---------+

8. Zakończenie • MySQL daje ogromne możliwości manipulacji danymi. Możliwości wykorzystania dyrektywy GROUP BY jest bardzo dużo. Dyrektywa ta jest niezwykle przydatna do robienia wszelakich statystyk. Zachęcam to własnych testów i prób.   Pozdrawiam kolo175 z zadane.pl