PostgreSQL - Window Functions

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

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

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)

Выше, так как ORDER BYв OVERпункте нет, рамка окна такая же, как и раздел, которым за неимением 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);