Kwalifikacja funkcji okienkowych, czyli …

Funkcje okienkowe do standardu SQL zostały wprowadzone w 2003 roku, czyli mówimy o rozwiązaniu, które towarzyszy nam już ponad 2 dekady, choć oczywiście praktyczna implementacja tego konceptu w konkretnych systemach zarządzania bazami danych mogła się odbywać w różnym czasie. Na ten przykład we flagowym systemie “made in Oracle” możliwość używania tychże (choć w formie okrojonej w stosunku do tego, co znamy obecnie) pojawiła się już w wersji 8i (rok 1997), czyli na kilka lat przed dodaniem tego rodzaju funkcji do standardu SQL. Z drugiej strony taki postgress zaprezentował to jako swoistą nowość dopiero w 2009 roku.

Tyle rysu historycznego, ważniejsze w tym wszystkim jest bowiem, iż to szalenie pomocne rozwiązanie szczególnie dla analityków danych – dlatego nie bez kozery w przypadku Oracle’a mówi się o funkcjach analitycznych (analytic functions) miast okienkowych – ponieważ pozwalają na realizację skomplikowanych operacji w obrębie zbioru wynikowego dla naszego zapytania, ale bez konieczności chociażby posługiwania się podzapytaniami czy tworzenia tymczasowych tabel. Nie za bardzo chciałbym tracić czas na wyjaśnianie na czym w tym wypadku zabawa polega i dlatego na potrzeby tego tekstu przyjmuję, że czytający ma przynajmniej mgliste – mniej lub bardziej – pojęcia o czym jest mowa. Dla tych, którzy tego warunku nie spełniają polecam ten wpis przed dalszą lekturą.

Osobiście po te rozwiązanie sięgam najczęściej  – by nie powiedzieć, że właściwie wyłącznie – w przypadku:

• tandemu funkcji LEAD oraz LAG, które de facto są nie do pomyślenia bez tej konstrukcji (z resztą chyba one właśnie były jednymi z pierwszych zastosowań tej konstrukcji w systemie Oracle’a), zaś ich znajomość jest wiedzą z rodzaju “must have”, ponieważ w kontekście pewnych specyficznych zadań osiągnięcie spodziewanego wyniku innymi środkami mogłoby być szalenie uciążliwe,
• dla różnych “normalnych” funkcji agregujących, choć sam chyba najczęściej używam do tego funkcji COUNT, by zliczyć wystąpienia rekordów w oparciu o jakieś grupowanie (na ten przykład aby upewnić się, czy przypadkiem wynikowo nie otrzymałem nadmiarowych danych w postaci niepożądanych duplikatów),
• funkcji rankingowych w rodzaju RANK czy DENSE_RANK czy ROW_NUMBER.

Tak po prawdzie to chyba ten ostatni przypadek jest najczęstszym scenariuszem, który wydarza się z moim udziałem i to z dość dużą regularnością. Chodzi w tym miejscu o wszystkie sytuacje, gdzie interesują mnie dane szczegółowe na temat pewnego wydarzenia/obiektu, ale przy tym nie jestem zainteresowany każdym przypadkiem, ale tylko takim, którego wystąpienie miało miejsce jako pierwsze lub ostatnie (kolejność wyznaczana w oparciu o założone z góry kryterium). By wyjaśnić co mam na myśli posłużę się przykładem: otóż załóżmy, że mam do dyspozycji bazę danych zawierającą informacje o wszystkich transakcjach dokonywanych przez klientów na jednej z witryny internetowych oraz pilną potrzebę chwili dotyczącą popełnienia zestawienia, w którym będzie można prześledzić szereg parametrów na temat zakupów dokonanych przez poszczególnych użytkowników rzeczonego portalu. Tym niemniej z pewnych powodów (wcale nie wydumanych) interesują nas te informacje w kontekście wyłącznie ostatniego zakup dla każdego klienta z osobna. 

Dla przykładu: chcemy mieć zestawienie ostatniej transakcji dla każdego z klientów, którzy kiedykolwiek dokonali zakupów, przy czym interesują nas takie dane jak chociażby kwota zakupu, liczba zgromadzonych punktów lojalnościowych przyznanych za zakup plus oczywiście datę złożenia/opłacenia zamówienia.

Do niedawna takie ćwiczenie w moim wykonaniu wyglądałoby następująco: 

WITH transakcje_wszystkie AS
(

 SELECT
     id_klienta
     , liczba_punktow
     , kwota_zakupu
     , data_trn
     , ROW_NUMBER() OVER (PARTITION BY id_klienta ORDER BY data_trn DESC) as liczba_porzadkowa
 FROM
     table_name

)
SELECT
 *
FROM
 transakcje_wszystkie
WHERE
 liczba_porzadkowa = 1

Mając na uwadze to wszystko co napisałem wyżej w pierwszej kolejności – w podzapytaniu transakcje_wszystkie – wyznaczam za pomocą funkcji okienkowej kolejność transakcji dla każdego unikalnego klienta w oparciu o datę z pola data_trn. Tym sposobem rekord zawierający poszukiwaną przeze mnie informację – najpóźniejsza data zakupy – zostanie opisany w kolumnie liczba_porzadkowa wartością liczbową równą jeden. 

Niestety dopiero w oparciu o tak przygotowane zestaw wszystkich transakcji, w kolejnym zapytaniu dokonuję właściwej selekcji interesujących mnie rekordów,  czyli takich gdzie w polu liczba_porzadkowa stoi jedynka. Dzieje się tak, ponieważ funkcja okienkowa jest zawsze wykonywana jako ostatnia i z tego powodu nie mogę jej użyć w klauzuli WHERE, ponieważ wykonanie tej klauzuli poprzedza zastosowanie samej funkcji okienkowej.

Rzecz jasna nie jest to duży kłopot, ponieważ i tak jest to rozwiązanie łatwiejsze oraz zgrabniejsze w formie niż wyliczenia najpierw maksymalnej daty transakcji per klient, by następnie przy jej pomocy odfiltrować interesujące nas rekordy. 

Tym niemniej przydałoby się tutaj coś na kształt klauzuli HAVING dla funkcji agregujących. Na szczęście coś takiego istnieje, choć niestety nie dla każdego środowiska bazodanowego (zdaje się, że z tych najbardziej popularnych rozwiązań – za wyłączeniem chmurowych, gdzie chyba powoli staje się to standardem – to póki co tylko w przypadku postgress jest taka możliwość, a przynajmniej tyle na ten moment udało mi się ustalić). Z resztą ja sam zacząłem z tego dobrodziejstwa korzystać dopiero w ramach googlowskiej usługi BigQuerry.

Wracają do naszego przykładu: by osiągnąć ten sam wynik bez konieczności zabawy w tworzenie ekstra podzapytania wystarczyłoby coś takiego:

SELECT
   id_klienta
 , liczba_punktow
 , kwota_zakupu
 , data_trn
 , ROW_NUMBER() OVER (PARTITION BY id_klienta ORDER BY data_trn DESC) as liczba_porzadkowa
FROM
 table_name
QUALIFY liczba_porzadkowa = 1

Alternatywnie, gdyby ktoś nie chciał wyświetlać tej informacji o pozycji wraz z pozostałymi danymi można zastosować taki zapis:

SELECT
 id_klienta
 , liczba_punktow
 , kwota_zakupu
 , data_trn
FROM
 table_name
QUALIFY ROW_NUMBER() OVER (PARTITION BY id_klienta ORDER BY data_trn DESC) = 1

Ja osobiście preferuję to pierwsze rozwiązanie, tym bardziej, że w realnych zastosowaniach nigdy nie zdarza się, by ćwiczenie było tak banalne jak to zaprezentowane wyżej, ponieważ na ogół konieczne są jeszcze kolejne przekształcenia, gdzie łatwo w następnych krokach wykluczyć to pole z wyniku końcowego.