PostgreSQL i funkcje okienkowe – pozycja w rankingu

PostgreSQLFunkcja okienkowa wykonuje obliczenia w całym zbiorze wierszy tabeli, które są w jakiś sposób związane z bieżącym wierszem. Jest to porównywalne do rodzaju obliczenia, które mogą być wykonane z funkcją agregującą. Ale w przeciwieństwie do zwykłych funkcji agregujących, korzystanie z funkcji okienkowych nie powoduje, że wiersze zostaną zgrupowane w jednym rzędzie wyjściowym, lecz zachowają swoje odrębne tożsamości.

Krótko podsumowując:

Funkcja okienkowa jest w stanie uzyskać dostęp do więcej niż tylko bieżącego wiersza wyniku zapytania.

Funkcja rank() – pozycja rankingowa

Funkcja rank() generuje numeryczny ranking każdej pozycji wiersza według unikalnych wartości całego zbioru, posortowanych w ustalonej kolejności.

Przykładowe dane

Tabela egzam zawiera wyniki egzaminu z danymi: id studenta, grupa, liczba punktów:

Ranking z ORDER BY

Cel: wyszukanie trzech studentów z najlepszymi wynikami wraz z numerem pozycji w rankingu.

Wykonanie zapytania z użyciem sortowania malejącego po punktach z limitem trzech pozycji nie rozwiązuje problemu, ponieważ w wyniku zostaną pominięci dwaj studenci z wynikiem 29.
Standardowo można użyć podzapytania z wykorzystaniem JOINa, jednak i tak będzie brakować informacji o dokładnej pozycji w rankingu, ponieważ studenci 3, 4, 6 zajmują ex aequo trzecie miejsce.

W bazie danych PostgreSQL od wersji 8.4  dostępne są funkcje okienkowe, dzięki którym taki raport można wykonać za pomocą krótkiego zapytania:

W wyniku otrzymujemy pozycję trzecią dla studentów 3, 4, 6 którzy otrzymali wynik 29. Aby zawęzić listę do trzech najlepszych osób należy użyć podzapytania:

Ranking z ORDER BY i PARTITION BY

Cel: wyszukanie najlepszych studentów w każdej grupie (którzy zajmują pierwsze miejsca w swoich grupach).

Można to zrobić, tworząc procedury, lub trochę bardziej skomplikowane zapytania z użyciem group, having, max. Można się również zdecydować na pobranie danych i podzielenie ich w pętli w preferowanym języku programowania. Dzięki funkcjom okienkowym staje się to dużo łatwiejsze. Mało tego – PostgreSQL zoptymalizuje wyszukiwanie.

Wyszukanie pozycji rankingowych w każdej grupie studentów z osobna:

To, co pozostaje do wykonania, to ograniczenie wyników do pierwszej pozycji w rankingu:

To również może Cię zainteresować:

  • PostgreSQL i funkcje okienkowe – suma narastającaPostgreSQL i funkcje okienkowe – suma narastająca W poprzednim wpisie opisywałem funkcję rank() jako pierwszą z okienkowych. Kolejną często wykorzystywaną funkcją podczas tworzenia raportów jest suma narastająca. Dane […]
  • PL/SQL, recenzja kursu wideo z Videopoint.plPL/SQL, recenzja kursu wideo z Videopoint.pl W kolejnym artykule recenzującym materiały z videopoint.pl zajmiemy się  kursem: "PL/SQL. Kurs video. Od podstaw do zagadnień zaawansowanych. Programowanie baz danych". Tym razem […]
  • Praktyki mistrza SQL. Programowanie zaawansowane. Recenzja książki.Praktyki mistrza SQL. Programowanie zaawansowane. Recenzja książki. Niniejszy wpis to kolejna recenzja książki przygotowana przez Bartka Medonia na moim blogu. Tym razem do oceny trafiła nowość wydana w połowie października tego roku - "Praktyki mistrza […]
  • Redis w WindowsRedis w Windows Redis (skrót od Remote Dictionary Service) jest pamięciową bazą NoSQL przechowującą dane jako pary klucz - wartość. Dzięki przechowywaniu danych w pamięci RAM charakteryzuje się wysoką […]
  • Warsztat i automatyzacja Podczas codziennej pracy często okazuje się, że setki razy powtarzamy pewne czynności według określonego algorytmu (nierzadko bezwiednie). Takie sytuacje należy eliminować poprzez […]
  • Siedem dni, siedem książek z rabatem -50%Siedem dni, siedem książek z rabatem -50% Jak co roku przed Świętami Helion wybiera 7 pozycji wśród najpopularniejszych książek roku i rabatują je o 50% codziennie inny z tej puli. Poniżej znajdziesz dzienny harmonogram i ceny po […]

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *