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

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

Ошибка 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,4 

Здесь считается две метрики: 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 инструмент позволяет так сделать — делайте, но порой необходимо обрабатывать сотни миллионов строк на стороне БД, а в инструмент отправлять уже агрегат (как указал выше).

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