Как в СКД получить движения регистра накопления за последний месяц активности по номенклатуре

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

Для регистров сведений такая задача решается срезом последних, однако для регистров накопления (особенно оборотных) встроенного механизма среза нет. Разберем типичные ошибки при построении таких запросов и рассмотрим правильные варианты реализации задачи с помощью языка запросов 1С и механизмов Системы компоновки данных (СКД).

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

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

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

Почему этот запрос возвращает записи за разные месяцы по одной номенклатуре:

1. Избыточная группировка: Агрегатная функция МАКСИМУМ(Период) рассчитывается для каждой уникальной комбинации всех полей секции СГРУППИРОВАТЬ ПО. Если в июне выпуск номенклатуры был по одной спецификации/партии, а в октябре — по другой, запрос вернет две строки (одну с датой за июнь, другую с датой за октябрь), так как комбинации измерений различны.
2. Неверная фильтрация в виртуальной таблице оборотов: При передаче жесткого параметра &НачалоПериода в параметры виртуальной таблицы оборотов данные за месяцы до этой даты будут отсечены еще до выполнения соединения.
3. Условия соединения по диапазону: Конструкции вида ПериодМесяц >= НАЧАЛОПЕРИОДА(...) И ПериодМесяц <= КОНЕЦПЕРИОДА(...) в условии соединения не пробрасываются в параметры виртуальной таблицы оборотов, что заставляет СУБД считывать большой объем данных и соединять их в памяти.

Решение 1: Пакетный запрос с временной таблицей максимальных дат

Разберем надежный и производительный подход. Для правильного определения последнего месяца движений необходимо разделить логику на два шага:

1. Определить максимальную дату движений исключительно в разрезе номенклатуры и поместить результат во временную таблицу.
2. Соединить полученную временную таблицу с реальной таблицей регистра или виртуальной таблицей оборотов для выборки детальных данных за этот месяц.

Посмотрим на пример текста пакетного запроса:

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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    втПоследниеДаты.Номенклатура КАК Номенклатура,
    ВыпускПродукции.Организация КАК Организация,
    ВыпускПродукции.СтруктурнаяЕдиница КАК СтруктурнаяЕдиница,
    ВыпускПродукции.Характеристика КАК Характеристика,
    ВыпускПродукции.Партия КАК Партия,
    ВыпускПродукции.ЗаказПокупателя КАК ЗаказПокупателя,
    ВыпускПродукции.Спецификация КАК Спецификация,
    ВыпускПродукции.Количество КАК Количество,
    ВыпускПродукции.Регистратор КАК Регистратор,
    ВыпускПродукции.Период КАК Период
ИЗ
    втПоследниеДаты КАК втПоследниеДаты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.ВыпускПродукции КАК ВыпускПродукции
        ПО (втПоследниеДаты.Номенклатура = ВыпускПродукции.Номенклатура)
        И (ВыпускПродукции.Период МЕЖДУ НАЧАЛОПЕРИОДА(втПоследниеДаты.МаксПериод, МЕСЯЦ) И КОНЕЦПЕРИОДА(втПоследниеДаты.МаксПериод, МЕСЯЦ))

Обратим внимание на ключевые особенности этого решения:

1. В первом запросе в секции СГРУППИРОВАТЬ ПО присутствует только Номенклатура. Это гарантирует, что для каждой позиции будет получена ровно одна строка с датой последней проводки.
2. Использование ИНДЕКСИРОВАТЬ ПО по полю Номенклатура ускоряет последующее соединение таблиц на уровне СУБД.
3. Для получения детальных записей и документов-регистраторов во втором запросе используется обращение к реальной таблице регистра, так как при обращении к полю Регистратор в виртуальной таблице оборотов преимущества предварительно рассчитанных итогов все равно теряются.

Решение 2: Использование связей наборов данных в СКД

Если отчет строится в СКД и требуется гибкая передача параметров, задачу можно решить через два связанных набора данных:

1. Набор данных 1 (Запрос): Выбирает номенклатуру и максимальную дату до границы отчета:
   Скопировать код

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

2. Набор данных 2 (Запрос): Выбирает обороты или движения с фильтром по номенклатуре и диапазону дат:
   Скопировать код

   
   ВЫБРАТЬ
       ВыпускПродукции.Номенклатура КАК Номенклатура,
       ВыпускПродукции.Организация КАК Организация,
       ВыпускПродукции.Регистратор КАК Регистратор,
       ВыпускПродукции.Количество КАК Количество,
       ВыпускПродукции.Период КАК Период
   ИЗ
       РегистрНакопления.ВыпускПродукции КАК ВыпускПродукции
   ГДЕ
       ВыпускПродукции.Номенклатура = &Номенклатура
       И ВыпускПродукции.Период МЕЖДУ &НачалоИнтервала И &КонецИнтервала
   

3. Настройка связи наборов данных:

   На вкладке «Связи наборов данных» указываем:

   1. Источник связи: Набор данных 1; Приемник: Набор данных 2.
   2. Условие связи: Номенклатура = Номенклатура.
   3. Параметры:
      1. Имя параметра: Номенклатура, Выражение источника: Номенклатура.
      2. Имя параметра: НачалоИнтервала, Выражение источника: НАЧАЛОПЕРИОДА(МаксПериод, "Месяц").
      3. Имя параметра: КонецИнтервала, Выражение источника: КОНЕЦПЕРИОДА(МаксПериод, "Месяц").

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