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

В  первой статье были разобраны причины поиска решения  по выводу корректных  подытогов и итогов в сводных таблицах SuperSet, а также подробный разбор первого способа решения с помощью UNION. В данной статья я разберу второй способ, который мне больше нравится – это использование массивов.  

Чтобы было более понятно, почему подобным образом собираем массив, рекомендую сначала ознакомиться со способом через UNION, описанном в первой части . Также в первой части подробно описано, почему мы добавляем невидимые символы и для чего. Здесь же кратко приведу SQL запрос, который в итоге приведет нас к такому же результату.

SELECT
  PERIOD,
  arrayJoin(REGION) AS REGION,
  arrayJoin(FRMT) as FRMT,
  LOST,
  SALE
FROM ( 
	SELECT 
		DAY_ID AS PERIOD,
		array(CONCAT('ㅤㅤ',REGION), 'ㅤИтог по компании') AS REGION,
		array(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
)

Есть небольшой нюанс в таком способе. При подобном подходе сбор метрики требует небольших корректировок.

Чтобы наглядно продемонстрировать данный нюанс, построим сводную таблицу, в которой показатель Потери, % будет считаться как обычно:

divide(toFloat32(Sum(LOST)),Sum(SALE))

Сводная таблица будет иметь вид:

"Итог по компании" у нас также разбился на форматы. Такой вид данных тоже может быть кому-то полезен, когда надо вывести еще и значение тотал по формату без разбивки по региону, но в данном случае нам надо оставить только строчку с итогом компании. Для этого расчет метрики Потери, % запишем следующим образом .

CASE 
  WHEN REGION = 'ㅤИтог по компании' 
    THEN divide(toFloat32(SumIf(LOST,FRMT='ㅤИтог')),SumIf(SALE,FRMT='ㅤИтог')) 
  ELSE divide(toFloat32(Sum(LOST)),Sum(SALE)) 
END

• не забываем про невидимые символы

После корректировки метрики в Итогах компании остается одна строчка.

Сводная таблица имеет финальный вид.

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) th:nth-of-type(2).pvtRowLabel {
   font-size: 0px;
   border-left: none;
} /*для итого компании убираем слово Итог в столбце форматов*/

/**** красные разделители*******/

.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 lightgrey !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:#dedede /*#f5f5f5*/; /*светло-серая заливка*/
  font-weight: 600;
  border-radius: 5px;
  --border: 2px solid #a73333 !important;
 -- border-bottom: 2px solid #a73333 !important;
  vertical-align: middle;
} /*подкрашиваем и выделяем линией подытог начиная со второй строки*/

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