# Window functions

Window functions calculate a value for every selected row using related rows from the same partition. Unlike a `GROUP BY` aggregate, a window calculation retains the individual input rows.

Use window functions for running totals, moving averages, rankings and comparisons with a preceding or following observation.

## Syntax

```text
function(arguments) OVER (
    [PARTITION BY expression, ...]
    [ORDER BY expression [ASC | DESC], ...]
    [ROWS | RANGE | GROUPS frame_bounds [EXCLUDE exclusion]]
)
```

A named `WINDOW` definition can be reused by several expressions:

```sql
CREATE TABLE sales (region TEXT, day INTEGER, amount INTEGER);
INSERT INTO sales VALUES ('East', 1, 10), ('East', 2, 20), ('West', 1, 50);
```

```sql
SELECT region, day, amount,
       sum(amount) OVER daily AS running_total,
       row_number() OVER daily AS position
FROM sales
WINDOW daily AS (PARTITION BY region ORDER BY day)
ORDER BY region, day
FORMAT csv;
```

```text
"East",1,10,10,1
"East",2,20,30,2
"West",1,50,50,1
```

## Partitions and ordering

`PARTITION BY` groups rows for independent calculations. Without it, all selected rows belong to one partition.

The `ORDER BY` inside `OVER` determines positions for the calculation. It does not sort the final query result; use an outer `ORDER BY` for that. Rows equal on every window ordering expression are called **peers**. Ranking functions use peer groups to handle ties.

## Window frames

A frame selects a subset of a partition around the current row. Aggregate windows and `first_value`, `last_value` and `nth_value` read the frame. Ranking functions and `lag`/`lead` use the partition regardless of frame bounds.

With an ordering clause and no explicit frame, the frame runs from the beginning of the partition through the current peer group. Without ordering, the default frame covers the whole partition.

| Unit     | Meaning of an offset                                                                          |
| -------- | --------------------------------------------------------------------------------------------- |
| `ROWS`   | A number of physical rows before or after the current row.                                    |
| `GROUPS` | A number of peer groups before or after the current peer group.                               |
| `RANGE`  | A distance from the current ordering value; an offset frame requires one ordering expression. |

Bounds can use `UNBOUNDED PRECEDING`, `offset PRECEDING`, `CURRENT ROW`, `offset FOLLOWING` and `UNBOUNDED FOLLOWING`, subject to their valid start/end ordering. `ROWS` and `GROUPS` offsets must be nonnegative whole numbers; `RANGE` offsets must be nonnegative numeric values.

### Moving totals

An explicit `ROWS` frame includes the current sale and at most one preceding sale:

```sql
CREATE TABLE sales_2 (day INTEGER, amount INTEGER);
INSERT INTO sales_2 VALUES (1, 10), (2, 20), (3, 30);
```

```sql
SELECT day, amount,
       sum(amount) OVER (
           ORDER BY day
           ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
       ) AS moving_total
FROM sales_2
ORDER BY day
FORMAT csv;
```

```text
1,10,10
2,20,30
3,30,50
```

### Frame exclusions

`EXCLUDE NO OTHERS` keeps every selected frame row. `EXCLUDE CURRENT ROW` removes the current row. `EXCLUDE GROUP` removes its whole peer group. `EXCLUDE TIES` removes other peers while retaining the current row.

## Aggregate windows

Ordinary aggregates such as `sum`, `count` and `avg` can be followed by `OVER`. Their local input and `NULL` rules still apply. Aggregate `DISTINCT` is not supported in windows. Ordered-set percentiles explicitly exclude `OVER`; consult the [aggregate reference](https://coordinatordb.com/docs/reference/functions/aggregate-functions) for restrictions on individual functions.

## Function reference

* [`row_number`](https://coordinatordb.com/docs/reference/functions/window-functions/row-number)
* [`rank`](https://coordinatordb.com/docs/reference/functions/window-functions/rank)
* [`dense_rank`](https://coordinatordb.com/docs/reference/functions/window-functions/dense-rank)
* [`percent_rank`](https://coordinatordb.com/docs/reference/functions/window-functions/percent-rank)
* [`cume_dist`](https://coordinatordb.com/docs/reference/functions/window-functions/cume-dist)
* [`ntile`](https://coordinatordb.com/docs/reference/functions/window-functions/ntile)
* [`lag`](https://coordinatordb.com/docs/reference/functions/window-functions/lag)
* [`lead`](https://coordinatordb.com/docs/reference/functions/window-functions/lead)
* [`first_value`](https://coordinatordb.com/docs/reference/functions/window-functions/first-value)
* [`last_value`](https://coordinatordb.com/docs/reference/functions/window-functions/last-value)
* [`nth_value`](https://coordinatordb.com/docs/reference/functions/window-functions/nth-value)