SQL в PostgreSQL: оконные функции
Оконная функция выполняет вычисление по набору строк таблицы, которые каким-то образом связаны с текущей строкой. Это сравнимо с типом расчета, который можно выполнить с помощью агрегатной функции. Однако оконные функции не заставляют строки группироваться в одну выходную строку, как это было бы при не оконных агрегатных вызовах. Вместо этого строки сохраняют свои отдельные идентификаторы. За кулисами оконная функция может получить доступ не только к текущей строке результата запроса.
Вот пример, который показывает, как сравнить зарплату каждого сотрудника со средней зарплатой в его или ее отделе:
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)
Как показано здесь, функция ранжирования производит числовой ранг для каждого отдельного значения ORDER BY в разделе текущей строки, используя порядок, определенный предложением ORDER BY. rank не нуждается в явном параметре, потому что его поведение полностью определяется предложением OVER.
Строки, рассматриваемые оконной функцией, - это строки "виртуальной таблицы", созданной предложением FROM запроса, отфильтрованные его предложениями WHERE, GROUP BY и HAVING, если таковые имеются. Например, строка, удаленная из-за несоответствия условию WHERE, не отображается ни одной оконной функцией. Запрос может содержать несколько оконных функций, которые по-разному разрезают данные с использованием разных предложений OVER, но все они действуют в одном и том же наборе строк, определенных этой виртуальной таблицей.
Мы уже видели, что ORDER BY можно опустить, если порядок строк не важен. Также можно опустить PARTITION BY, и в этом случае будет один раздел, содержащий все строки.
Есть еще одна важная концепция, связанная с оконными функциями: для каждой строки есть набор строк в ее разделе, называемый ее оконным фреймом (window frame). Некоторые оконные функции действуют только на строки оконного фрейма, а не на весь раздел. По умолчанию, если указан ORDER BY, то фрейм состоит из всех строк от начала раздела до текущей строки, плюс любые последующие строки, которые равны текущей строке в соответствии с предложением ORDER BY. Если ORDER BY опущен, фрейм по умолчанию состоит из всех строк в разделе. Вот пример использования суммы:
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. Это потому, что они логически выполняются после обработки этих предложений. Кроме того, оконные функции выполняются после не оконных агрегатных функций. Это означает, что можно включать вызов агрегатной функции в аргументы оконной функции, но не наоборот.
Если есть необходимость отфильтровать или сгруппировать строки после выполнения вычислений окна, вы можете использовать sub-select. Например:
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);
Читайте также:
- SQL в PostgreSQL: представления (Views)
- SQL в PostgreSQL: внешние ключи (Foreign Keys)
- SQL в PostgreSQL: транзакции
Комментарии
Отправить комментарий