При разработке отчетов в системе 1С:Предприятие программисты часто сталкиваются с классической проблемой: при соединении двух таблиц (например, данных по остаткам и данных по оборотам или справочных данных с регистрами) итоговые суммы начинают «размножаться». Это происходит из-за особенностей работы SQL-запросов: если одной записи из первой таблицы соответствует несколько записей из второй, данные первой таблицы дублируются для каждой строки соединения. В итоге общие суммы в отчете становятся некорректными.
В этой статье мы подробно разберем, почему возникает эта проблема и как эффективно решить ее с помощью механизмов Системы компоновки данных (СКД).
Проанализируем ситуацию. Представим, что у нас есть две таблицы. В первой таблице хранятся данные по счетам (например, СчетДт), и на один объект может приходиться две или более строки. Во второй таблице хранятся суммы. Если мы выполняем обычное ЛЕВОЕ СОЕДИНЕНИЕ (Left Join) в одном запросе, то каждая строка из второй таблицы соединится со всеми подходящими строками из первой. Если в первой таблице две строки с одинаковым ключом связи, то сумма из второй таблицы прибавится к итогу дважды.
Группировка в основном запросе не всегда возможна, так как нам может потребоваться детальная информация (например, те самые счета), которая и создает уникальность строк. Разберем основные способы решения этой задачи.
Это наиболее правильный и рекомендуемый метод для решения проблемы в рамках системы компоновки данных. Рассмотрим его подробнее.
Суть метода заключается в том, чтобы не объединять таблицы в тексте одного SQL-запроса, а создать в СКД два отдельных Набора данных - запрос. Связывание этих наборов происходит на уровне виртуальных таблиц в памяти самого приложения 1С, а не на уровне СУБД.
НаборДанных1), в котором выберем детальные записи со счетами.НаборДанных2), содержащий суммы.НаборДанных1 как источник связи, а НаборДанных2 — как приемник.Номенклатура или Документ).Почему это работает? При таком подходе 1С сначала выполняет запросы по отдельности. Когда СКД начинает компоновать результат, она берет запись из первого набора и «подтягивает» к ней данные из второго. Поскольку данные из второго набора не «размножаются» физически в результирующей выборке SQL-сервера, итоги по первому набору будут рассчитаны корректно. Итоговые суммы по второму набору также останутся верными, так как СКД учитывает их количество из исходного набора данных.
Если по каким-то причинам нам необходимо использовать именно один запрос с соединением, мы можем скорректировать расчет итогов на уровне Ресурсов СКД. Проанализируем ситуацию: если данные во второй таблице всегда одинаковы для группы строк из первой таблицы, мы можем применить функции Максимум или Среднее.
Разберем пример настройки ресурса:
Вместо стандартного выражения Сумма(СуммаОборота) в поле ресурса можно написать:
Максимум(СуммаОборота)
Это поможет избежать суммирования дублей внутри конкретной группировки. Однако будьте осторожны: этот метод требует тщательной настройки иерархии расчета (колонка «Рассчитывать по...» в закладке Ресурсы), иначе общие итоги по всему отчету все равно могут оказаться неверными.
Для продвинутых отчетов в СКД предусмотрена мощная функция ВычислитьВыражениеСГруппировкойТаблица. Она позволяет вычислить агрегатное значение (например, сумму) только по уникальным комбинациям определенных полей.
Рассмотрим пример выражения для ресурса:
Сумма(ВычислитьВыражениеСГруппировкойТаблица("Максимум(Сумма)", "КлючСвязи"))
В данном случае система сначала сгруппирует данные по полю КлючСвязи, возьмет максимальное значение суммы для каждой группы (отсекая дубли), а затем просуммирует эти уникальные значения для общего итога.
Если мы хотим решить проблему исключительно на уровне текста запроса (например, для повышения производительности на огромных объемах данных), нам следует использовать временные таблицы. Выясним причину дублей еще раз: она в избыточности строк. Значит, нам нужно избавиться от избыточности ДО момента соединения.
Рассмотрим алгоритм действий в конструкторе запроса:
ВТ_ДанныеСчетов, где выбираем все необходимые разрезы.ВТ_ДанныеСумм, в которой данные уже сгруппированы до того же уровня, что и в первой таблице.Пример кода запроса с предварительной группировкой:
ВЫБРАТЬ
Продажи.Номенклатура КАК Номенклатура,
СУММА(Продажи.Сумма) КАК СуммаПродаж
ПОМЕСТИТЬ ВТ_ИтоговыеСуммы
ИЗ
РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Счета.Номенклатура,
Счета.СчетДт,
ВТ_ИтоговыеСуммы.СуммаПродаж
ИЗ
Справочник.Номенклатура КАК Счета
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИтоговыеСуммы КАК ВТ_ИтоговыеСуммы
ПО Счета.Ссылка = ВТ_ИтоговыеСуммы.Номенклатура
В конструкторе схемы компоновки данных на закладке Наборы данных у каждого поля есть колонка Роль. Правильная настройка ролей может подсказать системе, как обрабатывать повторяющиеся значения.
Посмотрим, какие параметры ролей нам могут помочь:
Мы рассмотрели несколько методов борьбы с дублированием данных при объединении таблиц. Подведем итог: наиболее надежным и простым в реализации способом для СКД является соединение наборов данных на уровне схемы. Если же требуется высокая производительность и работа с большими массивами данных, следует отдать предпочтение временным таблицам с предварительной группировкой в тексте запроса.
Помните, что использование Максимум в ресурсах — это быстрый «костыль», который может выручить в простых случаях, но требует внимательной проверки общих итогов отчета. Используйте правильный инструмент в зависимости от сложности вашей задачи!