# Window Functions¶

## 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)
```

See Aggregation.