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

Перед прочтением¶

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

Описание¶

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

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

Определение окна¶

Окно описывается следующим образом:

WINDOW w AS (
  PARTITION BY ... // указание секции
  ORDER BY ... // сортировка внутри окна 
  ROWS BETWEEN ... AND ... // указание фрейма
)

А использовать его можно в SELECT следующим образом:

SELECT <функция> OVER <окно>

Так же можно объявить несколько окон:

WINDOW
    W1 as (...),
    W2 as (...)

Секция¶

Секция (или же партиция) - это набор строк, которые использует оконная функция, разделенные по одному или нескольким столбцам.

Указание PARTITION BY разделяет набор данных на секции. Оконная функция применяется к каждой секции отдельно, и вычисление начинается заново для каждой секции.

Если мы хотим сделать секции по столбцу с предметом, то они будут выглядеть вот так - для наглядности каждую секцию выделил отдельным цветом.

Пример секции

Пример

Секции - очень полезный механизм, который позволяет разделить наш набор данных на определенные группы.

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

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w as "sum"
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
)

Результат работы запроса с сортировкой

Сортировка¶

Сортировка определяет порядок строк в каждой секции - иногда от этого зависит работа некоторых оконных функций.

Пример

Вот так мы можем выставить ранг оценок среди каждого ученика по предметам:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    dense_rank() OVER w AS rank
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.grade desc
)

Сортировка запроса не влияет на результат оконной функции при указании секции. Это происходит из-за того, что оконная функция оперирует только с данными внутри секции/окна, а не с общим порядком данных в таблице. Так же, ORDER BY вне запроса отрабатывает после того, как отработало окно и другие части запроса (join, where и т.д.)

Пример

Снова выставим ранг, но вне окна отсортируем значения grades:

SELECT 
grades.id, 
grades.name,
grades.subject,
grades.grade,
dense_rank() OVER w AS rank
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.grade desc
)
ORDER BY grades.grade asc

Можете проверить результат с предыдущим примером - поменялась лишь позиция данных, сами строки - нет.

Если секция не указана, то оконная функция выполняется на всём наборе данных.

Пример

Это полезно, например, когда мы хотим выставить ранг для каждого отдельного значения в окне. В текущем случае оконная функция будет считать, что весь наш набор данных - это есть одна цельная и единственная секция:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    dense_rank() OVER w AS rank
FROM grades
WINDOW w AS (
    ORDER BY grades.grade desc
)
ORDER BY rank

Результат order by без партиции

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

Фреймы¶

Фрейм — это группа записей, по которым на данный момент работает оконная функция.

Только некоторые функции поддерживают фреймы:

Функции смещения first_value(), last_value(), nth_value() и т.д.
Все функции агрегации: sum(), avg() и т.д.

Фреймы бывают по строкам (rows), группам (groups) и диапазону (range).

Определение фрейма выглядит так:

<TYPE> BETWEEN <FRAME_START> AND <FRAME_END> EXCLUDE <EXCLUDE_TYPE>

Вместо FRAME_START и FRAME_END можно вставлять следующие ключевые слова:

unbounded preceding / following
N preceding / following
current row

В зависимости от типа фрейма работа ключевых слов разная, кроме UNBOUNDED <x> - они всегда указывают на границу секции.

EXCLUDE - не обязательный параметр, он позволяет исключать записи из фрейма.

Нюансы:

current row == 0 preceding == 0 following
Фрейм никогда не выходит за границы секции, а если столкнулся с границей - то он просто обрезается.

Строковые¶

Строковые фреймы, как бы это банально не звучало, работают со строками.

Определение фрейма выглядит так:

ROWS BETWEEN <FRAME_START AND <FRAME_END>

Вместо FRAME_START и FRAME_END можно использовать следующие ключевые слова задающие его границы:

unbounded preceding - устанавливает границу фрейма на верхний размер секции
unbounded following - устанавливает границу фрейма на нижний размер секции
N following - устанавливает границу фрейма на N записей после текущей
N preceding - устанавливает границу фрейма на N записей перед текущей
current row - устанавливает границу фрейма на текущую запись

Например, возьмём вот такое окно:

WINDOW w AS (
  PARTITION BY grades.subject
  ORDER BY grades.subject
  ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
)

Это значит, что в фреим попадут текущая (CURRENT ROW) и следующая 1 FOLLOWING строка. Над окном сработает функция sum, которая получит сумму всех значений в фрейме.

Приведу несколько примеров работы с строковыми фреймами

ROWS BETWEEN CURRENT ROW AND CURRENT ROW

Граница фрейма будет установлена на текущую запись:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.subject
    ROWS BETWEEN CURRENT ROW AND CURRENT ROW
)
ORDER BY subject

ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING

Граница фрейма будет установлена с текущей записи, до 1 запись после текущей

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.subject
    ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING
)
ORDER BY subject

Можем проверить на первой секции: 5 + 4 = 9, 4 + 3 = 7, 3 + ничего = 3

ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Граница фрейма будет установлена от текущей записи до конца секции:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.subject
    ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
)
ORDER BY subject

Тоже очень легко посчитать, поочередно посчитаем каждую строку для первой секции:

5 + 4 + 3 = 12
4 + 3 = 7
3 + ничего = 3

ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING

Граница фрейма будет установлена на 1 запись после текущей:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.subject
    ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING
)
ORDER BY subject

ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Граница фрейма будет установлена от начала до конца секции:

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    PARTITION BY grades.subject
    ORDER BY grades.subject
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY subject

Групповые¶

Групповые фреймы, в отличии от строковых, оперируют группами записей, у которых одинаковое значение столбца из ORDER BY, поэтому в основном их используют без указания секции (без partition by).

Описываются следующим образом:

GROUPS BETWEEN <FRAME_START> AND <FRAME_END>

Вместо FRAME START/END используем:

N preceding / N following — указывает на N-ую группу относительно текущей;
unbounded preceding / following — указывает на границу секции, работает как у rows фрейма.
current row — указывает на текущую группу

Приведу несколько примеров с групповыми фреймами.

GROUPS BETWEEN CURRENT ROW AND CURRENT ROW

Устанавливает размер фрейма от начала текущей секции и до конца текущей секции

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    ORDER BY grades.subject
    GROUPS BETWEEN CURRENT ROW AND CURRENT ROW
)
ORDER BY subject

GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING

Устанавливает размер фрейма от начала текущей секции и до следующей секции

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    ORDER BY grades.subject
    GROUPS BETWEEN CURRENT ROW AND 1 FOLLOWING
)
ORDER BY subject

Здесь первый фреим - это строки от 1 до 6 (группы английский и информатика), второй - от 4 до 9 (информатика и математика), а третий - от 7 до 9 (математика). Сумму grade можете проверить сами.

GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW

Устанавливает размер фрейма от предыдущей группы до текущей группы

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    ORDER BY grades.subject
    GROUPS BETWEEN 1 PRECEDING AND CURRENT ROW
)
ORDER BY subject

Здесь первый фрейм - это строки от 1 до 3, второй фреим - от 1 до 6, а третий фреим - от 4 до 9.

GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

Устанавливает фреим от начала и до конца секции

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    ORDER BY grades.subject
    GROUPS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
ORDER BY subject

Здесь всё просто, секция - это все наши записи, поэтому здесь записи захватятся в единственный фреим.

GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Устанавливает фреим от начала текущей секции и до текущей группы

SELECT 
    grades.id, 
    grades.name,
    grades.subject,
    grades.grade,
    sum(grades.grade) OVER w AS sum
FROM grades
WINDOW w AS (
    ORDER BY grades.subject
    GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
ORDER BY subject

Получаются следующие фреймы:

Первый фреим - это записи от 1 до 3 (5+4+3=12)
Второй фреим - это записи от 1 до 6 (5+4+3+5+5+5=27)
Третий фреим - это записи от 1 до 9 (5+4+3+5+5+5+3+4+4=38)

Диапазонные¶

Фреим по диапазону оперирует группами строк, у которых значение из ORDER BY попадает в указанный фреймом диапазон.

Описываются следующим образом:

RANGE BETWEEN <FRAME_START> AND <FRAME_END>

Например, возьмём вот такое окно:

WINDOW w AS (
  ORDER BY products.cost
  RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING
)

Это значит, что в диапазон попадёт стоимость продуктов от X-5 до X+10 включительно. Например, для cost = 20, в диапазон попадут значения от 15 до 30 включительно.

У диапазонных фреймов несколько особенностей:

В ORDER BY можно засунуть только 1 столбец
Для preceding / following можно использовать только числа и даты
current row работает точно так же, как у и groups-фрейма - то есть он включает одинаковые строки по group by.
unbounded preceding и unbounded following работают без изменений - они всё так же указывают на границы секций.

Для большего понимания предлагаю взглянуть на примеры.

RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING

Здесь указан фреим, который будет совпадать с диапазоном цен от x-5 до x+5 включительно.

SELECT 
    products.id,
    products.name,
    products.type,
    products.cost,
    sum(products.cost) OVER w AS sum,
    count(*) OVER w as cnt -- считаем количество записей, которые попали во фреим
FROM products
WINDOW w AS (
    ORDER BY products.cost
    RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING
)
ORDER BY products.cost

Давайте проверять.
Для первой записи в фреим попадут цены от 0 до 10, в окне у нас будут записи 1, 2, 3. Их сумма составляет 20 (5+5+10)
Для второй записи в фреим и сумма точно такие же как и для первой.
Для третьей записи в фреим попадут цены от 5 до 15, в окне у нас будут записи 1, 2, 3. Их сумма составляет 20 (5+5+10)
Для четвертой записи в фреим попадут цены от 15 до 25, в окне у нас будут записи 4 и 5. Их сумма составит 40 (20+20)
Для пятой записи фреим и сумма точно такие же как и для четвертой.
Для шестой записи в фреим попадут цены от 25 до 35. У нас всего одна такая запись, под номером 6.

RANGE BETWEEN CURRENT ROW AND CURRENT ROW

Этот фреим указывает на то, что в него попадут значения совпадающие с текущим.

SELECT 
    products.id,
    products.name,
    products.type,
    products.cost,
    sum(products.cost) OVER w AS sum,
    count(*) OVER w as cnt
FROM products
WINDOW w AS (
    ORDER BY products.cost
    RANGE BETWEEN CURRENT ROW AND CURRENT ROW
)
ORDER BY products.cost

RANGE BETWEEN 20 PRECEDING AND 10 PRECEDING

Этот фреим указывает на то, что в него попадут значения от -20 до -10 относительно текущей стоимости.

SELECT 
    products.id,
    products.name,
    products.type,
    products.cost,
    sum(products.cost) OVER w AS sum,
    count(*) OVER w as cnt
FROM products
WINDOW w AS (
    ORDER BY products.cost
    RANGE BETWEEN 20 PRECEDING AND 10 PRECEDING
)
ORDER BY products.cost

А теперь разбираемся, какие записи включились в фреймы.

Для четвертой и пятой записи сумма будет равна 20, так как в неё попадают записи от 0 (20-20) до 10 (20-10), а именно цены из первой, второй и третьей записи (5+5+10).
Для шестой записи сумма будет равна 50, так как в неё попадают записи от 10 (30-20), до 20 (30-10), а именно цены из третьей, четвёртой и пятой записи (10+10+20).
И так далее.

Исключение записей¶

В фрейме есть возможность исключать записи:

NO OTHERS - вариант по умолчанию, ничего не исключаем
CURRENT ROW - исключает текущую запись. Важно - он исключает ТЕКУЩУЮ запись, вне зависимости от типа фрейма.
GROUP - исключить текущую запись и все, равные её по ORDER BY. Важно - он исключает всю ГРУППУ, вне зависимости от типа фрейма.
TIES - оставляет текущую запись, но исключает все записи, которые будут равны текущей

Функции¶

Ранжирование¶

Функции ранжирования помогают ранжировать записи по какому-то конкретному правилу. Работают с order by (обязательно) и partition by (не обязательно), для них не имеет смысл указание фрейма.

Функция	Описание
row_number	Проставляет порядковый номер для каждой строки
rank	Возвращает ранг строки - вычисляется 1 + количество рангов, находящиеся до этой строк
dense_rank	Работает точно так же как и rank, но не пропускает строки
ntile	Разбивает все записи на N групп и возвращает номер группы для каждой записи.

Смещение¶

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

Функция	Описание
lag	Возвращает значение из n-ой строки назад
lead	Возвращает значение из n-ой строки вперёд
first_value	Возвращает первое значение из фрейма
last_value	Возвращает последнее значение из фрейма
nth_value	Возвращает значение из n-ой строки в фрейме

Агрегация¶

Агрегация используется для рассчета суммы, среднего и т.д. Здесь можно использовать как секции, так и сортировку и фреймы. Указание сортировки превращает агрегат в скользящий.

Функция	Описание
min	Возвращает минимальное значение из фрейма
max	Возвращает максимальное значение из секции или фрейма
count	Возвращает количество записей из секции или фрейма
avg	Рассчитывает среднее по секции или фрейму
sum	Рассчитывает сумму всех значений по секции или фрейму

Ссылки¶

Оконные функции SQL - Антон Жиянов - Если хотите практики и более детального разбора, очень рекомендую пройти этот курс.
SQLime - онлаин SQLite плейграунд