Агрегированная витрина для дэшборда

Кажется, это не особо сложная задача - построить витрину для дэшборда, однако, я хочу отметить одну важную особенность при построении агрегированной витрины.

Ошибка GROUP BY по всем полям

Мне часто по работе приходилось видеть коды, написанные кем-то до меня, на котором строился дэшборд и в котором можно было увидеть что-то вроде:

select
  created_at::date as created_at,
  zone_nm,
  service_nm,
  SUM(duration) as duration,
  COUNT(DISTINCT client_id) as users_count
FROM mytable
GROUP BY 1,2,3 

Здесь считается две метрики: duration и users_count. И далее прямо на этой витрине строится временной барчарт, где в фильтры уходят поля created_at, zone_nm, service_nm, channel_source_nm, а в меры - наши две метрики.

И вроде все норм, но есть нюанс - это неверный расчет метрик!

Почему? Все довольно просто - группируя по всем полям сразу - мы рассчитываем метрики для кадого такого разреза. Например для created_at = '2025-04-01' ; zone_nm = 'first_zone'; service_nm = 'application'; channel_source_nm = 'organic' рассчитывается отедельно duration и отдельно users_count. Иныиы словами, если захотим посмотреть сколько было duration или users_count просто по дням (created_at) - не получится, такого разреза у нас нет.

Далее, при создании графика в любом BI инструменте - указываем агрегацию для меры и вот тут происходит вторая ошибка - первая метрика - аддитивная (т.е. ее можно агрегировать сколько угодно раз) и с ней проблем нет - а вот users_count - неаддитивная (тк рассчитываются уникальные client_id) и ее суммировать или усреднять или еще как-то агрегировать ее нельзя.

Таким образом, если мы хотим построить правильную витрину - стоит заморочиться

-- 1 Полная детализация
SELECT
  created_at,
  zone_nm,
  service_nm,
  SUM(duration) AS duration,
  COUNT(DISTINCT client_id) AS users_count
FROM mytable
GROUP BY created_at, zone_nm, service_nm

UNION ALL

-- 2 Разрез по created_at + zone_nm
SELECT
  created_at,
  zone_nm,
  'all' AS service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY created_at, zone_nm

UNION ALL

-- 3 Разрез по created_at + service_nm
SELECT
  created_at,
  'all' AS zone_nm,
  service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY created_at, service_nm

UNION ALL

-- 4 Разрез по zone_nm + service_nm
SELECT
  'all' AS created_at,
  zone_nm,
  service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY zone_nm, service_nm

UNION ALL

-- 5 Разрез только по created_at
SELECT
  created_at,
  'all' AS zone_nm,
  'all' AS service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY created_at

UNION ALL

-- 6 Разрез только по zone_nm
SELECT
  'all' AS created_at,
  zone_nm,
  'all' AS service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY zone_nm

UNION ALL

-- 7 Разрез только по service_nm
SELECT
  'all' AS created_at,
  'all' AS zone_nm,
  service_nm,
  SUM(duration),
  COUNT(DISTINCT client_id)
FROM mytable
GROUP BY service_nm

И вот только теперь, при выборе в фильтрах значения полей, можно быть уверенным, что метрики посчитаны корректно для каждого разреза. И не важно, какую функцию агрегации мы укажем на стороне BI-инструмента (SUM, MAX, AVG) все равно показатель предрассчитан и не изменится.

Это громоздкий, но правильный подход, и, возможно, единственный, я не говорю про то, когда данные в витрине "сырые" - это просто сказка, и если BI инструмент позволяет так сделать - делайте, но порой необходимо обрабатывать сотни миллионов строк на стороне БД, а в инструмент отправлять уже агрегат (как указал выше).

И тут есть тоже один нюанс - нельзя выбрать несколько значений из полей фильтров, иначе метрики будут неверными, иными словами при тако модели построения витрины, можно ставить только "единичный выбор" на фильтрах.

Спасибо, что дочитали, буду рад, если есть иные варианты построения агрегированной витрины!