stringtranslate.com

Оконная функция (SQL)

В SQL оконная функция или аналитическая функция [1] — это функция, которая использует значения из одной или нескольких строк для возврата значения для каждой строки. (Это контрастирует с агрегатной функцией , которая возвращает одно значение для нескольких строк.) Оконные функции имеют предложение OVER; любая функция без предложения OVER не является оконной функцией, а скорее агрегатной или однострочной (скалярной) функцией. [2]

Пример

В качестве примера приведем запрос, который использует оконную функцию для сравнения зарплаты каждого сотрудника со средней зарплатой его отдела (пример из документации PostgreSQL ): [3]

ВЫБРАТЬ depname , empno , salary , avg ( зарплата ) НАД ( РАЗДЕЛЕНИЕ ПО depname ) ИЗ empsalary ;

Выход:

depname | empno | salary | avg ----------+-------+--------+----------------------разработать | 11 | 5200 | 5020.00000000000000000разработать | 7 | 4200 | 5020.00000000000000000разработать | 9 | 4500 | 5020.00000000000000000разработать | 8 | 6000 | 5020.00000000000000000разработать | 10 | 5200 | 5020.00000000000000000персонал | 5 | 3500 | 3700.00000000000000000персонал | 2 | 3900 | 3700.00000000000000000продажи | 3 | 4800 | 4866.6666666666666667продажи | 1 | 5000 | 4866.6666666666666667продажи | 4 | 4800 | 4866.6666666666666667(10 рядов)

Предложение PARTITION BYгруппирует строки в разделы, и функция применяется к каждому разделу отдельно. Если PARTITION BYпредложение опущено (например, с пустым OVER()предложением), то весь набор результатов рассматривается как один раздел. [4] Для этого запроса средняя зарплата, указанная в отчете, будет средним значением, взятым по всем строкам.

Оконные функции оцениваются после агрегации ( GROUP BYнапример, после предложения и неоконных агрегатных функций). [1]

Синтаксис

Согласно документации PostgreSQL, оконная функция имеет один из следующих синтаксисов: [4]

имя_функции ([ выражение [, выражение ... ]]) OVER имя_окна имя_функции ([ выражение [, выражение ... ]]) OVER ( определение_окна ) имя_функции ( * ) OVER имя_окна имя_функции ( * ) OVER ( определение_окна )

где window_definitionесть синтаксис:

[ имя_существующего_окна ] [ выражение PARTITION BY [, ... ] ] [ выражение ORDER BY [ оператор ASC | DESC | USING ] [ NULLS { FIRST | LAST } ] [, ... ] ] [ предложение_рамки ]

frame_clauseимеет один из следующих синтаксисов:

{ ДИАПАЗОН | СТРОКИ | ГРУППЫ } начало_кадра [ исключение_кадра ] { ДИАПАЗОН | СТРОКИ | ГРУППЫ } МЕЖДУ началом_кадра И концом_кадра [ исключение_кадра ]

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]

 ИМЯ |------------ Аарон| <-- Предшествующий (неограниченный) Эндрю| Амелия| Джеймс| Джилл| Джонни| <-- 1-й предыдущий ряд Майкл| <-- Текущая строка Ник| <-- 1-й следующий ряд Офелия| Зак| <-- Последующий (неограниченный)

В приведенной выше таблице следующий запрос извлекает для каждой строки значения окна с одной предыдущей и одной последующей строкой:

 SELECT LAG ( имя , 1 ) OVER ( УПОРЯДОЧИТЬ ПО имени ) "предыдущий" , имя , LEAD ( имя , 1 ) OVER ( УПОРЯДОЧИТЬ ПО имени ) "следующий" FROM people УПОРЯДОЧИТЬ ПО имени

Результат запроса содержит следующие значения:

| ПРЕДЫДУЩАЯ | ИМЯ | СЛЕДУЮЩАЯ ||----------|----------|----------|| (null)| Аарон| Эндрю|| Аарон| Эндрю| Амелия|| Эндрю| Амелия| Джеймс|| Амелия| Джеймс| Джилл|| Джеймс| Джилл| Джонни|| Джилл| Джонни| Майкл|| Джонни| Майкл| Ник|| Майкл| Ник| Офелия|| Ник| Офелия| Зак|| Офелия| Зак| (null)|

История

Оконные функции были введены в стандарт SQL:2003 и имели расширенную функциональность в более поздних спецификациях. [11]

Была добавлена ​​поддержка для конкретных реализаций баз данных следующим образом:

Смотрите также

Ссылки

  1. ^ ab "Концепции аналитических функций в стандартном SQL | BigQuery". Google Cloud . Получено 2021-03-23 .
  2. ^ "Оконные функции". sqlite.org . Получено 2021-03-23 ​​.
  3. ^ "3.5. Оконные функции". Документация PostgreSQL . 2021-02-11 . Получено 2021-03-23 ​​.
  4. ^ ab "4.2. Выражения значений". Документация PostgreSQL . 2021-02-11 . Получено 2021-03-23 ​​.
  5. ^ Лейс, Виктор; Кундхиканджана, Кан; Кемпер, Альфонс; Нойманн, Томас (июнь 2015 г.). «Эффективная обработка оконных функций в аналитических SQL-запросах». Proc. VLDB Endow . 8 (10): 1058–1069. doi :10.14778/2794367.2794375. ISSN  2150-8097.
  6. ^ Cao, Yu; Chan, Chee-Yong; Li, Jie; Tan, Kian-Lee (июль 2012 г.). «Оптимизация аналитических оконных функций». Proc. VLDB Endow . 5 (11): 1244–1255. arXiv : 1208.0086 . doi : 10.14778/2350229.2350243. ISSN  2150-8097.
  7. ^ "Вероятно, самая крутая функция SQL: оконные функции". Java, SQL и jOOQ . 2013-11-03 . Получено 2017-09-26 .
  8. ^ "Оконные функции в SQL - Simple Talk". Simple Talk . 2013-10-31 . Получено 2017-09-26 .
  9. ^ "Введение в функции окна SQL". Apache Drill .
  10. ^ "PostgreSQL: Документация: Оконные функции". www.postgresql.org . Получено 2020-04-04 .
  11. ^ "Обзор функций окна". MariaDB KnowledgeBase . Получено 2021-03-23 .
  12. ^ "PostgreSQL Release 8.4". www.postgresql.org . 24 июля 2014 г. Получено 2024-03-10 .
  13. ^ "MySQL :: Что нового в MySQL 8.0? (Общедоступно)". dev.mysql.com . Получено 21.11.2022 .
  14. ^ "MySQL :: Справочное руководство MySQL 8.0 :: 12.21.2 Концепции и синтаксис оконных функций". dev.mysql.com .
  15. ^ "MariaDB 10.2.0 Release Notes". mariadb.com . Получено 2024-03-10 .