Как получить последний актуальный автомобиль по каждому контрагенту, если автомобиль — измерение, а контрагент — ресурс в 1С

← На главную

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

Однако на практике встречаются гораздо более сложные и нетривиальные структуры метаданных. В этой статье мы подробно разберем, как решить задачу, когда привычная логика среза последних переворачивается «с ног на голову»: нам необходимо получить последнее актуальное измерение по значению ресурса. А именно — определить последний актуальный автомобиль по каждому контрагенту, при условии, что автомобиль в периодическом регистре является измерением, а контрагент — ресурсом составного типа.

В чем сложность задачи и почему простой срез не работает?

Давайте подробно проанализируем структуру нашего регистра сведений. Он является периодическим и имеет следующие поля:

Измерение: Автомобиль (тип СправочникСсылка.Автомобили)
Измерение: ВидЗначения (тип ПеречислениеСсылка.ДополнительнаяИнформацияАвтомобилей)
Ресурс: Значение (составной тип, в котором нас интересует СправочникСсылка.Контрагенты)

Бизнес-требование звучит следующим образом: необходимо получить последний актуальный автомобиль по каждому контрагенту. Под «актуальным» понимается автомобиль, которым контрагент владеет в данный момент (то есть этот контрагент является последним зарегистрированным хозяином машины). Если у контрагента сейчас несколько машин, мы должны выбрать ту, которая была приобретена последней. Если контрагент когда-то владел машиной, но потом продал ее другому человеку, этот автомобиль больше не должен отображаться за старым владельцем.

Попытка решить задачу «в лоб» через соединение справочника контрагентов с виртуальной таблицей СрезПоследних по условию АвтомобилиСрезПоследних.Значение = Контрагенты.Ссылка приведет к грубым ошибкам в данных:

Проблема дублирования данных: поскольку измерением является автомобиль, стандартный срез последних выдаст последнее состояние для каждого автомобиля. Если мы соединим его по значению ресурса, один контрагент может получить сразу несколько автомобилей, которыми он когда-либо владел, без учета того, какой из них действительно последний.
Проблема «исторических призраков»: если автомобиль сменил владельца (был продан), то в регистре появится новая запись с новым владельцем. Но если мы будем искать последнее авто по старому владельцу напрямую через историю, мы можем ошибочно закрепить за ним машину, которая ему уже давно не принадлежит.

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

Алгоритм решения задачи

Для получения корректных данных нам необходимо выстроить следующую логическую цепочку:

Шаг 1. Получить текущих владельцев для каждого автомобиля. Так как измерением регистра является именно Автомобиль, мы можем использовать виртуальную таблицу СрезПоследних. Это позволит нам отсечь всю историю продаж и получить только актуальных владельцев машин на текущий момент.
Шаг 2. Отфильтровать полученные данные по нужному типу. Нам нужно оставить только те записи, где вид значения соответствует хозяину, а само значение имеет тип справочника контрагентов.
Шаг 3. Найти дату последнего приобретения для каждого контрагента. Среди всех машин, которыми контрагенты владеют на текущий момент, мы находим максимальную дату (период) для каждого конкретного владельца.
Шаг 4. Получить итоговый автомобиль. Соединяем таблицу актуальных владельцев с таблицей максимальных дат по владельцу и периоду, чтобы вытащить конкретную ссылку на автомобиль.

Рассмотрим, как будет выглядеть оптимальный и быстрый запрос в 1С, соответствующий всем стандартам разработки.

Написание оптимального запроса

Ниже представлен готовый текст запроса, который решает поставленную задачу с учетом оптимизации производительности СУБД:


ВЫБРАТЬ
    АвтомобилиСрез.Период КАК Период,
    АвтомобилиСрез.Автомобиль КАК Автомобиль,
    ВЫРАЗИТЬ(АвтомобилиСрез.Значение КАК Справочник.Контрагенты) КАК Контрагент
ПОМЕСТИТЬ ВТ_ТекущиеВладельцы
ИЗ
    РегистрСведений.Автомобили.СрезПоследних(&Период, ВидЗначения = ЗНАЧЕНИЕ(Перечисление.ДополнительнаяИнформацияАвтомобилей.Хозяин)) КАК АвтомобилиСрез
ГДЕ
    АвтомобилиСрез.Значение ССЫЛКА Справочник.Контрагенты

ИНДЕКСИРОВАТЬ ПО
    Контрагент,
    Период
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_МаксДаты.Контрагент КАК Контрагент,
    МАКСИМУМ(ТекущиеВладельцы.Автомобиль) КАК Автомобиль
ИЗ
    (ВЫБРАТЬ
        ВТ_ТекущиеВладельцы.Контрагент КАК Контрагент,
        МАКСИМУМ(ВТ_ТекущиеВладельцы.Период) КАК Период
    ИЗ
        ВТ_ТекущиеВладельцы КАК ВТ_ТекущиеВладельцы
    
    СГРУППИРОВАТЬ ПО
        ВТ_ТекущиеВладельцы.Контрагент) КАК ВТ_МаксДаты
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_ТекущиеВладельцы КАК ТекущиеВладельцы
        ПО ВТ_МаксДаты.Контрагент = ТекущиеВладельцы.Контрагент
            И ВТ_МаксДаты.Период = ТекущиеВладельцы.Период

СГРУППИРОВАТЬ ПО
    ВТ_МаксДаты.Контрагент

Разбор работы запроса по шагам

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

Шаг 1: Получение актуального среза по автомобилям

В первом запросе пакета мы обращаемся к виртуальной таблице РегистрСведений.Автомобили.СрезПоследних. Это ключевой момент оптимизации. Мы передаем в параметры виртуальной таблицы условие отбора по виду значения: ВидЗначения = ЗНАЧЕНИЕ(Перечисление.ДополнительнаяИнформацияАвтомобилей.Хозяин). Это позволяет СУБД отсеять лишние записи (например, пробег или госномер, если они хранятся в этом же регистре) еще на этапе обращения к индексам виртуальной таблицы, не считывая лишние строки с диска.

Для обеспечения высокой скорости выполнения мы обязательно используем конструкцию ВЫРАЗИТЬ(АвтомобилиСрез.Значение КАК Справочник.Контрагенты). Поле Значение имеет составной тип. Если не выполнить типизацию, то SQL-сервер при формировании плана запроса выполнит неявные левые соединения (LEFT JOIN) со всеми таблицами, типы которых могут храниться в этом ресурсе. Использование ВЫРАЗИТЬ жестко указывает СУБД на то, что мы работаем только со справочником контрагентов, существенно упрощая план выполнения запроса.

Результат мы помещаем во временную таблицу ВТ_ТекущиеВладельцы и обязательно индексируем ее по полям Контрагент и Период. Индексация крайне важна, так как во втором запросе пакета мы будем производить соединение по этим полям.

Шаг 2: Вычисление последней сделки и защита от дублей при коллизии времени

Во втором запросе пакета мы решаем две задачи: находим самый свежий период владения для каждого контрагента и сопоставляем его с конкретной машиной. Для этого мы сначала делаем вложенный запрос к нашей временной таблице, группируя записи по контрагенту и вычисляя максимальный период: МАКСИМУМ(ВТ_ТекущиеВладельцы.Период).

Затем этот вложенный запрос мы соединяем с этой же временной таблицей ВТ_ТекущиеВладельцы по равенству контрагента и периода. На выходе мы получаем конкретную машину.

Важная деталь: обратите внимание на внешнюю группировку во втором запросе и поле МАКСИМУМ(ТекущиеВладельцы.Automobil). Зачем это нужно? Представим ситуацию, когда один и тот же контрагент умудрился приобрести два автомобиля абсолютно в одно и то же время (в пределах одной секунды или в пределах одного дня, если периодичность регистра настроена на день). В этом случае СУБД вернет две строки с одинаковым максимальным периодом, что приведет к дублированию строк в итоговом отчете. Использование агрегирующей функции МАКСИМУМ гарантирует, что даже в случае такой временной коллизии система вернет ровно один автомобиль (старшую ссылку по уникальному идентификатору GUID).

Архитектурные рекомендации по проектированию баз данных 1С

Решение, которое мы разобрали выше, является максимально эффективным в рамках текущей структуры метаданных. Однако сама структура регистра сведений в данном случае представляет собой известный антипаттерн проектирования, часто называемый EAV (Entity-Attribute-Value / Сущность-Атрибут-Значение).

В высоконагруженных системах использование универсальных регистров сведений, где в измерениях хранится тип информации (вид свойства), а в ресурсах — разнородные данные в составных типах, приводит к следующим проблемам:

Невозможность создания эффективных индексов СУБД по ресурсам. Поиск данных по владельцу (контрагенту) всегда будет приводить к полному сканированию индексов (Index Scan), так как поле Значение является ресурсом, а не измерением.
Сложность поддержки кода и написания простых запросов разработчиками. Вместо одного стандартного обращения к виртуальной таблице приходится писать громоздкие пакетные запросы с ручными срезами.
Падение общей производительности системы при увеличении объема записей в регистре.

Если в вашей системе часто возникает задача поиска автомобилей по контрагентам или получения списков актуального автопарка клиентов, настоятельно рекомендуется провести рефакторинг метаданных и создать специализированный периодический регистр сведений (например, ВладельцыАвтомобилей) со следующей структурой:

Измерение: Контрагент (тип СправочникСсылка.Контрагенты)
Ресурс: Автомобиль (тип СправочникСсылка.Автомобили)

При такой структуре решение нашей задачи свелось бы к простейшему обращению к виртуальной таблице среза последних в одну строку кода:


ВЫБРАТЬ
    ВладельцыАвтомобилейСрезПоследних.Контрагент КАК Контрагент,
    ВладельцыАвтомобилейСрезПоследних.Автомобиль КАК Автомобиль
ИЗ
    РегистрСведений.ВладельцыАвтомобилей.СрезПоследних(&Период, ) КАК ВладельцыАвтомобилейСрезПоследних

Такой запрос будет выполняться мгновенно на любом объеме данных, так как платформа 1С использует встроенные индексы СУБД, созданные по измерениям регистра сведений.