Funkcje okienkowe służa do wykonania obliczeń na zbiorze wierszy powiązanych
w jakiś sposób z bieżącym wierszem. Przypomina to funkcje agregujące, lecz
nie wymaga grupowania frazą GROUP BY.
Najczęściej umieszcza się je we frazie SELECT, gdzie
służą do obliczeń z użyciem okna: grupy wierszy powiązanych z bieżącym
wierszem zapytania. Wartości funkcji oblicza się na podstawie wszystkich
wierszy wchodzących w skład bieżącego okna.
Składnia:
funkcja(argument,...) [OVER ([klauzula-dzielenia] [klauzula-sortowania]
[klauzula-ramki])]
gdzie klauzula-dzielenia to
PARTITION BY wyrażenie[{,wyrażenie}...]
klauzula-sortowania to
ORDER BY wyrażenie [ASC|DESC] [{,wyrażenie}...]
zaś klauzula-ramki
{ROWS|RANGE} {początek-ramki|zakres-ramki}
Klauzula dzielenia PARTITION BY (ang.
partition definition) grupuje wiersze
wyniku w partycje, podobnie jak GROUP BY, np.
PARTITION BY gatunek
Klauzula sortowania ORDER BY (ang order definition)
porządkuje wiersze w ramach partycji, np.
ORDER BY waga DESC
Klauzula ramki (ang frame definition) pozwala określić wiersze, które będą użyte podczas obliczania wartości funkcji. Ramka przesuwa się ze zmianą bieżącego wiersza. Oprócz własnego zakresu, ramka jest róœnież ograniczona w ramach partycji.
Wszystkie funkcje agregujące (COUNT, SUM, AVG, MAX, MIN) mogą być użyte jako funkcje okienkowe.
SELECT imie, gatunek, waga, waga / avg(waga) OVER (PARTITION BY gatunek) FROM Zwierzaki;
Funkcja ROW_NUMBER() podaje numer kolejny wiersza w ramach
partycji, zaczynając od 1. Jeśli nie było ORDER BY, to kolejność jest
losowa.
SELECT gatunek, imie, wiek,
row_number() OVER (PARTITION BY gatunek ORDER BY wiek)
FROM Zwierzaki;
Funkcja RANK() podaje podobnie kolejne pozycje, ale
Poniższe zapytanie porządkuje zwierzaki w gatunkach według wagi.
SELECT gatunek, imie, waga,
rank() OVER (PARTITION BY gatunek ORDER BY waga)
FROM Zwierzaki;
W kolejnym zapytania wszystkie zwierzaki z tego samego gatunku dostaną tę samą ,,rangę''.
SELECT imie, gatunek, waga,
rank() OVER (ORDER BY gatunek)
FROM Zwierzaki;
Funkcja dense_rank() działa podobnie, lecz nie zostawia
pustych miejsc w numeracji.
SELECT dname, empno, sal, avg(sal) OVER (PARTITION BY dept.deptno) FROM emp JOIN dept on emp.deptno = dept.deptno; deptname | empno | sal | avg -----------+-------+-------+----------- develop | 11 | 5200 | 5020.0000 develop | 7 | 4200 | 5020.0000 develop | 9 | 4500 | 5020.0000 develop | 8 | 6000 | 5020.0000 develop | 10 | 5200 | 5020.0000 personnel | 5 | 3500 | 3700.0000 personnel | 2 | 3900 | 3700.0000 sales | 3 | 4800 | 4866.6667 sales | 1 | 5000 | 4866.6667 sales | 4 | 4800 | 4866.6667 (10 rows)
Jeśli chcielibyśmy mieć 2 najlepiej zarabiających z każdego działu, musimy użyć podzapytania:
SELECT dname, empno, sal
FROM
(SELECT dname, empno, sal,
rank() OVER (PARTITION BY dname ORDER BY sal DESC, empno) AS pos
FROM emp JOIN dept on emp.deptno = dept.deptno
) AS ss
WHERE pos < 3;
Jeśli chcemy wywołać kilka funkcji okienkowych o tym samej strukturze ramki, można użyć frazy (klauzuli) WINDOW, żeby uniknąć kopiowania
SELECT sum(sal) OVER w, avg(sal) OVER w FROM emp JOIN dept on emp.deptno = dept.deptno WINDOW w AS (PARTITION BY dname ORDER BY sal DESC);
Tabela z informacjami o wielkości opadów w różnych dniach dla różnych miejsc, chcemy policzyć lokalną sumę opadów do danego dnia.
CREATE TABLE Opady ( data DATE, miejsce VARCHAR(30), opad INTEGER, PRIMARY KEY(data,miejsce) );
Bez okienek:
SELECT data, miejsce, opad,
(SELECT SUM(opad)
FROM Opady AS c2
WHERE c2.miejsce = c1.miejsce AND
c2.data <= c1.data) AS razem
FROM Opady AS c1
ORDER BY miejsce, data;
Dzięki okienkom unika się duplikowania:
Partition: PARTITION BY miejsce
Order: ORDER BY data
Frame: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
W sumie:
SELECT data, miejsce, opad,
SUM(opad) OVER (PARTITION BY miejsce
ORDER BY data
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW) AS razem
FROM Opady
ORDER BY miejsce, data;
1. Wypisz imiona i wiek wszystkich zwierzaków, które nie mają towarzystwa
(zwierzaka tego samego gatunku) i posortuj ich według wieku malejaco.
Użyj PARTITION BY i perspektywy lokalnej zamiast JOIN.
2. Dla każdego zwierzaka policz średnią wagę w jego gatunku, a także jaka byłaby ta średnia, gdyby się go pozbyć (może się przyda funkcja COALESCE(args), która zwraca pierwszy argument nie będący NULLem, np. COALESCE(NULL, 0) zwraca 0).
3. Dla każdego zwierzaka wypisz poprzednika i następnika na liście posortowanej według wagi. Dla najlżejszego zwierzaka poprzednik powinien być NULLem, a dla najcięższego następnik powinien być NULLem.
WITH towarzystwo AS (
SELECT imie, wiek,
COUNT(id) OVER (PARTITION BY gatunek) AS ilu
FROM Zwierzaki
WHERE gatunek IS NOT NULL
)
SELECT imie, wiek
FROM towarzystwo
WHERE ilu = 1
ORDER BY wiek DESC;
1.2
WITH statystyki AS (
SELECT imie, gatunek,
AVG(waga) OVER (PARTITION BY gatunek) AS gat_avg,
COUNT(id) OVER (PARTITION BY gatunek) AS gat_count,
SUM(waga) OVER (PARTITION BY gatunek ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS pred_sum,
SUM(waga) OVER (PARTITION BY gatunek ORDER BY id ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS succ_sum
FROM Zwierzaki
)
SELECT
imie, gatunek,
gat_avg,
(COALESCE(pred_sum, 0) + COALESCE(succ_sum, 0)) / (gat_count - 1) AS gat_avg_without_this
FROM statystyki
WHERE gat_count > 1
ORDER BY 3;
1.3
SELECT
imie,
RANK() OVER (ORDER BY waga) AS "rank",
MAX(imie) OVER (ORDER BY waga, imie ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING) AS "next",
MAX(imie) OVER (ORDER BY waga, imie ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS "prev"
FROM Zwierzaki
ORDER BY 2, imie;
-- Elegancko.
SELECT
imie,
RANK() OVER (ORDER BY waga) AS "rank",
LEAD(imie, 1) OVER (ORDER BY waga, imie) AS "next",
LAG(imie, 1) OVER (ORDER BY waga, imie) AS "prev"
FROM Zwierzaki
ORDER BY 2, imie;