Использование оконных функций¶
Оконная функция отличается от
скалярной или агрегатной наличием собственного окна.
Параметры окна описываются в запросе с помощью выражения OVER. Также, для
каждой оконной функции может быть дополнительно задано фильтрующее выражение
FILTER. Таким образом, если функция применяется к окну с помощью OVER,
то она считается оконной. В противном случае — агрегатной или скалярной.
Примечание
Оконные функции разрешены только в проекции оператора SELECT.
Синтаксис¶
Раздел¶
Диаграмма
Выражение¶
Диаграмма
Литерал¶
Диаграмма
Параметры¶
Примеры в этом разделе предполагают, что база данных содержит следующие данные:
CREATE TABLE t1(a INTEGER PRIMARY KEY, b text, c text) DISTRIBUTED BY (a);
INSERT INTO t1 VALUES (1, 'A', 'one' ),
(2, 'B', 'two' ),
(3, 'C', 'three'),
(4, 'D', 'one' ),
(5, 'E', 'two' ),
(6, 'F', 'three'),
(7, 'G', 'one' );
Фильтрация¶
Если указано выражение FILTER, в набор данных для оконной функции включаются
только те кортежи, для которых значение фильтрующего выражения истинно.
Примечание
FILTER может использоваться только для агрегатных оконных функций.
Пример фильтрации¶
SELECT c, a, b, group_concat(b, '.') FILTER (WHERE c!='two') OVER (
ORDER BY a
)
FROM t1 ORDER BY a;
c | a | b | col_1
-------+---+---+-------------
one | 1 | A | "A"
two | 2 | B | "A"
three | 3 | C | "A.C"
one | 4 | D | "A.C.D"
two | 5 | E | "A.C.D"
three | 6 | F | "A.C.D.F"
one | 7 | G | "A.C.D.F.G"
В этом запросе FILTER (WHERE c!='two') исключает кортежи, в которых c = 'two',
из рамок окна. Однако в итоговом наборе данных кортежи с c = 'two' остаются,
и им присваивается результат, вычисленный только по отфильтрованным кортежам.
Например, в кортеже (2, 'B') значение group_concat остается A, так как
B не входит в рамки окна.
Разделение и упорядочивание¶
При вычислении оконных функций результирующий набор запроса делится на один
или несколько разделов. Раздел включает
все кортежи, имеющие одинаковые значения для всех выражений в PARTITION BY.
Если PARTITION BY отсутствует, весь результат запроса считается одним разделом.
Обработка оконных функций выполняется отдельно для каждого раздела.
ORDER BY определяет порядок, в котором
оконная функция обрабатывает кортежи раздела. Оно работает аналогично ORDER BY
на уровне SELECT, но также не принимает имена выходных столбцов или числа. Без
ORDER BY кортежи обрабатываются в неопределенном порядке.
Пример раздела с идущими подряд кортежами¶
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM t1
ORDER BY c, a;
c | a | b | col_1
-------+---+---+---------
one | 1 | A | "A.D.G"
one | 4 | D | "D.G"
one | 7 | G | "G"
three | 3 | C | "C.F"
three | 6 | F | "F"
two | 2 | B | "B.E"
two | 5 | E | "E"
В этом запросе выражение PARTITION BY c разбивает результирующий набор на
три раздела. Первый раздел содержит три кортежа с c = 'one', второй — два
кортежа с c = 'three', третий — два кортежа с c = 'two'. В итоговом
результате кортежи каждого раздела идут подряд, так как PARTITION BY c
совпадает с началом выражения ORDER BY во всем запросе.
Однако это не является обязательным. Кортежи одного раздела могут быть разбросаны по всему результирующему набору.
Пример раздела с разбросанными кортежами¶
SELECT c, a, b, group_concat(b, '.') OVER (
PARTITION BY c ORDER BY a
RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM t1
ORDER BY a;
c | a | b | col_1
-------+---+---+---------
one | 1 | A | "A.D.G"
two | 2 | B | "B.E"
three | 3 | C | "C.F"
one | 4 | D | "D.G"
two | 5 | E | "E"
three | 6 | F | "F"
one | 7 | G | "G"
В этом случае кортежи сортируются по a, но разделы остаются неизменными,
так какPARTITION BY c продолжает делить результат на три независимых
группы.
Рамки окна¶
Оконная агрегатная функция считывает кортежи в соответствии с рамками окна. Определение рамок включает три части:
- Тип рамки —
ROWSилиRANGE. - Начальная граница.
- Конечная граница.
Конечную границу можно опустить, если также убрать ключевые слова BETWEEN
и AND. В этом случае она по умолчанию принимает значение CURRENT ROW.
Если тип рамки — RANGE, то кортежи с одинаковыми значениями во всех
выражениях ORDER BY считаются равнозначными (peers). Если ORDER BY
отсутствует, все кортежи считаются равнозначными. Равнозначные кортежи всегда
находятся внутри одной рамки окна.
По умолчанию оконные агрегатные функции считывают все кортежи от начала раздела до текущего кортежа включительно, а также его равнозначные кортежи.
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE NO OTHERS
Это означает, что кортежи с одинаковыми значениями во всех выражениях
ORDER BY будут иметь одинаковый результат оконной функции.
SELECT a, b, c,
group_concat(b, '.') OVER (ORDER BY c)
FROM t1 ORDER BY a;
a | b | c | col_1
---+---+-------+-----------------
1 | A | one | "A.D.G"
2 | B | two | "A.D.G.C.F.B.E"
3 | C | three | "A.D.G.C.F"
4 | D | one | "A.D.G"
5 | E | two | "A.D.G.C.F.B.E"
6 | F | three | "A.D.G.C.F"
7 | G | one | "A.D.G"
Тип рамки¶
Существует два типа рамок окна: ROWS и RANGE. Тип рамки определяет,
как вычисляются начальная и конечная границы окна.
ROWS¶
Тип ROWS означает, что начальная и конечная границы определяются
путем подсчета отдельных кортежей относительно текущего кортежа.
RANGE¶
Тип RANGE требует, чтобы в ORDER BY окна было ровно одно выражение,
обозначим его как X. В этом случае границы рамки определяются на основе
значений X во всем разделе. В рамку включаются кортежи, у которых значение
X находится в определенном диапазоне относительно значения X для
текущего кортежа. Подробнее это описано в разделе ниже
про тип границы PRECEDING.
Оба типа — ROWS и RANGE — вычисляют рамки относительно текущего кортежа,
но ROWS считает отдельные кортежи, тогда как RANGE использует диапазон
значений выражения X в ORDER BY.
Границы рамки¶
Существует пять способов задать начальную и конечную границы рамки окна:
-
UNBOUNDED PRECEDINGНачальная граница — первый кортеж в разделе. -
<expr> PRECEDING<expr>— неотрицательное числовое выражение. Рамка начинается с кортежа, которая находится на<expr>позиций перед текущим кортежем. Единицы измерения зависят от типа рамки: -
ROWS- рамка начинается с кортежа, находящейся на<expr>кортежей перед текущей, либо с первого кортежа раздела, если таких кортежей меньше<expr>.<expr>должно быть целым числом. -
RANGE-ORDER BYв окне должен содержать только одно выражениеX. ПустьXi— значениеXдля кортежаi, аXc— значениеXдля текущего кортежа. Граница рамки определяется так:- Если
XiилиXcнечисловые, граница — первый кортеж, для которойXi IS Xcистинно. - Если
ORDER BY ASC, граница — первый кортеж, для которойXi >= Xc - <expr>. - Если
ORDER BY DESC, граница — первый кортеж, для которойXi <= Xc + <expr>. <expr>может быть нецелым числом, но должно быть неотрицательной константой.
- Если
Описание границы "0 PRECEDING" всегда эквивалентно "CURRENT ROW".
-
CURRENT ROWТекущий кортеж. В режимеRANGEв рамку также входят равнозначные кортежи. -
<expr> FOLLOWINGРаботает аналогично<expr> PRECEDING, но рамка начинается на<expr>кортежей после текущего. -
UNBOUNDED FOLLOWINGКонечная граница — последний кортеж в разделе.
Конечная граница не может стоять раньше начальной в приведенном выше списке.
Пример рамки¶
В этом запросе рамки окна включают все кортежи от текущего до конца набора,
упорядоченного по ORDER BY a.
SELECT c, a, b, group_concat(b, '.') OVER (
ORDER BY c, a ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
FROM t1 ORDER BY c, a;
c | a | b | col_1
-------+---+---+-----------------
one | 1 | A | "A.D.G.C.F.B.E"
one | 4 | D | "D.G.C.F.B.E"
one | 7 | G | "G.C.F.B.E"
three | 3 | C | "C.F.B.E"
three | 6 | F | "F.B.E"
two | 2 | B | "B.E"
two | 5 | E | "E"
В этом примере ORDER BY c, a сортирует кортежи, а
ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING задает рамки окна от
текущего кортежа до конца раздела.
Виды функций¶
Оконные функции в Picodata бывают двух видов: агрегатные и встроенные. Любая
агрегатная оконная функция может работать как обычная агрегатная,
если опустить выражения OVER и FILTER. Кроме того, все встроенные агрегатные
функции Picodata могут использоваться как оконные, если добавить к ним
соответствующее выражение OVER.
Агрегатные оконные функции¶
Агрегатная оконная функция похожа на обычную агрегатную, за исключением
того, что ее добавление в запрос не изменяет количество возвращаемых кортежей.
Вместо этого для каждого кортежа результат такой функции вычисляется так, как
если бы соответствующая агрегатная функция была применена ко всем кортежам
в рамках окна, определенных выражением OVER.
Пример запроса:
SELECT a, b, group_concat(b, '.') OVER (
ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) FROM t1;
a | b | col_1
---+---+---------
1 | A | "A.B"
2 | B | "A.B.C"
3 | C | "B.C.D"
4 | D | "C.D.E"
5 | E | "D.E.F"
6 | F | "E.F.G"
7 | G | "F.G"
В этом примере рамки окна включают все кортежи между предыдущей
(1 PRECEDING) и следующей (1 FOLLOWING), включая текущую. Кортежи
сортируются согласно ORDER BY внутри определения окна (в данном случае
ORDER BY a). Например, для кортежей с a=3 окно включает кортежи:
(2, 'B', 'two'), (3, 'C', 'three'), (4, 'D', 'one'). Следовательно,
результат group_concat(b, '.') для этого кортежа — 'B.C.D'.
Встроенные оконные функции¶
Помимо агрегатных оконных функций, Picodata поддерживает
встроенные оконные функции, которые нельзя использовать
без выражения OVER.
Встроенные оконные функции учитывают выражение PARTITION BY так же, как и
агрегатные оконные функции: каждый выбранный кортеж относится к своему
разделу, и каждый раздел обрабатывается отдельно. Если ORDER BY присутствует
в определении окна, оно влияет на порядок нумерации кортежей.
Использование общего окна¶
Если несколько оконных функций используют одно и то же определение окна,
можно объявить его один раз с WINDOW:
SELECT row_number() OVER w, SUM(a) OVER w FROM t1 WINDOW w AS ();
col_1 | col_2
-------+-------
1 | 28
2 | 28
3 | 28
4 | 28
5 | 28
6 | 28
7 | 28