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