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

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

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

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, в порядке, определённом в самом предложении 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)

Так как в предложении OVER нет ORDER BY, фрейм окна будет таким же как и разбиение, что приведёт к тому, что 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);

Подробности об оконных функциях можно найти в Section 4.2.8, Section 9.19, Section 7.2.4 и на справочной странице SELECT.