Window functions
Understanding window functions
In the previous chapters, you used aggregate functions such as COUNT(), SUM(), and AVG() together with GROUP BY.
For example:
12345SELECT status, COUNT(*) AS order_countFROM ordersGROUP BY status;GROUP BY combines many rows into one result row for each group.
But sometimes we want to calculate something across related rows without losing the individual rows.
This is where window functions are useful.
GROUP BY collapses rows
Start with the first five orders:
123456SELECT id, total_amountFROM ordersWHERE id <= 5ORDER BY id;You should see:
1234567 id | total_amount----+-------------- 1 | 10.01 2 | 10.02 3 | 10.03 4 | 10.04 5 | 10.05If we calculate their average with an ordinary aggregate:
1234SELECT ROUND(AVG(total_amount), 2) AS average_amountFROM ordersWHERE id <= 5;the five input rows become one result row:
123 average_amount---------------- 10.03The individual orders are no longer present in the result.
Keeping the individual rows
Now run:
1234567SELECT id, total_amount, ROUND(AVG(total_amount) OVER (), 2) AS average_amountFROM ordersWHERE id <= 5ORDER BY id;You should see the five orders again, but each row also contains the average calculated across those rows:
1234567 id | total_amount | average_amount----+--------------+---------------- 1 | 10.01 | 10.03 2 | 10.02 | 10.03 3 | 10.03 | 10.03 4 | 10.04 | 10.03 5 | 10.05 | 10.03The important difference is:
123456789ordinary aggregate5 rows │ ▼ AVG(...) │ ▼1 result rowwhile:
123456789window function5 rows │ ▼AVG(...) OVER (...) │ ▼5 result rowsThe window function performs a calculation across a set of rows while keeping each individual row in the result.
The OVER clause
The part that turns the aggregate into a window function is:
1OVER ()Compare:
1AVG(total_amount)with:
1AVG(total_amount) OVER ()Without OVER, AVG() is an ordinary aggregate.
With OVER, PostgreSQL calculates the aggregate as a window function.
The rows over which the window function operates are called its window.
When OVER is empty:
1OVER ()the window contains all rows available to the window function.
In our example, the WHERE clause first limits the query to orders 1 through 5, so the window contains those five rows.
12345678id total_amount1 10.01 ─────┐2 10.02 │3 10.03 ├──▶ AVG = 10.034 10.04 │5 10.05 ─────┘Each row remains in the result.Dividing rows into partitions
Sometimes we don't want one calculation across every row.
Instead, we want separate calculations for different groups.
For this, we use:
1PARTITION BYConsider the first ten products:
1234567SELECT id, category, priceFROM productsWHERE id <= 10ORDER BY id;You should see:
123456789101112 id | category | price----+----------+------- 1 | home | 5.01 2 | outdoor | 5.02 3 | office | 5.03 4 | garden | 5.04 5 | audio | 5.05 6 | home | 5.06 7 | outdoor | 5.07 8 | office | 5.08 9 | garden | 5.09 10 | audio | 5.10The products belong to different categories.
We can calculate the average price separately for each category while still keeping every product row:
12345678910111213SELECT id, category, price, ROUND( AVG(price) OVER ( PARTITION BY category ), 2 ) AS category_averageFROM productsWHERE id <= 10ORDER BY id;You should see:
123456789101112 id | category | price | category_average----+----------+-------+------------------ 1 | home | 5.01 | 5.04 2 | outdoor | 5.02 | 5.05 3 | office | 5.03 | 5.06 4 | garden | 5.04 | 5.07 5 | audio | 5.05 | 5.08 6 | home | 5.06 | 5.04 7 | outdoor | 5.07 | 5.05 8 | office | 5.08 | 5.06 9 | garden | 5.09 | 5.07 10 | audio | 5.10 | 5.08The important part is:
123AVG(price) OVER ( PARTITION BY category)PARTITION BY category divides the rows into separate windows based on category.
Conceptually:
123456789101112productshome ───┐home ───┘──▶ average for homeoutdoor ───┐outdoor ───┘──▶ average for outdooroffice ───┐office ───┘──▶ average for office...Each product remains a separate result row, but the calculation is performed only against rows in the same category.
PARTITION BY is not GROUP BY
PARTITION BY can look similar to GROUP BY, but they do different things.
With:
1GROUP BY categorywe get one result row per category.
With:
123OVER ( PARTITION BY category)the product rows remain separate.
For example:
1234GROUP BYhome products ───────▶ one home rowoffice products ─────▶ one office rowwhile:
1234567PARTITION BYProduct 1 home category averageProduct 6 home category averageProduct 3 office category averageProduct 8 office category averageA useful mental model is:
`GROUP BY` changes the number of rows. `PARTITION BY` divides rows for a window calculation without collapsing them.
What if PARTITION BY is omitted?
If we write:
1AVG(total_amount) OVER ()there is no PARTITION BY.
PostgreSQL therefore treats all available rows as one partition.
If we write:
123AVG(total_amount) OVER ( PARTITION BY customer_id)PostgreSQL calculates a separate average for each customer.
The choice depends on what set of rows the calculation should consider.
ORDER BY inside OVER
A window can also contain an ORDER BY:
123OVER ( ORDER BY id)This tells PostgreSQL the order in which rows should be considered by the window function.
This becomes important for calculations where row order matters, such as:
1234running totalsrankingprevious rownext rowFor example:
123SUM(total_amount) OVER ( ORDER BY id)can calculate a running total as PostgreSQL moves through the orders.
We will explore that in a later chapter.
Window ORDER BY and query ORDER BY are different
Consider:
123456789SELECT id, total_amount, SUM(total_amount) OVER ( ORDER BY id ) AS running_totalFROM ordersWHERE id <= 5ORDER BY total_amount DESC;You should see:
1234567 id | total_amount | running_total----+--------------+--------------- 5 | 10.05 | 50.15 4 | 10.04 | 40.10 3 | 10.03 | 30.06 2 | 10.02 | 20.03 1 | 10.01 | 10.01There are two ORDER BY clauses here.
This one:
123OVER ( ORDER BY id)controls the order used by the window calculation.
This one:
1ORDER BY total_amount DESCcontrols how the final query result is displayed.
They serve different purposes.
The window ORDER BY does not guarantee the final output order.
If you care about the order of the result rows, use the query's own ORDER BY.
Window functions are calculated after grouping
Window functions operate on the rows produced after WHERE, grouping, and aggregate calculations have been processed logically.
This means you can even apply a window function to grouped results.
It also explains why a window function cannot normally be used directly in WHERE.
For example, this is not valid:
123456789SELECT id, ROW_NUMBER() OVER ( ORDER BY total_amount DESC ) AS row_numberFROM ordersWHERE ROW_NUMBER() OVER ( ORDER BY total_amount DESC) <= 5;PostgreSQL answers with:
1ERROR: window functions are not allowed in WHEREThe WHERE clause is evaluated before the window-function result exists.
When we need to filter based on a window-function result, we normally calculate it first in another query level and then filter the result.
We will see a practical example of this when working with ranking functions.
The core mental model
A window function answers questions such as:
12345678What is this customer's average order amount,while still showing every individual order?What is this row's rank within its group?What is the running total up to this row?What value appeared in the previous row?The general structure is:
1234function(...) OVER ( PARTITION BY ... ORDER BY ...)Not every window needs both PARTITION BY and ORDER BY.
Which parts you use depends on the calculation.
The important point is:
Window functions calculate across related rows without collapsing those rows into a single result.