The jOOQ User Manual SQL building Column expressions Window functions

previous : next

Window functions

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Most major RDBMS support the concept of window functions.

A window function calculates an aggregate or ranking value over a subset of data (the window) relative to the projected row. This has numerous powerful applications, for example, cumulative sums or sliding averages:

SELECT
  id,

  -- A sliding average over 3 rows, including the current row
  avg(amount) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING),
  amount,
  
  -- A cumulative sum over all previous rows
  sum(amount) OVER (ORDER BY id)
FROM (VALUES 
  (1, 10.0), 
  (2, 15.0),
  (3, 20.0),
  (4, 25.0), 
  (5, 30.0), 
  (6, 35.0)
) AS t (id, amount)

The result being

+----+-----------------+--------+-----------------+
| id | sliding average | amount |  cumulative sum |
+----+-----------------+--------+-----------------+
|  1 |  12.5           |   10.0 | --\        10.0 |
|  2 |  15.0           |   15.0 |   |        25.0 |
|  3 |  20.0           |   20.0 |   |        45.0 |
|  4 |  25.0       /-- |   25.0 |   |        70.0 |
|  5 |  30.0 = AVG +   |   30.0 | --+ SUM = 100.0 |
|  6 |  32.5       \-- |   35.0 |           135.0 |
+----+-----------------+--------+-----------------+

As this illustration shows, the aggregation happens over a window that is defined relative to the row on which it is calculated:

• In the AVG case, the window moves along with the row, always looking 1 row behind and 1 row ahead (if applicable), spanning anything between 1-3 rows, and calculating the average over those, forming a sliding average.
• In the SUM case, the window always starts at the beginning of the data set, and sums up all the rows up to the current row, forming a cumulative sum.

The details of how this powerful feature works will be illustrated over the next pages, where the various clauses, including the PARTITION BY clause, the ORDER BY clause, and the frame clause are explained.

Table of contents

4.8.19.1.

PARTITION BY

4.8.19.2.

ORDER BY

4.8.19.3.

ROWS, RANGE, GROUPS (frame clause)

4.8.19.4.

EXCLUDE

4.8.19.5.

NULL treatment

4.8.19.6.

FROM FIRST, FROM LAST

4.8.19.7.

Nested aggregate functions

4.8.19.8.

Window aggregation

4.8.19.9.

Window ordered aggregate

4.8.19.10.

ROW_NUMBER

4.8.19.11.

RANK

4.8.19.12.

DENSE_RANK

4.8.19.13.

PERCENT_RANK

4.8.19.14.

CUME_DIST

4.8.19.15.

NTILE

4.8.19.16.

LEAD

4.8.19.17.

LAG

4.8.19.18.

FIRST_VALUE

4.8.19.19.

LAST_VALUE

4.8.19.20.

NTH_VALUE

The jOOQ User Manual SQL building Column expressions Window functions

previous : next