Как распределить партии товаров по заказам одним запросом в 1С?

Часто перед разработчиками встает классическая задача: имеются остатки товаров на складах и потребности (заказы) контрагентов. Необходимо распределить доступное количество товаров между заказами по принципу FIFO (первым пришел — первым ушел), максимально удовлетворив потребности с наиболее ранней датой. Многие пытаются решить эту задачу исключительно средствами языка запросов 1С. Давайте разберем по шагам, как это можно реализовать, проанализируем подводные камни такого подхода и рассмотрим более оптимальные альтернативы.

Решение задачи списания по партиям одним запросом

Для того чтобы распределить остатки, нам необходимо сопоставить две таблицы: таблицу остатков и таблицу потребностей. Главная сложность при решении этой задачи одним запросом заключается в том, что нам нужно знать, сколько товара уже было распределено на предыдущие заказы. В классическом SQL для этого существуют оконные функции, но в 1С их пока нет. Поэтому мы используем прием расчета «нарастающего итога».

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

// Подготовка тестовых данных
ВЫБРАТЬ
    "Товар1" КАК Товар,
    "Склад1" КАК Склад,
    10 КАК Количество
ПОМЕСТИТЬ Остатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "Товар2", "Склад1", 20
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "Товар2", "Склад3", 15;

ВЫБРАТЬ
    ДАТАВРЕМЯ(2020, 1, 1) КАК Дата,
    "Товар1" КАК Товар,
    "Контрагент1" КАК Контрагент,
    12 КАК Количество
ПОМЕСТИТЬ Заказы
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2020, 2, 2), "Товар1", "Контрагент2", 5
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ ДАТАВРЕМЯ(2020, 1, 3), "Товар2", "Контрагент2", 25;

// Получаем общие остатки по товарам
ВЫБРАТЬ
    Остатки.Товар,
    СУММА(Остатки.Количество) КАК Количество
ПОМЕСТИТЬ ОстаткиОбщие
ИЗ Остатки КАК Остатки
СГРУППИРОВАТЬ ПО Остатки.Товар;

// Получаем общие потребности по товарам
ВЫБРАТЬ
    Заказы.Товар,
    СУММА(Заказы.Количество) КАК Количество
ПОМЕСТИТЬ ЗаказыОбщие
ИЗ Заказы КАК Заказы
СГРУППИРОВАТЬ ПО Заказы.Товар;

// Вычисляем дефицит товаров
ВЫБРАТЬ
    ЗаказыОбщие.Товар,
    ЕСТЬNULL(ОстаткиОбщие.Количество, 0) КАК КоличествоОт,
    ЗаказыОбщие.Количество КАК КоличествоДо
ПОМЕСТИТЬ КоридорДефицита
ИЗ ЗаказыОбщие КАК ЗаказыОбщие
    ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиОбщие КАК ОстаткиОбщие
    ПО ЗаказыОбщие.Товар = ОстаткиОбщие.Товар
ГДЕ ЗаказыОбщие.Количество > ЕСТЬNULL(ОстаткиОбщие.Количество, 0);

// Расчет нарастающего итога по заказам
ВЫБРАТЬ
    Заказы.Дата КАК Дата,
    Заказы.Товар КАК Товар,
    Заказы.Контрагент КАК Контрагент,
    ЕСТЬNULL(СУММА(Заказы1.Количество), 0) КАК КоличествоОт,
    ЕСТЬNULL(СУММА(Заказы1.Количество), 0) + Заказы.Количество КАК КоличествоДо
ПОМЕСТИТЬ ЗаказыНарастающе
ИЗ Заказы КАК Заказы
    ЛЕВОЕ СОЕДИНЕНИЕ Заказы КАК Заказы1
    ПО Заказы.Товар = Заказы1.Товар
        И (Заказы.Дата > Заказы1.Дата
        ИЛИ (Заказы.Дата = Заказы1.Дата И Заказы.Контрагент > Заказы1.Контрагент))
СГРУППИРОВАТЬ ПО
    Заказы.Контрагент, Заказы.Дата, Заказы.Товар, Заказы.Количество;

// Расчет нарастающего итога по остаткам
ВЫБРАТЬ
    Остатки.Товар КАК Товар,
    Остатки.Склад КАК Склад,
    ЕСТЬNULL(СУММА(Остатки1.Количество), 0) КАК КоличествоОт,
    ЕСТЬNULL(СУММА(Остатки1.Количество), 0) + Остатки.Количество КАК КоличествоДо
ПОМЕСТИТЬ ОстаткиНарастающе
ИЗ Остатки КАК Остатки
    ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки1
    ПО Остатки.Товар = Остатки1.Товар
        И Остатки.Склад > Остатки1.Склад
СГРУППИРОВАТЬ ПО
    Остатки.Склад, Остатки.Товар, Остатки.Количество
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
    КоридорДефицита.Товар, NULL, КоридорДефицита.КоличествоОт, КоридорДефицита.КоличествоДо
ИЗ КоридорДефицита КАК КоридорДефицита;

// Итоговое сопоставление
ВЫБРАТЬ
    ЗаказыНарастающе.Дата КАК Дата,
    ЗаказыНарастающе.Товар КАК Товар,
    ЗаказыНарастающе.Контрагент КАК Контрагент,
    ОстаткиНарастающе.Склад КАК Склад,
    ВЫБОР
        КОГДА ЗаказыНарастающе.КоличествоДо < ОстаткиНарастающе.КоличествоДо
        ТОГДА ЗаказыНарастающе.КоличествоДо
        ИНАЧЕ ОстаткиНарастающе.КоличествоДо
    КОНЕЦ - ВЫБОР
        КОГДА ЗаказыНарастающе.КоличествоОт > ОстаткиНарастающе.КоличествоОт
        ТОГДА ЗаказыНарастающе.КоличествоОт
        ИНАЧЕ ОстаткиНарастающе.КоличествоОт
    КОНЕЦ КАК Количество
ИЗ ЗаказыНарастающе КАК ЗаказыНарастающе
    ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиНарастающе КАК ОстаткиНарастающе
    ПО ЗаказыНарастающе.Товар = ОстаткиНарастающе.Товар
        И ЗаказыНарастающе.КоличествоОт < ОстаткиНарастающе.КоличествоДо
        И ЗаказыНарастающе.КоличествоДо > ОстаткиНарастающе.КоличествоОт;

Данный запрос формирует границы КоличествоОт и КоличествоДо для каждой партии и каждого заказа. На этапе итогового соединения мы находим пересечения этих границ, что и дает нам точное количество товара, которое можно списать со склада под конкретный заказ.

Почему использовать этот метод в реальной базе — плохая идея?

Проанализируем ситуацию с точки зрения производительности. Главная проблема скрыта в блоках формирования временных таблиц ЗаказыНарастающе и ОстаткиНарастающе.

1. Квадратичная сложность алгоритма (O(N²)): Соединение таблицы самой с собой по условию неравенства (например, Заказы.Дата > Заказы1.Дата) заставляет СУБД для каждой строки перебирать все предыдущие строки. Если у нас 10 заказов по товару, будет обработано 100 связей. Если заказов 1000 — количество связей превысит миллион. На реальных объемах данных это вызовет катастрофическое падение производительности и зависание системы.
2. Отсутствие индексов: В приведенном примере временные таблицы не индексируются. При соединениях по условиям больше/меньше полное сканирование таблиц (Table Scan) гарантировано. Если вы все же решите использовать этот подход, обязательно применяйте конструкцию ИНДЕКСИРОВАТЬ ПО Товар, Дата при помещении данных во временные таблицы.
3. Ограничения языка запросов: Полноценные оконные функции SQL, позволяющие рассчитать нарастающий итог "на лету" без декартова произведения (вида SUM(Количество) OVER(...)), в 1С пока недоступны.

Альтернативные и правильные решения

Разберем, как сделать распределение эффективно.

1. Классический программный цикл

Самый надежный и производительный способ — получить два отдельных массива данных (отсортированные остатки и отсортированные заказы) и выполнить распределение в коде 1С за один проход. Этот алгоритм имеет линейную сложность O(N) и отрабатывает доли секунды даже на огромных объемах.

// Пример логики в коде
Пока ВыборкаЗаказы.Следующий() Цикл
    Требуется = ВыборкаЗаказы.Количество;
    Пока Требуется > 0 И ВыборкаОстатки.Следующий() Цикл
        Списываем = Мин(Требуется, ВыборкаОстатки.СвободныйОстаток);
        // ... фиксируем списание ...
        Требуется = Требуется - Списываем;
    КонецЦикла;
КонецЦикла;

2. Использование Системы Компоновки Данных (СКД)

Если стоит жесткое условие избежать объемных программных циклов, мы можем делегировать расчет нарастающего итога процессору компоновки данных. СКД умеет вычислять итоги линейно с помощью функции ВычислитьВыражение("Сумма(Количество)", "Товар", , "Первая", "Текущая"). Сгенерировав нужные нарастающие итоги в СКД программно, мы можем выгрузить результат в таблицу значений и использовать ее для дальнейшего проведения документов.

Итог: Решить задачу списания по партиям одним запросом можно, но применять это в рабочих (Production) базах данных настоятельно не рекомендуется. Используйте запросы для быстрого получения данных из СУБД, а саму бизнес-логику распределения реализуйте на стороне сервера приложений 1С с помощью программных циклов.