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