Как посчитать всё на свете одним 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

Прошу в коментариях накидать примеров, где особенно удобно применять оконные фунции. А также, какие с ними могут возникнуть проблемы, если таковые имеются.

Больше полезного можно найти на telegram-канале о разработке "Cross Join", где мы обсуждаем базы данных, языки программирования и всё на свете!