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

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

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 строк)

Первые три столбца вывода получены непосредственно из таблицы 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 значения в секции текущей строки, используя порядок, определенный ORDER BY предложением. rank не требует явного параметра, так как её поведение полностью определяется OVER предложением.

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

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

С оконными функциями связано еще одно важное понятие: для каждой строки в пределах ее секции определяется набор строк, называемый Рамка окна. Некоторые оконные функции обрабатывают только строки рамки окна, а не всей секции. По умолчанию, если ORDER BY задано, то рамка включает все строки от начала секции до текущей включительно, а также любые последующие строки, эквивалентные текущей согласно ORDER BY предложению. Если ORDER BY не указано, рамка по умолчанию включает все строки в секции. ^[5] Ниже приведен пример использования функции 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 представляет собой всю таблицу; иными словами, каждое значение sum вычисляется по всей таблице, поэтому для каждой строки результата получается одно и то же значение. Однако если добавить 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);

Подробные сведения об оконных функциях приведены в Раздел 2.1.2.8, Раздел 2.6.22, Раздел 2.4.2.5, а также на SELECT странице справочного руководства.