Как объединить две таблицы в запросе 1С по количеству без дублирования строк (партионное списание)

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

В этой статье мы подробно разберем, почему простые условия связи и конструкции ВЫБОР КОГДА в секции СУММА не спасают от этой проблемы, а также детально изучим надежный математический алгоритм — интервальный метод распределения нарастающим итогом, реализуемый исключительно средствами языка запросов 1С.

В чем суть проблемы: пример декартова произведения

Рассмотрим ситуацию, описанную автором темы. В документе списания (Расходной накладной) у нас есть две строки с одинаковым товаром и серией, но разными ценами:

1. Товар А, Серия Х — Количество 2.0
2. Товар А, Серия Х — Количество 1.0

В регистре партий на остатках числятся две партии этого же товара с той же серией:

1. Партия 1 — Количество 1.0
2. Партия 2 — Количество 2.0

Если мы соединим эти таблицы обычным левым соединением по полям Товар и Серия, СУБД сопоставит каждую строчку расхода с каждой строчкой партий. В результате мы получим 4 строки:

• Расход 2.0 соединится с Партией 1.0 (произойдет задвоение)
• Расход 2.0 соединится с Партией 2.0
• Расход 1.0 соединится с Партией 1.0
• Расход 1.0 соединится с Партией 2.0

Попытки применить группировку и конструкции вроде ВЫБОР КОГДА Количество1 > Количество2 ТОГДА... не решат проблему на уровне строк, так как СУБД физически плодит комбинации записей до того, как отрабатывают функции группировки. Нам же требуется строгое, последовательное распределение партий: сначала закрыть потребность первой строки расхода, затем перейти ко второй.

Решение: Интервальный метод нарастающих итогов

Для корректного сопоставления строк без дублирования мы применим математическую модель пересечения интервалов нарастающего итога. Мысленно представим наши потребности и наши запасы в виде непрерывных шкал (осей координат).

Для каждой строки расхода и каждой строки партий мы рассчитаем виртуальный интервал «ОТ» и «ДО»:

• Показатель ОТ — это количество единиц товара, которое уже было распределено (накоплено) до обработки текущей строки.
• Показатель ДО — это накопительный итог, включая текущую строку.

Проанализируем наши данные в рамках этой модели:

Таблица Расхода (Потребность):

• Строка 1 (надо 2 шт.): интервал равен [0; 2] (ОТ = 0, ДО = 2)
• Строка 2 (надо 1 шт.): интервал равен [2; 3] (ОТ = 2, ДО = 3)

Таблица Партий (Наличие):

• Партия 1 (есть 1 шт.): интервал равен [0; 1] (ОТ = 0, ДО = 1)
• Партия 2 (есть 2 шт.): интервал равен [1; 3] (ОТ = 1, ДО = 3)

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

Расход.ОТ < Партия.ДО И Партия.ОТ < Расход.ДО

Посмотрим, какие пары строк теперь свяжутся между собой:

• Строка расхода 1 [0; 2] и Партия 1 [0; 1]: связи пересекаются (0 < 1 и 0 < 2). Связь есть!
• Строка расхода 1 [0; 2] and Партия 2 [1; 3]: связи пересекаются (0 < 3 и 1 < 2). Связь есть!
• Строка расхода 2 [2; 3] и Партия 1 [0; 1]: связи НЕ пересекаются (2 < 1 — ложь). Связи нет!
• Строка расхода 2 [2; 3] и Партия 2 [1; 3]: связи пересекаются (2 < 3 и 1 < 3). Связь есть!

Вместо хаотичных 4 строк мы получили строго упорядоченные связи. Осталось лишь вычислить точное количество списания для каждого пересечения по формуле:
Минимум(Расход.ДО, Партия.ДО) - Максимум(Расход.ОТ, Партия.ОТ)

Реализация алгоритма в запросе 1С по шагам

Выясним, как технически посчитать нарастающий итог (интервалы ОТ и ДО) в запросах 1С, учитывая отсутствие стандартных оконных функций SQL. Мы воспользуемся механизмом временных таблиц, системной функцией АВТОНОМЕРЗАПИСИ() и соединением таблиц по неравенству.

Шаг 1. Подготовка исходных данных расхода с нумерацией строк

Для построения нарастающих итогов нам необходим уникальный ключ сортировки в рамках каждой аналитики (Товар, Серия). Самый простой способ получить его во временной таблице — использовать функцию АВТОНОМЕРЗАПИСИ().

Создадим временную таблицу для потребностей (расхода):

ВЫБРАТЬ
    РасходнаяТовары.Товар КАК Товар,
    РасходнаяТовары.Серия КАК Серия,
    РасходнаяТовары.Кво КАК Кво,
    РасходнаяТовары.ЦенаБезНДС КАК ЦенаБезНДС,
    РасходнаяТовары.СуммаСНДС КАК СуммаСНДС,
    АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Расход_Исходный
ИЗ
    Документ.Расходная.Товары КАК РасходнаяТовары
ГДЕ
    РасходнаяТовары.Ссылка = &Ссылка
;

Шаг 2. Расчет интервалов «ОТ» и «ДО» для расхода

Для расчета нарастающего итога мы соединим таблицу ВТ_Расход_Исходный саму с собой по условию НомерСтроки_Левая >= НомерСтроки_Правая в разрезе ключевых полей:

ВЫБРАТЬ
    Т1.Товар КАК Товар,
    Т1.Серия КАК Серия,
    Т1.Кво КАК Кво,
    Т1.ЦенаБезНДС КАК ЦенаБезНДС,
    Т1.СуммаСНДС КАК СуммаСНДС,
    Т1.НомерСтроки КАК НомерСтроки,
    СУММА(ЕСТЬNULL(Т2.Кво, 0)) КАК ДО,
    СУММА(ЕСТЬNULL(Т2.Кво, 0)) - Т1.Кво КАК ОТ
ПОМЕСТИТЬ ВТ_Расход_Интервалы
ИЗ
    ВТ_Расход_Исходный КАК Т1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Расход_Исходный КАК Т2
        ПО Т1.Товар = Т2.Товар
        И Т1.Серия = Т2.Серия
        И Т1.НомерСтроки >= Т2.НомерСтроки
ГРУППИРОВАТЬ ПО
    Т1.Товар,
    Т1.Серия,
    Т1.Кво,
    Т1.ЦенаБезНДС,
    Т1.СуммаСНДС,
    Т1.НомерСтроки
;

Шаг 3. Подготовка и расчет интервалов для партий

Аналогичные операции проведем и для партий из регистра остатков. Сначала пронумеруем записи, затем рассчитаем нарастающий итог.

ВЫБРАТЬ
    Парт.Товар КАК Товар,
    Парт.Серия КАК Серия,
    Парт.Колво КАК Колво,
    Парт.Сумма КАК Сумма,
    Парт.Партия КАК Партия,
    АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Партии_Исходные
ИЗ
    РегистрНакопления.Партия КАК Парт
ГДЕ
    Парт.Регистратор = &Ссылка
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    П1.Товар КАК Товар,
    П1.Серия КАК Серия,
    П1.Колво КАК Колво,
    П1.Сумма КАК Сумма,
    П1.Партия КАК Партия,
    СУММА(ЕСТЬNULL(П2.Колво, 0)) КАК ДО,
    СУММА(ЕСТЬNULL(П2.Колво, 0)) - П1.Колво КАК ОТ
ПОМЕСТИТЬ ВТ_Партии_Интервалы
ИЗ
    ВТ_Партии_Исходные КАК П1
    ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Партии_Исходные КАК П2
        ПО П1.Товар = П2.Товар
        И П1.Серия = П2.Серия
        И П1.НомерСтроки >= П2.НомерСтроки
ГРУППИРОВАТЬ ПО
    П1.Товар,
    П1.Серия,
    П1.Колво,
    П1.Сумма,
    П1.Партия
;

Шаг 4. Финальное объединение таблиц по интервалам

Теперь объединим подготовленные временные таблицы интервалов. Связь настроим по пересечению интервалов количества, а само распределенное количество вычислим через конструкцию ВЫБОР КОГДА.

ВЫБРАТЬ
    Расход.Товар КАК Товар,
    Расход.Серия КАК Серия,
    // Вычисляем распределенное количество для конкретной пары строка-партия
    ВЫБОР
        КОГДА Расход.ДО < Партии.ДО
            ТОГДА Расход.ДО
        ИНАЧЕ Партии.ДО
    КОНЕЦ - ВЫБОР
        КОГДА Расход.ОТ > Партии.ОТ
            ТОГДА Расход.ОТ
        ИНАЧЕ Партии.ОТ
    КОНЕЦ КАК КоличествоРаспределенное,
    Расход.ЦенаБезНДС КАК ЦенаБезНДС,
    Расход.СуммаСНДС КАК СуммаСНДС,
    Партии.Партия КАК Партия,
    Партии.Сумма КАК СуммаПартии
ИЗ
    ВТ_Расход_Интервалы КАК Расход
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Партии_Интервалы КАК Партии
        ПО Расход.Товар = Партии.Товар
        И Расход.Серия = Партии.Серия
        // Главное условие пересечения интервалов:
        И Расход.ОТ < Партии.ДО
        И Партии.ОТ < Расход.ДО
;

Проанализируем, что мы получили в результате выполнения этого запроса. Благодаря жесткому ограничению по пересечению диапазонов Расход.ОТ < Партии.ДО И Партии.ОТ < Расход.ДО, СУБД сопоставит только те строки, которые математически накладываются друг на друга на шкале нарастающего итога. Лишние комбинации (декартово произведение) будут полностью отсечены.

Для нашего примера мы получим ровно 3 строки:

1. Товар А, Серия Х — Количество 1.0 (списано из Партии 1)
2. Товар А, Серия Х — Количество 1.0 (списано из Партии 2)
3. Товар А, Серия Х — Количество 1.0 (вторая строка расхода, списана из Партии 2)

Это идеальный результат, полностью соответствующий логике ФИФО или партионного списания.

Рекомендации по производительности

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

• Индексируйте временные таблицы. Обязательно индексируйте поля связи во временных таблицах перед их соединением. В нашем случае это поля Товар, Серия, а также поле НомерСтроки во временных таблицах исходных данных. Для этого в конструкции ПОМЕСТИТЬ используйте директиву индексации: ИНДЕКСИРОВАТЬ ПО Товар, Серия.
• Минимизируйте объем выборки. Не пытайтесь рассчитывать нарастающий итог по всей таблице регистра. Всегда ограничивайте выборку конкретным документом (как в нашем примере с помощью параметра &Ссылка).
• Альтернатива в СКД. Если данный алгоритм требуется исключительно для построения аналитического отчета, рассмотрите возможность использования Системы компоновки данных. Функции СКД, такие как ВычислитьВыражение(), позволяют рассчитывать накопительные итоги и распределять показатели на уровне локального сервера приложений 1С без сложного конструирования тяжелых SQL-запросов, что существенно снизит нагрузку на сервер баз данных.

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