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
Window functions
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
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
- 3.8.19.1.
- PARTITION BY
- 3.8.19.2.
- ORDER BY
- 3.8.19.3.
- ROWS, RANGE, GROUPS (frame clause)
- 3.8.19.4.
- EXCLUDE
- 3.8.19.5.
- NULL treatment
- 3.8.19.6.
- FROM FIRST, FROM LAST
- 3.8.19.7.
- Nested aggregate functions
- 3.8.19.8.
- Window aggregation
- 3.8.19.9.
- Window ordered aggregate
- 3.8.19.10.
- ROW_NUMBER
- 3.8.19.11.
- RANK
- 3.8.19.12.
- DENSE_RANK
- 3.8.19.13.
- PERCENT_RANK
- 3.8.19.14.
- CUME_DIST
- 3.8.19.15.
- NTILE
- 3.8.19.16.
- LEAD
- 3.8.19.17.
- LAG
- 3.8.19.18.
- FIRST_VALUE
- 3.8.19.19.
- LAST_VALUE
- 3.8.19.20.
- NTH_VALUE
previous : next |
Feedback
Do you have any feedback about this page? We'd love to hear it!