Что это и зачем нужно

Window-функции — это SQL-инструмент который позволяет выполнять агрегацию НЕ теряя строк. Обычный GROUP BY собирает данные в группы и теряет отдельные строки. Window-функция сохраняет все строки, но добавляет колонку с агрегатом «над окном» для каждой строки.

В реальной работе это самый частый инструмент middle-аналитика после JOIN-ов. Если ты на Glovo считаешь время между двумя заказами клиента — это LAG(). Если на Каспи нумеруешь топ продавцов по выручке — ROW_NUMBER(). Если на Halyk строишь running balance по счёту — SUM() OVER.

Базовый пример

Задача: для каждого заказа клиента показать его порядковый номер и суммарный накопленный к этому заказу.

SELECT
  customer_id,
  order_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS order_num,
  SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS running_total
FROM orders
WHERE customer_id IN (101, 102);

Результат:

customer | order_id | amount | order_num | running_total
   101   |    A1    |   100  |     1     |     100
   101   |    A2    |   250  |     2     |     350
   101   |    A3    |   300  |     3     |     650
   102   |    B1    |  1000  |     1     |    1000
   102   |    B2    |   500  |     2     |    1500

Анатомия запроса:

OVER(...) — превращает обычную функцию в window-функцию
PARTITION BY customer_id — разделяет данные на «окна» (по customer_id)
ORDER BY created_at — упорядочивает внутри окна
Без PARTITION BY — окно = вся таблица. Без ORDER BY — порядок не определён (но для агрегатов это часто нормально)

Тонкости и pitfalls

1. Frame Clause: ROWS vs RANGE

Когда есть ORDER BY — PostgreSQL по умолчанию использует RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Это значит «все строки от начала окна до значения текущей строки».

Проблема: если у двух строк одинаковый created_at, RANGE считает их обе в один шаг — running total «прыгает».

-- ❌ Если две покупки в одну секунду — обе пойдут в одну запись running total
SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at)

-- ✅ Явно ROWS — по физическим строкам, стабильно
SUM(amount) OVER (PARTITION BY customer_id ORDER BY created_at
                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

В Каспи был incident: running balance прыгал на пиковые секунды (1000 транзакций/сек) из-за дефолтного RANGE.

2. ROW_NUMBER vs RANK vs DENSE_RANK

При одинаковых значениях в ORDER BY они работают по-разному:

данные: A=100, B=100, C=80, D=70

ROW_NUMBER  → 1, 2, 3, 4   (порядок недетерминирован между A и B)
RANK        → 1, 1, 3, 4   (одинаковый ранг, потом пропуск)
DENSE_RANK  → 1, 1, 2, 3   (одинаковый ранг, без пропуска)

Какой использовать на «топ-3 продавцов»? Зависит от семантики:

Хочешь ровно 3 строки → ROW_NUMBER ≤ 3
Хочешь всех с топ-3 рангом (при равенстве — обоих) → RANK ≤ 3

3. LAG / LEAD и NULL для крайних строк

SELECT
  user_id,
  event_time,
  event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS gap
FROM events;

Для первого события каждого пользователя LAG вернёт NULL → разница тоже NULL. Можно дать дефолт:

LAG(event_time, 1, '2026-01-01'::timestamp) OVER (...)

4. Производительность

Window-функции не бесплатны. Каждое окно — sort + scan. Если в запросе 5 разных OVER — может быть 5 сортов.

Совет: если несколько window-функций над одним окном — выноси в CTE с одним сортом:

WITH ordered AS (
  SELECT *, ROW_NUMBER() OVER (...) AS rn, SUM(...) OVER (...) AS s
  FROM orders
)
SELECT ... FROM ordered WHERE rn = 1;

5. Window + filter в одном уровне = ошибка

-- ❌ ОШИБКА: window не работает в WHERE
SELECT * FROM orders
WHERE ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) = 1;

Нужна обёртка через CTE или subquery:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS rn
  FROM orders
) t WHERE rn = 1;

Когда НЕ применять

Простой агрегат всей таблицы — SELECT SUM(amount) FROM orders быстрее чем SELECT amount, SUM(amount) OVER ().

Top-N per group в огромных таблицах — иногда LATERAL JOIN + LIMIT N эффективнее ROW_NUMBER:

SELECT c.id, o.*
FROM customers c
LEFT JOIN LATERAL (SELECT * FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1) o ON true;

На колоночных БД (ClickHouse, BigQuery) — там агрегации часто быстрее window-функций из-за storage layout.

Кейс из казахстанского бизнеса

В Каспи Магазин был отчёт «сколько дней в среднем между покупками клиента». Junior-аналитик написал так:

SELECT customer_id, AVG(diff) FROM (
  SELECT o1.customer_id,
         EXTRACT(EPOCH FROM (o2.created_at - o1.created_at))/86400 AS diff
  FROM orders o1
  JOIN orders o2 ON o2.customer_id = o1.customer_id AND o2.created_at > o1.created_at
) t GROUP BY 1;

Запрос работал 2 часа на 50M заказов — self-join создавал квадратичный объём строк (для клиента с 100 заказами получалось 100²/2 = 5000 пар).

Middle-аналитик переписал через window:

WITH gaps AS (
  SELECT customer_id,
         EXTRACT(EPOCH FROM (created_at -
           LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)
         )) / 86400 AS days_gap
  FROM orders
)
SELECT customer_id, AVG(days_gap) AS avg_days_between
FROM gaps
WHERE days_gap IS NOT NULL
GROUP BY customer_id;

Время выполнения: 8 секунд.

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

Window-функции: от ROW_NUMBER до running totals