Оконные функции в PostgreSQL

Оконные функции (window functions) позволяют вычислять значения “по группе строк”, не схлопывая результат, как это делает GROUP BY. То есть каждая строка остаётся на месте, а ты получаешь дополнительные вычисленные колонки: ранги, накопительные суммы, значения “предыдущей строки”, доли и т.д.

Синтаксис OVER

Почти любая оконная функция выглядит так:

func(...) OVER (
  PARTITION BY ...
  ORDER BY ...
  ROWS/RANGE BETWEEN ...
)

• PARTITION BY — “разделить” строки на группы (как мини-GROUP BY, но без схлопывания)
• ORDER BY — порядок строк внутри каждой группы
• ROWS/RANGE ... — рамка окна (frame): какие строки участвуют в вычислении для текущей строки

Пример 1 — SUM(...) OVER(PARTITION BY ...) (итог по группе без GROUP BY)

Посчитаем сумму зарплат по отделу, но оставим строки сотрудников.

Данные:

Запрос:

SELECT
  id,
  name,
  department,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees
ORDER BY department, salary DESC;

Пример 2 — ROW_NUMBER, RANK, DENSE_RANK

Эти функции помогают нумеровать строки и строить рейтинги.

• ROW_NUMBER() — уникальный номер строки внутри группы (1,2,3...)
• RANK() — рейтинг с “дырками” при равенстве (1,1,3...)
• DENSE_RANK() — рейтинг без “дырок” (1,1,2...)

Данные:

Запрос:

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id) AS row_number,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY department, salary DESC, name;

Пример 3 — топ-2 в каждой группе (ROW_NUMBER + подзапрос)

Данные:

Запрос:

SELECT department, name, salary
FROM (
  SELECT
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC, id) AS rn
  FROM employees
) t
WHERE rn <= 2
ORDER BY department, salary DESC;

Пример 4 — LAG и LEAD (предыдущее/следующее значение)

LAG и LEAD позволяют “заглянуть” на предыдущую/следующую строку внутри окна. Это удобно для расчёта изменений (дельт), трендов, сравнения с предыдущей покупкой и т.д.

Данные:

Запрос:

SELECT
  day,
  amount,
  LAG(amount)  OVER (ORDER BY day) AS prev_amount,
  amount - LAG(amount) OVER (ORDER BY day) AS delta_from_prev,
  LEAD(amount) OVER (ORDER BY day) AS next_amount
FROM sales
ORDER BY day;

Пример 5 — скользящая сумма (running total)

Скользящая сумма — это сумма “с начала до текущей строки”.

Данные:

Запрос:

SELECT
  day,
  amount,
  SUM(amount) OVER (
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM sales
ORDER BY day;

Пример 6 — скользящее среднее (moving average) за 3 строки

Оконная рамка позволяет брать “окрестность” строки: например текущая и две предыдущих.

Данные:

Запрос:

SELECT
  day,
  amount,
  ROUND(
    AVG(amount) OVER (
      ORDER BY day
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ),
    2
  ) AS moving_avg_3
FROM sales
ORDER BY day;

Пример 7 — среднее по группе + сравнение с ним

Сравним зарплату сотрудника со средней по отделу.

Данные:

Запрос:

SELECT
  department,
  name,
  salary,
  ROUND(AVG(salary) OVER (PARTITION BY department), 2) AS dept_avg_salary,
  ROUND(salary - AVG(salary) OVER (PARTITION BY department), 2) AS diff_from_avg
FROM employees
ORDER BY department, salary DESC;

Пример 8 — FIRST_VALUE и LAST_VALUE (нюанс рамки!)

FIRST_VALUE и LAST_VALUE возвращают первое/последнее значение в окне. В PostgreSQL есть тонкость: LAST_VALUE зависит от рамки окна.

Данные:

Запрос:

SELECT
  day,
  price,
  FIRST_VALUE(price) OVER (ORDER BY day) AS first_price,
  -- Без явной рамки last_value часто становится "текущим" значением
  LAST_VALUE(price) OVER (ORDER BY day) AS last_value_default_frame,
  -- Правильный "последний в полном окне":
  LAST_VALUE(price) OVER (
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_price_full_window
FROM prices
ORDER BY day;

Пример 9 — доля в сумме (процент от итога)

“Какую долю от суммы по отделу составляет зарплата сотрудника?”

Данные:

Запрос:

SELECT
  department,
  name,
  salary,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  ROUND(
    salary::numeric / SUM(salary) OVER (PARTITION BY department) * 100,
    2
  ) AS pct_of_dept
FROM employees
ORDER BY department, salary DESC;

Именованные окна (WINDOW) — меньше копипасты

В PostgreSQL можно объявить окно один раз через WINDOW и переиспользовать его.

Данные:

Запрос:

SELECT
  department,
  name,
  salary,
  SUM(salary) OVER w AS dept_total,
  ROW_NUMBER() OVER w_ordered AS rn_in_dept
FROM employees
WINDOW
  w AS (PARTITION BY department),
  w_ordered AS (PARTITION BY department ORDER BY salary DESC, id)
ORDER BY department, salary DESC;

FILTER и оконные функции

В PostgreSQL FILTER применяется к агрегатным функциям и позволяет учитывать в агрегате только строки, удовлетворяющие условию. И это работает и для оконных агрегатов:

SUM(total) FILTER (WHERE status = 'done')
OVER (PARTITION BY user_id)

Пример 10 — SUM(...) FILTER (...) OVER (...) (сумма “done” и сумма “всего” одновременно)

Данные:

Запрос:

SELECT
  id,
  user_id,
  status,
  total,
  SUM(total) OVER (PARTITION BY user_id) AS total_sum,
  SUM(total) FILTER (WHERE status = 'done')
    OVER (PARTITION BY user_id) AS done_sum
FROM orders
ORDER BY user_id, id;

Пример 11 — COUNT(*) FILTER (...) OVER (...) (счётчики разных статусов)

Данные:

Запрос:

SELECT
  id,
  user_id,
  status,
  COUNT(*) OVER (PARTITION BY user_id) AS orders_total,
  COUNT(*) FILTER (WHERE status = 'done') OVER (PARTITION BY user_id) AS orders_done,
  COUNT(*) FILTER (WHERE status = 'new')  OVER (PARTITION BY user_id) AS orders_new
FROM orders
ORDER BY user_id, id;

Пример 12 — FILTER + running total (накопительная сумма только “done”)

Данные:

Запрос:

SELECT
  id,
  status,
  total,
  SUM(total) FILTER (WHERE status = 'done')
    OVER (
      PARTITION BY user_id
      ORDER BY id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_done_sum
FROM orders
ORDER BY id;

Пример 13 — FILTER vs PARTITION BY (частая путаница)

Эти конструкции выглядят похоже, но делают разное:

-- 1) FILTER: окно по user_id, но агрегат считает только done
SUM(total) FILTER (WHERE status = 'done')
OVER (PARTITION BY user_id)

-- 2) PARTITION BY user_id, status: создаём отдельные окна для done и new
SUM(total)
OVER (PARTITION BY user_id, status)

Покажем разницу на примере.

Данные:

Запрос (оба варианта рядом):

SELECT
  id,
  status,
  total,
  SUM(total) FILTER (WHERE status = 'done') OVER (PARTITION BY user_id) AS done_sum_filter,
  SUM(total) OVER (PARTITION BY user_id, status) AS sum_by_status_partition
FROM orders
ORDER BY id;

Частые ошибки и нюансы

SELECT *
FROM (
  SELECT ..., ROW_NUMBER() OVER (...) AS rn
  FROM ...
) t
WHERE rn <= 3;

Итого

• Окна сохраняют строки и добавляют вычисления “по группе/порядку”.
• PARTITION BY — группы, ORDER BY — порядок, рамка — какие строки участвуют.
• Ключевые функции: SUM/AVG OVER, ROW_NUMBER/RANK, LAG/LEAD, running totals, moving averages.
• FILTER работает с агрегатами, в том числе оконными, и помогает считать “условные суммы/счётчики” без CASE WHEN.