Podstawy grupowania

Ostatnio na grupie dyskusyjnej pl.com.bazy_danych przewinął się wątek grupowania, wielu początkującym osobom grupowanie sprawia wiele problemów. Z resztą zaawansowanym użytkownikom też zdarzają się wpadki.

Tak więc zaczynając od początku czym jest grupowanie?

Gdy w jednej tabeli mamy kolumny o różnej ziarnistości, np. unikalną wartość klucza głównego i np. typ dokumentu lub płeć lub kolor.

weźmy za przykład tabele przyjaciele

create table przyjaciele (
id int, -- unikalny id
imie varchar2(255),
plec int, -- 0 dziewczyna, 1 facet
wiek int, -- wiek w latach
zarobki int, -- zarobki w tysiącach
kolor int); -- kolor skory 0 jasny 10 ciemny - skala

Przyjmijmy że mamy ok. 1000 przyjaciół i chcielibyśmy przeprowadzić analizę naszych znajomych korzystając z grupowania.

Co da nam grupowanie? Pozwoli nam na wrzucanie do jednego worka określonej grupy.
Podchodząc dość rynkowo do naszych przyjaciół moglibyśmy sprawdzić jaki jest ich średni status finansowy

select avg (zarobki) from przyjaciele;

dostaniemy jeden wiersz zawierający ogólną informację ze średnią zarobków naszych kamratów.
Jeśli chcielibyśmy sprawdzić czy lepiej zarabiają nasze koleżanki czy koledzy należy wykorzystać
klauzulę group by i pogrupować dane według płci.

select avg(zarobki) from przyjaciele
group by plec;

w wyniku otrzymamy dwa wiersze ze średnimi wartościami, nie wiemy jeszcze kto jest lepszą grupą.

select avg(zarobki),plec from przyjaciele
group by plec;

teraz dostaniemy informacje o zarobkach per płeć.

Ciekawą sprawą jest dodawanie stringa do zapytania. Normalnie każda kolumna wymieniona w sekcji select nie objęta funkcją agregującą powinna znaleźć się w sekcji group by. Jednak jeśli wytwarzamy sami jakąś kolumnę nie powiązaną z danymi (string,data itp) nie podlega ona tej regule.

select 'moi znajomi srednio zarabiaja ' || count(avg) from przyjaciele

Warto też zwrócić uwagę na znak concatenacji(konkatenacji?), według ANSII (albo tego co udało mi się znaleźć) to znak ten nie jest objęty żadną normą. I tak w Oracle mamy || w MSSQL + a w MySQL można zapisać po prostu dwa stringi obok siebie
‘napis 1’ ‘ napis 2’. Jeśli komuś leży na sercu przenośność kodu (albo korzysta z wielu RDBMS) polecam funkcję concat.

Wracając do grupowania. W powyższych przykładach korzystaliśmy albo z worka zwanego przyjaciółmi, albo rozbijaliśmy nasz worek na dwa mniejsze worki naszych przyjaciół segregując ich po płci.

W jednym zapytaniu możemy korzystać z wielu funkcji agregujących i z wielu kolumn grupujących,
tak więc:

select avg(zarobki),avg(wiek),plec from przyjaciele
group by plec;

dodatkowo możemy podejrzeć średni wiek naszych przyjaciół:

select avg(zarobki),max(zarobki),min(zarobki),avg(plec) from przyjaciele;

teraz możemy przejrzeć zakres zarobków ich średnią i sprawdzić czy mamy więcej przyjaciół czy też przyjaciółek.

select max(zarobki) from przyjaciele
group by kolor,plec

teraz mamy pewna wariacje,
w wyniku dostaniemy ilość wierszy odpowiadającą iloczynowi ilości koloru i płci w tabeli
czyli maksymalnie w naszym przykładzie 20 (zakładając że mamy 2 płci i 10 kolorów).

Właściwość ta jest często dość przykrą konsekwencją i wymusza często stosowanie podzapytań i złączeń składających się z podzapytań w celu uniknięcia błędnego grupowania (zmiany ziarnistości wyniku).

Grupowanie możemy przeprowadzić także po pewnej operacji na kolumnie, np.

select count(*),imie from przyjaciele
group by imie

zwróci nam ilość przyjaciół o określonym imieniu.
Jeśli chcielibyśmy zobaczyć jak się rozkłada ilość przyjaciół, których imię zaczyna się na określoną literę
możemy przeprowadzić coś takiego:

select count(*),susbstr(imie,0,1) from przyjaciele
group by susbstr(imie,0,1)

jednak:

select count(*),susbstr(imie,0,2) from przyjaciele
group by susbstr(imie,0,1)

zwróci nam już błąd co jest nawet logiczne, ale:

select count(*),susbstr(imie,0,1) from przyjaciele
group by susbstr(imie,0,2)

też nam zwróci błąd co niekoniecznie musi być logiczne, tak więc trzeba uważać. Powyższe przykłady działają na Oracle w wersji 10G. Nie wydają mi się zgodne z ANSII (muszę sprawdzić), mile widziany feedback czy to działa na innych bazach.

W temacie grupowania istnieje także wątek unikalnych wartości. Grupując po jednej kolumnie dostaniemy jej unikalne wartości:

select imie from przyjaciele
group by imie

taki sam efekt tylko teoretycznie niższym kosztem uzyskamy

select distinct imie from przyjaciele

Na tabeli z kilkoma tysiącami rekordów na nie indeksowanych polach nie ma praktycznie żadnej różnicy w koszcie tych dwóch zapytań (na danych z różną ziarnistością).

Oczywiście grupowanie może być użyte przy wykonywaniu złączeń. Wszystkie zachowania pozostają zgodne z powyższymi założeniami.

Leave a Reply

Your email address will not be published. Required fields are marked *

15 − 4 =