Języki i środowiska programowania systemów rozproszonych

1. Języki i środowiska programowania systemów rozproszonych Wykład 8 Operator sortowania order by Operator group by – czy potrzebny? Wykładowca: Tomasz Kowalski Wykłady przygotowane na podstawie materiałów prof. Kazimierza Subiety

2. Sortowanie w językach zapytań • Operator sortowania order by występuje w SQL i jest proponowany w innych językach takich jak ODMG OQL. • Operator ten umożliwia posortowanie wyniku według wybranego klucza. • Sortowanie jest bardzo ważną operacją w językach zapytań z następujących powodów: • Wyprowadzanie wyników wyszukiwania: wynik powinien być posortowany dla zwiększenia jego czytelności i umożliwienia podjęcia szybkiej decyzji. Np. kierownikowi działu może zależeć na posortowaniu wyniku wyszukiwania według malejących zarobków lub według rosnącego wieku. • Sortowanie zwiększa moc języka zapytań. Niektóre zapytania nie mogą być zadane bez sortowania, np. zapytanie „podaj 50-ciu najlepiej zarabiających pracowników”. Zapytania takie są zwane „zakresowymi” (range queries).

3. Definicja operatora order by w SBQL (1) • Operator order by został zaimplementowany w SBQL systemu ODRA. • Niżej objaśnimy (nieco uogólnioną) ideę tego operatora. • Operator ten będzie należał do kategorii nie-algebraicznych. • Przyjmijmy następującą składnię (rozszerzającą podane wcześniej reguły składniowe SBQL): • Semantyka zapytania q1orderbyq2 • Przedmiotem sortowania jest bag lub sekwencja zwrócona przez q1. • q2 wyznacza klucz sortowania. • Wynikiem jest sekwencja elementów zwróconych przez q1, uporządkowana zgodnie z kluczem q2. Zapytanie q2 wyznacza strukturę (ogólnie: bag struktur) struct{v1, v2, ..., vk}, gdzie vi Di, zaś Di V. • Zbiory Di są dziedzinami, w których istnieje naturalny porządek liniowy, np. zbiór liczb całkowitych, zbiór stringów, zbiór dat, itd. zapytanie ::= zapytanieorderby zapytanie

4. Definicja operatora order by w SBQL (2) • Przetwarzanie zapytania q1orderbyq2 odbywa się w sposób następujący: • Oblicza się zależne złączenie q1joinq2 . Jeżeli zapytanie q1 zwróci bag{ r1, r2, ... }, to w wyniku całego zapytania otrzymujemy bag o budowie • Analogicznie jeżeli q1zwróci sekwencję. • Jeżeli któreś z vij jest referencją, to wykonuje się automatycznie dereferencję. • Jeżeli wartość vij nie należy do dziedziny Di V, w której obowiązuje naturalny porządek liniowy, wówczas sytuacja ta jest uważana za błędną. • Dokonuje się sortowania powstałego bagu, które zamienia go na sekwencję. Sortowanie następuje według pierwszego klucza, w ramach identycznych wartości pierwszego klucza – według drugiego klucza, itd. • Po sortowaniu otrzymamy sekwencję: powstałą z poprzedniego bagu poprzez jego posortowanie; • Końcowy rezultat uzyskujemy po rzutowaniu powstałej sekwencji na rezultaty q1. Ostateczny rezultat: sequence{ s1, s2, ...}. bag{struct{ r1, v11, v21, ..., vk1 }, struct{ r2, v12, v22, ..., vk2 },...} sequence{ struct{ s1, vs11, vs21, ..., vsk1 }, struct{ s2, vs12, vs22, ..., vsk2 }, ...}

5. Przykłady (schemat bazy danych) Osoba[0..*] Nazwisko RokUr Wiek() PracujeW Zatrudnia[1..*] Prac[0..*] NrP Stan[1..*] Zar[0..1] ZmieńZar(nowyZar) ZarNetto( ) Dział [0..*] NrD Nazwa Lokacja[1..*] BudżetRoczny() Kieruje[0..1] Szef

6. Przykłady zapytań • Podaj referencje do obiektów wszystkich pracowników posortowane według ich nazwisk: PracorderbyNazwisko • Wynikiem będzie sequence{iPrac1, iPrac2, iPrac3...} identyfikatorów obiektów Prac, posortowanych według nazwisk. • Zwrócimy uwagę, że wykorzystaliśmy dziedziczenie. • Podaj identyfikatory wszystkich pracowników posortowane według ich wieku, zaś w ramach tego samego wieku – według nazwisk: Pracorderby (Wiek(), Nazwisko) • Podaj działy posortowane według liczby pracowników w działach oraz według nazwisk ich szefów; zwróć nazwę działu oraz jego lokacje posortowane alfabetycznie. (Działorderby (count(Zatrudnia), (Szef.Prac.Nazwisko))) . (Nazwa, (((Lokacjaasx) orderbyx).x) group aslokacje)

7. Puste i wielowartościowe klucze • Przedstawiona semantyka operatora orderby posiada kilka niuansów. Pierwszy z nich dotyczy sytuacji, kiedy w zapytaniu q1orderbyq2 podzapytanie q2 zwraca pusty wynik. • Np. w obiektach Prac podobiekt Zar może nie wystąpić. • Wówczas zgodnie z przedstawiona semantyką opartą na zależnym złączeniu zapytanie: PracorderbyZar pominie tych pracowników, dla których Zar nie występuje. • Aby uwzględnić tych pracowników w dostarczonym wyniku, wówczas należy ustalić dla nich klucz sortowania, np. przyjmując, że dla pracowników nie posiadających informacji o zarobku klucz wynosi zero. • Odpowiednie zapytanie (jedno z wielu) może mieć postać: Pracorderbymax( bag(0, Zar) ) • Funkcja max zwróci 0 dla pracowników nie posiadających zarobku i aktualny zarobek dla pozostałych.

8. Klucze wielowartościowe • Podobna sytuacja następuje wtedy, gdy w zapytaniu q1orderbyq2 podzapytanie q2 zwraca wiele wartości. • Np. zapytanie: PracorderbyStan • Zgodnie z przedstawioną semantyką opartą na zależnym złączeniu, identyfikatory pracowników posiadających więcej niż jedno stanowisko będą w wyniku powielone tyle razy, ile dany pracownik ma stanowisk. • Następnie te identyfikatory zostaną posortowane według poszczególnych stanowisk. • Taka interpretacja jest logiczna i konsekwentna. • Oczywiście, może on użyć innych środków aby, dla uniknięcia powtórzeń, ustalić takie q2, które zwróci dla pracownika dokładnie jedno stanowisko, np.: Pracorderby ((((Stan asz) orderbyz).z) [1]) • W tym przypadku programista uporządkował stanowiska pracownika alfabetycznie i wybrał pierwsze stanowisko jako klucz sortowania.

9. Sortowanie w kolejności rosnącej i malejącej • W SQL i OQL do tego celu służą specjalne słowa kluczowe ASC (ascending) i DESC (descending) umieszczone za danym kluczem sortowania (pierwsze z nich jest domyślne). • Obydwie opcje oznaczają funkcje działające na kluczach sortowania. • ASC jest funkcją identyczności (zwraca swój argument). • DESC jest funkcją zwracającą element będący dopełnieniem swojego argumentu zgodnym z określonym porządkiem liniowym. • Przykładowo, dla liczby całkowitej X dopełnieniem jest –X, lub 1000000000 – X, gdzie jest 1000000000 jest (przykładowym) maksymalnym kluczem. • Podobnie, dopełnieniem stringu może być string, w którym każdy znak w ASCII o kodzie k zostaje zastąpiony przez znak o kodzie 256 - k. • Przykład: podaj identyfikatory wszystkich pracowników posortowane malejąco według wieku i rosnąco według ich nazwisk: Pracorderby (DESC(Wiek), ASC(Nazwisko))

10. Zapytania zakresowe • Powinna istnieć możliwość wyboru i-tego elementu sekwencji, ostatniego elementu sekwencji, itd. W najprostszym przypadku można zastosować następującą składnię: zapytanie ::= zapytanie [liczba naturalna] zapytanie ::= zapytanie [liczba naturalna .. liczba naturalna] • Podaj 50-ciu najmniej zarabiających pracowników: (PracorderbyZar)[1..50] • W powyższym zapytaniu pierwszy element otrzymuje numer 1. • Język C i jego pochodne (C++, Java), CORBA, OQL, itd. wprowadzają numerację, w której pierwszy element oznacza się numerem 0. W C/C++ było to umotywowane, ale uzasadnienie dla tego atawizmu znikło. • Bardziej ogólna forma: zapytanie ::= zapytanie [zapytanie] zapytanie ::= zapytanie [zapytanie .. zapytanie] (PracorderbyZar)[x..y]

11. 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. • Mamy tabele Prac( NrP, Nazwisko, Stan, Zar, PracujeW) Dział(NrD, Nazwa, Lokacja, Szef ) • Dla każdego działu podaj jego numer, liczbę pracowników oraz średnią zarobków: SQL: selectPracujeW, count(*), avg(Zar) fromPracgroup byPracujeW • 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.

12. Konsekwencje wprowadzenia 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óry ten predykat zwróci true, są uwzględniane przy obliczaniu klauzuli select. Np.: • Dla każdego działu zatrudniającego więcej niż 50 pracowników podaj jego numer oraz średnią zarobków: SQL: selectPracujeW, avg(Zar) fromPrac group byPracujeW having count(*) > 50

13. Dalsze konsekwencje • 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 ono także nazwę działu, otrzymamy bardziej rozbudowaną formę: • Dla każdego działu zatrudniającego więcej niż 50 pracowników podaj jego numer, nazwę oraz średnią zarobków: SQL: selectPracujeW, Nazwa, avg(Zar) fromPrac, Dział where PracujeW = NrD group byPracujeW, 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 i błędogenny.

14. Semantyczne rafy opcji group by • Wada koncepcyjna: 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: SQL: selectPracujeW, count(*) fromPracgroup byPracujeW następnie przetworzy wynikową tabelę celem 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. • Inna wada: 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. • Np. jeżeli informacja PracujeW przyjęłaby wartość NULL dla niektórych pracowników, to skutek powyższego zdania SQL byłby taki, jakby wszyscy oni pracowali w jednym dziale, zaś liczba grup byłaby o jeden większa od liczby działów. • Średnia liczba pracowników byłaby oczywiście wyliczona błędnie.

15. Wyżej podane zapytania w SBQL Dział . (NrD, count(Zatrudnia), avg(Zatrudnia.Prac.Zar)) (Działwherecount(Zatrudnia) > 50). (NrD, avg(Zatrudnia.Prac.Zar)) (Działwherecount(Zatrudnia) > 50). (NrD, Nazwa, avg(Zatrudnia.Prac.Zar)) • 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. • 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.

16. Recepta na zapytanie w SBQL, które w SQL wymagałoby group by • 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 celem uzyskania ostatecznego wyniku. • Przykład: Dla każdego działu zatrudniającego programistów podaj jego numer, nazwę i średnią zarobków: (((Działwhere „programista”  (Zatrudnia.Prac.Stan)) asd) . (d.NrD, d.Nazwa, avg(d.Zatrudnia.Prac.Zar))

17. Przykłady wykorzystujące podaną receptę (1) • 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): (unique(Prac.Stan) asz) .(z, count(PracwherezStan), count(Działwherez (Zatrudnia.Prac.Stan)) • Pierwsza linia ustala wszystkie stanowiska, usuwa duplikaty i ten dyskryminator nazywa z. Druga i trzecia linia zawiera przetwarzanie tego z. • Recepta ta jest również dobra dla systemów relacyjnych. Np. zdanie SQL: selectPracujeW, avg(Zar) fromPrac group byPracujeW having count(*) > 50 można zapisać jako: SBQL: ((unique(Prac.PracujeW) asp) join ((PracwherePracujeW = p) group as g) wherecount(g) > 50). (p, avg(g.Zar))

18. Przykłady wykorzystujące podaną receptę (2) • 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: (unique(Prac.integerOf(Wiek/5)) asw) . ((((5*w) aswiekDol, (5*w + 4) aswiekGor) 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. • Sformułowanie tego zapytania w SQL okazało się niezłą łamigłówką.

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

20. Podsumowanie • Operator sortowania order by jest konieczny do wyprowadzania wyniku oraz do sformułowania niektórych zapytań, wobec czego powinien być zaimplementowany. • Jest to jedno z wymagań dla języków zapytań dla XML • Respektowanie porządku, w którym przetwarzane są kolekcje, zmniejsza potencjał dla optymalizacji zapytań. • Zatem sekwencje i operator porządkowania są z tego względu niekorzystne i powinno być stosowane tylko tam, gdzie jest niezbędne. • Operator group by w obiektowych językach zapytań jest tworem redundantnym. • Jeżeli przy tym przenosi wady z języka SQL, to powinien być również traktowany jako szkodliwy. • Implementacja tego operatora jest więc pozbawiona cienia sensu.