I.
Wprowadzenie do  języków zapytań (1)
II.
Wprowadzenie do  języków zapytań (2)
III.
Pojęcia obiektowości w bazach danych (1)
IV.
Pojęcia obiektowości w bazach danych (2)
V.
Podstawy semantyczne języków zapytań
VI.
Modele składu obiektów
VII.
Stos środowisk, rezultaty zapytań, funkcja nested
VIII.
Język SBQL (Stack-Based Query Language) (1)
IX.
X.
Dalsze własności SBQL
XI.
Operatory order by i group by
  Wstęp
  1. Operator sortowania order by
  2. Operator group by - czy potrzebny?
  Podsumowanie
  Zadania
XII.
Przetwarzanie struktur nieregularnych
XIII.
Rozszerzenie języków zapytań o konstrukcje imperatywne
XIV.
Procedury, procedury funkcyjne, metody, reguły zakresu
XV.
Parametry procedur i metod, procedury rekurencyjne, optymalizacja poprzez modyfikację zapytań

 

2. Operator group by - czy potrzebny?

Operator group by występuje w SQL i jest proponowany w innych językach, takich jak ODMG OQL. W językach relacyjnych okazał się on użyteczny do formułowania niektórych zapytań, szczególnie takich, które wymagały użycia funkcji agregowanych; np. (patrz Rys.47):

P11.12.
SQL

Dla każdego działu podaj jego numer, liczbę pracowników oraz średnią zarobków:

select PracujeW, count(*), avg(Zar) from Prac group by PracujeW

Semantyka tej konstrukcji jest prosta: tabelę pracowników dzieli się na grupy, w których atrybut PracujeW przyjmuje tę samą wartość; następnie dla każdej takiej grupy oblicza się wyrażenie znajdujące się w klauzuli select.

Wbrew twierdzeniom spotykanym w niektórych podręcznikach dotyczących systemów i modelu relacyjnego, operator group by w ogólnym przypadku nie jest wyrażalny przez inne operatory języka SQL i tym bardziej jest niewyrażalny w algebrze relacji, w szczególności, z powodu związania z nią semantyki innych własności SQL  Istotą tego operatora jest to, że na chwilę tabelę relacyjną traktuje się tak, jakby miała trzy poziomy hierarchii: poziom tabeli, poziom grup w tej tabeli oraz poziom krotek. Jest to (chwilowe) semantyczne odstępstwo od założeń modelu relacyjnego, wobec czego można mieć prawie pewność, że jeżeli dany formalizm posiada wyłącznie takie operatory, których wejściem są "płaskie" relacje i wyjściem jest "płaska" relacja, to nie jest on w stanie wyrazić semantyki operatora group by.

Wraz z pojawieniem się operatora group by pojawiła się konieczność wprowadzenia możliwości selekcji niektórych grup wyodrębnionych przez ten operator. Do tego celu służy specjalna klauzula having, która zawiera predykat operujący na grupach; tylko grupy, dla których ten predykat zwróci true, są uwzględniane przy obliczaniu klauzuli select. Przykładowo:

P11.13.
SQL

Dla każdego działu zatrudniającego więcej niż 50 pracowników podaj jego numer oraz średnią zarobków:

select PracujeW, avg(Zar) from Prac group by PracujeW  having count(*) > 50

Operator grupowania nie jest już tak oczywisty w przypadku złączeń, gdy w klauzuli from znajduje się więcej niż jedna nazwa tabeli. Dorzucając do powyższego zapytania wymaganie, aby zwróciło także nazwę działu, otrzymamy bardziej rozbudowaną formę:

P11.14.
SQL

Dla każdego działu zatrudniającego więcej niż 50 pracowników podaj jego numer, nazwę oraz średnią zarobków:

select PracujeW, Nazwa, avg(Zar) from Prac, Dział
where PracujeW = NrD  
group by PracujeW, Nazwa having count(*) > 50

SQL wymaga, aby dowolny atrybut występujący w klauzuli select i nie objęty funkcją agregowaną był wymieniony w klauzuli group by. Istnieją dalsze niuanse syntaktyczne i semantyczne klauzuli group by oraz klauzul select i having, które są z nią związane. Wbrew pozornej oczywistości, operator ten okazał się z pragmatycznego punktu widzenia dość trudny dla użytkowników.

Twórcy standardu ODMG [ODMG00] próbowali przenieść wzorzec syntaktyczny i semantyczny operatora group by z języka SQL na OQL oraz uogólnić go dla wprowadzonego przez nich modelu obiektowego. Zrobili to jednak w sposób mało precyzyjny, przez co semantyka i pragmatyka tej konstrukcji jest niejasna dla ogólnego przypadku i na pewno niekompletna. Wydaje się, że wprowadzenie tej klauzuli do OQL było motywowane względami koniunkturalnymi, mianowicie lansowaną  (nieprawdziwą) tezą, że OQL jest "niewielkim rozszerzeniem" SQL. Powielanie niekompletnych wyjaśnień i przykładów zawartych w tym standardzie mija się z celami tej książki.

Krytyka opcji group by ma historię co najmniej 20-letnią. Opcja ta była silnie przywiązana do mechanizmu implementacyjnego, w związku z tym optymalizacja zapytań z tą opcja była (i jest do dzisiaj) problematyczna, mimo wielu prac na ten temat. Opcja ta nie jest w pełni ortogonalna z innymi operatorami języka SQL, w szczególności, jeżeli zapytanie SQL zawiera group by, to zmienia się semantyka klauzuli select. Podobny brak ortogonalności dotyczy związania z tą opcją funkcji agregowanych. Powoduje to ich podwójną semantykę (przy tej samej składni): funkcje agregowane bez opcji działają na całych tabelach (ewentualnie na ich wyselekcjonowanych fragmentach), zaś w połączeniu z group by - na grupach. Obecność dwóch różnych semantycznie klauzul warunku (wherehaving) jest co najmniej nieelegancka i oznacza redundancję koncepcyjną. Opcja group by jest również krytykowana za złamanie zasady koncepcyjnej kontynuacji, która żąda małych zmian zapytania w przypadku małych zmian w jego nieformalnym sformułowaniu. Przykładowo, w ostatnim nieformalnym sformułowaniu zażądaliśmy tylko, aby w wyniku była jeszcze nazwa działu. Okazało się, że ta drobna zmiana zaowocowała nowymi konstrukcjami, zaś liczba elementów leksykalnych w tym zapytaniu, w stosunku do poprzedniego, wzrosła niemal dwukrotnie.

Zauważono też poważną wadę koncepcyjną: jeżeli pewna grupa ma zero elementów, wówczas nie uczestniczy w przetwarzaniu, co prowadzi do błędnych wyników. Przykładowo, programista chcący policzyć średnią liczbę pracowników w działach użyje zdania:

P11.15.
SQL

select PracujeW, count(*) from Prac group by PracujeW

a następnie przetworzy wynikową tabelę w celu obliczenia średniej. Niestety, ta tabela nie zawiera informacji o działach z zerową liczbą pracowników, w związku z czym wynik będzie błędny; co więcej, błąd ten pojawi się bez ostrzeżenia (odkrywca tego błędu słusznie nazwał go "rafą semantyczną"). W połączeniu z wartościami NULL opcja group by tworzy dalsze rafy semantyczne. Opcja ta grupuje wartości NULL w ramach jednej grupy, co jest sprzeczne z założeniem twórców SQL, że wartości NULL oznaczają wartości nieznane (zatem być może różne) lub nierelewantne. Przykładowo, jeżeli informacja PracujeW przyjęłaby wartość NULL dla niektórych pracowników, to skutek powyższego zdania SQL byłby taki, jakby wszyscy pracowali w jednym dziale, zaś liczba grup byłaby o jeden większa od liczby działów. Średnia pracowników byłaby oczywiście policzona, zaś mało doświadczony programista nie dowiedziałby się nawet, że jego metoda liczenia zawiera poważny błąd koncepcyjny. Ten efekt jest skutkiem braku troski o szczegóły koncepcyjne i semantyczne ze strony twórców SQL (a pamiętamy, że każdy, nawet najmniejszy problem semantyczny, jest ogromnym problemem). Wobec tego rodzaju wad koncepcyjnych wszelkie twierdzenia o "pełnej formalizacji" tej opcji są niewiarygodne. Nie można poprawnie sformalizować czegoś, co jest niespójne od strony koncepcji.

Języka SQL nie zmienimy, więc powyższa krytyka jest już krytyką historyczną. Opcja ta przeszła do SQL-99, ale uważamy, że szkoda czasu na dyskusję i wyjaśnianie zastosowanych tam rozwiązań, ponieważ szansa, że będą one implementowalne (w spójny sposób), jest bliska zeru. Możemy natomiast zastanawiać się, czy i w jaki sposób wprowadzić opcję group by do projektowanego przez nas języka. Autor spędził kilka tygodni próbując tak doszlifować tę opcję w ramach podejścia stosowego, aby pozbyć się wymienionych wyżej wad. Konkluzja jest następująca:

W obiektowych językach zapytań operator group by jest zbędny. Niesposób wskazać racjonalny powód dla jego wprowadzenia. Można go bez trudu zastąpić przez operator kropki,  zależnego złączenia i inne operatory.

Operator jest zbędny, ponieważ znika przyczyna będąca powodem jego wprowadzenia, mianowicie, chwilowe traktowanie dwupoziomowej struktury danych (tabeli) jako struktury trzypoziomowej. Języki obiektowe nie ograniczają liczby poziomów hierarchii struktur danych i wyników zwracanych przez zapytania, zatem grupowanie można bez najmniejszego trudu zastąpić przez operatory obsługujące takie hierarchiczne struktury. Sformułujemy w SBQL wyżej podane zapytania (patrz Rys.55):

P11.16.

Dział . (NrD, count(Zatrudnia), avg(Zatrudnia.Prac.Zar))

P11.17.

(Dział where count(Zatrudnia) > 50).
(NrD, avg(Zatrudnia.Prac.Zar))

P11.18.

(Dział where count(Zatrudnia) > 50).
(NrD, Nazwa, avg(Zatrudnia.Prac.Zar))

Zwrócimy uwagę, że podane zapytania wyglądają naturalnie, zaś zasada koncepcyjnej kontynuacji jest w pełni zachowana (porównaj dwa ostatnie zapytania). Nie występuje też wspomniana rafa semantyczna dotycząca pustych grup: jeżeli któryś dział nie będzie miał pracowników, to count(Zatrudnia) zwróci dla niego 0, co jest zgodne z oczekiwaniem programisty. Druga rafa semantyczna związana z wartościami zerowymi nie może wystąpić, ponieważ modele składu i SBQL w ogóle nie wprowadzają wartości zerowych (motywy dla tej decyzji są objaśnione w Rozdziale 3). Ponieważ nie ma opcji group by, niepotrzebne są też dedykowane dla tej opcji metody optymalizacyjne. Niepotrzebne są też oczywiście specjalne warunki, które w opcji group by znajdują się w klauzuli having. Przerobienie kilkudziesięciu przykładów przekonało ostatecznie autora, że wprowadzenie operatora group by w języku zapytań bazującym na podejściu stosowym jest kompletnie pozbawione sensu.

Jedna z wielu recept na sformułowanie dowolnego zapytania, które (pozornie) wymaga operacji grupowania, jest następująca:

  • Ustalamy dyskryminator grup, tj. takie podzapytanie, którego wynik będzie podstawą podziału pewnych zasobów danych na grupy.

  • Nazywamy dyskryminator pewną nazwą d poprzez operator as.

  • Dyskryminator wraz z nazwą d obejmujemy nawiasami i po operatorze kropki formułujemy drugie podzapytanie, które dla danej wartości d ustala odpowiadającą jej grupę. Krok ten można pominąć.

  • Stosujemy dowolne operatory działających na d lub wyodrębnionych w poprzednim kroku grupach w celu uzyskania ostatecznego wyniku.


Przykłady (wykorzystujące powyższą receptę):

P11.19.

Dla każdego działu zatrudniającego programistów podaj jego numer, nazwę i średnią zarobków:

(((Dział where "programista" Î (Zatrudnia.Prac.Stan)) as d) .
(d.NrD, d.Nazwa, avg(d.Zatrudnia.Prac.Zar))


P11.20.

Dla każdego stanowiska podaj liczbę pracowników, którzy je posiadają oraz liczbę działów, w których ci pracownicy są zatrudnieni (atrybut Stan jest wielowartościowy):

(distinct(Prac.Stan) as z) .
(z, count(Prac whereÎ Stan),
count(Dział whereÎ (Zatrudnia.Prac.Stan))

Pierwsza linia ustala wszystkie stanowiska, usuwa duplikaty i ten dyskryminator nazywa z. Druga i trzecia linia zawiera przetwarzanie tego z.

P11.21.

Dla grup wiekowych pracowników .., 15-19, 20-24, 25-29, .... podaj średni zarobek oraz różnicę pomiędzy maksymalnym i minimalnym zarobkiem; pomiń grupy wiekowe, w których nie ma żadnego pracownika:

(distinct(Prac.integerOf(Wiek/5)) as w) .
((((5*w) as wiekDol,  (5*w + 4) as wiekGor) join
(Prac where Wiek > wiekDol and Wiek < wiekGor) group as g).
(wiekDol, wiekGor, avg(g.Zar), max(g.Zar) - min(g.Zar)))

Pierwsza linia dzieli wiek pracowników przez 5, bierze od tego część całkowitą, usuwa duplikaty i ten dyskryminator nazywa w (nie uwzględnia grup wiekowych, w których nie ma żadnego pracownika). Druga linia ustala dolny i górny wiek dla każdej grupy. Trzecia linia ustala grupy referencji do obiektów pracowników znajdujących się w tym przedziale wiekowym i każdą taką grupę nazywa g. Złączenie czyni nazwy wiekDol, wiekGor oraz g widocznymi dla dalszej części zapytania. Ostatnia linia dokonuje obliczeń wyniku.

Przykład P11.21 pokazuje użycie omówionego wcześniej operatora group as. Pojawił się on jako skutek drobiazgowej analizy semantyki i pragmatyki tego rodzaju przykładów na grupowanie. Okazało się jednak, po pierwsze, że w większości tego rodzaju przykładów nie jest niezbędny, zaś po drugie, że jest  niezbędny w innych kontekstach, np. wtedy, gdy chcemy wynik zapytania zbudować w postaci hierarchii nazwanych, zagnieżdżonych podwyników. Z tego powodu operatory group as oraz group by nie mają ze sobą istotnego związku, mimo pewnych zależności genealogicznych.

Copyrights © 2006 PJWSTK
Materiały zostały opracowane w PJWSTK w projekcie współfinansowanym ze środków EFS.