В практике управленческого учета и финансового планирования часто возникает задача контроля платежной дисциплины. Особенно интересной и сложной становится ситуация, когда платежный календарь реализован на оборотном регистре накопления, а график платежей и фактические оплаты по договору или счету не зафиксированы жестко (то есть суммы и даты как плановых платежей, так и фактических оплат могут быть произвольными). В такой конфигурации для определения количества просроченных платежей и даты возникновения первой просрочки нам приходится применять алгоритм ФИФО (First In, First Out) непосредственно в запросе.
В рамках этой статьи мы подробно разберем, как решить эту задачу с помощью пакета запросов в системе компоновки данных (СКД), рассмотрим подводные камни оптимизатора СКД и предложим альтернативные архитектурные решения.
Перед тем как переходить к написанию кода, давайте разберем логику расчета. У нас есть оборотный регистр накопления ПлатежныйКалендарь с измерением СчетНаОплату и двумя ресурсами: Сумма (план платежа) и СуммаОплаты (факт оплаты).
Рассмотрим три классических примера, которые иллюстрируют, как должен работать алгоритм ФИФО на определенную дату (например, на 11.03.2019):
Для реализации этой логики в запросе нам необходимо сопоставить общую накопленную сумму фактических оплат по каждому счету на выбранную дату с нарастающим итогом плановых платежей по этому же счету.
Рассмотрим по шагам, как построить оптимальный пакет запросов для получения количества просроченных платежей и даты первой просрочки.
В первой временной таблице мы собираем все фактические оплаты по счетам нарастающим итогом на указанную дату (параметр &Период). Поскольку нас интересует только итоговая сумма оплаты на текущую дату, мы группируем данные по измерению СчетНаОплату:
ВЫБРАТЬ
ПлатежныйКалендарьОбороты.СчетНаОплату КАК СчетНаОплату,
ЕСТЬNULL(ПлатежныйКалендарьОбороты.СуммаОплатыОборот, 0) КАК СуммаОплаты
ПОМЕСТИТЬ ТабОплачено
ИЗ
РегистрНакопления.ПлатежныйКалендарь.Обороты(, &Период, , ) КАК ПлатежныйКалендарьОбороты
;
Теперь нам нужно получить график плановых платежей по каждому счету и рассчитать для каждой точки графика нарастающий итог. Для этого мы выбираем плановые обороты и соединяем таблицу саму с собой (левое соединение по условию «Период текущей записи >= Период предыдущей записи»):
ВЫБРАТЬ
ПлатежныйКалендарьОбороты.СчетНаОплату КАК СчетНаОплату,
ПлатежныйКалендарьОбороты.Период КАК Период,
СУММА(ПлатежныйКалендарьОбороты1.СуммаОборот) КАК СуммаНарастающимИтогом
ПОМЕСТИТЬ ТабНарастающимИтогом
ИЗ
РегистрНакопления.ПлатежныйКалендарь.Обороты(, &Период, Регистратор, ) КАК ПлатежныйКалендарьОбороты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ПлатежныйКалендарь.Обороты(, &Период, Регистратор, ) КАК ПлатежныйКалендарьОбороты1
ПО ПлатежныйКалендарьОбороты.СчетНаОплату = ПлатежныйКалендарьОбороты1.СчетНаОплату
И ПлатежныйКалендарьОбороты.Период >= ПлатежныйКалендарьОбороты1.Период
ГДЕ
ПлатежныйКалендарьОбороты.СуммаОборот <> 0
СГРУППИРОВАТЬ ПО
ПлатежныйКалендарьОбороты.СчетНаОплату,
ПлатежныйКалендарьОбороты.Период
;
В финальном запросе мы соединяем таблицу планов с нарастающим итогом и таблицу оплат. Если плановый нарастающий итог на определенную дату превышает общую сумму оплаты по счету, значит, этот платеж (или его часть) остался неоплаченным. Мы подсчитываем количество таких записей и берем минимальную дату — это и будет дата первой просрочки:
ВЫБРАТЬ
ТабНарастающимИтогом.СчетНаОплату КАК СчетНаОплату,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТабНарастающимИтогом.Период) КАК КоличествоНеОплаченных,
МИНИМУМ(ТабНарастающимИтогом.Период) КАК ДатаОбразованияПервойЗадолженности
ИЗ
ТабНарастающимИтогом КАК ТабНарастающимИтогом
ЛЕВОЕ СОЕДИНЕНИЕ ТабОплачено КАК ТабОплачено
ПО ТабНарастающимИтогом.СчетНаОплату = ТабОплачено.СчетНаОплату
ГДЕ
ТабНарастающимИтогом.СуммаНарастающимИтогом > ЕСТЬNULL(ТабОплачено.СуммаОплаты, 0)
СГРУППИРОВАТЬ ПО
ТабНарастающимИтогом.СчетНаОплату
;
При тестировании этого запроса в обычной консоли запросов результаты выводятся абсолютно корректно. Однако при переносе этого запроса в Систему компоновки данных (СКД) разработчики часто сталкиваются с неприятной особенностью: отчет для всех строк выводит количество просрочек, равное 1.
Выясним причину этого поведения. Она кроется в работе оптимизатора запросов СКД. Когда СКД анализирует схему компоновки, она пытается оптимизировать выполнение на уровне СУБД. Если в настройках отчета (группировках) не выводятся детальные записи (периоды), СКД может самостоятельно упростить левое соединение во временных таблицах или сгруппировать данные раньше времени, что ломает расчет нарастающего итога.
Чтобы избежать этой проблемы и гарантировать корректный расчет в СКД, примените следующие методы:
СУММА(1) или КОЛИЧЕСТВО(*) обязательно используйте КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ТабНарастающимИтогом.Период). Это заставит СКД сохранить уникальные детальные записи периодов во временной таблице.Период укажите роль «Период», а для поля СчетНаОплату — роль «Измерение». Это явно укажет компоновщику макета, по каким полям нельзя производить произвольное сворачивание данных.В реальных базах данных возможна ситуация, когда по одному и тому же счету вводятся две записи по планированию в одну и ту же секунду (или одним документом). В этом случае стандартное условие связи по периоду T1.Период >= T2.Период начнет некорректно удваивать суммы нарастающего итога, так как записи с одинаковым временем «увидят» друг друга.
Чтобы обойти эту проблему на платформе 1С v8.3, рекомендуется использовать встроенную функцию АВТОНОМЕРЗАПИСИ(). С ее помощью мы создаем уникальный упорядоченный индекс для каждой строки во временной таблице, после чего рассчитываем нарастающий итог на основе этого индекса:
ВЫБРАТЬ
ПлатежныйКалендарьОбороты.СчетНаОплату КАК СчетНаОплату,
ПлатежныйКалендарьОбороты.Период КАК Период,
ПлатежныйКалендарьОбороты.СуммаОборот КАК СуммаОборот,
АВТОНОМЕРЗАПИСИ() КАК НомерСтроки
ПОМЕСТИТЬ ТабИсходнаяСНомером
ИЗ
РегистрНакопления.ПлатежныйКалендарь.Обороты(, &Период, Регистратор, ) КАК ПлатежныйКалендарьОбороты
ГДЕ
ПлатежныйКалендарьОбороты.СуммаОборот <> 0
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.СчетНаОплату КАК СчетНаОплату,
Т1.Период КАК Период,
СУММА(Т2.СуммаОборот) КАК СуммаНарастающимИтогом
ПОМЕСТИТЬ ТабНарастающимИтогом
ИЗ
ТабИсходнаяСНомером КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ТабИсходнаяСНомером КАК Т2
ПО Т1.СчетНаОплату = Т2.СчетНаОплату
И Т1.НомерСтроки >= Т2.НомерСтроки
СГРУППИРОВАТЬ ПО
Т1.СчетНаОплату,
Т1.Период
;
Такой подход гарантирует абсолютную точность расчета нарастающего итога независимо от того, сколько движений было сделано в одну секунду или в рамках одного регистратора.
Необходимо помнить, что левое соединение таблицы самой с собой по условию неравенства (Theta-join) — это ресурсоемкая операция, которая на больших объемах данных (десятки тысяч платежей) может работать медленно, так как сложность алгоритма возрастает квадратично. Если вы разрабатываете отчет исключительно в рамках СКД, вы можете полностью отказаться от тяжелых соединений в SQL-запросе и переложить расчет нарастающего итога на процессор компоновки данных.
Для этого в запросе мы получаем линейную таблицу планов и общую сумму оплат, а в СКД добавляем вычисляемое поле СуммаПланаНарастающимИтогом. В ресурсах этого поля мы прописываем встроенную функцию СКД ВычислитьВыражение:
ВычислитьВыражение("Сумма(Сумма)", "Период", , "Первая", "Текущая")
Затем с помощью вычисляемых полей и ресурсов мы сравниваем это значение с суммой оплаты. Такой расчет происходит в памяти компьютера за линейное время $O(N)$ и не перегружает сервер СУБД тяжелыми запросами.
В процессе обсуждения этой проблемы опытные разработчики часто обращают внимание на архитектуру метаданных. Использование оборотного регистра для учета взаиморасчетов и контроля просрочки — это паттерн, который изначально усложняет разработку. Оборотный регистр не хранит текущее состояние долга, заставляя систему при каждом формировании отчета рассчитывать всю историю операций с самого начала ведения базы данных (ведь неоплаченным может оказаться платеж за прошлый год).
Рекомендация по проектированию:
Для платежного календаря гораздо эффективнее использовать Регистр накопления остатков (например, РасчетыПоПлатежномуКалендарю) с измерениями СчетНаОплату и ДатаПлановогоПлатежа.
ВЫБРАТЬ
ПлатежныйКалендарьОстатки.СчетНаОплату КАК СчетНаОплату,
КОЛИЧЕСТВО(ПлатежныйКалендарьОстатки.ДатаПлановогоПлатежа) КАК КоличествоНеОплаченных,
МИНИМУМ(ПлатежныйКалендарьОстатки.ДатаПлановогоПлатежа) КАК ДатаОбразованияПервойЗадолженности
ИЗ
РегистрНакопления.ПлатежныйКалендарь.Остатки(&Период, ДатаПлановогоПлатежа < &Период) КАК ПлатежныйКалендарьОстатки
СГРУППИРОВАТЬ ПО
ПлатежныйКалендарьОстатки.СчетНаОплату
;
Этот запрос отработает мгновенно даже на базе данных объемом в миллионы записей, так как СУБД обратится к уже готовым рассчитанным итогам регистра остатков. Тем не менее, если вы ограничены существующей архитектурой конфигурации, предложенный выше пакетный запрос с расчетом нарастающего итога по ФИФО станет надежным и проверенным решением вашей задачи.