re:re:actable
Урок 013 минуты чтения

Что такое оконная функция

На этой странице
Кратко

Оконная функция вычисляет значение для каждой строки по набору связанных строк — окну, — но, в отличие от GROUP BY, не схлопывает строки: результат имеет столько же строк, сколько исходный набор. Окно задаётся в OVER (PARTITION BY … ORDER BY … frame): PARTITION BY делит строки на группы, ORDER BY упорядочивает внутри группы, frame ограничивает диапазон строк для агрегата. Виды: ранжирующие (row_number, rank, dense_rank, ntile), смещения (lag, lead, first_value, last_value, nth_value) и обычные агрегаты в оконном режиме (sum, avg, count … OVER). Оконные функции вычисляются после WHERE/GROUP BY/HAVING, поэтому фильтровать по их результату можно только во внешнем запросе.

Вопрос

Что такое оконная функция и чем она отличается от GROUP BY?

GROUP BY и окно

-- GROUP BY: одна строка на пользователя
SELECT user_id, sum(total) FROM orders GROUP BY user_id;
 
-- окно: каждая строка заказа + сумма по пользователю
SELECT id, user_id, total,
       sum(total) OVER (PARTITION BY user_id) AS user_total,
       round(total / sum(total) OVER (PARTITION BY user_id) * 100, 1) AS share_pct
FROM orders;
id | user_id | total | user_total | share_pct
10 | 1       | 500   | 800        | 62.5
11 | 1       | 300   | 800        | 37.5
12 | 2       | 700   | 700        | 100.0

Синтаксис

function(args) OVER (
  PARTITION BY ...      -- группы (необязательно)
  ORDER BY ...          -- порядок внутри группы (необязательно)
  ROWS BETWEEN ...      -- рамка (необязательно)
)

OVER () — окно из всех строк результата: count(*) OVER () даст общее количество строк рядом с каждой строкой (удобно для пагинации «всего N»).

Виды функций

ГруппаФункцииПример задачи
Ранжированиеrow_number, rank, dense_rank, ntile, percent_rankТоп-3 товара в категории
Смещениеlag, lead, first_value, last_value, nth_valueИзменение к предыдущему месяцу
Агрегатыsum, avg, count, min, maxНакопительная сумма, скользящее среднее

Именованное окно

SELECT id, user_id, total,
       row_number() OVER w AS n,
       sum(total)   OVER w AS running_total
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY created_at);

Фильтрация по результату окна

-- ✗ оконные функции нельзя использовать в WHERE
SELECT * FROM orders WHERE row_number() OVER (...) = 1;
 
-- ✓ через подзапрос или CTE
SELECT * FROM (
  SELECT o.*, row_number() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders o
) t
WHERE rn = 1;

Порядок вычисления

FROM → WHERE → GROUP BY → HAVING → оконные функции → SELECT → DISTINCT → ORDER BY → LIMIT

Поэтому окно можно применять поверх агрегатов: sum(sum(total)) OVER () — доля группы от общего итога.

SELECT category_id, sum(total) AS revenue,
       round(sum(total) / sum(sum(total)) OVER () * 100, 1) AS share_pct
FROM sales GROUP BY category_id;

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

  • Оконная функция в WHERE или HAVING.
  • Ожидать, что ORDER BY внутри OVER отсортирует результат — для этого нужен внешний ORDER BY.
  • Забыть, что ORDER BY в окне меняет рамку по умолчанию для агрегатов (получается накопительная сумма вместо общей).
  • LIMIT применяется после окон: count(*) OVER () считает все строки до LIMIT.

Что важно помнить

  • Окно не уменьшает количество строк.
  • PARTITION BY — группы, ORDER BY — порядок, frame — диапазон.
  • Фильтр по результату окна — во внешнем запросе.

Мини-шпаргалка

  • f() OVER (PARTITION BY a ORDER BY b).
  • count(*) OVER () — всего строк.
  • WINDOW w AS (...).
  • sum(sum(x)) OVER () — доля группы.

Документация

PostgreSQL: Window Functions Tutorial, PostgreSQL: Window Functions.

Дочитали до конца?
Отметьте урок — прогресс сохранится в браузере.

Поиск

Поиск по урокам