Window functions — это инструмент, который окупает себя в первые же часы реальной работы. В этом уроке — семь паттернов, которые встретятся в любом дашборде, в любой аналитической задаче. Их стоит знать наизусть и узнавать в любой формулировке клиента.

Паттерн 1: Percent of total

Доля строки в общем итоге. Один из самых частых отчётов: «какие категории дают какой процент выручки».

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

Паттерн 2: Percent within category

Доля строки в её группе, а не в общей выборке. Например: «какую долю занимает каждый продукт в выручке своей категории».

Единственное отличие от первого паттерна — PARTITION BY category_id в окне. Деление всегда «своя строка / своя группа».

Паттерн 3: Top-N per group

Топ-3 самых дорогих продукта в каждой категории; последние 5 заказов каждого клиента; первый платёж каждого месяца.

Если хочешь «N лучших с допуском равных» — замени ROW_NUMBER на RANK или DENSE_RANK. Помни: окно нельзя в WHERE, поэтому фильтр rn <= N всегда идёт в обёрточном SELECT.

Паттерн 4: Gap-and-island — острова непрерывности

Это паттерн, который восхищает с первой встречи. Задача: «найди все непрерывные периоды активности» — например, дни подряд, когда клиент логинился. «Острова» — это полосы активных дней, «промежутки» — паузы между ними.

Трюк: для каждой строки вычислить разность ROW_NUMBER по последовательности и ROW_NUMBER по фактической дате. Эта разность одинакова для всех строк одного «острова» — потому что обе последовательности растут на 1 шагом. На границе острова появляется пропуск в одной из них, и разность скачет.

Этот трюк (Mo. Itzik Ben-Gan, 2003) — один из самых эффектных способов показать, что окна — это не «ещё одна фишка SQL», а алгоритмический инструмент.

Паттерн 5: Retention cohorts

Сколько клиентов из когорты «зарегистрировались в январе» вернулись и купили в феврале, марте, и так далее. Это базовая SaaS-метрика.

Идея: для каждого клиента посчитать его первую активность (=когорта) и привязать остальные активности к этой когорте через окно.

В реальных дашбордах когорту обычно собирают по месяцу регистрации (date_trunc('month', signup_date)), а активность — по покупкам, и считают сводку «N клиентов из когорты M были активны на M+k месяце». Здесь — упрощённая суть.

Паттерн 6: Time-to-event / time-between-events

Сколько прошло между двумя событиями? Между логином и checkout? Между заказами одного клиента?

EXTRACT(EPOCH FROM interval) превращает interval в секунды — иначе AVG над interval в PostgreSQL не работает напрямую везде. Делением на 86400 переводим в дни.

Паттерн 7: Deduplication — оставить только одну строку из дублей

Часто в реальных данных есть дубли: одна транзакция записана дважды из-за бага, или один логин логирован несколько раз. Удалить дубли по бизнес-ключу — задача, которая идеально решается через ROW_NUMBER.

Этот же паттерн используется при импорте данных: «по бизнес-ключу оставь только последнюю версию». Меняешь ORDER BY paid_at ASC на DESC — получаешь latest вместо earliest.

Бонус: совмещение нескольких паттернов

Реальные задачи редко решаются одним окном. Сложный отчёт — это связка из 2-3 окон на разных уровнях. Покажу пример: для каждого клиента — его топ-1 платёж, % этого платежа в его лайфтайм-выручке, и ранг этого клиента в общем списке по лайфтайм-выручке.

Окна вложены — но не друг в друга, а через CTE. Каждый CTE — один уровень окна. Это и есть способ строить любую аналитику любой сложности.

Что унести из всего модуля 8

Window functions — это, пожалуй, самый практический инструмент в курсе. Главные пункты:

• Окно сохраняет строки, GROUP BY коллапсирует. Это семантическое различие, не оптимизация.
• OVER (PARTITION BY ... ORDER BY ... frame) — три необязательных компонента. Каждый меняет смысл функции.
• Дефолтный frame с ORDER BY — running от начала до текущей. Без ORDER BY — вся партиция.
• ROWS — по позиции; RANGE — по значению. Различие критично на дублях ORDER BY-ключа.
• LAST_VALUE и NTH_VALUE почти всегда требуют явного frame UNBOUNDED ... UNBOUNDED.
• Окно не работает в WHERE, GROUP BY, HAVING — фильтр по результату окна делается через CTE.
• Агрегат внутри окна — OK. Окно внутри агрегата — нет (нужен CTE).
• Семь паттернов: percent of total/in category, top-N per group, gap-and-island, cohorts, time-to-event, deduplication. Это уже 80% реальных задач аналитика.

В следующем модуле — рекурсивные CTE: то самое, что позволяет «гулять» по иерархиям (категории-подкатегории, менеджеры-подчинённые, дерево комментариев). Window function + recursive CTE — это уже почти полный арсенал middle-аналитика.