Рассмотрим следующую задачу - пусть имеются 2 таблицы, которые необходимо соединить по общим полям. Если уровни детализации (гранулярность) у обеих таблиц совпадают, то всё просто, используем встроенную опцию JOIN в моделях. Сложности начинают возникать, если уровни детализации разные.
Классический пример: таблица фактов продаж с детализацией по дням и таблица плана с детализацией по месяцам:.
При применении операции JOIN значение плана по месяцу раскидывается по каждому дню месяца в таблице фактов. После этого при построении визуализаций с группировкой по месяцу мы получаем значение плана за месяц, помноженное на количество дней, относящихся к этому месяцу, вместо ожидаемого значения (красный столбец). Как же получить верное значение плана (зеленый столбец)? Давайте разбираться.
Способ 1. Равномерно разнести величину месячного плана по дням. Сделать это мы можем в модели с помощью SQL-блока. В SQL-блок помещаем таблицы факта и плана, соединенные операцией JOIN между собой.
В нашем примере соединяем по полям Регион, Месяц и Год.
Если в первоначальном датасете отсутствуют поля Месяц и Год, то предварительно извлекаем их из поля Период, например, с помощью 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
с окном, построенным по полям Регион, Месяц и Год – тех самых полей, по которым мы соединяли таблицы факта и плана.
Теперь осталось только синхронизировать модель и можем смело строить виджеты с отображением плана через агрегирующую функцию Сумма
и не волноваться, что получим неожиданные результаты.
Способ 2. Вариаций разнесения плана может быть много в зависимости от задачи. Возможные варианты – поместить величину плана в последний день месяца (или первый день месяца), приравняв нулю значения во всех остальных строках. В этом случае столбец с планом будет вычисляться с помощью следующего запроса (в случае отнесения плана на последний день месяца):
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)
Заключение
В итоге мы рассмотрели несколько способов, как данные с разными уровнями детализации объединять в единой витрине и получать визуализации с корректными результатами. Эти принципы можно распространить для решения своих рабочих задач.