Как получить срез последних на каждую дату в запросе 1С

Программист 1С v8.3 (Управляемые формы) IT и автоматизация бизнеса
← На главную

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

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

Почему нельзя использовать СрезПоследних в цикле или нестрогом соединении?

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

Попытка передать временную таблицу дат непосредственно в параметры виртуальной таблицы СрезПоследних, как это пытался сделать автор темы:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСтатусов.СрезПоследних(&Даты) КАК РегистрСтатусовСрезПоследних

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

Если же мы попробуем обойти это ограничение, делая левое соединение таблицы дат с виртуальной таблицей СрезПоследних БЕЗ параметров, а условие по дате наложим в секции ПО:


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСтатусов.СрезПоследних() КАК РегистрСтатусовСрезПоследних
ПО ТаблицаДат.Дата >= РегистрСтатусовСрезПоследних.Период

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

Рассмотрим правильные и эффективные архитектурные подходы к решению этой задачи.

Решение 1. Эмуляция среза последних через пакетный запрос (Классический метод)

Этот способ является "золотым стандартом" в разработке на платформе 1С. Мы полностью отказываемся от использования виртуальной таблицы СрезПоследних и эмулируем ее работу вручную с помощью пакета запросов и временных таблиц.

Разберем логику этого подхода по шагам:

  1. Подготовка исходных дат. Помещаем интересующие нас даты во временную таблицу ВТ_Даты.
  2. Получение максимального периода. Соединяем таблицу дат с физической таблицей регистра сведений по условию ВТ_Даты.Дата >= ФизическийРегистр.Период. Группируем результат по измерениям регистра и исходной дате, выбирая максимум от поля Период регистра. Результат помещаем во временную таблицу ВТ_МаксимальныеПериоды.
  3. Получение ресурсов регистра. Соединяем таблицу ВТ_МаксимальныеПериоды обратно с физической таблицей регистра сведений по равенству измерений и найденного максимального периода. Это позволяет вытащить нужные значения ресурсов (статусы, цены и т.д.).

Посмотрим на практический пример реализации такого запроса для регистра документов физических лиц:


// Шаг 1: Помещаем исходную таблицу нужных дат во временную таблицу
ВЫБРАТЬ
    ТаблицаДат.Дата КАК Дата
ПОМЕСТИТЬ ВТ_Даты
ИЗ
    &ТаблицаДат КАК ТаблицаДат
;

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

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

Важные правила оптимизации при использовании этого метода:

Решение 2. Использование Системы компоновки данных (СКД)

Если задача стоит в рамках разработки отчета, то писать сложный пакетный запрос вручную нет необходимости. В СКД заложен мощный механизм — Связи наборов данных, который позволяет решать задачу "среза на каждую строку" декларативно и максимально эффективно.

Проанализируем алгоритм настройки этой схемы:

  1. Создаем Набор данных - запрос 1 (Источник). Это запрос, который получает наши базовые данные. Например, продажи товаров с полями Период и Номенклатура.
  2. Создаем Набор данных - запрос 2 (Приемник). Пишем простой запрос к виртуальной таблице СрезПоследних нашего регистра цен с использованием параметров: 
    
ВЫБРАТЬ
    Цены.Номенклатура,
    Цены.Цена
ИЗ
    РегистрСведений.ЦеныНоменклатуры.СрезПоследних(&ПериодСреза, Номенклатура = &ПараметрНоменклатура) КАК Цены
  3. Переходим на вкладку «Связи наборов данных» и добавляем строки связи:
    • Источник: Набор 1, Приемник: Набор 2. Связываем поле Период с полем ПериодСреза в колонке "Параметр".
    • Источник: Набор 1, Приемник: Набор 2. Связываем поле Номенклатура с полем ПараметрНоменклатура в колонке "Параметр".

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

Особенности оптимизации под СУБД PostgreSQL

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

На таблицах объемом свыше 1 миллиона записей планировщик PostgreSQL может ошибочно выбрать неоптимальный план запроса (например, Nested Loop вместо эффективного Hash Join), посчитав, что нестрогое соединение вернет слишком мало строк. В результате запрос, который на MS SQL Server выполнялся за секунды, на Postgres может зависнуть на часы.

Чтобы избежать этой проблемы, придерживайтесь следующих рекомендаций:

← На главную