|previous : next|
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
- A sliding average can be achieved by framing the window to include a certain number of
PRECEDINGrows as well as
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 PRECEDINGwill 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 PRECEDINGwill 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 PRECEDINGwill 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.
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.
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();
+----+--------------+-------+-------+-------+--------+ | 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.
1990has 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 the
1980s 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
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 the
RANGE UNBOUNDED PRECEDINGframe is implicit for aggregate window functions. Without
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGis implicit, i.e. the entire window partition.
This example using jOOQ:
Translates to the following dialect specific expressions:
-- AURORA_POSTGRES, BIGQUERY, COCKROACHDB, DB2, DUCKDB, EXASOL, FIREBIRD, H2, INFORMIX, MARIADB, MEMSQL, MYSQL, ORACLE, -- POSTGRES, REDSHIFT, SNOWFLAKE, SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, TRINO, VERTICA, YUGABYTEDB count(*) OVER ( ORDER BY BOOK.ID ROWS 3 PRECEDING ) -- ACCESS, ASE, AURORA_MYSQL, DERBY, HANA, HSQLDB /* UNSUPPORTED */