Зачем нужны оконные функции sql

Оконные функции SQL

Нет более обманчивого раздела SQL, чем «оконные функции». Когда слышишь эти слова, думаешь «наверно, просто придумали какие-то дополнительные функции». А вот и нет! «Оконные функции» — это отдельный язык, встроенный в обычный SQL. И он сложнее, чем все, что вы знали о «селектах», вместе взятое.

Если вкратце — оконные функции помогают делать классные аналитические отчеты без участия «экселя» и pandas. Хотите посчитать процент продаж по месяцам от общих продаж за год? Оконные функции. Разделить маркетинговые каналы на эффективные и неэффективные? Оконные функции. Выбрать топ-10 клиентов по каждому сегменту? Тоже они.

Я прочитал несколько десятков статей «для начинающих», которые объясняли, что такое оконные функции. Все они страдали от одной из двух проблем:

1. Все понятно, но описано 10% возможностей «окошек».
2. Написано так сложно, что если бы я уже не знал предмет обсуждения — ничего бы не понял.

Я решил исправить ситуацию и подготовил интерактивный курс об оконных функциях SQL.

О курсе

Это понятное и наглядное введение в оконные функции. Понятное, потому что я умею доходчиво писать о сложных темах. Наглядное — потому что подготовил сотню картинок и пятьдесят упражнений, которые помогут глубоко понять «окошки».

Оконные функции — реально сложная тема. Поэтому курс не пытается объять необъятное, и объясняет их шаг за шагом. А еще на курсе много практики — потому что только с ней абстрактные знания превратятся в навыки.

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

Курс можно проходить в онлайн-песочнице, либо использовать любую из следующих СУБД:

• MySQL 8.0.2+ (MariaDB 10.2+)
• PostgreSQL 11+
• SQLite 3.28+
• MS SQL 2012+
• Oracle 11g+

Чему вы научитесь

Часть 1. Окна и функции

Часть 3. Практика

• Финансы
• Кластеризация
• Очистка данных

Подробное содержание

Введение

Зачем нужны оконные функции

Часть 1. Окна и функции

• Оконная функция
• Сортировка окна и сортировка результатов
• Однозначность сортировки
• Несколько окон
• Секции окна
• Группы
• Функции ранжирования

• Сравнение с соседями
• Сравнение с границами
• Окно, секция, фрейм
• Сравнение с границами, окончание
• Функции смещения

• Агрегат по секции
• Фильтрация и порядок выполнения
• Описание окна
• Функции агрегации

• Скользящее среднее
• Фрейм
• Сумма нарастающим итогом
• Фрейм по умолчанию
• Функции для скользящих агрегатов

• Кумулятивное распределение
• Относительный ранг
• Сводные значения
• Процентили в SQL
• Процентиль как оконная функция
• Статистические функции

Часть 2. Фреймы

• ROWS-фреймы
• GROUPS-фреймы

• RANGE-фреймы
• Особенности диапазонов
• Границы фрейма
• Фрейм по умолчанию
• Частые вопросы о фреймах

• Исключение строк
• Виды исключений

• Фильтрация строк
• CASE как альтернатива FILTER

Часть 3. Практика

• Сначала агрегация, затем окна
• Сначала окна, затем отсев
• Агрегация и null
• Выручка по тарифу gold
• Выручка по тарифам за 1 квартал
• Скользящее среднее по тарифу platinum
• Сравнение с декабрем
• Вклад тарифов
• Высокая, средняя и низкая выручка
• 2020 vs 2019
• Рейтинг месяцев по продажам

• Идентифицируем острова
• Острова с дублями
• Острова на датах
• Кластеры значений
• Кластеры на датах

• Дубли
• Пропуски
• Предыдущее непустое значение
• Выбросы

Курс начинается с основ «окошек» (часть 1), погружается в нюансы фреймов (часть 2) и заканчивает довольно продвинутыми приемами (часть 3).

Первые пять уроков (без задач) доступны по ссылкам в оглавлении выше.

Об авторе

Я Антон Жиянов, Python/Golang разработчик и энтузиаст SQLite. Работаю над опенсорсом, веду курсы и блог.

В 2021 я запустил курс по оконным функциям SQL. Сейчас у него больше 1000 выпускников и 340 отзывов со средним рейтингом 5 звезд.

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

3.5. Оконные функции

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

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

SELECT depname, empno, salary, avg(salary) OVER (PARTITION BY depname) FROM empsalary;

depname | empno | salary | avg -----------+-------+--------+----------------------- develop | 11 | 5200 | 5020.0000000000000000 develop | 7 | 4200 | 5020.0000000000000000 develop | 9 | 4500 | 5020.0000000000000000 develop | 8 | 6000 | 5020.0000000000000000 develop | 10 | 5200 | 5020.0000000000000000 personnel | 5 | 3500 | 3700.0000000000000000 personnel | 2 | 3900 | 3700.0000000000000000 sales | 3 | 4800 | 4866.6666666666666667 sales | 1 | 5000 | 4866.6666666666666667 sales | 4 | 4800 | 4866.6666666666666667 (10 rows)

Первые три колонки извлекаются непосредственно из таблицы empsalary, при этом для каждой строки таблицы есть строка результата. В четвёртой колонке оказалось среднее значение, вычисленное по всем строкам, имеющим то же значение depname, что и текущая строка. (Фактически среднее вычисляет та же функция avg , которую мы знаем как агрегатную, но предложение OVER превращает её в оконную, так что она обрабатывает лишь заданный набор строк.)

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

Вы можете также определять порядок, в котором строки будут обрабатываться оконными функциями, используя ORDER BY в OVER. (Порядок ORDER BY для окна может даже не совпадать с порядком, в котором выводятся строки.) Например:

SELECT depname, empno, salary, rank() OVER (PARTITION BY depname ORDER BY salary DESC) FROM empsalary;

depname | empno | salary | rank -----------+-------+--------+------ develop | 8 | 6000 | 1 develop | 10 | 5200 | 2 develop | 11 | 5200 | 2 develop | 9 | 4500 | 4 develop | 7 | 4200 | 5 personnel | 2 | 3900 | 1 personnel | 5 | 3500 | 2 sales | 1 | 5000 | 1 sales | 4 | 4800 | 2 sales | 3 | 4800 | 2 (10 rows)

Как показано здесь, функция rank выдаёт порядковый номер в разделе текущей строки для каждого уникального значения, по которому выполняет сортировку предложение ORDER BY. У функции rank нет параметров, так как её поведение полностью определяется предложением OVER.

Строки, обрабатываемые оконной функцией, представляют собой «виртуальные таблицы» , созданные из предложения FROM и затем прошедшие через фильтрацию и группировку WHERE и GROUP BY и, возможно, условие HAVING. Например, строка, отфильтрованная из-за нарушения условия WHERE, не будет видна для оконных функций. Запрос может содержать несколько оконных функций, разделяющих данные по-разному с помощью разных предложений OVER, но все они будут обрабатывать один и тот же набор строк этой виртуальной таблицы.

Мы уже видели, что ORDER BY можно опустить, если порядок строк не важен. Также возможно опустить PARTITION BY, в этом случае будет только один раздел, содержащий все строки.

Есть ещё одно важное понятие, связанное с оконными функциями: для каждой строки существует набор строк в её разделе, называемый рамкой окна. По умолчанию, с указанием ORDER BY рамка состоит из всех строк от начала раздела до текущей строки и строк, равных текущей по значению выражения ORDER BY. Без ORDER BY рамка по умолчанию состоит из всех строк раздела. [1] Посмотрите на пример использования sum :

SELECT salary, sum(salary) OVER () FROM empsalary;

salary | sum --------+------- 5200 | 47100 5000 | 47100 3500 | 47100 4800 | 47100 3900 | 47100 4200 | 47100 4500 | 47100 4800 | 47100 6000 | 47100 5200 | 47100 (10 rows)

Так как в этом примере нет указания ORDER BY в предложении OVER, рамка окна содержит все строки раздела, а он, в свою очередь, без предложения PARTITION BY включает все строки таблицы; другими словами, сумма вычисляется по всей таблице и мы получаем один результат для каждой строки результата. Но если мы добавим ORDER BY, мы получим совсем другие результаты:

SELECT salary, sum(salary) OVER (ORDER BY salary) FROM empsalary;

salary | sum --------+------- 3500 | 3500 3900 | 7400 4200 | 11600 4500 | 16100 4800 | 25700 4800 | 25700 5000 | 30700 5200 | 41100 5200 | 41100 6000 | 47100 (10 rows)

Здесь в сумме накапливаются зарплаты от первой (самой низкой) до текущей, включая повторяющиеся текущие значения (обратите внимание на результат в строках с одинаковой зарплатой).

Оконные функции разрешается использовать в запросе только в списке SELECT и предложении ORDER BY. Во всех остальных предложениях, включая GROUP BY, HAVING и WHERE, они запрещены. Это объясняется тем, что логически они выполняются после обычных агрегатных функций, а значит агрегатную функцию можно вызвать из оконной, но не наоборот.

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

SELECT depname, empno, salary, enroll_date FROM (SELECT depname, empno, salary, enroll_date, rank() OVER (PARTITION BY depname ORDER BY salary DESC, empno) AS pos FROM empsalary ) AS ss WHERE pos < 3;

Данный запрос покажет только те строки внутреннего запроса, у которых rank (порядковый номер) меньше 3.

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

SELECT sum(salary) OVER w, avg(salary) OVER w FROM empsalary WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);

Подробнее об оконных функциях можно узнать в Подразделе 4.2.8, Разделе 9.21, Подразделе 7.2.4 и в справке SELECT.

Примечания

Пред.	Начало	След.
Транзакции	Уровень выше	Наследование

Полезные оконные функции SQL

Можно бесконечно долго «воротить нос» от использования SQL для Data Preparation, отдавая лавры змеиному языку, но нельзя не признавать факт, что чаще мы используем и еще долго будем использовать SQL для работы с данными, в том числе и очень объемными.

40K открытий

Более того, считаем, что на текущий момент SQL окажется под рукой сотрудника с большей вероятностью, чем Python, и поможет быстро решить аналитическую задачку с приоритетом «-1».

Предложение OVER помогает «открыть окно», т.е. определить строки, с которым будет работать та или иная функция.

Предложение partion BY не является обязательным, но дополняет OVER и показывает, как именно мы разделяем строки, к которым будет применена функция.

ORDER BY определит порядок обработки строк.

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

Итак, оконные функции делятся на:

• Агрегатные функции
• Ранжирующие функции
• Функции смещения
• Аналитические функции

Собственно, те же, что и обычные, только встроенные в конструкцию с OVER

SUM/ AVG / COUNT/ MIN/ MAX

Для наглядности работы данных функций воспользуемся базовым набором данных (T)

Найти максимальную задолженность в каждом банке.

Для чего тут оконные функции? Можно же просто написать:

SELECT TB, max(OSZ) OSZ FROM T group by TB

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

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

SELECT TB, ID_CLIENT, ID_DOG, OSZ, PROCENT_RATE, RATING, SEGMENT , MAX(OSZ) OVER (PARTITION BY TB) 'Максимальная задолженность в разбивке по банкам' , AVG(PROCENT_RATE) OVER (PARTITION BY TB, SEGMENT) 'Средняя процентная ставка в разрезе банка и сегмента' , COUNT(ID_DOG) OVER () 'Всего договоров во всех банках' FROM T

На примере AVG(procent_RATE) OVER (partition BY TB, segment) подробнее:

• Мы применяем AVG – агрегатную функцию по подсчету среднего значения к столбцу procent_RATE.
• Затем предложением OVER определяем, что будем работать с некоторым набором строк. По умолчанию, если указать OVER() с пустыми строками, то этот набор строк равен всей таблице.
• Предложением partition BY выделяем разделы в наборе строк по заданному условию, в нашем случае, в разбивке на Территориальные банки и Сегмент.
• В итоге, к каждой строке базовой таблицы применится функция по подсчету среднего из набора строк, разбитых на разделы (по Территориальным Банкам и Сегменту).

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

Базовый набор данных: банки, отделы и количество ревизий.

Сами ранжирующие функции:

ROW_number – нумерует строки в результирующем наборе.

RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается с пропуском.

DENSE_RANK -присваивает ранг для каждой строки, если найдутся одинаковые значения, то следующий ранг присваивается без пропуска.

NTILE – помогает разделить результирующий набор на группы.

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

SELECT * , ROW_NUMBER() OVER(ORDER BY count_revisions desc) , Rank() OVER(ORDER BY count_revisions desc) , DENSE_RANK() OVER(ORDER BY count_revisions desc) , NTILE(3) OVER(ORDER BY count_revisions desc) FROM Table_Rev

ROW_number – пронумеровал столбцы в порядке убывания количества ревизий.

RANK – проранжировал отделы во всех банках в порядке убывания количества ревизий, но как только встретились одинаковые значения (количество ревизий 95), функция присвоила им ранг 4, а следующее значение получило ранг 6.

DENSE_RANK – аналогично RANK, но как только встретились одинаковые значения, следующее значение получило ранг 5.

NTILE – функция помогла разбить таблицу на 3 группы (указал в аргументе). Так как в таблице 18 значений, в каждую группу попало по 6.

Найти второй отдел во всех банках по количеству ревизий.

Можно, конечно, воспользоваться чем-то вроде:

SELECT MAX(count_revisions) ms FROM Table_Rev WHERE count_revisions!=(SELECT MAX(count_revisions) FROM Table_Rev)

Но если речь идет не про второй отдел, а про трети? .. уже сложнее. Действительно, никто не списывает со счетов offset, но в этой статье говорится об оконных функциях, так почему бы не написать так:

With T_R as ( SELECT * , DENSE_RANK() OVER(ORDER BY count_revisions desc) ds FROM Table_Rev ) SELECT * FROM T_R WHERE ds=3

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

With T_R as ( SELECT * , DENSE_RANK() OVER(PARTITION BY tb ORDER BY count_revisions) ds FROM Table_Rev ) SELECT tb,dep,count_revisions FROM T_R WHERE ds=1

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

LAG — смещение назад.

LEAD — смещение вперед.

FIRST_VALUE — найти первое значение набора данных.

LAST_VALUE — найти последнее значение набора данных.

LAG и LEAD имеют следующие аргументы:

• Столбец, значение которого необходимо вернуть
• На сколько строк выполнить смешение (дефолт =1)
• Что вставить, если вернулся NULL

Как обычно, на практике проще:

Базовый набор данных, содержит id задачи, события внутри нее и их дату:

Оконные функции – то, что должен знать каждый T-SQL программист. Часть 1.

Еще в Microsoft SQL Server 2005 появился интересный функционал – оконные функции. Это функции, которые позволяют осуществлять вычисления в заданном диапазоне строк внутри предложения Select. Для тех, кто не сталкивался с этими функциями возникает вопрос – «Что значит оконные?». Окно – значит набор строк, в рамках которого происходит вычисление. Оконная функция позволяет разбивать весь набор данных на такие окна.

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

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

Окно определяется с помощью инструкции OVER(). Давайте рассмотрим синтаксис этой инструкции:

Оконная функция (столбец для вычислений) OVER ([PARTITION BY столбец для группировки] [ORDER BY столбец для сортировки] [ROWS или RANGE выражение для ограничения строк в пределах группы])

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

Для демонстрации работы оконных функций предлагаю на тестовой таблице:

1 2 3 4 5 6 7

CREATE TABLE ForWindowFunc (ID INT, GroupId INT, Amount INT) GO INSERT INTO ForWindowFunc (ID, GroupId, Amount) VALUES(1, 1, 100), (1, 1, 200), (1, 2, 150), (2, 1, 100), (2, 1, 300), (2, 2, 200), (2, 2, 50), (3, 1, 150), (3, 2, 200), (3, 2, 10);