Оконные функции

Оконные функции — это функции, которые выполняют вычисления для набора строк, некоторым образом связанных с текущей строкой. При этом в отличие от агрегатных функций, значения, вычисленные оконными функциями, могут проставляться в каждую строку исходной таблицы.

Вызов оконной функции всегда содержит выражение OVER, следующее за названием и аргументами оконной функции. Выражение OVER определяет, как именно нужно разделить строки набора данных для обработки оконной функцией.

Выражение OVER может дополняться выражением PARTITION BY, которое разделяет строки набора данных по группам, объединяя строки в группы по совпадению значений указанных столбцов. Значение оконной функции вычисляется по строкам, попадающим в одну группу с текущей строкой.

cume_dist()

Описание

Кумулятивное распределение.
Использование

cume_dist([ORDER BY column])

Кумулятивное распределение: количество строк группы, предшествующих текущей строке или с тем же значением, что и в текущей строке, деленное на общее количество строк группы.

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 2),
    ('A', 3),
    ('B', 4);

SELECT gr, num,
    cume_dist(ORDER BY num) OVER (PARTITION BY gr) AS cume_dist
    FROM t ORDER BY num;
+-------+--------+-----------+
| gr    | num    | cume_dist |
+-------+--------+-----------+
| A     | 1      | 0.25      |
+-------+--------+-----------+
| A     | 2      | 0.75      |
+-------+--------+-----------+
| A     | 2      | 0.75      |
+-------+--------+-----------+
| A     | 3      | 1         |
+-------+--------+-----------+
| B     | 4      | 1         |
+-------+--------+-----------+

dense_rank()

Описание

Ранг текущей строки внутри группы без пропусков.
Использование

dense_rank()
Псевдонимы

rank_dense()

Возвращает ранг текущей строки внутри группы без пропусков. По сути эта функция считает группы строк с совпадающими значениями по данному столбцу и назначает им номера внутри группы.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 2),
    ('A', 5),
    ('B', 4);

SELECT gr, num,
    dense_rank() OVER (PARTITION BY gr ORDER BY num) AS dense_rank
    FROM t ORDER BY gr, num;
+----+--------+------------+
| gr | num    | dense_rank |
+----+--------+------------+
| A  | 1      | 1          |
+----+--------+------------+
| A  | 2      | 2          |
+----+--------+------------+
| A  | 2      | 2          |
+----+--------+------------+
| A  | 5      | 3          |
+----+--------+------------+
| B  | 4      | 1          |
+----+--------+------------+

first_value()

Описание

Возвращает значение, вычисленное по указанному выражению для первой строки группы.
Использование

first_value(expression[ ORDER BY column][ IGNORE NULLS])

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Параметры

  • IGNORE NULLS — значение вычисляется без учета строк со значением NULL.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('A', NULL),
    ('B', 4);

SELECT gr, num,
    first_value(num) OVER (PARTITION BY gr) AS first_value
    FROM t ORDER BY gr, num;
+----+------+-------------+
| gr | num  | first_value |
+----+------+-------------+
| A  | 1    | 1           |
+----+------+-------------+
| A  | 2    | 1           |
+----+------+-------------+
| A  | 3    | 1           |
+----+------+-------------+
| A  | null | 1           |
+----+------+-------------+
| B  | 4    | 4           |
+----+------+-------------+

lag()

Описание

Возвращает значение, вычисленное для строки, сдвинутой на offset строк от текущей к началу группы.
Использование

lag(expression[, offset[, default]][ ORDER BY column][ IGNORE NULLS])

Если такой строки нет, возвращается значение default (оно должно быть совместимого по типу).

Оба аргумента, offset и default, являются опциональными. Если они не указываются, то используются следующие значения по умолчанию:

  • offset: 1

  • default: NULL

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Параметры

  • IGNORE NULLS — значение вычисляется без учета строк со значением NULL.

Посмотреть примеры 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    lag(num) OVER (PARTITION BY gr) AS lag
    FROM t ORDER BY gr, num;
+----+-----+------+
| gr | num | lag  |
+----+-----+------+
| A  | 1   | null |
+----+-----+------+
| A  | 2   | 1    |
+----+-----+------+
| A  | 3   | 2    |
+----+-----+------+
| B  | 4   | null |
+----+-----+------+
| B  | 5   | 4    |
+----+-----+------+
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    lag(num, 1, -1) OVER (PARTITION BY gr) AS lag
    FROM t ORDER BY gr, num;
+----+-----+-----+
| gr | num | lag |
+----+-----+-----+
| A  | 1   | -1  |
+----+-----+-----+
| A  | 2   | 1   |
+----+-----+-----+
| A  | 3   | 2   |
+----+-----+-----+
| B  | 4   | -1  |
+----+-----+-----+
| B  | 5   | 4   |
+----+-----+-----+

last_value()

Описание

Возвращает значение, вычисленное по указанному выражению для последней строки группы.
Использование

last_value(expression[ ORDER BY column][ IGNORE NULLS])

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Параметры

  • IGNORE NULLS — значение вычисляется без учета строк со значением NULL.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('A', NULL),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    last_value(num IGNORE NULLS) OVER (PARTITION BY gr) AS last_value
    FROM t ORDER BY gr, num;
+----+------+------------+
| gr | num  | last_value |
+----+------+------------+
| A  | 1    | 3          |
+----+------+------------+
| A  | 2    | 3          |
+----+------+------------+
| A  | 3    | 3          |
+----+------+------------+
| A  | null | 3          |
+----+------+------------+
| B  | 4    | 5          |
+----+------+------------+
| B  | 5    | 5          |
+----+------+------------+

lead()

Описание

Возвращает значение, вычисленное для строки, сдвинутой на offset строк от текущей к концу группы.
Использование

lead(expression[, offset[, default]][ ORDER BY column][ IGNORE NULLS])

Если такой строки нет, возвращается значение default (оно должно быть совместимого по типу).

Оба аргумента, offset и default, являются опциональными. Если они не указываются, то используются следующие значения по умолчанию:

  • offset: 1

  • default: NULL

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Параметры

  • IGNORE NULLS — значение вычисляется без учета строк со значением NULL.

Посмотреть примеры 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    lead(num) OVER (PARTITION BY gr) AS lead
    FROM t ORDER BY gr, num;
+----+-----+------+
| gr | num | lead |
+----+-----+------+
| A  | 1   | 2    |
+----+-----+------+
| A  | 2   | 3    |
+----+-----+------+
| A  | 3   | null |
+----+-----+------+
| B  | 4   | 5    |
+----+-----+------+
| B  | 5   | null |
+----+-----+------+
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    lead(num, 1, -1) OVER (PARTITION BY gr) AS lead
    FROM t ORDER BY gr, num;
+----+-----+------+
| gr | num | lead |
+----+-----+------+
| A  | 1   | 2    |
+----+-----+------+
| A  | 2   | 3    |
+----+-----+------+
| A  | 3   | -1   |
+----+-----+------+
| B  | 4   | 5    |
+----+-----+------+
| B  | 5   | -1   |
+----+-----+------+

nth_value()

Описание

Возвращает значение, вычисленное для n-ой строки внутри группы (считая с 1).
Использование

nth_value(expression, n[ ORDER BY column][ IGNORE NULLS])

Если такой строки нет, возвращается значение NULL.

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Параметры

  • IGNORE NULLS — значение вычисляется без учета строк со значением NULL.

Посмотреть примеры 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    nth_value(num, 3) OVER (PARTITION BY gr) AS third_value
    FROM t ORDER BY gr, num;
+----+-----+-------------+
| gr | num | third_value |
+----+-----+-------------+
| A  | 1   | 3           |
+----+-----+-------------+
| A  | 2   | 3           |
+----+-----+-------------+
| A  | 3   | 3           |
+----+-----+-------------+
| B  | 4   | null        |
+----+-----+-------------+
| B  | 5   | null        |
+----+-----+-------------+

ntile()

Описание

Разбивает каждую группу на num подгрупп равного (насколько возможно) размера и возвращает номер подгруппы.
Использование

ntile(num[ ORDER BY ordering])

Разбиение происходит так, чтобы размеры подгрупп были максимально близкими.

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    ntile(2) OVER (PARTITION BY gr) AS group_number
    FROM t ORDER BY gr, num;
+----+-----+--------------+
| gr | num | group_number |
+----+-----+--------------+
| A  | 1   | 1            |
+----+-----+--------------+
| A  | 2   | 1            |
+----+-----+--------------+
| A  | 3   | 2            |
+----+-----+--------------+
| B  | 4   | 1            |
+----+-----+--------------+
| B  | 5   | 2            |
+----+-----+--------------+

percent_rank()

Описание

Вычисляет относительный ранг текущей строки внутри группы.
Использование

` percent_rank([ORDER BY column])`

Возвращает значение типа DOUBLE от 0 до 1 включительно. Относительный ранг вычисляется по формуле (rank - 1) / (общее число строк группы - 1).

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    percent_rank(ORDER BY num) OVER (PARTITION BY gr) AS percent_rank,
    FROM t ORDER BY gr, num;
+----+-----+--------------+
| gr | num | percent_rank |
+----+-----+--------------+
| A  | 1   | 0            |
+----+-----+--------------+
| A  | 2   | 0.5          |
+----+-----+--------------+
| A  | 3   | 1            |
+----+-----+--------------+
| B  | 4   | 0            |
+----+-----+--------------+
| B  | 5   | 1            |
+----+-----+--------------+

rank()

Описание

Возвращает ранг (с пропусками) текущей строки внутри группы.
Использование

rank([ORDER BY column])

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    rank(ORDER BY num) OVER (PARTITION BY gr) AS rank,
    FROM t ORDER BY gr, num;
+----+-----+------+
| gr | num | rank |
+----+-----+------+
| A  | 1   | 1    |
+----+-----+------+
| A  | 2   | 2    |
+----+-----+------+
| A  | 2   | 2    |
+----+-----+------+
| A  | 3   | 4    |
+----+-----+------+
| B  | 4   | 1    |
+----+-----+------+
| B  | 5   | 2    |
+----+-----+------+

row_number()

Описание

Возвращает номер текущей строки в её группе (считая с 1).
Использование

row_number([ORDER BY column])

Если указано условие ORDER BY, значение вычисляется с использованием указанного порядка.

Посмотреть пример 
CREATE TABLE t(gr VARCHAR, num BIGINT);

INSERT INTO t VALUES
    ('A', 1),
    ('A', 2),
    ('A', 2),
    ('A', 3),
    ('B', 4),
    ('B', 5);

SELECT gr, num,
    row_number() OVER (PARTITION BY gr) AS row_number,
    FROM t ORDER BY gr, num;
+----+-----+------------+
| gr | num | row_number |
+----+-----+------------+
| A  | 1   | 1          |
+----+-----+------------+
| A  | 2   | 2          |
+----+-----+------------+
| A  | 2   | 3          |
+----+-----+------------+
| A  | 3   | 4          |
+----+-----+------------+
| B  | 4   | 1          |
+----+-----+------------+
| B  | 5   | 2          |
+----+-----+------------+