Obliczanie sum narastających z użyciem funkcji okiennych: SUM(), AVG()

Wyobraź sobie: śledzisz przychody swojej firmy, sprzedaż w sklepie internetowym albo po prostu analizujesz swoje wydatki w ciągu roku. Potrzebujesz nie tylko widzieć przychody lub wydatki za każdy miesiąc, ale też rozumieć, jak się one kumulują z miesiąca na miesiąc.

Zwykłe funkcje agregujące (GROUP BY) tu nie pomogą — pogrupują dane i zwrócą jeden wiersz na grupę. A co, jeśli chcesz widzieć każdy miesiąc i jednocześnie liczyć sumę narastającą? Tu właśnie wchodzi do gry SUM() razem z funkcjami okiennymi.

Podstawy użycia funkcji okiennych do sum narastających

Funkcje okienne pozwalają robić operacje agregujące na ramkach okiennych. Dzięki temu możemy np. sumować wartości w każdym wierszu, ale bez usuwania innych wierszy. Koniec z poświęcaniem się dla GROUP BY!

Składnia SUM() z funkcją okienną

Oto podstawowy szablon do liczenia sumy narastającej:

SELECT
    column_name,
    SUM(column_name) OVER (PARTITION BY partition_column ORDER BY order_column) AS cumulative_sum
FROM 
    table_name;

Tu:

• SUM(column_name) — sumuje wartości.
• OVER() — ustawia okno do obliczeń.
• PARTITION BY — dzieli dane na grupy (opcjonalnie).
• ORDER BY — ustala kolejność wierszy w oknie.

Przykład: narastający przychód miesiącami

Wyobraź sobie tabelę twoich przychodów:

Chcemy zobaczyć przychód za każdy miesiąc i narastający wynik. Spróbujmy napisać zapytanie SQL:

SELECT
    miesiąc,
    przychód,
    SUM(przychód) OVER (ORDER BY miesiąc) AS narastający_przychód
FROM 
    przychody;

Wynik:

Co się dzieje:

1. ORDER BY miesiąc w OVER() mówi PostgreSQL, żeby brał wiersze w kolejności chronologicznej.
2. Dla każdego wiersza suma liczona jest z uwzględnieniem wszystkich poprzednich wierszy (i bieżącego).

Pomyśl dobrze, co tu się dzieje. Dla pierwszego wiersza SUM() liczy tylko pierwszy wiersz, dla drugiego - sumę dwóch, dla trzeciego - sumę trzech. Dlatego kolejność miesięcy jest mega ważna!

Przykład: narastający przychód według regionów

Gdybyś miał tabelę sprzedaży według regionów, fragment mógłby wyglądać tak:

Teraz chcemy liczyć narastający przychód osobno dla każdego regionu:

SELECT
    region,
    miesiąc,
    przychód,
    SUM(przychód) OVER (PARTITION BY region ORDER BY miesiąc) AS narastający_przychód
FROM 
    sprzedaż;

Wynik będzie taki:

Teraz każdy region analizowany jest osobno (PARTITION BY region), ale w środku regionu wiersze są uporządkowane po czasie (ORDER BY miesiąc).

Średnia krocząca (AVG())

Okej, sumy narastające są spoko, ale co jeśli chcesz analizować trendy, np. za ostatnie 3 miesiące? Tu przyda się średnia krocząca.

Przykład: średnia krocząca przychodów

Znowu pracujemy z tabelą przychody, oto jej dane:

Zapytanie do obliczenia 3-miesięcznej średniej kroczącej:

SELECT
    miesiąc,
    przychód,
    AVG(przychód) OVER (
        ORDER BY miesiąc 
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS średnia_krocząca
FROM 
    przychody;

Wynik:

Wyjaśnienie:

1. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW mówi PostgreSQL, żeby patrzył na bieżący wiersz i dwa poprzednie wiersze przy liczeniu średniej.
2. W efekcie dla każdego miesiąca widzimy średni przychód z ostatnich 3 miesięcy.

Czyli dla każdego wiersza ustawiamy okno na 3 wiersze: bieżący i dwa poprzednie. I po nich liczymy średnią. Mega wygodne.

Jak działa ORDER BY i jego wpływ

Funkcje okienne zależą od poprawnej kolejności wierszy. Jeśli kolejność jest zła (albo jej nie ma), wyniki mogą być dziwne.

Przykład: błąd przez brak ORDER BY

Jeśli wyrzucimy ORDER BY z OVER(), zamiast sumy narastającej dostaniemy sumę wszystkich przychodów dla każdego wiersza:

SELECT
    miesiąc,
    przychód,
    SUM(przychód) OVER () AS zła_suma_narastająca
FROM 
    przychody;

Wynik:

Wiersze nie są uporządkowane i zamiast sumy narastającej funkcja po prostu sumuje wszystkie wiersze bez rozróżnienia.

Realne case'y użycia

Analiza przychodów:

• Sumy narastające pozwalają śledzić, jak rośnie sprzedaż albo przychody firmy.
• Średnia krocząca pomaga zobaczyć "czysty" trend bez szumów.

Modelowanie finansowe:

Banki i firmy finansowe używają funkcji okiennych do analizy spłat, wzrostu zadłużenia i innych metryk.

Budowanie szeregów czasowych:

Dane czasowe, takie jak liczba użytkowników online, odsłony stron, przychód itd., idealnie analizuje się z SUM() i AVG().