Lekcja 6: Funkcje Okienkowe — Dajmy Twoim Zapytaniom Widok z Góry!

Witaj ponownie, odkrywco SQL! Dziś zanurzymy się w świat funkcji okienkowych — to jak przeprowadzka do apartamentu z panoramicznym widokiem w krainie SQL. Jeśli kiedykolwiek chciałeś dodać odrobinę luksusu do swojego analitycznego warsztatu, funkcje okienkowe są tu, by zapewnić Ci królewski widok. Wyobraź je sobie jak teleskopy: pozwalają spojrzeć na każdy wiersz, nie tracąc przy tym z oczu całego obrazu.

Czym są Funkcje Okienkowe?

Funkcje okienkowe, znane też jako funkcje analityczne, wykonują obliczenia na zbiorze wierszy tabeli, które w jakiś sposób są powiązane z bieżącym wierszem. To jak porównywanie sukcesów każdego rycerza, nie tracąc przy tym z oczu całego królestwa. Funkcje okienkowe pomagają:

• Tworzyć rankingi rycerzy według ich osiągnięć.
• Obliczać sumy narastające, średnie wartości lub bieżące podsumowania.
• Znajdować procenty i bieżące wyniki misji.

Składnia Funkcji Okienkowej

Funkcje okienkowe mają prostą składnię, która wygląda mniej więcej tak:

SELECT column_name,
       window_function(column_name) OVER (PARTITION BY column_name ORDER BY column_name)
FROM table_name;

Najważniejsza część tej funkcji to klauzula OVER. Mówi ona PostgreSQL, przez jakie „okno” patrzeć na dane podczas obliczeń.

RANK i DENSE_RANK: Bo Każdy Rycerz Zasługuje na Swój Tytuł

Zaczynamy od funkcji rankingowych. Wyobraź sobie, że masz tabelę rycerzy z liczbą wykonanych misji i chcesz zobaczyć, kto jest prawdziwym bohaterem (albo najstraszliwszym pogromcą smoków):

SELECT imie, misje_wykonane,
       RANK() OVER (ORDER BY misje_wykonane DESC) AS rank
FROM rycerze;

Ten zapytanie przypisze rycerzom rangi w zależności od liczby ukończonych misji. Ale uwaga: RANK pominie numery, jeśli kilku rycerzy ma taką samą liczbę misji. Czyli jeśli dwóch rycerzy jest na pierwszym miejscu, kolejny będzie już trzeci, a nie drugi!

Chcesz, żeby każdy czuł się wyjątkowo? Użyj DENSE_RANK:

SELECT imie, misje_wykonane,
       DENSE_RANK() OVER (ORDER BY misje_wykonane DESC) AS dense_rank
FROM rycerze;

Dzięki temu będzie bardziej elegancko — żadnych dziur w rankingach!

ROW_NUMBER: Bo Każdy Wiersz Jest Ważny

Jeśli potrzebujesz prostego numerowania każdego wiersza, ROW_NUMBER to Twój najlepszy przyjaciel:

SELECT imie, misje_wykonane,
       ROW_NUMBER() OVER (ORDER BY misje_wykonane DESC) AS numer_wiersza
FROM rycerze;

Idealne, gdy chcesz, żeby każdy rycerz miał swój unikalny numer, bez względu na liczbę misji.

PARTITION BY: Organizujemy Królewski Bankiet

Teraz podkręćmy trochę poziom. PARTITION BY pozwala grupować wiersze w oddzielne „okna”, tak jakbyśmy rozsadzali rycerzy według królestw na wielkim bankiecie. Powiedzmy, że chcesz posortować rycerzy w obrębie każdego królestwa:

SELECT imie, krolestwo, misje_wykonane,
       RANK() OVER (PARTITION BY krolestwo ORDER BY misje_wykonane DESC) AS rank_w_krolestwie
FROM rycerze;

Teraz każdy rycerz ma swoją rangę wewnątrz swojego królestwa, więc Sir Galahad może nadal czuć się numerem jeden w Camelocie, nawet jeśli Sir Artur go wyprzedza ogólnie.

Sumy Narastające: Liczymy Złoto w Skarbcu

Jeśli chcesz widzieć bieżące sumy, funkcje okienkowe świetnie się sprawdzą. Załóżmy, że chcemy policzyć narastającą sumę misji dla rycerzy, uporządkowaną według ich osiągnięć:

SELECT imie, misje_wykonane,
       SUM(misje_wykonane) OVER (ORDER BY misje_wykonane DESC) AS suma_narastajaca
FROM rycerze;

To jak układanie złotych monet w skarbcu: każdy wkład rycerzy dodaje się do stosu, pokazując bieżący wynik.

Średnie Ruchome: Okrągły Stół Obliczeń

Średnie ruchome są przydatne, gdy chcesz wygładzić fluktuacje. Znajdźmy średnią liczbę misji dla trzech rycerzy:

SELECT imie, misje_wykonane,
       AVG(misje_wykonane) OVER (ORDER BY misje_wykonane DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS srednia_ruchoma
FROM rycerze;

To obliczy średnią liczbę misji dla każdego rycerza, biorąc pod uwagę jego własne misje oraz misje dwóch poprzednich. To jak debata przy okrągłym stole — każda opinia ma znaczenie!

LEAD i LAG: Zajrzyjmy w Przeszłość i Przyszłość

Chcesz zobaczyć, jak rycerz radził sobie w poprzedniej misji, albo przewidzieć następną? Użyj LEAD i LAG, aby rzucić okiem na oś czasu:

SELECT imie, misje_wykonane,
       LAG(misje_wykonane, 1) OVER (ORDER BY misje_wykonane DESC) AS poprzednia_misja,
       LEAD(misje_wykonane, 1) OVER (ORDER BY misje_wykonane DESC) AS nastepna_misja
FROM rycerze;

To pozwala porównać wyniki każdego rycerza z tym, kto był przed nim, i z tym, kto jest za nim — idealne do wykrywania trendów i anomalii.

Czego Się Dzisiaj Nauczyliśmy?

Dziś odkryliśmy świat funkcji okienkowych:

• Funkcje Rankingu: RANK, DENSE_RANK i ROW_NUMBER — aby każdy rycerz znał swoje miejsce.
• Sumy Narastające: SUM, AVG i COUNT — aby zobaczyć, kto naprawdę króluje w bitwach.
• LEAD i LAG: pozwalają zajrzeć w przeszłość i przyszłość — jak mieć kryształową kulę w SQL.

Co Dalej?

Na następnym spotkaniu zagłębimy się w świat Wyszukiwania Pełnotekstowego w PostgreSQL — czas dać Twoim zapytaniom głos i pozwolić danym przemówić!

Naprzód, i niech Twoje zapytania zawsze patrzą z góry na świat!