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

Примеры и гайды
,
1

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

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

image image

При применении операции JOIN значение плана по месяцу раскидывается по каждому дню месяца в таблице фактов. После этого при построении визуализаций с группировкой по месяцу мы получаем значение плана за месяц, помноженное на количество дней, относящихся к этому месяцу, вместо ожидаемого значения (красный столбец). Как же получить верное значение плана (зеленый столбец)? Давайте разбираться.

image

:white_check_mark: Способ 1. Равномерно разнести величину месячного плана по дням. Сделать это мы можем в модели с помощью SQL-блока. В SQL-блок помещаем таблицы факта и плана, соединенные операцией JOIN между собой.

image

В нашем примере соединяем по полям Регион, Месяц и Год.

image

Если в первоначальном датасете отсутствуют поля Месяц и Год, то предварительно извлекаем их из поля Период, например, с помощью SQL-запроса к источнику: 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, который содержит равномерно распределенное значение месячного плана по дням, полученное по формуле:

\frac{План\ за\ месяц}{Кол-во\ дней\ (строк)\ в\ месяце}

Количество дней в месяце считаем с помощью оконной функции COUNT с окном, построенным по полям Регион, Месяц и Год – тех самых полей, по которым мы соединяли таблицы факта и плана.

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

:white_check_mark: Способ 2. Вариаций разнесения плана может быть много в зависимости от задачи. Возможные варианты – поместить величину плана в последний день месяца (или первый день месяца), приравняв нулю значения во всех остальных строках. В этом случае столбец с планом будет вычисляться с помощью следующего запроса (в случае отнесения плана на последний день месяца):

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

:white_check_mark: Вариации задачи. Детализация в таблице фактов не всегда может быть по периоду, как в рассмотренном примере. Предположим, ваша таблица фактов имеет детализацию по сотрудникам и товарной группе (при этом в плане эти уровни отсутствуют). И общие поля в обеих таблицах – период и регион. Тогда формула для подсчета равномерного разнесения плана по строкам примет вид:

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

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

2 лайка