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