В шестой версии SuperSet появилась возможность прописывать HTML код в метриках и измерениях сводных таблиц, что значительно расширило приемы для BI-разработчиков по форматированию таблиц. Покажу как в SuperSet можно реализовать сводную таблицу со сменой периодов и выводом красивой дельты.
Сделаем сводную таблицу, в которой можно будет выводить и скрывать дельты по показателям.
Для этого за вывод дельты у нас будет отвечать фильтр, который назовем SHOW_DELTA
SELECT ‘Да’ AS SHOW_DELTA UNION ALL SELECT ‘Нет’
По-умолчанию выставим значение ‘Да’
Чтобы не прописывать для каждого показателя расчет дельты, применим цикл {% for %} … {% endfor %}, но сначала создадим переменную measures_calc, где первым значением идет расчет метрики, вторым — название метрики.
{# переменная с расчетом метрик индекс 0 — расчет метрики, 1 — как называем ее в скрипте #} {% set measures_calc = { ‘Продажи, шт’: [«sum(SALE)»,»sale_qnty»], ‘Потери, шт’: [«sum(LOST)», «lost_qnty»], ‘Потери, %’: [«divide(toFloat32(sum(LOST)),sum(SALE))»,»lost_share»] } %} {% set partition_region_frmt = «OVER (partition by region, frmt ORDER BY period ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)» %} {# рассчитываем метрику и дельту через оконную функцию #} {% set measure_value %} {% for value in measures_calc %} {{ measures_calc.get(value)[0] }} as {{ measures_calc.get(value)[1] }}, {% if filter_values(‘SHOW_DELTA’)[0] == ‘Да’ %} {{ measures_calc.get(value)[1] }} — anyOrNull({{ measures_calc.get(value)[1] }}) {{ partition_region_frmt }} as {{ measures_calc.get(value)[1] }}_delta , {% endif %} {% endfor %} {% endset %}
Согласно логике, прописанной в переменной measure_value, вид запроса по продажам, будет иметь следующий вид.
sum(SALE) as sale_qnty, sale_qnty — anyOrNull(sale_qnty) OVER (partition by region, frmt ORDER BY period ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) as sale_qnty_delta
Таким приемом очень удобно пользоваться. Если у вас добавляется новый показатель в таблицу, добавляете его в переменную measures_calc, а дальше цикл сделает все за вас. Останется только добавить новый показатель на чарт.
Теперь соберем сам датасет. Циклы и переменные разбирались в статье «Динамические таблицы», так же выведем итоги и подытоги, которые были ранее разобраны в статье. В данном случае используем способ с UNION.
{% set table = «public.mytable» %} {% set dims_days = «dims.days» %} {% set dims_whs = «dims.whs» %} {# переменная- словарь измерений #} {# 0 — для переменной PERIOD, 1 — для where_day #} {% set day_table = { ‘День’: [«DAY_ID»,»toUInt64(concat(toUnixTimestamp(DAY_ID),’000′))»], ‘Неделя’: [«WEEK»,»WEEK»], ‘Месяц’: [«MONTH»,»MONTH»], ‘Год’: [«YEAR»,»YEAR»], } %} {% set whs_table = { ‘Регион’: «REGION», ‘Формат’: «FORMAT» } %} {# переменная с расчетом метрик индекс 0 — расчет метрики, 1 — как называем ее в скрипте #} {% set measures_calc = { ‘Продажи, шт’: [«sum(SALE)»,»sale_qnty»], ‘Потери, шт’: [«sum(LOST)», «lost_qnty»], ‘Потери, %’: [«divide(toFloat32(sum(LOST)),sum(SALE))»,»lost_share»] } %} {# переменная периода, которая зависит от выбора значения пользователем на дашборде #} {% set PERIOD %} {{ day_table.get(filter_values(‘PERIOD’)[0] or ‘Неделя’)[0] }} {% endset %} {% set partition_region_frmt = «OVER (partition by region, frmt ORDER BY period ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING)» %} {# рассчитываем метрику и дельту через оконную функцию #} {% set measure_value %} {% for value in measures_calc %} {{ measures_calc.get(value)[0] }} as {{ measures_calc.get(value)[1] }}, {% if filter_values(‘SHOW_DELTA’)[0] == ‘Да’ %} {{ measures_calc.get(value)[1] }} — anyOrNull({{ measures_calc.get(value)[1] }}) {{ partition_region_frmt }} as {{ measures_calc.get(value)[1] }}_delta , {% endif %} {% endfor %} {% endset %} {# формируем where по дням, таблица партицирована по DAY_ID, поэтому фильтры выставленные по неделям, месяцам и т.д. прячем в подзапрос и из справочника вытаскиваем нужные дни #} {% set where_day %} {% for value in filter if value.col in day_table %} {% if loop.last %} AND DAY_ID IN ( SELECT DAY_ID FROM {{ dims_days }} WHERE 1=1 {% for value in filter if value.col in day_table %} AND {{ day_table.get(value.col)[1] }} IN {{ filter_values(value.col,remove_filter=true)|where_in }} {% endfor %}) {% endif %} {% endfor %} {% endset %} {# формируем where по магазинам. В источнике WHS_ID является PRIMARY KEY, поэтому выбор пользователей региона и формата, прячем в подзапрос и из справочника магазинов вытаскивать нужные WHS_ID #} {% set where_whs %} {% for value in filter if value.col in whs_table %} {% if loop.last %} AND WHS_ID IN (SELECT WHS_ID FROM {{ dims_whs }} WHERE 1=1 {% for value in filter if value.col in whs_table %} AND {{ whs_table.get(value.col) }} IN {{ filter_values(value.col,remove_filter=true)|where_in }} {% endfor %}) {% endif%} {% endfor %} {% endset %} /*remove_filter прописываем, чтобы фильтры дополнительно не накладывались на запрос, с вводились только внутрь там, где они нужны*/ {#****************************************#} {#**************Запрос********************#} {#****************************************#} {# определяем максимальную дату в источнике #} {# Если вы знаете, что у вас стабильно есть данные например за вчера или за сегодня, этот шаг можно пропустить #} WITH MAX_DATE_TABLE AS ( SELECT MAX(DAY_ID) AS MAX_DATE FROM {{ table }} WHERE DAY_ID >= CURDATE()-14 ) {# по умолчанию при открытии дашборда, пока не выставлено пользовательских фильтров, ограничиваемся двумя периодами #} {% if filter_values(‘День’)|length == 0 and filter_values(‘Неделя’)|length == 0 and filter_values(‘Месяц’)|length == 0 and filter_values(‘Год’)|length == 0 %} , DATE_TABLE AS ( SELECT DAY_ID FROM {{ dims_days }} WHERE {{ PERIOD }} IN ( SELECT {{ PERIOD }} FROM {{ dims_days }} WHERE 1=1 AND DAY_ID <= (SELECT MAX_DATE FROM MAX_DATE_TABLE) GROUP BY {{ PERIOD }} ORDER BY {{ PERIOD }} DESC LIMIT 2 ) ) {% else %} , DATE_TABLE AS ( SELECT DAY_ID FROM {{ dims_days }} WHERE DAY_ID <= (SELECT MAX_DATE FROM MAX_DATE_TABLE ) {{ where_day }} ) {% endif %} /**********************/ SELECT {{ PERIOD }} AS period, CONCAT(‘ㅤㅤ’,{{ dimension_value[‘Регион’] }}) AS region, CONCAT(‘ㅤㅤ’,{{ dimension_value[‘Формат’] }}) AS frmt, {{ measure_value }} 1 FROM {{ table }} WHERE 1=1 AND DAY_ID GLOBAL IN (SELECT DAY_ID FROM DATE_TABLE) {{ where_whs }} GROUP BY period,region,frmt /***итог по региону************/ UNION ALL SELECT {{ PERIOD }} AS period, CONCAT(‘ㅤㅤ’,{{ dimension_value[‘Регион’] }}) AS region, ‘ㅤИтог’ AS frmt, {{ measure_value }} 1 FROM {{ table }} WHERE 1=1 AND DAY_ID GLOBAL IN (SELECT DAY_ID FROM DATE_TABLE) {{ where_whs }} GROUP BY period,region,frmt /***итог по компании, не добавляем переменную where_whs************/ UNION ALL SELECT {{ PERIOD }} AS period, ‘ㅤИтог по компании’ AS region, » AS frmt, {{ measure_value }} 1 FROM {{ table }} WHERE 1=1 AND DAY_ID GLOBAL IN (SELECT DAY_ID FROM DATE_TABLE) GROUP BY period,region,frmt
Требования к дельте: окрашиваем фон в зависимости от динамики, а так же добавляем стрелку соответствующей росту или падению
Чтобы провести такое форматирование будем добавлять html код в расчет показателя. Для Продаж, если дельта положительная, присваиваем class = “background-green”, при отрицательной дельте присваиваем class = “background-red”. Так же добавляем соответствующую стрелку после показателя.
{% set measure = «toFloat32(sum(sale_qnty_delta))» %} {% set measure_print = «replaceOne(printf(‘%.1f’,»~ measure ~»), ‘.’, ‘,’)» %} {% if filter_values(‘SHOW_DELTA’)[0] == ‘Да’ %} CASE WHEN {{ measure }} >= 0 THEN concat(‘<span class=»background-green»>’,{{ measure_print }}, ‘</span>’,'<span style=»color: green»> ▲ </span>’) WHEN {{ measure }} < 0 THEN concat(‘<span class=»background-red»>’,{{ measure_print }}, ‘</span>’,'<span style=»color: red»> ▼ </span>’) ELSE {{ measure_print }} END {% else %} NULL {% endif %}
Для Потерь, % форматирование противоположное, при росте потерь class = “background-red”
{% set measure = «toFloat32(sum(lost_share_delta))» %} {% set measure_print = «replaceOne(printf(‘%.2f%%’,»~ measure ~» * 100), ‘.’, ‘,’)» %} {% if filter_values(‘SHOW_DELTA’)[0] == ‘Да’ %} CASE WHEN {{ measure }}<= 0 THEN concat(‘<span class=»background-green»>’,{{ measure_print }}, ‘</span>’,'<span style=»color: green»> ▼ </span>’) WHEN {{ measure }} > 0 THEN concat(‘<span class=»background-red»>’,{{ measure_print }}, ‘</span>’,'<span style=»color: red»> ▲ </span>’) ELSE {{ measure_print }} END {% else %} NULL {% endif %}
Собираем сводную таблицу
Чтобы действие классов присвоенных в формулах к дельтам сработало, в CSS добавим следующий код
/****зеленый фон ячеек****/ td:has(.background-green) { background: #F2F7E9 !important; } /****розовый фон****/ td:has(.background-red) { background: #FBEEEC !important ; }
В итоге таблица будет иметь вид. По умолчанию выводятся 2 последние недели с выводом дельты
Если в фильтре «Вывести дельту» выставить — Нет, дельта скроется
Итого, в данной сводной можно менять период, сравнивать необходимые периоды, а так же при необходимости убирать дельты
Можно вывести больше двух периодов, и для каждого посчитается дельта
Эту идею можно развивать и дальше. Например, можно не выводить сразу все показатели, а дать пользователю возможность выводить в таблицу только те метрики, которые ему нужны для анализа. Можно убрать мультивыбор даты, и при выборе одной недели, всегда сравнивать с предыдущей.
*БД ClickHouse, SuperSet 6.0
**все данные, представленные в таблицах, выдуманы
*** полный код для сводной таблицы написан здесь (кроме html блока)
Всем творчества и идей!
Загрузка...
