Menu

PostgreSQL Window Functions: OVER, PARTITION BY, ROW_NUMBER

Window functions compute a value for each row from a group of related rows without collapsing them: rankings, running totals, moving averages and comparisons with the previous row.

This page includes runnable editors. Edit, run, and see output instantly.

A window function computes a value for each row from a set of related rows, its window, without merging the rows the way GROUP BY does. Write it as function() OVER (PARTITION BY ... ORDER BY ...). PARTITION BY splits the rows into groups and ORDER BY sets the order inside each group.

Every row stays in the result and carries its region's total: 1000 for north, 500 for south. Each rep's share is then one expression (ana has 50 percent of north). Any aggregate (sum, count, avg, min, max) becomes a window function when you add OVER. An empty OVER () makes the whole result one window.

Number rows with ROW_NUMBER, RANK and DENSE_RANK

The three ranking functions differ only when rows tie on the ORDER BY value:

ben and cy both have 85. row_number still gives them 2 and 3; which one gets 2 is undefined unless the ORDER BY breaks the tie. rank gives both 2 and skips to 4 for dee. dense_rank gives both 2 and continues with 3.

FunctionTiesGaps after ties
row_number()different numbersno
rank()same numberyes
dense_rank()same numberno

Get the top N rows per group

Window functions are evaluated after WHERE, so you cannot filter on them directly. Number the rows in a subquery or CTE, then filter outside:

This returns the two best reps of each region. Use rank() instead of row_number() to keep every row that ties for second place. For only the first row per group, DISTINCT ON is shorter.

Calculate a running total or moving average

With ORDER BY inside OVER, an aggregate runs over a frame. By default the frame goes from the first row of the partition to the current row, which gives a running total. A ROWS BETWEEN clause sets a different frame, such as the current row and the two before it:

The running total reaches 130 on day 5, and the three-day average on day 5 is (30 + 20 + 50) / 3 = 33.3.

The default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. It includes every row that ties with the current one on the ORDER BY value, so two rows with the same day get the same running total. Write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to add them one at a time.

Compare with the previous row: LAG and LEAD

lag(col) reads a value from the row before the current one in the window order, and lead(col) from the row after. Both return NULL past the edge unless you pass a default as the third argument.

Day 1 has no previous row, so previous and change are NULL. The WINDOW clause defines a window once, by name, for several functions to share.

first_value works with the default frame, but last_value does not: the default frame ends at the current row, so it returns the current row's value. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make the frame the whole partition. Other window-only functions are ntile(n), percent_rank(), cume_dist() and nth_value(); the PostgreSQL window function reference lists them all.

Filter which rows an aggregate counts

FILTER (WHERE ...) works on window aggregates too, for conditional totals on every row:

SELECT day,
       count(*) FILTER (WHERE status = 'error') OVER (ORDER BY day) AS errors_so_far
FROM requests;

Frequently Asked Questions

What is the difference between GROUP BY and PARTITION BY?

GROUP BY returns one row per group. PARTITION BY inside OVER keeps every row and gives each one a value computed over its group, so you can show a row's amount next to its group's total.

Can I use a window function in WHERE?

No. Window functions run after WHERE, GROUP BY and HAVING, so they are allowed only in the select list and ORDER BY. Compute the value in a subquery or CTE and filter in the outer query.

Why does LAST_VALUE return the current row's value?

With ORDER BY in OVER, the default frame ends at the current row (and its ties), so the last row of the frame is the current one. Add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to make the frame the whole partition.

What is the difference between ROWS and RANGE in a frame?

ROWS counts physical rows: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is the current row and the two before it. RANGE works on the ORDER BY value, so rows with equal values (peers) are always in the frame together.

Coddy programming languages illustration

Learn to code with Coddy

Get started