Skip to content
Tutorials Center
My learning

Topics

Lesson 28 of 90 ยท Querying with PostgreSQL Features

Window Functions in Depth

Master PostgreSQL window functions: ROWS, RANGE and GROUPS frames, EXCLUDE, named WINDOW clauses, FILTER, ntile, first_value, nth_value and percentile_cont.

You already know that a window function computes a value for each row using a set of related rows without collapsing them, as GROUP BY does. You have met ranking functions, running totals, LAG and LEAD and the basics of window frames. This lesson goes deeper into the part people find hardest: exactly which rows each calculation sees, plus the PostgreSQL-specific tools around it.

The topics are the three frame modes (ROWS, RANGE, GROUPS), excluding rows from a frame, naming a window once and reusing it, filtering what an aggregate counts, picking values out of a frame with first_value and nth_value, splitting rows into buckets with ntile, and the neighbouring family of percentile aggregates. Nearly everything runs live here, because SQLite has supported window functions, frame modes, EXCLUDE, named windows and FILTER for years. The one exception is percentile_cont, which is PostgreSQL's ordered-set aggregate and is shown read-only with a runnable stand-in.

Anatomy of a window

PARTITION BY Split rows into independent groups.
ORDER BY Put each group in a sequence.
Frame The slice around the current row.
1 / 2

The data

All of the live examples use a small table of seven daily sales. Two days share the amount 120 and two share 150. These ties matter, because they are exactly where frame modes behave differently.

day amount
2024-03-01 100
2024-03-02 120
2024-03-03 120
2024-03-04 90
2024-03-05 200
2024-03-06 150
2024-03-07 150

What the default frame really is

When a window has ORDER BY but no frame clause, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: everything from the start of the partition up to the current row and all of its ties. Without ORDER BY, the frame is the whole partition. This explains two famous surprises: a running SUM that jumps by two rows at once when values tie, and last_value that seems to return the current row's value, as you will see below.

ROWS: count physical rows

ROWS counts rows by position, no matter what their values are. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW is a three-row sliding window, the usual shape for moving averages:

SQL A three-day sliding sum with ROWS

Press Run (or Ctrl + Enter) to see the result here.

last_3_days reads 100, 220, 340, 330, 410, 440, 500. The first two rows have fewer than three rows available, so they add what exists (100 and then 100 + 120). On 03-04 the window holds 120 + 120 + 90 = 330. The running_total column uses the default frame and ends at 930, the sum of everything. Because day is unique, the default frame and ROWS agree for the running total.

RANGE and GROUPS: ties change everything

Now sort by amount, which has ties, and compare the three modes on the same running sum:

SQL ROWS vs RANGE vs GROUPS with tied values

Press Run (or Ctrl + Enter) to see the result here.

Look at the two rows with amount 120 (days 03-02 and 03-03):

  • rows_total is 310 and then 430. It counts the rows one by one, so the answer for tied rows depends on their arbitrary order.
  • range_total is 430 for both. RANGE treats rows with equal sort values as peers, and the frame includes all peers of the current row. This is the default behaviour and the reason a plain SUM(...) OVER (ORDER BY ...) gives the same total to tied rows.
  • groups_total is 340 for both: GROUPS counts peer groups instead of rows. 1 PRECEDING AND CURRENT ROW means "the previous group of equal values plus the current group", so for 120 it is 100 + 120 + 120 = 340. For the two 150 rows it is 240 + 300 = 540.

When you need a deterministic running total with ties, either add a tie-breaker to ORDER BY (ORDER BY amount, day) or ask for ROWS explicitly.

RANGE can also use value offsets when the sort column is a number, date or timestamp, which is perfect for time windows with gaps. This PostgreSQL example takes the average over "the last 7 calendar days", however many rows that is (it is read-only because it needs real date arithmetic):

SQL
SELECT day,
       avg(amount) OVER (
         ORDER BY day
         RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
       ) AS avg_7_days
FROM sales;

A ROWS 6 PRECEDING frame would silently cover more than a week if some days had no sale. RANGE with an interval measures the calendar instead.

EXCLUDE: leave rows out of the frame

An EXCLUDE clause at the end of a frame removes some rows from it. EXCLUDE CURRENT ROW is useful for "compared with my neighbours, not including me":

SQL Neighbours only with EXCLUDE CURRENT ROW

Press Run (or Ctrl + Enter) to see the result here.

For 03-04 the full window is 120 + 90 + 200 = 410, and neighbours_only is 320 (120 + 200). The first row has no previous day, so its neighbours total is just 120. The options are EXCLUDE CURRENT ROW, EXCLUDE GROUP (the row and its peers), EXCLUDE TIES (only the peers, keep the row) and the default EXCLUDE NO OTHERS. All of them work in PostgreSQL 11 and later.

Named windows

A query often uses the same window several times. Repeating a long OVER (...) is noisy and error-prone, so define it once in a WINDOW clause after HAVING, and refer to it by name. You can also start from a named window and add to it:

SQL
SELECT day, amount,
       avg(amount) OVER w AS moving_avg,
       max(amount) OVER w AS moving_max,
       rank() OVER (w ORDER BY amount DESC) AS rnk   -- reuse partition, change order
FROM sales
WINDOW w AS (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

PostgreSQL also notices when several functions share the same window definition and sorts the data only once, so naming windows is clearer and helps performance.

FILTER, first_value, nth_value and last_value

Aggregates used as window functions accept FILTER (WHERE ...), which makes them ignore rows that fail the condition, without a CASE. The "pick a value" functions first_value, nth_value and last_value read a single row of the frame:

SQL FILTER and frame value functions with a named window

Press Run (or Ctrl + Enter) to see the result here.

Read the columns one at a time:

  • avg_3 is 100, 110, 113.3, 110, 136.7, 146.7, 166.7.
  • strong_days counts how many days in the three-day window had at least 150: it is 0 until 03-05 (200), then 1, 2 and 3 on the last three rows.
  • first_in_window and second_in_window pick the first and second row of each frame. On the first row there is no second row, so second_in_window is NULL.
  • last_default_frame simply repeats amount. With the default frame the "last row" of the frame is the current row (or its last peer), which is almost never what you intended.
  • last_full_frame is 150 on every row, because the explicit frame reaches to the end of the partition.

This is the classic last_value trap: if you want the last row of the whole partition, spell out ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

ntile: equal-sized buckets

ntile(n) splits the ordered rows into n buckets numbered 1 to n, as evenly as possible. When rows do not divide evenly, the first buckets get one extra row:

SQL Three buckets with ntile

Press Run (or Ctrl + Enter) to see the result here.

Seven rows into three buckets give sizes 3, 2 and 2. Bucket 1 holds the three best days (200, 150, 150), bucket 2 the two 120s, and bucket 3 the 100 and the 90. Remember that ntile splits rows, not values: two equal amounts can land in different buckets if the boundary falls between them, which is why the ORDER BY above includes day as a tie-breaker. For "top quartile" style questions with ties, compare with percent_rank() or cume_dist() instead.

Percentiles: ordered-set aggregates

Medians and percentiles are not window functions. In PostgreSQL they are ordered-set aggregates, written with WITHIN GROUP (ORDER BY ...). They collapse rows like GROUP BY does, and they cannot take OVER:

SQL
SELECT percentile_cont(0.5)  WITHIN GROUP (ORDER BY amount) AS median,    -- 120
       percentile_cont(0.25) WITHIN GROUP (ORDER BY amount) AS p25,       -- 110
       percentile_disc(0.9)  WITHIN GROUP (ORDER BY amount) AS p90_disc,  -- 200
       mode()                WITHIN GROUP (ORDER BY amount) AS most_common
FROM sales;

percentile_cont interpolates between neighbouring values, so it can return a number that is not in the data. For the seven sales, the sorted values are 90, 100, 120, 120, 150, 150, 200, and the 25th percentile sits one and a half steps along, halfway between 100 and 120: 110. percentile_disc always returns an actual value from the column. mode() returns the most frequent value (with ties it returns the first in sort order). Per group, just add GROUP BY. SQLite has no percentile_cont, so here is the median computed with ROW_NUMBER and COUNT, which gives the same answer:

SQL Median with ROW_NUMBER (stand-in for percentile_cont(0.5))

Press Run (or Ctrl + Enter) to see the result here.

The result is 120, the middle of the seven sorted values. For an even count, the two rn values differ and AVG takes the mean of the two middle rows, which is exactly the interpolation percentile_cont(0.5) performs. More statistics recipes live in statistics and percentiles.

Rules and traps

  • Window functions run late. They are evaluated after WHERE, GROUP BY and HAVING, so they see the already filtered and grouped rows, and they cannot appear in WHERE. To filter on a window result (for example rn = 1), wrap the query in a subquery or CTE. PostgreSQL has no QUALIFY clause.
  • Combine with GROUP BY carefully. A window over grouped rows sees one row per group; put aggregates inside the window function, such as SUM(SUM(amount)) OVER (...).
  • Make ORDER BY deterministic. If ties exist and the result matters, add a unique tie-breaker column.
  • Performance. Each distinct window needs sorted input. An index that matches PARTITION BY columns followed by the ORDER BY columns lets PostgreSQL skip the sort. Check it with EXPLAIN.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A window has ORDER BY but no frame clause. Which frame does PostgreSQL use?

    The default is RANGE from the start of the partition to the current row and its peers. Without ORDER BY the frame is the whole partition.
  2. 2. Two rows tie on the ORDER BY value. How do they differ in a running SUM with ROWS versus RANGE?

    ROWS counts rows one at a time, so tied rows get different totals. RANGE includes all peers in the frame, so tied rows share one total.
  3. 3. Why does last_value(amount) OVER (ORDER BY day) simply return the current row's value?

    With the default frame, nothing after the current row (and its peers) is visible. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to reach the partition's last row.
  4. 4. What does percentile_cont(0.5) WITHIN GROUP (ORDER BY amount) compute, and can it be used with OVER?

    It is an ordered-set aggregate that collapses rows and interpolates, so it takes WITHIN GROUP rather than OVER.
  5. 5. What is the benefit of a named WINDOW clause?

    Naming avoids repeated, error-prone OVER (...) text, and identical windows are computed from one sorted pass.

Summary

  • Without a frame clause, ORDER BY implies RANGE UNBOUNDED PRECEDING TO CURRENT ROW, tied rows included; without ORDER BY the frame is the whole partition.
  • ROWS counts physical rows, RANGE uses sort values (and can use offsets such as INTERVAL '6 days'), and GROUPS counts groups of tied rows.
  • EXCLUDE CURRENT ROW | GROUP | TIES removes rows from a frame.
  • A WINDOW clause names a window for reuse, and a named window can be extended in OVER (w ORDER BY ...).
  • FILTER (WHERE ...) limits what an aggregate window function counts; first_value, nth_value and last_value read one row of the frame, and last_value needs an explicit full frame.
  • ntile(n) makes near-equal buckets of rows, not of values.
  • percentile_cont, percentile_disc and mode are ordered-set aggregates used with WITHIN GROUP, not window functions.
  • To filter on a window result, wrap the query in a subquery or CTE.

Next: CTEs, Materialization and Recursive Queries.