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