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

← На главную

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

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

Зачем отказываться от виртуальной таблицы СрезПоследних?

Прежде чем перейти к написанию кода, давайте ответим на вопрос: почему стандартный механизм платформы может не подходить? Выделим три основные причины:

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

Метод 1. Классический вариант с использованием временных таблиц (Рекомендуемый)

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

Рассмотрим ситуацию на примере периодического регистра сведений ЦеныНоменклатуры, у которого есть измерения Номенклатура, ВидЦены и ресурс Цена.

Алгоритм решения состоит из двух шагов:

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

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


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

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_МаксимальныеДаты.Номенклатура КАК Номенклатура,
    ВТ_МаксимальныеДаты.ВидЦены КАК ВидЦены,
    ВТ_МаксимальныеДаты.Период КАК Период,
    ЦеныНоменклатурыАналог.Цена КАК Цена
ИЗ
    ВТ_МаксимальныеДаты КАК ВТ_МаксимальныеДаты
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатурыАналог
        ПО ВТ_МаксимальныеДаты.Номенклатура = ЦеныНоменклатурыАналог.Номенклатура
        И ВТ_МаксимальныеДаты.ВидЦены = ЦеныНоменклатурыАналог.ВидЦены
        И ВТ_МаксимальныеДаты.Период = ЦеныНоменклатурыАналог.Период

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

Метод 2. Использование конструкции "ГДЕ ... В ..." (Вложенный запрос)

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

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


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

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

Метод 3. Срез последних на каждую дату в таблице-источнике

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

Предположим, у нас есть временная таблица ВТ_ТоварыДокументов с полями ДокументСсылка, Номенклатура, ВидЦены, ДатаДокумента. Наша задача — дополнить ее актуальной ценой.

Рассмотрим детально, как это реализовать:


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

СГРУППИРОВАТЬ ПО
    ТаблицаДокументов.ДокументСсылка,
    ТаблицаДокументов.Номенклатура,
    ТаблицаДокументов.ВидЦены
;

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

Этот двухшаговый алгоритм гарантирует быстрое и точное сопоставление цен с документами даже при обработке десятков тысяч строк. Мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы документы не исчезли из выборки, если для какого-то товара цена еще ни разу не была установлена.

Критический подводный камень: Угроза дублирования записей

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

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

Как от этого защититься?

Если регистр имеет периодичность «Позиция регистратора» (а не в пределах секунды), то уникальность записи гарантируется комбинацией полей Период, Регистратор и НомерСтроки. В этом случае в первом запросе для поиска максимума нам нужно группировать не только по периоду, но и находить максимальную позицию регистратора. На практике для этого во временную таблицу выбирают МАКСИМУМ(Период), а на втором шаге соединяют таблицы и страхуются дополнительной фильтрацией по регистратору и номеру строки.
Если периодичность регистра установлена в пределах секунды, но человеческий фактор или внешние интеграции допускают запись нескольких цен в одну секунду, единственным надежным выходом будет пересмотр архитектуры регистра (добавление уникального измерения, например, УникальныйИдентификаторСтроки или ключа связи) либо программная очистка дублей перед проведением расчетов.

Альтернатива в СКД: Связи наборов данных

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

Создайте Набор данных №1 (например, простой запрос, выбирающий список номенклатуры или документов).
Создайте Набор данных №2, в котором напишите запрос к стандартной виртуальной таблице ЦеныНоменклатуры.СрезПоследних(&Период, Номенклатура = &Номенклатура И ВидЦены = &ВидЦены).
Перейдите на вкладку «Связи наборов данных» и свяжите Набор №1 с Набором №2 по полям Номенклатура и ВидЦены. В качестве параметра &Период для Набора №2 передайте поле даты из Набора №1.

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

Математический трюк для обхода временных таблиц (Метод Гилева)

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

Идея метода заключается в том, чтобы закодировать дату и значение ресурса (например, цену) в одно большое число прямо в агрегатных функциях СУБД:

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

Этот метод практически не используется в промышленной разработке, так как его код крайне сложно поддерживать и отлаживать, но он наглядно демонстрирует гибкость платформы 1С и изобретательность разработчиков при обходе системных ограничений.

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