PARTITION BY и OVER в SQL: окно, группы и отличие от GROUP BY

Коротко

• OVER превращает агрегат или ранжирующую функцию в оконную: значение считается по группе строк, но каждая строка остаётся в результате.
• OVER () — одна группа на всю выборку. OVER (PARTITION BY channel) — отдельная группа на каждый канал.
• Если в окне есть ORDER BY, функция считает не по всей группе, а от её начала до текущей строки — так получается накопительный итог.
• Рамка по умолчанию при ORDER BY — RANGE: строки с одинаковым значением сортировки получают одинаковый итог. Для построчного итога пишите ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW и добавляйте уникальный ключ в сортировку.
• Оконные функции нельзя ставить в WHERE. Фильтруйте через CTE или подзапрос. QUALIFY есть в DuckDB, но не в PostgreSQL.

Что такое PARTITION BY и OVER

OVER — часть оконной функции, которая описывает окно: набор строк, по которым считается значение для текущей строки. PARTITION BY внутри OVER делит строки на независимые группы по значению колонки, например по каналу или пользователю. В отличие от GROUP BY, строки не схлопываются: каждая получает результат расчёта по своей группе.

Внутри скобок три необязательные части, и каждая отвечает на свой вопрос. PARTITION BY — какие строки считаются вместе. ORDER BY — в каком порядке они идут внутри группы. Рамка (ROWS, RANGE или GROUPS) — какой отрезок группы видит функция в текущей строке.

OVER (), OVER (PARTITION BY) и OVER (PARTITION BY … ORDER BY)

Проще всего увидеть разницу на одной выборке. Запрос ниже для каждого пользователя из таблицы users считает три числа: сколько пользователей всего, сколько в его канале и сколько в его канале зарегистрировалось к дате его регистрации.

Первые два столбца предсказуемы: 4613 во всех строках и размер канала — 814 у partner, 1037 у referral, 1747 у organic. Третий удивляет: у первого же пользователя канала partner стоит не 1, а 9. Причина в рамке по умолчанию. 1 июня в канал partner пришло 9 человек, у всех одинаковая signup_date, и окно с ORDER BY считает их одновременно. К этому поведению вернёмся в разделе про ROWS и RANGE.

select
  user_id,
  channel,
  signup_date,
  count(*) over () as all_users,
  count(*) over (partition by channel) as channel_users,
  count(*) over (
    partition by channel
    order by signup_date
  ) as channel_users_to_date
from users
order by user_id
limit 5;

Чем PARTITION BY отличается от GROUP BY

select channel, count(*) from users group by channel возвращает четыре строки — по одной на канал. count(*) over (partition by channel) возвращает все 4613 строк, и в колонке с результатом встречается ровно четыре разных значения. Группы одни и те же, а форма результата разная.

Из этого следует главное практическое различие. После GROUP BY в select можно оставить только колонки группировки и агрегаты. Попытка вывести user_id рядом с count(*) при group by channel в PostgreSQL заканчивается ошибкой column "users.user_id" must appear in the GROUP BY clause or be used in an aggregate function. С окном такой проблемы нет: каждая строка сохраняет все свои колонки.

Эти конструкции не конкурируют, а работают вместе. Окна считаются после GROUP BY и HAVING, поэтому оконную функцию можно применить к уже сгруппированному результату. Этим пользуются для долей, о них следующий раздел.

Доля от итога через SUM() OVER (PARTITION BY)

Задача: выручка по каналу и тарифу, и для каждой строки — доля тарифа внутри канала и доля от всей выручки. Запрос сначала группирует оплаты по каналу и тарифу, а затем окна складывают эти суммы: sum(sum(p.amount)) over (partition by u.channel) — выручка канала, sum(sum(p.amount)) over () — выручка всего.

Двойной sum выглядит странно, но читается по порядку выполнения. Внутренний — обычный агрегат GROUP BY. Внешний — оконная функция поверх получившихся двенадцати строк. В PostgreSQL round(x, 1) не принимает double precision, а amount в учебной базе именно такого типа, поэтому суммы приведены к numeric. В DuckDB запрос работает и без приведения.

По результату видно, что тариф basic даёт почти половину выручки в paid_search и partner, а в organic его опережает pro. Подробнее о долях, процентных пунктах и ловушке с WHERE — в статье про процент и долю в SQL.

select
  u.channel,
  p.plan,
  sum(p.amount) as revenue,
  round(100 * sum(p.amount)::numeric
    / sum(sum(p.amount)::numeric) over (partition by u.channel), 1) as share_in_channel,
  round(100 * sum(p.amount)::numeric
    / sum(sum(p.amount)::numeric) over (), 1) as share_of_total
from payments as p
join users as u on u.user_id = p.user_id
group by u.channel, p.plan
order by u.channel, p.plan;

Накопительный итог по пользователю

Добавим в окно ORDER BY, и сумма станет накопительной: для каждой оплаты — сколько пользователь заплатил к этому моменту. PARTITION BY user_id начинает счёт заново для каждого пользователя. В одном запросе рядом можно поставить окно без ORDER BY, и оно вернёт общий итог пользователя.

У пользователя 8 три оплаты тарифа pro по 29. Накопительный итог — 29, 58 и 87, номер оплаты — 1, 2 и 3, общий итог — 87 в каждой строке. В сортировку добавлен payment_id: если у пользователя две оплаты в один день, порядок между ними должен быть определён.

select
  payment_id,
  paid_at,
  amount,
  sum(amount) over (partition by user_id order by paid_at, payment_id) as revenue_to_date,
  count(*)    over (partition by user_id order by paid_at, payment_id) as payment_no,
  sum(amount) over (partition by user_id) as user_total
from payments
where user_id = 8
order by paid_at;

Рамка окна: чем ROWS отличается от RANGE

Рамка задаёт, какой отрезок группы видит функция в текущей строке. Если в окне нет ORDER BY, рамка — вся группа. Если ORDER BY есть, а рамка не указана, по стандарту и в PostgreSQL, и в DuckDB действует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

Слово RANGE здесь ключевое. Оно считает «текущей» не одну строку, а все строки с тем же значением сортировки. ROWS считает физические строки по одной. Пока значения сортировки уникальны, разницы нет. Как только появляются повторы, RANGE выдаёт всем одинаковым строкам одно и то же значение.

В учебной базе 6 июня было две оплаты, 7 июня — одна, 8 июня — три. Накопительная выручка по дням с рамкой по умолчанию стоит ступеньками: 48 у обеих оплат 6 июня и 144 у всех трёх оплат 8 июня. С ROWS и payment_id в сортировке итог растёт на каждой строке: 29, 48, 67, 106, 125, 144. PostgreSQL и DuckDB здесь отвечают одинаково.

Какая рамка правильная, зависит от вопроса. «Выручка к концу дня» — это RANGE: внутри дня порядок не важен. «Номер оплаты и сумма к моменту этой оплаты» — это ROWS с уникальным ключом в сортировке. Если писать ROWS без уникального ключа, порядок внутри одинаковых дат не определён, и накопительный итог может меняться от запуска к запуску. Третий вид рамки, GROUPS, считает группы одинаковых значений: groups between 1 preceding and current row в тех же строках берёт текущий и предыдущий день с оплатами. Его понимают и PostgreSQL, и DuckDB.

select
  payment_id,
  paid_at,
  amount,
  sum(amount) over (order by paid_at) as default_frame,
  sum(amount) over (
    order by paid_at, payment_id
    rows between unbounded preceding and current row
  ) as rows_frame
from payments
where paid_at <= '2026-06-08'
order by paid_at, payment_id;

Скользящее окно: 7 строк или 7 дней

Для скользящих сумм рамку задают со смещением. rows between 6 preceding and current row берёт текущую строку и шесть предыдущих. Если строки — это дни, всё верно, пока дни идут без пропусков. В учебной базе 9 и 11 июня оплат не было, и в дневной таблице этих дат просто нет. Тогда шесть предыдущих строк растягиваются больше чем на неделю.

range between interval '6 days' preceding and current row считает по значению даты, а не по числу строк: в окно попадает всё, что не старше шести дней от текущей даты. На 13 июня «7 строк» дают 470, потому что захватывают 6 июня, а «7 дней» — 422: окно начинается с 7 июня. С 18 июня пропуски выходят из окна, и оба варианта снова дают 1028. Такой RANGE со смещением поддерживают PostgreSQL 14, на котором проверены примеры, и DuckDB.

with daily as (
  select paid_at, sum(amount) as revenue
  from payments
  group by paid_at
)
select
  paid_at,
  revenue,
  sum(revenue) over (order by paid_at
    rows between 6 preceding and current row) as last_7_rows,
  sum(revenue) over (order by paid_at
    range between interval '6 days' preceding and current row) as last_7_days
from daily
where paid_at <= '2026-06-18'
order by paid_at;

Почему LAST_VALUE возвращает текущую строку

Рамка по умолчанию объясняет частый вопрос: почему last_value(paid_at) over (partition by user_id order by paid_at) возвращает не последнюю дату оплаты, а дату текущей строки. Рамка заканчивается на текущей строке, и последняя строка рамки — она сама. У пользователя 8 такой запрос выдаёт 10 июня, 10 июля и 9 августа — ровно его же paid_at.

Чтобы получить последнюю дату в группе, расширьте рамку до конца: rows between unbounded preceding and unbounded following. Тогда во всех трёх строках будет 9 августа. Проще и понятнее то же даёт max(paid_at) over (partition by user_id): без ORDER BY рамка и так охватывает всю группу.

select
  payment_id,
  paid_at,
  last_value(paid_at) over (partition by user_id order by paid_at) as last_default,
  last_value(paid_at) over (
    partition by user_id order by paid_at
    rows between unbounded preceding and unbounded following
  ) as last_full,
  max(paid_at) over (partition by user_id) as last_max
from payments
where user_id = 8
order by paid_at;
-- last_default: 2026-06-10, 2026-07-10, 2026-08-09
-- last_full и last_max: 2026-08-09 во всех строках

WINDOW: как не повторять одно и то же окно

Когда в запросе три-четыре функции с одинаковым окном, копировать partition by … order by … в каждую неудобно и опасно: поправите сортировку в одном месте и забудете в другом. Предложение WINDOW задаёт окно один раз по имени, и функции ссылаются на него через OVER w. Оно стоит после WHERE, GROUP BY и HAVING, перед ORDER BY. PostgreSQL и DuckDB его поддерживают.

Результат совпадает с запросом из раздела про накопительный итог, а lag добавляет дату предыдущей оплаты: пусто у первой, 10 июня и 10 июля у следующих.

select
  payment_id,
  paid_at,
  amount,
  row_number() over w as payment_no,
  sum(amount)  over w as revenue_to_date,
  lag(paid_at) over w as prev_paid_at
from payments
where user_id = 8
window w as (partition by user_id order by paid_at, payment_id)
order by paid_at;

Как отфильтровать строки по результату окна

Оконные функции считаются после WHERE, поэтому сослаться на них там нельзя. PostgreSQL на where sum(amount) over (partition by user_id) >= 100 отвечает ERROR: window functions are not allowed in WHERE, DuckDB — «WHERE clause cannot contain window functions!». В HAVING окно тоже запрещено: window functions are not allowed in HAVING.

Рабочий вариант — посчитать окно в CTE или подзапросе и фильтровать снаружи. Запрос ниже оставляет все оплаты пользователей, которые в сумме заплатили от 100: таких 7 человек и 21 оплата. В DuckDB есть сокращение: QUALIFY user_total >= 100 после FROM и WHERE, без CTE. PostgreSQL его не знает. На from payments qualify user_total >= 100 он отвечает syntax error at or near "user_total": слово qualify он принимает за псевдоним таблицы. Подробнее о фильтре по рангу и по LAG — в статьях про RANK и LAG.

with p as (
  select
    user_id,
    paid_at,
    amount,
    sum(amount) over (partition by user_id) as user_total
  from payments
)
select count(*) as payments, count(distinct user_id) as users
from p
where user_total >= 100;

Частые ошибки с OVER и PARTITION BY

• Ожидать построчный накопительный итог при ORDER BY без рамки. При повторяющихся значениях сортировки рамка по умолчанию RANGE даёт ступеньки.
• Писать ROWS без уникального ключа в сортировке. Порядок внутри одинаковых дат не определён, и итог может отличаться между запусками.
• Считать «7 строк» скользящей неделей. Если в данных бывают пропущенные дни, нужен RANGE с интервалом или календарь дней, соединённый с данными.
• Ждать, что окно видит отфильтрованные строки. Окно считается по результату после WHERE: count(*) over () в запросе с where channel = 'organic' вернёт 1747, а не 4613.
• Ждать, что LIMIT ограничит окно. LIMIT применяется после окон: в выборке из трёх пользователей count(*) over () всё равно равен 4613.
• Забыть про NULL в ключе партиции. Строки с пустым значением образуют отдельную группу: у 55 пользователей без страны count(*) over (partition by country) равен 55.
• Переносить count(distinct …) over (…) из DuckDB в PostgreSQL. DuckDB вернёт 912 платящих пользователей, а PostgreSQL ответит DISTINCT is not implemented for window functions. Посчитайте уникальных в подзапросе и присоедините.
• Считать, что ORDER BY внутри OVER сортирует результат. Он задаёт порядок только для расчёта, итоговую сортировку задаёт внешний ORDER BY.

Чеклист перед тем, как отдать запрос с окном

• Зерно строк перед окном то, что нужно: оплаты, дни или пользователи, а не сырые события там, где сравниваются дни.
• В PARTITION BY ровно тот ключ, внутри которого идёт расчёт, и понятно, что происходит со строками, где он NULL.
• В ORDER BY окна есть уникальный ключ, если нужен построчный результат.
• Рамка указана явно, когда в окне есть ORDER BY и результат зависит от одинаковых значений.
• Фильтр по оконному значению вынесен в CTE или подзапрос, если запрос должен работать в PostgreSQL.
• На одной группе результат проверен руками: первая строка, последняя строка и строки с одинаковой датой.

Что попробовать на учебной базе

Все запросы из статьи выполняются в песочнице SQL-курса. Для тренировки посчитайте для каждого пользователя долю его выручки в выручке его страны и оставьте по пять пользователей с наибольшей долей в каждой стране. Понадобятся sum() over (partition by …), CTE для фильтра и ранжирующая функция.