Superset умеет строить сводные таблицы, но при выводе подытогов и итогов (subtotals/total) в сводных таблицах с иерархически структурированной метрикой сталкиваешься с ограничением: из коробки можно строить только простые метрики. А если нужна процентная метрика с корректными подытогами и итогами — приходится искать обходные решения.
В этой статье разберём, как обойти это ограничение на уровне обращения к БД (ClickHouse).
Я нашла два подхода: UNION и массивы. И начну с первого, детально разобрав решение именно через UNION.
Разберем на примере расчета показателя Потери, %
Потери, % = Потери, шт/Продажи, шт
Если мы построим итоги и подытоги стандартным функционалом SuperSet,
то в результате получим не совсем то, что нужно.
В подытогах/итогах сложатся уже рассчитанные значения по форматам, что не является корректным.
Как обойти это ограничение я покажу на примере ClickHouse — у PostgreSQL тот же приём может не сработать
Считаем показатель для каждого уровня:
Дата-Регион-Формат
Дата-Регион /*подытог*/
Дата /*Итог*/
И далее "схлопываем" через UNION
Вид запроса:
/*рассчитываем до Дата-Регион- Формат*/ SELECT DAY_ID AS PERIOD, REGION, FORMAT AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем подытог Дата-Регион, вместо формата прописываем слово «Итог» */ UNION ALL SELECT DAY_ID ASPERIOD, REGION, 'Итог' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» */ UNION ALL SELECT DAY_ID AS PERIOD, 'Итог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3
Нюансы. Когда мы прописываем «Итог» в Select-е, то поле имеет формат String, и если поле, которое вы объединяете имеет формат Int, например, то его надо преобразовать toString(MyColumn)
Строим сводную таблицу:
Итоги и подытоги рассчитались корректно, но таблица имеет не совсем правильный вид: "Итог" между форматами, а "Итог по компании" расположился под одним из округов.
Поправим это, использовав «Невидимый символ». Это не пробел, а именно символ. Можно в поисковик ввести Invisible symbol, перейти на предложенный сайт и там скопировать этот символ.
Вид запроса с добавлением «невидимого символа» в ' Итог по компании' и ' Итог'
/*рассчитываем до Дата-Регион- Формат*/ SELECT DAY_ID AS PERIOD, REGION, FORMAT AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем подытог Дата-Регион, вместо формата прописываем слово «Итог» впереди пустой символ */ UNION ALL SELECT DAY_ID ASPERIOD, REGION, 'ㅤИтог' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» впереди пустой символ*/ UNION ALL SELECT DAY_ID AS PERIOD, 'ㅤИтог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3
Новый вид сводной таблицы после применения "невидимого символа"
Итог по компании и Итог теперь расположены правильно: "Итог" снизу под форматами, а "Итог по компании" в самом конце таблицы под всеми округами.
Но пользователям больше нравится, когда итог и подытог расположены сверху, чтобы сразу первой строчкой видеть «Итог по компании», а не пролистывать вниз.
Также такое расположение проще форматировать с помощью CSS.
Для того, чтобы итоги и подытоги расположились первой строкой, в ' Итог по компании' и ' Итог' вначале добавим один «невидимый символ», а к названию округа и формата присоединим два невидимых символа. Соответственно сортировка пройдет по количеству символов.
Запрос будет иметь вид
/*рассчитываем до Дата-Регион- Формат к региону и формату через CONCAT Добавляем для пустых символа */ SELECT DAY_ID AS PERIOD, CONCAT('ㅤㅤ',REGION) AS REGION, CONCAT('ㅤㅤ',FORMAT) AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем подытог Дата-Регион, вместо формата прописываем слово «Итог» впереди пустой символ, к региону через CONCAT Добавляем для пустых символа */ UNION ALL SELECT DAY_ID ASPERIOD, CONCAT('ㅤㅤ',REGION) AS REGION, 'ㅤИтог' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3 /*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» впереди пустой символ*/ UNION ALL SELECT DAY_ID AS PERIOD, 'ㅤИтог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST, Sum(SALE) AS SALE FROM temp.my_table WHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1 GROUP BY 1,2,3
Вид итоговой сводной таблицы:
Знаю, что многим интересен еще и код CSS, поэтому размещаю его ниже:
.pivot_table_v_2 table.pvtTable { width:55% !important; font-size: 12px !important; background-color: white; } /*задаем динамическую ширину сводной таблицы*/ .pvtTable { border: 2px solid lightgrey; border-radius: 5px; } /*граница вокруг всей сводной*/ .pivot_table_v_2 table.pvtTable tr th.pvtAxisLabel{ font-size: 0px; border-color: white; width: 0%; padding: 0px !important; } /*скрываем названия измерений слово metric и уменьшаем ширину*/ .pivot_table_v_2 table.pvtTable tr:nth-of-type(3) th.pvtAxisLabel { font-size: 13px; font-weight: 700; text-align: center; padding: 1px 8px !important; } /*название столбцов возвращаем*/ .pivot_table_v_2 table.pvtTable thead tr th.pvtTotalLabel { border: 0px solid white; padding: 0px !important; } /*убираем границы у левой ячейки в заголовках*/ .pivot_table_v_2 table.pvtTable thead tr:nth-of-type(1) th.pvtColLabel { color: black; text-align: center; font-size: 13px; font-weight: 600; padding: 1px 8px !important; border-left: 1px solid lightgrey; } /*подкрашиваем 1 строку заголовка в сводной*/ .pivot_table_v_2 table.pvtTable thead tr:nth-of-type(2) th.pvtColLabel { color: black; background-color: #dbdbdb; /*светло-серая заливка*/ text-align: center; font-size: 13px; font-weight: 600; padding: 1px 8px !important; text-wrap: nowrap!important; } /*подкрашиваем 2 строку заголовка в сводной*/ .pivot_table_v_2 table.pvtTable tr td.pvtVal { text-wrap: nowrap!important; text-align: center; padding: 1px 8px; vertical-align: middle; color: black; } /*параметры для значений сводных таблиц*/ .pivot_table_v_2 table.pvtTable tr th.pvtRowLabel { text-wrap: nowrap!important; padding: 1px 8px; font-size: 13px; vertical-align: middle !important; } /*в заголовках строк убираем перенос текста*/ /**** красные разделители*******/ .pivot_table_v_2 table.pvtTable tr:nth-of-type(1) .pvtVal, .pivot_table_v_2 table.pvtTable tr:nth-of-type(1) th.pvtRowLabel { border-top: 2px solid #a73333; } /*полоса под шапкой*/ .pivot_table_v_2 table.pvtTable thead tr:nth-of-type(n) th:nth-of-type(2).pvtColLabel, .pivot_table_v_2 table.pvtTable thead tr:nth-of-type(1) th:nth-of-type(3).pvtColLabel, .pivot_table_v_2 table.pvtTable td:nth-of-type(1).pvtVal, .pivot_table_v_2 table.pvtTable tbody tr.pvtRowTotals td:nth-of-type(1){ border-left: 2px solid #a73333; } /*полоса отделяем названия строк от значений*/ .pivot_table_v_2 table.pvtTable th.pvtRowLabel[rowspan]:not([rowspan="1"]) { border-top: 2px solid #a73333 !important; font-weight: 600; } /*граница первого столбца*/ .pivot_table_v_2 table.pvtTable tr:nth-of-type(-n+1) th:nth-of-type(-n+2).pvtRowLabel, .pivot_table_v_2 table.pvtTable tr:nth-of-type(-n+1) td.pvtVal { background: #FBEEEC; /*розовая заливка*/ font-weight: 600; } /*подкрашиваем и выделяем линией строку с итогом компании*/ .pivot_table_v_2 table.pvtTable tr:nth-of-type(n+2) th[rowspan="1"]:nth-of-type(2).pvtRowLabel, .pivot_table_v_2 table.pvtTable tr:nth-of-type(n+2):has(th[rowspan="1"]:nth-of-type(2).pvtRowLabel) .pvtVal { background: #f5f5f5; /*светло-серая заливка*/ font-weight: 600; border-top: 2px solid #a73333 !important; border-bottom: 2px solid #a73333 !important; vertical-align: middle; } /*подкрашиваем и выделяем линией подытог начиная со второй строки*/
Вывод: продолжаем экспериментировать, SuperSet не так уж прост, и в нем можно реализовать даже самые смелые идеи.