Extended Window Frames

Extending Window Frames Beyond ROWS and RANGE

LAMP Stack Development used ROWS frames (a row count) and RANGE frames (a distance in the ordering value). PostgreSQL 1,289 adds the rest of SQL:2011, which MySQL 8.4 524 parses but rejects: GROUPS frames count peer groups (rows with equal ordering values) and EXCLUDE drops the current row (CURRENT ROW), its peers (GROUP) or only the peers (TIES). Daily sales per channel, three rows per date, show the difference:

ROWS, GROUPS and EXCLUDE over daily channel salesSQL

WITH daily AS (
  SELECT order_date, channel, sum(gross_amount) AS gross
  FROM mart.sales WHERE status <> 'cancelled' AND order_date >= '2026-06-24'
  GROUP BY 1, 2
)
SELECT order_date, channel, gross,
       sum(gross) OVER w_rows AS rows_3,
       sum(gross) OVER w_days AS days_3,
       round(avg(gross) OVER (PARTITION BY order_date ROWS BETWEEN UNBOUNDED PRECEDING
             AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW), 2) AS others_avg
FROM daily
WINDOW w_rows AS (ORDER BY order_date, channel ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),
       w_days AS (ORDER BY order_date GROUPS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY order_date DESC, channel
LIMIT 4;

Output

 order_date | channel |  gross  | rows_3  |  days_3  | others_avg
------------+---------+---------+---------+----------+------------
 2026-06-30 | android | 2347.25 | 6325.31 | 19627.01 |    2161.40
 2026-06-30 | ios     | 3480.01 | 7343.16 | 19627.01 |    1595.02
 2026-06-30 | web     |  842.79 | 6670.05 | 19627.01 |    2913.63
 2026-06-29 | android | 2349.04 | 6522.29 | 19080.12 |    1989.03

rows_3 sums three rows, straddling two days, a classic bug when rows are not one per period. days_3 sums three dates, every channel of 28 to 30 June; GROUPS counts dates present in the data, so gap-fill first (Gap-Filling Time Series) when a missing day should count. others_avg compares each channel with the other two that day, a benchmark that would otherwise need a self-join.

MENU