Задача автоматического распределения сумм (взаимозачеты, погашение долгов оплатами, распределение материалов по партиям) по методу ФИФО (First In, First Out) является классической для учетных систем. Часто возникает требование реализовать этот механизм исключительно средствами языка запросов, без последующей обработки в цикле. Это позволяет использовать результат сразу в Системе Компоновки Данных (СКД) или динамических списках.
В этой статье мы подробно разберем, как соединить таблицу должников и таблицу кредиторов так, чтобы суммы «закрывались» последовательно, и выясним, как работает метод пересечения интервалов.
Рассмотрим ситуацию, описанную в задаче. У нас есть две таблицы:
Простое соединение таблиц (ЛЕВОЕ или ВНУТРЕННЕЕ) по контрагенту здесь не сработает, так как нам нужно распределить сумму одной строки кредитора на несколько строк должников (или наоборот) в зависимости от порядка записей.
Эффективным решением является геометрический подход. Представим суммы долгов и кредитов как отрезки на числовой прямой. Если выложить все долги в одну линию и все кредиты в другую линию параллельно, то взаимозачет — это пересечение этих отрезков.
Разберем реализацию данного алгоритма по шагам. Нам потребуется выполнить следующие действия:
Для начала нам необходимо получить таблицы с данными и обеспечить их строгий порядок. Без сортировки понятие «ФИФО» (первый пришел) не имеет смысла. Допустим, мы сортируем записи по дате возникновения или приоритету.
Главная хитрость заключается в расчете нарастающего итога. На современных версиях платформы 1С (8.3.14 и выше) мы можем использовать оконные функции языка запросов. Если вы используете старую версию, придется применять соединение таблицы самой с собой (что менее производительно на больших объемах).
Рассмотрим пример кода для подготовки таблицы Долги:
ВЫБРАТЬ
ТЗ.Должник КАК Должник,
ТЗ.СуммаДолга КАК Сумма,
ТЗ.ПорядокСортировки КАК Порядок
ПОМЕСТИТЬ ВТ_ИсходныеДолги
ИЗ
&ТЗ_Должники КАК ТЗ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Должник,
Т1.Сумма,
// Рассчитываем конец интервала (нарастающий итог)
СУММА(Т2.Сумма) КАК КонецИнтервала,
// Рассчитываем начало интервала (Конец - ТекущаяСумма)
СУММА(Т2.Сумма) - Т1.Сумма КАК НачалоИнтервала
ПОМЕСТИТЬ ВТ_ДолгиИнтервалы
ИЗ
ВТ_ИсходныеДолги КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеДолги КАК Т2
ПО Т1.Порядок >= Т2.Порядок
СГРУППИРОВАТЬ ПО
Т1.Должник,
Т1.Сумма,
Т1.Порядок
;
Аналогичную операцию мы проделываем для таблицы Кредиты (оплаты):
ВЫБРАТЬ
ТЗ.Кредитор КАК Кредитор,
ТЗ.СуммаКредита КАК Сумма,
ТЗ.ПорядокСортировки КАК Порядок
ПОМЕСТИТЬ ВТ_ИсходныеКредиты
ИЗ
&ТЗ_Кредиторы КАК ТЗ
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Кредитор,
Т1.Сумма,
СУММА(Т2.Сумма) КАК КонецИнтервала,
СУММА(Т2.Сумма) - Т1.Сумма КАК НачалоИнтервала
ПОМЕСТИТЬ ВТ_КредитыИнтервалы
ИЗ
ВТ_ИсходныеКредиты КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ИсходныеКредиты КАК Т2
ПО Т1.Порядок >= Т2.Порядок
СГРУППИРОВАТЬ ПО
Т1.Кредитор,
Т1.Сумма,
Т1.Порядок
;
В результате этого шага каждая строка в обеих таблицах получила координаты на числовой оси. Например:
Теперь переходим к самой магии распределения. Нам нужно найти такие пары записей из двух таблиц, чьи интервалы пересекаются. Условие пересечения двух отрезков [A1, B1] и [A2, B2] выглядит следующим образом:
Начало первого меньше конца второго И Конец первого больше начала второго.
Используем это условие для соединения таблиц (это и называется "тета-соединение", так как используется оператор сравнения, отличный от "равно"):
ВЫБРАТЬ
Долги.Должник,
Кредиты.Кредитор,
Долги.НачалоИнтервала КАК ДолгНачало,
Долги.КонецИнтервала КАК ДолгКонец,
Кредиты.НачалоИнтервала КАК КредитНачало,
Кредиты.КонецИнтервала КАК КредитКонец
ПОМЕСТИТЬ ВТ_Пересечения
ИЗ
ВТ_ДолгиИнтервалы КАК Долги
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_КредитыИнтервалы КАК Кредиты
ПО Долги.НачалоИнтервала < Кредиты.КонецИнтервала
И Долги.КонецИнтервала > Кредиты.НачалоИнтервала
;
После того как мы нашли пересекающиеся пары, нам нужно вычислить сумму, которая "зачитывается" в рамках этого пересечения. Длина пересечения двух отрезков рассчитывается по формуле:
МАКСИМУМ(Начала обоих отрезков) ... МИНИМУМ(Концов обоих отрезков)
Соответственно, сумма равна:
МИН(Конец1, Конец2) - МАКС(Начало1, Начало2)
Напишем итоговый запрос:
ВЫБРАТЬ
Т.Должник,
Т.Кредитор,
ВЫБОР
КОГДА Т.ДолгКонец < Т.КредитКонец ТО Т.ДолгКонец
ИНАЧЕ Т.КредитКонец
КОНЕЦ -
ВЫБОР
КОГДА Т.ДолгНачало > Т.КредитНачало ТО Т.ДолгНачало
ИНАЧЕ Т.КредитНачало
КОНЕЦ КАК СуммаВзаимозачета
ИЗ
ВТ_Пересечения КАК Т
Вспомним наши интервалы:
В итоге мы получаем таблицу, которую запрашивал автор:
При использовании данного метода обратите внимание на следующие моменты:
НачалоИнтервала и КонецИнтервала. Это критически важно для производительности соединения на неравенство (тета-соединения).ВЫРАЗИТЬ(... КАК ЧИСЛО(15, 2)) на этапах расчета интервалов.JOIN таблицы самой к себе имеет квадратичную сложность (O(n^2)). На объемах более 10 000 строк это может работать медленно. В таких случаях лучше использовать постобработку или, если позволяет версия платформы 8.3.14+, использовать конструкции типа СУММА(Сумма) СЕКЦИЯ (УПОРЯДОЧИТЬ ПО ...), которые работают линейно.Таким образом, мы решили задачу распределения ФИФО полностью на стороне SQL-сервера, получив готовую таблицу для отчетов или дальнейшей обработки.