Еще один способ расчета остатков на каждый день в запросе

24.04.2016

Еще один способ расчета остатков на каждый день в запросе

Предлагается новый способ расчета остатков на каждый день (час, минуту, секунду) в запросе. Способ не требует предварительного формирования таблицы дат и также подходит для расчета курсов валют, цен номенклатуры и других периодических сведений на каждую дату периода. На больших объемах данных предлагаемый способ может превосходить по быстродействию ранее известные методы из-за линейной (в лучшем случае) зависимости трудоемкости от длины периода.

Разработчики платформы "1С: Предприятие 8" постарались сделать все, чтобы при решении рядовых задач не приходилось ломать голову, придумывая сложные алгоритмы. Но несколько нетривиальных задач все же осталось. Одной из них является задача расчета остатков на каждый день в запросе.

Появление этой задачи связано с тем, что виртуальная таблица  остатков и оборотов с периодом "день" (например) не содержит записей для периодов, в которых не было оборотов. Очевидно, это было сделано умышленно. Из-за того, что определение значения остатков внутри периода хранения итогов связано с решением задачи интегрирования оборотов (расчета нарастающего итога). При использовании базового набора команд реляционных СУБД (без оконных функций) это довольно ресурсоемкая задача. Поэтому отказ от расчета в виртуальной таблице остатков и оборотов повторяющихся (а значит, в определенном смысле, избыточных) значений в большинстве случаев экономит время выполнения запроса. В тех, более редких, случаях, когда эти данные оказываются нужны, предлагается решать эту задачу дополнительными построениями на языке запросов или средствами СКД.

Хотя кажется, что все решают задачу расчета остатков на каждый день "кто во что горазд" и вариантов решения очень много, все известные решения в целом можно разделить всего на два класса.

В первом основой является таблица оборотов. Исходные дневные обороты суммируются в интервале от начала до каждой даты периода. Этот подход в точности воспроизводит работу платформы по определению остатков внутри периода их хранения для формирования виртуальной таблицы остатков и оборотов. Отличие только в том, что суммирование выполняется для каждого дня. Примером такого подхода является решение задачи 6 из статьи [Минимализмы].

Во втором подходе основой является таблица остатков тех периодов, где были обороты. Остатки для пропущенных дней находятся по принципу срезов последних: для каждой даты находится ближайший в прошлом остаток. Примером этого подхода является решение из статьи [Остатки на каждый день периода одним запросом - универсальный]. 

И в первом и во втором подходе требуется предварительно сформировать таблицу всех дат периода. Для этого обычно используются: производственный календарь, таблица курсов валют или искусственная таблица дат периода, сформированная одним из общеизвестных способов [Работаем с датами в запросе, Порождающий запрос].

Новый способ, предлагаемый в данной статье,  опирается на таблицу остатков, но НЕ ТРЕБУЕТ ПРЕДВАРИТЕЛЬНОГО ФОРМИРОВАНИЯ ТАБЛИЦЫ ДАТ. За счет многократного повторения одного простого запроса остаток одного дня распространяется (по принципу домино) на следующие дни. За счет этого пропуски в таблице остатков быстро заполняются.

Вот предлагаемый запрос:

ВЫБРАТЬ
    Номенклатура,
    Период,
    ВНаличииКонечныйОстаток КАК Было
ПОМЕСТИТЬ Шаг0
ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, День, , )

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    Номенклатура,
    &НачалоПериода,
    0
ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, ДЕНЬ, , )
;

////////Повторяется Х раз////////
ВЫБРАТЬ
    Номенклатура,
    Период,
    ЕСТЬNULL(МАКСИМУМ(Было), МАКСИМУМ(Стало)) КАК Было
ПОМЕСТИТЬ Шаг1
ИЗ
    (ВЫБРАТЬ
        Номенклатура КАК Номенклатура,
        Период КАК Период,
        Было КАК Было,
        NULL КАК Стало
    ИЗ      
        Шаг0 //таблица, полученная на предыдущем шаге
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        Номенклатура,
        ДОБАВИТЬКДАТЕ(Период, ДЕНЬ, 1),//каждый раз вдвое больше дней: 1, 2, 4 и так далее
        NULL,
        Было
    ИЗ      
        Шаг0
    ГДЕ
        ДОБАВИТЬКДАТЕ(Период, ДЕНЬ, 1) <= &КонецПериода) КАК Куча

СГРУППИРОВАТЬ ПО
    Номенклатура,
    Период
;
////////конец повторов////////

ВЫБРАТЬ
    Номенклатура,
    Период,
    Было
ИЗ
    ШагХ

УПОРЯДОЧИТЬ ПО
    Номенклатура,
    Период

 

Единственным отличием каждого повторяющегося фрагмента от следующего является в два раза больший интервал, прибавляемый к дате. Для заполнения интервала из 32 дней достаточно пятикратного , а для интервала из 1024 дней - десятикратного повторения ключевого фрагмента. Принцип, наверное, понятен. Повторы можно реализовать, построив текст запроса динамически или взяв их количество с запасом.

Возможно, кому-то приведенный запрос покажется похожим на  "машину Руби Голдберга" (она изображена на картинке в анонсе). Сходство действительно есть. Тут тоже используется принцип домино. Но вот с точки зрения эффективности все наоборот: приведенный запрос очень экономен. Наилучшим случаем является большая разреженность таблицы остатков. Когда, например, оборот (и остаток) задан в исходной таблице оборотов и остатков  только за один день периода, потребуется ровно N операций для заполнения пропусков на интервале из N дней. В наихудшем случае отсутствия пропусков для работы алгоритма потребуется примерно N/2 * log (N) времени. Оно уйдет на безуспешные попытки распространить остаток на уже "занятые" периоды. 

Наверное, понятно, что тот же запрос может быть применен и для решения задачи расчета курсов валют на каждый день, цен номенклатуры на каждый день и других задач интерполяции периодических сведений. Для этого исходную таблицу остатков потребуется заменить на таблицу соответствующего периодического регистра сведений.

Таким образом, этот подход работает при решении любой задачи линейно-ступенчатой интерполяции в запросе. Для определенности и из-за сходства принципов можно назвать предложенный способ методом домино.

Для примера к статье приложен отчет на базе СКД, решающий одновременно задачи нахождения остатков и цен на каждый день. Интересно, что предложенный прием позволяет решать эти задачи одновременно. Отчет проверен на конфигурации 1С:ERP Управление предприятием 2.1, но должен работать и в УТ11 и в КА2.

Специального сравнительного тестирования производительности нового способа и обычных способов в данном случае не проводилось. В других случаях [Быстрое определение интервалов в запросе] похожие подходы начинают выигрывать у традиционных при достаточно больших значениях N. Обычно это сотни и тысячи дат в периоде. Поэтому решающих преимуществ у данного способа перед уже известными на обычных средних объемах данных как будто бы нет.  Кроме своеобразной красоты регулярности итогового запроса.

Арт-объект, упражнение по языку запросов, средство развития кругозора или инструмент анализа больших данных - хотя бы одно из этих применений для приведенного нового способа, надеюсь, найдется. 

Текст публикации

Все новости