Window Function and Frame

A window function computes a value using related rows while retaining one result for each input row at that stage. PARTITION BY divides those rows into independent sets. ORDER BY inside OVER establishes their calculation order; an outer ORDER BY is still needed to guarantee display order.

For a running aggregate, the frame selects which rows contribute to the current result. ROWS BETWEEN 6 PRECEDING AND CURRENT ROW includes up to seven rows. Those rows represent seven consecutive days only if the input has exactly one row for every day. A date-based RANGE frame uses distance in the ordering value instead.

The default frame is the trap, because it is invisible in the query text. PostgreSQL documents it as RANGE UNBOUNDED PRECEDING, “which is the same as RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW,” setting the frame to “all rows from the partition start up through the current row’s last ORDER BY peer.” That is the frame for a running total accumulated per ordering value — every row sharing a date sees the same total — while a total accumulated row by row needs ROWS and an ordering without ties; it is the wrong frame for anything looking forward.

The documentation is explicit about the consequence: first_value, last_value and nth_value see only the frame, and the default “is likely to give unhelpful results for last_value.” So last_value(x) OVER (PARTITION BY k ORDER BY d) returns the value at the current row’s last peer, not the partition’s final value; obtaining the latter requires stating ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Even then the frame only fixes the candidates; the ordering decides which is last, so rows tied on d need a tie-breaker or the result is whichever the engine placed last. Write the frame explicitly whenever the answer depends on it.

If observations exist only on January 1 and January 10, the previous row on January 10 is January 1. LAG therefore does not automatically mean yesterday. Similarly, an average over the two latest rows and an average over the latest seven calendar dates can use different observations. Decide whether missing dates mean zero activity, unknown activity, or omitted observations before filling them.

ROW_NUMBER assigns sequential numbers within its ordering. To select one version per order, use the business precedence and enough tie-break columns to determine a unique winner. Timestamp ties alone leave the choice unspecified. A deterministic winner can still be the wrong source version; ordering and version validity are separate checks. State aggregate frames explicitly rather than relying on an engine default that includes peers.

Further reading

• DuckDB Documentation, Window Functions
• PostgreSQL Documentation, Window Functions
• PostgreSQL Documentation, Value Expressions

See the worked examples in SQL from Zero to Working.