Оконные функции
Оконные функции выполняют вычисления по набору строк. Они не возвращают одну результирующую строку как агрегатные функции, а включают дополнительную информацию в выборку.
Упрощенный синтаксис оконной функции можно записать следующим образом:
<имя функции> OVER (<окно>)Где:
-
<имя функции>— имя оконной функции; -
<окно>— выражение, описывающее набор строк для обработки и порядок обработки.
Для получения информации о расширенном синтаксисе оконных функций обратитесь к статье Window Function Calls.
Если вам нужно передать все строки результата выполнения в оконную функцию, используйте пустые круглые скобки (). Например, следующий запрос добавляет к результату номера строк:
SELECT id, title, evaluation,
row_number() OVER () AS numbers
FROM books;Результат:
id | title | evaluation | numbers ----+-------------------------------------+------------+--------- 1 | Mrs. Dalloway | 7.82 | 1 2 | To the Lighthouse | 8.45 | 2 3 | To Kill a Mockingbird | 7.48 | 3 4 | The Great Gatsby | 9.23 | 4 5 | The Lord of the Rings | 9.49 | 5 6 | 1984 | 8.17 | 6 7 | The Hobbit, or There and Back Again | 9.32 | 7 8 | War and Peace | 9.69 | 8 9 | Hyperion | 9.46 | 9 10 | The Time Machine | 8.12 | 10
Вы можете добавить выражение ORDER BY к оконной функции, чтобы изменить порядок обработки:
SELECT id, title, genre, evaluation,
row_number() OVER (ORDER BY evaluation DESC) AS rating
FROM books
ORDER BY id;Результат:
id | title | genre | evaluation | rating ----+-------------------------------------+---------+------------+-------- 1 | Mrs. Dalloway | novel | 7.82 | 9 2 | To the Lighthouse | novel | 8.45 | 6 3 | To Kill a Mockingbird | novel | 7.48 | 10 4 | The Great Gatsby | novel | 9.23 | 5 5 | The Lord of the Rings | fantasy | 9.49 | 2 6 | 1984 | sci-fi | 8.17 | 7 7 | The Hobbit, or There and Back Again | fantasy | 9.32 | 4 8 | War and Peace | novel | 9.69 | 1 9 | Hyperion | sci-fi | 9.46 | 3 10 | The Time Machine | sci-fi | 8.12 | 8
В приведенном выше примере в запрос добавлена сортировка на уровне запроса (ORDER BY id). PostgreSQL сначала оценивает результаты оконной функции, а затем сортирует результаты запроса вместе с её результатами в соответствии с ORDER BY id. ORDER BY на уровне окна и ORDER BY на уровне запроса применяются корректно и не оказывают негативного влияния друг на друга.
Вы можете использовать выражение PARTITION BY, чтобы разделить строки на группы или партиции (partitions), которые используют одни и те же значения выражения PARTITION BY. Оконная функция вычисляет значение строки по строкам одной партиции. В следующем примере вычисляется рейтинг книг одного жанра:
SELECT id, title, genre, evaluation,
row_number() OVER (PARTITION BY genre ORDER BY evaluation DESC) AS rating
FROM books
ORDER BY genre;Результат:
id | title | genre | evaluation | rating ----+-------------------------------------+---------+------------+-------- 5 | The Lord of the Rings | fantasy | 9.49 | 1 7 | The Hobbit, or There and Back Again | fantasy | 9.32 | 2 8 | War and Peace | novel | 9.69 | 1 4 | The Great Gatsby | novel | 9.23 | 2 2 | To the Lighthouse | novel | 8.45 | 3 1 | Mrs. Dalloway | novel | 7.82 | 4 3 | To Kill a Mockingbird | novel | 7.48 | 5 9 | Hyperion | sci-fi | 9.46 | 1 6 | 1984 | sci-fi | 8.17 | 2 10 | The Time Machine | sci-fi | 8.12 | 3
Если не указывать PARTITION BY, то партицией являются все строки.
Список предопределенных оконных функций доступен по следующей ссылке: General-purpose window functions.
Вы также можете использовать агрегатные функции sum, count и другие в качестве оконных функций. Если ORDER BY не указан в окне функции, функция вычисляется для всей партиции, и её результат записывается во все строки партиции.
Например, имеется таблица orders:
id | customer_id | total ----+-------------+--------- 1 | 17 | 3500.00 2 | 22 | 1000.00 4 | 46 | 3300.00 11 | 38 | 2000.00 12 | 17 | 1750.00 3 | 17 | 5000.00 5 | 17 | 4700.00 6 | 17 | 2000.00 7 | 46 | 1900.00 8 | 22 | 2100.00 9 | 22 | 1400.00 10 | 13 | 1400.00 13 | 46 | 4600.00 14 | 13 | 2250.00
Следующий запрос вычисляет сумму заказов каждого клиента:
SELECT id, customer_id,
sum(total) OVER (PARTITION BY customer_id) as sum
FROM orders;Результат:
id | customer_id | sum ----+-------------+---------- 10 | 13 | 3650.00 14 | 13 | 3650.00 6 | 17 | 16950.00 12 | 17 | 16950.00 3 | 17 | 16950.00 5 | 17 | 16950.00 1 | 17 | 16950.00 2 | 22 | 4500.00 9 | 22 | 4500.00 8 | 22 | 4500.00 11 | 38 | 2000.00 7 | 46 | 9800.00 4 | 46 | 9800.00 13 | 46 | 9800.00
Если указан ORDER BY, функция вычисляется для строк от начала раздела до текущей строки. Если следующая строка содержит такое же значение поля, которое указано в ORDER BY, она включается в расчет. Этот набор строк называется фреймом (window frame). Некоторые оконные функции вычисляются на основании фрейма, а не всей партиции. Например, функция sum работает с фреймами. Добавим ORDER BY к приведенному выше примеру:
SELECT id, customer_id,
sum(total) OVER (PARTITION BY customer_id ORDER BY id) as sum
FROM orders;Результат:
id | customer_id | sum ----+-------------+---------- 10 | 13 | 1400.00 14 | 13 | 3650.00 1 | 17 | 3500.00 3 | 17 | 8500.00 5 | 17 | 13200.00 6 | 17 | 15200.00 12 | 17 | 16950.00 2 | 22 | 1000.00 8 | 22 | 3100.00 9 | 22 | 4500.00 11 | 38 | 2000.00 4 | 46 | 3300.00 7 | 46 | 5200.00 13 | 46 | 9800.00
Результат содержит сумму нарастающим итогом для каждой партиции.
Вы можете использовать оконные функции в выражениях SELECT и ORDER BY. Они не могут быть включены в GROUP BY, HAVING, WHERE и другие, поскольку оконные функции вычисляются после обработки этих выражений. Кроме того, оконные функции выполняются после неоконных агрегатных функций, поэтому вы можете включить вызов агрегатной функции в аргументы оконной функции.
Чтобы отфильтровать или сгруппировать строки после расчета оконной функции, используйте подзапросы.
Следующий запрос использует подзапрос c фильтром для отображения строк, где sum меньше 5000:
SELECT id, customer_id, sum
FROM
(SELECT id, customer_id, sum(total) OVER (PARTITION BY customer_id ORDER BY id) as sum
FROM orders
)AS orders_with_sum
WHERE sum < 5000;Результат:
id | customer_id | sum ----+-------------+--------- 10 | 13 | 1400.00 14 | 13 | 3650.00 1 | 17 | 3500.00 2 | 22 | 1000.00 8 | 22 | 3100.00 9 | 22 | 4500.00 11 | 38 | 2000.00 4 | 46 | 3300.00
В одном запросе можно использовать несколько оконных функций:
SELECT id, customer_id,
sum(total) OVER (PARTITION BY customer_id ORDER BY id) as sum,
round(avg(total) OVER(PARTITION BY customer_id),2) as avg
FROM orders;Результат:
id | customer_id | sum | avg ----+-------------+----------+--------- 10 | 13 | 1400.00 | 1825.00 14 | 13 | 3650.00 | 1825.00 1 | 17 | 3500.00 | 3390.00 3 | 17 | 8500.00 | 3390.00 5 | 17 | 13200.00 | 3390.00 6 | 17 | 15200.00 | 3390.00 12 | 17 | 16950.00 | 3390.00 2 | 22 | 1000.00 | 1500.00 8 | 22 | 3100.00 | 1500.00 9 | 22 | 4500.00 | 1500.00 11 | 38 | 2000.00 | 2000.00 4 | 46 | 3300.00 | 3266.67 7 | 46 | 5200.00 | 3266.67 13 | 46 | 9800.00 | 3266.67
Если несколько оконных функций имеют одно и то же выражение OVER, можно дать ему имя и поместить отдельно с помощью ключевого слова WINDOW. Запрос, приведенный выше, можно переписать следующим образом:
SELECT id, customer_id,
sum(total) OVER (w ORDER BY id) as sum,
round(avg(total) OVER(w),2) as avg
FROM orders
WINDOW w AS (PARTITION BY customer_id);Результат:
id | customer_id | sum | avg ----+-------------+----------+--------- 10 | 13 | 1400.00 | 1825.00 14 | 13 | 3650.00 | 1825.00 1 | 17 | 3500.00 | 3390.00 3 | 17 | 8500.00 | 3390.00 5 | 17 | 13200.00 | 3390.00 6 | 17 | 15200.00 | 3390.00 12 | 17 | 16950.00 | 3390.00 2 | 22 | 1000.00 | 1500.00 8 | 22 | 3100.00 | 1500.00 9 | 22 | 4500.00 | 1500.00 11 | 38 | 2000.00 | 2000.00 4 | 46 | 3300.00 | 3266.67 7 | 46 | 5200.00 | 3266.67 13 | 46 | 9800.00 | 3266.67