При разработке отчетов и доработке конфигураций в 1С мы часто сталкиваемся с необходимостью собрать данные из разных источников. Одна из самых распространенных задач — получить количество и сумму продажи из табличной части документа (например, реализации) и сопоставить их с себестоимостью, хранящейся в регистре накопления. Однако при написании на первый взгляд правильного запроса с использованием ЛЕВОЕ СОЕДИНЕНИЕ мы вдруг обнаруживаем, что итоговые суммы увеличиваются в два, три и более раз. Разберем по шагам, почему так происходит, выясним причину и рассмотрим несколько надежных способов решения этой проблемы.
Проанализируем типичную ситуацию. Допустим, мы берем таблицу Документ.РеализацияТоваровУслуг.Товары и пытаемся присоединить к ней РегистрНакопления.ВыручкаИСебестоимостьПродаж по полям Регистратор = Ссылка и Номенклатура = Номенклатура.
Проблема кроется в так называемом Декартовом произведении. Если в табличной части документа присутствует несколько строк с одинаковой номенклатурой (например, один и тот же товар с разными складами, характеристиками или просто разбитый пользователем на две строки), то при проведении документа в регистре также сформируется несколько соответствующих движений. При выполнении левого соединения каждая строка из левой таблицы находит все подходящие строки из правой.
Посмотрим на пример. Если в документе есть 3 строки с товаром "Стол" и в регистре есть 3 строки по этому же документу с тем же товаром, то запрос соединит каждую строку документа с каждой строкой регистра. Результатом станут 3 × 3 = 9 строк! После того как платформа сгруппирует этот результат, сумма многократно сложится и увеличится в разы.
Рассмотрим один из самых эффективных и быстродействующих методов решения данной проблемы — использование оператора ОБЪЕДИНИТЬ ВСЕ с последующей группировкой в подзапросе.
Суть метода заключается в том, что мы не соединяем таблицы между собой по горизонтали, а просто выстраиваем их друг под другом (по вертикали). Недостающие колонки в каждой из таблиц мы заполняем нулями, а затем оборачиваем все это во внешний запрос и группируем по ключевым полям.
Проанализируем механику на простом примере данных. Допустим, у нас есть две выборки:
После объединения мы получим следующий набор:
Товар1 | 0 (Себест) | 2 (Кол) | 5 (Сумма)
Товар1 | 0 (Себест) | 3 (Кол) | 7 (Сумма)
Товар1 | 3 (Себест) | 0 (Кол) | 0 (Сумма)
Товар1 | 5 (Себест) | 0 (Кол) | 0 (Сумма)
А теперь сворачиваем этот результат по Товару и Регистратору и получаем абсолютно корректный итог: Товар1, Себестоимость = 8, Количество = 5, Сумма = 12. Никакого задвоения!
Посмотрим на пример кода, реализующего этот подход:
ВЫБРАТЬ
Общ.Товар КАК Товар,
Общ.Регистратор.ЗаказКлиента КАК ЗаказКлиента,
СУММА(Общ.Себестоимость) КАК Себестоимость,
СУММА(Общ.Количество) КАК Количество,
СУММА(Общ.Сумма) КАК Сумма
ИЗ (
ВЫБРАТЬ
0 КАК Себестоимость,
Товары.Ссылка КАК Регистратор,
Товары.Номенклатура КАК Товар,
Товары.Количество КАК Количество,
Товары.Сумма КАК Сумма
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК Товары
ГДЕ
Товары.Ссылка В(&МассивОтгрузок)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Рег.СтоимостьРегл,
Рег.Регистратор,
Рег.АналитикаУчетаНоменклатуры.Номенклатура,
0,
0
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК Рег
ГДЕ
Рег.Регистратор В(&МассивОтгрузок)
) КАК Общ
СГРУППИРОВАТЬ ПО
Общ.Регистратор.ЗаказКлиента,
Общ.Товар
Если объединение кажется сложным для восприятия или если логика отчета требует именно соединений, рассмотрим другой классический подход. Мы должны сгруппировать данные до того, как они будут соединены.
Хорошей практикой в 1С является создание двух временных таблиц: в первую мы помещаем свернутые итоги по табличной части документа, во вторую — свернутые итоги по регистру. Затем мы просто выполняем ЛЕВОЕ СОЕДИНЕНИЕ этих двух предварительно сжатых таблиц. Раз строки свернуты до уникальных пар "Документ + Номенклатура", декартово произведение физически невозможно.
Пример подготовки временной таблицы для регистра:
ВЫБРАТЬ
Рег.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
Рег.Регистратор КАК Регистратор,
СУММА(Рег.СтоимостьРегл) КАК СтоимостьРегл
ПОМЕСТИТЬ ВТ_Себестоимость
ИЗ
РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК Рег
ГДЕ
Рег.Регистратор В(&МассивОтгрузок)
СГРУППИРОВАТЬ ПО
Рег.АналитикаУчетаНоменклатуры.Номенклатура,
Рег.Регистратор;
Важное примечание: при левом соединении обязательно оборачивайте числовые показатели правой таблицы в функцию ЕСТЬNULL. Если товар был в документе, но по какой-то причине не сделал движений в регистре по себестоимости (например, не было партий на складе), соединение вернет значение NULL. В 1С при попытке математически обработать NULL (даже просто прибавить его к числу) весь результат вычисляемого поля обращается в пустоту. Используйте конструкцию вида: ЕСТЬNULL(ВТ_Себестоимость.СтоимостьРегл, 0) КАК Себестоимость.
Современные типовые конфигурации (такие как ERP, УТ 11, Комплексная автоматизация) предлагают архитектурное решение этой проблемы. Обратим внимание на структуры таблиц: и в табличных частях документов, и в движениях многих регистров предусмотрено специальное связующее поле — КлючСтроки (или КлючАналитики).
Если конфигурация поддерживает эту механику, мы можем вообще отказаться от предварительных группировок. Достаточно добавить это поле в условие связи:
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ВыручкаИСебестоимостьПродаж КАК Выручка
ПО (Выручка.Регистратор = Товары.Ссылка)
И (Выручка.КлючСтроки = Товары.КлючСтроки)
Благодаря этому условию запрос связывает каждую конкретную строку документа со строго определенным, относящимся только к ней движением в регистре, по принципу «одна строка к одной строке».
Мы проанализировали основные причины затроения и задвоения сумм. Если вам нужно получить быстрый запрос без лишних соединений, используйте ОБЪЕДИНИТЬ ВСЕ. Если алгоритм требует сложной выборки с большим количеством источников — применяйте предварительную группировку во временные таблицы или соединяйте таблицы строго по КлючСтроки.