2025-05-14 11:50 Сбор данных

Объединение таблиц факта и плана с разной детализацией

Рассмотрим распространённую задачу: необходимо объединить две таблицы по общим полям. Если уровни детализации (гранулярность) совпадают — используем стандартный JOIN в модели, никаких сложностей. Проблемы начинаются, если детализация различается.

Типичный пример — таблица фактов продаж с дневной детализацией и таблица плана с детализацией по месяцам.

Если выполнить JOIN напрямую, месячное значение плана будет повторяться для каждого дня месяца. Это приводит к некорректной агрегации: при группировке по месяцам значение плана умножается на количество дней месяца — вместо ожидаемого итога отображается завышенное значение (см. красный столбец на графике).

Чтобы получить корректный результат (зелёный столбец), нужно предварительно обработать данные.

Разберём два подхода.

Способ 1. Равномерное распределение плана по дням

Наиболее универсальный метод — распределить значение месячного плана равномерно по дням. Это можно реализовать с помощью SQL-блока внутри модели.

Что делаем:

Объединяем таблицы факта и плана через JOIN по полям Регион, Год, Месяц.

Если в таблице фактов нет полей Месяц и Год, извлекаем их из даты (Период) функциями MONTH(period) и YEAR(period) — либо используем соответствующий диалект СУБД.

Далее пишем SQL-запрос в блоке:

SELECT period_fact as period
    ,month_fact as month
    ,year_fact as year
    ,region_fact as region
    ,sum_fact
    ,count_fact
    ,sum_plan
    ,sum_plan / COUNT(period_fact) OVER(PARTITION BY region_fact, month_fact, year_fact) as plan_uniform
FROM child

Здесь plan_uniform — равномерно распределённое значение месячного плана по дням. Мы делим план на количество строк (дней) в том же месяце, регионе и году, используя оконную функцию COUNT.

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

Способ 2. Распределение плана по ключевой дате

В зависимости от задачи план можно привязать не ко всем дням, а только к одной дате — например, к последнему дню месяца. Остальным строкам присваиваем 0.

Пример SQL-выражения:

CASE
    WHEN period_fact = MAX(period_fact) OVER(PARTITION BY region_fact, month_fact, year_fact)
    THEN sum_plan
    ELSE 0
END

Вариации по детализации

Не всегда ключом детализации является только период. Например, таблица фактов может быть детализирована по сотрудникам и товарной группе, а в плане — только по региону и периоду. В таком случае общими остаются Регион и Период.

Формула для равномерного распределения плана по строкам может выглядеть так:

sum_plan / COUNT(*) OVER(PARTITION BY region_fact, period_fact)

Таким образом, мы рассмотрели несколько рабочих подходов к объединению таблиц с разной детализацией и корректному представлению плана в витринах. Эти принципы позволяют избежать ошибок в визуализациях и обеспечить достоверность аналитики.

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