The jOOQ User Manual SQL building Column expressions Window functions ROWS, RANGE, GROUPS (frame clause)

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 PRECEDING rows.
• A sliding average can be achieved by framing the window to include a certain number of PRECEDING rows as well as FOLLOWING 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: While 1990 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 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 RANGE and GROUPS clauses are a bit more tricky to understand, although they're much more powerful.

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 */

The jOOQ User Manual SQL building Column expressions Window functions ROWS, RANGE, GROUPS (frame clause)

previous : next