В мире 1С, как и в любой системе учета товарных запасов, часто возникает необходимость получить данные об остатках номенклатуры на складах. Однако иногда требуется решить более специфичную задачу: вывести только те позиции номенклатуры, которые есть в наличии на абсолютно всех складах компании. Представьте ситуацию, когда у вас есть пять складов, и вам нужно найти только те товары, которые имеются на каждом из них. Если хотя бы на одном складе позиции нет, она не должна попасть в итоговый список.
Эта задача является классической в реляционной алгебре и известна как операция "деление" (division). Мы рассмотрим несколько подходов к её решению в языке запросов 1С, уделяя внимание как наиболее распространенным, так и альтернативным методам, а также важным аспектам, таким как производительность и универсальность.
Один из самых интуитивно понятных и эффективных способов решения данной задачи заключается в том, чтобы сначала определить общее количество активных складов в системе, а затем в основном запросе проверить, совпадает ли количество складов, на которых есть остаток по каждой номенклатурной позиции, с этим общим количеством. Разберем этот подход по шагам.
Определение общего количества складов в системе
Первым делом нам необходимо узнать, сколько всего складов активно используется в нашей конфигурации. Это можно сделать, обратившись к справочнику Справочник.Склады. Для удобства и оптимизации дальнейшего запроса, мы поместим результат этого подсчета во временную таблицу. Это позволит нам избежать повторного вычисления и сделает запрос более читаемым.
ВЫБРАТЬ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Склады.Ссылка) КАК КоличествоСкладов
ПОМЕСТИТЬ ВТ_Склады
ИЗ
Справочник.Склады КАК Склады
;
В этом запросе мы используем функцию КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Склады.Ссылка), чтобы получить уникальное число складов. Результат помещается во временную таблицу ВТ_Склады, которая будет содержать всего одну запись с одним полем КоличествоСкладов.
Формирование основного запроса к регистру остатков
После того как мы узнали общее число складов, мы можем приступить к формированию основного запроса к регистру накопления, который хранит информацию об остатках номенклатуры. Обычно это регистр РегистрНакопления.ТоварыНаСкладах.Остатки или аналогичный. Мы будем группировать данные по номенклатуре и подсчитывать количество различных складов, на которых есть остаток по каждой позиции.
//////////////////////////////////////////////////////////////
ВЫБРАТЬ
ТоварыНаСкладахОстатки.Номенклатура,
ВТ_Склады.КоличествоСкладов
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(
&МоментВремени,
КоличествоОстаток > 0 // Важно: учитываем только положительные остатки
) КАК ТоварыНаСкладахОстатки
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Склады КАК ВТ_Склады
ПО (ИСТИНА) // Декартово произведение или просто привязка, так как ВТ_Склады - одна строка
СГРУППИРОВАТЬ ПО
ТоварыНаСкладахОстатки.Номенклатура,
ВТ_Склады.КоличествоСкладов
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТоварыНаСкладахОстатки.Склад) = ВТ_Склады.КоличествоСкладов
;
Давайте подробно разберем этот запрос:
РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментВремени, КоличествоОстаток > 0): Мы обращаемся к виртуальной таблице остатков регистра накопления. Очень важно использовать параметр &МоментВремени для указания даты и времени, на которое мы хотим получить остатки. Также мы добавляем условие КоличествоОстаток > 0, чтобы учитывать только те склады, где действительно есть товар, а не нулевые остатки, которые могут быть записаны в регистре.ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Склады КАК ВТ_Склады ПО (ИСТИНА): Мы соединяем нашу виртуальную таблицу остатков с временной таблицей ВТ_Склады. Поскольку ВТ_Склады содержит всего одну строку с общим количеством складов, условие ПО (ИСТИНА) фактически присоединяет это общее количество к каждой строке остатков, что позволяет использовать его в условии ИМЕЮЩИЕ.СГРУППИРОВАТЬ ПО ТоварыНаСкладахОстатки.Номенклатура, ВТ_Склады.КоличествоСкладов: Мы группируем полученные данные по номенклатуре. Это позволяет нам агрегировать информацию для каждой позиции. Мы также включаем ВТ_Склады.КоличествоСкладов в группировку, поскольку это поле используется в части ВЫБРАТЬ и является частью нашего условия агрегации.ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТоварыНаСкладахОстатки.Склад) = ВТ_Склады.КоличествоСкладов: Это ключевое условие. Мы подсчитываем количество различных складов, на которых есть остаток по каждой группе (номенклатуре). Если это количество равно общему числу складов, полученному на первом шаге, то данная номенклатура удовлетворяет нашему условию и включается в результат.Таким образом, этот пакетный запрос эффективно решает задачу, выводя список номенклатуры, которая есть в наличии на всех складах. Он также универсален, так как не требует ручного изменения при добавлении или удалении складов, поскольку количество складов определяется динамически.
Обратим особое внимание на параметр &МоментВремени в запросах к виртуальным таблицам остатков. В 1С, регистры накопления оперируют остатками на определенный момент времени. Если вы не укажете этот параметр или укажете его некорректно, запрос может вернуть остатки на текущий момент (по умолчанию) или на момент формирования отчета, что не всегда соответствует вашим требованиям.
Виртуальная таблица остатков, например, РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментВремени), строится на начало секунды указанного МоментаВремени, не включая движения, произошедшие в эту же секунду. Если вам необходимо учитывать движения за весь указанный день, рекомендуется использовать либо МоментВремени, соответствующий концу дня (например, Дата(Год, Месяц, День, 23, 59, 59)), либо передавать Границу (Новый Граница(МоментВремени, ВидГраницы.Включая)), если есть такая возможность (зависит от версии платформы и метода вызова).
Еще один мощный подход к решению задачи "деления" основан на логике исключения. Вместо того чтобы искать то, что есть на всех складах, мы можем найти то, чего нет хотя бы на одном складе, а затем исключить эти позиции из общего списка номенклатуры. Рассмотрим эту стратегию:
Создание всех возможных комбинаций "Номенклатура - Склад"
Нам потребуется список всех возможных пар номенклатуры и складов, которые могут существовать в системе. Это можно получить, например, путем декартова произведения (CROSS JOIN, или в 1С это реализуется как ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПО (ИСТИНА)) справочника Номенклатура и справочника Склады. Этот шаг создаст "полную матрицу" всех потенциальных размещений товаров.
ВЫБРАТЬ
Номенклатура.Ссылка КАК Номенклатура,
Склады.Ссылка КАК Склад
ПОМЕСТИТЬ ВТ_ВсеКомбинации
ИЗ
Справочник.Номенклатура КАК Номенклатура,
Справочник.Склады КАК Склады
;
Эта временная таблица ВТ_ВсеКомбинации будет содержать каждую номенклатурную позицию, связанную с каждым складом.
Левое соединение с остатками и поиск отсутствующих
Теперь мы выполним ЛЕВОЕ СОЕДИНЕНИЕ нашей временной таблицы ВТ_ВсеКомбинации с регистром остатков ТоварыНаСкладах.Остатки. Если для какой-либо комбинации "Номенклатура - Склад" остаток не будет найден в регистре (или будет равен нулю), соответствующее поле КоличествоОстаток из регистра остатков будет иметь значение NULL (или 0, если его явно обрабатывать).
//////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ_ВсеКомбинации.Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураСОтсутствием
ИЗ
ВТ_ВсеКомбинации КАК ВТ_ВсеКомбинации
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
&МоментВремени,
КоличествоОстаток > 0
) КАК ТоварыНаСкладахОстатки
ПО ВТ_ВсеКомбинации.Номенклатура = ТоварыНаСкладахОстатки.Номенклатура
И ВТ_ВсеКомбинации.Склад = ТоварыНаСкладахОстатки.Склад
ГДЕ
ТоварыНаСкладахОстатки.Номенклатура ЕСТЬ NULL
ИЛИ ТоварыНаСкладахОстатки.КоличествоОстаток ЕСТЬ NULL
;
Здесь мы отбираем все записи, где ТоварыНаСкладахОстатки.Номенклатура ЕСТЬ NULL (т.е., для данной пары номенклатура-склад нет записи в остатках) или ТоварыНаСкладахОстатки.КоличествоОстаток ЕСТЬ NULL (если в условии виртуальной таблицы не был учтен КоличествоОстаток > 0, но логика остатков подразумевает NULL при отсутствии). Результат этого запроса – список номенклатуры, которая отсутствует хотя бы на одном складе.
Исключение отсутствующей номенклатуры из общего списка
Наконец, мы формируем запрос ко всему справочнику Номенклатура и исключаем из него те позиции, которые мы нашли во временной таблице ВТ_НоменклатураСОтсутствием.
//////////////////////////////////////////////////////////////
ВЫБРАТЬ
Номенклатура.Ссылка,
Номенклатура.Наименование
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.Ссылка В (
ВЫБРАТЬ
ВТ_НоменклатураСОтсутствием.Номенклатура
ИЗ
ВТ_НоменклатураСОтсутствием
)
;
Этот подход может быть более ресурсоемким для больших баз данных, так как он сначала генерирует все возможные комбинации, что может привести к созданию очень большой временной таблицы. Однако он является очень гибким и понятным с точки зрения логики.
Этот метод похож на предыдущий по логике исключения, но может быть реализован более компактно. Суть его также в том, чтобы найти номенклатуру, которая отсутствует хотя бы на одном складе, и затем отсеять ее из полного списка. Разберем по шагам:
Поиск номенклатуры, отсутствующей хотя бы на одном складе
Этот шаг является ключевым. Мы должны найти каждую номенклатурную позицию, для которой существует хотя бы один склад, где ее нет. Для этого мы можем получить список всех складов, а затем для каждой номенклатуры найти склады, на которых ее нет, используя ЛЕВОЕ СОЕДИНЕНИЕ или РАЗНОСТЬ.
Рассмотрим вариант с ЛЕВОЕ СОЕДЕНЕНИЕ, как это было в одной из попыток решения в обсуждении, но с учетом исправления ошибок:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВсеСклады.Ссылка КАК Склад,
ВсеНоменклатура.Ссылка КАК Номенклатура
ПОМЕСТИТЬ ВТ_ОтсутствующаяНоменклатураНаСкладе
ИЗ
Справочник.Склады КАК ВсеСклады,
Справочник.Номенклатура КАК ВсеНоменклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки(
&МоментВремени,
КоличествоОстаток > 0
) КАК ТоварыНаСкладахОстатки
ПО ВсеСклады.Ссылка = ТоварыНаСкладахОстатки.Склад
И ВсеНоменклатура.Ссылка = ТоварыНаСкладахОстатки.Номенклатура
ГДЕ
ТоварыНаСкладахОстатки.Склад ЕСТЬ NULL
;
Здесь мы создаем все возможные комбинации складов и номенклатуры, а затем левым соединением ищем, где остатков нет. В результате ВТ_ОтсутствующаяНоменклатураНаСкладе будут записи только о тех парах номенклатуры и склада, где товар отсутствует (или его количество равно 0).
Однако, для нашей задачи, нам нужна именно номенклатура, которая отсутствует хотя бы на одном складе. Поэтому, нам необходимо из ВТ_ОтсутствующаяНоменклатураНаСкладе выбрать только поле Номенклатура.
//////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ_ОтсутствующаяНоменклатураНаСкладе.Номенклатура
ПОМЕСТИТЬ ВТ_НоменклатураБезВсехСкладов
ИЗ
ВТ_ОтсутствующаяНоменклатураНаСкладе КАК ВТ_ОтсутствующаяНоменклатураНаСкладе
;
Исключение этой номенклатуры из общего списка
Теперь, когда у нас есть список ВТ_НоменклатураБезВсехСкладов (номенклатура, которая отсутствует хотя бы на одном складе), мы можем использовать оператор НЕ В для получения искомого результата.
//////////////////////////////////////////////////////////////
ВЫБРАТЬ
Номенклатура.Ссылка,
Номенклатура.Наименование
ИЗ
Справочник.Номенклатура КАК Номенклатура
ГДЕ
НЕ Номенклатура.Ссылка В (
ВЫБРАТЬ
ВТ_НоменклатураБезВсехСкладов.Номенклатура
ИЗ
ВТ_НоменклатураБезВсехСкладов
)
;
Этот метод, как и предыдущий, основан на принципе "найти отсутствующее и исключить". Он может быть более читаемым для некоторых разработчиков, но его производительность также будет зависеть от размера справочников Номенклатура и Склады.
При работе с запросами в 1С всегда следует помнить о нескольких ключевых моментах, которые влияют на корректность и производительность ваших решений:
Актуальность данных и "Момент времени"
Как мы уже обсуждали, для запросов к регистрам остатков критически важно правильно указать параметр &МоментВремени. Без него данные могут быть неактуальными. Всегда старайтесь использовать этот параметр для виртуальных таблиц остатков. Например, если отчет строится на конец дня, убедитесь, что &МоментВремени установлен на 23:59:59 текущего дня, или используйте Границу.
Производительность запроса и временные таблицы
При работе с большими объемами данных производительность запроса становится приоритетной. Использование временных таблиц (конструкция ПОМЕСТИТЬ) в пакетных запросах может значительно улучшить производительность и читаемость. Результаты промежуточных выборок кэшируются, и СУБД не приходится пересчитывать их несколько раз.
Кроме того, при написании запросов в 1С всегда рекомендуется по возможности использовать параметры виртуальных таблиц для отбора данных на ранних стадиях (например, РегистрНакопления.ТоварыНаСкладах.Остатки(&МоментВремени, КоличествоОстаток > 0)). Это позволяет СУБД отфильтровать ненужные данные еще до их загрузки в оперативную память, что существенно сокращает объем обрабатываемой информации.
Универсальность решения
Решение, предложенное в первом подходе (сообщение 7), с динамическим подсчетом количества складов, является наиболее универсальным. Оно не требует ручного изменения запроса при добавлении или удалении складов в системе, что делает его устойчивым к изменениям бизнес-логики и структуры данных. Избегайте "зашивания" фиксированных чисел (например, = 5) в запросы, если эти числа могут меняться.
Система Компоновки Данных (СКД)
Для более сложных аналитических задач, а также в случаях, когда требуется пользовательская настройка отборов, группировок и различных вариантов отчетов, часто используют Систему Компоновки Данных (СКД). СКД позволяет строить гибкие и интерактивные отчеты на основе ваших запросов, значительно упрощая их дальнейшую модификацию и адаптацию под нужды конечных пользователей. Если вы разрабатываете отчет для пользователя, то скорее всего, вы будете использовать СКД, а описанные запросы станут основой для его набора данных.
Мы рассмотрели несколько методов решения классической задачи "деления" в контексте 1С. Каждый из них имеет свои особенности и может быть более или менее эффективным в зависимости от конкретных условий, объемов данных и требований к производительности. Выбор оптимального решения всегда остается за разработчиком, который, анализируя ситуацию, выбирает наиболее подходящий инструмент и подход.