Дельты, Jinja и HTML в сводных таблицах SuperSet

В шестой версии 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"> &#9650 </span>')
  WHEN {{ measure }} < 0
    THEN concat('<span class="background-red">',{{ measure_print }}, '</span>','<span style="color: red"> &#9660 </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"> &#9660 </span>')
  WHEN {{ measure }} > 0
    THEN concat('<span class="background-red">',{{ measure_print }}, '</span>','<span style="color: red"> &#9650 </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 блока)