Как правильно посчитать уникальное количество контрагентов (АКБ) в запросах 1С и СКД?

← На главную

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

Почему возникает проблема с подсчетом АКБ?

Основная причина некорректного подсчета уникальных значений лежит в том, как функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Поле) взаимодействует с оператором СГРУППИРОВАТЬ ПО в запросе. Если в ваш список группировок включены поля, которые могут отличаться для одного и того же контрагента (например, СуммаДокумента, Ссылка на конкретный документ реализации, или даже сам Контрагент), то система будет формировать уникальные строки по всем этим детализирующим полям. В результате, для каждой такой уникальной комбинации полей, в которой присутствует конкретный контрагент, КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагент) будет всегда равно 1. Это происходит потому, что в пределах этой *конкретной* строки результата запроса контрагент уже является уникальным для данной комбинации группировки. Таким образом, мы получаем множество строк с '1' вместо общего уникального количества.

Рассмотрим исходный запрос, представленный коллегой на форуме, как яркий пример этой ситуации:


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

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

Решение 1: Использование вложенного запроса для подсчета уникальных контрагентов

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

Формирование внутреннего (вложенного) запроса:
Наша первостепенная цель — получить уникальные комбинации тех полей, по которым мы хотим определить "активность" контрагента и по которым в дальнейшем будет производиться подсчет. В нашем случае это Контрагент, а также поля группировки, по которым мы хотим видеть итоги, например, ДатаМесяц, Ответственный и кпкТорговыйАгент. Мы используем оператор ВЫБРАТЬ РАЗЛИЧНЫЕ, чтобы гарантировать уникальность каждой строки на этом этапе.

Пример внутреннего запроса:
```
ВЫБРАТЬ РАЗЛИЧНЫЕ
    НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, МЕСЯЦ) КАК ДатаМесяц,
    РеализацияТоваровУслуг.Ответственный,
    РеализацияТоваровУслуг.кпкТорговыйАгент,
    РеализацияТоваровУслуг.Контрагент
ИЗ
    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
    РеализацияТоваровУслуг.Проведен
    И РеализацияТоваровУслуг.Дата МЕЖДУ &ДатаНачало И &ДатаКонец
```
Этот внутренний запрос создает временную таблицу, содержащую только уникальные сочетания месяца, ответственного, торгового агента и контрагента, у которых была реализация в заданном периоде. В этой таблице уже нет дубликатов контрагентов в рамках одной комбинации (ДатаМесяц, Ответственный, Агент).
Формирование внешнего (основного) запроса:
Теперь, когда у нас есть "чистый" набор уникальных контрагентов, мы можем использовать внешний запрос для их агрегации. В этом запросе мы будем применять функцию КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Контрагент) к результатам вложенного запроса, группируя по тем полям, по которым нам нужны итоги (например, по ДатаМесяц, Ответственный и кпкТорговыйАгент).

Полный пример измененного запроса:
```
ВЫБРАТЬ
    ОсновнойЗапрос.ДатаМесяц,
    ОсновнойЗапрос.Ответственный,
    ОсновнойЗапрос.кпкТорговыйАгент,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ОсновнойЗапрос.Контрагент) КАК АКБ
ИЗ
    (ВЫБРАТЬ РАЗЛИЧНЫЕ
        НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, МЕСЯЦ) КАК ДатаМесяц,
        РеализацияТоваровУслуг.Ответственный,
        РеализацияТоваровУслуг.кпкТорговыйАгент,
        РеализацияТоваровУслуг.Контрагент
    ИЗ
        Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
    ГДЕ
        РеализацияТоваровУслуг.Проведен
        И РеализацияТоваровУслуг.Дата МЕЖДУ &ДатаНачало И &ДатаКонец) КАК ОсновнойЗапрос
СГРУППИРОВАТЬ ПО
    ОсновнойЗапрос.ДатаМесяц,
    ОсновнойЗапрос.Ответственный,
    ОсновнойЗапрос.кпкТорговыйАгент
```
В этом запросе внешний запрос оперирует уже с агрегированными данными. Поле ОсновнойЗапрос.Контрагент не является частью детальной группировки для внешнего запроса, а используется только для подсчета уникальных значений внутри каждой группы. Таким образом, мы получаем корректный подсчет АКБ в разрезе месяца, ответственного и торгового агента.

Решение 2: Настройка в Системе Компоновки Данных (СКД)

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

Правильное использование ресурсов в СКД:
В СКД вы можете определить ресурс с выражением Количество(Различные Контрагент). Это наиболее прямой и часто используемый способ.

Важно понимать: Для корректной работы этого ресурса необходимо, чтобы поле Контрагент не входило в поля "Детальные записи" или в группировки нижних уровней, если вы хотите получить общий итог по более высоким уровням группировки (например, по месяцу или по ответственному). СКД автоматически агрегирует ресурсы по вышестоящим группировкам. Если Контрагент будет в детальных записях, вы столкнетесь с той же проблемой, что и в исходном запросе (каждая строка будет иметь АКБ=1). Вместо этого, поле Контрагент должно быть доступно в наборе данных запроса СКД, но не должно быть выбрано для отображения в детальных записях или в качестве поля группировки, если цель - агрегированный подсчет.

Например, если вы хотите увидеть АКБ в разрезе кпкТорговыйАгент, то в СКД вы создаете группировку по кпкТорговыйАгент, а в ресурсах указываете Количество(Различные Контрагент). СКД самостоятельно вычислит уникальное количество контрагентов для каждого агента.
Применение функции ВычислитьВыражениеСГруппировкойМассив() (для сложных сценариев):
В некоторых, более сложных, случаях, когда требуется особая логика агрегации или подсчет уникальных значений по определенному набору полей, который не совпадает с текущей структурой группировок отчета, можно воспользоваться функцией ВычислитьВыражениеСГруппировкойМассив(). Эта функция является мощным инструментом СКД, позволяющим выполнять агрегацию по одному набору полей, получать массив значений, а затем использовать этот массив для дальнейших вычислений в других группировках.

Рассмотрим, как это может быть применено:
1. На уровне набора данных-запроса: В запросе к базе данных выбираем все необходимые поля (Дата, Ответственный, кпкТорговыйАгент, Контрагент, СуммаДокумента и т.д.) без детализирующих агрегаций или группировок, позволяя СКД выполнять всю работу по агрегации.
```
ВЫБРАТЬ
    РеализацияТоваровУслуг.Дата КАК Дата,
    НАЧАЛОПЕРИОДА(РеализацияТоваровУслуг.Дата, МЕСЯЦ) КАК ДатаМесяц,
    РеализацияТоваровУслуг.Ответственный КАК Ответственный,
    РеализацияТоваровУслуг.кпкТорговыйАгент КАК кпкТорговыйАгент,
    РеализацияТоваровУслуг.Контрагент КАК Контрагент,
    РеализацияТоваровУслуг.СуммаДокумента КАК СуммаДокумента
ИЗ
    Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
    РеализацияТоваровУслуг.Проведен
    И РеализацияТоваровУслуг.Дата МЕЖДУ &ДатаНачало И &ДатаКонец
```
2. На уровне СКД (Закладка "Ресурсы"): Создаем новый ресурс для подсчета АКБ. Допустим, мы хотим подсчитать АКБ для каждого Ответственного, но при этом иметь возможность просматривать другие метрики (например, общую сумму продаж) по более детализированным группировкам (например, по кпкТорговыйАгент). В этом случае мы можем использовать следующее выражение:
  - Имя ресурса: АКБ_Ответственный
  - Выражение: Количество(ВычислитьВыражениеСГруппировкойМассив("Контрагент", "Ответственный"))
  Это выражение говорит СКД: "Для каждой уникальной комбинации значения поля Ответственный соберите массив всех значений поля Контрагент, которые ему соответствуют, а затем подсчитайте уникальное количество элементов в этом массиве". СКД автоматически позаботится об уникальности при подсчете, если в массиве будут дубликаты, и выдаст корректный подсчет АКБ в разрезе ответственных, даже если в отчете используются более детальные группировки.
  
  Следует отметить, что для простого подсчета АКБ по основным группировкам (например, по месяцу и агенту, как в нашем примере со вложенным запросом), ресурс Количество(Различные Контрагент), установленный в СКД на закладке "Ресурсы", обычно является достаточным и более простым решением, при условии правильного построения группировок отчета.

Определение "Активной Клиентской Базы"

Перед тем как приступить к написанию запроса или настройке отчета в СКД, всегда критически важно четко определить, что именно мы подразумеваем под термином "активная клиентская база". Это определение напрямую влияет на структуру запроса и условия отбора данных. Рассмотрим несколько возможных интерпретаций:

Контрагенты с хотя бы одним документом реализации: Это наиболее простое определение, которое мы использовали в наших примерах. Под АКБ понимаются все контрагенты, у которых в заданный период был хотя бы один проведенный документ реализации.
Контрагенты, у которых сумма реализаций превысила определенное значение: В этом случае, помимо факта наличия реализации, требуется агрегация по суммам и применение дополнительного условия отбора (например, ИмеетЗначение(СУММА(СуммаДокумента) > 1000)). Для такого сценария внутренний запрос может сначала агрегировать суммы по контрагентам, а затем внешний запрос будет считать уникальных контрагентов, удовлетворяющих условию по сумме.
Контрагенты, которые совершают покупки регулярно: Это более сложное определение, которое может потребовать анализа периодичности покупок, использования временных интервалов и более сложных запросов с оконными функциями или даже дополнительных регистров для хранения истории активности.

В нашей задаче, как уточнил автор, речь шла об "уникальном количестве контрагентов в разрезе 1 агента", что соответствует первому, наиболее распространенному определению АКБ.

Заключение

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

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