Оконные функции в SQL: синтаксис и примеры в SQLite

Что такое оконные функции в SQL

Оконная функция — это тип SQL-функции, работающей с набором строк, называемым «окном», выбранным из результирующего набора оператора SELECT.

Если функция использует предложение OVER, она считается оконной; если нет, это обычная агрегатная или скалярная функция. Оконные функции также могут включать предложение FILTER между самой функцией и предложением OVER

Синтаксис вызова оконной функции выглядит следующим образом:

window-func ( expr ) [FILTER ( WHERE expr )] OVER window-name | window-defn

Оконные функции не используют ключевое слово DISTINCT и могут применяться только в результирующем наборе и в предложении ORDER BY оператора SELECT

Оконные функции делятся на агрегатные и встроенные. Агрегатные оконные функции могут действовать как обычные агрегатные, если опустить предложения OVER и FILTER. Встроенные агрегатные функции SQLite применяются в качестве оконных, если добавить предложение OVER. Приложения могут регистрировать новые агрегатные оконные функции через интерфейс sqlite3_create_window_function(), но встроенные функции требуют специального подхода в планировщике запросов.

Вот пример использования встроенной оконной функции row_number():

CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);
INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb');

-- Следующий оператор SELECT возвращает:
--
-- x | y | row_number
-- -----------------------
-- 1 | aaa | 1
-- 2 | ccc | 3
-- 3 | bbb | 2
--
SELECT x, y, row_number() OVER (ORDER BY y) AS row_number
FROM t0 ORDER BY x;

Функция row_number() присваивает каждой строке последовательные целые числа в порядке, указанном в предложении ORDER BY внутри определения окна — в данном случае ORDER BY y. Это не влияет на порядок, в котором результаты возвращаются из общего запроса, так как порядок итогового вывода определяется предложением ORDER BY, прикреплённым к оператору SELECT — в данном случае ORDER BY x.

Именованные определения окна также могут быть добавлены к оператору SELECT с помощью предложения WINDOW, а затем на них можно ссылаться по имени в вызовах оконных функций. Например, следующий оператор SELECT содержит два именованных определения окна — «win1» и «win2»:

SELECT x, y, row_number() OVER win1, rank() OVER win2
FROM t0
WINDOW win1 AS (ORDER BY y RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), win2 AS (PARTITION BY y ORDER BY x)
ORDER BY x;

Предложение WINDOW, если оно присутствует, располагается после любого предложения HAVING и перед любым предложением ORDER BY.

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы.

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы.

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

Все примеры в этом разделе предполагают, что база данных заполнена следующим образом:

CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
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' );

Агрегатная оконная функция ведет себя как обычная агрегатная, но не изменяет количество возвращаемых строк в запросе. Результат каждой строки выглядит так, будто агрегатная функция была применена ко всем строкам в определённом «окне».

-- Следующий оператор SELECT возвращает:
--
-- a | b | group_concat
-- -------------------------
-- 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
--
SELECT a, b, group_concat(b, '.') OVER ( ORDER BY a ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS group_concat
FROM t1;

В приведённом выше примере оконный фрейм состоит из всех строк между предыдущей строкой (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'.

Все агрегатные функции SQLite могут использоваться как агрегатные оконные функции. Также возможно создание пользовательских агрегатных оконных функций

Предложение PARTITION BY

Для целей вычисления оконных функций результирующий набор запроса делится на одну или несколько «секций» (partitions). Секция состоит из всех строк, имеющих одинаковое значение для всех элементов предложения PARTITION BY в определении окна. Если предложение PARTITION BY отсутствует, то весь результирующий набор запроса является единственной секцией. Обработка оконных функций выполняется отдельно для каждой секции.

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

Например:

-- Следующий оператор SELECT возвращает:
--
-- c | a | b | group_concat
-- ---------------------------------
-- 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
--
SELECT c, a, b, group_concat(b, '.') OVER ( PARTITION BY c ORDER BY a RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
) AS group_concat
FROM t1 ORDER BY c, a;

В приведённом выше запросе предложение PARTITION BY c разбивает результирующий набор на три секции. Первая секция содержит три строки с c == 'one'. Вторая секция содержит две строки с c == 'three', а третья секция содержит две строки с c == 'two'.

В приведённом выше примере все строки каждой секции сгруппированы вместе в итоговом выводе. Это происходит потому, что предложение PARTITION BY является префиксом предложения ORDER BY всего запроса. Но так бывает не всегда — секция может состоять из строк, произвольно разбросанных по результирующему набору.

Спецификации фрейма

Спецификация фрейма (frame-spec) определяет, какие выходные строки считываются агрегатной оконной функцией. Спецификация фрейма состоит из четырёх частей:

• Тип фрейма — ROWS, RANGE или GROUPS
• Начальная граница фрейма
• Конечная граница фрейма
• Предложение EXCLUDE

Тип фрейма задаёт единицу измерения, по которой определяются границы:

• ROWS — границы задаются в физических строках относительно текущей строки
• RANGE — границы задаются по значению выражения ORDER BY; строки с одинаковым значением считаются «равными» и попадают в фрейм вместе
• GROUPS — границы задаются в группах строк с одинаковым значением ORDER BY

Границы фрейма могут принимать следующие значения:

Предложение EXCLUDE позволяет исключить из фрейма определённые строки:

• EXCLUDE CURRENT ROW — исключает текущую строку
• EXCLUDE GROUP — исключает текущую строку и все строки с тем же значением ORDER BY
• EXCLUDE TIES — исключает строки с тем же значением ORDER BY, но оставляет текущую строку
• EXCLUDE NO OTHERS — ничего не исключает (поведение по умолчанию)

Если предложение OVER не содержит спецификации фрейма, то по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW при наличии предложения ORDER BY, либо RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING при его отсутствии.

Встроенные оконные функции SQLite: row_number, rank, lag и другие

SQLite поддерживает набор встроенных оконных функций, которые не являются агрегатными. Ниже приведён их полный список.

row_number()

Возвращает номер текущей строки в секции. Нумерация начинается с 1.

rank()

Возвращает ранг текущей строки с учётом пропусков. Строки с одинаковым значением ORDER BY получают одинаковый ранг, а следующий ранг пропускается на количество таких строк.

dense_rank()

percent_rank()

Вычисляет относительный ранг строки в секции по формуле (rank - 1) / (total_rows - 1). Возвращает значение от 0.0 до 1.0.

cume_dist()

Вычисляет кумулятивное распределение (cumulative distribution): долю строк в секции, значение которых меньше или равно значению текущей строки.

ntile(N)

Делит строки секции на N групп примерно равного размера и возвращает номер группы для каждой строки.

lag(expr [, offset [, default]])

Возвращает значение выражения expr для строки, находящейся на offset строк раньше текущей в секции. Если такой строки нет, возвращает default (по умолчанию NULL).

lead(expr [, offset [, default]])

first_value(expr)

Возвращает значение выражения expr для первой строки оконного фрейма.

last_value(expr)

nth_value(expr, N)

Когда я впервые разбирался с разницей между rank() и dense_rank(), проще всего было запустить оба запроса на одних и тех же данных и сравнить результаты вживую — поведение сразу становится очевидным.

Типичные ошибки при работе с оконными функциями

Работа с оконными функциями требует понимания нескольких тонкостей, на которых легко споткнуться.

Смешение ORDER BY окна и ORDER BY запроса. Предложение ORDER BY внутри OVER управляет только порядком строк внутри фрейма. Оно никак не влияет на порядок строк в итоговом результате — для этого нужен отдельный ORDER BY на уровне SELECT.

Неверные границы фрейма по умолчанию. Если ORDER BY в определении окна задан, а спецификация фрейма явно не указана, SQLite использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это означает, что last_value() без явного фрейма вернёт не последнее значение секции, а значение текущей строки. Чтобы получить ожидаемый результат, нужно явно указать ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

Использование DISTINCT. Оконные функции не поддерживают ключевое слово DISTINCT — попытка его использовать приведёт к ошибке.

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

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

Часто задаваемые вопросы об оконных функциях SQL

Чем оконная функция отличается от обычной агрегатной функции? Обычная агрегатная функция сворачивает несколько строк в одну. Оконная функция вычисляет результат для каждой строки отдельно, не уменьшая количество строк в результате запроса.

Можно ли использовать одну и ту же агрегатную функцию и как обычную, и как оконную? Да. Все встроенные агрегатные функции SQLite, например sum(), avg(), group_concat(), работают как оконные функции при добавлении предложения OVER.

Что произойдёт, если не указать спецификацию фрейма в предложении OVER? При наличии ORDER BY в определении окна SQLite по умолчанию использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. При отсутствии ORDER BY — RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, то есть весь фрейм охватывает всю секцию.

В чём разница между ROWS, RANGE и GROUPS? ROWS считает физические строки. RANGE объединяет строки с одинаковым значением ORDER BY в одну логическую единицу. GROUPS считает группы строк с одинаковым значением ORDER BY как единый блок.

Можно ли создать собственную оконную функцию в SQLite? Пользовательские агрегатные оконные функции можно зарегистрировать через интерфейс sqlite3_create_window_function(). Встроенные оконные функции с их специальными свойствами добавить через приложение нельзя — они требуют поддержки на уровне планировщика запросов.