SQL window functions: per-group answers without losing your rows

You have a table of orders and you want each order next to its customer's average. Or the three most recent orders per customer. Or a running total by date. With GROUP BY you end up joining the table to a summary of itself. Window functions do it in one pass and leave your rows intact.

The core idea

A window function computes something across a set of rows related to the current row, like an aggregate does, but it does not collapse them into one output row. The PostgreSQL tutorial on window functions puts it that way: each row keeps its identity.

What marks a window function is the OVER clause after the call. Without it you have an ordinary aggregate.

SELECT customer_id, id, total,
       avg(total) OVER (PARTITION BY customer_id) AS customer_avg
FROM orders;

Every order appears once, and each one carries the average for its customer. PARTITION BY splits the rows into groups; the function runs over the group that contains the current row. Leave it out and the whole result is one group.

Ordering inside the window

Add ORDER BY inside OVER and the function sees rows in that order. This is what makes ranking and running totals possible.

SELECT customer_id, id, created_at, total,
       row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS n
FROM orders;

Each customer's orders are numbered 1, 2, 3 from newest to oldest. Note the ordering inside OVER is separate from the final ORDER BY of the query, so you can number one way and display another.

If two rows tie on the ordering column, the numbering between them is not defined. Add a tiebreaker such as the id whenever the result must be stable:

ORDER BY created_at DESC, id

Top N per group

This is the question that sends most people to window functions. Window functions are not allowed in WHERE, so wrap the numbered query and filter outside:

SELECT *
FROM (
  SELECT customer_id, id, total,
         row_number() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id) AS n
  FROM orders
) AS recent
WHERE n <= 3;

The tutorial uses the same shape for the top earners in each department. The reason for the sub-select is the order of evaluation: window functions run after WHERE, GROUP BY and HAVING, so the filter cannot see their results.

Tip

If the table has an index on (customer_id, created_at DESC), Postgres can often avoid sorting everything. Run EXPLAIN on the query to check; the habits in reading EXPLAIN ANALYZE apply here too.

Running totals and the default frame

SELECT created_at, total,
       sum(total) OVER (ORDER BY created_at, id) AS running_total
FROM orders;

With ORDER BY in the window, the default frame runs from the start of the partition up to the current row, including any later rows that tie with it. That tie detail surprises people: if three rows share a timestamp and you order only by that column, all three show the same running total. Adding a unique tiebreaker, as above, gives each row its own step.

Without ORDER BY, the frame is the whole partition. That is why sum(total) OVER () gives the grand total on every row, handy for computing each row's share:

SELECT id, total,
       round(100.0 * total / sum(total) OVER (), 1) AS pct_of_all
FROM orders;

Reusing a definition

When several functions share one window, name it once:

SELECT id,
       sum(total) OVER w AS running_total,
       avg(total) OVER w AS running_avg
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at, id);

Common functions worth knowing

Function	What it gives you
row_number()	A unique position within the partition
rank(), dense_rank()	Position with ties sharing a rank
lag(x), lead(x)	The value from the previous or next row
sum(x), avg(x) and other aggregates	A value over the frame

lag is especially useful for change detection, such as time since the customer's previous order:

SELECT id, created_at,
       created_at - lag(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS gap
FROM orders;

The first order per customer gets NULL, because there is no previous row.

Mistakes to avoid

• Using one in WHERE. It is rejected. Wrap in a sub-select or a common table expression.
• Relying on tie order. Add a tiebreaker so results do not change between runs.
• Mixing up aggregates. A plain sum(total) with GROUP BY collapses rows; sum(total) OVER (...) does not. Both can appear in one query, and the aggregate runs first.
• Windowing huge tables without an index. Each distinct window may need a sort. Check the plan before putting it in a hot path.

A way to practise

Take a report you currently build with a self-join, rewrite it with a window function, and compare the results row for row. The query is usually shorter, and often faster, because the table is read once. Once OVER and PARTITION BY feel natural, a whole class of awkward reporting questions turns into a few lines.

Sources

• PostgreSQL documentation: Window Functions tutorial