При разработке отчетов и обработок в среде 1С:Предприятие программисты часто сталкиваются с необходимостью сопоставления данных из разных регистров. Классический пример — получение сводной информации по заказам покупателей, их фактической отгрузке (документами «Реализация товаров и услуг», или РТУ) и закрытию невыполненных остатков. Однако при неверном проектировании связей в запросе возникает неприятный эффект: суммы заказов начинают дублироваться, необоснованно увеличивая итоговые показатели. Рассмотрим подробно, почему возникает эта проблема и как построить идеальную архитектуру запроса, которая будет работать быстро и без ошибок.
Давайте проанализируем ситуацию, описанную автором темы. В его исходном решении предпринимается попытка связать данные о продажах (из регистра накопления Продажи) с данными о заказах (из регистра накопления ЗаказыПокупателей) и документами закрытия заказов.
На выходе автор получает дублирование сумм заказов в тех случаях, когда по одному заказу было проведено несколько раздельных расходных накладных (РТУ). Почему так происходит? Дело в нарушении базовых правил реляционной алгебры и специфики работы СУБД при операциях соединения таблиц (JOIN). Когда мы пытаемся связать таблицы, находящиеся на разных уровнях детализации (гранулярности), без предварительной подготовки, СУБД выполняет математическое умножение строк.
Давайте подробно разберем по шагам ключевые ошибки в логике исходного запроса, которые приводят к неверным результатам и снижению производительности системы.
1. Ошибка детализации при связи таблиц (Разная гранулярность)
В первом пакете запроса автор создает временную таблицу ВТ_РТУ, группируя продажи по полям Регистратор и Регистратор.Сделка (то есть по конкретным документам реализации и связанным с ними заказам):
ВЫБРАТЬ
РТУ.Регистратор,
СУММА(РТУ.Стоимость) КАК СуммаРТУ,
РТУ.Регистратор.Сделка КАК Сделка
ПОМЕСТИТЬ ВТ_РТУ
...
СГРУППИРОВАТЬ ПО
РТУ.Регистратор,
РТУ.Регистратор.Сделка
В финальном запросе эта таблица соединяется с таблицей заказов ВТ_Заказы по условию:
ПО Реализации.Регистратор.Сделка = Заказы.ЗаказПокупателя.Ссылка
Представим сценарий: по заказу покупателя №1 на сумму 100 000 рублей были сделаны две отгрузки: РТУ №1 на сумму 40 000 рублей и РТУ №2 на сумму 60 000 рублей. В таблице ВТ_РТУ у нас будет две строки:
В таблице ВТ_Заказы у нас всего одна строка:
При выполнении внутреннего соединения СУБД сопоставит каждую строку из ВТ_РТУ со строкой из ВТ_Заказы. На выходе мы получим две строки:
Когда в самом конце запроса мы применим агрегатную функцию СУММА(СуммаЗаПериод), сумма заказа просуммируется дважды и превратится в 200 000 рублей вместо исходных 100 000! Это и есть классический дубль из-за разницы уровней детализации (гранулярности) соединяемых данных.
2. Отсутствие группировки во временной таблице закрытия заказов
Обратим внимание на пакет формирования таблицы ВТ_ЗакрытиеЗаказов:
ВЫБРАТЬ
ЗаказыЗакрытие.ЗаказПокупателя,
ЗаказыЗакрытие.СуммаВзаиморасчетов КАК СуммаЗакрытия
ПОМЕСТИТЬ ВТ_ЗакрытиеЗаказов
ИЗ
РегистрНакопления.ЗаказыПокупателей КАК ЗаказыЗакрытие
ГДЕ
...
Здесь полностью отсутствует секция СГРУППИРОВАТЬ ПО. Если один и тот же заказ покупателя закрывался несколькими документами «Закрытие заказов покупателей» или в одном документе было несколько строк по данному заказу, то в таблицу ВТ_ЗакрытиеЗаказов попадет несколько записей на один заказ. При последующем левом соединении с таблицей ВТ_Заказы строки заказов размножатся еще сильнее, порождая лавинообразный рост дублей.
3. Разыменование составного типа «Регистратор»
В коде автора активно используется конструкция РТУ.Регистратор.Сделка. Поле Регистратор в регистре накопления Продажи имеет составной тип данных, который может включать десятки различных документов (Реализация товаров и услуг, Отчет о розничных продажах, Передача оборудования и т.д.).
Когда платформа 1С видит обращение через точку к реквизиту составного поля (Регистратор.Сделка), она транслирует это в SQL-запрос как левое соединение (LEFT JOIN) со всеми таблицами документов, входящих в этот составной тип, у которых есть реквизит Сделка. Это приводит к созданию монструозного SQL-кода, неоптимальным планам запроса и колоссальному падению производительности даже на базах среднего размера.
4. Избыточная эмуляция ПОЛНОГО СОЕДИНЕНИЯ через UNION ALL
В финальном пакете автор пытается решить проблему отсутствия связей между заказами и реализациями с помощью конструкции ОБЪЕДИНИТЬ ВСЕ, соединяя три разных выборки: «Заказ + Реализация», «Реализация без Заказа» и «Заказ без Реализации». Использование подзапросов с отрицанием типа НЕ Заказы.ЗаказПокупателя В (ВЫБРАТЬ ...) заставляет СУБД выполнять сканирование таблиц заново, блокирует использование индексов и делает запрос крайне тяжелым.
Для решения подобных задач в 1С существует стандартный, надежный и максимально производительный паттерн проектирования запросов. Вместо того чтобы соединять таблицы с разной детализацией, мы должны привести их к единому знаменателю (уровню аналитики) и объединить их вертикально через ОБЪЕДИНИТЬ ВСЕ, а затем выполнить финальную группировку.
Рассмотрим по шагам, как построить такой запрос:
ЗаказПокупателя, рассчитываем сумму заказов. Помещаем в ВТ_Заказы.ЗаказПокупателя, суммируем величину закрытия. Помещаем в ВТ_Закрытия.ВЫРАЗИТЬ(РТУ.Регистратор КАК Документ.РеализацияТоваровУслуг).Сделка. Группируем продажи строго по сделке (заказу), рассчитываем сумму продаж. Помещаем в ВТ_Продажи.ОБЪЕДИНИТЬ ВСЕ, приводя их к единому набору полей (Сделка, СуммаЗаказа, СуммаЗакрытия, СуммаПродажи). В ветке заказов поля закрытия и продаж заполняем нулями, в ветке продаж — поля заказов и закрытий заполняем нулями и так далее.Сделка и суммируем все числовые ресурсы (СуммаЗаказа, СуммаЗакрытия, СуммаПродажи). На этом этапе мы гарантированно получаем ровно одну строку по каждому заказу (сделке), где без всяких дублей лежат корректные показатели.ВЫБОР КОГДА) на уже сгруппированные данные.Давайте посмотрим на практический пример реализации этой архитектуры. Ниже представлен чистый, оптимизированный и избавленный от дублей текст запроса:
// Шаг 1: Собираем данные по заказам покупателей
ВЫБРАТЬ
Заказы.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(Заказы.СуммаВзаиморасчетов) КАК СуммаЗаказов
ПОМЕСТИТЬ ВТ_Заказы
ИЗ
РегистрНакопления.ЗаказыПокупателей КАК Заказы
ГДЕ
Заказы.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И Заказы.ЗаказПокупателя.Контрагент = &Контрагент
И Заказы.Регистратор ССЫЛКА Документ.ЗаказПокупателя
ГРУППИРОВАТЬ ПО
Заказы.ЗаказПокупателя
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Собираем данные по закрытию заказов (с обязательной группировкой!)
ВЫБРАТЬ
Закрытие.ЗаказПокупателя КАК ЗаказПокупателя,
СУММА(Закрытие.СуммаВзаиморасчетов) КАК СуммаЗакрытия
ПОМЕСТИТЬ ВТ_ЗакрытиеЗаказов
ИЗ
РегистрНакопления.ЗаказыПокупателей КАК Закрытие
ГДЕ
Закрытие.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И Закрытие.ЗаказПокупателя.Контрагент = &Контрагент
И Закрытие.Регистратор ССЫЛКА Документ.ЗакрытиеЗаказовПокупателей
ГРУППИРОВАТЬ ПО
Закрытие.ЗаказПокупателя
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Собираем данные по продажам (исключаем Регистратор, группируем по Сделке)
ВЫБРАТЬ
ВЫРАЗИТЬ(РТУ.Регистратор КАК Документ.РеализацияТоваровУслуг).Сделка КАК Сделка,
СУММА(РТУ.Стоимость) КАК СуммаРТУ
ПОМЕСТИТЬ ВТ_РТУ
ИЗ
РегистрНакопления.Продажи КАК РТУ
ГДЕ
РТУ.Период МЕЖДУ &ДатаНачала И &ДатаОкончания
И РТУ.Контрагент = &Контрагент
И РТУ.Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг
ГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(РТУ.Регистратор КАК Документ.РеализацияТоваровУслуг).Сделка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Объединяем все три источника данных в один поток
ВЫБРАТЬ
Заказы.ЗаказПокупателя КАК Сделка,
Заказы.СуммаЗаказов КАК СуммаЗаказа,
0 КАК СуммаЗакрытия,
0 КАК СуммаРТУ
ПОМЕСТИТЬ ВТ_ВсеПотоки
ИЗ
ВТ_Заказы КАК Заказы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Закрытие.ЗаказПокупателя,
0,
Закрытие.СуммаЗакрытия,
0
ИЗ
ВТ_ЗакрытиеЗаказов КАК Закрытие
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
РТУ.Сделка,
0,
0,
РТУ.СуммаРТУ
ИЗ
ВТ_РТУ КАК РТУ
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 5: Сворачиваем (группируем) данные по Сделке
ВЫБРАТЬ
ВсеДанные.Сделка КАК Сделка,
СУММА(ВсеДанные.СуммаЗаказа) КАК СуммаЗаказа,
СУММА(ВсеДанные.СуммаЗакрытия) КАК СуммаЗакрытия,
СУММА(ВсеДанные.СуммаРТУ) КАК СуммаРТУ
ПОМЕСТИТЬ ВТ_Сгруппировано
ИЗ
ВТ_ВсеПотоки КАК ВсеДанные
ГРУППИРОВАТЬ ПО
ВсеДанные.Сделка
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 6: Рассчитываем итоговые показатели с учетом условий бизнеса
ВЫБРАТЬ
Итог.Сделка КАК Сделка,
ВЫБОР
КОГДА Итог.СуммаЗаказа - Итог.СуммаЗакрытия <= Итог.СуммаРТУ
ТОГДА Итог.СуммаРТУ
ИНАЧЕ Итог.СуммаЗаказа - Итог.СуммаЗакрытия
КОНЕЦ КАК СуммаЗаПериод
ПОМЕСТИТЬ ВТ_ИТОГ
ИЗ
ВТ_Сгруппировано КАК Итог
;
////////////////////////////////////////////////////////////////////////////////
// Получение финальной суммы
ВЫБРАТЬ
СУММА(ВТ_ИТОГ.СуммаЗаПериод) КАК СуммаЗаПериод
ИЗ
ВТ_ИТОГ КАК ВТ_ИТОГ
Проанализируем преимущества обновленной архитектуры перед исходным вариантом:
ОБЪЕДИНИТЬ ВСЕ мы полностью исключили декартово произведение строк. Каждая сделка обрабатывается ровно один раз.ВЫРАЗИТЬ).ОБЪЕДИНИТЬ ВСЕ в шаге 4, не переписывая сложные логические связи и условия соединений.Используйте данный паттерн всегда, когда вам нужно сопоставить данные из разных регистров по общему объекту аналитики. Это убережет ваши отчеты от неверных итоговых сумм и обеспечит отличную скорость работы системы даже при больших объемах данных.