Каталог решений - Нарастающие итоги в запросе и методы ускорения его выполнения.

Любезные критики, смотрите P.S. к статье.
Есть множество задач, в которых требуется получение и/или обработка нарастающих итогов. В частности это дебиторская/кредиторская задолженность и расчетные листки. Задача легко решается в рамках встроенного языка, но по разным причинам это не всегда приемлемо (подробнее см. P.S.).  Язык запросов 1с позволяет получить нарастающие итоги. Как подсказывает логика, итоги должны нарастать относительно какого-либо показателя. Самый распространенный случай – период. Именно такой случай и будет рассматривать. Так как лично столкнулся с необходимостью использования нарастающих итогов в рамках анализа дебиторской задолженности, то и в статье будем рассматривать её.
Итак, для чего при анализе дебиторской задолженности могут потребоваться нарастающие итоги? Для формирования списка документов по текущей задолженности (при этом абсолютно не важно с какой степенью детализации ведутся взаиморасчеты – алгоритм такой же). Этот список даёт важную информацию для дальнейшего анализа – дата возникновения задолженности, число дней долга, если есть параметр числа дней задолженности, то количество дней просрочки, сумма просроченного долга, ну и сам список документов долга.
Для начала нам потребуется получить список документов взаиморасчетов контрагента:
Реализация товаров и услуг №1          500р
Реализация товаров и услуг №2          1000р
Платежное поручение исходящее №1 -500р
Реализация товаров и услуг №3          1200р
Итого                                                  2200р

Для того чтобы получить нарастающий итог нужно использовать ещё одну такую же таблицу и связать её с первой. Связывать нужно по дате (при совпадении времени можно дополнительно сравнивать по моменту времени), но связывать не через равенство, а через сравнение >= или
Реализация товаров и услуг №1           500р 500р
Реализация товаров и услуг №1           500р 1000р
Реализация товаров и услуг №1           500р -500р
Реализация товаров и услуг №1           500р 1200р
Реализация товаров и услуг №2           1000р 1000р
Реализация товаров и услуг №2           1000р -500р
Реализация товаров и услуг №2           1000р 1200р
Платежное поручение исходящее №1 -500р -500р
Платежное поручение исходящее №1 -500р 1200р
Реализация товаров и услуг №3           1200р 1200р

Как-то некрасиво…А, ну да! Нужно сгруппировать по всем поля Основного списка с суммированием поля вспомогательного. В итоге получим искомое:

Реализация товаров и услуг №1           500р 2200р
Реализация товаров и услуг №2           1000р 1700р
Платежное поручение исходящее №1  -500р 700р
Реализация товаров и услуг №3           1200р 1200р

Пример запроса

ВЫБРАТЬ
    ПериодыИзменения.Период КАК Время,
    СУММА(Регистр.СуммаОборот) КАК Сумма
ИЗ
    (ВЫБРАТЬ РАЗЛИЧНЫЕ
        ПродажиПоДисконтнымКартам.Период КАК Период
    ИЗ
        РегистрНакопления.ПродажиПоДисконтнымКартам КАК ПродажиПоДисконтнымКартам
    ГДЕ
        ПродажиПоДисконтнымКартам.ДисконтнаяКарта = &Ссылка) КАК ПериодыИзменения
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
            ПродажиПоДисконтнымКартамОбороты.Период КАК Период,
            ПродажиПоДисконтнымКартамОбороты.СуммаОборот КАК СуммаОборот
        ИЗ
            РегистрНакопления.ПродажиПоДисконтнымКартам.Обороты(, , Регистратор, ДисконтнаяКарта = &Ссылка) КАК ПродажиПоДисконтнымКартамОбороты
        ГДЕ
            ПродажиПоДисконтнымКартамОбороты.ДисконтнаяКарта = &Ссылка) КАК Регистр
        ПО ПериодыИзменения.Период >= Регистр.Период

СГРУППИРОВАТЬ ПО
    ПериодыИзменения.Период

Все…..можно было бы и так сказать, но у такого решения есть один существенный недостаток – с ростом числа строк в таблице время выполнения запроса увеличивается геометрически (или экспоненциально). Для ускорения требуется оптимизация. Приведу 3 способа:
1. Ограничить период в запросе. Самый распространенный (честно говоря единственный найденный мной на степях интернета), позволяет ограничить число строк в таблице, а значит и ускорить время запроса. Наиболее легкий способ, но имеет недостаток – всё что не входит в период не суммируется – можно и не угадать с периодом, да и необходимее периоды иногда бывают очень большими.

2. Метод последовательного приближения. Строго говоря, метод не ускоряет получения нарастающих итогов для всей таблицы, а только ускоряет поиск нужного значения с использованием нарастающих итогов. Вернемся к примеру с дебиторской задолженностью. Таблица документов долга не будет совпадать со всей таблицей – 500 рублей уже заплатили. Нам требуется получить таблицу с документами возникновения долга (реализация) сумма которых в обратном порядке от последнего числа равна текущему долгу 2200р. Для таких малых таблиц ускорение не требуется, но когда речь идет о большом количестве контрагентов и взаиморасчетах за несколько лет, то можно применить метод последовательного приближения. Идея заключается в том, чтобы сгруппировать документы по периоду и детализировать только нужные периоды. Удобнее всего это делать через пакетные запросы. В моем случае я использовал следующий алгоритм: сначала получил 3 дополнительные таблицы: группировка по годам, группировка по месяцам и группировка по дням. Сначала уже известным способом для таблицы по годам рассчитываем нарастающие итоги. Далее получаем из неё 2 таблицы: если сумма долга с нарастанием для года меньше общего долга, то включаем его в таблицу безусловного включения в конечную таблицу, а первый год, где итоговая сумма больше долга (год с минимальным превышением), отправляем в таблицу для дальнейшего рассмотрения и не забываем передать туда сумму остатка долга (общая сумма долга минус сумма долга из таблицы безусловного включения). Именно по этой таблице отбираем данные из таблицы сгруппированной по месяцам. И также как для таблицы с годами, делим её на 2 таблицы по сумме остаточного долга. Далее таблицу для дальнейшего рассмотрения используем для отбора в таблице по дням. Ну и после делаем всё тоже самое для дальнейшего рассмотрения по дням и основной таблицы.. В результате мы получаем следующие таблицы: безусловное включение по годам, безусловное включение по месяцам, безусловное включение по дням и документы долга в первый день (наиболее удаленные от дня отчета) долга. В последней фазе отбираем из основной таблицы конкретные документы с использованием вышеперечисленных таблиц. Объединяем и вуаля! Готовая таблица документов долга.
Недостаток этого метода – сложность реализации. Сама реализация на //sale.itcity.ru/public/20221/ — ссылка на файл «Новый запрос»

3. Метод сложения периодов. Более универсальный метод и позволяет ускорить именно получение нарастающих итогов. При расчете нарастающих итогов в условие связи добавляется дополнительное условие, чтобы разделить общую таблицу на подтаблицы (желательно помельче). Далее последовательно объединяем эти подтаблицы в более крупные подтаблицы с нарастанием итогов — к каждому последующему периоду прибавляется итоги (последняя сумма в подтаблице) предыдущих периодов. И так далее в зависимости от выбранного для конкретного случая числа итераций.
Недостаток метода – тоже достаточная сложность реализации и то, что этот метод не разработан в деталях – я окончательно его сформулировал только что.

P.S. «Не надо ругать пианиста, он играет как умеет». Я работаю с 8кой, поэтому не могу сказать, что частности реализации методов для 7ки будет таким же (если это вообще возможно), но общие алгоритмы универсальны.

По поводу необходимости получать нарастающие итоги в запросе — в 1с 8 есть возможность создание отчетов с помощью мастеров только из запроса, не используя встроенный язык. Это существенно ускоряет создание отчетов. И когда отчет сам по себе очень прост, то переходить из-за нарасающих итогов на встроенный язык нецелесообразно. Второй случай, когда структура выводимых данных иерархична и строится в нескольких разрезах — язык запросов более гибок. Пример в статье дан именно как пример, для лучшего понимания алгоритма — в реальной задаче такой частный случай с одним клиентом и одним показателем легче решить встроенным языком. Кроме того, как я уже говорил в статье, у запросного метода есть один глобальный недостаток — рост времени выполнения при увеличении объема исходных данных. Но недостаток это можно устранить сложными методами оптимизации, после которых запрос выполняется быстрее встроенного языка.

Метод подсчета нарастающих итогов известен и много раз публиковался на форумах, но именно подробной статьи не было. Метод простой, и статья только про него вышла бы очень короткой. Поэтому добавил описание методов ускорения расчетов. 1 метод используют для ускорения расчетов только запросом (если ускорение вообще используется) все отчеты по задолженности, которые я рассматривал. И в некоторых комментариях я уже писал, что ограничение глубины рассмотрения задолженности – это метод Александра Македонского для узлов – проблема вроде бы решена, только веревка уже не целая. 2 метод я использую для своих отчетов по дебиторской задолженности. После применения этого метода время исполнения отчета для достаточно крупной базы (по всем покупателям) уменьшилось с более 30 минут, до минуты и менее – и не надо про производительность и т.д. всё это уже проверялось. Все варианты отчетов можно посмотреть в моих разработках, в т.ч. и запрос по методу 2 в чистом виде. Ну и 3 метод мне пока не требовался и окончательно я его сформулировал только сейчас. Дискуссия и плюсование поощряется.