В практике разработчика 1С часто возникает задача распределения определенной суммы (например, дополнительных расходов или скидки) по списку документов пропорционально их сумме или количеству. Главная трудность здесь заключается не столько в расчете процентов, сколько в «проблеме последней копейки». Из-за округлений сумма распределенных частей часто не совпадает с исходным итогом на 1-2 копейки. Чтобы итоговый баланс сошелся, этот остаток необходимо принудительно отнести на последнюю запись в реестре.
Традиционно такие задачи решаются во временных таблицах или при обходе результата запроса в коде 1С, однако существует способ реализовать всю логику, включая поиск последней строки для распределения «хвоста», непосредственно в языке запросов. Рассмотрим этот подход подробнее, разобрав «нестандартный» метод нумерации и фильтрации данных.
Представим, что у нас есть реестр документов РеализацияТоваровУслуг за определенный период. Нам нужно распределить некую внешнюю сумму &ОбщаяСуммаДляРаспределения между всеми документами. Логика распределения следующая:
Для реализации последнего пункта нам нужно однозначно идентифицировать «последнюю накладную». В обычном случае мы бы использовали конструкцию ВЫБРАТЬ ПЕРВЫЕ 1 ... УПОРЯДОЧИТЬ ПО МоментВремени УБЫВ. Но что делать, если нам нужно получить полный список документов с пометкой, какой из них последний, в рамках одного сложного запроса? Разберем предложенный алгоритм.
Для начала выберем все необходимые документы во временную таблицу. Важным нюансом здесь является добавление фиктивного поля Счетчик, которое позже поможет нам вычислить порядковые номера записей.
ВЫБРАТЬ
РеализацияТоваровУслуг.Ссылка КАК Ссылка,
РеализацияТоваровУслуг.МоментВремени КАК МоментВремени,
1 КАК Счетчик
ПОМЕСТИТЬ Шаг1
ИЗ
Документ.РеализацияТоваровУслуг КАК РеализацияТоваровУслуг
ГДЕ
РеализацияТоваровУслуг.Проведен
И РеализацияТоваровУслуг.Дата МЕЖДУ &НачДата И &КонДата
;
Мы используем МоментВремени, так как это наиболее точный способ упорядочивания документов в 1С, гарантирующий уникальность позиции документа на временной оси (учитывает дату, время и ссылку документа).
Теперь проанализируем самый интересный этап — использование самосоединения таблицы Шаг1 с самой собой. Этот метод называется «треугольным соединением», так как для каждой записи основной таблицы мы подтягиваем все записи, которые «меньше или равны» ей по определенному критерию (в нашем случае — по МоментВремени).
ВЫБРАТЬ
Т1.Ссылка КАК Ссылка,
СУММА(Т2.Счетчик) КАК ПорядковыйНомер
ПОМЕСТИТЬ ПронумерованныеЗаписи
ИЗ
Шаг1 КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Шаг1 КАК Т2
ПО Т1.МоментВремени >= Т2.МоментВремени
СГРУППИРОВАТЬ ПО
Т1.Ссылка
;
Как это работает: Рассмотрим ситуацию. Если у нас 3 документа (А, Б, В) с моментами времени 1, 2 и 3 соответственно:
1 >= Т2.МоментВремени выполнится только для самого документа А. Сумма счетчиков = 1.2 >= Т2.МоментВремени выполнится для А и Б. Сумма счетчиков = 2.3 >= Т2.МоментВремени выполнится для А, Б и В. Сумма счетчиков = 3.В результате мы получаем таблицу, где каждой ссылке сопоставлен её порядковый номер в выборке. Проанализируем эффективность: при 100 записях количество строк в соединении составит около 5 000, при 1000 записей — уже 500 000. Это «тяжелая» операция, которую следует применять с осторожностью на больших объемах данных.
Чтобы найти именно последнюю накладную, нам нужно сравнить текущий ПорядковыйНомер каждой строки с общим количеством документов. Выясним, как это сделать изящно через условие ИМЕЮЩИЕ.
ВЫБРАТЬ
Шаг1.Ссылка,
СУММА(Шаг11.Счетчик) КАК Счетчик
ИЗ
Шаг1 КАК Шаг1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Шаг1 КАК Шаг11
ПО Шаг1.МоментВремени >= Шаг11.МоментВремени
СГРУППИРОВАТЬ ПО
Шаг1.Ссылка
ИМЕЮЩИЕ
СУММА(Шаг11.Счетчик) = (ВЫБРАТЬ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Ссылка) ИЗ Шаг1)
В данном фрагменте кода мы оставляем в результате только ту строку, чей накопленный счетчик равен общему количеству записей в исходной таблице Шаг1. Это и будет наша «последняя накладная».
Теперь соберем все воедино. Для практического применения нам нужно рассчитать долю. Рассмотрим пример логики итогового запроса (предположим, общая сумма всех накладных уже получена в параметре или отдельной временной таблице):
ВЫБРАТЬ
Т.Ссылка КАК Ссылка,
Т.СуммаДокумента КАК СуммаДокумента,
// Пропорциональное распределение с округлением
ВЫРАЗИТЬ(Т.СуммаДокумента / &ОбщийИтог * &РаспределяемаяСумма КАК ЧИСЛО(15, 2)) КАК РаспределеннаяДоля,
// Проверка: является ли строка последней
ВЫБОР
КОГДА Нумерация.ПорядковыйНомер = ОбщийСчет.ВсегоЗаписей
ТОГДА ИСТИНА
ИНАЧЕ ЛОЖЬ
КОНЕЦ КАК ЭтоПоследняяЗапись
ИЗ
Шаг1 КАК Т
ЛЕВОЕ СОЕДИНЕНИЕ ПронумерованныеЗаписи КАК Нумерация ПО Т.Ссылка = Нумерация.Ссылка
КРОСС СОЕДИНЕНИЕ (ВЫБРАТЬ КОЛИЧЕСТВО(*) КАК ВсегоЗаписей ИЗ Шаг1) КАК ОбщийСчет
Получив флаг ЭтоПоследняяЗапись, мы можем в постобработке или в следующем пакете запроса вычислить &РаспределяемаяСумма - СУММА(РаспределеннаяДоля) и добавить этот остаток к сумме последней строки.
Рассмотрим слабые места данного метода. Основная проблема — квадратичная сложность соединения. Если в наборе данных тысячи документов, сервер СУБД будет испытывать колоссальную нагрузку на дисковую подсистему и процессор.
Какие альтернативы мы можем использовать в современной разработке на платформе 1С:
ROW_NUMBER() всё еще нет в языке запросов в чистом виде для всех СУБД, многие задачи агрегации стали проще.JOIN.ТаблицаЗначений, обходим в цикле, рассчитываем доли и в последней строке (Таблица[Таблица.Количество() - 1]) корректируем сумму на остаток.Мы проанализировали алгоритм распределения суммы «чистым запросом». Метод «треугольного соединения» — это мощный инструмент, который позволяет решать задачи нумерации и вычисления нарастающих итогов там, где нет возможности использовать оконные функции SQL. Несмотря на свою ресурсоемкость, он демонстрирует гибкость языка запросов 1С и может быть полезен в специфических задачах отчетности или при работе с небольшими выборками данных.
Помните: при использовании подобных «извращенных» (как назвал их автор темы) запросов всегда оценивайте объем данных. Если записей больше нескольких сотен — лучше отдать предпочтение классической обработке результата запроса в коде на языке 1С.