Оконная функция (SQL)
В SQL — оконная функция или аналитическая функция. [1] — это функция, которая использует значения из одной или нескольких строк для возврата значения для каждой строки. (Это контрастирует с агрегатной функцией , которая возвращает одно значение для нескольких строк.) Оконные функции имеют предложение OVER; любая функция без предложения OVER не является оконной функцией, а скорее агрегатной или однострочной (скалярной) функцией. [2]
Пример
[ редактировать ]В качестве примера приведем запрос, который использует оконную функцию для сравнения зарплаты каждого сотрудника со средней зарплатой его отдела (пример из документации PostgreSQL ): [3]
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)
The PARTITION BY В предложении строки группируются в разделы, и функция применяется к каждому разделу отдельно. Если PARTITION BY предложение опущено (например, с пустым OVER() предложение), то весь набор результатов рассматривается как один раздел. [4] Для этого запроса сообщаемая средняя зарплата будет средней по всем строкам.
Оконные функции оцениваются после агрегации (после GROUP BY например, предложение и неоконные агрегатные функции). [1]
Синтаксис
[ редактировать ]Согласно документации PostgreSQL, оконная функция имеет один из следующих синтаксисов: [4]
function_name ([expression [, expression ... ]]) OVER window_name
function_name ([expression [, expression ... ]]) OVER ( window_definition )
function_name ( * ) OVER window_name
function_name ( * ) OVER ( window_definition )
где window_definition имеет синтаксис:
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
frame_clause имеет синтаксис одного из следующих:
{ RANGE | ROWS | GROUPS } frame_start [ frame_exclusion ]
{ RANGE | ROWS | GROUPS } BETWEEN frame_start AND frame_end [ frame_exclusion ]
frame_start и frame_end может быть UNBOUNDED PRECEDING, offset PRECEDING, CURRENT ROW, offset FOLLOWING, или UNBOUNDED FOLLOWING. frame_exclusion может быть EXCLUDE CURRENT ROW, EXCLUDE GROUP, EXCLUDE TIES, или EXCLUDE NO OTHERS.
expression относится к любому выражению, которое не содержит вызова оконной функции.
Обозначение:
- Скобки [] обозначают необязательные предложения.
- Фигурные скобки {} обозначают набор различных возможных вариантов, каждый из которых ограничен вертикальной чертой |
Пример
[ редактировать ]Оконные функции позволяют получить доступ к данным в записях непосредственно до и после текущей записи. [5] [6] [7] [8] Оконная функция определяет рамку или окно строк заданной длины вокруг текущей строки и выполняет вычисления по набору данных в окне. [9] [10]
NAME |
------------
Aaron| <-- Preceding (unbounded)
Andrew|
Amelia|
James|
Jill|
Johnny| <-- 1st preceding row
Michael| <-- Current row
Nick| <-- 1st following row
Ophelia|
Zach| <-- Following (unbounded)
В приведенной выше таблице следующий запрос извлекает для каждой строки значения окна с одной предыдущей и одной следующей строкой:
SELECT
LAG(name, 1)
OVER(ORDER BY name) "prev",
name,
LEAD(name, 1)
OVER(ORDER BY name) "next"
FROM people
ORDER BY name
Результат запроса содержит следующие значения:
| PREV | NAME | NEXT | |----------|----------|----------| | (null)| Aaron| Andrew| | Aaron| Andrew| Amelia| | Andrew| Amelia| James| | Amelia| James| Jill| | James| Jill| Johnny| | Jill| Johnny| Michael| | Johnny| Michael| Nick| | Michael| Nick| Ophelia| | Nick| Ophelia| Zach| | Ophelia| Zach| (null)|
История
[ редактировать ]Оконные функции были представлены в стандарте SQL:2003 , а в более поздних спецификациях их функциональность была расширена. [11]
Поддержка определенных реализаций баз данных была добавлена следующим образом:
- PostgreSQL — версия 8.4 2009 года. [12]
- MySQL — версия 8 в 2018 году. [13] [14]
- MariaDB — версия 10.2 2016 года. [15]
См. также
[ редактировать ]Ссылки
[ редактировать ]- ^ Jump up to: а б «Концепции аналитических функций в стандартном SQL | BigQuery» . Гугл облако . Проверено 23 марта 2021 г.
- ^ «Оконные функции» . sqlite.org . Проверено 23 марта 2021 г.
- ^ «3.5. Оконные функции» . Документация PostgreSQL . 11 февраля 2021 г. Проверено 23 марта 2021 г.
- ^ Jump up to: а б «4.2. Выражения значений» . Документация PostgreSQL . 11 февраля 2021 г. Проверено 23 марта 2021 г.
- ^ Лейс, Виктор; Кундхиканджана, Кан; Кемпер, Альфонс; Нойманн, Томас (июнь 2015 г.). «Эффективная обработка оконных функций в аналитических SQL-запросах». Учеб. ВЛДБ Эндоу . 8 (10): 1058–1069. дои : 10.14778/2794367.2794375 . ISSN 2150-8097 .
- ^ Цао, Ю; Чан, Чи-Ён; Ли, Цзе; Тан, Киан-Ли (июль 2012 г.). «Оптимизация аналитических оконных функций». Учеб. ВЛДБ Эндоу . 5 (11): 1244–1255. arXiv : 1208.0086 . дои : 10.14778/2350229.2350243 . ISSN 2150-8097 .
- ^ «Наверное, самая крутая функция SQL: оконные функции» . Java, SQL и jOOQ . 03.11.2013 . Проверено 26 сентября 2017 г.
- ^ «Оконные функции в SQL — простой разговор» . Простой разговор . 31 октября 2013 г. Проверено 26 сентября 2017 г.
- ^ «Введение в оконные функции SQL» . Апач дрель .
- ^ «PostgreSQL: Документация: Оконные функции» . www.postgresql.org . Проверено 4 апреля 2020 г.
- ^ «Обзор оконных функций» . База знаний MariaDB . Проверено 23 марта 2021 г.
- ^ «PostgreSQL версии 8.4» . www.postgresql.org . 24 июля 2014 года . Проверено 10 марта 2024 г.
- ^ «MySQL :: Что нового в MySQL 8.0? (общедоступно)» . dev.mysql.com . Проверено 21 ноября 2022 г.
- ^ «MySQL :: Справочное руководство MySQL 8.0 :: 12.21.2 Концепции и синтаксис оконных функций» . dev.mysql.com .
- ^ «Примечания к выпуску MariaDB 10.2.0» . mariadb.com . Проверено 10 марта 2024 г.