При разработке запросов в платформе 1С разработчики часто сталкиваются с ситуацией, когда стандартная виртуальная таблица СрезПоследних возвращает избыточные данные. Разберем типичный сценарий: периодический регистр сведений ОсновныеСпецификацииНоменклатуры имеет измерения Номенклатура и Подразделение, а также ресурс Спецификация. Задача — получить для каждой номенклатуры строго одну последнюю актуальную запись, узнав при этом подразделение и спецификацию.
Если мы обратимся напрямую к виртуальной таблице СрезПоследних, платформа вернет срез по первичному ключу, в который входят Период и абсолютно все измерения регистра. В результате по одной позиции номенклатуры система вернет несколько строк — по одной на каждое подразделение. Выясним причину этой проблемы и рассмотрим надежные варианты построения запроса.
Виртуальная таблица не умеет выполнять срез только по выбранному подмножеству измерений (например, только по номенклатуре, игнорируя подразделение). Поэтому срез приходится собирать вручную с помощью группировок реальной таблицы регистра.
Однако здесь кроется серьезная архитектурная ловушка: на одну и ту же дату в регистре могут существовать записи по одной номенклатуре, но для разных подразделений. Если мы попытаемся одновременно применить агрегатные функции МАКСИМУМ(Подразделение) и МАКСИМУМ(Спецификация) в одном запросе, мы рискуем получить некорректные данные: подразделение будет выбрано из одной строки регистра, а спецификация — из совершенно другой. Чтобы сохранить целостность данных, необходимо соблюдать четкую последовательность отбора ключей.
Разберем по шагам наиболее универсальный и контролируемый способ. Алгоритм состоит из трех этапов:
Посмотрим на пример реализации данного запроса в коде:
ВЫБРАТЬ
Регистр.Номенклатура КАК Номенклатура,
МАКСИМУМ(Регистр.Период) КАК Период
ПОМЕСТИТЬ ВТ_МаксПериод
ИЗ
РегистрСведений.ОсновныеСпецификацииНоменклатуры КАК Регистр
СГРУППИРОВАТЬ ПО
Регистр.Номенклатура
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_МаксПериод.Номенклатура КАК Номенклатура,
ВТ_МаксПериод.Период КАК Период,
МАКСИМУМ(Регистр.Подразделение) КАК Подразделение
ПОМЕСТИТЬ ВТ_КлючиЗаписей
ИЗ
ВТ_МаксПериод КАК ВТ_МаксПериод
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ОсновныеСпецификацииНоменклатуры КАК Регистр
ПО ВТ_МаксПериод.Номенклатура = Регистр.Номенклатура
И ВТ_МаксПериод.Период = Регистр.Период
СГРУППИРОВАТЬ ПО
ВТ_МаксПериод.Номенклатура,
ВТ_МаксПериод.Период
ИНДЕКСИРОВАТЬ ПО
Номенклатура,
Период,
Подразделение
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Ключи.Номенклатура КАК Номенклатура,
Ключи.Период КАК Период,
Ключи.Подразделение КАК Подразделение,
Регистр.Спецификация КАК Спецификация
ИЗ
ВТ_КлючиЗаписей КАК Ключи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ОсновныеСпецификацииНоменклатуры КАК Регистр
ПО Ключи.Номенклатура = Регистр.Номенклатура
И Ключи.Период = Регистр.Период
И Ключи.Подразделение = Регистр.Подразделение
Обратим внимание на директиву ИНДЕКСИРОВАТЬ ПО. При работе с большими объемами данных индексация полей соединения во временных таблицах гарантирует быстрое выполнение запроса СУБД без просадок по производительности.
Если в конфигурации используется современная версия платформы с поддержкой оконных функций, задачу можно решить в один проход без создания временных таблиц и многократных соединений. Используем функцию ROW_NUMBER() с секционированием по номенклатуре.
Проанализируем пример такого запроса:
ВЫБРАТЬ
Подзапрос.Период КАК Период,
Подзапрос.Номенклатура КАК Номенклатура,
Подзапрос.Подразделение КАК Подразделение,
Подзапрос.Спецификация КАК Спецификация
ИЗ
(ВЫБРАТЬ
Регистр.Период КАК Период,
Регистр.Номенклатура КАК Номенклатура,
Регистр.Подразделение КАК Подразделение,
Регистр.Спецификация КАК Спецификация,
ROW_NUMBER() OVER (
PARTITION BY Регистр.Номенклатура
ORDER BY Регистр.Период УБЫВ, Регистр.Подразделение УБЫВ
) КАК НомерСтрокиВГруппе
ИЗ
РегистрСведений.ОсновныеСпецификацииНоменклатуры КАК Регистр) КАК Подзапрос
ГДЕ
Подзапрос.НомерСтрокиВГруппе = 1
Разберем подробнее, как это работает: секция PARTITION BY разбивает строки на группы по номенклатуре, а конструкция ORDER BY Регистр.Период УБЫВ, Регистр.Подразделение УБЫВ сначала сортирует записи по убыванию даты, а при совпадении дат — упорядочивает подразделения. Каждой строке присваивается порядковый номер, и фильтр ГДЕ Подзапрос.НомерСтрокиВГруппе = 1 оставляет строго одну, самую свежую и согласованную запись.
Когда требуется получить актуальную спецификацию не для всей номенклатуры разом, а только для одного конкретного элемента в документе или форме, выполнять группировки всей таблицы нерационально. Оптимальнее сделать прямой запрос к реальной таблице с ограничением выборки.
Посмотрим на пример:
ВЫБРАТЬ ПЕРВЫЕ 1
Регистр.Период КАК Период,
Регистр.Подразделение КАК Подразделение,
Регистр.Спецификация КАК Спецификация
ИЗ
РегистрСведений.ОсновныеСпецификацииНоменклатуры КАК Регистр
ГДЕ
Регистр.Номенклатура = &Номенклатура
И Регистр.Период <= &Период
УПОРЯДОЧИТЬ ПО
Регистр.Период УБЫВ,
Регистр.Подразделение УБЫВ
Такой запрос отрабатывает мгновенно за счет попадания в индекс регистра и гарантированно возвращает одну запись даже при совпадении периодов.
ГДЕ Номенклатура В (&СписокНоменклатуры) на первом шаге выборки максимального периода. Это избавит СУБД от полного сканирования таблицы (Table Scan).МАКСИМУМ(Подразделение) пустая ссылка проиграет заполненной. Если требуется учитывать значения по умолчанию, логику приоритета подразделений необходимо настраивать индивидуально.