Pchełki SQL, odcinek 11: funkcje rankingowe

https://xpil.eu/5B4kP 📋

Proszę się nie obawiać, nie zamierzam tu wklejać całego rozdziału z MSDN dotyczącego funkcji rankingowych. Wrzucę tylko jedną malutką pchełkę własnej roboty - i już zmykam.

Najpierw jednak, słowem wstępu, czym w ogóle są owe funkcje rankingowe?

Otóż najprościej mówiąc, są to funkcje wyliczające ranking każdego rekordu wejściowego, na podstawie zadanych kryteriów. Ranking może być liczony względem całości bądź też w grupach.

Przykład takiego rankingu: Kirzyński zarabia sto tysięcy, Nowak dwieście, Kowalska trzysta a Kunecka pińcet. Dzięki funkcjom rankingowym możemy sobie wyliczyć w jakiej kolejności ich poustawiać, żeby stali od najniższego do najwyższego, albo od najstarszego do najmłodszego, albo - dla hecy - od najlepiej zarabiającego do najgorzej. Możemy ich też poustawiać w osobnych grupkach (na przykład, faceci osobno od babeczek, albo czarni osobno od białych, albo blondyni od szatynów i od brunetów), i w każdej grupie mieć ich posortowanych od najbardziejszego do najniebardziejszego, albo na odwrót.

Po tym wstępnie zakładam, że trzy czwarte moich Czytelników już opuściło stronę i poszło czytać Joe Monstera albo Nowego Pompona. Sprawa jest o tyle niepokojąca, że Czytelników mam póki co ledwie trzech, a więc odejmując z tego trzy czwarte mamy niezłą jatkę. Nie przejmujemy się nią jednak zbytnio i kontynuujemy nasze zabawy z rankingami. W następnej kolejności pokażę kod:

WITH ludzie AS (
  SELECT 1 AS ID, 'Jan Kowalski' AS nazwa, 'm' AS plec, 190 AS wzrost FROM dual
  UNION ALL SELECT 2, 'Adam Malinowski', 'm', 180 FROM dual
  UNION ALL SELECT 3, 'Andrzej Nowak', 'm', 170 FROM dual
  UNION ALL SELECT 4, 'Gerard Słabszy', 'm', 190 FROM dual
  UNION ALL SELECT 5, 'Eliza Bębenek', 'k', 160 FROM dual
  UNION ALL SELECT 6, 'Kora Śliska', 'k', 195 FROM dual
  UNION ALL SELECT 7, 'Juanita Guadalajara-Pieniążek', 'k', 170 FROM dual
)
SELECT
    id,
    nazwa,
    plec,
    wzrost,
    DENSE_RANK() OVER(ORDER BY wzrost DESC) ranking_1,
    RANK() OVER(ORDER BY wzrost DESC) ranking_2,
    DENSE_RANK() OVER(PARTITION BY PLEC ORDER BY wzrost DESC) ranking_3,
    RANK() OVER(PARTITION BY PLEC ORDER BY WZROST DESC) ranking_4,
    ROW_NUMBER() over(order by wzrost desc) lista_1,
    ROW_NUMBER() over(partition by plec order by wzrost desc) lista_2,
    NTILE(2) over(order by wzrost desc) dwupodzial,
    NTILE(3) over(order by WZROST desc) trojpodzial,
    plec || NTILE(2) over(PARTITION BY PLEC order by WZROST desc) dwupodzial_w_grupach
  FROM
    ludzie;

Teraz omówimy sobie powyższy kod dokładnie. Szacuję, że podczas omawiania odpadnie kolejna połowa Czytelników; nie wiem ile to dokładnie będzie bo jestem kiepski w ułamkach.

Zapytanie otwiera proste CTE o nazwie 'ludzie' z przykładową listą osób (potrzebujemy jakichś danych wejściowych, prawda?). CTE generuje cztery kolumny: unikalny identyfikator osoby, imię i nazwisko, płeć i wzrost. Mamy więc siedem osób (czterech samców i trzy samiczki), o różnych wzrostach (niektóre osoby mają taki sam wzrost, co wynika prawdopodobnie z błędu pomiaru, jednak zadowolimy się takim zaokrągleniem).

Uwaga: powyższe CTE jest kompatybilne z Oracle. Jak już wielokrotnie wspominałem, wystarczy wywalić nieszczęsne "FROM DUAL" i kod stanie się kompatybilny z MS SQL Server.

A dalej zaczyna się już samo gęste, czyli ustawianie naszej siódemki bohaterów w rozmaite rankingi według wzrostu. Przyjrzyjmy się jak są wyliczane kolejne kolumny na wyjściu zapytania:

ranking_1:

DENSE_RANK() OVER(ORDER BY wzrost DESC)

Funkcja DENSE_RANK generuje ranking w taki sposób, że osoba najwyższa dostaje jedynkę, kolejna dwójkę i tak dalej. Jeżeli dwie osoby są identycznego wzrostu, funkcja DENSE_RANK wyliczy dla nich identyczny ranking, a kolejna osoba (ciut niższa od tych dwóch) otrzyma ranking o jeden wyższy. W ten sposób żadne dwie kolejne pozycje w rankingu nie różnią się o więcej niż jeden. Funkcja przyjmuje jedną, jedyną informację na wejściu: nazwę kolumny, według której ma nastąpić wyliczanie rankingu. W tym przypadku (oraz we wszystkich pozostałych w tej pchełce) jest to kolumna wzrost, posortowana malejąco.

ranking_2:

RANK() OVER(ORDER BY wzrost DESC)

Funkcja RANK w tym przykładzie ma identyczną składnię jak DENSE_RANK powyżej. Jedyną różnicą jest sposób wyliczania rankingu w przypadku powtórzeń. W odróżnieniu od DENSE_RANK, tutaj w przypadku dubli ranking kolejnych elementów jest zwiększany nie o jeden, a o ilość powtórzonych elementów. Jan Kowalski i Gerard Słabszy mają identyczny wzrost, obydwaj są więc na miejscu drugim - jednak odrobinę niższy od nich Adam Malinowski dostaje miejsce czwarte (a nie trzecie jak w wersji z DENSE_RANK). W ten sposób numeracja miejsc jest "dziurawa" w okolicach dubli. Taki ranking wylicza pozycję "zawodnika" na "mecie" - Malinowski faktycznie jest czwarty (są trzy osoby od niego wyższe) a nie trzeci (jak podaje funkcja DENSE_RANK). Można by pomyśleć, że funkcja DENSE_RANK jest przez to jakoś "gorsza" (bo podaje nieprawdziwe rankingi), jednak po krótkim zastanowieniu dojdziemy do wniosku, że każda z nich po prostu służy do innych celów i każda znajdzie swoje zastosowanie.

ranking_3 i ranking_4:

DENSE_RANK() OVER(PARTITION BY PLEC ORDER BY wzrost DESC)
RANK() OVER(PARTITION BY PLEC ORDER BY WZROST DESC)

Kolejne dwie kolumny (ranking_3 i ranking_4) są wyliczone za pomocą funkcji RANK i DENSE_RANK, analogicznie do kolumn ranking_1 oraz ranking_2. Jedyna różnica polega na tym, że tutaj liczymy rankingi osobno w każdej grupie (faceci osobno, babeczki osobno). Służy do tego opcjonalny operator PARTITION BY z następującą po nim nazwie kolumny, według której ma nastąpić podział na grupy. Jak widać, Kora Śliska dostaje pierwsze miejsce bo jest najwyższa wśród kobiet, zaś Jan Kowalski ląduje na pierwszym miejscu wśród mężczyzn. Mamy po prostu dwie osobne listy rankingowe, reszta pozostaje bez zmian.

lista_1:

ROW_NUMBER() over(order by wzrost desc)

Kolumna lista_1 została wyliczona za pomocą funkcji ROW_NUMBER. Funkcja ta wylicza unikalny numer wiersza, startując od jedynki i zwiększając z każdym wierszem licznik o jeden. Powtórzenia nie mają żadnego wpływu na numerację (osoby o tym samym wzroście dostaną po prostu dwa kolejne numery, w nieznanej z góry kolejności).

lista_2:

ROW_NUMBER() over(partition by plec order by wzrost desc)

Kolumna lista_2, podobnie jak poprzednia, wylicza kolejne numery wierszy, według wzrostu (od najwyższego). Jedyna różnica polega na obecności operatora PARTITION BY, który sprawia, że numeracja nastąpi osobno w każdej grupie (dlatego mamy dwie jedynki - Jan Kowalski jest najwyższy wśród facetów, a Kora Śliska wśród pań).

Na koniec wyliczymy sobie kilka entyli (słowo "entyl" utworzyłem ad-hoc na potrzeby niniejszego wpisu, proszę się nim nie posługiwać w życiu codziennym - natomiast gorąco polecam zajrzeć do encyklopedii pod hasło 'kwantyl'). Entyl (po naszemu: n-tile) to liczba mówiąca do której grupy należy dany rekord. W najprostszym przypadku możemy sobie wyobrazić dwie grupy: wysocy i niscy. Funkcja NTILE podzieli nam całą grupę na pół (lub prawie pół w przypadku nieparzystej liczebności grupy), umieszczając wysokich w pierwszej połowie a niskich w drugiej. Właśnie to wydarza się w kolumnie dwupodział:

dwupodzial:

NTILE(2) over(order by wzrost desc)

Funkcja NTILE przyjmuje dokładnie jeden argument, jest nim ilość grup na które należy podzielić rekordy wejściowe. Występujący w nawiasie operator ORDER BY mówi zaś - tradycyjnie - według jakiego kryterium ma ów podział nastąpić.

W następnej kolumnie dzielimy ludków na trzy grupy: wysocy, średni i niscy:

trojpodzial:

NTILE(3) over(order by WZROST desc)

Jak widać, jedyna różnica między definicją tej kolumny a poprzedniej to trójka zamiast dwójki, przekazana do funkcji NTILE. A więc dzielimy na trzy grupy, a nie na dwie.

Ostatnia kolumna grupuje ludzi na wysokich i niskich, ale osobno kobitki i osobno facetów. W wyniku dostajemy cztery grupy: wysocy i niscy, każdej płci.

dwupodzial_w_grupach:

plec || NTILE(2) over(PARTITION BY PLEC order by WZROST desc)

Proszę przy okazji zauważyć, że "przyklejam" na początku entyla oznaczenie płci, w efekcie dostajemy nie tylko suchą liczbę, ale dodatkowo oznaczenie czy jest to grupa męska czy żeńska.

https://xpil.eu/5B4kP 📋