Как запросом распределить складские остатки по строкам заказа без свертки таблицы номенклатуры?

← На главную

В практике разработки на платформе 1С часто возникает задача распределения имеющегося складского запаса по строкам потребности (например, по строкам табличной части заказа покупателя). Ситуация усложняется, когда одна и та же позиция номенклатуры встречается в документе несколько раз, а сворачивать строки нельзя из-за необходимости сохранять исходную привязку: номер строки, ключ связи, заказ-источник или статус обеспечения.

Разберем, как решить эту задачу исключительно средствами языка запросов 1С: распределить доступный остаток по порядку строк и вывести недостающее количество (дефицит) отдельной строкой к заказу.

Постановка задачи и исходные данные

Проанализируем типовую ситуацию на числовом примере:

Таблица потребности содержит две строки с одной номенклатурой: Строка №1 — 4 шт., Строка №2 — 6 шт. (всего требуется 10 шт.).
На складе числится свободный остаток: 8 шт.
Требуется получить распределение:
1. Для строки №1 берем 4 шт. (строка обеспечена полностью).
2. Для строки №2 берем 4 шт. (оставшийся остаток: 8 - 4).
3. В дополнительную строку дефицита выводим 2 шт. (нехватка: 10 - 8), чтобы сформировать потребность к закупке или перемещению.

Поскольку встроенный язык запросов 1С не поддерживает стандартные оконные функции (такие как SUM(...) OVER (...)), для решения применяется паттерн нарастающего итога через самосоединение временных таблиц.

Пошаговое построение запроса с нарастающим итогом

Рассмотрим алгоритм реализации задачи по шагам:

Формирование исходных временных таблиц: помещаем в таблицы строки потребностей с уникальным порядковым номером (НомерСтроки) и актуальные остатки товаров.
Расчет кумулятивной суммы (нарастающего итога): соединяем таблицу потребностей саму с собой по условию равенства номенклатуры и нестрогого неравенства по номеру строки (тТаб.НомерСтроки >= тТаб1.НомерСтроки). Это позволяет для каждой текущей строки получить суммарную потребность всех предыдущих строк вместе с текущей.
Распределение остатка: с помощью логического выражения ВЫБОР КОГДА сравниваем имеющийся остаток с нарастающим итогом и определяем, какое количество может покрыть текущую строку.
Вычисление дефицита: с помощью объединения выборок (ОБЪЕДИНИТЬ ВСЕ) рассчитываем разницу между общей потребностью и складским остатком для тех позиций, где потребность превышает наличие.

Посмотрим на полный текст запроса, реализующий данный алгоритм:


// 1. Исходные потребности (имитация табличной части документа)
ВЫБРАТЬ
    1 КАК НомерСтроки,
    "Гайка" КАК Номенклатура,
    4 КАК Количество
ПОМЕСТИТЬ ВТ_Потребность

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    2,
    "Гайка",
    6

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    3,
    "Шайба",
    7
;

////////////////////////////////////////////////////////////////////////////////
// 2. Исходные остатки (имитация регистра накопления)
ВЫБРАТЬ
    "Гайка" КАК Номенклатура,
    8 КАК Остаток
ПОМЕСТИТЬ ВТ_Остатки

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    "Шайба",
    20
;

////////////////////////////////////////////////////////////////////////////////
// 3. Расчет нарастающего итога потребности по каждой строке
ВЫБРАТЬ
    Потребность.НомерСтроки КАК НомерСтроки,
    Потребность.Номенклатура КАК Номенклатура,
    Потребность.Количество КАК Количество,
    СУММА(ПотребностьДо.Количество) КАК КоличествоНарастающее
ПОМЕСТИТЬ ВТ_ПотребностьСИтогом
ИЗ
    ВТ_Потребность КАК Потребность
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Потребность КАК ПотребностьДо
        ПО Потребность.Номенклатура = ПотребностьДо.Номенклатура
            И Потребность.НомерСтроки >= ПотребностьДо.НомерСтроки
СГРУППИРОВАТЬ ПО
    Потребность.НомерСтроки,
    Потребность.Номенклатура,
    Потребность.Количество
;

////////////////////////////////////////////////////////////////////////////////
// 4. Итоговое распределение остатков и расчет строк дефицита
ВЫБРАТЬ
    ВТ_ПотребностьСИтогом.НомерСтроки КАК НомерСтроки,
    ВТ_ПотребностьСИтогом.Номенклатура КАК Номенклатура,
    ВТ_ПотребностьСИтогом.Количество КАК Требуется,
    ВЫБОР
        КОГДА ЕСТЬNULL(ВТ_Остатки.Остаток, 0) >= ВТ_ПотребностьСИтогом.КоличествоНарастающее
            ТОГДА ВТ_ПотребностьСИтогом.Количество
        ИНАЧЕ ВЫБОР
            КОГДА ЕСТЬNULL(ВТ_Остатки.Остаток, 0) - ВТ_ПотребностьСИтогом.КоличествоНарастающее + ВТ_ПотребностьСИтогом.Количество > 0
                ТОГДА ЕСТЬNULL(ВТ_Остатки.Остаток, 0) - ВТ_ПотребностьСИтогом.КоличествоНарастающее + ВТ_ПотребностьСИтогом.Количество
            ИНАЧЕ 0
        КОНЕЦ
    КОНЕЦ КАК Обеспечено
ИЗ
    ВТ_ПотребностьСИтогом КАК ВТ_ПотребностьСИтогом
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
        ПО ВТ_ПотребностьСИтогом.Номенклатура = ВТ_Остатки.Номенклатура

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    NULL КАК НомерСтроки,
    ОбщаяПотребность.Номенклатура КАК Номенклатура,
    0 КАК Требуется,
    ОбщаяПотребность.ВсегоПотребность - ЕСТЬNULL(ВТ_Остатки.Остаток, 0) КАК Обеспечено
ИЗ
    (ВЫБРАТЬ
        ВТ_Потребность.Номенклатура КАК Номенклатура,
        СУММА(ВТ_Потребность.Количество) КАК ВсегоПотребность
    ИЗ
        ВТ_Потребность КАК ВТ_Потребность
    СГРУППИРОВАТЬ ПО
        ВТ_Потребность.Номенклатура) КАК ОбщаяПотребность
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Остатки КАК ВТ_Остатки
        ПО ОбщаяПотребность.Номенклатура = ВТ_Остатки.Номенклатура
ГДЕ
    ОбщаяПотребность.ВсегоПотребность > ЕСТЬNULL(ВТ_Остатки.Остаток, 0)

Математическая модель интервалов (альтернативный способ распределения)

Для более компактного и строгого расчета распределения без вложенных условий ВЫБОР КОГДА можно представить процесс как пересечение двух числовых отрезков:

Каждая строка потребности занимает диапазон: [НакопленоДо, НакопленоПосле], где НакопленоДо = КоличествоНарастающее - Количество, а НакопленоПосле = КоличествоНарастающее.
Доступный остаток задает интервал покрытия: [0, Остаток].
Длина пересечения двух отрезков рассчитывается по математической формуле:
Длина = МАКСИМУМ(0, МИНИМУМ(НакопленоПосле, Остаток) - МАКСИМУМ(НакопленоДо, 0))

Применение такой логики позволяет упростить громоздкие ветвления в выражении ВЫБОР при сложных схемах распределения по нескольким складам или партиям.

Ограничения производительности и важные нюансы

Разберем технические особенности, которые необходимо учитывать при использовании нарастающего итога в реальных конфигурациях:

Квадратичная сложность ($O(N^2)$): Соединение таблицы самой с собой по неравенству (>=) при большом числе строк номенклатуры приводит к лавинообразному росту промежуточных записей. Для единичных документов на десятки строк это незаметно, но при пакетной обработке сотен заказов запрос может создавать высокую нагрузку на СУБД.
Индексация временных таблиц: Для исключения полного сканирования таблиц (Table Scan) временные таблицы, участвующие в соединении, необходимо обязательно индексировать:
```
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    НомерСтроки
;
```
Детерминированность сортировки: Поле, по которому строится неравенство, должно быть строго уникальным в пределах группировки. Если номера строк могут дублироваться (например, при объединении нескольких табличных частей), в качестве упорядочивающего ключа необходимо использовать составной ключ (например, Ссылка + НомерСтроки).
Обработка отсутствующих остатков: Соединение с таблицей остатков обязательно должно быть ЛЕВЫМ с обязательным применением функции ЕСТЬNULL(Остаток, 0), иначе позиции, которых вовсе нет на складе, пропадут из итоговой выборки.
Альтернатива на встроенном языке 1С: Если жесткое требование выполнять расчет внутри одного запроса отсутствует, линейный проход по предварительно отсортированной ТаблицеЗначений выполняется за линейное время $O(N)$. Это работает значительно быстрее на больших объемах данных и не нагружает временную базу данных СУБД (tempdb).