Премиум
Продвинутый SQL

Рамки окон

В контексте оконных функций 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, тогда окно по умолчанию совпадает с партицией. В этом случае, оконная функция будет обрабатывать все строки внутри партиции, не ограничиваясь подмножеством. Это значит, что результат функции будет одинаков для всех строк внутри той же партиции.

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

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

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

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

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

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

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

MySQL
... 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 чётко ограничивает количество строк, которые включаются в окно, делая его предсказуемым и конкретным.

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

RANGE

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

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

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

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

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

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

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

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