Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11 | 3.10
ROWS, RANGE, GROUPS (frame clause)
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
When a window specification contains a ORDER BY clause, then a window frame may be explicit or implicit, with aggregate window functions. In short, a window frame limits the size of the window in both directions, from the current row.
For example:
- A cumulative sum can be achieved by framing the window to include all
PRECEDING
rows. - A sliding average can be achieved by framing the window to include a certain number of
PRECEDING
rows as well asFOLLOWING
rows.
A frame can be limited in 3 modes:
-
ROWS
: This limits the frame by an exact number of rows, similar to the LIMIT clause. For example,ROWS 3 PRECEDING
will include the 3 preceding rows and the current row in the window. -
RANGE
: This limits the frame logically by a value range, e.g.RANGE 3 PRECEDING
will include the a value range of[current value - 3, current value]
and the current row in the window. This only works for types with well defined ranges, including numeric and temporal types. -
GROUPS
: This limits the frame logically by a distinct value count range, e.g.GROUPS 3 PRECEDING
will include the rows containing the 3 preceding distinct values and the current row in the window.
Notice how in the above examples, the current row is always included by default. It can be excluded using the window exclusion clause.
Complete syntax
The complete syntax is best illustrated using a grammar:
In the above short form where only a single frameBound
is provided (e.g. ROWS 3 PRECEDING
), then ROWS BETWEEN frameBound AND CURRENT ROW
is implied.
Example
This is again best explained by example:
SELECT ID, PUBLISHED_IN, -- The 2 preceding rows and the current row COUNT(*) OVER (ORDER BY PUBLISHED_IN ROWS 2 PRECEDING), -- The 42 preceding years and the current row COUNT(*) OVER (ORDER BY PUBLISHED_IN RANGE 42 PRECEDING), -- The 1 preceding groups of years and the current row trunc(PUBLISHED_IN, -1), COUNT(*) OVER (ORDER BY trunc(PUBLISHED_IN, -1) GROUPS 1 PRECEDING) FROM BOOK ORDER BY published_in
create.select( BOOK.ID, BOOK.PUBLISHED_IN, // The 2 preceding rows and the current row count().over(orderBy(BOOK.PUBLISHED_IN).rowsPreceding(2)), // The 42 preceding years and the current row count().over(orderBy(BOOK.PUBLISHED_IN).rangePreceding(42)), // The 1 preceding groups of years and the current row trunc(BOOK.PUBLISHED_IN, -1), count().over(orderBy(trunc(BOOK.PUBLISHED_IN, -1)) .groupsPreceding(1))) .from(BOOK) .orderBy(BOOK.PUBLISHED_IN) .fetch();
Producing:
+----+--------------+-------+-------+-------+--------+ | id | published_in | rows | range | decade | groups | +----+--------------+------+-------+--------+--------+ | 2 | 1945 | 1 | 1 | 1940 | 2 | | 1 | 1948 | 2 | 2 | 1940 | 2 | | 3 | 1988 | 3 | 2 | 1980 | 3 | | 4 | 1990 | 3 | 3 | 1990 | 2 | +----+--------------+------+-------+--------+--------+
As you can see:
-
ROWS
: All rows have between 0 and 2 preceding rows, plus the current row. -
RANGE
: While1990
has 2 preceding rows within 42 years (plus the rows with the same value as the current row), the other years have less rows in that time span. -
GROUPS
: The decade of the1980
s have 2 rows belonging to the previous 1 group of decades (plus the rows with the same value as the current row).
This certainly requires a bit of practice. While the ROWS
clause is straightforward, the RANGE
and GROUPS
clauses are a bit more tricky to understand, although they're much more powerful.
If you omit the frame clause, but have an ORDER BY clause, then theRANGE UNBOUNDED PRECEDING
frame is implicit for aggregate window functions. WithoutORDER BY
,RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
is implicit, i.e. the entire window partition.
Dialect support
This example using jOOQ:
count().over(orderBy(BOOK.ID).rowsPreceding(3))
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, ClickHouse, CockroachDB, DB2, DuckDB, Exasol, Firebird, H2, Informix, MariaDB, MemSQL, MySQL, Oracle, Postgres, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, Trino, Vertica, YugabyteDB
count(*) OVER ( ORDER BY BOOK.ID ROWS 3 PRECEDING )
ASE, Access, Aurora MySQL, Derby, HSQLDB, Hana
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!