Заполнение пропусков в результатах статистических временных рядов

Результаты некоторых статистических расчётов будут иметь пропуски, поскольку информация за конкретные дни не сохранялась. Но вместо того, чтобы заполнять эти пустоты кодом приложения, можно перестроить запрос к базе данных: В качестве источника для присоединения к статистическим данным создаётся последовательность значений без пропусков. Для PostgreSQL для создания последовательности можно использовать функцию generate_series, в то время как для MySQL это необходимо делать вручную с помощью рекурсивного общего табличного выражения (CTE).

Использование

MySQL

WITH RECURSIVE dates_without_gaps(day) AS (
  SELECT DATE_SUB(CURRENT_DATE, INTERVAL 14 DAY) as day
  UNION ALL
  SELECT DATE_ADD(day, INTERVAL 1 DAY) as day
  FROM dates_without_gaps
  WHERE day < CURRENT_DATE
)
SELECT dates_without_gaps.day, COALESCE(SUM(statistics.count), 0)
FROM dates_without_gaps
LEFT JOIN statistics ON(statistics.day = dates_without_gaps.day)
GROUP BY dates_without_gaps.day;

PostgreSQL

SELECT dates_without_gaps.day, COALESCE(SUM(statistics.count), 0)
FROM generate_series(
  CURRENT_DATE - INTERVAL '14 days',
  CURRENT_DATE,
  '1 day'
) as dates_without_gaps(day)
LEFT JOIN statistics ON(statistics.day = dates_without_gaps.day)
GROUP BY dates_without_gaps.day;

Подробное объяснение

Многие приложения содержат статистически интересную информацию, такую как регистрация пользователей, количество созданных статей в блоге или продаж. Для целей отчётности эти ряды часто обобщаются по определённым временным интервалам, таким как дни, недели или месяцы. В результате получается временной ряд, показывающий изменение значений за равные промежутки времени. Однако во временном ряду отсутствуют некоторые значения, когда в одном или нескольких периодах не было никаких событий. Если эти данные отобразить на графике, то график будет вводить в заблуждение, поскольку, например, за январём будет непосредственно следовать апрель.

Разрывы в данных традиционно устраняются в приложении. Но при этом увеличивается объем многословного кода, поскольку обработка временных интервалов должна быть скопирована из базы данных, иногда с дополнительными сложностями, связанными с настраиваемыми пользователем временными интервалами и периодами. Программисту приходится разрабатывать две различные реализации статистического обобщения: одну для построения запроса и вторую для заполнения пропусков. Наиболее простым решением было бы создание одного SQL-запроса, выдающего результаты без разрывов.

Идея заключается в том, чтобы изменить рабочий процесс, заполнив разрывы до вычисления статистики. Создаётся отношение с именем dates_without_gaps, которое содержит все даты, доступные после расчёта, если бы данные не содержали пропущенных значений. С помощью этого отношения статистические данные могут быть соединены со ссылкой на временной интервал, так что в результате не будет никаких пропусков, если в некоторые дни не было данных. Построение этого временного отношения не представляет сложности при работе с PostgreSQL: Функция generate_series создаёт все данные на основе интервала и начальной вместе с конечной точкой. В то время как для MySQL это необходимо делать вручную, используя рекурсивное общее табличное выражение (CTE).

Дополнительные ресурсы

• MySQL Documentation: An introduction to Recursive Common Table Expressions.
• PostgreSQL documentation: The generate_series function.
• Constantin' Blog: Filling months without bike rides following this approach.