Window functions

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/window-functions/index.md)

Window functions let you perform calculations across a set of rows that are related to the current row.
Some of the calculations that you can do are similar to those that can be done with an aggregate function, but a window function doesn't cause rows to be grouped into a single output - the individual rows are still returned.

## Standard window functions[​](#standard-window-functions "Direct link to Standard window functions")

ClickHouse supports the standard grammar for defining windows and window functions. The table below indicates whether a feature is currently supported.

|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |
| --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- |
| Feature Supported?|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | ad hoc window specification (`count(*) over (partition by id order by time desc)`) ✅|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | expressions involving window functions, e.g. `(count(*) over ()) / 2)` ✅|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `WINDOW` clause (`select ... from table window w as (partition by id)`) ✅|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `ROWS` frame ✅|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `RANGE` frame ✅ (the default)|  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `INTERVAL` syntax for `DateTime` `RANGE OFFSET` frame ❌ (specify the number of seconds instead (`RANGE` works with any numeric type).)| `GROUPS` frame ❌|  |  |  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | Calculating aggregate functions over a frame (`sum(value) over (order by time)`) ✅ (All aggregate functions are supported)|  |  |  |  |  |  |  |  |  |  | | --- | --- | --- | --- | --- | --- | --- | --- | --- | --- | | `rank()`, `dense_rank()`, `row_number()` ✅  Alias: `denseRank()`| `percent_rank()` ✅ Efficiently computes the relative standing of a value within a partition in a dataset. This function effectively replaces the more verbose and computationally intensive manual SQL calculation expressed as `ifNull((rank() OVER(PARTITION BY x ORDER BY y) - 1) / nullif(count(1) OVER(PARTITION BY x) - 1, 0), 0)`  Alias: `percentRank()`| `cume_dist()` ✅ Computes the cumulative distribution of a value within a group of values. Returns the percentage of rows with values less than or equal to the current row's value.|  |  |  |  | | --- | --- | --- | --- | | `lag/lead(value, offset)` ✅   You can also use one of the following workarounds:  1) `any(value) over (.... rows between <offset> preceding and <offset> preceding)`, or `following` for `lead`   2) `lagInFrame/leadInFrame`, which are analogous, but respect the window frame. To get behavior identical to `lag/lead`, use `rows between unbounded preceding and unbounded following`| ntile(buckets) ✅   Specify window like, (partition by x order by y rows between unbounded preceding and unbounded following). | | | | | | | | | | | | | | | | | | | | | | | | | | | |

## ClickHouse-specific window functions[​](#clickhouse-specific-window-functions "Direct link to ClickHouse-specific window functions")

There is also the following ClickHouse specific window function:

### nonNegativeDerivative(metric\_column, timestamp\_column[, INTERVAL X UNITS])[​](#nonnegativederivativemetric_column-timestamp_column-interval-x-units "Direct link to nonNegativeDerivative(metric_column, timestamp_column[, INTERVAL X UNITS])")

Finds non-negative derivative for given `metric_column` by `timestamp_column`.
`INTERVAL` can be omitted, default is `INTERVAL 1 SECOND`.
The computed value is the following for each row:

- `0` for 1st row,
- metrici−metrici−1timestampi−timestampi−1∗interval{\text{metric}\_i - \text{metric}\_{i-1} \over \text{timestamp}\_i - \text{timestamp}\_{i-1}} \* \text{interval}timestampi​−timestampi−1​metrici​−metrici−1​​∗interval for ithi\_{th}ith​ row.

## Syntax[​](#syntax "Direct link to Syntax")

```
aggregate_function (column_name)
  OVER ([[PARTITION BY grouping_column] [ORDER BY sorting_column] 
        [ROWS or RANGE expression_to_bound_rows_within_the_group]] | [window_name])
FROM table_name
WINDOW window_name as ([
  [PARTITION BY grouping_column]
  [ORDER BY sorting_column]
  [ROWS or RANGE expression_to_bound_rows_within_the_group]
])
```

- `PARTITION BY` - defines how to break a resultset into groups.
- `ORDER BY` - defines how to order rows inside the group during calculation aggregate\_function.
- `ROWS or RANGE` - defines bounds of a frame, aggregate\_function is calculated within a frame.
- `WINDOW` - allows multiple expressions to use the same window definition.

```
      PARTITION
┌─────────────────┐  <-- UNBOUNDED PRECEDING (BEGINNING of the PARTITION)
│                 │
│                 │
│=================│  <-- N PRECEDING  <─┐
│      N ROWS     │                     │  F
│  Before CURRENT │                     │  R
│~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW    │  A
│     M ROWS      │                     │  M
│   After CURRENT │                     │  E
│=================│  <-- M FOLLOWING  <─┘
│                 │
│                 │
└─────────────────┘  <--- UNBOUNDED FOLLOWING (END of the PARTITION)
```

### Functions[​](#functions "Direct link to Functions")

These functions can be used only as a window function.

- [`row_number()`](/docs/sql-reference/window-functions/row_number) - Number the current row within its partition starting from 1.
- [`first_value(x)`](/docs/sql-reference/window-functions/first_value) - Return the first value evaluated within its ordered frame.
- [`last_value(x)`](/docs/sql-reference/window-functions/last_value) - Return the last value evaluated within its ordered frame.
- [`nth_value(x, offset)`](/docs/sql-reference/window-functions/nth_value) - Return the first non-NULL value evaluated against the nth row (offset) in its ordered frame.
- [`rank()`](/docs/sql-reference/window-functions/rank) - Rank the current row within its partition with gaps.
- [`dense_rank()`](/docs/sql-reference/window-functions/dense_rank) - Rank the current row within its partition without gaps.
- [`lagInFrame(x)`](/docs/sql-reference/window-functions/lagInFrame) - Return a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.
- [`leadInFrame(x)`](/docs/sql-reference/window-functions/leadInFrame) - Return a value evaluated at the row that is offset rows after the current row within the ordered frame.

## Examples[​](#examples "Direct link to Examples")

Let's have a look at some examples of how window functions can be used.

### Numbering rows[​](#numbering-rows "Direct link to Numbering rows")

```
CREATE TABLE salaries
(
    `team` String,
    `player` String,
    `salary` UInt32,
    `position` String
)
Engine = Memory;