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

Оконные функции — функции, которые выполняют вычисления внутри заданного набора данных или "окна" в таблице БД.

Принцип работы

1. Оконная функция (ОФ) определяет "окно" данных, на которых будет выполняться вычисление (несколько строк или диапазон значений столбца).
2. Если требуется, данные внутри окна упорядочиваются по одному или нескольким столбцам.
3. Применяются вычисления, заданные в ОФ (сумма, среднее значение, ранжирование и т.д.).
4. Если требуется, результаты группируются по столбцам или выражениям.
5. Возврат результатов.

Упрощенный синтаксис

<имя функции> OVER (<окно>)

где имя функции = имя оконной функции, окно = выражение, описывающее набор строк для обработки и порядок обработки.

Характеристики

• В отличие от агрегатных функций, оконные могут вычислять значения для каждой строки в результате запроса. Агрегатные работают над всем набором данных.
• ОФ не уменьшают количество строк, а возвращают столько значений, сколько получили на вход.
• В отличие от GROUP BY, OVER может обращаться к другим строкам.

Как используются?

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

Виды

• Агрегатные — вычисляют агрегированные значения по группам строк в окне.
  • Пример: среднее значение или сумма всех значений в окне.
• Ранжирующие — назначают ранг каждой строке в пределах окна в соответствии с заданными критериями сортировки.
  • Пример: можно ранжировать товары по их цене в каждой категории.
  • ROW_NUMBER — возвращает номер строки, используется для нумерации.
  • RANK — возвращает ранг каждой строки.
• Смещения — позволяют перемещаться и обращаться к разным строкам в окне относительно текущей строки.
  • LAG — обращается к данным из предыдущих строк окна.
  • LEAD — обращается к данным из следующих строк. Аналогично LAG имеет 3 аргумента.

Примеры

Ранжирование данных

SELECT student_id, test_score,  
  RANK() OVER (ORDER BY test_score DESC) AS rank  
  FROM student_scores;

Вычисление среднего значения по группам

SELECT region, sales_amount,  
  AVG(sales_amount) OVER (PARTITION BY region) AS avg_sales_per_region  
  FROM sales_data;

Окно vs Партиция в оконных функциях

Окно

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

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

Партиция (PARTITION BY)

Подмножество строк, выделенное для ОФ по одному или нескольким столбцам в таблице. Задает условие, по которому эти строки группируются для вычисления функций.

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

Окно определяет, где выполняется функция, а партиция - как данные группируются для выполнения этой функции.

Недостатки оконных функций

• Не все СУБД их поддерживают.
• Производительность может ухудшаться из-за их влияния на выполнение запроса.
• Отладка может быть затруднительна.

Подборка материалов по оконным функциям

1. Оконные функции SQL простым языком с примерами
2. Оконные функции
3. Учимся применять оконные функции
4. Оконные функции в SQL — что это и зачем они нужны
5. Оконные функции с примерами
6. Clickhouse — оконные функции, которых нет
7. Партиции в оконных функциях
8. Зачем вам оконные функции в Apache Spark
9. Python Pandas — оконные функции
10. Оконные функции в SQL и их родственники в Python
11. Оконные функции SQL
12. Оконные функции в Postgres
13. Группировки и оконные функции в Oracle
14. Магия оконных функций (на примере PostgreSQL)
15. Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL
16. Оконные функции: сравнение
17. Оконные функции: ограничения, синтаксис

Видео

1. Оконные функции SQL за 13 минут
2. Как применять оконные функции в SQL и Pandas | Анатолий Карпов
3. Оконные функции RANK и LAG | Анатолий Карпов
4. Цикл видео "Расширенные возможности SQL" (границы ОФ, агрегатные ОФ, аналитические ОФ, ранжирующие ОФ)

Книги

1. Оконные функции в T-SQL — Ицик Бен-Ган
2. PostgreSQL. Основы языка SQL — Е.П. Моргунов
3. SQL для чайников — Аллен Тейлор
4. SQL. Сборник рецептов — М. Энтони, де Грааф Роберт
5. Изучаем SQL. — А. Болье
6. SQL для анализа данных — К. Танимура