Feedback
3.3
Window Functions¶
Table of Contents
Introduction¶
Window functions are functions which perform a computation across a set of rows which are related to the current row. This is comparable to aggregation functions, but window functions do not cause multiple rows to be grouped into a single row.
Window Definition¶
The OVER
clause defines the window
containing the appropriate rows
which will take part in the window function
computation.
An empty OVER
clause defines a window
containing all the rows in the
result set.
Example:
cr> select name, avg(price) OVER() from articles order by name;
+------------------------------+--------------------+
| name | avg(price) OVER () |
+------------------------------+--------------------+
| Infinite Improbability Drive | 18375.317556142807 |
| Kill-o-Zap blaster pistol | 18375.317556142807 |
| Starship Titanic | 18375.317556142807 |
| Towel | 18375.317556142807 |
+------------------------------+--------------------+
SELECT 4 rows in set (... sec)
If ORDER BY
is supplied the window
definition consists of a range of
rows starting with the first row in the result set and ending with the current
row, plus any subsequent rows that are equal to the current row, which are the
current row’s peers
.
Example:
cr> select col1, sum(col1) over (order by col1) from unnest([1, 5, 2, 3, 2, 5, 4]);
+------+--------------------------------------+
| col1 | sum(col1) OVER (ORDER BY "col1" ASC) |
+------+--------------------------------------+
| 1 | 1 |
| 2 | 5 |
| 2 | 5 |
| 3 | 8 |
| 4 | 12 |
| 5 | 22 |
| 5 | 22 |
+------+--------------------------------------+
SELECT 7 rows in set (... sec)
Note
Taking into account the peers
concept mentioned above, for an empty
OVER
clause all the rows in the result set are peers
.
Note
Aggregation functions will be treated as
window functions
when used in conjuction with the OVER
clause.
Note
Window definitions order or partitioned by an array column type are currently not supported.
OVER¶
Synopsis¶
OVER (
[ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
)
Example:
cr> select price, sum(price) OVER(ORDER BY price) from articles;
+----------+----------------------------------------+
| price | sum(price) OVER (ORDER BY "price" ASC) |
+----------+----------------------------------------+
| 1.29 | 1.29 |
| 3499.99 | 3501.28 |
| 19999.99 | 23501.27 |
| 50000.0 | 73501.266 |
+----------+----------------------------------------+
SELECT 4 rows in set (... sec)
General-Purpose Window Functions¶
row_number()
¶
Returns the number of the current row within its window.
Example:
cr> select col1, row_number() over(order by col1) from unnest(['x','y','z']);
+------+-----------------------------------------+
| col1 | row_number() OVER (ORDER BY "col1" ASC) |
+------+-----------------------------------------+
| x | 1 |
| y | 2 |
| z | 3 |
+------+-----------------------------------------+
SELECT 3 rows in set (... sec)
first_value(arg)
¶
Note
The first_value
window function is an enterprise
feature.
Returns the argument value evaluated at the first row within the window.
Its return type is the type of its argument.
Example:
cr> select col1, first_value(col1) over(order by col1) from unnest(['x','y', 'y', 'z']);
+------+----------------------------------------------+
| col1 | first_value(col1) OVER (ORDER BY "col1" ASC) |
+------+----------------------------------------------+
| x | x |
| y | x |
| y | x |
| z | x |
+------+----------------------------------------------+
SELECT 4 rows in set (... sec)
last_value(arg)
¶
Note
The last_value
window function is an enterprise
feature.
Returns the argument value evaluated at the last row within the window.
Its return type is the type of its argument.
Example:
cr> select col1, last_value(col1) over(order by col1) from unnest(['x','y', 'y', 'z']);
+------+---------------------------------------------+
| col1 | last_value(col1) OVER (ORDER BY "col1" ASC) |
+------+---------------------------------------------+
| x | x |
| y | y |
| y | y |
| z | z |
+------+---------------------------------------------+
SELECT 4 rows in set (... sec)
nth_value(arg, number)
¶
Note
The nth_value
window function is an enterprise
feature.
Returns the argument value evaluated at row that is the nth row within the window. Null is returned if the nth row doesn’t exist in the window.
Its return type is the type of its first argument.
Example:
cr> select col1, nth_value(col1, 3) over(order by col1) from unnest(['x','y', 'y', 'z']);
+------+-----------------------------------------------+
| col1 | nth_value(col1, 3) OVER (ORDER BY "col1" ASC) |
+------+-----------------------------------------------+
| x | NULL |
| y | y |
| y | y |
| z | y |
+------+-----------------------------------------------+
SELECT 4 rows in set (... sec)