Window functions in SQL are advanced functions used to perform calculations across a set of table rows related to the current row within a result set, without collapsing the result set into a single value. They are often used for tasks like running totals, ranking, and moving averages.
The syntax for window functions is:
sql
SELECT column1, column2, window_function() OVER (PARTITION BY column ORDER BY column)
FROM table;
Key components of window functions:
– PARTITION BY: Divides the result set into partitions to apply the window function to each partition separately.
– ORDER BY: Defines the order of rows within each partition.
– Window Function Types:
– Ranking functions: ROW_NUMBER(), RANK(), DENSE_RANK()
– Aggregate functions: SUM(), AVG(), COUNT()
– Analytical functions: LEAD(), LAG(), FIRST_VALUE()
Example:
sql
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS rank
FROM employees;
This ranks employees by salary without reducing the result set. Window functions provide powerful analysis capabilities in SQL.