Окна функций

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

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

Примеры

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

SELECT row_number() OVER ()
FROM sales;

Сгенерировать столбец row_number по порядку времени:

SELECT row_number() OVER (ORDER BY time)
FROM sales;

Сгенерировать столбец row_number по порядку времени, разделяя по региону:

SELECT row_number() OVER (PARTITION BY region ORDER BY time)
FROM sales;

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

SELECT amount - lag(amount) OVER (ORDER BY time)
FROM sales;

Вычислить процент от общей суммы продаж по каждому региону для каждой строки:

SELECT amount / sum(amount) OVER (PARTITION BY region)
FROM sales;

Синтаксис

Функции окон могут быть использованы только в SELECT предложении. Для совместноиспользования OVER спецификаций между функциями, используйте WINDOW предложение и используйте OVER ⟨window-name⟩ синтаксис.

Функции окон общего назначения

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

cume_dist()

dense_rank()

first_value(expr[ IGNORE NULLS])

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

last_value(expr[ IGNORE NULLS])

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

nth_value(expr, nth[ IGNORE NULLS])

ntile(num_buckets)

percent_rank()

rank_dense()

rank()

row_number()

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

Все агрегатные функции могут использоваться в контексте окон, включая необязательную FILTER. Функции first и last затенены соответствующими универсальными оконными функциями, с незначительным последствием, что FILTER недоступно для них, но IGNORE NULLS есть.

Пропуски (Nulls)

Все универсальные оконные функции, которые принимают IGNORE NULLS, по умолчанию учитывают пропуски. Это поведение по умолчанию можно дополнительно сделать явным с помощью RESPECT NULLS.

В отличие от этого, все агрегатные оконные функции (за исключением list и её псевдонимов, которые можно настроить на игнорирование пропусков с помощью FILTER) игнорируют пропуски и не принимают RESPECT NULLS. Например, sum(column) OVER (ORDER BY time) AS cumulativeColumn вычисляет кумулятивную сумму, где строки со значением NULL column имеют то же значение cumulativeColumn , что и предшествующая им строка.

Вычисление

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

Полный синтаксис показан на диаграмме в верхней части страницы, и эта диаграмма визуально иллюстрирует вычислительную среду:

Разделение и Сортировка

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

Сортировка также необязательна, но без неё результаты не определены. Каждый раздел сортируется с использованием той же сортировки.

Ниже приведена таблица данных по выработке электроэнергии, доступная в формате CSV (power-plant-generation-history.csv). Для загрузки данных выполните:

CREATE TABLE "Generation History" AS
    FROM 'power-plant-generation-history.csv';

После разделения по заводу и сортировки по дате, она будет иметь такой вид:

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

Самая простая оконная функция — row_number(). Эта функция просто вычисляет номер строки в рамках раздела, начиная с 1, используя запрос:

SELECT
    "Plant",
    "Date",
    row_number() OVER (PARTITION BY "Plant" ORDER BY "Date") AS "Row"
FROM "Generation History"
ORDER BY 1, 2;

Результат будет следующим:

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

Кадрирование

Кадрирование определяет набор строк относительно каждой строки, где функция оценивается. Расстояние от текущей строки даётся как выражение, либо PRECEDING или FOLLOWING текущей строки. Это расстояние может быть задано как целое число строк или как выражение смещения диапазона от значения выражения упорядочивания. Для спецификации RANGE должно быть только одно выражение упорядочивания, и оно должно поддерживать сложение и вычитание (т.е. числа или INTERVAL). Значения по умолчанию для кадров от UNBOUNDED PRECEDING до CURRENT ROW. Недопустимо, чтобы кадр начинался позже, чем заканчивается. С помощью EXCLUDE строки вокруг текущей строки можно исключить из кадра.

Кадрирование строк

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

SELECT points,
    sum(points) OVER (
        ROWS BETWEEN 1 PRECEDING
                 AND 1 FOLLOWING) we
FROM results;

Этот запрос вычисляет sum каждой точки и точек по обе стороны от неё:

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

Кадрирование диапазона

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

SELECT "Plant", "Date",
    avg("MWh") OVER (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING)
        AS "MWh 7-day Moving Average"
FROM "Generation History"
ORDER BY 1, 2;

Этот запрос разделяет данные по Plant (чтобы разделять данные разных электростанций), упорядочивает каждое разбиение электростанции по Date (чтобы разместить измерения энергии рядом друг с другом) и использует RANGE кадр из трёх дней по обе стороны от каждого дня для avg (чтобы обработать любые пропущенные дни). Вот результат:

EXCLUDE Оператор

Оператор EXCLUDE позволяет исключать строки вокруг текущей строки из кадра. У него есть следующие параметры:

• EXCLUDE NO OTHERS: ничего не исключать (по умолчанию)
• EXCLUDE CURRENT ROW: исключить текущую строку из кадра
• EXCLUDE GROUP: исключить текущую строку и все её аналоги (согласно столбцам, указанным в ORDER BY ) из кадра
• EXCLUDE TIES: исключить только аналоги текущей строки из кадра

WINDOW Операторы

Несколько разных OVER операторов могут быть указаны в одном SELECT, и каждый из них будет вычислен отдельно. Однако часто нам нужно использовать один и тот же макет для нескольких оконных функций. Оператор WINDOW позволяет определить именованное окно, которое можно использовать совместно между несколькими оконными функциями:

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum"
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;

Три оконные функции также будут использовать один и тот же макет данных, что улучшит производительность.

Несколько окон можно определить в одном операторе WINDOW с помощью запятой между ними:

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    avg("MWh") OVER seven AS "MWh 7-day Moving Average",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
    min("MWh") OVER three AS "MWh 3-day Moving Minimum",
    avg("MWh") OVER three AS "MWh 3-day Moving Average",
    max("MWh") OVER three AS "MWh 3-day Moving Maximum"
FROM "Generation History"
WINDOW
    seven AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
                  AND INTERVAL 3 DAYS FOLLOWING),
    three AS (
        PARTITION BY "Plant"
        ORDER BY "Date" ASC
        RANGE BETWEEN INTERVAL 1 DAYS PRECEDING
        AND INTERVAL 1 DAYS FOLLOWING)
ORDER BY 1, 2;

В представленных запросах не используется ряд операторов, обычно встречающихся в операторах SELECT, например, WHERE, GROUP BY, и т.д. Для более сложных запросов вы можете узнать, где операторы WINDOW находятся в стандартном порядке операторов SELECT statement.

Фильтрация результатов оконных функций с помощью QUALIFY

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

Запросы с ящиковой диаграммой

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

SELECT "Plant", "Date",
    min("MWh") OVER seven AS "MWh 7-day Moving Minimum",
    quantile_cont("MWh", [0.25, 0.5, 0.75]) OVER seven
        AS "MWh 7-day Moving IQR",
    max("MWh") OVER seven AS "MWh 7-day Moving Maximum",
FROM "Generation History"
WINDOW seven AS (
    PARTITION BY "Plant"
    ORDER BY "Date" ASC
    RANGE BETWEEN INTERVAL 3 DAYS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;