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

← На главную

При разработке в среде 1С:Предприятие программисты регулярно сталкиваются с необходимостью получения среза последних данных из периодических регистров сведений. Для этой задачи платформа предоставляет удобную виртуальную таблицу СрезПоследних. Однако стандартными средствами СУБД получить предпоследнюю запись (то есть состояние объекта непосредственно перед текущим изменением) в один клик невозможно. Платформа 1С не имеет виртуальной таблицы «СрезПредпоследних».

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

В чем сложность задачи и почему «взрывается» выборка?

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

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


ВЫБРАТЬ
    НаливПродуктаНов.Вагон,
    НаливПродуктаНов.Грузополучатель,
    НаливПродуктаНов.Грязь,
    МАКСИМУМ(НаливПродуктаНов.Период) КАК Период
ИЗ
    РегистрСведений.НаливПродуктаНов КАК НаливПродуктаНов
...
СГРУППИРОВАТЬ ПО
    НаливПродуктаНов.Вагон,
    НаливПродуктаНов.Грузополучатель,
    НаливПродуктаНов.Грязь

Почему этот подход в корне неверен? Когда мы добавляем ресурсы (Грузополучатель, Грязь) в секцию СГРУППИРОВАТЬ ПО, СУБД начинает искать максимальный период для каждой уникальной комбинации вагона, получателя и уровня грязи. Если один и тот же вагон за свою историю совершил 10 поездок к разным грузополучателям, запрос выдаст предпоследнюю дату для каждого грузополучателя в отдельности, а не одну истинную предпоследнюю дату для конкретного вагона. В результате вместо ожидаемых 1000 строк мы получим лавинообразный рост выборки до 5000–6000 строк и более.

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

Выяснив причину частой ошибки, перейдем к разработке правильного и быстрого алгоритма.

Пошаговый алгоритм решения задачи

Для надежного получения предпоследней записи мы применим пакетный запрос, состоящий из трех шагов:

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

Рассмотрим, как выглядит этот алгоритм на практике в коде запроса 1С.

Реализация пакетного запроса

Ниже представлен оптимизированный и проверенный шаблон запроса для получения предпоследней записи по железнодорожным вагонам:


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

ИНДЕКСИРОВАТЬ ПО
    Вагон
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Находим максимальный период, который меньше последнего периода
ВЫБРАТЬ
    МАКСИМУМ(РегистрИсточника.Период) КАК ПредпоследнийПериод,
    РегистрИсточника.Вагон КАК Вагон
ПОМЕСТИТЬ втПредпоследняя
ИЗ
    РегистрСведений.НаливПродуктаНов КАК РегистрИсточника
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПоследняя КАК втПоследняя
        ПО РегистрИсточника.Вагон = втПоследняя.Вагон
        И РегистрИсточника.Период < втПоследняя.ПоследнийПериод
СГРУППИРОВАТЬ ПО
    РегистрИсточника.Вагон

ИНДЕКСИРОВАТЬ ПО
    Вагон,
    ПредпоследнийПериод
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Получаем ресурсы (Грузополучатель, Грязь) для предпоследнего периода
ВЫБРАТЬ
    втПредпоследняя.Вагон КАК Вагон,
    втПредпоследняя.ПредпоследнийПериод КАК Период,
    РегистрДанных.Грузополучатель КАК Грузополучатель,
    РегистрДанных.Грязь КАК Грязь
ИЗ
    втПредпоследняя КАК втПредпоследняя
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.НаливПродуктаНов КАК РегистрДанных
        ПО втПредпоследняя.Вагон = РегистрДанных.Вагон
        И втПредпоследняя.ПредпоследнийПериод = РегистрДанных.Период

Пример применения алгоритма для цен номенклатуры

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


// ШАГ 1: Находим последнюю цену на определенную дату
ВЫБРАТЬ
    ЦеныСрезПоследних.Период КАК ПоследнийПериод,
    ЦеныСрезПоследних.Номенклатура КАК Номенклатура,
    ЦеныСрезПоследних.ВидЦены КАК ВидЦены
ПОМЕСТИТЬ втПоследняяЦена
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&НаДату, ВидЦены = &ВидЦены) КАК ЦеныСрезПоследних

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

////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Вычисляем предпоследний период цены для каждой номенклатуры
ВЫБРАТЬ
    МАКСИМУМ(ЦеныФизическая.Период) КАК ПредпоследнийПериод,
    ЦеныФизическая.Номенклатура КАК Номенклатура,
    ЦеныФизическая.ВидЦены КАК ВидЦены
ПОМЕСТИТЬ втПредпоследняяЦена
ИЗ
    РегистрСведений.ЦеныНоменклатуры КАК ЦеныФизическая
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ втПоследняяЦена КАК втПоследняяЦена
        ПО ЦеныФизическая.Номенклатура = втПоследняяЦена.Номенклатура
        И ЦеныФизическая.ВидЦены = втПоследняяЦена.ВидЦены
        И ЦеныФизическая.Период < втПоследняяЦена.ПоследнийПериод
СГРУППИРОВАТЬ ПО
    ЦеныФизическая.Номенклатура,
    ЦеныФизическая.ВидЦены

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    ВидЦены,
    ПредпоследнийПериод
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Выбираем саму предпоследнюю цену
ВЫБРАТЬ
    втПредпоследняяЦена.Номенклатура КАК Номенклатура,
    втПредпоследняяЦена.ВидЦены КАК ВидЦены,
    втПредпоследняяЦена.ПредпоследнийПериод КАК Период,
    ЦеныДанные.Цена КАК Цена
ИЗ
    втПредпоследняяЦена КАК втПредпоследняяЦена
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныДанные
        ПО втПредпоследняяЦена.Номенклатура = ЦеныДанные.Номенклатура
        И втПредпоследняяЦена.ВидЦены = ЦеныДанные.ВидЦены
        И втПредпоследняяЦена.ПредпоследнийПериод = ЦеныДанные.Период

Оптимизация производительности и индексация

Если ваш регистр сведений содержит сотни тысяч или миллионы записей, представленный пакетный запрос может создать серьезную нагрузку на СУБД, вплоть до получения ошибок таймаута выполнения. Проанализируем критически важные приемы оптимизации производительности:

Индексация временных таблиц. Это обязательное требование при работе с СУБД в 1С. Обратите внимание на инструкции ИНДЕКСИРОВАТЬ ПО в первом и втором запросах пакета. Без индексов SQL-сервер будет вынужден выполнять тяжелое сканирование таблиц (Table Scan) при соединении временных таблиц с физическим регистром, что парализует работу базы данных.
Ограничение выборки на ранних этапах. Старайтесь передавать параметры отбора (например, конкретную группу номенклатуры, список складов или собственника вагонов) непосредственно в параметры виртуальной таблицы СрезПоследних на первом шаге.
Перенос фильтров во второй запрос пакета. Чтобы СУБД не сканировала весь физический регистр на втором шаге во время поиска максимума, обязательно продублируйте условия отбора в секции ГДЕ. Например: ГДЕ РегистрИсточника.Вагон.СобственникОКПО = &ПараметрОКПО Это заставит оптимизатор SQL использовать индекс по измерениям физического регистра сведений.

Обработка сложных и пограничных сценариев

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

Сценарий 1. У объекта всего одна запись в истории.
Если по конкретному вагону в базе данных есть только одна запись, то условия РегистрИсточника.Период < втПоследняя.ПоследнийПериод не вернет строк. При использовании ВНУТРЕННЕГО СОЕДИНЕНИЯ на третьем шаге такой вагон полностью исчезнет из итогового отчета.

Решение: Если по требованиям бизнес-логики вам необходимо вывести список всех вагонов, даже если у них нет предыстории (оставляя поля предпоследних данных пустыми), замените ВНУТРЕННЕЕ СОЕДИНЕНИЕ на ЛЕВОЕ СОЕДИНЕНИЕ на финальном шаге запроса, а пустые значения обработайте функцией ЕСТЬNULL(РегистрДанных.Грязь, 0).

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

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

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