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

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

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 не указано, по умолчанию рамка включает все строки в секции. ^[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)

Выше, поскольку в предложении 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);

Более подробную информацию по оконным функциям можно найти в Раздел 4.2.8, Раздел 9.22, Раздел 7.2.5 и на странице SELECT.