# 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.

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