Compute without collapsing
A window function performs a calculation across a set of rows that are related to the current row, but unlike GROUP BY it does not collapse those rows. Each input row stays in the output, and the function adds a computed value alongside it.
The engine evaluates window functions in a dedicated phase, after WHERE, GROUP BY, and HAVING, but before ORDER BY and the final SELECT projection.
The OVER clause
Every window function uses an OVER clause with up to three parts:
- PARTITION BY splits rows into independent groups, like a reset boundary.
- ORDER BY orders rows inside each partition so position based functions make sense.
- A frame such as ROWS BETWEEN defines which rows around the current one are visible.
A simple example
To show each employee next to their department average without losing individual rows, you write AVG salary OVER PARTITION BY dept. Every row keeps its name and salary, and gains the department average.
Key idea
Window functions add a computed value to each row by looking across a defined window of related rows, keeping every row instead of collapsing them like GROUP BY does.