Аналитик Brew собирает дашборд «выручка по дням». Две колонки заказал лично владелец: «дельта день-к-дню» — насколько касса сегодня выросла или просела относительно вчера, и «сглаженный тренд за 3 дня» — чтобы не пугаться разовых всплесков. Первую он считает через «значение предыдущей строки», вторую — через «среднее по трём строкам назад». На неделе с 1 по 7 февраля всё выглядит гладко — пока кто-то не замечает, что 6 и 7 февраля «тренд за 3 дня» в двух разных версиях отчёта показывает разные числа. Одна версия даёт 146.67, другая — 175.00. Цифры разъезжаются ровно после 5 февраля, а 5 февраля кофейня не работала: выходной, строки за этот день в таблице просто нет.

Дыра в ряду — вот где «три строки назад» перестаёт значить «три дня назад». Этот юнит про то, как Postgres отвечает на вопрос «какие строки попадают в окно», и почему ответов на самом деле два.

lag и lead — соседи по окну

lag(cents) возвращает значение cents из предыдущей строки окна, lead(cents) — из следующей. «Предыдущая» и «следующая» определяются ORDER BY внутри OVER (...): упорядочили по day — значит соседи это вчера и завтра. Дельта день-к-дню тогда просто cents - lag(cents).

У краёв ряда соседа нет: у самой первой строки нет предыдущей, поэтому lag там NULL; у самой последней нет следующей — NULL у lead. Это не ошибка, а честный ответ «слева/справа пусто». В query.sql мы приводим результат к text и через coalesce подменяем NULL на '—', чтобы в Go прилетал чистый string, а не nullable-тип, который пришлось бы разворачивать на каждой строке.

Оконный фрейм — какие строки считаются «вокруг текущей»

Когда внутри OVER (...) стоит агрегат (avg, sum, count), он считается не по всему окну, а по фрейму — подмножеству строк относительно текущей. Фрейм можно задать двумя способами, и в этом весь сюжет.

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW — это «текущая строка и две физические строки перед ней», ровно три строки в порядке ORDER BY. Считаются именно строки; что у них в day и есть ли между ними разрывы — фрейму всё равно.

RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW — это «все строки, чья дата попадает в диапазон [день−2, день]». Здесь считается не количество строк, а попадание значения ORDER BY в окно. Если какой-то день в этом диапазоне отсутствует в таблице, его и нет в расчёте — фрейм для такой строки просто сужается.

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

Та самая дыра 5 февраля — на одной картинке. Касса в этот день не работала, строки за него в таблице нет:

   01.02   02.02   03.02   04.02   ·····   06.02   07.02
    100     120      90     150    (нет)    200     110
                                     ↑ дыра в ряду
 
  Текущая строка — 06.02. «Три дня назад» два фрейма понимают по-разному:
   ROWS  2 PRECEDING    → 3 строки подряд: 03, 04, 06       → avg(90, 150, 200) = 146.67
   RANGE '2 days' PREC.  → даты в окне [04.02 … 06.02]: 04, 06 → avg(150, 200)    = 175.00

ROWS отсчитывает строки и перешагивает дыру, не заметив её; RANGE отсчитывает по дате и потому теряет 05 февраля, которого нет, — окно сужается. Та же развилка в сводке:

Что показывает наш код

Сердце урока — query.sql. Первый запрос строит «день-к-дню» через lag/lead:

-- name: DayOverDay :many
SELECT
    day::text AS day,
    cents,
    coalesce((lag(cents) OVER (ORDER BY day))::text, '—')            AS prev,
    coalesce((cents - lag(cents) OVER (ORDER BY day))::text, '—')    AS delta,
    coalesce((lead(cents) OVER (ORDER BY day))::text, '—')           AS next
FROM daily_revenue_lab
ORDER BY day;

Второй считает одно и то же скользящее среднее «текущий день и два предыдущих» двумя фреймами сразу, чтобы их можно было поставить в соседние колонки:

-- name: MovingAverage :many
SELECT
    day::text AS day,
    cents,
    round((avg(cents) OVER (ORDER BY day ROWS  BETWEEN 2 PRECEDING AND CURRENT ROW))::numeric, 2)::text AS ma_rows,
    round((avg(cents) OVER (ORDER BY day RANGE BETWEEN INTERVAL '2 days' PRECEDING AND CURRENT ROW))::numeric, 2)::text AS ma_range
FROM daily_revenue_lab
ORDER BY day;

schema.sql заводит лабораторную таблицу daily_revenue_lab (дата + центы) с намеренным пропуском 5 февраля — базовые таблицы Brew с тремя заказами для ровного временного ряда не годятся, поэтому таблица своя, а базовые таблицы не трогаем. cmd/demo/main.go — тонкий: открывает пул, вызывает DayOverDay и MovingAverage, печатает обе таблицы через tabwriter. Среднее завёрнуто в round(..., 2), поэтому текст детерминирован и совпадает с тем, что вставлено в ## Запуск ниже.

Запуск

docker compose up -d
make lecture L=08-analytical-and-lateral/08-03-lag-lead-and-frames T=db-reset
make lecture L=08-analytical-and-lateral/08-03-lag-lead-and-frames

T=run — цель по умолчанию, поэтому второй вызов её и запускает. Изнутри каталога юнита те же шаги короче: make db-reset, затем make run.

1) lag/lead — день-к-дню (prev/next = '—', где соседа нет):
ДЕНЬ        выручка  вчера  дельта  завтра
2025-02-01  100      —      —       120
2025-02-02  120      100    20      90
2025-02-03  90       120    -30     150
2025-02-04  150      90     60      200
2025-02-06  200      150    50      110
2025-02-07  110      200    -90     —
 
2) Скользящее среднее за 3 дня — ROWS vs RANGE (расходятся после пропуска 05.02):
ДЕНЬ        выручка  ma_rows  ma_range
2025-02-01  100      100.00   100.00
2025-02-02  120      110.00   110.00
2025-02-03  90       103.33   103.33
2025-02-04  150      120.00   120.00
2025-02-06  200      146.67   175.00
2025-02-07  110      153.33   155.00
   → 06 и 07 февраля: ROWS берёт 3 строки подряд, RANGE — только даты в окне 2 дней (05 нет).

В первой таблице видно работу краёв: у 1 февраля вчера и дельта это '—' (предыдущей строки нет), у 7 февраля '—' стоит в завтра. В остальном дельта = выручка − вчера ровно так, как заказывал владелец: +20, −30, +60, +50, −90.

Вторая таблица — та самая развилка. До 4 февраля включительно ma_rows и ma_range совпадают: ряд ровный, дыры ещё нет. А вот после пропуска 5 февраля они расходятся. На 6 февраля ma_rows = avg(90, 150, 200) = 146.67 — это три подряд идущие физические строки (03, 04, 06 февраля), ROWS про даты не думает. А ma_range = avg(150, 200) = 175.00 — это только даты, попавшие в окно [04, 06]: 4 и 6 февраля, потому что 5 февраля в таблице нет. На 7 февраля та же история: ma_rows = avg(150, 200, 110) = 153.33 (три строки: 04, 06, 07), а ma_range = avg(200, 110) = 155.00 — окно [05, 07], но 5 февраля пропущено, так что в расчёт попали только 6 и 7. Аналитик считал «среднее за 3 дня», а ROWS дал ему «среднее за 3 строки» — на неделе с выходным это разные вещи.

Заборчик

• ROWS слеп к промежуткам: «две строки назад» — это две строки назад, даже если между ними провал в неделю. RANGE считает по значению ORDER BY и потому корректен на дырявом ряде. Практическое правило: «скользящее среднее за календарные N дней» в финансах и аналитике почти всегда хотят как RANGE по дате — чтобы выходные и пропуски не раздували окно лишними строками; ROWS уместен там, где важна позиция (последние 3 события, что бы ни стояло в датах).
• RANGE платит за корректность: ему нужен сортируемый тип в ORDER BY (дата, число, timestamp — не любой), и работает он дороже, потому что границы окна для каждой строки ищет по значению, а не по счётчику строк.
• Сюрприз новичка — фрейм по умолчанию. ORDER BY в OVER (...) без явного фрейма — это RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Поэтому sum(...) OVER (ORDER BY ...) из 08-01 уже давал running total, хотя про фреймы мы тогда не говорили. Привыкни проверять: если агрегат поверх ORDER BY ведёт себя как накопительный, а ты этого не закладывал — сработал умолчательный RANGE UNBOUNDED PRECEDING.
• Твой DBA смотрит на фрейм как на цену: на больших рядах оконный агрегат — это сортировка по ORDER BY плюс буфер строк под фрейм, и цена видна в плане (EXPLAIN, модуль 06) отдельным узлом WindowAgg. Память под буфер и выбор «держать в work_mem или слить на диск» — его забота, не запроса.

Что забрать с собой

lag/lead — это соседи по окну: предыдущая и следующая строка в порядке ORDER BY, а на краях ряда соседа нет и приходит NULL (мы подменили его на '—'). Оконный фрейм решает, какие строки попадают в агрегат вокруг текущей: ROWS считает физические строки, RANGE считает по значению ORDER BY. На ровном ряде они дают одно и то же, но на дырах расходятся — и «среднее за 3 дня» почти всегда хотят как RANGE. И помни про дефолт: ORDER BY без явного фрейма — это RANGE UNBOUNDED PRECEDING, то есть незаметный running total.

Пока что мы навигировали по строкам, которые лежат рядом в одном плоском ряду. Но данные бывают вложенными: дерево категорий, цепочка «заказ → возврат → перевыставление», иерархия сотрудников. Чтобы пройтись по такой структуре вглубь, окон уже не хватает — нужен запрос, который ссылается сам на себя. Следующий юнит, 08-04, как раз про рекурсивные CTE: как WITH RECURSIVE обходит дерево уровень за уровнем и где у такого обхода предохранитель от бесконечного цикла.