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