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