При разработке в среде 1С:Предприятие программисты регулярно сталкиваются с задачами распределения ресурсов, партионного учета или сопоставления взаиморасчетов. Типичный пример такой задачи — отфильтровать таблицу значений (ТЗ) таким образом, чтобы в ней остались строки, нарастающий итог по которым укладывается в определенный лимит, включая первую строку, которая этот лимит превысила (так называемое условие «чуть больше лимита»).
В этой статье мы подробно разберем, как правильно реализовать подобный алгоритм. Мы проанализируем математическую логику отбора, рассмотрим несколько программных способов фильтрации в оперативной памяти (включая оптимизированные варианты) и научимся решать эту задачу на уровне СУБД с помощью запросов и Системы компоновки данных (СКД).
Прежде чем переходить к написанию кода, давайте четко определим логику фильтрации. Представим, что у нас есть лимит в 400 единиц и следующая таблица значений:
Нам необходимо оставить в таблице строки 1, 2 и 3. Строка 3 должна войти в результат, так как именно на ней сумма превысила порог (450 > 400). Все последующие строки (начиная со строки 4) должны быть удалены.
Чтобы точно определить, должна ли строка остаться в таблице, мы можем использовать математическое правило:
Оставляем строку, если: (НарастающийИтог - ТекущаяСумма) < Лимит
Давайте проверим это правило на нашем примере:
Этот простой математический подход позволяет легко и безошибочно определять статус каждой строки в процессе обхода.
Рассмотрим классический способ решения, который часто используют начинающие разработчики. Суть метода заключается в том, что мы обходим таблицу значений сверху вниз, накапливаем сумму, а строки, которые превышают лимит, помещаем во временный массив. После завершения обхода мы удаляем накопленные строки из исходной таблицы.
Разберем по шагам программный код, реализующий эту логику:
ПроверяемаяСумма = 400;
ОбщаяСумма = 0;
МассивУдаляемыхСтрок = Новый Массив;
// Шаг 1. Обходим таблицу значений и собираем строки для удаления
Для Каждого СтрокаТЗ Из ТЗ Цикл
Если ОбщаяСумма >= ПроверяемаяСумма Тогда
// Если предыдущая накопленная сумма уже больше или равна лимиту,
// то текущая строка и все последующие нам точно не нужны.
МассивУдаляемыхСтрок.Добавить(СтрокаТЗ);
КонецЕсли;
// Накапливаем итог для следующей итерации
ОбщаяСумма = ОбщаяСумма + СтрокаТЗ.ОбщСумма;
КонецЦикла;
// Шаг 2. Удаляем ненужные строки из исходной таблицы
Для Каждого УдаляемаяСтрока Из МассивУдаляемыхСтрок Цикл
ТЗ.Удалить(УдаляемаяСтрока);
КонецЦикла;
Анализ метода: Данный способ полностью работоспособен и прост в понимании. Однако у него есть существенный недостаток с точки зрения производительности. Мы тратим дополнительную оперативную память на хранение ссылок в объекте Массив, а также совершаем два полных прохода по коллекции (первый раз при поиске, второй раз при удалении). На больших таблицах (десятки тысяч строк) этот код будет работать медленно.
Чтобы оптимизировать алгоритм и отказаться от создания вспомогательных массивов, мы можем применить обход коллекции с конца. Напрямую удалять строки во время прямого обхода (Для Каждого) нельзя, так как при удалении текущей строки индексы последующих элементов смещаются, что приводит к пропускам строк или ошибке выхода за границы коллекции.
Если же мы сначала найдем точку перелома (индекс строки, на которой лимит превышен), то сможем быстро удалить всю оставшуюся «хвостовую» часть таблицы, двигаясь от конца к этой точке. Рассмотрим, как это выглядит на практике:
ПроверяемаяСумма = 400;
ОбщаяСумма = 0;
ИндексПревышения = Неопределено;
// Шаг 1. Ищем строку, на которой сумма превышает лимит
Для Каждого СтрокаТЗ Из ТЗ Цикл
ОбщаяСумма = ОбщаяСумма + СтрокаТЗ.ОбщСумма;
Если ОбщаяСумма > ПроверяемаяСумма Тогда
// Запоминаем индекс этой строки. Она должна остаться в таблице,
// а все строки, расположенные строго ЗА ней, подлежат удалению.
ИндексПревышения = ТЗ.Индекс(СтрокаТЗ);
Прервать; // Прекращаем обход, так как дальнейшие строки нас не интересуют
КонецЕсли;
КонецЦикла;
// Шаг 2. Если превышение найдено, удаляем все последующие строки с конца таблицы
Если ИндексПревышения <> Неопределено Тогда
Для Индекс = ТЗ.Количество() - 1 По ИндексПревышения + 1 Шаг -1 Цикл
ТЗ.Удалить(Индекс);
КонецЦикла;
КонецЕсли;
Преимущества оптимизированного метода:
Прервать) сразу же, как только набрали нужную сумму. Нам не нужно сканировать всю таблицу до самого конца, если лимит был превышен в самом начале.В реальной практике данные для таблицы значений практически всегда выбираются из базы данных с помощью запроса. Производить выгрузку миллионов строк в память сервера приложений 1С, чтобы потом фильтровать их в цикле — крайне неоптимальный подход. Гораздо эффективнее переложить расчет нарастающего итога на СУБД.
Для вычисления нарастающего итога в запросах 1С традиционно используется механизм левого соединения таблицы самой с собой по условию связи «больше или равно». В качестве уникального ключа последовательности строк обычно выступает дата, время, номер строки или уникальный идентификатор.
Проанализируем пример запроса, который вернет уже отфильтрованный по нарастающему итогу результат:
// Предположим, ВТ_Данные — это временная таблица, содержащая исходные строки
// с уникальным полем "НомерСтроки" и числовым полем "Сумма"
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Т1.НомерСтроки КАК НомерСтроки,
| Т1.Сумма КАК Сумма,
| СУММА(Т2.Сумма) КАК НарастающийИтог
|ПОМЕСТИТЬ ВТ_НарастающийИтог
|ИЗ
| ВТ_Данные КАК Т1
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Данные КАК Т2
| ПО Т1.НомерСтроки >= Т2.НомерСтроки
|ГРУППИРОВАТЬ ПО
| Т1.НомерСтроки,
| Т1.Сумма
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Результат.НомерСтроки КАК НомерСтроки,
| Результат.Сумма КАК Сумма,
| Результат.НарастающийИтог КАК НарастающийИтог
|ИЗ
| ВТ_НарастающийИтог КАК Результат
|ГДЕ
| (Результат.НарастающийИтог - Результат.Сумма) < &Лимит";
Запрос.УстановитьПараметр("Лимит", 400);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Как работает этот запрос:
ВТ_НарастающийИтог мы соединяем каждую строку исходной таблицы со всеми строками, которые находятся «выше» нее или равны ей (условие Т1.НомерСтроки >= Т2.НомерСтроки).СУММА(Т2.Сумма) мы получаем нарастающий итог для каждой строки.(Результат.НарастающийИтог - Результат.Сумма) < &Лимит отсекает все строки, которые идут после пороговой, возвращая нам идеально точный результат.Если задача решается в рамках построения отчетов, то писать сложные и «тяжелые» для СУБД запросы с соединениями нет необходимости. Система компоновки данных обладает встроенным оптимизированным функционалом для расчета нарастающих итогов «на лету» без дополнительной нагрузки на базу данных.
Рассмотрим, как настроить расчет нарастающего итога и фильтрацию в СКД:
НарастающийИтог.НарастающийИтог в список ресурсов. В выражении для расчета ресурса укажите встроенную функцию СКД:
ВычислитьВыражение("Сумма(Сумма)", , , "ПерваяСтрока", "ТекущаяСтрока")
Сумма от самой первой строки группировки до текущей.
[НарастающийИтог] - [Сумма], вид сравнения «Меньше», а в качестве правого значения — ваш лимит (например, параметр &Лимит).Этот способ является наиболее производительным для построения отчетов, так как СКД выполняет расчет нарастающего итога в памяти при выводе данных, не нагружая СУБД лишними операциями соединения таблиц.
Описанные выше алгоритмы лежат в основе классического метода FIFO (First In, First Out). В прикладных решениях 1С (таких как ERP, Комплексная автоматизация или Управление торговлей) этот алгоритм применяется повсеместно:
Понимание рассмотренных нами алгоритмов и умение выбрать правильный инструмент (будь то обратный цикл в коде, оптимизированный запрос или настройки компоновки данных) позволяет разработчику писать быстрый, чистый и профессиональный код, готовый к высоким нагрузкам.