С версии 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
Всем идей и хорошего настроения!
