Funkcja okna liczy dla każdego wiersza wartość ze zbioru powiązanych wierszy, czyli jego okna, nie łącząc wierszy tak, jak robi to GROUP BY. Zapisujesz ją jako function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY dzieli wiersze na grupy, a ORDER BY ustala kolejność wewnątrz każdej grupy.
Każdy wiersz zostaje w wyniku i niesie sumę swojego regionu: 1000 dla north, 500 dla south. Udział każdego handlowca to wtedy jedno wyrażenie (ana ma 50 procent north). Każdy agregat (sum, count, avg, min, max) staje się funkcją okna, gdy dodasz OVER. Puste OVER () robi z całego wyniku jedno okno.
Numerowanie wierszy przez ROW_NUMBER, RANK i DENSE_RANK
Trzy funkcje rankingowe różnią się tylko wtedy, gdy wiersze remisują w wartości z ORDER BY:
ben i cy mają po 85. row_number i tak daje im 2 i 3; kto dostanie 2, nie jest określone, chyba że ORDER BY rozstrzyga remis. rank daje obu 2 i przeskakuje do 4 dla dee. dense_rank daje obu 2 i kontynuuje od 3.
| Funkcja | Remisy | Luki po remisach |
|---|---|---|
row_number() | różne numery | nie |
rank() | ten sam numer | tak |
dense_rank() | ten sam numer | nie |
Pierwsze N wierszy w każdej grupie
Funkcje okna są liczone po WHERE, więc nie możesz filtrować po nich bezpośrednio. Ponumeruj wiersze w podzapytaniu albo CTE, a potem filtruj na zewnątrz:
To zwraca dwóch najlepszych handlowców w każdym regionie. Użyj rank() zamiast row_number(), żeby zachować każdy wiersz remisujący o drugie miejsce. Dla samego pierwszego wiersza w grupie krótsze jest DISTINCT ON.
Suma narastająca i średnia ruchoma
Z ORDER BY w OVER agregat działa na ramce. Domyślnie ramka sięga od pierwszego wiersza partycji do bieżącego, co daje sumę narastającą. Klauzula ROWS BETWEEN ustawia inną ramkę, na przykład bieżący wiersz i dwa przed nim:
Suma narastająca dochodzi w dniu 5 do 130, a średnia z trzech dni w dniu 5 to (30 + 20 + 50) / 3 = 33.3.
Domyślna ramka to RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Obejmuje każdy wiersz, który remisuje z bieżącym w wartości z ORDER BY, więc dwa wiersze z tym samym dniem dostają tę samą sumę narastającą. Napisz ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, żeby dodawać je po jednym.
Porównanie z poprzednim wierszem: LAG i LEAD
lag(col) czyta wartość z wiersza przed bieżącym w kolejności okna, a lead(col) z wiersza po nim. Obie zwracają NULL poza krawędzią, chyba że jako trzeci argument podasz wartość domyślną.
Dzień 1 nie ma poprzedniego wiersza, więc previous i change to NULL. Klauzula WINDOW definiuje okno raz, pod nazwą, żeby kilka funkcji mogło z niego korzystać.
first_value działa z domyślną ramką, ale last_value nie: domyślna ramka kończy się na bieżącym wierszu, więc zwraca wartość bieżącego wiersza. Dodaj ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, żeby ramka obejmowała całą partycję. Inne funkcje dostępne tylko jako funkcje okna to ntile(n), percent_rank(), cume_dist() i nth_value(); dokumentacja funkcji okna w PostgreSQL wymienia je wszystkie.
Filtrowanie wierszy liczonych przez agregat
FILTER (WHERE ...) działa też z agregatami okna, dając warunkowe sumy w każdym wierszu:
SELECT day,
count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;
Najczęściej zadawane pytania
Czym różni się GROUP BY od PARTITION BY?
GROUP BY zwraca jeden wiersz na grupę. PARTITION BY wewnątrz OVER zachowuje każdy wiersz i daje mu wartość policzoną dla jego grupy, więc możesz pokazać kwotę wiersza obok sumy jego grupy.
Czy można użyć funkcji okna w WHERE?
Nie. Funkcje okna działają po WHERE, GROUP BY i HAVING, więc wolno ich używać tylko na liście kolumn i w ORDER BY. Policz wartość w podzapytaniu albo CTE i filtruj w zewnętrznym zapytaniu.
Dlaczego LAST_VALUE zwraca wartość bieżącego wiersza?
Przy ORDER BY w OVER domyślna ramka kończy się na bieżącym wierszu (i wierszach mu równych), więc ostatnim wierszem ramki jest bieżący. Dodaj ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, żeby ramka obejmowała całą partycję.
Czym różnią się ROWS i RANGE w ramce?
ROWS liczy fizyczne wiersze: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW to bieżący wiersz i dwa przed nim. RANGE działa na wartości z ORDER BY, więc wiersze o równych wartościach (peers) zawsze są w ramce razem.