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