Оконные функции SQL: OVER и PARTITION BY, ROW_NUMBER и LAG

Коротко

Оконная функция выполняет расчёт по «окну» строк, заданному через OVER. В отличие от GROUP BY, она сохраняет исходные строки и добавляет к ним вычисленный столбец.

• PARTITION BY задаёт независимые группы, например одного пользователя.
• ORDER BY внутри окна задаёт порядок строк для нумерации и сравнений.
• ROW_NUMBER() нумерует строки внутри каждой группы.
• LAG() и LEAD() достают значение из предыдущей или следующей строки.
• SUM(...) OVER считает накопительный итог, не убирая отдельные операции.

Оконная функция не схлопывает строки

Сравните два подхода. GROUP BY user_id вернёт одну строку на пользователя и потеряет порядок отдельных событий. ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) сохранит все события, но добавит к каждому номер внутри истории пользователя.

ROW_NUMBER: найти первое событие пользователя

Один из самых полезных паттернов — найти первое событие в истории пользователя. Сначала нумеруем события по времени внутри каждого user_id, затем оставляем строки с номером один. Если у двух событий одинаковое время, добавьте второй стабильный ключ в ORDER BY, например event_id.

В учебной базе первым у всех идёт app_open, поэтому запрос ищет первое действие, кроме открытия приложения: у 2 750 человек это workspace_created, у 456 — invite_sent. LIMIT 10 оставляет десять самых ранних строк.

with numbered_events as (
  select
    user_id,
    event_time,
    event_name,
    row_number() over (
      partition by user_id
      order by event_time, event_id
    ) as event_number
  from events
  where event_name <> 'app_open'
)
select
  user_id,
  event_time as first_action_time,
  event_name as first_action
from numbered_events
where event_number = 1
order by first_action_time, user_id
limit 10;

LAG: сравнить строку с предыдущей

LAG возвращает значение из предыдущей строки в заданном порядке. Для дневного DAU сначала получите одну строку на день, затем добавьте предыдущий DAU и разницу. Так можно увидеть не только саму метрику, но и день, когда изменение стало заметным.

У первой даты previous_dau будет NULL: до неё нет предыдущей строки. Это нормальный результат, а не ноль. Если в отчёте нужен ноль, преобразуйте его явно через COALESCE, но не смешивайте «нет сравнения» и «предыдущий день действительно был нулевым».

На учебной базе в результате 91 строка, активность — любое событие. В последней строке, 30 августа, DAU 223 против 530 накануне, но это не спад: выгрузка обрывается в 12:59, день неполный.

with daily as (
  select
    cast(event_time as date) as day,
    count(distinct user_id) as dau
  from events
  group by cast(event_time as date)
),
with_previous as (
  select
    day,
    dau,
    lag(dau) over (order by day) as previous_dau
  from daily
)
select
  day,
  dau,
  previous_dau,
  dau - previous_dau as dau_change
from with_previous
order by day;

SUM OVER: накопительная выручка

Обычный SUM(amount) с GROUP BY даёт одну строку на день или пользователя. Оконный SUM(amount) OVER (ORDER BY paid_at, user_id) оставляет каждый платёж и показывает, как растёт итог после каждой операции. Это удобно для финансового графика и проверки, в какой момент достигнут план. Запрос берёт июнь учебной базы: 167 платежей, в последней строке итог месяца — 3 983.

select
  paid_at,
  user_id,
  amount,
  sum(amount) over (
    order by paid_at, user_id
    rows between unbounded preceding and current row
  ) as cumulative_revenue
from payments
where paid_at < date '2026-07-01'
order by paid_at, user_id;

Окно по пользователю и окно по всей таблице

Место PARTITION BY меняет смысл результата. С PARTITION BY user_id нумерация начинается заново для каждого пользователя. Без PARTITION вся таблица считается одной группой. Это удобно для общего накопительного итога, но неверно, если нужно найти первое событие каждого пользователя.

Порядок тоже часть определения метрики. Если сортировать историю только по дате, а на одну дату приходится несколько событий, порядок между ними может быть нестабильным. Добавляйте tie-breaker: event_id, payment_id или другой уникальный ключ.

• Окно по user_id — отдельная история каждого пользователя.
• Окно без PARTITION — вся таблица как одна последовательность.
• Окно по channel — сравнение позиций внутри канала.
• ORDER BY в окне не обязательно сортирует финальный результат, поэтому внешний ORDER BY всё равно нужен.

Частые ошибки в оконных функциях

Оконные запросы становятся понятными, если всегда отделять два вопроса: какие строки должны остаться и по какой последовательности считается новое поле. Большинство ошибок появляется, когда эти уровни смешивают.

• Использовать ROW_NUMBER без PARTITION BY и получить одну глобальную нумерацию вместо нумерации по пользователю.
• Ожидать, что LAG вернёт ноль для первой строки: там корректно будет NULL.
• Забыть внешний ORDER BY и увидеть строки в порядке, который не обязан совпадать с окном.
• Считать накопительный итог без уникального порядка при одинаковых датах.
• Применить оконную функцию до агрегации, хотя сравнивать нужно уже дневные метрики.
• Использовать окно как замену GROUP BY и случайно оставить слишком детальный результат.

Оконная функция после агрегации

Оконная функция работает с теми строками, которые пришли в её слой. Если нужно сравнить DAU по дням, сначала агрегируйте события до одной строки на дату, а уже затем применяйте LAG. Если поставить LAG на сырые events, предыдущей строкой станет событие, а не предыдущий день.

Это правило помогает разделить detail и metric layers. На детальном уровне нужны user_id и timestamp, на уровне метрики — date и dau. Оконная функция не меняет grain, поэтому перед ней нужно явно получить тот уровень, который сравнивается.

После окна проверьте первые строки, количество строк и диапазон дат. Если метрика должна быть одной строкой на день, сравните count(*) с count(distinct day). Такой простой sanity-check ловит случайный JOIN и лишнее поле в GROUP BY. В запросе ниже 90 строк на 90 полных дней: неполное 30 августа отрезано до агрегации.

with daily as (
  select
    cast(event_time as date) as day,
    count(distinct user_id) as dau
  from events
  where event_time < date '2026-08-30'
  group by cast(event_time as date)
), compared as (
  select
    day,
    dau,
    lag(dau) over (order by day) as previous_dau
  from daily
)
select
  day,
  dau,
  previous_dau,
  dau - previous_dau as change
from compared
order by day;

Одинаковые timestamp требуют tie-breaker

Если два события имеют одинаковое время, row_number может выбрать первую строку по-разному между запусками, если в ORDER BY нет уникального поля. Добавьте event_id, ingestion_id или другой стабильный ключ. Если такого ключа нет, честно напишите, что порядок внутри одной секунды неизвестен, и не делайте вывод о последовательности.

Для rank и dense_rank одинаковые значения получают одинаковое место, а row_number всегда выдаёт разные номера. Выбирайте функцию по смыслу: место в рейтинге и первое событие — разные задачи. В отчёте назовите, как обрабатываются ничьи.

У LAG первая строка partition закономерно получает NULL. Не превращайте её в ноль до того, как посчитаете долю изменений. Если это первый день истории, предыдущего значения просто нет; если внутри ряда пропущен день, нужно сначала восстановить календарную сетку.

Доля от группы: считаем без второго запроса

Частая задача: показать не только выручку канала, но и его долю в общей. Обычно её решают в два прохода — сначала считают итог, потом делят. Окно позволяет сделать это одним запросом: агрегат по всей выборке живёт рядом со строкой группы.

Приём работает и внутри разрезов. Добавьте u.country в GROUP BY: sum(sum(p.amount)) OVER (PARTITION BY u.country) станет знаменателем доли канала внутри своей страны, а окно без PARTITION BY — доли в общем итоге. Обе колонки можно поставить рядом, и тогда видно, что organic в России — это 25,2% всей выручки учебной базы, но 44,1% выручки своей страны.

Важно не перепутать уровни: агрегация по группам делается обычным GROUP BY, а окно применяется уже к результату группировки. Оконная функция здесь не заменяет GROUP BY, а надстраивается над ним.

SELECT
  u.channel,
  sum(p.amount)                                        AS revenue,
  sum(sum(p.amount)) OVER ()                           AS revenue_total,
  round(100.0 * sum(p.amount) / sum(sum(p.amount)) OVER (), 1) AS share_pct
FROM users u
JOIN payments p ON p.user_id = u.user_id
GROUP BY u.channel
ORDER BY revenue DESC;

Серии подряд: как посчитать непрерывную активность

Продуктовые команды любят вопрос «сколько дней подряд человек заходит в продукт». В лоб он не решается: в SQL нет функции «длина серии». Зато есть приём, который стоит знать, — его называют gaps and islands, острова и разрывы.

Идея в одном наблюдении. Если пронумеровать дни активности пользователя подряд и вычесть номер из самой даты, то у всех дней одной непрерывной серии разность окажется одинаковой. Как только в активности появляется пропуск, разность меняется — начинается новый «остров». Дальше остаётся сгруппировать по этой разности и посчитать длину каждой серии.

Приём работает и за пределами streak: так же ищут непрерывные периоды подписки, интервалы без ошибок в логах или дни, когда метрика держалась выше порога. Один раз разобравшись с ним, вы будете узнавать задачу по формулировке «сколько подряд».

Запрос показывает десять самых длинных серий учебной базы: рекорд — восемь дней подряд у пользователя 1718, остальные девять — по семь.

WITH active_days AS (          -- зерно: пользователь + день
  SELECT DISTINCT user_id, CAST(event_time AS DATE) AS day
  FROM events
),
marked AS (
  SELECT
    user_id,
    day,
    day - CAST(row_number() OVER (PARTITION BY user_id ORDER BY day) AS INTEGER) AS island
  FROM active_days
)
SELECT
  user_id,
  min(day)   AS streak_start,
  max(day)   AS streak_end,
  count(*)   AS streak_days
FROM marked
GROUP BY user_id, island
HAVING count(*) > 1
ORDER BY streak_days DESC, user_id, streak_start
LIMIT 10;

Сессия собирается из LAG, порога бездействия и накопительной суммы

Разрывы в активности мы только что считали по дням: номер строки минус дата давали «остров». Сессия — тот же разрыв, только измеренный в минутах, и собирается она другой парой функций. В трекинге обычно нет колонки session_id: приходит поток событий с user_id и временем, а границу между визитами аналитик назначает сам.

Правило состоит из трёх шагов, и каждый — отдельное окно. Сначала LAG(event_time) по пользователю даёт время предыдущего события, из него считается пауза. Потом пауза сравнивается с порогом: если она больше либо равна порогу или предыдущего события нет вообще, ставится флаг 1. Потом SUM по тому же окну превращает поток нулей и единиц в номер сессии — это тот же накопительный итог, что считал выручку, только складывает он не деньги, а количество начавшихся визитов.

Посмотрите, как это работает на первых шести событиях пользователя 5 из учебной базы. Паузы между ними — 35 минут, 85 минут, 23, 27 и 22 часа. При пороге 60 минут флаг не сработает только на втором событии, и получится пять сессий: первая — два события за 35 минут, остальные — по одному. Поставьте порог 3 часа, и пауза в 85 минут перестанет быть границей: сессий станет четыре. Тот же набор событий, два разных ответа на вопрос «сколько визитов», и отличаются они только числом в условии. По всей базе это 32 591 и 30 477 сессий, а при пороге 30 минут — 35 341: самая короткая пауза в базе — 35 минут, и сессией становится каждое событие.

Отсюда два практических следствия. Первое: порог проверяют по распределению пауз в своём продукте, а не берут из чужой статьи — для мобильной игры тридцать минут могут склеить два вечерних захода, а для оформления заказа разорвать один. Второе: пауза считается по тем строкам, которые дошли до LAG. Если оставить в потоке только app_open, промежуточные события выпадут, паузы вырастут, и число сессий уменьшится — разметку делают на полном потоке, а фильтр применяют после.

Разметка не меняет зерно: session_number живёт на строке события. Чтобы говорить про сессии, нужен ещё один слой — агрегация до user_id × session_number, где появятся длительность, число событий и точка входа. На этом слое сразу видна ловушка средней длительности: визит из одного события имеет длину ноль, и чем больше в продукте одностраничных заходов, тем сильнее они тянут среднее вниз, ничего не говоря о вовлечённости. В учебной базе 29 841 сессия из 32 591 состоит из одного события, остальные 2 750 длятся 35 минут: средняя по всем — около трёх минут, и ни одна сессия столько не длится.

with ordered as (
  select
    user_id,
    event_id,
    event_time,
    event_name,
    lag(event_time) over (partition by user_id order by event_time, event_id) as previous_time
  from events
), flagged as (
  select
    ordered.*,
    case
      when previous_time is null then 1
      when event_time - previous_time >= interval '60 minutes' then 1
      else 0
    end as is_new_session
  from ordered
), marked as (
  select
    flagged.*,
    sum(is_new_session) over (partition by user_id order by event_time, event_id) as session_number
  from flagged
), sessions as (          -- слой отчёта: одна строка на сессию
  select
    user_id,
    session_number,
    min(event_time)                                   as started_at,
    max(event_time)                                   as ended_at,
    cast(extract(epoch from max(event_time) - min(event_time)) / 60 as integer) as duration_min,
    count(*)                                          as events_in_session
  from marked
  group by user_id, session_number
)
select
  events_in_session,
  count(*)                    as sessions,
  round(avg(duration_min), 1) as avg_duration_min
from sessions
group by events_in_session
order by events_in_session;

Словарь окон: какой функцией решается какая задача

Оконных функций немного, и почти все аналитические задачи сводятся к десятку узнаваемых паттернов. Полезно один раз запомнить соответствие «задача → функция», чтобы не изобретать подзапрос там, где хватает одной строки.

Отдельно стоит группа first_value и last_value: они достают значение с края окна и удобны, когда нужно сравнить каждую строку с исходным состоянием — например, показать выручку месяца рядом с выручкой первого месяца когорты.

Цена окна: сортировка, память и место расчёта

Оконная функция требует упорядочить данные внутри каждой секции. На небольших выборках это незаметно, но на десятках миллионов строк сортировка становится главной статьёй расходов запроса, особенно если секций мало, а строк в каждой много.

Практические выводы простые. Сначала сужайте данные — фильтр по периоду и агрегация до нужного зерна дешевле, чем окно поверх всей истории. Если по полям PARTITION BY и ORDER BY есть подходящий индекс, база может пропустить отдельную сортировку. И помните, что несколько окон с одинаковой спецификацией считаются за один проход, а с разными — за несколько: одинаковые OVER лучше выписывать одинаково или выносить в WINDOW.

Есть и вопрос места расчёта. Скользящее среднее и доли многие BI-инструменты умеют считать сами. Если метрика нужна только на одном графике, дешевле оставить это BI; если она попадает в несколько отчётов и должна везде совпадать — считайте в SQL и храните как витрину.

Рамка окна: где заканчивается «до текущей строки»

У окна есть не только PARTITION BY и ORDER BY, но и рамка — набор строк, которые участвуют в расчёте для текущей строки. Обычно её не пишут явно, и это работает до тех пор, пока не появляются повторяющиеся значения в сортировке.

Умолчание такое: если у окна есть ORDER BY, рамкой считается RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, а CURRENT ROW в режиме RANGE означает не одну строку, а все строки с тем же значением сортировки. Практический эффект: накопительный итог по дням при нескольких платежах в один день сразу включит весь день, а не остановится на текущей записи.

Если нужен именно построчный накопительный итог, рамку задают явно через ROWS. Разница между ROWS и RANGE — одна из тех вещей, которые незаметны на аккуратных данных и проявляются ровно в тот день, когда два события совпали по времени. В запросе ниже это 8 июня: у трёх платежей дня total_default одинаковый, 144, а total_rows растёт построчно — 106, 125, 144.

Та же рамка позволяет считать скользящие метрики: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW даёт среднее за последние семь строк — привычный сглаженный ряд для дневных метрик. В запросе это avg_last_7, средний чек семи последних платежей.

SELECT
  payment_id,
  paid_at,
  amount,
  -- рамка по умолчанию, RANGE: все платежи дня входят в итог сразу
  sum(amount) OVER (ORDER BY paid_at) AS total_default,
  -- построчный итог: каждая строка добавляет свой платёж
  sum(amount) OVER (
    ORDER BY paid_at, payment_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS total_rows,
  -- скользящее среднее по семи последним строкам
  round(avg(amount) OVER (
    ORDER BY paid_at, payment_id
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 1) AS avg_last_7
FROM payments
WHERE paid_at < DATE '2026-06-15'
ORDER BY paid_at, payment_id;

Пропущенный день растягивает семидневное среднее на девять дней

Рамка ROWS BETWEEN 6 PRECEDING AND CURRENT ROW отсчитывает строки. Недельное окно отсчитывает дни. Пока в дневной витрине есть строка на каждую дату, эти два счёта совпадают — и расходятся они без всякой ошибки: GROUP BY создаёт строки из данных, а не из календаря, поэтому день, в который не пришло ни одного подходящего события, просто не появляется в ряду.

Возьмите выручку по дням в учебной базе. Первые оплаты приходят 6 июня, а 9 и 11 июня оплат нет: в витрине за 6–16 июня девять строк вместо одиннадцати. Для 16 июня рамка по строкам возьмёт семь последних: 8, 10, 12, 13, 14, 15 и 16 июня. Среднее — 836 / 7 = 119,4. Окно растянулось на девять календарных дней, но в подписи графика по-прежнему стоит «среднее за 7 дней».

Рамку можно задать и по значению даты: RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW возьмёт строки с датами от 10 до 16 июня. Границы стали честными, но внутрь попали только шесть существующих строк, и среднее получится 759 / 6 = 126,5. Это средняя выручка дня, в который были оплаты, — тоже осмысленное число, просто отвечающее на другой вопрос.

Третий вариант — достроить календарь generate_series, присоединить к нему оплаты и получить нули в пустых датах: (58 + 0 + 105 + 163 + 145 + 96 + 192) / 7 = 108,4. Три числа — 119,4, 126,5 и 108,4 — различаются не округлением, а определением «семи дней». Выбор между ними делается до запроса: если дыра означает «никто не платил», нужен календарь с нулями; если она означает «загрузка не сработала», публиковать нельзя ни одно из трёх, сначала чинится загрузка.

Второе место, где семидневное среднее не семидневное, — начало ряда. На второй дате окно состоит из двух строк, и выдавать это за недельное сглаживание нельзя. Дешёвая защита — count(*) over w: если в окне меньше семи значений, отдавайте NULL. В BI такая точка просто не нарисуется, и никто не будет объяснять падение первых дней месяца. В запросе так считается avg_7_calendar; с 18 июня, когда дыры уходят из окна, все три столбца совпадают.

with daily as (            -- витрина из данных: у дней без оплат строк нет
  select
    paid_at as day,
    sum(amount) as revenue
  from payments
  where paid_at < date '2026-07-01'
  group by paid_at
), by_rows as (
  select
    day,
    avg(revenue) over (order by day rows between 6 preceding and current row) as avg_7_rows,
    avg(revenue) over (order by day range between interval '6 days' preceding and current row) as avg_7_range
  from daily
), spine as (              -- календарь: строка на каждую дату
  select cast(d as date) as day
  from generate_series(timestamp '2026-06-06', timestamp '2026-06-30', interval '1 day') as g(d)
), filled as (
  select
    spine.day,
    coalesce(daily.revenue, 0) as revenue,
    by_rows.avg_7_rows,
    by_rows.avg_7_range
  from spine
  left join daily on daily.day = spine.day
  left join by_rows on by_rows.day = spine.day
)
select
  day,
  revenue,
  round(avg_7_rows, 1)  as avg_7_rows,
  round(avg_7_range, 1) as avg_7_range,
  case when count(*) over w = 7 then round(avg(revenue) over w, 1) end as avg_7_calendar
from filled
window w as (order by day rows between 6 preceding and current row)
order by day;

Скользящий WAU не складывается из дневных DAU

Среднее DAU за семь дней и число уникальных пользователей за те же семь дней — разные метрики, и вторая не получается из первой ни сложением, ни усреднением. DAU считает человека заново каждый день; скользящая аудитория обязана посчитать его один раз на всё окно.

Условный пример: из пяти пользователей за неделю один заходил три дня, один — все семь, один — два, двое — по одному. Сумма дневных DAU равна 3 + 7 + 2 + 1 + 1 = 14, уникальных пользователей — пять. В учебной базе за 17–23 августа сумма семи DAU — 3 501, уникальных пользователей — 2 045. Сумма завышает аудиторию в 1,7 раза, и тем сильнее, чем чаще люди возвращаются, то есть ровно в тех продуктах, где на эту метрику смотрят чаще всего.

Соблазн посчитать её рамкой окна упирается в ограничение PostgreSQL: count(distinct user_id) over (order by day rows between 6 preceding and current row) он не выполнит — DISTINCT в оконных функциях там не реализован. DuckDB, на котором работает песочница, DISTINCT в окне считает, но в PostgreSQL такой запрос не перенести. Это единственная из скользящих метрик в статье, которую рамка в PostgreSQL не умеет, и обходят её соединением: календарь соединяется с таблицей «пользователь × день» по диапазону, а уникальность считается обычным агрегатом на каждой дате окна.

У такого соединения есть цена: каждая дата тянет к себе семь дней активности, и промежуточный набор строк растёт примерно в семь раз. На дневной таблице «пользователь × день» это терпимо даже на миллионах строк, на сырых событиях — уже нет, поэтому слой активности собирают один раз и хранят. Когда окно становится тридцатидневным и данных десятки миллионов, считают приблизительно — через HyperLogLog или битовые маски по пользователям.

И последнее, из-за чего эту метрику чаще всего читают неверно: скользящее окно и календарная неделя не одно и то же. Rolling 7d на воскресенье совпадёт с WAU за неделю, rolling на среду — нет, он захватывает часть прошлой недели. Если в отчёте стоят оба числа, режим окна пишут прямо в названии колонки, иначе спор «почему у нас два разных WAU» повторяется каждый месяц. Первые шесть дат от начала данных при этом неполные по той же причине, что и у скользящего среднего; в запросе ряд начинается 10 августа, и все окна в нём полные.

with spine as (
  select cast(d as date) as day
  from generate_series(timestamp '2026-08-10', timestamp '2026-08-23', interval '1 day') as g(d)
), activity as (            -- зерно: пользователь + день
  select distinct
    cast(event_time as date) as day,
    user_id
  from events
)
select
  spine.day,
  count(distinct activity.user_id) as rolling_7d_users,
  count(activity.user_id)          as sum_of_dau_7d
from spine
left join activity
  on activity.day between spine.day - 6 and spine.day
group by spine.day
order by spine.day;

NTILE делит строки, а не величину метрики

NTILE(5) раскладывает упорядоченные строки по пяти корзинам примерно равного размера. Именно размер он и гарантирует — ничего про значения метрики внутри корзины функция не обещает. Это удобно, пока распределение плавное, и сбивает с толку, как только в базе есть большая масса одинаковых значений.

Учебная база такая и есть. В августе события есть у 4 109 пользователей, и на них приходится всего 14 разных значений: от одного события до шестнадцати. NTILE(5) обязан положить по 822 человека в первые четыре корзины и 821 в пятую, поэтому каждая граница режет группу одинаковых значений. Два события в августе у 695 человек: 168 из них попали в Q1, остальные 527 — в Q2. Двое пользователей с одинаковой активностью оказались в разных «сегментах», и разница между ними не в поведении, а в том, как сработал tie-breaker.

Верхняя корзина устроена наоборот. В Q5 попадают и те, у кого шесть событий, и тот, у кого шестнадцать: размер группы тот же, а диапазон внутри шире, чем у четырёх остальных корзин вместе. Называть такую корзину «power users» и считать по ней среднюю выручку можно только после того, как вы посмотрели её границы. Поэтому первый запрос после NTILE — не отчёт по сегментам, а min и max метрики в каждой корзине: он показывает, есть ли вообще разница между соседними группами.

Второе свойство, которое мешает переносить квинтили в CRM: границы живут не в правиле, а в данных. Пересчитал месяцем позже — порог между Q4 и Q5 сдвинулся, и часть людей «выросла» или «упала» без единого изменения в своём поведении. Для исследования это нормально, для триггерной рассылки — нет: там нужен либо фиксированный порог, либо гибрид, где квинтиль дополнен минимальным абсолютным требованием.

И формальность, без которой результат не воспроизводится: ORDER BY внутри NTILE должен содержать уникальное поле. Если сортировать только по числу действий, порядок внутри группы одинаковых значений не определён, и при повторном запуске человек может переехать из Q2 в Q3. Когда на 4 109 человек приходится 14 разных значений, это не редкий случай, а основной режим работы.

with monthly as (
  select
    user_id,
    count(*) as actions
  from events
  where event_time >= date '2026-08-01'
    and event_time <  date '2026-09-01'
  group by user_id
), bucketed as (
  select
    user_id,
    actions,
    ntile(5) over (order by actions, user_id) as quintile
  from monthly
)
select
  quintile,
  count(*)      as users,
  min(actions)  as actions_from,
  max(actions)  as actions_to
from bucketed
group by quintile
order by quintile;

PERCENTILE_CONT — упорядоченный агрегат, а не оконная функция

Перцентили в SQL выглядят как соседи оконных функций, но устроены иначе. У percentile_cont порядок живёт не в OVER, а в WITHIN GROUP (ORDER BY ...), и строки он схлопывает, как обычный агрегат. Поэтому он работает вместе с GROUP BY, а попытка дописать к нему рамку окна заканчивается ошибкой: PostgreSQL отвечает, что OVER для упорядоченного агрегата не поддерживается. Из-за этого привычный приём «посчитать метрику и рядом её скользящую версию одним запросом» для p95 не работает — нужен отдельный слой. В DuckDB оконная запись есть — quantile_cont(gap_hours, 0.95) over (...), — но PostgreSQL такой функции не знает.

Практическая разница с avg начинается там, где у распределения есть хвост. В учебной базе такая величина — пауза между соседними событиями пользователя, та самая, что даёт LAG. 29 августа у неё p50 50 часов и p95 502 часа: половина пауз — около двух суток и короче, каждая двадцатая — почти три недели и длиннее; среднее, 130 часов, не показывает ни того, ни другого. Между percentile_cont и percentile_disc выбирают по смыслу: первый интерполирует значение между наблюдениями и годится для SLA, второй возвращает реально существующее наблюдение — он нужен, когда по результату надо найти конкретный запрос или заказ.

Главное правило: перцентили не усредняются. Проверьте на двух условных днях. В первый день пять запросов с задержками 1, 1, 1, 1 и 9 секунд, медиана — 1. Во второй день 2, 2, 2, 8 и 8, медиана — 2. Среднее двух медиан даёт 1,5. А медиана объединённого набора из десяти значений 1, 1, 1, 1, 2, 2, 2, 8, 8, 9 равна 2. Та же арифметика ломает и недельный p95: он считается по всем наблюдениям недели, а не по семи дневным p95, и отличаться может в обе стороны, потому что дни с разным числом наблюдений весят в общем распределении по-разному. В учебной базе p95 паузы за неделю 17–23 августа — 431 час, а среднее семи дневных p95 — 413.

Отсюда рабочая схема: недельный или месячный перцентиль считается на всём наборе за период, а из дневных p95 строят только линию тренда — и подписывают её как сглаженный ряд дневных значений, а не как «p95 за неделю». Сглаживание здесь делает уже обычное оконное среднее поверх дневного слоя, и это единственная роль, в которой окно и перцентиль встречаются в одном запросе.

Рядом с перцентилем всегда держите размер базы — в запросе это столбец pauses. На сорока наблюдениях p95 — это одно-два значения, и любой всплеск сдвигает его целиком; having count(*) >= 200 в разрезе по сегментам дешевле, чем объяснять, почему метрика прыгает на редких группах. Отдельно решается, что вообще считается наблюдением: у латентности, например, смешивать успешные ответы с ретраями и таймаутами нельзя — у них разные распределения, и их объединение даёт число, за которым нет ни одного пользовательского сценария.

with gaps as (             -- окно на полном потоке: пауза до предыдущего события
  select
    cast(event_time as date) as day,
    extract(epoch from event_time - lag(event_time) over (
      partition by user_id order by event_time, event_id
    )) / 3600 as gap_hours
  from events
), daily as (              -- порядок живёт внутри within group, строки схлопываются как в обычном агрегате
  select
    day,
    count(*)                                                as pauses,
    percentile_cont(0.50) within group (order by gap_hours) as p50,
    percentile_cont(0.95) within group (order by gap_hours) as p95,
    avg(gap_hours)                                          as mean_hours
  from gaps
  where gap_hours is not null
    and day >= date '2026-08-01'
    and day <  date '2026-08-30'
  group by day
)
-- так нельзя: percentile_cont(0.95) within group (order by gap_hours) over (partition by day)
select
  day,
  pauses,
  round(cast(p50 as numeric))        as p50,
  round(cast(p95 as numeric))        as p95,
  round(cast(mean_hours as numeric)) as mean_hours,
  -- тренд: окно поверх дневного слоя, только по полной неделе
  case when count(*) over w = 7 then round(cast(avg(p95) over w as numeric)) end as p95_trend
from daily
window w as (order by day rows between 6 preceding and current row)
order by day;

Сквозной пример: путь пользователя от регистрации до оплаты

Соберём типичную задачу целиком на маленьком наборе: 196 пользователей учебной базы, которые зарегистрировались в первую неделю июня. Вопрос звучит так: сколько времени проходит от регистрации до первого создания рабочего пространства и до первого платежа, и у кого этот путь короче всего.

Без окон такую задачу решают через несколько подзапросов с min(). С окнами она разбивается на два понятных шага: пронумеровать события каждого пользователя по времени, оставить первые и посчитать разницу дат. Первый шаг — ровно ROW_NUMBER, второй — обычная арифметика.

Тонкость здесь одна, зато принципиальная: ROW_NUMBER обязан иметь детерминированную сортировку. Если у пользователя два события с одинаковым временем, без второго поля в ORDER BY результат будет меняться от запуска к запуску, и «первое действие» окажется случайным.

WITH first_activation AS (
  SELECT user_id, event_time,
         row_number() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS rn
  FROM events
  WHERE event_name = 'workspace_created'
),
first_payment AS (
  SELECT user_id, paid_at,
         row_number() OVER (PARTITION BY user_id ORDER BY paid_at, payment_id) AS rn
  FROM payments
)
SELECT
  u.user_id,
  u.channel,
  u.signup_date,
  CAST(a.event_time AS DATE)                       AS activated_on,
  p.paid_at                                        AS first_payment_on,
  CAST(a.event_time AS DATE) - u.signup_date       AS days_to_activation,
  p.paid_at - u.signup_date                        AS days_to_payment
FROM users u
LEFT JOIN first_activation a ON a.user_id = u.user_id AND a.rn = 1
LEFT JOIN first_payment    p ON p.user_id = u.user_id AND p.rn = 1
WHERE u.signup_date < DATE '2026-06-08'
ORDER BY days_to_payment NULLS LAST, u.user_id;

Как читать такой результат

В результате появляются четыре группы пользователей, и каждая означает своё. 33 человека с обеими датами прошли путь целиком — по ним считают время до денег. 88 человек с активацией без платежа находятся внутри воронки: их стоит смотреть отдельно, особенно если с регистрации прошло много дней. У 14 есть платёж без рабочего пространства: оплата не обязана идти через шаг, названный активацией. У 61 обе колонки пустые — они не дошли ни до пространства, ни до оплаты. days_to_activation либо пуст, либо равен нулю: в учебной базе пространство создают только в день регистрации.

Обратите внимание на NULLS LAST в сортировке: без него пользователи без платежа в PostgreSQL окажутся в конце при возрастании и в начале при убывании, и «топ самых быстрых» неожиданно начнётся с тех, кто не заплатил никогда.

И главное ограничение: разница дат отвечает на вопрос «сколько прошло», а не «почему». Если время до оплаты в одном канале вдвое больше, это повод посмотреть на онбординг и на обещание рекламы, а не вывод о качестве канала. Окна дают точную хронологию — объяснение по-прежнему остаётся работой аналитика.

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

Оконные функции опасны тем, что почти всегда что-то возвращают. Неверная сортировка не вызывает ошибку — она просто меняет ответ, и заметить это можно только по смыслу.

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

• В ORDER BY окна есть поле, разрывающее равенство, — иначе порядок недетерминирован.
• PARTITION BY перечисляет ровно те поля, которые вы называете группой словами.
• Для накопительных итогов рамка задана явно, если в сортировке возможны одинаковые значения.
• Ряд для lag и lead не имеет пропусков: отсутствующий день — это не «предыдущий», а дыра.
• Фильтр по результату окна стоит во внешнем запросе, а не в WHERE того же уровня.
• Результат сверен на одном пользователе, которого можно проверить руками.

Мини-практика и следующий шаг

Начните с таблицы events: добавьте ROW_NUMBER по user_id, затем оставьте первые события. После этого соберите дневной DAU и добавьте LAG. В финале попробуйте SUM OVER на payments и сравните накопительный итог с обычной суммой по дням.