3.5. Оконные функции #
Оконная функция выполняет вычисления для набора строк, некоторым образом связанных с текущей строкой. Её действие можно сравнить с вычислением, производимым агрегатной функцией. Однако с оконными функциями строки не группируются в одну выходную строку, что имеет место с обычными, не оконными, агрегатными функциями. Вместо этого, эти строки остаются отдельными сущностями. Внутри же, оконная функция, как и агрегатная, может обращаться не только к текущей строке результата запроса.
Вот пример, показывающий, как сравнить зарплату каждого сотрудника со средней зарплатой его отдела:
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
рамка по умолчанию состоит из всех строк раздела. [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);
Подробнее об оконных функциях можно узнать в Подразделе 4.2.8, Разделе 9.22, Подразделе 7.2.5 и в справке SELECT.
[5] Рамки окна можно определять и другими способами, но в этом введении они не рассматриваются. Узнать о них подробнее вы можете в Подразделе 4.2.8.