Bazy
danych
Analityczny SQL dodatkowe
możliwości w dostępie do danych
Jerzy Michałowski
ażdy programista bazy danych Oracle wyko-
rzystujący w swojej pracy SQL zdaje sobie
Ksprawę, że nawet mimo szerokich możliwo-
ści tego języka, istnieją takie obszary, dla których nie
można przygotować zapytania. A nawet, jeśli można,
zapytania te są skomplikowane, o dużym koszcie wy-
konania, co w praktyce dyskwalifikuje ich zastosowa-
nie. Do takich obszarów należą między innymi:
" obliczanie postępującej sumy (tzn. wynikiem jest
suma odpowiedniej wartości bieżącego rekordu
i wszystkich wcześniejszych)
" zapytania wyszukujące N największych / naj-
Rysunek 1. Funkcja analityczna sum
mniejszych wartości
" wyznaczanie rankingów Przykład funkcji analitycznej przedstawiony jest na
" obliczanie wartości na podstawie danego rekordu Rysunku 1. Jak widzimy wynikiem działania tej funk-
oraz N poprzednich / następnych cji jest lista klientów z saldem ich konta, a także sumo-
wanie wiersz po wierszu poszczególnych kwot w ra-
Jednak z drugiej strony, wielu programistów nie zdaje mach oddziału (czyli widzimy tutaj przykład obliczania
sobie sprawy, jakie możliwości niosą kolejne rozszerze- postępującej sumy). Na tej podstawie możemy omó-
nie bazy danych Oracle. Już od wersji 8.1.6. udostępnio- wić poszczególne części składni funkcji analitycznej:
ne zostały funkcje analityczne jako rozszerzenie SQL a.
Zastosowanie funkcji analitycznych w powyższych przy- " function _ name nazwa funkcji analitycznej
padkach powoduje, że przygotowywane zapytania są razem z argumentami. W naszym przypadku
prostsze, a jednocześnie ich wykonanie jest bardziej jest to sum(kwota). Argumentem może być po-
optymalne, niż w przypadku zastosowania klasyczne- jedyncza kolumna lub wyrażenie. W zależno-
go SQL (co nie jest bez znaczenia dla dużych syste- ści od rodzaju, funkcje analityczne mogą mieć
mów operujących na setkach tysięcy rekordów). W tym od zera do trzech argumentów. Funkcji anali-
artykule będę się starał przybliżyć tematykę funkcji ana- tyczne, udostępniane przez Oracle są klasy-
litycznych w SQL ich składnię, rodzaje oraz przykłady fikowane w następujących kategoriach: [1] po-
rozwiązań dla wyżej wymienionych obszarów. rządkujące wyznaczające ranking. Wykorzy-
stywane głównie w zapytaniach wyszukujących
Składnia i klasyfikacja N największych/najmniejszych wartości oraz
Funkcje analityczne mogą być umieszczane jedynie w rankingach. Przykładem takiej funkcji jest
w liście atrybutów klauzuli select lub jako argument row _ number, rank, dense _ rank, [2] okienko-
klauzuli order by. Przyczyną tego jest fakt, że funkcje we kumulujące wartości agregatów. Wykorzy-
analityczne są wykonywane jako prawie ostatnie (przed stywane do obliczania postępującej sumy. Wy-
order by). Operują na zbiorze rekordów wyznaczonych magają podania klauzuli order by. [3] raportu-
już przez połączenia, warunki, grupowanie i klauzulę jące wyliczające udziały. Są to analogiczne
having. Składnia funkcji analitycznej jest następująca: funkcje do funkcji okienkowych (sum, max, itd.),
jednak w ich składni nie podaje się klauzuli or-
FUNCTION_NAME(argument1, argument2, argument3) der by. Dzięki temu, funkcja operuje na wszyst-
OVER ( kich rekordach w grupie i dla każdego rekordu
zwraca tą samą wartość. [4] znajdujące warto-
ści w krotkach o określonej odległości od krot-
) ki bieżącej. Dzięki nim, podczas przygotowania
zapytania nie musimy tworzyć połączenia tabe-
li z samą sobą. Takimi funkcjami są między in-
Autor jest absolwentem kierunku informatyka na Poli-
nymi lead oraz lag [5] statystyczne wylicza-
technice Poznańskiej. Pracuje jako starszy programista
jące zmiany poziomów i inne statystyki. Do tej
w firmie ComArch S.A.
kategorii należą funkcje takie jak var _ pop czy
Kontakt z autorem: Jerzy.Michalowski@comarch.pl
stdev _ pop
34
www.sdjournal.org
Software Developer s Journal 5/2006
Analityczny SQL dodatkowe możliwości w dostępie do danych
Listing 1. Zapytanie wykorzystujące funkcje analityczne Listing 2. Zapytanie bez funkcji analitycznych
SELECT SELECT
oddzial_numer, oddzial_numer,
klient_nazwa, klient_nazwa,
kwota, kwota,
ROW_NUMBER() OVER(PARTITION BY oddzial_numer ORDER BY (SELECT
klient_nazwa) klient_numer, count(kk1.klient_nazwa)
SUM(kwota) OVER (PARTITION BY oddzial_numer ORDER BY FROM konta_klientow kk1
klient_nazwa) suma_kolejnych_oddzial, WHERE kk1.oddzial_numer = kk.oddzial_numer
SUM(kwota) OVER (ORDER BY oddzial_numer, klient_nazwa) AND kk1.klient_nazwa <= kk.klient_nazwa
suma_kolejnych, ) klient_numer,
MAX(kwota) OVER (PARTITION BY oddzial_numer) max_kwota (SELECT
FROM konta_klientow sum(kk2.kwota)
ORDER BY oddzial_numer, klient_nazwa FROM konta_klientow kk2
WHERE kk2.oddzial_numer = kk.oddzial_numer
AND kk2.klient_nazwa <= kk.klient_nazwa
" over słowo kluczowe, które określa, że mamy do czynie- ) suma_kolejnych_oddzial,
nia z funkcją analityczną a nie zwykłym agregatem. (SELECT
" partition by określa, w jaki sposób zbiór rekordów będą- sum(kk3.kwota)
cych wynikiem zapytania ma być podzielony na grupy. W na- FROM konta_klientow kk3
szym przykładzie partycje są tworzone na podstawie nume- WHERE kk3.oddzial_numer < kk.oddzial_numer
ru oddziału. Funkcja analityczna jest wykonywana dla każdej OR (kk3.oddzial_numer = kk.oddzial_numer AND kk3.klient_
partycji oddzielnie (co widać na wynikach zwracanych przez nazwa <= kk.klient_nazwa )
to zapytanie). Jeśli podział nie jest zdefiniowany, to wszystkie ) suma_kolejnych,
rekordy będące wynikiem zapytania są traktowane jako poje- (SELECT
dyncza partycja. Podział na partycje może być oparty o do- max(kk4.kwota)
wolną kolumnę lub wyrażenie. Ponieważ partycje są tworzo- FROM konta_klientow kk4
ne po grupowaniu, dlatego nie są dozwolone dla nich funkcje WHERE kk4.oddzial_numer = kk.oddzial_numer
agregujące. AND kk4.klient_nazwa <= kk.klient_nazwa
" order by definiuje, w jaki sposób dane mają być sortowa- ) max
ne wewnątrz partycji. W naszym przykładzie sortowanie na- FROM konta_klientow kk
stępuje po nazwie klienta tak, aby sumowanie kolejnych kwot ORDER BY kk.oddzial_numer, kk.klient_nazwa
z kont klientów odbywało się w tej samej kolejności, w któ-
rej są oni wyświetlani. Niektóre z funkcji analitycznych ści-
śle zależą od kolejności rekordów w partycji (np. funkcje wy- kordu. Rozmiary okna mogą być oparte zarówno na fizycz-
korzystujące do obliczeń poprzednie lub następne rekordy nej liczbie rekordów, jak i na logicznym przedziale, na którym
lag, lead). Klauzula ta jest obowiązkowa w przypadku funk- operuje funkcja analityczna. Temat zostanie obszerniej omó-
cji okienkowych. wiony w dalszej części artykułu
" windowing _ clause definiuje statyczne lub ruchome okno
danych (wewnątrz partycji). Okno takie określa zakres re- W przykładzie pokazanym na Rysunku 1 jako funkcję analityczną
kordów używanych do wykonania obliczeń dla bieżącego re- zastosowano sum. Każdy programista języka SQL na pewno zna
tą funkcję, jako jedną z klasycznych funkcji agregujących. Jednak
wynik jej działania był inny od tego, które można zobaczyć na
przykładzie. Czym różnią się funkcje sum, max, avg wywoływane
z klauzulą over (czyli jako funkcje analityczne) od zwykłych funk-
cji agregujących? Funkcja agregująca operująca na zbiorze re-
kordów zwraca jako wynik jeden rekord. Natomiast funkcja anali-
tyczna zwraca w wyniku wiele rekordów. W przypadku przedsta-
wionej funkcji sum (z klauzulą over oraz dodatkowo order by), dla
każdego rekordu w grupie wynikiem jest narastająca suma, czyli
suma wartości zawartych w aktualnym rekordzie i wszystkich po-
przedzających. Gdybyśmy zastosowali funkcję sum bez order by,
jako wynik funkcji analitycznej także otrzymalibyśmy wiele rekor-
dów, chociaż dla każdego z nich byłaby to taka sama wartość
suma kwot na kontach w ramach oddziału.
Aatwiej, ale też wydajniej
Rysunek 2. Statystyki i plan wykonania zapytania Zapytania przygotowywane z wykorzystaniem funkcji analitycz-
wykorzystującego funkcje analityczne
nych są dużo prostsze, od analogicznych zapytań napisanych
Software Developer s Journal 5/2006 www.sdjournal.org
35
Bazy
danych
bą znaczne polepszenie wydajności. Na Rysunku 2 przedstawio-
no obliczone statystyki i wyznaczony plan wykonania zapytania
przedstawionego na Listingu 1. Analogiczne informacje dla zapy-
tania z Listingu 2 zawiera Rysunek 3. Od razu widać, że w pierw-
szym przypadku potrzebujemy jednokrotnego pełnego dostę-
pu do tabeli (ang. table access full). Natomiast w zapytaniu bez
funkcji analitycznej, tabela konta _ klientów przeglądana jest aż
cztery razy. Dlatego też, pierwsze zapytanie wykonuje się w cza-
sie 0.8 sekundy, a wyznaczenie tych samych danych za pomo-
cą drugiego: 7 sekund. Porównajmy jeszcze liczbę dostępów do
bloków tabel (ang. consistent gets). W pierwszym zapytaniu po-
trzebowaliśmy tylko 6 dostępów, a w drugim aż 20500. Zysk ze
stosowania funkcji analitycznych jest ogromny.
N największych wartości, rankingi
Stosując klasyczny SQL przygotowanie wydajnych zapy-
tań dla przypadków opisanych we wstępie tego artykułu jest
utrudnione. Jak więc będzie wyglądało rozwiązanie tych przy-
padków z wykorzystywaniem analitycznego SQL a. Oblicza-
nie postępującej sumy zostało już omówione we wcześniej-
szej części artykułu.
Przejdzmy, więc dalej. Zapytanie wyszukujące n najwięk-
szych / najmniejszych wartości. Dla każdego oddziału chcemy
Rysunek 3. Statystyki i plan wykonania zapytania bez funkcji
wyszukać tych klientów, którzy na swoim koncie mają kwoty na-
analitycznych
leżące do 4 najwyższych kwot na kontach klientów. Jak łatwo się
jedynie za pomocą standardowego SQL a. Jako potwierdzenie domyśleć należy zastosować jedną z funkcji porządkujących, któ-
tych słów, porównajmy zapytania przedstawione na Listingu 1 re uszeregują odpowiednio rekordy. Wykorzystamy do tego funk-
i 2. Obydwa mają za zadanie wyszukać te same dane. Wynikiem cję rank. Zapytanie wykorzystujące tą funkcję przedstawione jest
tych zapytań są następujące wartości: na Rysunku 4. Działanie funkcji rank polega na posortowaniu re-
kordów w partycji zgodnie z klauzulą order by i przypisaniu każ-
" numer oddziału, w którym klient ma założone konto demu z nich numeru począwszy od 1. Jeśli kilka rekordów będzie
" nazwa klienta miało tą samą wartość argumentu, według którego następuje sor-
" kwota, jaką posiada dany klient na koncie towanie, wszystkie one otrzymają ten sam numer. W takim przy-
" numer klienta w ramach oddziału, według porządku alfa- padku, w generowanej numeracji powstaje przerwa. Kolejny nu-
betycznego mer zwrócony przez funkcję rank będzie przesunięty - przykłado-
" postępująca suma kwot na kontach w ramach oddziału wo, gdy dwa rekordy otrzymają numer 1, to kolejnym wynikiem
" postępująca suma kwot w całym banku funkcji rank będzie 3 (brak 2), gdyby trzy rekordy otrzymały ten
" największa kwota zgromadzona na kontach klientów w da- sam numer 1, kolejny rekord otrzymałby 4 (brak 3). Dlatego też,
nym oddziale klient Nowak oraz klient Kowalski, ponieważ obydwoje mają na
koncie tą samą kwotę pieniędzy, otrzymali ten sam numer 1. Na-
Na Listingu 1 widzimy, że począwszy od numeru klienta, każda tomiast kolejnemu klientowi została przypisana wartość 3.
z szukanych wartości wyznaczana jest za pomocą odpowied- Zwróćmy jednak uwagę, że nie otrzymaliśmy dokładnie te-
niej funkcji analitycznej. Funkcja row _ number zwraca kolejny nu- go, czego oczekiwaliśmy. Zapytanie miało zwrócić klientów, któ-
mer dla każdego z rekordów w partycji (w tym przypadku party-
cja wyznaczana jest na podstawie numeru oddziału). Postępu-
jące sumy w ramach oddziału i w całym banku wyznaczane są
za pomocą funkcji sum z klauzulami order by i odpowiednio zdefi-
niowanymi partycjami (w przypadku sumowania kwot w ramach
całego banku, nie została podana klauzula partition by, czy-
li cały zbiór rekordów jest jedną partycją, na której działa funk-
cja sum). Zapytanie na Listingu 2 jest dłuższe i bardziej skompli-
kowane. Tam gdzie na Listingu 1 stosowaliśmy funkcje analitycz-
ne, musieliśmy przygotować osobne zapytanie, które wyznacza
potrzebną wartość. Gdyby osoba postronna miała na podsta-
wie zapytania określić, jakie dane są zwracane jako wynik, to pa-
trząc na Listing 2, musiałaby poświęcić dłuższą chwilę na prze-
analizowanie wszystkich jego fragmentów, aby udzielić popraw-
nej odpowiedzi.
Oprócz tego, że stosowanie funkcji analitycznych upraszcza
strukturę zapytania i ułatwia ich analizowanie, niosą one ze so- Rysunek 4. Przykład funkcji rank
36
www.sdjournal.org
Software Developer s Journal 5/2006
Rysunek 5. Przykład funkcji dense_rank
rzy na swoim koncie mają kwoty należące do 4 najwyższych
kwot w ramach oddziału. Natomiast wynikiem jest czworo klien-
tów o trzech najwyższych saldach. Zostało to spowodowane lu-
ką w numeracji funkcji rank - dwóch klientów miało tą samą war-
tość saldo na koncie. Funkcja dense _ rank działa analogicznie do
rank, z tą różnicą, że nie zostawia przerw w przygotowywanym
rankingu. Zapytanie na Rysunku 5 zwraca oczekiwany przez nas
wynik klienci, którzy na swoich kontach zgromadzili cztery naj-
większe kwoty w ramach oddziałów. Ponieważ w oddziale pierw-
szym, najwyższą wartość salda mają dwaj klienci, dlatego wyni-
kiem jest pięć rekordów. Przykład ten nie tylko pokazuje, w jaki
sposób znalezć n największych wartości za pomocą funkcji ana-
litycznych. Przedstawia on również sposób tworzenia rankin-
gów. A gdyby z zapytania usunąć zawężenie, które zwraca jedy-
nie cztery największe salda w ramach oddziału, wynikiem byłyby
typowy ranking lista klientów z przypisanymi miejscami wyzna-
czonymi na podstawie wielkości salda na koncie.
Przydatne funkcje analityczne
l
ratio_to_raport (argument) funkcja wylicza wartość ar-
gumentu w sumie wartości z określonej partycji, jest wykorzy-
stywana do określenia procentowego udziału danego wyraże-
nia w całej partycji. Tak, więc wynikiem tej funkcji jest argument/
sum(argument). Przykładowo, dla partycji, w której suma pewnego
argumentu wynosi 20, dla rekordu z argumentem o wartości 4, wy-
nikiem będzie 4/20, a dla wartości 7, odpowiednio 7/20.
l
ntile(n) funkcja dzieli rekordy w uporządkowanej partycji na
n grup (ang. bucket) i przypisuje każdej z nich liczbę porządko-
wą. Dla n=4 i partycji składającej się z ośmiu rekordów, każda
kolejna para (tzn. pierwszy i drugi, trzeci i czwarty, itd.) otrzy-
ma kolejny rosnący numer. Natomiast w przypadku, gdy licz-
ba rekordów nie dzieli się bez reszty przez n, dodatkowe rekor-
dy zostają rozłożone w jak najwcześniejszych grupach (dla n=4
i dziewięciu rekordów, pierwsze 3 rekordy otrzymają numer 1,
a pozostałe pary kolejne numery)
l
cume _ dist funkcja wylicza pozycję rekordu w partycji. Kolej-
ność może być rosnąca lub malejąca. Wynikiem funkcji są warto-
ści w zakresie od 0 do 1. Dla kolejności rosnącej i partycji składa-
jącej się z pięciu rekordów, pierwszemu zostanie przepisana war-
tość 1/5, drugiemu 2/5 i tak dalej, aż ostatni otrzyma wartość 1.
l
stddev(argument) wylicza odchylenie standardowe dla da-
nego argumentu względem całej partycji.
Software Developer s Journal 5/2006
Bazy
danych
Rysunek 8. Pivot query
Rysunek 6. Funkcje odwołujące się do wartości
Natomiast funkcja lead ma identyczną składnię, ale dotyczy re-
wyznaczonych dla innych rekordów
kordów następnych. Czyli jako przesunięcie podajemy, w któ-
Funkcje lag, lead i inne rym z następnych rekordów ma być szukana wartość wyraże-
Często się zdarza, że programista przygotowujący zapyta- nia, będącego pierwszym argumentem funkcji.
nie, oprócz wartości bieżącego rekordu, musi przedstawić tak- Przykład wykorzystania funkcji lag oraz lead jest przedsta-
że wartości wyznaczone dla wcześniejszych lub następnych re- wiony na Rysunku 6. Z tabeli zawierającej informacje o opera-
kordów. W jakich sytuacjach może zajść taka potrzeba? Na przy- cjach klientów, chcemy uzyskać informacje na temat daty opera-
kład podczas generowania zestawienia operacji wykonywanych cji, a także daty poprzedniej i następnej. Dlatego też w obu funk-
przez klientów na swoich kontach. Standardowy, pojedynczy re- cjach szukanym wyrażeniem jest wartość kolumny data _ opera-
kord zawiera w sobie informacje na temat kwoty i daty operacji. cji, a przesunięcie wynosi 1. Ponieważ nie zdefiniowaliśmy war-
Jednak czasami dla każdej operacji chcemy również przedsta- tości domyślnej, funkcja lag zwróciła null dla pierwszego rekordu
wić datę poprzedniej i następnej (np. po to, żeby analitycy mogli w partycji (zdefiniowanej na podstawie nazwy klienta) - pierwszy
wyznaczyć częstotliwość dokonywanych operacji przez danego rekord nie ma poprzednika, a lead dla ostatniego ostatni rekord
klienta). Bez pomocy funkcji analitycznych, musielibyśmy przy- nie ma następnika. Dla pozostałych rekordów obie funkcje znala-
gotować zapytanie składające się z podzapytań, które to dla każ- zły odpowiednie wartości.
dego rekordu wynikowego ponownie przeszukają tabelę, żeby W zapytaniu na Rysunku 6 zastosowano także funkcję
znalezć odpowiednie wartości z poprzedniego i następnego re- first _ value oraz last _ value. Działają one podobnie do funkcji
kordu wchodzącego do zbioru wyników. A ponieważ bank może lag i lead: jako parametr podajemy wyrażenie, dla którego szu-
posiadać bardzo wielu klientów, którzy nawet kilka razy dziennie kamy wartości. Nie podajemy przesunięcia, gdyż jak można wy-
mogą wykonywać wpłaty lub wypłaty, tak przygotowane zapyta- wnioskować na podstawie ich nazwy, funkcje te zwracają odpo-
nie na pewno wykonywałoby się bardzo długo. Na szczęście ist- wiednie wartości wyznaczone dla pierwszego i ostatniego re-
nieją odpowiednie funkcje analityczne, które umożliwiają dostęp kordu w grupie. I tak, dla klienta Kowalski, dla każdego rekordu
do wcześniejszych lub poprzednich rekordów zwracanych przez w partycji funkcja first _ value(data _ operacji) zwraca datę 05/
zapytanie, bez konieczności wykorzystywania połączenia zwrot- 12/20, gdyż z taką datą została wykonana pierwsza operacja
nego tabeli z samą sobą. Funkcja lag pozwala na dostęp do po- i taką wartość atrybutu data_operacji ma pierwszy z rekordów.
przednich rekordów. Jako argumenty podajemy: Natomiast dla ostatniego rekordu data _ operacji = 06/02/01,
dlatego taki jest wynik funkcji last _ value(data _ operacji). Dla
" wyrażenie (może to być nazwa kolumny), dla którego szu- drugiego z klientów, czyli jednocześnie dla drugiej partycji, funk-
kamy wartości w poprzednich rekordach cje te zwracają odpowiednie wartości wyznaczone na podstawie
" przesunięcie definiujemy, który z poprzednich rekordów nas pierwszego i ostatniego rekordu.
interesuje, domyślnie jest to rekord bezpośrednio poprzedzają-
cy (jeśli jako przesunięcie podamy np. 3 - funkcja lag odnajdzie
Inne bazy danych
rekord, który znajduje się o trzy miejsca wcześniej i jako wynik
przekaże wartość wyrażenia obliczoną dla tego rekordu).
Funkcje analityczne w artykule zostały przedstawione dla wersji języka
" wartość domyślna jaka ma być wartość w przypadku, gdy
SQL wykorzystywanej w bazie danych Oracle. Inne bazy danych rów-
zdefiniowane przez nas przesunięcie wykracza poza grupę
nież udostępniają mniejszy lub większy zbiór użytecznych funkcji anali-
rekordów (dla przesunięcia = 3, wynik domyślny będzie zwró-
tycznych w swoich wersjach SQL a. Wynika to także z tego, że funkcje
cony dla dwóch pierwszych rekordów, a dopiero dla trzeciego
analityczne stały się fragmentem standardu ANSI SQL 1999.
będzie to wartość wyznaczona na podstawie poprzedniego,
" DB2 implementuje funkcje analityczne zgodnie ze standar-
czyli w tym przypadku pierwszego, rekordu na liście)
dem ANSI SQL 1999
" Sybase udostępnia między innymi funkcje porządkujące
(rank, dense _ rank, percent _ rank, cume _ dist), okien-
kowe i raportujące (sum, min, max, avg), a także statystycz-
ne (stddev, variance)
" MS SQL Server 2005 wprowadza szereg porządkujących
funkcji analitycznych, a także operator relacyjny pivot. We
wcześniejszych wersjach MS SQL Server a, programista mu-
siał samodzielnie implementować potrzebne mu funkcje za
pomocą podzapytań.
Rysunek 7. Okna danych
38
www.sdjournal.org
Software Developer s Journal 5/2006
Analityczny SQL dodatkowe możliwości w dostępie do danych
Okno danych Pivot query
Na początku artykułu została przedstawiona składnia funk- Dzięki stosowaniu porządkujących funkcji analitycznych, mo-
cji analitycznych. Do tej pory omówione zostały wszyst- żemy wykorzystywać mechanizm zapytań pivot query (ina-
kie jej części, oprócz fragmentu odpowiadającego za de- czej crosstab query). Zapytania te pozwalają na grupowanie
finiowane okien danych. Jak wygląda skłania klauzuli two- danych w inny sposób niż jest to klasycznie przyjęte. Dzięki
rzącej tą strukturę? Okno danych wewnątrz partycji rekor- nim, mamy możliwość obrócenia zbioru wynikowego wzglę-
dów może zostać oparte na zakresie danych (wtedy stoso- dem jednej kolumny w taki sposób, że kolejne wartości re-
wana jest składnia ze słowem kluczowym range), lub na fi- kordów stają się kolumnami nowego wyniku. Inaczej mówiąc,
zycznej liczbie krotek (stosujemy składnię z rows). Dodat- w prosty sposób grupujemy rekordy w partycji łącząc kilka re-
kowo możemy określić, czy interesują nas rekordy znajdu- kordów w jeden.
jące się przed bieżącym rekordem - precending, lub rekor- Zapytanie przedstawione na Rysunku 5 generuje li-
dy za bieżącym following. W przypadku zakresu danych stę klientów oddziałów wraz z saldem konta i numerem, któ-
definiujemy warunek, jaki muszą spełnić rekord, żeby był ry tworzy ranking najwyższych kwot. Teraz chcemy tak prze-
uwzględniony w działaniu funkcji analitycznej. Gdy defini- transformować wynik, żeby otrzymać po jednym rekordzie dla
cja okna ma postać: order by data _ operacji asc range 14 każdego oddziału, a kolumnami były: numer oddziału, nazwi-
precending, oznacza to, że okno danych tworzy bieżący re- sko klienta o najwyższym saldzie, nazwisko klienta z drugim,
kord, oraz wszystkie wcześniejsze, dla których data opera- co do wielkości saldem i kolejno nazwisko trzeciego klienta.
cji nie jest wcześniejsza niż czternaście dni. W takim przy- Teoretycznie moglibyśmy przygotować odpowiednie zapy-
padku dla ostatniego rekordu okno mogą tworzyć nawet tanie nie znając mechanizmu pivot query, ale musielibyśmy
wszystkie rekordy w partycji. Natomiast, gdy wykorzystu- wyszukiwać każdego z klientów osobnym podzapytaniem.
jemy funkcję analityczną ze składnią order by data _ ope- A dzięki funkcjom analitycznym i pivot query, zmiana sposo-
racji rows 3 precending, wtedy do okno danych, na których bu wyświetlania zajmuje niewiele czasu. Rozwiązanie znajdu-
operuje funkcja, należy bieżący rekordy oraz trzy poprzed- je się na Rysunku 8.
nie (lub mniejsza ich liczba, jeśli bieżący rekord jest pierw- Kiedy warto stosować mechanizm pivot query? Funkcjo-
szym, drugim lub trzecim na liście). nalność ta jest bardzo pomocna, gdy musimy zmienić sposób
Jeśli okno danych nie zostało jawnie określone, a poda- wyświetlania rekordów wynikowych. Zamiast typowej listy ko-
na została klauzula order by, domyślnie tworzone jest okno lejnych rekordów (jeden pod drugim), musimy kilka rekordów
z klauzulą range unbounded precending, które określa okno da- przedstawić w postaci jednego (czyli wyświetlić je w jednej li-
nych rozpoczynające się na pierwszym rekordzie w partycji, nii). Może to być wymuszone na przykład formatem szablonu
a kończące się na bieżącym rekordzie. Właśnie na takim wydruku, w który nie możemy ingerować i który wymaga wła-
oknie danych operowały wcześniej przedstawione przykłady śnie takiego przedstawienia danych.
np. funkcje liczące postępującą sumę.
Zobaczmy, jak zachowują się funkcje okienkowe w zapy- Podsumowanie
taniu, które zwraca listę klientów banku posortowaną rosnąco Do tej pory przedstawiłem kilka różnych typów funkcji anali-
względem kwoty zgromadzonej na koncie. Dla każdego klien- tycznych wraz z przykładami. Niestety nie jest możliwe omó-
ta w ramach oddziału chcemy wiedzieć: wienie w tym artykule wszystkich udostępnianych przez Orac-
le funkcji. Dlatego w Ramce 1 zostało krótko omówionych kil-
" jaka łączna kwota znajduje się na jego koncie oraz na kon- ka przydatnych funkcji analitycznych, które nie pojawiły się do
tach dwóch poprzedników tej pory na żadnym z przykładów.
" ilu klientów ma kwoty równe, lub mniejsze o nie więcej niż W mojej pracy zawodowej spotykałem się z proble-
trzysta złotych mami związanymi z wydajnością, które zostały rozwiąza-
ne poprzez zastosowanie funkcji analitycznych. W ostat-
Tak zdefiniowane wymagania nie są trywialne gdyby- nich miesiącach, podczas konserwacji wdrożonego wcze-
śmy musieli korzystać jedynie z klasycznego SQL a. Nato- śniej systemu, byłem odpowiedzialny za optymalizację
miast zapytanie przedstawione na Rysunku 7 jest bardzo pewnych procedur, które były uruchamiane na koniec każ-
proste. Dla funkcji sum, która wyznacza łączną kwotę na dego miesiąca. Procedury te przygotowywały zestawienie
koncie bieżącego klienta i dwóch wcześniejszych zostało dla zarządu oraz zagranicznych udziałowców. Problemem
zdefiniowane okno oparte na konkretnej liczbie rekordów był czas z pewnych względów (bezpieczeństwo oraz ko-
(rows 2 precending). W przypadku zakresu kwot dla funk- nieczność wykonywania innych czasochłonnych proce-
cji count zdefiniowany jest odpowiedni warunek: range 300 sów) zapytania przygotowujące zestawienie miały dostęp
precending. do bazy danych jedynie przez dwie godziny. Zapytania
operowały na kilku tabelach, z których największa (tak-
że pod względem liczby i wielkości atrybutów), zawiera-
Bibliografia
ła kilkaset tysięcy rekordów. Dla każdego z rekordu, na-
leżało pobrać odpowiednie dane z innych tabel, oraz ob-
" Oracle Expert One on One , Tom Kyte
liczyć odpowiednie wartości (sumy, średnie, itd.). Nieste-
" Analytic SQL Features in Oracle 9i , An Oracle Technical
ty, czas działania był nie do przyjęcia - zestawienie gene-
White Paper
rowało się ponad osiem godzin. Po zastosowaniu materia-
" Oracle Analytic Functions: An Introduction ,
http://www.orafusion.com/art_anlyt.htm lizacji wyników pośrednich oraz funkcji analitycznych, ze-
stawienie było gotowe po godzinie. n
Software Developer s Journal 5/2006 www.sdjournal.org
39
Wyszukiwarka