Как правильно рассчитать итог "Количество различных" в кросс-таблице СКД?

При построении аналитических отчетов в Системе компоновки данных (СКД), таких как когортный анализ или статистика удержания клиентов (Retention Rate), разработчики часто сталкиваются с неприятной особенностью расчета итогов. Типичная ситуация: в строках отчета выводится дата первого привлечения клиента, в колонках — номера дней активности (0-й день, 1-й день, 2-й день и так далее), а на пересечении — количество уникальных пользователей.

Логика подсказывает, что если в 0-й день к нам пришло 95 уникальных клиентов, в 1-й день вернулось 30, а во 2-й — 15, то общий сквозной итог уникальных клиентов за весь период должен составить, например, 100 человек (с учетом того, что клиенты пересекаются в разные дни). Однако СКД в итоговой колонке упорно выводит число 95. Почему так происходит и как заставить систему рассчитывать итоги корректно? Давайте подробно разберем эту проблему и найдем пути ее решения.

Выясняем причину: главная архитектурная ошибка группировки

Для начала проанализируем, как большинство разработчиков строит исходный запрос для подобных отчетов. Очень часто в попытке "оптимизировать" выборку данных программисты пишут запрос, который группирует информацию по дням активности еще на стороне СУБД:

ВЫБРАТЬ
    Подзапрос.ДатаПривлечения КАК ДатаПривлечения,
    Подзапрос.ДеньПривлечения КАК ДеньПривлечения,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Подзапрос.ИДПокупателя) КАК КоличествоПокупателей
ИЗ
    (ВЫБРАТЬ ... ) КАК Подзапрос
СГРУППИРОВАТЬ ПО
    Подзапрос.ДатаПривлечения,
    Подзапрос.ДеньПривлечения

На первый взгляд запрос выглядит логично. Мы получаем готовую свернутую таблицу, где для каждого дня уже рассчитано количество уникальных клиентов. Но именно в этой группировке кроется фундаментальная ошибка работы с СКД!

Когда мы передаем в СКД уже сгруппированные данные, в систему компоновки поступают простые числа: для Дня 0 — это 95, для Дня 1 — это 30, для Дня 2 — это 15. Уникальные идентификаторы (ссылки или уникальные строки) клиентов в СКД не попадают. В результате СКД лишается возможности узнать, какие именно клиенты совершали действия в эти дни.

Когда дело доходит до расчета общего итога по строке, СКД пытается применить выражение ресурса к имеющимся агрегированным числам. Если в ресурсах СКД указано выражение Сумма(КоличествоПокупателей), система выдаст 140 (95 + 30 + 15), что не является количеством уникальных клиентов, так как многие из них дублируются в разные дни. Если же указать выражение Максимум(КоличествоПокупателей) или оставить расчет по умолчанию, СКД выведет максимальное число из имеющихся колонок — 95.

Решение №1: Отказ от группировки в запросе (классический сквозной итог)

Чтобы получить истинный сквозной итог уникальных клиентов (например, те самые 100 человек), мы должны передать расчет агрегатных функций целиком на сторону СКД. СУБД должна вернуть нам "сырые", детальные записи без предварительного сворачивания.

Рассмотрим правильную структуру запроса. Мы убираем секцию СГРУППИРОВАТЬ ПО и выводим детальный идентификатор каждого покупателя:

ВЫБРАТЬ
    "ДатаПривлечения1" КАК ДатаПривлечения,
    0 КАК ДеньПривлечения,
    "ИДПокупателя1" КАК ИДПокупателя
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    "ДатаПривлечения1",
    0,
    "ИДПокупателя2"
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    "ДатаПривлечения1",
    1,
    "ИДПокупателя1"
-- И так далее для всех детальных записей вашей базы данных

После того как детальные записи получены, переходим к настройке СКД:

1. Откроем вкладку "Ресурсы" в схеме компоновки данных.
2. Добавим поле ИДПокупателя в список ресурсов.
3. В колонке "Выражение" укажем: Количество(Различные ИДПокупателя).
4. В колонке "Рассчитывать по..." оставим поле пустым (чтобы расчет применялся ко всем группировкам и общим итогам).

Как это работает: получив детальные записи, СКД самостоятельно распределит идентификаторы по колонкам дней привлечения. Для Дня 0 она посчитает уникальные ИД (получит 95), для Дня 1 — также уникальные ИД (получит 30). А при расчете общего итога по строке СКД соберет в единый пул все идентификаторы за все дни данной строки, применит к ним операцию исключения дублей и выведет верный результат — 100 уникальных клиентов.

Решение №2: Расчет суммы уникальных по дням (двойное агрегирование)

Иногда перед разработчиком стоит альтернативная задача. Заказчик отчета может потребовать, чтобы в общем итоге выводилась не сквозная уникальность (100 человек), а именно сумма уникальных клиентов по каждому дню (то есть 95 + 30 + 15 = 140 клиентов/визитов). В терминологии СКД это называется двойным агрегированием.

Обычная формула ресурса в таком случае не поможет. Нам потребуется применить специальную функцию СКД для работы с массивами группировок. Настроим наш ресурс следующим образом:

1. Переходим на вкладку "Ресурсы".
2. Для поля ИДПокупателя в строке общего итога пишем формулу:

Сумма(ВычислитьВыражениеСГруппировкойМассив("Количество(Различные ИДПокупателя)", "ДеньПривлечения"))

Разберем по шагам, как работает это выражение:

• Функция ВычислитьВыражениеСГруппировкойМассив виртуально рассчитывает уникальное количество покупателей отдельно по каждой колонке группировки ДеньПривлечения.
• В результате мы получаем массив чисел, соответствующих колонкам нашего отчета (например, [95, 30, 15]).
• Внешняя функция Сумма складывает элементы полученного массива, возвращая в итоговой ячейке точное математическое значение 140.

Решение №3: Разделение формул для ячеек и итогов в кросс-таблице

Система компоновки данных позволяет гибко разграничивать формулы расчетов для пересечений строк/колонок и для итоговых полей. Это крайне полезно, если в теле таблицы нам нужно видеть обычное уникальное количество, а в итогах — сумму или среднее значение по дням.

Для реализации такой схемы на вкладке "Ресурсы" нам нужно добавить поле ИДПокупателя дважды с разными настройками:

1. Первая строка ресурса (для ячеек таблицы):
   • Поле: ИДПокупателя
   • Выражение: Количество(Различные ИДПокупателя)
   • Рассчитывать по...: Указываем группировку колонок, например, ДеньПривлечения.
2. Вторая строка ресурса (для итогов по строкам и общего итога):
   • Поле: ИДПокупателя
   • Выражение: Сумма(ВычислитьВыражениеСГруппировкойМассив("Количество(Различные ИДПокупателя)", "ДеньПривлечения"))
   • Рассчитывать по...: Указываем группировку строк (например, ДатаПривлечения) и ОбщийИтог.

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

Используя описанные методики, вы сможете легко управлять поведением агрегатных функций КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) в любых сложных отчетах и кросс-таблицах 1С.