Składnia OVER() i jego kluczowe cechy

OVER() — to instrukcja, która mówi SQL, na jakim zbiorze wierszy ma być zastosowana funkcja okienkowa. Można powiedzieć, że to sposób, który określa "okno" danych do użycia funkcji okienkowej. Wyobraź sobie, że mamy pokój pełen ludzi i chcemy policzyć, ile osób stoi na każdym metrze kwadratowym podłogi. OVER() wskaże, w której części pokoju skupiamy swoje wysiłki. Innymi słowy, określa, na jakim zbiorze wierszy będzie działać funkcja.

Operator OVER() używany jest wyłącznie z funkcjami okienkowymi do wykonywania operacji na wierszach jednej lub kilku tabel, bez grupowania danych.

Składnia:

funkcja_okienkowa() OVER (
    [PARTITION BY ...]
    [ORDER BY ...]
    [ROWS/RANGE ...]
)

Gdzie:

• PARTITION BY — dzieli zbiór danych na logiczne grupy
• ORDER BY — ustala kolejność wierszy w każdej grupie
• ROWS/RANGE — precyzuje rozmiar "okna" (np. bieżący wiersz + 1 następny)

Przykład: OVER() bez parametrów

Kiedy OVER() jest używany bez dodatkowych parametrów, oznacza to, że funkcja podana przed nim będzie działać na całym zbiorze danych.

SELECT
    employee_id,
    salary,
    ROW_NUMBER() OVER () AS row_num -- ROW_NUMBER() zostanie zastosowane do wszystkich wierszy wyniku
FROM employees;

Co się dzieje?

1. ROW_NUMBER() przypisuje unikalny numer każdemu wierszowi.
2. Ponieważ w OVER() nie podano żadnych parametrów, wszystkie wiersze z tabeli employees są traktowane jako całość.

Wynik:

Użycie PARTITION BY do podziału na grupy

Super, teraz wyobraź sobie, że interesuje nas numeracja pracowników nie w całej firmie, ale w każdym dziale osobno. Tutaj na scenę wchodzi PARTITION BY.

PARTITION BY wewnątrz OVER() dzieli dane na grupy (albo "sekcje"). Dla każdej grupy funkcja liczy wartość osobno. Czyli, gdyby ROW_NUMBER() był kelnerem, zaczynałby numerację od nowa dla każdego "stołu" (sekcji).

Przykład: użycie PARTITION BY

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id) AS row_num
FROM employees;

Co się dzieje?

1. Dane z tabeli employees są podzielone na grupy według wartości department_id.
2. W każdej grupie wierszom przypisywany jest numer porządkowy przez ROW_NUMBER().

Wynik:

Użycie ORDER BY do ustalenia kolejności

Teraz dodajmy trochę struktury. Wyobraź sobie, że chcemy nie tylko numerować wiersze, ale robić to w określonej kolejności, np. zaczynając od najwyższej pensji. To zadanie rozwiązuje ORDER BY.

ORDER BY określa kolejność, w jakiej wiersze będą przetwarzane przez funkcję okienkową.

Przykład: użycie ORDER BY wewnątrz OVER()

SELECT
    department_id,
    employee_id,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

Co się dzieje?

1. Dane są podzielone na grupy (PARTITION BY department_id).
2. W każdej grupie wiersze są sortowane malejąco według pensji (ORDER BY salary DESC).
3. Każdemu wierszowi przypisywany jest ranking z uwzględnieniem sortowania.

Wynik:

Kombinowanie funkcji okienkowych

SQL pozwala używać kilku funkcji okienkowych w jednym zapytaniu, przy czym każda z nich może działać według własnych zasad. To tak, jakby w jednym pokoju jednocześnie grała muzyka i liczono ludzi — każdy proces jest niezależny!

Przykład: kilka funkcji okienkowych

SELECT
    department_id,
    employee_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

Co się dzieje?

1. ROW_NUMBER() numeruje wiersze w każdej grupie malejąco według pensji.
2. AVG() liczy średnią pensję w każdej grupie.

Wynik:

Przykłady z życia codziennego

Funkcje okienkowe z OVER() są używane w wielu realnych scenariuszach. Oto kilka przykładów:

• Analiza sprzedaży: ranking produktów według liczby sprzedanych sztuk w każdej kategorii.
• Rankingi: określanie pozycji studentów w każdej grupie według średniej oceny.
• Szeregi czasowe: narastająca suma sprzedaży w podziale na czas.

Przykład z analizy sprzedaży:

SELECT
    category_id,
    product_id,
    product_name,
    SUM(sales) OVER (PARTITION BY category_id ORDER BY sales DESC) AS cumulative_sales
FROM products;

Typowe błędy przy pracy z funkcjami okienkowymi

1. Brak PARTITION BY

Jeśli nie używasz PARTITION BY, funkcja okienkowa działa na całej tabeli. Może to prowadzić do nieoczekiwanych wyników, zwłaszcza jeśli spodziewałeś się podziału na grupy.

---

2. Nieprawidłowe typy danych w ORDER BY

ORDER BY wewnątrz funkcji okienkowej jest wrażliwy na typy danych. Jeśli sortujesz po polu z datą zapisaną jako tekst (VARCHAR), kolejność może być alfabetyczna, a nie chronologiczna.

3. Nieprawidłowe użycie ROWS BETWEEN

Domyślnie funkcje okienkowe działają na ramach określonych przez ROWS BETWEEN. Jeśli nie wskażesz ramy jawnie, może być użyte zachowanie RANGE, które działa inaczej i może zwrócić więcej wierszy, niż się spodziewasz.

4. Nieprawidłowa praca z NULL

Funkcje okienkowe mogą różnie traktować NULL. Na przykład RANK() i DENSE_RANK() potraktują NULL jako wartość i przypiszą jej osobny ranking.

5. Stosowanie agregatów okienkowych zamiast zwykłych

Czasem używa się funkcji agregujących okienkowych (SUM() OVER(...)) tam, gdzie wystarczy zwykły agregat z GROUP BY, co komplikuje zapytanie i spowalnia wykonanie.