Руководство по оконным функциям

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

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

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. Для rank не требуется явный параметр, поскольку его поведение полностью определяется предложением OVER.

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

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

Есть еще одна важная концепция, связанная с оконными функциями: для каждой строки в ее разделе есть набор строк, называемый кадром окна. Некоторые оконные функции действуют только на строки кадра окна, а не на весь раздел. По умолчанию, если задано ORDER BY, то кадр состоит из всех строк от начала раздела до текущей строки, а также любых последующих строк, которые равны текущей строке в соответствии с предложением ORDER BY. Если ORDER BY опущен, кадр по умолчанию состоит из всех строк в разделе¹. Вот пример использования 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;

Приведенный выше запрос показывает только строки из внутреннего запроса, имеющие ранг менее 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);