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