Funkcje okienkowe SQL — ściąga: ROW_NUMBER, RANK i LAG/LEAD w pięciu silnikach

ROW_NUMBER, RANK, DENSE_RANK i LAG/LEAD z OVER, PARTITION BY i klauzulą ramki — składnia per silnik: PostgreSQL, MySQL/MariaDB, Oracle, SQL Server, SQLite.

Autor: Łukasz Żochowski

  • #sql
  • #dbeaver
  • #postgresql
  • #mysql
  • #oracle

Funkcje okienkowe to moment, w którym SQL przestaje być językiem raportów, a staje się prawdziwym językiem. Rankingowanie wierszy, porównywanie każdego wiersza z poprzednim, sumy kroczące, które nie zwijają wyniku — wszystko to pochodzi z tej samej małej rodziny funkcji i jednego słowa kluczowego: OVER. Dobra wiadomość: rdzeń składni jest zaskakująco przenośny między silnikami. Zła: ostatnie 10% — klauzula ramki, obsługa NULL-i i wersja, na której siedzisz — różni się na tyle, żeby cię sparzyć.

Ta ściąga obejmuje ROW_NUMBER, RANK, DENSE_RANK, LAG i LEAD z gotowym do wklejenia SQL dla PostgreSQL, MySQL/MariaDB, Oracle, SQL Server i SQLite — i mówi wprost, gdzie dialekty się rozjeżdżają. Każda informacja o dostępności i każdy niuans ramki poniżej są sprawdzone w oficjalnej dokumentacji, z linkiem w treści.

Czym są funkcje okienkowe i kiedy po nie sięgać?

Funkcja okienkowa liczy wartość na zbiorze wierszy powiązanych z bieżącym wierszem — jego oknem — nie zwijając tych wierszy w jeden. To cała różnica względem GROUP BY. Agregat z GROUP BY daje jeden wiersz na grupę; funkcja okienkowa daje jedną wartość na każdy wiersz wejściowy, wciąż zwracając wszystkie wiersze.

Kształt jest zawsze taki sam:

funkcja(...) OVER (
  [ PARTITION BY ... ]   -- podziel wiersze na niezależne grupy
  [ ORDER BY ... ]       -- ustal kolejność wewnątrz każdej grupy
  [ klauzula_ramki ]     -- które wiersze wokół bieżącego się liczą
)

PARTITION BY restartuje obliczenie dla każdej grupy (per dział, per klient, per dzień). ORDER BY wewnątrz OVER ustala kolejność, którą widzi funkcja — i to ona nadaje sens rankingowi oraz „poprzedniemu wierszowi”. Klauzula ramki, której większość osób nigdy nie dotyka, to miejsce, gdzie dialekty walczą. Dojdziemy do niej.

Które silniki obsługują funkcje okienkowe i od kiedy?

To pierwsza rzecz do ustalenia, bo „moje zapytanie działa” zależy od wersji znacznie mocniej, niż się ludziom wydaje.

Jeśli wspierasz starsze instalacje, ta lista to Twoja podłoga kompatybilności. Reszta artykułu zakłada wersję na poziomie każdej z nich lub wyższą.

Jak numerować wiersze: ROW_NUMBER, RANK i DENSE_RANK?

Tu naprawdę dobra wiadomość: trzy funkcje numerujące są składniowo identyczne we wszystkich pięciu silnikach. To zapytanie działa na PostgreSQL, MySQL/MariaDB, Oracle, SQL Server i SQLite bez żadnej zmiany:

SELECT
  employee_id,
  department_id,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

Różni się znaczenie — a pomyłka to klasyczny błąd:

  • ROW_NUMBER() nadaje każdemu wierszowi unikalny numer: 1, 2, 3, 4 — nawet gdy dwa wiersze mają tę samą wartość ORDER BY. Który z remisujących dostanie niższy numer, jest arbitralne, więc dołóż kolumnę rozstrzygającą do ORDER BY, jeśli potrzebujesz determinizmu.
  • RANK() daje remisującym ten sam rank, a potem zostawia luki: 1, 2, 2, 4. Dwa srebra, brak brązu.
  • DENSE_RANK() daje remisującym ten sam rank bez luk: 1, 2, 2, 3.

Wzorzec „top N na grupę” wypada wprost z tego: opakuj zapytanie i filtruj WHERE row_num <= 3. Użyj ROW_NUMBER dla dokładnie trzech wierszy, RANK/DENSE_RANK, jeśli chcesz zachować wszystkich remisujących na granicy odcięcia. Jedno zastrzeżenie warte powiedzenia: czyste funkcje rankingowe nie przyjmują klauzuli ramki — w SQL Server np. specyfikacja ROWS/RANGE jest dla funkcji rankingowych po prostu ignorowana, nawet gdy ORDER BY jest obecny (uwagi do klauzuli OVER).

Jak zajrzeć do poprzedniego lub następnego wiersza: LAG i LEAD?

LAG sięga wstecz do wcześniejszego wiersza; LEAD do przodu. Tak liczysz zmiany dzień-do-dnia, wykrywasz luki albo porównujesz wartość z sąsiadem bez self-joina. Sygnatura jest spójna wszędzie — funkcja(wartość [, offset [, default]]) — a to zapytanie jest przenośne we wszystkich pięciu silnikach:

SELECT
  order_date,
  amount,
  LAG(amount,  1, 0) OVER (ORDER BY order_date) AS prev_amount,
  LEAD(amount, 1, 0) OVER (ORDER BY order_date) AS next_amount
FROM orders;

offset domyślnie wynosi 1 (sąsiedni wiersz), a default to wartość, którą dostajesz, gdy wyjdziesz poza krawędź partycji — bez niej dostaniesz NULL, co często psuje obliczenie „zmiana od wczoraj” w pierwszym wierszu.

Jedyny realny rozjazd to obsługa NULL-i. Oracle pozwala dodać RESPECT NULLS (domyślne) albo IGNORE NULLS, żeby pomijać wartości NULL przy cofaniu się do ostatniego rzeczywistego odczytu (Oracle: LAG):

-- Oracle: cofnij się do poprzedniej wartości NIE-NULL
LAG(amount) IGNORE NULLS OVER (ORDER BY order_date)

Wsparcie IGNORE NULLS na LAG/LEAD jest w pozostałych silnikach nierówne, więc nie zakładaj, że ta klauzula się przeniesie — jeśli na niej polegasz, sprawdź to na silniku, na którym faktycznie jesteś, a nie na tym, na którym testowałeś.

Czym jest klauzula ramki i dlaczego gryzie?

Tu naprawdę rozgrywa się praca między silnikami. Gdy napiszesz ORDER BY wewnątrz OVER, ale bez jawnej ramki, silnik zastosuje domyślną ramkę — a nie jest nią „wszystkie wiersze, które uporządkowałem”. W PostgreSQL, MySQL, SQL Server i SQLite domyślną ramką przy obecnym ORDER BY jest:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

(Potwierdzone dla MySQL, SQL Server, PostgreSQL i SQLite.) Subtelne słowo to RANGE. W trybie RANGE „bieżący wiersz” obejmuje wszystkich peerów — każdy wiersz o tej samej wartości ORDER BY. Więc ta suma krocząca:

SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

robi coś zaskakującego, gdy order_date ma duplikaty: wszystkie zamówienia z tej samej daty dostają tę samą sumę kroczącą — sumę do końca całego tego dnia — a nie akumulację wiersz po wierszu. W dziewięciu przypadkach na dziesięć lekarstwem jest być jawnym i przełączyć się na ROWS, które liczy wiersze fizyczne i jest wspierane w każdym silniku mającym funkcje okienkowe:

SELECT
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders;

Trzy fakty o ramce, które trzeba trzymać prosto — wszystkie zweryfikowane:

  • Tryb GROUPS istnieje tylko w dwóch z pięciu. PostgreSQL (od 11) i SQLite (od 3.25.0) obsługują GROUPS, który przesuwa ramkę o całe grupy peerów (PostgreSQL 11; SQLite). MySQL i SQL Server obsługują wyłącznie ROWS i RANGE — ich gramatyka nie zna GROUPS w ogóle (ramki MySQL; SQL Server OVER).
  • RANGE w SQL Server jest ograniczone. Nie użyjesz offsetu liczbowego (RANGE BETWEEN 5 PRECEDING ...) z RANGE w SQL Server — tylko UNBOUNDED i CURRENT ROW. Dla okna liczbowego musisz użyć ROWS (ograniczenia klauzuli OVER).
  • Bez ORDER BY domyślną ramką jest cała partycja — każdy wiersz jest peerem każdego innego, więc agregat OVER (PARTITION BY ...) bez kolejności zwraca sumę grupy w każdym wierszu. Ta akurat jest przenośna i użyteczna.

Co jest takie samo, a co się różni w pięciu silnikach?

Uspokajające podsumowanie: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, PARTITION BY, ORDER BY i zwykła ramka ROWS to praktycznie ten sam SQL wszędzie. Jeśli trzymasz się tego, Twoje zapytania okienkowe przenoszą się czysto. Ostre krawędzie są wąskie i konkretne:

  • Podłoga wersji — MySQL 8.0, MariaDB 10.2, SQLite 3.25.0; poniżej nic nie zadziała.
  • Tryb GROUPS — tylko PostgreSQL 11+ i SQLite 3.25+.
  • RANGE z offsetami liczbowymi — nie ma w SQL Server.
  • IGNORE NULLS na LAG/LEAD — Oracle tak; w innych zweryfikuj.
  • Domyślna ramka RANGE — cichy powód błędnych sum kroczących, gdy kolumna ORDER BY ma remisy.

Jeśli to jest przydatne, łączy się wprost z szerszymi różnicami dialektów w tych samych pięciu silnikach — paginacją, cytowaniem identyfikatorów, konkatenacją — oraz z czytaniem planu wykonania, gdy zapytanie okienkowe po dużej tabeli zacznie zwalniać.

Jak zachować świadomość dialektu bez zapamiętywania tego wszystkiego?

Trzymaj pod ręką ściągę taką jak ta i korzystaj z narzędzi, które znają dialekt bazy, do której jesteś podłączony — zamiast pisać jeden wariant i zostawiać tłumaczenie tobie.

Ujawnienie: ten blog wydaje LumaSoft (LUMA sp. z o.o.), a ja pracuję nad narzędziem wspomnianym poniżej.

SQL Sage for DBeaver to plugin, który dodaje AI copilota wewnątrz DBeavera i jest świadomy dialektu tych samych pięciu silników — PostgreSQL, MySQL/MariaDB, Oracle, SQL Server i SQLite — więc składnia funkcji okienkowych, którą pisze, pasuje do połączenia, na którym jesteś, łącznie z różnicami w klauzuli ramki opisanymi wyżej. Kilka faktów wprost: jest wyłącznie dla Windows (Linux i macOS nie są wspierane), jest keyless (logujesz się własnym kontem Claude lub ChatGPT, bez klucza API) i działa read-only domyślnie — na PostgreSQL oraz MySQL/MariaDB ten tryb tylko-do-odczytu jest wymuszany na poziomie silnika, nie tylko w interfejsie. Nie jest powiązany z DBeaver, Oracle, Microsoft, Anthropic ani OpenAI.

Niezależnie od tego, czy użyjesz narzędzia, czy własnej pamięci, wniosek jest ten sam: rdzeń składni funkcji okienkowych jest przenośny, a cała stawka leży w klauzuli ramki. Zachowaj tę stronę w zakładkach na następny raz, gdy suma krocząca wróci wyglądając nie tak.

← Wróć do bloga