Урок 7.3 · Время чтения: ~8 мин

Рамка окна определяет, какие строки вокруг текущей реально использует агрегатная оконная функция: все строки от начала, три последние строки или последние семь дней. В этом уроке вы изучите синтаксис рамок ROWS и RANGE, построите нарастающие итоги и скользящие средние, увидите на реальных данных, почему «последние 3 строки» и «последние 3 дня» дают разные ответы, и избежите ловушки рамки по умолчанию.

Рамки окна в SQL: ROWS и RANGE

PARTITION BY делит строки на группы, а ORDER BY упорядочивает их. Рамка — третья часть OVER: внутри текущего раздела она выбирает скользящий диапазон строк относительно текущей. Агрегатные функции (SUM, AVG, COUNT, MIN, MAX) и функции значений (FIRST_VALUE, LAST_VALUE) учитывают рамку; функции ранжирования и LAG/LEAD её игнорируют.

Примеры работают на учебной базе Sakila (MySQL 8.0).

Синтаксис рамки

function_name(expression) OVER (
    PARTITION BY partition_columns
    ORDER BY sort_column
    { ROWS | RANGE } BETWEEN frame_start AND frame_end
)

• ROWS считает физические строки: 2 PRECEDING — это две строки над текущей, какими бы ни были их значения.
• RANGE работает со значениями столбца из ORDER BY: INTERVAL 2 DAY PRECEDING — это все строки, дата которых не более чем на два дня раньше. Рамке RANGE со смещением нужен ровно один числовой столбец или столбец с датой в ORDER BY.

Данные: выручка по дням

Во всех примерах используется выручка по дням с 26 июля по 23 августа 2005 года. Обратите внимание на пропуск: после 2 августа следующий день с платежами — 16 августа.

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT pay_day, revenue
FROM daily
ORDER BY pay_day;

Результат: 16 дней: с 26 июля по 2 августа и с 16 по 23 августа.

Нарастающий итог и остаток

UNBOUNDED PRECEDING AND CURRENT ROW накапливает сумму с первой строки; CURRENT ROW AND UNBOUNDED FOLLOWING суммирует то, что ещё впереди:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
    SUM(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS remaining_total
FROM daily
ORDER BY pay_day
LIMIT 3;

Результат: на 26 июля нарастающий итог равен 160.67, а остаток — выручке за весь период, 37971.56; на 28 июля — 5464.98 и 35084.38.

Нарастающий итог подробнее, включая итоги по группам и решение без оконных функций, разобран в уроке 12.7.

Скользящее среднее: 3 строки — это не 3 дня

Скользящее среднее сглаживает дневные колебания. Обычно сначала пишут «текущая строка и две перед ней» — рамку ROWS. Рамка RANGE с INTERVAL вместо этого берёт «текущий день и два календарных дня до него»:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS avg_3_rows,
    ROUND(AVG(revenue) OVER (ORDER BY pay_day
        RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW), 2) AS avg_3_days
FROM daily
ORDER BY pay_day;

Результат: 16 строк; два средних совпадают везде, кроме дней сразу после пропуска: 16 августа среднее по 3 строкам равно 1885.21, а по 3 дням — 111.77.

16 августа «последние 3 строки» — это 1 августа, 2 августа и 16 августа: две из них двухнедельной давности. «Последние 3 дня» содержат только само 16 августа, потому что 14 и 15 августа платежей не было. Когда в датах есть пропуски, используйте RANGE с INTERVAL для календарных окон или сначала присоедините данные к полному календарю (урок 6.6), чтобы у каждого дня была строка.

MariaDB не принимает INTERVAL в рамке. Там упорядочивайте по номеру дня и используйте числовое смещение — результат будет тем же: AVG(revenue) OVER (ORDER BY TO_DAYS(pay_day) RANGE BETWEEN 2 PRECEDING AND CURRENT ROW).

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

Рамка по умолчанию

Если в OVER есть ORDER BY, но нет рамки, база данных использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это нарастающее вычисление, и поскольку это RANGE, строки с одинаковым значением сортировки добавляются вместе, группой. Без ORDER BY рамкой по умолчанию становится весь раздел.

Два практических следствия:

• SUM(x) OVER (ORDER BY day) — это нарастающий итог, а не общий. Уберите ORDER BY, чтобы получить общий итог.
• Когда в столбце сортировки есть повторы, нарастающий итог прыгает сразу на всю группу равных строк. Урок 12.7 показывает это на двух платежах, сделанных в одну и ту же секунду. Пишите ROWS BETWEEN ... явно, когда рамка важна.

Имя для окна: предложение WINDOW

Когда несколько функций используют одно и то же окно, определите его один раз с помощью предложения WINDOW и ссылайтесь на него по имени:

WITH daily AS (
    SELECT DATE(payment_date) AS pay_day, SUM(amount) AS revenue
    FROM payment
    WHERE payment_date >= '2005-07-26'
      AND payment_date < '2005-08-24'
    GROUP BY DATE(payment_date)
)
SELECT
    pay_day,
    revenue,
    MIN(revenue) OVER last_3_days AS min_3_days,
    MAX(revenue) OVER last_3_days AS max_3_days,
    COUNT(*) OVER last_3_days AS days_in_window
FROM daily
WINDOW last_3_days AS (
    ORDER BY pay_day
    RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND CURRENT ROW
)
ORDER BY pay_day;

Результат: 16 строк; 16 августа days_in_window равно 1, 17 августа — 2, а с 18 августа снова 3.

Предложение WINDOW пишется после HAVING и перед ORDER BY. Его поддерживают MySQL 8.0, MariaDB 10.2+, PostgreSQL и SQLite, а SQL Server — начиная с версии 2022.

Чего нет в MySQL

В стандарте SQL есть также рамки GROUPS, которые считают группы равных значений, и параметр EXCLUDE. PostgreSQL и SQLite их поддерживают, MySQL и MariaDB — нет.

Ключевые выводы:

• Рамка выбирает строки вокруг текущей, которые использует агрегатная оконная функция: ROWS или RANGE BETWEEN start AND end.
• UNBOUNDED PRECEDING AND CURRENT ROW даёт нарастающий итог; n PRECEDING AND CURRENT ROW — скользящее окно.
• ROWS считает строки, RANGE сравнивает значения: при пропусках в датах «3 строки» и «3 дня» дают разные средние.
• С ORDER BY и без рамки по умолчанию используется RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW; пишите рамку явно, когда она важна.
• Предложение WINDOW даёт окну имя и позволяет нескольким функциям использовать его повторно.

Часто задаваемые вопросы

Чем ROWS отличается от RANGE в SQL?

ROWS считает физические строки: 2 PRECEDING означает две строки над текущей. RANGE сравнивает значения столбца из ORDER BY: INTERVAL 2 DAY PRECEDING означает все строки с датой в пределах двух дней до текущей, сколько бы таких строк ни было.

Как посчитать скользящее среднее в SQL?

Используйте AVG с рамкой: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) усредняет текущую строку и шесть перед ней. Если для некоторых дней нет строк, используйте RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW или сначала заполните пропуски календарём.

Какая рамка окна используется по умолчанию?

При ORDER BY в OVER и без рамки — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: нарастающее вычисление, в которое строки с равными значениями сортировки входят вместе. Без ORDER BY рамкой является весь раздел.

Работают ли рамки окна с ROW_NUMBER и RANK?

Нет. Функции ранжирования (ROW_NUMBER, RANK, DENSE_RANK, NTILE) и LAG/LEAD всегда работают со всем разделом и игнорируют рамку. Рамки влияют на агрегатные функции и на FIRST_VALUE, LAST_VALUE и NTH_VALUE.