Оконные функции aka over-функции

Если бы у нас были только агрегатные функции, мы были бы принуждены постоянно делать выбор: видеть детальные данные или агрегированные? Аналитические функции, известные также как оконные функции или over-функции, позволяют одновременно получить и то и другое. С их помощью можно видеть данные, агрегированные по множеству строк, рядом с детальными данными.

Обычные, или скалярные, функции принимают на вход значения столбцов одной — текущей — строки курсора. Агрегатные функции принимают на вход значения столбцов всех строк курсора, определяемых where и group by (или их отсутствием). Оконные функции принимают на вход значения столбцов множества строк, определяемого в предложении over и зависящего от текущей строки.

Рассмотрим оконные функции на примере СУБД PosgreSQL 15.

Для удобства обзора, разделим все оконные функции на три группы:

• агрегатные функции,
• ранжирующие функции,
• относительные функции.

Обычные агрегатные функции, кроме функций на упорядоченных множествах (с предложением within group), можно использовать как оконные функции, добавив к их вызову предложение over.

Ранжирующие и относительные функции нужно обязательно вызывать с предложением over.

Агрегатные функции

Ниже список агрегатных функций PosrgreSQL 15, исключая статистические функции и функции на упорядоченных множествах. Подробнее см. Aggregate Functions.

Функция	Описание
array_agg	Массив значений.
avg	Среднее арифметическое.
bit_and	Побитовое И.
bit_or	Побитовое ИЛИ.
bit_xor	Побитовое исключающее ИЛИ.
bool_and	Логическое И.
bool_or	Логическое ИЛИ.
count	Счетчик значений.
every	Стандартный эквивалент bool_and.
json_agg	Массив json.
jsonb_agg	Массив jsonb.
json_object_agg	Объект json.
jsonb_object_agg	Объект jsonb.
max	Максимальное значение.
min	Минимальное значение.
range_agg	Объединение диапазонов.
range_intersect_agg	Пересечение диапазонов.
string_agg	Конкатенация строк.
sum	Сумма значений.
xmlagg	Агрегат xml.

И пара примеров их использования в качестве оконных.

Агрегатная функция с order by в предложении over и оконной рамкой (window frame) по умолчанию производит накопительный эффект от строки к строке результирующей таблицы.

select n,
    count(n) over (order by n) count_,
    sum(n) over (order by n) sum_,
    avg(n) over (order by n) avg_
from
    generate_series(1,5) as t(n)
;

n	count_	sum_	avg_
1	1	1	1.0
2	2	3	1.5
3	3	6	2.0
4	4	10	2.5
5	5	15	3.0

select n,
    string_agg(n::text, ', ') over (order by n) asc_,
    string_agg(n::text, ', ') over (order by n desc) desc_
from
    generate_series(1,5) as t(n)
;

n	asc_	desc_
1	1	5, 4, 3, 2, 1
2	1, 2	5, 4, 3, 2
3	1, 2, 3	5, 4, 3
4	1, 2, 3, 4	5, 4
5	1, 2, 3, 4, 5	5

Ранжирующие функции

Ниже список ранжирующих функций PosrgreSQL 15 и пара примеров их использования. Подробнее см. Window Functions.

Функция	Описание
row_number	Номер текущей строки.
rank	Ранг текущей строки, с пропусками; это номер первой строки в группе равнозначных ей (its peer group).
dense_rank	Ранг текущей строки, без пропусков; это номер группы равнозначных строк (peer groups).
percent_rank	Ранг текущей строки в диапазоне от 0 до 1.
cume_dist	Кумулятивное распределение в диапазоне от 1/(число строк в окне) to 1.
ntile(arg)	Номер группы (bucket) от 1 до arg, в которую попадает текущая строка.

Все перечисленные функции нуждаются в упорядочивании входных данных с помощью order by в предложении over. Строки, которые не отличаются друг от друга значениями столбцов в order by, называются равнозначными (peers).

Функции rank, dense_rank, percent_rank и cume_dist возвращают одинаковые значения для всех строк из группы равнозначных.

select n,
       dense_rank() over (order by n) dense_rank_,
       percent_rank() over (order by n) percent_rank_,
       cume_dist() over (order by n) cume_dist_
from 
    generate_series(1,5) as t(n)
;

n	dense_rank_	percent_rank_	cume_dist_
1	1	0.0	0.2
2	2	0.25	0.4
3	3	0.5	0.6
4	4	0.75	0.8
5	5	1.0	1.0

select n,
       ntile(2) over (order by n) ntile_2,
       ntile(3) over (order by n) ntile_3
from 
    generate_series(1,10) as t(n)
;

n	ntile_2	ntile_3
1	1	1
2	1	1
3	1	1
4	1	1
5	1	2
6	2	2
7	2	2
8	2	3
9	2	3
10	2	3

Относительные функции

Ниже список относительных функций PosrgreSQL 15 и пара примеров их использования. Подробнее см. Window Functions.

Функция	Описание
lag	Значение из строки, предшествующей текущей.
lead	Значение из строки, следующей за текущей.
first_value	Значение из первой строки окна (window frame).
last_value	Значение из последней строки окна (window frame).
nth_value	Значение из n-ной строки окна (window frame).

select n,
    lag(n) over (order by n) lag_,
    lead(n) over (order by n) lead_
from 
    generate_series(1,5) as t(n)
;

n	lag_	lead_
1		2
2	1	3
3	2	4
4	3	5
5	4

Заметьте, что функции first_value, last_value и nth_value имеют дело только со строками в оконной рамке (window frame), которая по умолчанию содержит строки с первой строки окна до последней из равнозначных текущей строке.

select n,
    first_value(n) over (order by n) first_,
    nth_value(n, 3) over (order by n) nth_,
    last_value(n) over (order by n) last_
from 
    generate_series(1,5) as t(n)
;

n	first_	nth_	last_
1	1		1
2	1		2
3	1	3	3
4	1	3	4
5	1	3	5

Оконные рамки

Оконная рамка (window frame) определяет часть строк окна, на которых работает оконная функция.

В следующем примере значения столбцов first_ и last_ вычисляются в оконной рамке по умолчанию, а значения столбцов first_x и last_x вычисляются в оконной рамке, включающей текущую строку и все строки до конца окна:

select n,
    first_value(n) over (order by n) first_,
    first_value(n) over (order by n rows between current row and unbounded following) first_x,
    last_value(n) over (order by n) last_,
    last_value(n) over (order by n rows between current row and unbounded following) last_x
from 
    generate_series(1,5) as t(n)
;

n	first_	first_x	last_	last_x
1	1	1	1	5
2	1	2	2	5
3	1	3	3	5
4	1	4	4	5
5	1	5	5	5

Сузив оконные рамки до одной предыдущей и одной последующей строки, сымитируем функции lag и lead с помощью функций first_value и last_value:

select n,
    first_value(n) over (order by n rows between 1 preceding and 1 preceding) lag_,
    last_value(n) over (order by n rows between 1 following and 1 following) lead_
from 
    generate_series(1,5) as t(n)
;

n	lag_	lead_
1		2
2	1	3
3	2	4
4	3	5
5	4

Помимо rows, оконная рамка может определяться через range и groups. Подробнее см. описание frame_clause в документации PostgreSQL.

Больше окон, полезных и разных

В вышеприведенных примерах оконные функции работают в рамках одного окна, которое включает все строки, порождаемые частями from и where запроса select.

Но предложение partition by в over позволяет разбить это множество строк на группы, чтобы оконные функции работали отдельно со строками каждой из них – аналогично тому, как group by порождает группы строк для агрегатных функций. Таким образом, partition by разбивает множество строк на ряд окон.

Рассмотрим такой набор данных:

select
    case n % 3
        when 0 then
            'A'
        when 1 then
            'B'
        when 2 then
            'C'
    end item,
    n
from generate_series(1, 9) as t(n)
;

item	n
B	1
C	2
A	3
B	4
C	5
A	6
B	7
C	8
A	9

Тогда все множество строк можно разбить на группы по значению item:

select
    item,
    n,
    count(n) over (partition by item order by n) count_,
    lag(n) over (partition by item order by n) lag_
from (
    select
        case n % 3
            when 0 then
                'A'
            when 1 then
                'B'
            when 2 then
                'C'
        end item,
        n
    from generate_series(1, 9) as t(n)
) t
;

item	n	count_	lag_
A	3	1
A	6	2	3
A	9	3	6
B	1	1
B	4	2	1
B	7	3	4
C	2	1
C	5	2	2
C	8	3	5

Подробнее см. описание window_definition в документации PostgreSQL.

Что дальше

Оконные функции, а частности, используются при построении запросов вида "пробелы и острова", или "gaps and islands". См. мой пост Пробелы и острова, или Gaps and islands. Часть I.

Ранжирующие функции, а частности, помогают строить запросы вида "верхние-n", или "top-n", где требуется получить наименьшие или наибольшие по некоторому критерию n строк.

SQL неисчерпаем!