SQL Academy
Премиум
Продвинутый SQL
Урок доступен в видеоформате 

Рамки окон, RANGE и ROWS

В контексте оконных функций SQL, «рамка окна» определяет подмножество строк, которые рассматриваются SQL-функцией при выполнении вычислений.

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

Рамка окна vs Партиции

Хотя термины "рамка окна" и "партиция" могут показаться схожими, они представляют разные концепции:

  • Партиция (PARTITION BY). Это деление всего набора результатов на непересекающиеся подмножества, где каждое подмножество содержит строки с одинаковыми значениями в одном или нескольких столбцах. Оконные функции применяются отдельно к каждой партиции, как если бы каждая из них была отдельным набором данных.

    Схема разбиения на партиции

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

    Например, если используется правило ROWS BETWEEN 1 PRECEDING AND CURRENT ROW, то для каждой строки рамка будет состоять из неё самой и одной предшествующей строки. Это как "подпартиция" внутри уже существующей партиции.

    Схема формирования рамок окна

    То есть:

    • Первая рамка у нас состоит только из 1-ой записи, потому что предыдущей записи нет. Одна единственная запись подаётся в агрегатную функцию AVG(price) и результат добавляется в поле avg_price.
    • Вторая рамка у нас уже содержит 1 и 2 запись, которые и отправляются в AVG(price) и возвращают (170 + 220) / 2 = 195.
    • Третья рамка у нас содержит 2 и 3 запись, результат (220 + 150) / 2 = 185.
    • и т.д.

Примечание о рамке окна без ROWS/RANGE

Если в определении оконной функции отсутствует ROWS/RANGE, тогда рамка окна по умолчанию зависит от наличия ORDER BY.

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

Если ORDER BY присутствует, по умолчанию применяется правило RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. В этом случае рамка окна начинается с первой строки и заканчивается текущей строкой, включая все строки с теми же значениями полей сортировки, что и у текущей строки.

Определение границ рамки окна

Используя синтаксис ROWS или RANGE, мы можем определить какая именно рамка окна будет передаваться в оконную функцию для вычисления значения для текущей строки.

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

MySQL 8.1
SELECT <оконная_функция>(<поле_таблицы>)
OVER (
      ...
      ROWS|RANGE BETWEEN <начало рамки> AND <конец рамки>
)

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

MySQL 8.1
... ROWS|RANGE BETWEEN 2 PRECEDING AND CURRENT ROW

Если мы хотим, чтобы в оконную функцию передавались текущая строка и все последующие, то синтаксис будет выглядеть так:

MySQL 8.1
... ROWS|RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING

Возможные определения границ рамки окна

  • UNBOUNDED PRECEDING, все строки, предшествующие текущей
  • N PRECEDING, N строк до текущей строки
  • CURRENT ROW, текущая строка
  • N FOLLOWING, N строк после текущей строки
  • UNBOUNDED FOLLOWING, все последующие строки

Схема определения границ рамки окна

Определение границ рамки окна

Отличие ROWS от RANGE

Для определения границ рамки используются ключевые слова ROWS и RANGE. Работают они по-разному:

ROWS

  • Основан на физических строках:

    При использовании ROWS определение рамки основывается на физическом положении строк относительно текущей строки. Например, 1 PRECEDING означает одну строку до текущей.

  • Точная граница:

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

Определение границ рамки с ROWS

RANGE

  • Основан на значениях:

    RANGE, в отличие от ROWS, определяет границы рамки на основе значений столбцов, упорядоченных в соответствии с ORDER BY в оконной функции.

  • Динамичность границ:

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

Определение границ рамки с RANGE

Давайте проверим, правильно ли вы поняли разницу между ROWS и RANGE:

Какое из следующих утверждений наилучшим образом описывает разницу между использованием ROWS и RANGE в контексте оконных функций SQL?

Редактировать на GitHub