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.

zagadka – zapytania skorelowane ciąg dalszy

Na jednym z blogów znalazłem ciekawą zagadkę:

zagadka o bazach

SELECT
*
FROM
tab_a
WHERE
pole_a IN ( SELECT pole_a FROM tab_b );

Co wyświetli takie zapytanie?

Jak na ironie wyświetli wyniki i to niestety na pierwszy rzut oka sensowne.

Jednak jeśli się przyjrzeć widać w tym zapytaniu duży błąd.

Dzięki mechanizmowi zapytań skorelowanych mamy możliwość łączenia wyników zapytania z podzapytaniem. Jednak w tym wypadku mamy do czynienia z czymś na wzór:

select ‘cos’ from tabela

zapytanie zwróci n wierszy z ‘cos’ gdzie n to ilość wierszy w tabela.

Jednakże bardziej roztropni zrobią coś takiego:

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT a.pole_a FROM tab_b b );

i teraz błąd już łatwo dostrzec, ale nadal zapytanie wykona się prawidłowo.

SELECT
*
FROM
tab_a a
WHERE
pole_a IN ( SELECT b.pole_a FROM tab_b b );

Dopiero teraz dostaniemy błąd (o ile pole_a nie istnieje w tab_b)

Teraz mała kwestia optymalizacyjna

spotkałem się z tym iż w podzapytaniu funkcji in () niektórzy używają distincta.

select a from tab where tab_a in (select distinct b from tab_b )

Jest to kompletnie bez sensu, w przypadku baz Oracle hasło distinct zostanie pominięte i nie wpłynie na koszt zapytania.

Ale jeśli ktoś wpadnie na pomysł

select a from tab where tab_a in (select b from tab_b
group by b)

to koszt zapytania już wzrośnie.

Left outer join vs zapytanie skorelowane

Dość ciekawym polem do optymalizacji zapytań jest możliwość wykorzystania zapytań skorelowanych.

select col_a, col_b from tab_a a
left outer join tab_b b on
a.id = b.id

vs

select col_a, ( select col_b from tab_b b where a.id=b.id) from tab_a a

W przykładzie testowanym na bazie produkcyjnej okazało się że użycie zapytań skorelowanych dało 18% zysku w stosunku do left outer joinów. W testowanym przykładzie była joinowana ta sama tabela (słownikowa) 5 razy i za każdym razem były z niej pobrane 3 kolumny.

Widać iż ilość kolumn potrzebnych w zapytaniu finalnym będzie miało wpływ na koszt zapytania, jednakże w niektórych przypadkach może to być całkiem ciekawy sposób na optymalizacje.

Jak twierdzi Dan Tow w swojej książce SQL optymalizacja, opłaca się optymalizować od zysku 20% w górę, jednak osobiście uważam że w skali całej aplikacji z ziarenek może uzbierać się znaczna różnica w wydajności.