Konfigurowanie ramki okna za pomocą ROWS i RANGE

Kiedy używasz funkcji okiennych, pojawia się pytanie: "Ile wierszy w oknie bierze udział w obliczeniu wartości dla bieżącego wiersza?" Odpowiedź na to zależy od ramki okna.

Ramka okna — to zakres wierszy, który jest używany do obliczenia wyniku funkcji okiennej. Ten zakres buduje się na podstawie bieżącego wiersza oraz dodatkowych warunków ustawionych przez ROWS albo RANGE.

Prosty przykład: licząc sumę narastającą, możesz wskazać:

• Bierz pod uwagę tylko bieżący wiersz.
• Bierz pod uwagę bieżący wiersz i wszystkie powyżej.
• Bierz pod uwagę bieżący wiersz i określoną liczbę wierszy powyżej/poniżej.

To właśnie ROWS i RANGE sterują tym, które wiersze trafią do ramki okna.

Użycie ROWS

ROWS określa ramkę okna na poziomie fizycznego położenia wierszy. To znaczy, że liczy wiersze od góry do dołu w ich kolejności, niezależnie od wartości w tych wierszach.

Składnia

funkcja_okienna OVER (
    ORDER BY kolumna
    ROWS BETWEEN poczatek AND koniec
)

Kluczowe wyrażenia:

• CURRENT ROW — bieżący wiersz.
• liczba PRECEDING — określona liczba wierszy powyżej bieżącego.
• liczba FOLLOWING — określona liczba wierszy poniżej bieżącego.
• UNBOUNDED PRECEDING — od początku okna.
• UNBOUNDED FOLLOWING — do końca okna.

Przykład: suma narastająca dla bieżącego i 2 poprzednich wierszy

SELECT
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS rolling_sum
FROM employees;

Wyjaśnienie:

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW znaczy: weź bieżący wiersz i dwa powyżej niego.
• Suma narastająca będzie liczona tylko dla tych trzech wierszy.

Wynik:

Przykład: analiza "przesuwającego się okna" z ustaloną liczbą wierszy

Zadanie: policzyć średnią pensję dla bieżącego wiersza i dwóch kolejnych.

SELECT 
    employee_id,
    salary,
    AVG(salary) OVER (
        ORDER BY employee_id
        ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
    ) AS rolling_avg
FROM employees;

Wynik:

Użycie RANGE

RANGE buduje ramkę okna na podstawie wartości, a nie pozycji wierszy. To znaczy, że wiersze są w ramce, jeśli ich wartości w kolumnie ORDER BY mieszczą się w podanym zakresie.

Składnia

funkcja_okienna OVER (
    ORDER BY kolumna
    RANGE BETWEEN poczatek AND koniec
)

Przykład: suma narastająca po zakresie wartości

Zadanie: policzyć sumę narastającą dla wierszy, gdzie pensja różni się od bieżącej maksymalnie o 2000.

SELECT 
    employee_id,
    salary,
    SUM(salary) OVER (
        ORDER BY salary
        RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING
    ) AS range_sum
FROM employees;

Wyjaśnienie:

• RANGE BETWEEN 2000 PRECEDING AND 2000 FOLLOWING znaczy: weź wiersze, gdzie salary jest w zakresie ±2000 od bieżącego wiersza.

Wynik:

Porównanie ROWS i RANGE

• ROWS działa na realnych wierszach i ich liczbie. Nie zależy od wartości.
• RANGE działa na logicznym zakresie wartości ustawionym dla kolumny z ORDER BY.

Dla porównania przykład. Załóżmy, że mamy tabelę sales z danymi:

Porównajmy zapytania:

ROWS:

SELECT
    id,
    SUM(amount) OVER (
        ORDER BY amount
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_rows
FROM sales;

Wynik:

Tutaj każdy wiersz system dodaje do sumy w rzeczywistej kolejności pojawiania się.

RANGE:

SELECT 
    id,
    SUM(amount) OVER (
        ORDER BY amount
        RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS sum_range
FROM sales;

Wynik:

Tutaj wiersze 1 i 2 zostały połączone, bo ich amount = 100. RANGE bierze pod uwagę powtarzające się wartości w kolumnie amount.

Przykłady realnych zadań

1. Obliczanie przyrostu dochodu

Zadanie: policzyć zmianę dochodu względem poprzedniego wiersza.

SELECT 
    month,
    revenue,
    revenue - LAG(revenue) OVER (
        ORDER BY month
    ) AS revenue_change
FROM sales_data;

2. Porównanie bieżącego wiersza ze średnią w grupie

Zadanie: dla każdego działu policzyć różnicę pensji pracownika względem średniej pensji w dziale.

SELECT 
    department_id,
    employee_id,
    salary,
    salary - AVG(salary) OVER (
        PARTITION BY department_id
    ) AS salary_diff
FROM employees;

Błędy przy użyciu ROWS i RANGE

Źle podana kolejność wierszy (ORDER BY): Jeśli nie podasz sortowania, PostgreSQL wywali błąd, bo nie ogarnie, który wiersz jest bieżący.

Mieszanie podejść ROWS i RANGE w jednym zadaniu: Wybieraj podejście zależnie od danych. ROWS jest spoko do zadań z ustaloną liczbą wierszy, a RANGE — do zakresów wartości.

Pomijanie powtarzających się wartości w RANGE: Pamiętaj, że RANGE bierze pod uwagę wszystkie powtarzające się wartości, co może mocno zmienić wynik.