В практике разработчика 1С часто возникает задача сопоставить данные из двух таблиц, не имеющих прямого ключа связи, ориентируясь на даты событий. Классический пример из финансовой сферы — сопоставление фактических платежей, поступивших на расчетный счет или в кассу, с плановым графиком платежей по договору. На первый взгляд кажется, что достаточно соединить таблицы по принципу «ближайшей даты». Однако при детальном анализе бизнес-процесса выясняется, что простое математическое сопоставление дат приводит к грубым ошибкам в учете. Происходит это из-за просрочек, авансовых платежей и оплат частями.
В рамках данной статьи мы подробно разберем оба подхода. Сначала мы решим техническую задачу сопоставления таблиц по «ближайшей дате» (минимальной разности дат) в запросе 1С. Затем мы рассмотрим более правильный с точки зрения бизнес-логики алгоритм — распределение оплат по графику платежей методом ФИФО с помощью запроса.
---
Представим, что у нас есть две таблицы (или два временных источника данных). Нам необходимо связать каждую строку первой таблицы с той строкой второй таблицы, у которой разница между датами минимальна. Для реализации этой задачи на уровне СУБД нам потребуется выполнить декартово произведение, вычислить разность дат, сгруппировать результат для нахождения минимума и выполнить финальное соединение.
Разберем этот процесс по шагам.
Соединять две таблицы абсолютно без условий (полное декартово произведение) в реальной базе данных крайне опасно, так как при количестве записей в обеих таблицах более нескольких тысяч СУБД может просто зависнуть из-за квадратичного роста числа комбинаций. Нам обязательно нужно ограничить соединение ключевым разрезом аналитики. В нашем случае таким разрезом будет выступать, например, Договор или Контрагент.
В языке запросов 1С отсутствует математическая функция взятия модуля числа (типа ABS). Для того чтобы получить абсолютную разность дат (всегда положительное число секунд), нам придется использовать условную конструкцию ВЫБОР в связке с функцией РАЗНОСТЬДАТ.
Мы должны сгруппировать полученные сопоставления по ключевым полям первой таблицы и найти минимальное значение разности дат.
Имея минимальную разность дат для каждой строки первой таблицы, мы снова соединяем ее со второй таблицей по этому минимальному значению, чтобы «подтянуть» соответствующие реквизиты.
Проанализируем готовый шаблон запроса, реализующий данную логику:
// Шаг 1: Получаем все возможные комбинации с расчетом разности дат
ВЫБРАТЬ
Таблица1.Договор КАК Договор,
Таблица1.Реквизит11 КАК Реквизит11,
Таблица1.Дата11 КАК Дата11,
Таблица2.Реквизит21 КАК Реквизит21,
Таблица2.Дата21 КАК Дата21,
ВЫБОР
КОГДА Таблица1.Дата11 > Таблица2.Дата21
ТОГДА РАЗНОСТЬДАТ(Таблица2.Дата21, Таблица1.Дата11, СЕКУНДА)
ИНАЧЕ РАЗНОСТЬДАТ(Таблица1.Дата11, Таблица2.Дата21, СЕКУНДА)
КОНЕЦ КАК РазностьСекунд
ПОМЕСТИТЬ ВТ_ВсеВариантыСопоставления
ИЗ
ВТ_Таблица1 КАК Таблица1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Таблица2 КАК Таблица2
ПО Таблица1.Договор = Таблица2.Договор
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Находим минимальную разность для каждой строки первой таблицы
ВЫБРАТЬ
ВсеВарианты.Договор КАК Договор,
ВсеВарианты.Реквизит11 КАК Реквизит11,
ВсеВарианты.Дата11 КАК Дата11,
МИНИМУМ(ВсеВарианты.РазностьСекунд) КАК МинимальнаяРазность
ПОМЕСТИТЬ ВТ_МинимальныеРазности
ИЗ
ВТ_ВсеВариантыСопоставления КАК ВсеВарианты
СГРУППИРОВАТЬ ПО
ВсеВарианты.Договор,
ВсеВарианты.Реквизит11,
ВсеВарианты.Дата11
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем обратно для получения реквизитов из Таблицы 2
ВЫБРАТЬ
Минимумы.Договор КАК Договор,
Минимумы.Реквизит11 КАК Реквизит11,
Минимумы.Дата11 КАК Дата11,
// Для исключения неоднозначности (если две даты из Таблицы 2 равноудалены)
// берем минимальную дату из второй таблицы
МИНИМУМ(ВсеВарианты.Дата21) КАК Дата21,
МИНИМУМ(ВсеВарианты.Реквизит21) КАК Реквизит21
ИЗ
ВТ_МинимальныеРазности КАК Минимумы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ВсеВариантыСопоставления КАК ВсеВарианты
ПО Минимумы.Договор = ВсеВарианты.Договор
И Минимумы.Реквизит11 = ВсеВарианты.Реквизит11
И Минимумы.Дата11 = ВсеВарианты.Дата11
И Минимумы.МинимальнаяРазность = ВсеВарианты.РазностьSecунд
СГРУППИРОВАТЬ ПО
Минимумы.Договор,
Минимумы.Реквизит11,
Минимумы.Дата11
Важное примечание по Шагу 3: Обратите внимание на использование функции МИНИМУМ() при получении полей из второй таблицы в финальном запросе. Если дата из Таблица1 находится ровно посередине между двумя датами из Таблица2 (например, платеж совершен 15-го числа, а плановые даты — 10-го и 20-го числа), минимальная разница в 5 дней будет одинаковой для обеих записей. Без дополнительной группировки и функции МИНИМУМ() (или МАКСИМУМ()) строка из первой таблицы раздвоится в результате запроса.
---
Рассмотрим реальную ситуацию в банке или торговой компании. У клиента оформлен кредит или рассрочка с графиком платежей:
Теперь представим сценарии поведения клиента:
Для корректного отражения таких операций используется классический бухгалтерский принцип ФИФО (First In, First Out). Любой поступивший платеж должен закрывать самый старый незакрытый плановый платеж по графику, вне зависимости от того, насколько далека дата платежа от плановой даты.
---
Реализация ФИФО в запросе строится на концепции сопоставления интервалов нарастающего итога. Нам необходимо вычислить накопленный итог по суммам графика и накопленный итог по суммам фактических оплат, а затем связать их по условию пересечения числовых интервалов.
Рассмотрим по шагам, как построить такой запрос.
Для каждой строки графика мы рассчитываем сумму всех предыдущих плановых платежей плюс текущий платеж. Это дает нам верхнюю границу интервала. Нижняя граница — это накопленная сумма до текущей строки.
Аналогично рассчитываем нарастающий итог для таблицы фактических поступлений денежных средств.
Интервал планового платежа [СуммаПланНачало, СуммаПланКонец] пересекается с интервалом фактического платежа [СуммаФактНачало, СуммаФактКонец], если выполняются следующие условия:
Давайте посмотрим на практическую реализацию этого сложного алгоритма в виде готового запроса 1С:
// Шаг 1: Готовим график платежей с нарастающим итогом
ВЫБРАТЬ
График1.Договор КАК Договор,
График1.ДатаПлатежа КАК ДатаПлатежа,
График1.Сумма КАК СуммаПлан,
СУММА(График2.Сумма) КАК НакопленоПланКонец,
СУММА(График2.Сумма) - График1.Сумма КАК НакопленоПланНачало
ПОМЕСТИТЬ ВТ_ГрафикНарастающий
ИЗ
ВТ_График КАК График1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_График КАК График2
ПО График1.Договор = График2.Договор
И График2.ДатаПлатежа <= График1.ДатаПлатежа
СГРУППИРОВАТЬ ПО
График1.Договор,
График1.ДатаПлатежа,
График1.Сумма
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Готовим фактические оплаты с нарастающим итогом
ВЫБРАТЬ
Оплаты1.Договор КАК Договор,
Оплаты1.ДатаОплаты КАК ДатаОплаты,
Оплаты1.Сумма КАК СуммаФакт,
СУММА(Оплаты2.Сумма) КАК НакопленоФактКонец,
СУММА(Оплаты2.Сумма) - Оплаты1.Сумма КАК НакопленоФактНачало
ПОМЕСТИТЬ ВТ_ОплатыНарастающий
ИЗ
ВТ_Оплаты КАК Оплаты1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Оплаты КАК Оплаты2
ПО Оплаты1.Договор = Оплаты2.Договор
И Оплаты2.ДатаОплаты <= Оплаты1.ДатаОплаты
СГРУППИРОВАТЬ ПО
Оплаты1.Договор,
Оплаты1.ДатаОплаты,
Оплаты1.Сумма
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем по пересечению интервалов сумм и рассчитываем распределение
ВЫБРАТЬ
План.Договор КАК Договор,
План.ДатаПлатежа КАК ДатаПлатежа,
План.СуммаПлан КАК СуммаПлан,
Факт.ДатаОплаты КАК ДатаОплаты,
Факт.СуммаФакт КАК СуммаФакт,
// Вычисляем сумму, которая распределилась между конкретным планом и конкретным фактом
ВЫБОР
КОГДА План.НакопленоПланКонец < Факт.НакопленоФактКонец
ТОГДА План.НакопленоПланКонец
ИНАЧЕ Факт.НакопленоФактКонец
КОНЕЦ
-
ВЫБОР
КОГДА План.НакопленоПланНачало > Факт.НакопленоФактНачало
ТОГДА План.НакопленоПланНачало
ИНАЧЕ Факт.НакопленоФактНачало
КОНЕЦ КАК РаспределеннаяСумма
ИЗ
ВТ_ГрафикНарастающий КАК План
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ОплатыНарастающий КАК Факт
ПО План.Договор = Факт.Договор
// Условие пересечения интервалов нарастающих итогов
И План.НакопленоПланНачало < Факт.НакопленоФактКонец
И План.НакопленоПланКонец > Факт.НакопленоФактНачало
УПОРЯДОЧИТЬ ПО
Договор,
ДатаПлатежа,
ДатаОплаты
Этот запрос возвращает полную детализацию взаиморасчетов. Если один фактический платеж закрывает несколько плановых дат, мы увидим в результате несколько строк с распределением сумм. Аналогично, если одна плановая дата закрывается несколькими мелкими оплатами, запрос сопоставит их корректно и покажет распределенную сумму для каждого сопоставления.
---
Проанализируем технические аспекты работы предложенных решений. Запросы с вычислением нарастающего итога путем соединения таблицы самой с собой имеют квадратичную сложность $O(N^2)$, где $N$ — количество записей по договору. Если у вас по договору ведением расчетов накоплено несколько тысяч записей (например, ежедневное начисление процентов в банке за несколько лет), такой запрос начнет сильно нагружать СУБД.
Для обхода ограничений производительности в современной разработке на платформе 1С применяются следующие подходы:
ВычислитьВыражение, которая позволяет рассчитать нарастающий итог на этапе компоновки результата практически мгновенно, не нагружая процессор СУБД тяжелыми соединениями таблиц.1С:ERP Управление предприятием или 1С:Комплексная автоматизация) отказались от динамического расчета ФИФО "на лету" в отчетах. Вместо этого при проведении документов данные записываются в обычные регистры накопления. Специальное фоновое задание (или регламентная операция закрытия месяца) выполняет детальное распределение расчетов в фоновом режиме и записывает готовые связи в регистры сведений (например, РасчетыСКлиентамиПоДокументам). Отчеты строятся по уже готовым распределенным данным, что гарантирует моментальный отклик интерфейса для пользователя.