Window function wykonuje obliczenia w całym zestawie wierszy tabeli, które są w pewien sposób związane z bieżącym wierszem. Jest to porównanie do pewnym typów obliczeń, które mogą być wykonywane z funkcjami agregującymi. Ale w przeciwieństwie do zwykłych funkcji agregujących, użycie window function nie powoduje, że wiersze będą zgrupowane w jednym wierszu wyniku - ale będą zachowywać swoje odrębne tożsamości. Są one w stanie uzyskać dostęp do więcej niż tylko bieżącego wiersza wyniku zapytania.
Tak mniej więcej wygląda szkielet zapytania z użyciem window function:
SELECT ... nazwa_fun (wyrażenie [, wyrażenie]) OVER (def_fun_window) FROM ...Definicja funkcji (def_fun_window):
SELECT ... nazwa_fun (wyrażenie [, wyrażenie]) OVER nazwa_fun_window FROM ...
SELECT ... nazwa_fun OVER (def_fun_window) FROM ...
SELECT ... nazwa_fun OVER nazwa_fun_window FROM ...
[istniejąca_fukcja_window]
[PARTITION by wyrażenie [, ...]]
[ORDER BY wyrażenie [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...]]
[{ RANGE | ROWS } frame_start ]
[{ RANGE | ROWS } BETWEEN frame_start AND frame_end]
Gdzie frame_start i frame_end to jedna z wartości:
- UNBOUNDED PRECEDING
- value PRECEDING
- CURRENT ROW
- value FOLLOWING
- UNBOUNDED FOLLOWING
Wiemy już z czym będziemy się zmagać, więc jak zawsze zaczynam od przedstawienia prostej tabeli, zawierającej kilka elementów, nad którą będziemy pracować:
test=# select * from users; id | name | country ----+-------+--------- 1 | Ula | PL 5 | Kasia | PL 6 | Beata | PL 7 | Marta | PL 3 | Ela | GB 4 | Kuba | GB 2 | Ola | NL (7 rows)
Zobaczmy kilka przykładów:
test=# select name, count(*) OVER (partition by country) as x from users; name | x -------+--- Ela | 2 Kuba | 2 Ola | 1 Ula | 4 Marta | 4 Kasia | 4 Beata | 4 (7 rows)
Opcja PARTITION BY grupuje wiersze zapytania w partycje, które są przetwarzane oddzielnie przez window function. Działa to podobnie do zapytań z klauzurą GROUP BY, z wyjątkiem, że jego wyrażenia są zawsze tylko wyrażeniami i nie mogą być nazwami kolumn wyjściowych lub numerami. Bez opcji PARTITION BY, wszystkie wiersze produkowane przez zapytanie są traktowane jako pojedyncza partycja (jedna partycja zawierająca wszystkie dane).
test=# select count(*),
count(*) OVER () as x
from users group by country;
count | x
-------+---
2 | 3
1 | 3
4 | 3
(3 rows)
test=# select count(*),
count(*) OVER (order by country) as x
from users group by country;
count | x
-------+---
2 | 1
1 | 2
4 | 3
(3 rows)
Opcje ORDER BY określa kolejność, w której rzędy partycji są przetwarzane przez window function. Działa to podobnie jak w zapytaniach z ORDER BY z poziomu klauzuli, ale różniej nie można użyć nazw kolumn wyjściowych lub cyfr. Bez opcji ORDER BY wiersze są przetwarzane w bliżej nieokreślonym porządku.
Wiersze brane pod uwagę przez window function są to wiersze z tzw. wirtualnej tabeli, czyli wyprodukowane przez kwerendy FROM i filtrowane przez jej klauzule WHERE, GROUP BY i HAVING jeśli takie istnieją. Np. wiersze usunięte przez klauzulę WHERE, nie są widziane przez window function. Zapytanie może zawierać wiele window function, które mogą kawałkować dane w różnych sposób za pomocą OVER, ale wszystkie działają na tej samej kolekcji wierszy określonych przez tabelę wirtualną.
Oprócz dostępnych funkcji agregujących, możemy używać kilku wbudowanych window function, które znajdziecie pod tym linkiem tutaj.
Użycie funkcji agregującej bez słowa kluczowego OVER, jest traktowane jako wywołanie zwykłej funkcji agregującej (a nie jako window function).
test=# test=# select id, name, country, row_number() over (partition by country) as notordered, row_number() over (partition by country order by id) as ordered from users order by id; id | name | country | notordered | ordered ----+-------+---------+------------+--------- 1 | Ula | PL | 1 | 1 2 | Ola | NL | 1 | 1 3 | Ela | GB | 1 | 1 4 | Kuba | GB | 2 | 2 5 | Kasia | PL | 3 | 2 6 | Beata | PL | 4 | 3 7 | Marta | PL | 2 | 4 (7 rows)
Teraz może coś bardziej życiowego. W tabeli test_window_function przechowuję informację o kategoriach. Chciała bym wiedzieć, ile jest każdej z nich i jaki to jest procent z całości. Teraz to już nic prostszego:
test=# select
category_id,
count(1),
((count(1) / sum(count(1)) over ())::float * 100) || '%' as "% of categories"
from test_window_function group by category_id;
category_id | count | % of categories
-------------+-------+-----------------
6 | 7 | 7%
8 | 8 | 8%
2 | 5 | 5%
1 | 16 | 16%
3 | 10 | 10%
10 | 7 | 7%
4 | 10 | 10%
5 | 11 | 11%
9 | 13 | 13%
7 | 13 | 13%
(10 rows)
Jeśli twoje zapytanie posiada wiele window function, które mają te same zachowania, lepiej jest przenieść ich definicję do klauzuli WINDOW, na przykład w ten sposób:
test=# select id, name, country, row_number() over w, count(1) over w from users WINDOW w AS (partition by country) order by id; id | name | country | row_number | count ----+-------+---------+------------+------- 1 | Ula | PL | 1 | 4 2 | Ola | NL | 1 | 1 3 | Ela | GB | 1 | 2 4 | Kuba | GB | 2 | 2 5 | Kasia | PL | 3 | 4 6 | Beata | PL | 4 | 4 7 | Marta | PL | 2 | 4 (7 rows)
Przedstawione informację w tym poście to zaledwie początek wiedzy na temat window function. Mam nadzieję, że w przyszłych postach znajdę czas, aby przyjrzeć się bliżej tego typu zapytaniom.
