Introduction to Window Functions
SQL · Window Functions
Theory
A window function calculates a value for each row by looking at a set of related rows, called the window. The key difference from GROUP BY is that rows are not collapsed: GROUP BY dept turns 6 employees into 3 summary rows, while AVG(salary) OVER (PARTITION BY dept) keeps all 6 rows and adds the department average as an extra column on each one.
Every window function call has the shape function() OVER (...). Inside OVER you can say three things: PARTITION BY (which rows belong together), ORDER BY (in what sequence to walk through them) and an optional frame (which slice of the partition counts for this row, such as 'the previous 2 rows'). Window functions run after WHERE, GROUP BY and HAVING, just before the final ORDER BY, which is why you cannot use them in WHERE. To filter on one, wrap the query in a subquery or CTE.
Why we use it
Many questions need both the detail row and a group-level number side by side: each employee's salary next to the department average, each day's sales next to the running total, each row's rank. Without windows you need self-joins or subqueries that are slow and hard to read.
When to use it
Use a window function when you want to keep every row but add a calculation over related rows (ranks, running totals, previous/next values, group totals). Use plain GROUP BY when you only want one summary row per group.
How it works
- FROM, WHERE, GROUP BY, HAVING produce the rows
- PARTITION BY splits rows into independent groups
- ORDER BY sorts rows inside each partition
- Frame picks which rows count for the current row
- Function is computed for each row from its window
- Row is kept, with the result as a new column
Syntax
function() OVER (
PARTITION BY column
ORDER BY column
)Example
SELECT name, department, salary,
AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;Key points
- Every input row stays in the output; nothing is collapsed
- Built from OVER(), PARTITION BY, ORDER BY and an optional frame
- Evaluated after WHERE/GROUP BY/HAVING, so not allowed in WHERE
- Filter on a window result via a subquery or CTE
- Supported by all major databases, including SQLite, MySQL and Postgres