Как посчитать всё на свете одним SQL-запросом. Оконные функции PostgreSQL
Я с удивлением обнаружил, что многие разработчики, даже давно использующие postgresql, не понимают оконные функции, считая их какой-то особой магией для избранных. Ну или в лучшем случае «копипастят» со StackOverflow выражения типа «row_number() OVER ()», не вдаваясь в детали. А ведь оконные функции — полезнейший функционал PostgreSQL.
Попробую по-простому объяснить, как можно их использовать.
Для начала хочу сразу пояснить, что оконные функции не изменяют выборку, а только добавляют некоторую дополнительную информацию о ней. Т.е. для простоты понимания можно считать, что postgres сначала выполняет весь запрос (кроме сортировки и limit), а потом только просчитывает оконные выражения.
Синтаксис примерно такой:
функция OVER окно
Окно — это некоторое выражение, описывающее набор строк, которые будет обрабатывать функция и порядок этой обработки.
Причем окно может быть просто задано пустыми скобками (), т.е. окном являются все строки результата запроса.
Например, в этом селекте к обычным полям id, header и score просто добавится нумерация строк.
SELECT
id,
section,
header,
score,
row_number() OVER () AS num
FROM news;
id | section | header | score | num
----+---------+-----------+-------+-----
1 | 2 | Заголовок | 23 | 1
2 | 1 | Заголовок | 6 | 2
3 | 4 | Заголовок | 79 | 3
4 | 3 | Заголовок | 36 | 4
5 | 2 | Заголовок | 34 | 5
6 | 2 | Заголовок | 95 | 6
7 | 4 | Заголовок | 26 | 7
8 | 3 | Заголовок | 36 | 8
В оконное выражение можно добавить ORDER BY, тогда можно изменить порядок обработки.
SELECT
id,
section,
header,
score,
row_number() OVER (ORDER BY score DESC) AS rating
FROM news
ORDER BY id;
id | section | header | score | rating
----+---------+-----------+-------+--------
1 | 2 | Заголовок | 23 | 7
2 | 1 | Заголовок | 6 | 8
3 | 4 | Заголовок | 79 | 2
4 | 3 | Заголовок | 36 | 4
5 | 2 | Заголовок | 34 | 5
6 | 2 | Заголовок | 95 | 1
7 | 4 | Заголовок | 26 | 6
8 | 3 | Заголовок | 36 | 3
Обратите внимание, что я добавил еще и в конце всего запоса ORDER BY id, при этом рейтинг посчитан все равно верно. Т.е. посгрес просто отсортировал результат вместе с результатом работы оконной функции, один order ничуть не мешает другому.
Дальше — больше. В оконное выражение можно добавить слово PARTITION BY [expression],
например row_number() OVER (PARTITION BY section), тогда подсчет будет идти в каждой группе отдельно:
SELECT
id,
section,
header,
score,
row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section
FROM news
ORDER BY section, rating_in_section;
id | section | header | score | rating_in_section
----+---------+-----------+-------+-------------------
2 | 1 | Заголовок | 6 | 1
6 | 2 | Заголовок | 95 | 1
5 | 2 | Заголовок | 34 | 2
1 | 2 | Заголовок | 23 | 3
4 | 3 | Заголовок | 36 | 1
8 | 3 | Заголовок | 36 | 2
3 | 4 | Заголовок | 79 | 1
7 | 4 | Заголовок | 26 | 2
Если не указывать партицию, то партицией является весь запрос.
Тут сразу надо немного сказать о функциях, которые можно использовать, так как есть очень важный нюанс.
В качестве функции можно использовать, так сказать, истинные оконные функции из мануала — это row_number(), rank(), lead() и т.д., а можно использовать функции-агрегаты, такие как: sum(), count() и т.д. Так вот, это важно, агрегатные функции работают слегка по-другому: если не задан ORDER BY в окне, идет подсчет по всей партиции один раз, и результат пишется во все строки (одинаков для всех строк партиции). Если же ORDER BY задан, то подсчет в каждой строке идет от начала партиции до этой строки.
Давайте посмотрим это на примере. Например, у нас есть некая (сферическая в вакууме) таблица пополнений балансов.
SELECT
transaction_id,
change
FROM balance_change
ORDER BY transaction_id;
transaction_id | change
----------------+--------
1 | 1.00
2 | -2.00
3 | 10.00
4 | -4.00
5 | 5.50
и мы хотим узнать заодно, как менялся остаток на балансе при этом:
SELECT
transaction_id,
change,
sum(change) OVER (ORDER BY transaction_id) as balance
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | balance
----------------+--------+---------
1 | 1.00 | 1.00
2 | -2.00 | -1.00
3 | 10.00 | 9.00
4 | -4.00 | 5.00
5 | 5.50 | 10.50
Т.е. для каждой строки идет подсчет в отдельном фрейме. В данном случае фрейм — это набор строк от начала до текущей строки (если было бы PARTITION BY, то от начала партиции).
Если же мы для агрегатной фунции sum не будем использовать ORDER BY в окне, тогда мы просто посчитаем общую сумму и покажем её во всех строках. Т.е. фреймом для каждой из строк будет весь набор строк
от начала до конца партиции.
SELECT
transaction_id,
change,
sum(change) OVER () as result_balance
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | result_balance
----------------+--------+----------------
1 | 1.00 | 10.50
2 | -2.00 | 10.50
3 | 10.00 | 10.50
4 | -4.00 | 10.50
5 | 5.50 | 10.50
Вот такая особенность агрегатных функций, если их использовать как оконные. На мой взгляд, это довольно-таки странный, интуитивно неочевидный момент SQL-стандарта.
Оконные функции можно использовать сразу по несколько штук, они друг другу ничуть не мешают, чтобы вы там в них не написали.
SELECT
transaction_id,
change,
sum(change) OVER (ORDER BY transaction_id) as balance,
sum(change) OVER () as result_balance,
round(
100.0 * sum(change) OVER (ORDER BY transaction_id) / sum(change) OVER (),
2
) AS percent_of_result,
count(*) OVER () as transactions_count
FROM balance_change
ORDER BY transaction_id;
transaction_id | change | balance | result_balance | percent_of_result | transactions_count
----------------+--------+---------+----------------+-------------------+--------------------
1 | 1.00 | 1.00 | 10.50 | 9.52 | 5
2 | -2.00 | -1.00 | 10.50 | -9.52 | 5
3 | 10.00 | 9.00 | 10.50 | 85.71 | 5
4 | -4.00 | 5.00 | 10.50 | 47.62 | 5
5 | 5.50 | 10.50 | 10.50 | 100.00 | 5
Если у вас много одинаковых выражений после OVER, то можно дать им имя и вынести отдельно с ключевым словом WINDOW, чтобы избежать дублирования кода. Вот пример из мануала:
SELECT
sum(salary) OVER w,
avg(salary) OVER w
FROM empsalary
WINDOW w AS (PARTITION BY depname ORDER BY salary DESC);
Здесь w после слова OVER идет без уже скобок.
Результат работы оконной функции невозможно отфильтровать в запросе с помощью WHERE, потому что оконные фунции выполняются после всей фильтрации и группировки, т.е. с тем, что получилось. Поэтому чтобы выбрать, например, топ 5 новостей в каждой группе, надо использовать подзапрос:
SELECT *
FROM (
SELECT
id,
section,
header,
score,
row_number() OVER (PARTITION BY section ORDER BY score DESC) AS rating_in_section
FROM news
ORDER BY section, rating_in_section
) counted_news
WHERE rating_in_section <= 5;
Еще пример для закрепления. Помимо row_number() есть несколько других функций. Например lag, которая ищет строку перед последней строкой фрейма. К примеру мы можем найти насколько очков новость отстает от предыдущей в рейтинге:
SELECT
id,
section,
header,
score,
row_number() OVER w AS rating,
lag(score) OVER w - score AS score_lag
FROM news
WINDOW w AS (ORDER BY score DESC)
ORDER BY score desc;
id | section | header | score | rating | score_lag
----+---------+-----------+-------+--------+-----------
6 | 2 | Заголовок | 95 | 1 |
3 | 4 | Заголовок | 79 | 2 | 16
8 | 3 | Заголовок | 36 | 3 | 43
4 | 3 | Заголовок | 36 | 4 | 0
5 | 2 | Заголовок | 34 | 5 | 2
7 | 4 | Заголовок | 26 | 6 | 8
1 | 2 | Заголовок | 23 | 7 | 3
2 | 1 | Заголовок | 6 | 8 | 17
Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.
