Приветствую участников нашего форума 
Часто в анализе данных чтобы описать показатель мы пользуемся сравнением с каким-нибудь другим показателем, например с планом или прошлым месяцем. И называем это “Отклонение”, то есть отклонение от плана или от прошлого месяца. Например Магазин №1 за март продал на 200 товаров больше, чем за февраль, значит отклонение составило +200. Отклонение может быть выражено в абсолютных и в относительных значениях. В этой статье я расскажу как можно быстро обогатить датасет в dremio, чтобы вывести на диаграмму показатель отклонения от предыдущего месяца.
-
Для примера возьмем датасет, содержащий данные о продажах в разрезах партнеров и номенклатуры. С помощью функции date_part создадим расчетный столбец с номером месяца и года.
-
На основе датасета из предыдущего шага, создадим датасет с агрегацией. Для этого просуммируем значения продаж по году, месяцу, а также по партнерам и номенклатуре.
Условие where было задано только для упрощения восприятия данного примера. Когда мы уберем данное условие, то отклонение рассчитается на весь датасет. -
Также на основе предыдущего датасета проделаем следующие действия. С помощью функции left join присоединим нашу таблицу к самой себе, но сдвинем на 1 месяц назад. На следующем скриншоте, к значению за февраль 2021г. присоединилось значение за январь 2021г. Создаем вычисляемый столбец, в котором рассчитываем “дельту” - разницу между значением за текущий месяц и значением за прошлый месяц.
Обращаю внимание, что предыдущего значение (за декабрь 2020г.) в системе не было, поэтому в нашем примере пустое значение. -
Теперь у нас есть датасет, в котором к каждому месяцу есть соответствующее значение отклонения от предыдущего месяца.
Подключим этот датасет к комбинированной диаграмме. Укажем в качестве измерения - номер месяца. В качестве метрики для столбцов (баров) - сумму по столбцу amount, то есть сумма продаж.
В конструкторе Insight есть возможность добавлять не только основную, но и дополнительную подпись к точкам и барам. Поэтому в основной подписи мы покажем сумму продаж за текущий месяц. А в дополнительной подписи выведем сумму продаж за предыдущий месяц, то есть сумму по столбцу “delta” - сумма отклонения. Также мы можем задать цвет по условию для этой подписи, на примере у нас отрицательные значения подкрашены красным, положительные - зеленым.
Обращаю внимание, что на скриншоте в некоторых столбцах отсутствует дополнительная подпись. Это означает, что для данных столбцов в нашем датасете отсутствуют значения за предыдущий месяц. В конструкторе есть возможность пропускать пустые значения, но мы их оставили для наглядности примера.
В примере я создавал новый датасет на каждом шаге, используя уровневую структуризацию хранения в Dremio. Подробнее об этом можно прочитать на нашей wiki в разделе “Структуризация хранения” ссылка.
Но для удобства можно объединить все датасеты в один с помощью оконной функции. Привожу пример данного кода:
WTH dataset AS (
SELECT “period”, date_part(‘month’, “period”) as month_num, date_part(‘year’, “period”) as year_num, “id_partner”, “id_nomenclature”, “amount”
FROM facts
), dataset_aggr AS (
SELECT “month_num”,
“year_num”,
“id_partner”,
“id_nomenclature”,
SUM(“amount”) as amount
FROM dataset
WHERE “year_num” >= ‘2021’ and “id_partner” = ‘444’ and “id_nomenclature” = ‘6’
GROUP BY “month_num”, “year_num”, “id_partner”, “id_nomenclature”
ORDER BY “year_num”, “month_num”
)
SELECT
t1.“month_num”,
t1.“year_num”,
t1.“id_partner”,
t1.“id_nomenclature”,
t1.“amount”,
t2.“month_num” as month_num_last,
t2.“year_num” as year_num_last,
t2.“amount” as amount_last,
t1.“amount” - t2.“amount” as delta
FROM dataset_aggr t1
LEFT JOIN dataset_aggr t2 on t1.“month_num” = t2.“month_num” + 1
AND t1.“year_num” = t2.“year_num” AND t1.“id_partner” = t2.“id_partner” AND t1.“id_nomenclature” = t2.“id_nomenclature”