Как в 1С вычислить количество дней присутствия товаров на складе одним запросом

← На главную

Расчет времени нахождения товара на складе — одна из наиболее часто встречающихся задач в управленческом учете и анализе товарооборота. Она необходима для вычисления оборачиваемости, анализа неликвидов и оценки эффективности использования складских площадей. В данной статье мы подробно разберем, как реализовать этот расчет с помощью одного запроса в системе 1С:Предприятие 8.3, соблюдая требования к высокой производительности на больших объемах данных.

Постановка задачи и анализ исходных данных

Рассмотрим ситуацию: у нас есть регистр накопления ОстаткиНоменклатуры с измерениями Номенклатура, Склад и ресурсом Количество. Нам необходимо за заданный период (например, с 01.01.2021 по 10.01.2021) определить, сколько календарных дней каждый товар фактически находился на складе. При этом, если товар присутствовал на складе хотя бы часть дня, этот день должен быть засчитан как день присутствия.

Важные ограничения:

Использование только одного запроса (допускаются временные таблицы).
Запрет на использование циклов в коде 1С.
Запрет на использование СКД (задача должна быть решена на уровне чистого запроса).
Оптимизация под большие объемы данных (отказ от посекундной детализации).

Вариант 1: Использование виртуальной таблицы ОстаткиИОбороты

Проанализируем самый очевидный подход — использование виртуальной таблицы ОстаткиИОбороты с периодичностью День. Это позволяет системе автоматически подготовить данные по остаткам на начало и конец каждого дня, когда были движения.

Рассмотрим пример запроса, который собирает данные по периодам:


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

В этом подходе мы выбираем только те дни, когда конечный остаток был положительным. Однако здесь кроется подвох: виртуальная таблица ОстаткиИОбороты при периодичности День возвращает строки только за те дни, в которых были совершены обороты (приход или расход), либо за первый день периода, если есть начальный остаток. Если товар лежал неподвижно 5 дней, эти 5 дней не будут возвращены отдельными строками, и простая функция КОЛИЧЕСТВО(ПериодДень) выдаст неверный результат.

Вариант 2: Использование вспомогательной таблицы дат (Календаря)

Для корректного расчета на больших объемах данных наиболее эффективным методом считается соединение таблицы остатков с заранее подготовленным списком всех дат периода. Выясним причину: это позволяет «размножить» записи об остатках на каждый день, даже если движений не было.

Разберем по шагам создание такого запроса:

Шаг 1: Формирование временной таблицы всех дней периода. Мы можем получить список дат из любого достаточно большого регистра или производственного календаря. Если календаря нет, можно использовать конструкцию ОБЪЕДИНИТЬ ВСЕ для небольших интервалов или выбрать записи из системного справочника.


ВЫБРАТЬ
    ДОБАВИТЬКДАТЕ(&ДатаНачала, ДЕНЬ, Номера.Номер) КАК Дата
ПОМЕСТИТЬ ВТ_Календарь
ИЗ
    (ВЫБРАТЬ 0 КАК Номер ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ... ОБЪЕДИНИТЬ ВЫБРАТЬ 31) КАК Номера
ГДЕ
    ДОБАВИТЬКДАТЕ(&ДатаНачала, ДЕНЬ, Номера.Номер) <= &ДатаОкончания
;

Шаг 2: Получение остатков на каждый день. Теперь мы соединяем наш календарь с виртуальной таблицей остатков. Проанализируем ситуацию: нам нужно знать остаток на каждую дату из календаря.


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

Примечание: В реальном запросе вместо таблицы Остатки на фиксированную дату нужно использовать срез на каждый момент времени из календаря, что может быть ресурсоемким. Поэтому перейдем к более оптимизированному варианту.

Вариант 3: Оптимальный расчет через интервалы (Метод нарастающего итога)

Рассмотрим более продвинутый способ: вычисление периодов «с» и «по», когда товар был в наличии. Это позволяет избежать соединения каждой даты и значительно ускоряет работу с миллионами записей.

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

Посмотрим на пример реализации такого подхода:


ВЫБРАТЬ
    ОстаткиИОбороты.Склад КАК Склад,
    ОстаткиИОбороты.Номенклатура КАК Номенклатура,
    ОстаткиИОбороты.Период КАК ДатаНачалаИнтервала,
    МИНИМУМ(ЕСТЬNULL(ОборотыПоследующие.Период, &ДатаКонец)) КАК ДатаКонцаИнтервала,
    ОстаткиИОбороты.КоличествоКонечныйОстаток КАК ОстатокВИнтервале
ПОМЕСТИТЬ ВТ_ИнтервалыНаличия
ИЗ
    РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&ДатаНачала, &ДатаКонец, День, , ) КАК ОстаткиИОбороты
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Обороты(&ДатаНачала, &ДатаКонец, День, ) КАК ОборотыПоследующие
        ПО ОстаткиИОбороты.Склад = ОборотыПоследующие.Склад
        И ОстаткиИОбороты.Номенклатура = ОборотыПоследующие.Номенклатура
        И ОстаткиИОбороты.Период < ОборотыПоследующие.Период
СГРУППИРОВАТЬ ПО
    ОстаткиИОбороты.Склад,
    ОстаткиИОбороты.Номенклатура,
    ОстаткиИОбороты.Период,
    ОстаткиИОбороты.КоличествоКонечныйОстаток
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.Склад,
    ВТ.Номенклатура,
    СУММА(ВЫБОР
        КОГДА ВТ.ОстатокВИнтервале > 0
            ТОГДА РАЗНОСТЬДАТ(ВТ.ДатаНачалаИнтервала, ВТ.ДатаКонцаИнтервала, ДЕНЬ) + 1
        ИНАЧЕ 0
    КОНЕЦ) КАК КоличествоДнейПрисутствия
ИЗ
    ВТ_ИнтервалыНаличия КАК ВТ
СГРУППИРОВАТЬ ПО
    ВТ.Склад,
    ВТ.Номенклатура

Анализ критических ошибок и способы их исправления

При реализации данного запроса программисты часто сталкиваются с тремя проблемами, которые мы сейчас разберем.

1. Отрицательные остатки («Краснота»). В реальных базах из-за ошибок учета остаток может уйти в минус. В тексте задачи сказано: «находился на складе». Формально, если остаток -10, товара на складе нет. Поэтому в условии ГДЕ или ВЫБОР всегда используйте строгое неравенство Количество > 0. Использование <> 0 приведет к тому, что дни с дефицитом будут считаться днями наличия.

2. Остатки, сформированные до начала периода. Если товар поступил в 2020 году, а отчет мы строим за 2021 год, и движений в 2021 году не было, виртуальная таблица Обороты не вернет ничего. Для решения этой проблемы необходимо всегда объединять данные по движениям с данными таблицы Остатки на начало периода.

3. Производительность и индексы. При использовании временных таблиц (как в ВТ_ИнтервалыНаличия) обязательно добавляйте индексацию по полям Склад и Номенклатура. Без индексов левое соединение таблицы с самой собой (для поиска последующей даты) превратится в квадратичную зависимость, и запрос «зависнет» на базе среднего размера.