Window function calculates values over all rows and provide a value for each row in contrast to the Aggregate function which provides single value for the group.
Window function in SQL always require the OVER() clause, which is the window holding the set of rows over which the function operates.
The functions are generally grouped into three main categories:
| Category | Function |
|---|---|
| Aggregate | SUM(), AVG(), COUNT(), MIN(), MAX() |
| Ranking | RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE(n) |
| Analytical | LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), CUME_DIST(), PERCENT_RANK() |
Example:
SELECT
employee_id,
salary,
department_id,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;