badcasedaily1 8 часов назад

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

5 мин

256

Обзор

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

Сегодня рассмотрим, как разреженный временной ряд ломает оконные функции (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.

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

Затронутая функция	Наблюдаемое поведение	Риск
`LAG`, `LEAD`	`NULL` вместо значения	Цепочки `NULL` искажают сравнение периодов
`SUM() OVER`	Прерывистая кумулятивная линия	Тренд визуально проседает
`ROW_NUMBER`, `RANK`	Непредсказуемые разрывы нумерации	Сложность интерпретации позиции в рядах

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

Хороший метод — сгенерировать эталонный календарь через 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 на окно вместо джойна «многие-к-многим».

Чек-лист:

Приём	Когда применять	Ключевой индекс
`generate_series` + `LEFT JOIN`	быстрые одноразовые развертки	не нужен
`dim_date`	повседневные отчёты, BI	PK(day), (month_start)
`LATERAL` на `generate_series`	интервалы < 1 ч, неравные шаги	`(timestamp)`

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

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

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

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

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

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

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;

Оконная фильтрация: 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
Выявление и анализ проблем в бизнес-процессах. Узнать подробнее

Теги:

Хабы: