Postgres – Window Functions

An aggregate collapses rows. A window function keeps every row and adds a column computed from its neighbours. That one difference covers running totals, rankings, "compare this row to the previous one", and top-N-per-group.

The difference, in one query

SELECT b.id, b.total,
       avg(b.total) OVER ()               AS avg_all,       -- 400,000 rows out
       b.total - avg(b.total) OVER ()     AS vs_average
FROM   bookings b
WHERE  b.status = 'CONFIRMED'
LIMIT  5;

OVER () is what makes it a window function. Without it, avg(b.total) would be an aggregate and the query would return one row. With it, the average is computed across the window and attached to every row.

PARTITION BY

PARTITION BY is GROUP BY for windows — it restarts the calculation per group, without collapsing anything:

SELECT p.city, p.title, p.price_per_night,
       round(avg(p.price_per_night) OVER (PARTITION BY p.city), 2) AS city_avg,
       count(*)      OVER (PARTITION BY p.city)                    AS in_city
FROM   properties p
WHERE  p.status = 'PUBLISHED' AND p.city IN ('Oslo', 'Lisbon')
ORDER  BY p.city, p.price_per_night DESC
LIMIT  6;

Every row keeps its own price and gains its city's average beside it. Doing that with GROUP BY takes a subquery and a join.

Ranking, and picking the right one

SELECT city, title, price_per_night,
       row_number() OVER w AS row_number,
       rank()       OVER w AS rank,
       dense_rank() OVER w AS dense_rank
FROM   properties
WHERE  status = 'PUBLISHED' AND city = 'Oslo'
WINDOW w AS (ORDER BY price_per_night DESC)
LIMIT  8;

The WINDOW w AS (…) clause is worth the habit: define the window once, use it in every column, and change it in one place.

row_number() is not deterministic when the ORDER BY has ties — two rows with the same price can swap between runs. If the numbering is stored or paged on, add a unique tiebreaker: ORDER BY price_per_night DESC, id.

Top-N per group

The query that makes the whole feature worth learning. The three most expensive properties in each city:

SELECT city, title, price_per_night
FROM (
    SELECT city, title, price_per_night,
           row_number() OVER (PARTITION BY city ORDER BY price_per_night DESC, id) AS rn
    FROM   properties
    WHERE  status = 'PUBLISHED'
) ranked
WHERE  rn <= 3
ORDER  BY city, price_per_night DESC
LIMIT  9;

The subquery is not optional. A window function is computed after WHERE, so you cannot filter on rn in the same query level — that ordering is why WHERE rn <= 3 fails with "column rn does not exist".

Compare it with the LATERAL version from the joins post: this one ranks every property and throws most away, while LATERAL seeks the top three per city and stops. When the groups are few and large, LATERAL with a matching index wins; when you need the rank itself, or the groups are many and small, this one does.

Running totals and the frame

SELECT b.check_in, b.total,
       sum(b.total) OVER (ORDER BY b.check_in, b.id
                          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM   bookings b
WHERE  b.property_id = 42
ORDER  BY b.check_in, b.id
LIMIT  6;

The ROWS BETWEEN clause is the frame: which rows of the partition this row's calculation sees. It is also the source of the most common surprise in this post.

Adding ORDER BY to a window changes the default frame. With no ORDER BY, the frame is the whole partition. With one, the default becomes RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — so avg(x) OVER (ORDER BY d) is a running average, not the overall one. That is rarely what someone means the first time they write it.

And RANGE is not ROWS. RANGE includes every row that ties on the ordering value, so with duplicate dates a "running total" jumps to the day's full total on the first row of the day. Write ROWS when you mean rows.

-- a 3-row moving average, centred
SELECT b.check_in, b.total,
       round(avg(b.total) OVER (ORDER BY b.check_in, b.id
                                ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING), 2) AS moving_avg
FROM   bookings b
WHERE  b.property_id = 42
ORDER  BY b.check_in, b.id
LIMIT  6;

Comparing a row to its neighbours

SELECT check_in, total,
       lag(total)  OVER w                    AS previous,
       total - lag(total) OVER w             AS change,
       first_value(total) OVER w             AS first_of_series
FROM   bookings
WHERE  property_id = 42
WINDOW w AS (ORDER BY check_in, id)
ORDER  BY check_in, id
LIMIT  6;

lag() reaches backwards, lead() forwards; both take an offset and a default — lag(total, 1, 0) gives 0 rather than NULL on the first row, which keeps the arithmetic from turning into NULL.

Note the third column. first_value() respects the frame, and the default frame here ends at the current row — so last_value() in the same window returns the current row, not the last of the partition. That is the second frame surprise, and the fix is to say what you meant: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Deduplication

The most useful thing in this post that is not a report. Given rows that should be unique and are not, keep one of each and delete the rest — with the ORDER BY deciding which one survives:

WITH duplicates AS (
    SELECT id,
           row_number() OVER (PARTITION BY property_id, check_in
                              ORDER BY created_at DESC, id DESC) AS rn
    FROM   bookings
)
SELECT count(*) AS would_delete FROM duplicates WHERE rn > 1;

Run it as a SELECT first, every time. Turning it into DELETE FROM bookings WHERE id IN (SELECT id FROM duplicates WHERE rn > 1) is one edit, and the PARTITION BY list is the definition of "duplicate" — get it wrong and you delete rows that were not.

Windows over aggregates

Because window functions run after grouping, they can be applied to aggregate results. This ranks cities by revenue and gives each its share of the total, in one pass:

SELECT p.city,
       count(*)                                              AS stays,
       round(sum(b.total), 2)                                AS revenue,
       rank() OVER (ORDER BY sum(b.total) DESC)              AS by_revenue,
       round(100 * sum(b.total) / sum(sum(b.total)) OVER (), 1) AS pct_of_total
FROM   bookings b
JOIN   properties p ON p.id = b.property_id
WHERE  b.status = 'CONFIRMED'
GROUP  BY p.city
ORDER  BY revenue DESC
LIMIT  5;

sum(sum(b.total)) OVER () is not a typo. The inner sum is the aggregate producing each city's revenue; the outer one is a window function totalling those results. Nested like that it is legal exactly once — you cannot nest a window function inside another.

Where they run

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. So they can see aggregate results — rank() OVER (ORDER BY count(*) DESC) in a grouped query is legal and useful — and they cannot be filtered on without another query level.

They also run after LIMIT has not yet been applied, which is worth saying out loud: a window function over a query with LIMIT 10 sees every row the WHERE clause allowed, not ten. That is what makes avg(total) OVER () beside a ten-row page the average of everything rather than of the page — usually what you wanted, and occasionally an expensive surprise on a large table, because the whole set has to be computed to produce ten rows.