Yandex Cloud
  • Сервисы
  • Решения
  • Почему Yandex Cloud
  • Сообщество
  • Тарифы
  • Документация
  • Связаться с нами
Подключиться
Language / Region
© 2022 ООО «Яндекс.Облако»
  • Оглавление
  • Начало работы
    • Обзор
    • Создание базы данных
    • Аутентификация
    • Интерфейс командной строки YDB CLI
    • Язык запросов YQL
    • YDB SDK
    • Самостоятельное развертывание
      • Обзор
      • Docker
      • Бинарный файл
      • Minikube
    • Полезные ссылки
    • Document API, совместимый с Amazon DynamoDB
      • Настройка инструментов AWS
      • Работа с данными через HTTP-интерфейс
      • Работа с AWS CLI
        • Обзор
        • Создание таблицы
        • Добавление данных в таблицу
        • Чтение данных из таблицы
        • Обновление данных
        • Выборка данных
        • Удаление созданных ресурсов
      • Работа с AWS SDK
        • Обзор
        • Создание таблицы
        • Загрузка данных в таблицу
        • Управление записями в таблице
          • Создание записи
          • Чтение записи
          • Обновление записи
          • Удаление записи
        • Поиск и извлечение данных
        • Удаление таблицы
  • Практические руководства
    • Развертывание веб-приложения
    • Разработка Slack-бота
    • Подключение к YDB из функции Yandex Cloud Functions на Python
    • Подключение к базе данных YDB из функции Yandex Cloud Functions на Node.js
    • Конвертация видео в GIF на Python
    • Разработка навыка Алисы и сайта с авторизацией
  • Концепции
    • Обзор
    • Термины и определения
    • Соединение с БД и аутентификация
    • Модель данных и схема
    • Режимы работы Serverless и Dedicated
    • Транзакции
    • Вторичные индексы
    • Time to Live (TTL)
    • Скан запросы
    • Ограничения базы данных
    • Кластер YDB
      • Обзор
      • Общая схема YDB
      • Дисковая подсистема кластера
    • Квоты и лимиты
  • Пошаговые инструкции
    • Обзор
    • Переименование
    • Использование плана запроса и AST
    • Чтение и запись данных
    • Работа со вторичными индексами
  • Рекомендации
    • Обзор
    • Выбор первичного ключа для максимальной производительности
    • Вторичные индексы
    • Постраничный вывод
    • Загрузка больших объемов данных
    • Использование таймаутов
  • Управление базами данных
    • Обзор
    • Облачная консоль управления
      • Обзор
      • Создание, изменение, удаление баз данных
      • Таблицы и директории
      • Управление доступом
    • Интерфейс командной строки YC CLI
    • Резервное копирование и восстановление
    • Диагностика
      • Обзор
      • Системные таблицы
      • Мониторинг
  • Правила тарификации
    • Обзор
    • Бессерверный режим
      • Правила тарификации для бессерверного режима
      • Стоимость запроса для YQL
      • Стоимость запроса для Document API
      • Стоимость запроса для специальных API
    • Режим с выделенными инстансами
  • HTTP API, совместимый с Amazon DynamoDB
    • Все методы
    • Actions
      • BatchGetItem
      • BatchWriteItem
      • CreateTable
      • DeleteItem
      • DeleteTable
      • DescribeTable
      • DescribeTimeToLive
      • GetItem
      • ListTables
      • PutItem
      • Query
      • Scan
      • TransactGetItems
      • TransactWriteItems
      • UpdateItem
      • UpdateTimeToLive
    • Общие ошибки
  • YQL
    • Обзор
    • Типы данных
      • Обзор
      • Простые
      • Опциональные
      • Контейнеры
      • Специальные
      • Преобразования типов
      • Текстовое представление типов данных
      • JSON
    • Синтаксис
      • Обзор
      • Лексическая структура
      • Выражения
      • ACTION
      • ALTER TABLE
      • CREATE TABLE
      • DECLARE
      • DELETE
      • DISCARD
      • DROP TABLE
      • GROUP BY
      • FLATTEN
      • INSERT
      • INTO RESULT
      • JOIN
      • PRAGMA
      • REPLACE
      • SELECT
      • UPDATE
      • UPSERT
      • VALUES
      • WINDOW
      • Неподдерживаемые конструкции
    • Встроенные функции
      • Обзор
      • Базовые
      • Агрегатные
      • Оконные
      • Для работы со списками
      • Для работы со словарями
      • Для работы со структурами
      • Для работы с типами
      • Для работы с JSON
      • Библиотеки C++
        • Обзор
        • Hyperscan
        • Pcre
        • Pire
        • Re2
        • String
        • Unicode
        • DateTime
        • Url
        • Ip
        • Yson
        • Digest
        • Math
        • Histogram
    • Туториал YQL
      • Обзор
      • Создание таблицы
      • Добавление данных в таблицу
      • Выборка данных из всех колонок
      • Выборка данных из определенных колонок
      • Сортировка и фильтрация
      • Агрегирование данных
      • Дополнительные условия выборки
      • Объединение таблиц с помощью JOIN
      • Вставка и модификация данных с помощью REPLACE
      • Вставка и модификация данных с помощью UPSERT
      • Вставка данных с помощью INSERT
      • Обновление данных с помощью UPDATE
      • Удаление данных
      • Добавление и удаление колонок
      • Удаление таблицы
  • Работа с YDB CLI
    • Обзор
    • Установка
    • Структура команд YDB CLI
    • Сервисные команды
    • Соединение с БД и аутентификация
    • Глобальные параметры
    • Работа со схемой БД
      • Список объектов
      • Информация об объекте
      • Директории
      • Вторичные индексы
      • Переименование таблиц
    • Работа с данными
      • Выполнение запроса к данным
      • План исполнения запроса
      • Потоковое чтение таблицы
      • Скан запросы
    • Загрузка и выгрузка данных
      • Обзор
      • Файловая структура выгрузки
      • Выгрузка в файловую систему
      • Загрузка из файловой системы
      • Соединение и аутентификация с S3
      • Выгрузка в S3
      • Загрузка из S3
      • Импорт данных из файла в существующую таблицу
    • Управление профилями
      • Обзор
      • Создание профиля
      • Использование профиля в запросах
      • Получение информации о профиле
      • Удаление профиля
      • Активированный профиль
    • Информационные сервисы
      • Список эндпоинтов
      • Проверка аутентификации
    • Нагрузочное тестирование
      • Обзор
      • Stock нагрузка
  • Работа с YDB SDK
    • Обзор
    • Установка
    • Аутентификация
    • Тестовое приложение
      • Обзор
      • C++
      • C# (.NET)
      • Go
      • Java
      • Node.js
      • PHP
      • Python
      • Архив
        • Go v1
        • Go v2
    • Обработка ошибок в API
    • Рецепты кода
      • Обзор
      • Аутентификация
        • Обзор
        • С помощью токена
        • Анонимная
        • Файл сервисного аккаунта
        • Сервис метаданных
        • С помощью переменных окружения
        • С помощью логина и пароля
      • Балансировка
        • Обзор
        • Равномерный случайный выбор
        • Предпочитать ближайший дата-центр
        • Предпочитать зону доступности
      • Выполнение повторных запросов
      • Диагностика проблем
        • Обзор
        • Включить логирование
        • Подключить метрики в Prometheus
        • Подключить трассировку в Jaeger
      • Установить размер пула сессий
      • Вставка данных
      • Пакетная вставка данных
  • Управление кластером
    • Обзор
    • Kubernetes
      • Обзор
      • Развертывание в Yandex Managed Service for Kubernetes
      • Развертывание в AWS Elastic Kubernetes Service
      • Эксплуатация
    • Вручную
      • Локальная установка
      • Конфигурация кластера
      • Обслуживание дисковой подсистемы кластера
        • Обзор
        • Способы предотвращения выхода за модель отказа
        • Балансировка нагрузки на диски
        • Способы освобождения места на физических устройствах
        • Расширение кластера
        • Добавление групп хранения
        • Безопасные рестарт и выключение узлов
        • Включение/выключение SelfHeal
        • Включение/выключение Scrubbing
        • Перевоз VDisk'ов
        • Изменение конфигураций через CMS
        • Изменение конфигурации акторсистемы
    • Встроенный UI
      • Обзор
      • YDB Monitoring
      • Hive web-viewer
      • Обзор соединений
      • Логи
      • Графики
    • Системные таблицы
  • Вопросы и ответы
    • Обзор
    • Общие вопросы
    • Ошибки
    • YQL
    • Serverless
    • Все вопросы на одной странице
  • Публичные материалы
  1. YQL
  2. Синтаксис
  3. WINDOW

OVER, PARTITION BY и WINDOW

Статья создана
Yandex.Cloud
  • Синтаксис
  • Алгоритм вычисления
    • Разбиение
    • Рамка
  • Особенности реализации

Механизм оконных функций, появившийся в стандарте SQL:2003 и расширенный в стандарте SQL:2011, позволяет выполнять вычисления над набором строк таблицы, который некоторым образом соотносится с текущей строкой.

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

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

Синтаксис

Общий синтаксис вызова оконной функции имеет вид

function_name([expression [, expression ...]]) OVER (window_definition)
или
function_name([expression [, expression ...]]) OVER window_name

Здесь window_name (имя окна) – произвольный идентификатор, уникальный в рамках запроса, expression – произвольное выражение не содержащее вызова оконных функций.

В запросе каждому имени окна должно быть сопоставлено определение окна (window_definition):

SELECT
    F0(...) OVER (window_definition_0),
    F1(...) OVER w1,
    F2(...) OVER w2,
    ...
FROM my_table
WINDOW
    w1 AS (window_definition_1),
    ...
    w2 AS (window_definition_2)
;

Здесь window_definition записывается в виде

[ PARTITION BY (expression AS column_identifier | column_identifier) [, ...] ]
[ ORDER BY expression [ASC | DESC] ]
[ frame_definition ]

Необязательное определение рамки (frame_definition) может быть задано одним из двух способов:

  • ROWS frame_begin
  • ROWS BETWEEN frame_begin AND frame_end

Начало рамки (frame_begin) и конец рамки (frame_end) задаются одним из следующих способов:

  • UNBOUNDED PRECEDING
  • offset PRECEDING
  • CURRENT ROW
  • offset FOLLOWING
  • UNBOUNDED FOLLOWING

Здесь смещение рамки (offset) – неотрицательный числовой литерал. Если конец рамки не задан, то подразумевается CURRENT ROW.

Все выражения внутри определения окна не должны содержать вызовов оконных функций.

Алгоритм вычисления

Разбиение

Указание PARTITION BY группирует строки исходной таблицы в разделы, которые затем обрабатываются независимо друг от друга.
Если PARTITION BY не указан, то все строки исходной таблицы попадают в один раздел. Указание ORDER BY определяет порядок строк в разделе.
В PARTITION BY, как и в GROUP BY можно использовать алиасы и SessionWindow.

При отсутствии ORDER BY порядок строк в разделе не определен.

Рамка

Определение рамки frame_definition задает множество строк раздела, попадающих в рамку окна связанную с текущей строкой.

В режиме ROWS (в YQL пока поддерживается только он) в рамку окна попадают строки с указанными смещениями относительно текущей строки раздела.

  • Например, для ROWS BETWEEN 3 PRECEDING AND 5 FOLLOWING в рамку окна попадут три строки перед текущей, текущая строка и пять строк после текущей строки.

Множество строк в рамке окна может меняться в зависимости от того, какая строка является текущей.

  • Например, для первой строки раздела в рамку окна ROWS BETWEEN 3 PRECEDING AND 1 PRECEDING не попадет ни одной строки.

Указание UNBOUNDED PRECEDING в качестве начала рамки означает "от первой строки раздела", UNBOUNDED FOLLOWING в качестве конца рамки – "до последней строки раздела", CURRENT ROW – "от/до текущей строки".

Если определение_рамки не указано, то в множество строк попадающих в рамку окна определяется наличием ORDER BY в определении_окна.
А именно, при наличии ORDER BY неявно подразумевается ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, а при отсутствии – ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

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

Список доступных оконных функций

Примеры:

SELECT
    COUNT(*) OVER w AS rows_count_in_window,
    some_other_value -- доступ к текущей строке
FROM `my_table`
WINDOW w AS (
    PARTITION BY partition_key_column
    ORDER BY int_column
);
SELECT
    LAG(my_column, 2) OVER w AS row_before_previous_one
FROM `my_table`
WINDOW w AS (
    PARTITION BY partition_key_column
);
SELECT
    -- AVG (как и все агрегатные функции, используемые в качестве оконных)
    -- вычисляется на рамке окна
    AVG(some_value) OVER w AS avg_of_prev_current_next,
    some_other_value -- доступ к текущей строке
FROM my_table
WINDOW w AS (
    PARTITION BY partition_key_column
    ORDER BY int_column
    ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
);
SELECT
    -- LAG не зависит от положения рамки окна
    LAG(my_column, 2) OVER w AS row_before_previous_one
FROM my_table
WINDOW w AS (
    PARTITION BY partition_key_column
    ORDER BY my_column
);

Особенности реализации

  • Функции, вычисляемые на рамке окна ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING либо ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, реализованы эффективно (не требуют дополнительной памяти и вычисляются на разделе за O(размер раздела)).

  • Для рамки окна ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING есть возможность выбрать стратегию выполнения в оперативной памяти, указав хинт COMPACT после ключевого слова PARTITION.

    Например: PARTITION COMPACT BY key или PARTITION COMPACT BY () (в случае если PARTITION BY изначально отсутствовал).

    При наличии хинта COMPACT потребуется дополнительная память в размере O(размер раздела), но при этом не возникнет дополнительной JOIN операции.

  • Если рамка окна не начинается с UNBOUNDED PRECEDING, то для вычисления оконных функций на таком окне потребуется дополнительная память в размере O(максимальное расстояние от границ окна до текущей строки), а время вычисления будет равно O(число_строк_в_разделе * размер_окна).

  • Для рамки окна, начинающейся с UNBOUNDED PRECEDING и заканчивающейся на N, где N не равен CURRENT ROW или UNBOUNDED FOLLOWING, потребуется дополнительная память в размере O(N), а время вычисления будет O(N * число_строк_в_разделе).

  • Функции LEAD(expr, N) и LAG(expr, N) всегда потребуют O(N) памяти.

Учитывая вышесказанное, запрос с ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING по возможности стоит переделать в ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, поменяв порядок сортировки в ORDER BY на обратный.

Была ли статья полезна?

Language / Region
© 2022 ООО «Яндекс.Облако»
В этой статье:
  • Синтаксис
  • Алгоритм вычисления
  • Разбиение
  • Рамка
  • Особенности реализации