Подытоги и итоги сводных таблиц в Apache SuperSet: многоуровневая агрегация на ClickHouse (часть 1)

Superset умеет строить сводные таблицы,  но при выводе подытогов и итогов (subtotals/total)  в сводных таблицах с иерархически структурированной метрикой сталкиваешься с ограничением: из коробки можно строить только простые метрики. А если нужна процентная метрика с корректными подытогами и итогами — приходится искать обходные решения.

 В этой статье разберём, как обойти это ограничение на уровне обращения к БД (ClickHouse).

Я  нашла два подхода: UNION и массивы. И начну с первого, детально разобрав решение именно через UNION.

Разберем на примере расчета показателя Потери, %

Потери, % = Потери, шт/Продажи, шт

Если мы построим итоги и подытоги стандартным функционалом SuperSet,

то в результате получим не совсем то, что нужно.

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

Как обойти это ограничение я покажу на примере ClickHouse — у PostgreSQL тот же приём может не сработать

Считаем показатель для каждого уровня:

1. Дата-Регион-Формат

2. Дата-Регион /*подытог*/

3. Дата /*Итог*/

И далее «схлопываем» через UNION

Вид запроса:

/*рассчитываем до Дата-Регион- Формат*/SELECT DAY_ID  AS PERIOD,REGION,FORMAT AS FRMT,Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем подытог Дата-Регион, вместо формата прописываем  слово «Итог» */UNION ALLSELECT DAY_ID ASPERIOD,REGION,'Итог' AS FRMT, Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» */UNION ALLSELECT DAY_ID AS PERIOD,'Итог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP 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 SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем подытог Дата-Регион, вместо формата прописываем  слово «Итог» впереди пустой символ */UNION ALLSELECT DAY_ID ASPERIOD,REGION,'ㅤИтог' AS FRMT, Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» впереди пустой символ*/UNION ALLSELECT DAY_ID AS PERIOD,'ㅤИтог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP 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 SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем подытог Дата-Регион, вместо формата прописываем слово «Итог» впереди пустой символ, к региону через CONCAT Добавляем для пустых символа */UNION ALLSELECT DAY_ID ASPERIOD,CONCAT('ㅤㅤ',REGION) AS REGION,'ㅤИтог' AS FRMT,Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP BY 1,2,3/*рассчитываем итог по компании, вместо формата ставим одинарные кавычки (внутри пусто), вместо региона прописываем «Итог по компании» впереди пустой символ*/UNION ALLSELECT DAY_ID AS PERIOD,'ㅤИтог по компании' AS REGION, '' AS FRMT, Sum(LOST) AS LOST,  Sum(SALE) AS SALEFROM temp.my_tableWHERE DAY_ID BETWEEN CURDATE()-7 AND CURDATE() - 1GROUP 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 не так уж прост, и в нем можно реализовать даже самые смелые идеи.