Funkcje okna w SQLite: OVER, PARTITION BY i ramki

Funkcja okna dodaje kolumnę, nie scalając wierszy

GROUP BY zamienia wiele wierszy w jeden. Funkcja okna robi coś innego: oblicza wartość na zbiorze powiązanych wierszy, ale zachowuje każdy wiersz wejściowy w wyniku. Dostajesz szczegóły wiersz po wierszu i agregat obok siebie.

Schemat jest zawsze taki sam: funkcja, a potem OVER (...).

CREATE TABLE sales (
    employee TEXT,
    region   TEXT,
    amount   INTEGER
);

INSERT INTO sales VALUES
    ('Ada',   'EU', 100),
    ('Boris', 'EU', 250),
    ('Cleo',  'EU', 175),
    ('Dan',   'US', 300),
    ('Eve',   'US', 150);

SELECT employee, region, amount,
       SUM(amount) OVER () AS total_all
FROM sales;

Kolumna total_all pokazuje sumę całkowitą wszystkich wierszy, powtórzoną w każdej linii. Oryginalne wiersze pozostają nietknięte. Porównaj to z SELECT SUM(amount) FROM sales: ta sama liczba, ale wraca tylko jeden wiersz. Funkcje okna dają oba widoki naraz.

PARTITION BY: agregacja w grupach

Puste OVER () agreguje po całej tabeli. Dodaj PARTITION BY, żeby agregować w grupach, podobnie jak przy GROUP BY, ale znowu bez scalania wierszy.

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount) OVER (PARTITION BY region) AS region_total,
       amount * 100.0 / SUM(amount) OVER (PARTITION BY region) AS pct_of_region
FROM sales;

Każdy wiersz dostaje sumę swojego regionu i swój udział w tej sumie. Przy zwykłym GROUP BY szczegóły dla poszczególnych pracowników by zniknęły. To główna zaleta funkcji okna: szczegóły i agregat w jednym zapytaniu.

Ranking: ROW_NUMBER, RANK, DENSE_RANK

Rodzina funkcji rankingowych numeruje wiersze według ORDER BY wewnątrz OVER. Trzy warianty różnią się obsługą remisów.

CREATE TABLE scores (player TEXT, points INTEGER);
INSERT INTO scores VALUES
    ('Ada', 90), ('Boris', 85), ('Cleo', 90),
    ('Dan', 70), ('Eve', 85);

SELECT player, points,
       ROW_NUMBER() OVER (ORDER BY points DESC) AS rn,
       RANK()       OVER (ORDER BY points DESC) AS rk,
       DENSE_RANK() OVER (ORDER BY points DESC) AS dr
FROM scores;

Jak czytać wynik:

• ROW_NUMBER() jest zawsze unikalny: remisy są rozstrzygane dowolnie. Używaj go, gdy potrzebujesz stabilnego, odrębnego numeru dla każdego wiersza.
• RANK() daje remisującym wierszom tę samą pozycję, a potem pomija kolejne numery. Po dwóch graczach remisujących na pozycji 1 następuje pozycja 3.
• DENSE_RANK() też uwzględnia remisy, ale niczego nie pomija. Następna pozycja to 2.

Aby uzyskać „top N w każdej grupie”, połącz ranking z PARTITION BY i filtruj w zapytaniu zewnętrznym: WHERE nie może bezpośrednio odwoływać się do funkcji okna:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT *
FROM (
    SELECT employee, region, amount,
           ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
    FROM sales
)
WHERE rn <= 2;

Dwie osoby z najwyższą sprzedażą w każdym regionie.

LAG i LEAD: zajrzyj do sąsiednich wierszy

LAG(col) zwraca wartość col z poprzedniego wiersza w oknie. LEAD(col) sięga do przodu. Obie idealnie nadają się do pytań o zmiany w czasie.

CREATE TABLE temps (day INTEGER, celsius INTEGER);
INSERT INTO temps VALUES (1, 18), (2, 21), (3, 19), (4, 24), (5, 22);

SELECT day, celsius,
       LAG(celsius)  OVER (ORDER BY day) AS yesterday,
       celsius - LAG(celsius) OVER (ORDER BY day) AS delta
FROM temps;

W pierwszym wierszu yesterday to NULL: przed nim nic nie ma. Możesz podać wartość domyślną: LAG(celsius, 1, celsius) OVER (ORDER BY day) użyje dzisiejszej wartości, gdy poprzedni wiersz nie istnieje.

LEAD to lustrzane odbicie. Połącz obie funkcje z PARTITION BY, żeby dostać sekwencje w grupach, na przykład porównać sprzedaż z tego miesiąca z poprzednim miesiącem w każdym regionie.

Sumy narastające z ramkami okna

Dodaj ORDER BY wewnątrz OVER, a funkcje agregujące takie jak SUM, AVG, COUNT zaczną liczyć narastająco:

CREATE TABLE deposits (day INTEGER, amount INTEGER);
INSERT INTO deposits VALUES (1, 50), (2, 30), (3, 70), (4, 20), (5, 40);

SELECT day, amount,
       SUM(amount) OVER (ORDER BY day) AS running_total,
       AVG(amount) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS avg_3day
FROM deposits;

Dwie rzeczy, na które warto zwrócić uwagę:

• SUM(amount) OVER (ORDER BY day) to suma narastająca. Domyślna ramka, gdy piszesz ORDER BY bez jawnej ramki, to „od początku okna do bieżącego wiersza”.
• Druga kolumna używa jawnej ramki: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. To przesuwane okno z trzech wierszy, czyli średnia krocząca.

Model myślowy ramek: każda funkcja okna jest obliczana na ramce wierszy zdefiniowanej względem bieżącego wiersza. Typowe ramki:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: suma narastająca (domyślna, gdy ramka nie jest podana).
• ROWS BETWEEN N PRECEDING AND CURRENT ROW: okno kroczące wstecz.
• ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING: cała partycja.

ROWS liczy fizyczne wiersze. Jest też RANGE, które grupuje według wartości: przydaje się, gdy w kolumnie ORDER BY są remisy i chcesz, żeby były traktowane jako jeden krok.

FIRST_VALUE, LAST_VALUE, NTILE

Kilka innych funkcji okna, które warto znać:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150),('Finn','US',220);

SELECT employee, region, amount,
       FIRST_VALUE(employee) OVER (PARTITION BY region ORDER BY amount DESC) AS top_earner,
       NTILE(2) OVER (ORDER BY amount DESC) AS half
FROM sales;

• FIRST_VALUE i LAST_VALUE zwracają pierwszą lub ostatnią wartość w ramce. Przy LAST_VALUE uważaj na ramkę: domyślna ramka kończy się na CURRENT ROW, więc zwykle potrzebujesz ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, żeby dostać faktyczną ostatnią wartość partycji.
• NTILE(n) dzieli wiersze na n mniej więcej równych koszyków: przydaje się do kwartyli, percentyli i podziałów w stylu testów A/B.

Nazywanie okna przez WINDOW

Gdy kilka kolumn ma tę samą klauzulę OVER (...), powtarzanie jej staje się męczące. SQLite pozwala nazwać okno raz i używać go wielokrotnie:

CREATE TABLE sales (employee TEXT, region TEXT, amount INTEGER);
INSERT INTO sales VALUES
    ('Ada','EU',100),('Boris','EU',250),('Cleo','EU',175),
    ('Dan','US',300),('Eve','US',150);

SELECT employee, region, amount,
       SUM(amount)   OVER region_window AS region_total,
       AVG(amount)   OVER region_window AS region_avg,
       RANK()        OVER region_window AS region_rank
FROM sales
WINDOW region_window AS (PARTITION BY region ORDER BY amount DESC);

To samo zapytanie, mniej szumu. Klauzula WINDOW stoi po WHERE/GROUP BY/HAVING, a przed ORDER BY.

Funkcje okna vs GROUP BY

Oba mechanizmy dotyczą agregacji, ale odpowiadają na różne pytania:

• GROUP BY redukuje. Jeden wiersz na grupę. Używaj go, gdy chcesz tylko podsumowania.
• Funkcje okna zachowują. Każdy wiersz wejściowy przetrwa, a obok pojawiają się dodatkowe obliczone kolumny.

Jeśli kiedyś robisz GROUP BY, a potem łączysz agregaty z powrotem z oryginalną tabelą, to mocny sygnał, że funkcja okna załatwiłaby sprawę jednym zapytaniem.

Kilka pułapek

• WHERE nie może odwoływać się do funkcji okna. Filtry działają, zanim okna zostaną obliczone. Opakuj zapytanie w podzapytanie albo CTE i filtruj na poziomie zewnętrznym.
• Niejawne ramki potrafią zaskoczyć. SUM(x) OVER (ORDER BY y) to suma narastająca, bo domyślna ramka to RANGE UNBOUNDED PRECEDING. Jeśli chodziło ci o sumę całej partycji, napisz OVER (PARTITION BY ...) bez ORDER BY albo podaj ramkę jawnie.
• LAST_VALUE za pierwszym razem zaskakuje każdego. Przy domyślnej ramce kończącej się na bieżącym wierszu zwraca bieżącą wartość, a nie ostatnią wartość partycji. Nadpisz ramkę.
• Funkcje okna wymagają SQLite 3.25+ (wydanego w 2018 roku). Każda w miarę nowa instalacja je ma, ale niektóre środowiska wbudowane są w tyle.

Dalej: kolumny generowane

Funkcje okna to obliczenia w momencie zapytania. Następna strona omawia obliczenia w momencie zapisu: kolumny generowane, których wartość jest zdefiniowana wyrażeniem i aktualizowana automatycznie, gdy zmieniają się dane źródłowe.