Funkcje okna w SQL, to te które korzystają z klauzuli OVER (). Dzięki tej klauzuli np. sortujemy produkty nie względem całej tabeli, ale w obrębie „okien” na jakie tabela jest dzielona.
RANK
RANK () OVER (PARTITION BY product_group ORDER BY product_price desc, product_date)
Powyższy kod uszeregowuje produkty wg najwyższej wartości product_price, a gdy napotka te same wartości to wg najmniejszej wartości product_date w obrębie grupy (product_group)
Klauzulę PARTITION BY można usunąć by otrzymać uszeregowanie na całej tabeli:
RANK () OVER (ORDER BY product_price desc, product_date)
LEAD
LEAD umożliwia odniesienie się do wierszy o pozycji następnej.
LEAD (payment_date,1) OVER (PARTITION BY client_id ORDER BY payment_date)
Powyższy kod do obecnego wiersza dodaje kolumnę z payment_date następnej płatności klienta.
LAG
LAG odnosi się do wierszy o pozycji poprzedniej.
LEAD (payment_date,1) OVER (PARTITION BY client_id ORDER BY payment_date)
Zmieniając wartość argumentu 1 na -1, kod zwróci tę samą wartość co przytoczony LEAD
AVG, SUM, COUNT, MIN, MAX
Np. do dodania kolumny z najmniejszą wartością product_price w obrębie grupy:
MIN (product_price) OVER (PARTITION BY product_group ORDER BY product_price)
Funkcje te są szczególnie przydatne, gdy musimy obliczyć agregaty jakichś danych, ale nie chcemy używać GROUP BY, by zwrócić wszystkie wiersze tabeli.
ROWS UNBOUNDED PRECEDING
Okno danych możemy dalej ograniczać, dodając do niego „ramkę”
SUM (product_price) OVER (PARTITION BY product_group ORDER BY product_price ROWS UNBOUNDED PRECEDING)
W tym przypadku liczymy sumę kumulatywną product_price. Przykładowo, dla drugiego wiersza wg product_price, obliczona będzie suma jego product_price oraz wszystkich poprzednich (w obrębie grupy product_group).
Możemy działać także w drugą stronę – obliczać sumę tylko obecnego wiersza i następnych:
SUM (product_price) OVER (PARTITION BY product_group ORDER BY product_price ROWS UNBOUNDED FOLLOWING)
Lub dokładnie precyzować wielkość ramki:
SUM (product_price) OVER (PARTITION BY product_group ORDER BY product_price ROWS 2 PRECEDING)
SUM (product_price) OVER (PARTITION BY product_group ORDER BY product_price ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING)