Заплатки для LAG/LEAD

Привет, Хабр.

Сегодня рассмотрим, как разреженный временной ряд ломает оконные функции (LAG, LEAD, SUM OVER и др.), посмотрим как находить дыры и заполнять их, чтобы метрики не сыпались и строки не дублировались.

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

LAG, LEAD, SUM() OVER (…), ROW_NUMBER() рассчитываются поверх упорядоченной и непрерывной последовательности строк. Если между датами возникает пробел, нужной строки нет в буфере, и функция возвращает NULL. Стандарт SQL допускает директиву IGNORE NULLS, однако в PostgreSQL она пока не реализована , следовательно, любая дыра в календаре немедленно проявляется в результате.

Пример:

WITH daily_balance AS (   SELECT * FROM (VALUES     ('2025-04-20'::date, 100),     ('2025-04-22',        150) -- пропущено 21-е   ) AS t(trn_date, balance) ) SELECT   trn_date,   balance,   balance - LAG(balance) OVER (ORDER BY trn_date) AS diff FROM daily_balance;

Поле diff за 22 апреля содержит NULL, хотя при непрерывном ряде ожидалось +50.

Последствия пропусков

Диагностика неполного календаря

Хороший метод — сгенерировать эталонный календарь через generate_series и выполнить анти-соединение (anti-join):

WITH calendar AS (   SELECT gs::date AS d   FROM   generate_series('2025-04-01','2025-04-30','1 day') AS gs ), gaps AS (   SELECT c.d   FROM   calendar c   LEFT   JOIN daily_balance b ON b.trn_date = c.d   WHERE  b.trn_date IS NULL ) SELECT d FROM gaps ORDER BY d;

Результат — строгий список потерянных дат, который можно отправлять в мониторинг.

Стратегии заполнения промежутков

generate_series + LEFT JOIN

Ежедневная сетка — простое выравнивание баланса.

WITH calendar AS (   SELECT gs::date AS d   FROM   generate_series('2025-04-01', '2025-04-30', '1 day') gs ) SELECT   c.d,   COALESCE(b.balance, 0) AS balance FROM calendar c LEFT JOIN daily_balance b USING (d) ORDER BY c.d;

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

WITH months AS (   SELECT date_trunc('month', gs)::date AS month   FROM   generate_series('2024-01-01', '2024-12-01', '1 month') gs ), m_balance AS (   SELECT date_trunc('month', trn_date) AS month,          SUM(amount) AS gmv   FROM   payments   GROUP  BY 1 ) SELECT m.month,        COALESCE(gmv, 0)                                              AS gmv,        SUM(COALESCE(gmv,0)) OVER (ORDER BY m.month)                  AS gmv_cum FROM   months m LEFT   JOIN m_balance USING (month) ORDER  BY m.month;

Индексация: CREATE INDEX ON payments (date_trunc('month', trn_date));

Постоянная таблица dim_date

-- Однократное создание CREATE TABLE dim_date AS SELECT gs::date                        AS day,        date_trunc('week',  gs)::date   AS week_start,        date_trunc('month', gs)::date   AS month_start FROM   generate_series('2010-01-01','2030-12-31','1 day') gs;  ALTER TABLE dim_date ADD PRIMARY KEY (day); CREATE INDEX ON dim_date (month_start); 

Ночной крон-апдейт (пример на psql + cron):

psql -c " INSERT INTO dim_date SELECT gs::date, date_trunc('week', gs), date_trunc('month', gs) FROM generate_series(         (SELECT max(day)+1 FROM dim_date),         current_date,         '1 day') gs ON CONFLICT (day) DO NOTHING;"

Использование во всех отчётах:

SELECT d.day, COALESCE(p.gmv,0) AS gmv FROM   dim_date d LEFT   JOIN payment_agg p ON p.trn_date = d.day WHERE  d.day BETWEEN '2025-01-01' AND '2025-01-31';

Планировщик больше не считает generate_series, индексы готовы заранее.

LATERAL-соединение для мелких интервалов

Задача: проверить, был ли трафик в каждом 10-минутном окне.

WITH slots AS (   SELECT gs AS slot_start,          gs + interval '10 minutes' AS slot_end   FROM generate_series(          '2025-04-10 00:00',          '2025-04-10 23:50',          '10 minutes'        ) gs ) SELECT   s.slot_start,   COALESCE(t.hits, 0) AS hits FROM   slots s LEFT   JOIN LATERAL (          SELECT COUNT(*) AS hits          FROM   web_log w          WHERE  w.event_ts >= s.slot_start            AND  w.event_ts <  s.slot_end        ) t ON TRUE ORDER  BY s.slot_start;

Обязательно: CREATE INDEX ON web_log (event_ts);. LATERAL даёт один индекс-lookup на окно вместо джойна «многие-к-многим».

Чек-лист:

Используйте нужный шаблон — и ни один пропуск больше не испортит агрегаты.

Предотвращение повторного счёта

Календарь умноженный на фактические данные = потенциальное умножение строк, если на дату приходится более одной записи. Избыточные строки искажают итоговые агрегаты.

Способы устранения

1. COUNT(DISTINCT …) — просто, но ресурсоёмко.

2. Предагрегация до соединения:

   WITH payment_agg AS (   SELECT trn_date, SUM(amount) AS gmv   FROM payments   GROUP BY trn_date ) SELECT c.d, COALESCE(p.gmv, 0) AS gmv FROM calendar c LEFT JOIN payment_agg p ON p.trn_date = c.d;

3. Оконная фильтрация: ROW_NUMBER() OVER (PARTITION BY d ORDER BY …) = 1, если требуется одна строка на дату.

Ретеншен с неполными логами

WITH activity AS (   SELECT user_id,          date_trunc('month', signup_date)   AS cohort_month,          date_trunc('month', activity_date) AS activity_month   FROM   user_activity ) SELECT cohort_month,        EXTRACT(MONTH FROM activity_month) -        EXTRACT(MONTH FROM cohort_month) + 1 AS month_n,        COUNT(DISTINCT user_id) AS active_users FROM activity GROUP BY 1,2 ORDER BY 1,2;

При отсутствии логов за месяц февраль смещается, ошибочно попадая в колонку month_n = 1.

Исправленный вариант:

WITH months AS (   SELECT date_trunc('month', gs)::date AS month   FROM   generate_series('2024-01-01','2024-12-01','1 month') gs ), signups AS (   SELECT user_id,          date_trunc('month', signup_date) AS cohort_month   FROM   user_activity   WHERE  event = 'signup' ), cross AS (   SELECT s.user_id, s.cohort_month, m.month AS activity_month   FROM   signups s   JOIN   months  m ON m.month >= s.cohort_month ), hits AS (   SELECT DISTINCT user_id,          cohort_month,          date_trunc('month', activity_date) AS activity_month   FROM   user_activity   WHERE  event = 'activity' ) SELECT   c.cohort_month,   (EXTRACT(YEAR  FROM c.activity_month) - EXTRACT(YEAR  FROM c.cohort_month))*12 +   (EXTRACT(MONTH FROM c.activity_month) - EXTRACT(MONTH FROM c.cohort_month)) + 1     AS month_n,   COUNT(DISTINCT c.user_id) FILTER (WHERE h.user_id IS NOT NULL) AS active_users FROM cross c LEFT JOIN hits h        ON h.user_id = c.user_id       AND h.activity_month = c.activity_month GROUP BY 1,2 ORDER BY 1,2;

Календарное развертывание сохраняет правильный номер месяца, даже если данные за период отсутствуют.

Кумулятивная выручка:

WITH calendar AS (   SELECT gs::date AS d   FROM generate_series('2025-01-01','2025-01-31','1 day') gs ), payments_agg AS (   SELECT trn_date, SUM(amount) AS daily_gmv   FROM payments   GROUP BY trn_date ), aligned AS (   SELECT c.d,          COALESCE(p.daily_gmv, 0) AS daily_gmv   FROM calendar c   LEFT JOIN payments_agg p ON p.trn_date = c.d ) SELECT d,        SUM(daily_gmv) OVER (ORDER BY d) AS gmv_cumulative FROM aligned ORDER BY d;

Непрерывный календарь гарантирует, что кумулятивная метрика не пропускает дни и не залипает на предыдущем значении.

---

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

• 12 мая 20:00
  Формирование бизнес-модели продукта на примере Business Model Canvas. Узнать подробнее

• 21 мая 20:00
  Выявление и анализ проблем в бизнес-процессах. Узнать подробнее