# Working with Window Functions

Window functions perform ranking and aggregate calculations across sets of rows that bear some relation to the current row. Unlike aggregates, which also act on multiple rows, window functions return a result for every row, instead of aggregating values from all rows into a single value.

## Syntax

Window functions in SingleStore use the following syntax:

```
function ([expr]) OVER {window_name | (window_definition)}

window_definition:
    [partition_by_clause] [order_by_clause] [frame_clause]

partition_by_clause:
    PARTITION BY {col | expr}

order_by_clause:
    ORDER BY {col | expr}

frame_clause:
    {ROWS | RANGE}
        {UNBOUNDED PRECEDING | int PRECEDING | CURRENT ROW} |
        {BETWEEN
            {UNBOUNDED PRECEDING | int {PRECEDING | FOLLOWING} | CURRENT ROW}
         AND
            {UNBOUNDED FOLLOWING | int {PRECEDING | FOLLOWING} | CURRENT ROW}
        }
```

## Syntax Remarks

## window\_definition

Window definition clauses are similar to standard `GROUP BY` and `ORDER BY` clauses with the following exception: you cannot refer to the projections list using aliases or position, because literal integers are treated as integers instead of positional references.

## frame\_clause

The `frame_clause` is used with an aggregate window function, and it specifies a subset of rows in a partition to evaluate. Ranking, value, and percentile window functions are not affected by the `frame_clause` because they are intended to operate on the entire set of rows in each partition.

In the `frame_clause`, `int PRECEDING` and `int FOLLOWING` are referred to as frame offsets. `RANGE` cannot be used with frame offsets. Additionally, frame offsets must be non-negative integers. Any non-integer is automatically rounded.

If `BETWEEN` is not specified, the default upper bound is `CURRENT ROW`. For example, `ROWS UNBOUNDED PRECEDING` is equivalent to `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.

If no frame is specified, the default is `RANGE UNBOUNDED PRECEDING`, i.e. `RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`.

`RANGE` includes all rows whose elements in the `order_by_clause` are equal. If the `order_by_clause` is empty, `RANGE` will include all rows in the partition.

The `order_by_clause` can include optional `NULLS FIRST` or `NULLS LAST` clauses that sort NULL values to the beginning or end of the results set. Ascending order is assumed for the non-NULL values. `DESC` can be added to the clause to order the non-NULL values in descending order. `NULLS FIRST` and `NULLS LAST` clauses are supported by all window functions. Consider the following examples:

```sql
CREATE TABLE wf_t (a INT);

INSERT INTO wf_t VALUES (1),(2),(2),(3),(3),(3),(4);

```

```sql
SELECT COUNT(1) OVER (ORDER BY a ROWS UNBOUNDED PRECEDING) AS col1,
          COUNT(1) OVER (ORDER BY a RANGE UNBOUNDED PRECEDING) AS col2 FROM wf_t;

```

```output

+------+------+
| col1 | col2 |
+------+------+
|    1 |    1 |
|    2 |    3 |
|    3 |    3 |
|    4 |    6 |
|    5 |    6 |
|    6 |    6 |
|    7 |    7 |
+------+------+
```

```sql
INSERT INTO wf_t VALUES (null), (null);
```

```sql
SELECT a, RANK() OVER (ORDER BY a NULLS FIRST) AS col1 FROM wf_t;

```

```output

+------+------+
| a    | col1 |
+------+------+
| NULL |    1 |
| NULL |    1 |
|    1 |    3 |
|    2 |    4 |
|    2 |    4 |
|    3 |    6 |
|    3 |    6 |
|    3 |    6 |
|    4 |    9 |
+------+------+
```

```sql
SELECT a, RANK() OVER (ORDER BY a NULLS LAST) AS col1 FROM wf_t;

```

```output

+------+------+
| a    | col1 |
+------+------+
|    1 |    1 |
|    2 |    2 |
|    2 |    2 |
|    3 |    4 |
|    3 |    4 |
|    3 |    4 |
|    4 |    7 |
| NULL |    8 |
| NULL |    8 |
+------+------+
```

```sql
SELECT a, RANK() OVER (ORDER BY a DESC NULLS FIRST) AS col1 FROM wf_t;

```

```output

+------+------+
| a    | col1 |
+------+------+
| NULL |    1 |
| NULL |    1 |
|    4 |    3 |
|    3 |    4 |
|    3 |    4 |
|    3 |    4 |
|    2 |    7 |
|    2 |    7 |
|    1 |    9 |
+------+------+
```

```sql
SELECT a, RANK() OVER (ORDER BY a DESC NULLS LAST) AS col1 FROM wf_t;

```

```output

+------+------+
| a    | col1 |
+------+------+
|    4 |    1 |
|    3 |    2 |
|    3 |    2 |
|    3 |    2 |
|    2 |    5 |
|    2 |    5 |
|    1 |    7 |
| NULL |    8 |
| NULL |    8 |
+------+------+
```

## Supported Functions

The following built-in functions are supported in a window function:

Ranking functions:

* [RANK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/rank.md)
* [DENSE\_RANK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/dense-rank.md)
* [ROW\_NUMBER](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/row-number.md)
* [NTILE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/ntile.md)
* [PERCENT\_RANK](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percent-rank.md)

Value functions:

* [LAG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/lag.md)
* [LEAD](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/lead.md)
* [FIRST\_VALUE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/first-value.md)
* [NTH\_VALUE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/nth-value.md)
* [LAST\_VALUE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/last-value.md)

Aggregate functions:

* [AVG](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/avg.md)

  [COUNT](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/count.md)
* [MIN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/min.md)
* [MAX](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/max.md)
* [STD](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/std.md)
* [STDDEV\_SAMP](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/stddev-samp.md)
* [SUM](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/sum.md)
* [VAR\_SAMP](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/var-samp.md)
* [VARIANCE](https://docs.singlestore.com/db/v9.1/reference/sql-reference/aggregate-functions/variance.md)

Percentile functions:

* [PERCENTILE\_DISC](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-disc.md)
* [PERCENTILE\_CONT and MEDIAN](https://docs.singlestore.com/db/v9.1/reference/sql-reference/window-functions/percentile-cont-and-median.md)

## Named Window Definition

`window_name` is a reference to a named window definition given in a `WINDOW` clause, such as:

```sql
SELECT RANK() OVER window_name FROM table WINDOW window_name AS (window_definition)
```

If using multiple window functions on the same window frame, you may see improved performance by setting and using a window\_name. Using the same window\_name across multiple functions allows SingleStore code generation to make execution more efficient.

Consider the following examples. The statement below does not use a `window_name` for two identical window definitions.

```sql
SELECT website, timestamp,
AVG(clicks) OVER (PARTITION BY website
                  ORDER BY timestamp
                  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING),
SUM(visits) OVER (PARTITION BY website
                  ORDER BY timestamp
                  ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING)
FROM t;
```

The statement above can be improved by using `window_name` syntax, which will improve performance:

```sql
SELECT website, timestamp,
AVG(clicks) OVER w,
SUM(visits) OVER w
FROM t
WINDOW w AS (PARTITION BY website
              ORDER BY timestamp
              ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING);
```

The second version of the query has fewer parameters, so the generated code can be smaller and more efficient.

## Examples

In the following simple examples, the table `t` contains four uniquely identified values divided into two categories.

```sql
CREATE TABLE wft (id int, category char(1), value int);

INSERT INTO wft VALUES (1, 'a', 1), (2, 'a', 3),
        (3, 'b', 2), (4, 'b', 4);

SELECT * FROM wft ORDER BY id;

```

```output

+----+----------+-------+
| id | category | value |
+----+----------+-------+
|  1 | a        |     1 |
|  2 | a        |     3 |
|  3 | b        |     2 |
|  4 | b        |     4 |
+----+----------+-------+
```

Selecting the average of a column with `GROUP BY` performs an aggregation that returns a single row per group. Selecting (projecting over) `id` would be meaningless in this case – SingleStore will return values from an arbitrary row.

```sql
SELECT category, avg(value) FROM wft GROUP BY category;

```

```output

+----------+------------+
| category | avg(value) |
+----------+------------+
| a        |     2.0000 |
| b        |     3.0000 |
+----------+------------+
```

In contrast, using `avg` as a window function with `OVER` and partitioning by category will compute the average value for each partition of category, then append that category-wise average to each row.

```sql
SELECT id, category, value, avg(value) over (partition by category) FROM wft;

```

```output

+----+----------+-------+-----------------------------------------+
| id | category | value | avg(value) over (partition by category) |
+----+----------+-------+-----------------------------------------+
|  2 | a        |     3 |                                  2.0000 |
|  1 | a        |     1 |                                  2.0000 |
|  4 | b        |     4 |                                  3.0000 |
|  3 | b        |     2 |                                  3.0000 |
+----+----------+-------+-----------------------------------------+
```

Here is another example where we order by `value` within each partition, and use the `rank()` window function to produce the rank of each row within its partition.

```sql
SELECT id, category, value, rank() OVER (partition by category order by value) AS rank FROM wft;

```

```output

+------+----------+-------+------+
| id   | category | value | rank |
+------+----------+-------+------+
|    1 | a        |     1 |    1 |
|    2 | a        |     3 |    2 |
|    3 | b        |     2 |    1 |
|    4 | b        |     4 |    2 |
+------+----------+-------+------+
```

***

Modified at: April 6, 2023

Source: [/db/v9.1/developer-resources/functional-extensions/working-with-window-functions/](https://docs.singlestore.com/db/v9.1/developer-resources/functional-extensions/working-with-window-functions/)

(An index of the documentation is available at /llms.txt)