С версии Superset 6.0 в метрики сводных таблиц можно подставлять произвольный HTML. Это открывает возможности, которые раньше казались недоступными — например, карточки KPI прямо в ячейках Pivot Table.

Я уже описывала различные фишки, которые можно реализовывать в сводных таблицах: здесь, здесь и здесь.

В данной статье продолжу раскрывать потенциал сводных таблиц.

Заказчик пришел с запросом вывести показатели в карточках и прислал пример, как должен выглядеть результат.

Выглядело это примерно так:

Что мы имеем: двумерная сетка - регион по строкам, карточки городов в колонках. Первое, что приходит в голову , — это использовать Handlebars.

Собрать матрицу в шаблоне можно, но тогда приходится вручную раскладывать плоский список через CSS grid: следить за шириной ряда и разбивкой по регионам. Работает, пока карточек немного и макет не меняется. А вот масштабировать такую раскладку — отдельная история. Я решила пойти другим путем и попробовать отрисовать такие карточки в сводной таблице. Сводная таблица — готовая матрица: ячейка «регион × город» существует сама по себе, и остаётся положить в неё карточку.

В строках — регион, внутри карточки — город и значения двух показателей. Вроде всё понятно, ничего сложного: объединить название города с метриками и вложить в одну ячейку — сложности нет. А вот как расставить карточки по ячейкам? Если добавить город в измерения, таблица выглядит совсем не так, как хотелось бы. В таблице пропуски, а нам так не надо.

Я уже думала, что ничего из этого не выйдет, но тут вдруг появилась идея отранжировать города внутри каждого региона и уже этот номер добавлять в столбцы.

И вот что получилось. Ура.

Главное решение было найдено, остальное дело за малым — собрать метрики в одной ячейке и придать красивый вид с помощью html и css.

Вот так выглядит сборка метрики для вывода данных в одной ячейке с применением html и css. Эта метрика будет прописана в SQL запросе, который представлен ниже.

CONCAT( '<div>',
            '<span class = "city-name">', city ,'</span>',           
            '<div class = "card_metric">',
              '<div>', 
                CASE
                  WHEN sale_qnty_delta >= 0 
                    THEN '<span class = "point background-green"></span>'
                    ELSE '<span class = "point background-red"></span>'
                END,
                '<span class = "name_metric"> Продажи, шт: </span>', 
                  CASE 
                    WHEN sale_qnty_delta >= 0 
                     THEN CONCAT('<span class="metric">',printf('%.0f',toFloat32(sale_qnty)), '</span>',
                     '<span class = "delta background-green" >▴', replaceOne(printf('%.2f%%',abs(sale_qnty_delta_perc) * 100), '.', ','),'</span>')
                     ELSE CONCAT('<span class="metric">',printf('%.0f',toFloat32(sale_qnty)), '</span>',
                     '<span class = "delta background-red" > ▾', replaceOne(printf('%.2f%%',abs(sale_qnty_delta_perc) * 100), '.', ','),'</span>') 
                  END,
              '</div>',
              '<div>',
                CASE 
                  WHEN lost_share_delta <= 0 
                    THEN '<span class = "point background-green"></span>'
                    ELSE '<span class = "point background-red"></span>'
                END,
                '<span class = "name_metric"> Потери, %: </span>',   
                  CASE 
                    WHEN lost_share_delta <= 0 
                      THEN CONCAT('<span class="metric">',replaceOne(printf('%.2f%%',lost_share * 100), '.', ','), '</span>',
                      '<span class = "delta background-green">▾', replaceOne(printf('%.2f%%',abs(lost_share_delta) * 100), '.', ','),'</span>')
                      ELSE CONCAT('<span class="metric">',replaceOne(printf('%.2f%%',lost_share * 100), '.', ','), '</span>',
                      '<span class = "delta background-red"> ▴', replaceOne(printf('%.2f%%',abs(lost_share_delta) * 100), '.', ','),'</span>')                    
                  END,
              '</div>',
           '</div>
        </div>'
    ) AS metric_print,

На первый взгляд сборка показателя кажется сложной, но ниже выделила блоки из которых все складывается. В моем примере 2 показателя, но их может быть больше, в зависимости от вашей задачи.

В текущем кейсе показатели выводятся в разрезе недели и дельта считается к предыдущей неделе. Но можно подобно тому как было разобрано в статье, сделать динамически меняющийся период сравнения.

А вот и весь запрос к БД ClickHouse.

{% set table = "public.mytable" %}
{% set dims_days = "dims.days" %}

{% set whs_table = {
  'Регион': "REGION",
  'Город': "CITY"
  }
%}

{# переменная с расчетом метрик индекс 0  - расчет метрики, 1  - как называем ее в скрипте #}
{% set measures_calc = {
  'Продажи, шт': ["sum(SALE)/280","sale_qnty"],
  'Потери, %': ["divide(toFloat32(sum(SALE_MARKDOWN)/130),sum(SALE)/280)","lost_share"]
  }
%}

{% set partition_region_frmt = "OVER (partition by region, city 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] }}, 
    anyOrNull({{ measures_calc.get(value)[1] }}) {{ partition_region_frmt }} as  {{ measures_calc.get(value)[1] }}_prev,
    {{ measures_calc.get(value)[1] }}-{{ measures_calc.get(value)[1] }}_prev as {{ measures_calc.get(value)[1] }}_delta,
    divide(toFloat32({{ measures_calc.get(value)[1] }}),{{ measures_calc.get(value)[1] }}_prev) - 1 as {{ measures_calc.get(value)[1] }}_delta_perc,
  {% endfor %}
{% endset %}

{% set measure_value_2 %}
  {% for value in measures_calc %}
    {{ measures_calc.get(value)[1] }}, 
    {{ measures_calc.get(value)[1] }}_delta,
    {{ measures_calc.get(value)[1] }}_delta_perc,
  {% endfor %}
{% endset %}


/***************Запрос***********************/
{# определяем максимальную дату в источнике #}
/* Если вы знаете, что у вас стабильно есть данные например за вчера или за сегодня, 
 этот шаг можно пропустить */
WITH MAX_DATE_TABLE AS (
SELECT 
    MAX(DAY_ID) AS MAX_DATE
FROM  {{ table }}
WHERE DAY_ID >= CURDATE()-14
)
/*берём 2 последних недели с данными*/
, DATE_TABLE AS (
SELECT 
    DAY_ID, WEEK_ID_2, WEEK_ISO_NUMBER
FROM {{ dims_days }}
WHERE WEEK_ID_2 IN (
    SELECT
        WEEK_ID_2
    FROM {{ dims_days }}
    WHERE 1=1
        AND DAY_ID <= (SELECT MAX_DATE FROM MAX_DATE_TABLE)
    GROUP BY WEEK_ID_2
    ORDER BY WEEK_ID_2 DESC
    LIMIT 2
    )
)
SELECT
  (SELECT CONCAT(max(WEEK_ISO_NUMBER),' нед к ', min(WEEK_ISO_NUMBER),' нед')  FROM DATE_TABLE) as period_name,
  region,
CONCAT( '<div>',
            '<span class = "city-name">', city ,'</span>',           
            '<div class = "card_metric">',
              '<div>', 
                CASE
                  WHEN sale_qnty_delta >= 0 
                    THEN '<span class = "point background-green"></span>'
                    ELSE '<span class = "point background-red"></span>'
                END,
                '<span class = "name_metric"> Продажи, шт: </span>', 
                  CASE 
                    WHEN sale_qnty_delta >= 0 
                     THEN CONCAT('<span class="metric">',printf('%.0f',toFloat32(sale_qnty)), '</span>',
                     '<span class = "delta background-green" >▴', replaceOne(printf('%.2f%%',abs(sale_qnty_delta_perc) * 100), '.', ','),'</span>')
                     ELSE CONCAT('<span class="metric">',printf('%.0f',toFloat32(sale_qnty)), '</span>',
                     '<span class = "delta background-red" > ▾', replaceOne(printf('%.2f%%',abs(sale_qnty_delta_perc) * 100), '.', ','),'</span>') 
                  END,
              '</div>',
              '<div>',
                CASE 
                  WHEN lost_share_delta <= 0 
                    THEN '<span class = "point background-green"></span>'
                    ELSE '<span class = "point background-red"></span>'
                END,
                '<span class = "name_metric"> Потери, %: </span>',   
                  CASE 
                    WHEN lost_share_delta <= 0 
                      THEN CONCAT('<span class="metric">',replaceOne(printf('%.2f%%',lost_share * 100), '.', ','), '</span>',
                      '<span class = "delta background-green">▾', replaceOne(printf('%.2f%%',abs(lost_share_delta) * 100), '.', ','),'</span>')
                      ELSE CONCAT('<span class="metric">',replaceOne(printf('%.2f%%',lost_share * 100), '.', ','), '</span>',
                      '<span class = "delta background-red"> ▴', replaceOne(printf('%.2f%%',abs(lost_share_delta) * 100), '.', ','),'</span>')                    
                  END,
              '</div>',
           '</div>'
          ) AS metric_print,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY city) as RN,
  {{ measure_value_2 }}
  1
FROM (  
  SELECT 
    WEEK_ID_2 AS period,
    {{ whs_table['Регион'] }} AS region,
    {{ whs_table['Город'] }} AS city,
    {{ measure_value }}
    1
  FROM {{ table }}
  WHERE 1=1
    AND DAY_ID GLOBAL IN (SELECT DAY_ID FROM DATE_TABLE)  
  GROUP BY period, region, city
 ) 
 WHERE period = (SELECT max(WEEK_ID_2) FROM DATE_TABLE) /*оставляем только последнюю неделю*/

Собираем сводную.

В строки - регион, в колонки - период и номер, в метрики - показатель, который прописываем также с использованием html и CSS кода.

Цвет рамки карточки зависит от зон показателей:

Показатели	Рамка карточки
Оба в зелёной зоне	Зелёная
Один в красной зоне	Жёлтая
Оба в красной зоне	Красная

{% set measure_1 = "sum(sale_qnty_delta)" %}
{% set measure_2 = "sum(lost_share_delta)" %}  
{% set measure_print = "any(metric_print)"%}
    CASE 
        WHEN {{ measure_1 }} >= 0 and {{ measure_2 }} <= 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #188A42 !important;">', {{ measure_print }}, '</div>')
        WHEN {{ measure_1 }} >= 0 and {{ measure_2 }} > 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #F1C232 !important">', {{ measure_print }}, '</div>')
         WHEN {{ measure_1 }} < 0 and {{ measure_2 }} <= 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #F1C232 !important">', {{ measure_print }}, '</div>')    
        WHEN {{ measure_1 }} < 0 and {{ measure_2 }} > 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #D93025 !important;">',{{ measure_print }}, '</div>')
        ELSE {{ measure_print }}
    END

Остается для всех присвоенных классов прописать CSS код в настройках дашборда.

CSS код приводится для чарта с названием "demo. region_card", не забудь поменять на свой, либо совсем убрать, тогда код применится ко всем сводным:

.city-name {
  font-size: 14px;  
  font-weight: 600; 
  color: #4A4A4A;
}

.point {
  display: inline-block;
  width: 6px; 
  height: 6px; 
  border-radius: 50%;
  margin-right: 6px; 
  vertical-align: middle; 
} /*общие параметры маркера*/

.point.background-red {
background-color: #D93025; 
} /*красный маркер перед метрикой*/

.point.background-green {
background-color: #188A42; 
} /*зеленый маркер перед метрикой*/

.metric  {
  padding: 1px 6px;
  font-size: 14px !important;
  font-weight: 600;
  color: black; 
}/*параметры метрики*/

.delta  {
  display: inline-block;
  font-size: 12px !important;
  border-radius: 10px;
  padding: 1px 10px;
  white-space: nowrap;
  min-width: 70px;
}/*параметры дельты*/

.delta.background-red {
color: #D93025; 
background-color: rgba(217, 48, 37, 0.09) !important;
}

.delta.background-green {
color: #188A42;
background-color: rgba(24, 138, 66, 0.10);
}

.card_dc {
  border-radius: 10px; 
  border: 2px solid #dbdbdb !important ;
  padding: 10px;
  margin: 5px 0px;
  box-shadow: 0 1px 2px rgba(16,24,40,.05), 0 8px 24px rgba(16,24,40,.07)
}

.card_metric > div {
 display: flex;
 justify-content: space-between;
 align-items: center;
 width: 100%;
} /*прижимаем значения к правому и левому краю */

.name_metric {
  display: inline-block;
  color: grey;
  text-align: left !important;
}

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable {
 width:65% !important;
 background-color:  white;
} /*задаем динамическую ширину сводной таблицы*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable tr td.pvtVal {
  text-wrap: nowrap!important; /*убираем перенос*/
  text-align: justify !important;
}  /*параметры для значений сводных таблиц*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable,
div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable td , 
div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable th {
  border: none !important;
} /*убираем все границы*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable tr th.pvtColLabel {
   font-size: 0px !important; 
} /*убираем заголовок сверху*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable tr:nth-of-type(2) th.pvtColLabel {
   font-size: 16px !important; 
   font-weight: 600;
} /*возвращаем заголовок с номерами недель*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable tr th.pvtRowLabel {
   font-size: 16px !important; 
   font-weight: 600;
   text-wrap: wrap !important;
   padding: 0px;
   vertical-align: middle; 
} /*название регионов*/

div[data-test-chart-name="demo. region_card"] .pivot_table_v_2 table.pvtTable tr th.pvtAxisLabel{ 
  font-size: 0px;
  border-color: white;
  width: 0%;
  padding: 0px !important;
  } /*убираем названия измерений слово metric и уменьшаем ширину*/

.header-title { 
 font-size: 0px;
 padding: 0px;
} /*убираем заголовки у всех чартов*/

И вот что у нас получается после всех манипуляций. Красота!

Уже смотрится хорошо, но хочется дополнительно выделить карточки, где оба показателя в красной зоне.

Добавим градиент для карточек с красной рамкой

{% set measure_1 = "sum(sale_qnty_delta)" %}
{% set measure_2 = "sum(lost_share_delta)" %}  
{% set measure_print = "any(metric_print)"%}
    CASE 
        WHEN {{ measure_1 }} > 0 and {{ measure_2 }} <= 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #188A42 !important;">', {{ measure_print }}, '</div>')
        WHEN {{ measure_1 }} > 0 and {{ measure_2 }} > 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #F1C232 !important">', {{ measure_print }}, '</div>')
         WHEN {{ measure_1 }} <= 0 and {{ measure_2 }} <= 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #F1C232 !important">', {{ measure_print }}, '</div>')    
        WHEN {{ measure_1 }} <= 0 and {{ measure_2 }} > 0
            THEN concat('<div class = "card_dc" style = "border-top: 4px solid #D93025 !important;background:linear-gradient(180deg, rgba(217,48,37,0.10) 0%, rgba(217,48,37,0) 45%), #ffffff;">',{{ measure_print }}, '</div>')
        ELSE {{ measure_print }}
    END

Теперь проблемные места лучше считываются при просмотре дашборда.

Заказчик получил нужный визуал, а я — код, который легко поддерживать и в который просто вносить изменения.

*БД ClickHouse, SuperSet 6.0

Всем идей и хорошего настроения!

Возможности сводных таблиц в Superset