Подытоги и итоги сводных таблиц в 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 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 не так уж прост, и в нем можно реализовать даже самые смелые идеи.