Podstawowe funkcje okienne: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

Na poprzedniej lekcji ogarnęliśmy, po co są funkcje okienne. Teraz zobaczymy konkretne funkcje i ich wyniki. Szczegóły składni rozkminimy na kolejnej lekcji.

Funkcja ROW_NUMBER()

Funkcja ROW_NUMBER() zwraca unikalny numer dla każdego wiersza w obrębie okna. To po prostu numerowanie wierszy według kolejności określonej w ORDER BY.

Składnia:

ROW_NUMBER() OVER ([PARTITION BY kolumna] ORDER BY kolumna)

Gdzie:

• PARTITION BY kolumna (opcjonalnie): dzieli dane na grupy. Jeśli pominiesz, numeracja będzie globalna dla całego zbioru.
• ORDER BY kolumna: określa kolejność wierszy do numerowania.

Przykład. Numerowanie wierszy w tabeli

Weźmy tabelę students, która zawiera info o studentach i ich ocenach.

SELECT * FROM students;

Teraz ponumerujemy wiersze według malejących ocen (score):

SELECT
    name, 
    score, 
    ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num
FROM students;

Wynik:

Każdy wiersz dostał unikalny numer porządkowy — z uwzględnieniem sortowania malejąco po ocenach.

To prosta, ale mega przydatna operacja — dodać numer wiersza do wyniku zapytania. W klasycznym SELECT nie da się tego zrobić bez funkcji okiennych.

Funkcja RANK()

Funkcja RANK() jest bardzo podobna do ROW_NUMBER(), ale bierze pod uwagę takie same wartości. Jeśli wiersze mają tę samą wartość w sortowaniu, dostaną ten sam ranking, a następny zostanie pominięty.

Składnia:

RANK() OVER ([PARTITION BY kolumna] ORDER BY kolumna)

Przykład. Ranking studentów według ocen

Użyjmy RANK() na tych samych danych:

SELECT
    name, 
    score, 
    RANK() OVER (ORDER BY score DESC) AS rank
FROM students;

Wynik:

Tutaj wiersze z tymi samymi wartościami (95 i 87) dostały ten sam ranking, a kolejne rankingi są pominięte.

Funkcja DENSE_RANK()

DENSE_RANK() jest podobny do RANK(), ale nie pomija wartości rankingów. To znaczy, że jeśli są powtarzające się wiersze, następny ranking będzie o jeden większy od poprzedniego.

Składnia:

DENSE_RANK() OVER ([PARTITION BY kolumna] ORDER BY kolumna)

Przykład. Gęsty ranking

Użyjmy DENSE_RANK() na tych samych danych:

SELECT
    name, 
    score, 
    DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM students;

Wynik:

Tutaj, w przeciwieństwie do RANK(), rankingi rosną bez przerw.

Funkcja NTILE()

Funkcja NTILE() dzieli wiersze na równe grupy (kwantyle) i przypisuje każdemu wierszowi numer grupy.

Składnia:

NTILE(n) OVER ([PARTITION BY kolumna] ORDER BY kolumna)

• n: liczba grup, na które trzeba podzielić dane.

Przykład. Podział studentów na 3 grupy

Podzielmy studentów na 3 grupy według malejących ocen:

SELECT
    name, 
    score, 
    NTILE(3) OVER (ORDER BY score DESC) AS group_num
FROM students;

Wynik:

Pamiętaj: jeśli nie da się podzielić wierszy idealnie równo na grupy, nadmiarowe wiersze trafiają do pierwszych grup. W tym przykładzie dwie pierwsze grupy mają po dwa wiersze, a ostatnia — jeden.

Kiedy używać której funkcji?

• ROW_NUMBER(): do unikalnego numerowania wierszy według sortowania.
• RANK(): do rankingowania z uwzględnieniem tych samych wartości i pominięciem kolejnego rankingu.
• DENSE_RANK(): do rankingowania z uwzględnieniem tych samych wartości bez pomijania rankingów.
• NTILE(): Do równomiernego podziału wierszy na grupy.

Wszystkie te funkcje pozwalają analizować dane na zupełnie nowym poziomie. Używaj ich tam, gdzie potrzebujesz elastyczności w liczeniu numerów porządkowych albo dzieleniu danych.