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