Running totals and window frames

Run:

You should see:

The first row contains:

The second contains:

The third contains:

and so on.

The important new part is:

This defines the window frame.

A partition tells PostgreSQL which broad group of rows belongs to the window.

A frame determines which rows inside that partition are used for the calculation for the current row.

This distinction is important.

Consider:

When PostgreSQL is evaluating row 3, our frame is:

For row 5:

The frame grows as PostgreSQL moves through the ordered rows.

In:

UNBOUNDED PRECEDING means:

Start at the first row of the partition.

CURRENT ROW means:

End at the current row.

So for every row, PostgreSQL calculates:

That is why this frame produces a running total.

A frame doesn't have to start at the beginning of the partition.

For example, we can calculate an average using the current row and the previous two rows:

You should see:

For row 5, the frame contains:

so PostgreSQL calculates the average of:

Frame boundaries can move relative to the current row.

For example:

means:

While:

means:

This can be useful for moving averages and other calculations based on nearby rows.

The boundaries you will commonly encounter are:

Suppose we calculate a running total separately for every customer:

Here:

determines which customer's orders belong together.

Then:

determines their sequence inside that partition.

Finally:

determines which of those ordered rows participate in the calculation for each current row.

A useful mental model is:

Consider:

When a window contains ORDER BY but no explicit frame, PostgreSQL uses a default frame that runs from the beginning of the partition through the current row, including any rows that are peers of the current row according to the window ordering.

For an ordering column such as our unique id, this often looks exactly like a running total.

So this shorter query:

would produce the running total we expect here.

However, the default behavior becomes more important when multiple rows have the same ORDER BY value.

For row-by-row calculations, explicitly writing:

makes the intended frame clear.

ROWS counts actual rows.

For example:

means exactly:

PostgreSQL also supports other frame modes such as:

which treat ordering values and peer groups differently.

For most application-level running totals and moving calculations, understanding ROWS first gives you the most useful mental model.

PostgreSQL also provides functions such as:

These operate on the current window frame, not automatically on the entire partition.

This is especially important with LAST_VALUE().

Consider:

Because the default frame ends around the current row, LAST_VALUE() may return the value from the current row rather than the last value from the entire partition.

If we explicitly want the last value from the whole partition, we can define a frame that extends to the end:

Now the frame contains the entire ordered partition.

You can see both side by side:

You should see:

This is why understanding frames is important even when the window function itself looks simple.

Compare:

with:

The ordinary aggregate gives one final total.

The window version gives a total for every row based on that row's frame.

The important point is:

A window frame controls which rows inside a partition participate in the calculation for the current row.