Как запросом получить последние цены из регистра сведений «Цены номенклатуры поставщиков» без дублирования строк?

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

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

Виртуальная таблица СрезПоследних работает по жестко заданному алгоритму СУБД: она возвращает последние записи в разрезе уникальной комбинации всех измерений регистра. Проанализируем типичную структуру регистра ЦеныНоменклатурыПоставщиков. В нем, помимо измерения Номенклатура, практически всегда присутствуют другие измерения, например: Поставщик, ВидЦены, Характеристика или Валюта.

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

Способ 1. Получение последней цены для одного товара (оптимально для единичного выбора)

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

Рассмотрим пример такого запроса:


ВЫБРАТЬ ПЕРВЫЕ 1
    Цены.Цена КАК Цена,
    Цены.Номенклатура КАК Номенклатура,
    Цены.Период КАК ДатаЦены,
    Цены.Валюта КАК Валюта
ИЗ
    РегистрСведений.ЦеныНоменклатурыПоставщиков КАК Цены
ГДЕ
    Цены.Номенклатура = &Номенклатура
УПОРЯДОЧИТЬ ПО
    Цены.Period УБЫВ

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

Способ 2. Получение цен по списку номенклатуры с помощью временных таблиц

Если нам необходимо получить последние цены для списка товаров или для всего справочника, использовать первый способ в цикле категорически запрещено стандартами разработки 1С (это приведет к критическому падению производительности из-за лавинообразного роста запросов к СУБД).

В сообщении 8 на форуме было предложено использовать вложенный запрос с получением максимальной даты и его последующим внутренним соединением с физической таблицей регистра. Логически этот подход абсолютно верен, но с точки зрения производительности СУБД использование вложенных запросов в соединениях считается «антипаттерном». Оптимизаторы MS SQL Server или PostgreSQL часто не могут построить эффективный план для соединений с подзапросами, что приводит к полному сканированию таблиц (Full Table Scan) и зависаниям на больших базах данных.

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

Сначала мы выберем товары и их максимальные даты (периоды) во временную таблицу и проиндексируем ее по ключевым полям связи.
Затем мы соединим полученную временную таблицу с физической таблицей регистра сведений для получения цен и валют на эти даты.

Посмотрим на пример реализации этого пакета запросов:


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

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    МаксПериод
;

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

Благодаря явной индексации временной таблицы ВТ_МаксДаты, планировщик СУБД выполнит операцию соединения (Nested Loops или Hash Match) за доли секунды, используя индексный поиск (Index Seek) по физической таблице регистра сведений.

Способ 3. Оптимизация выбора максимальных дат через СрезПоследних

Рассмотрим ситуацию, когда история изменения цен в вашей системе ведется годами, и физическая таблица регистра содержит миллионы записей. Поиск максимальной даты МАКСИМУМ(Период) по всей физической таблице может занять значительное время.

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

В таком случае первый запрос пакета будет выглядеть следующим образом:


ВЫБРАТЬ
    ЦеныСрез.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ЦеныСрез.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_МаксДаты
ИЗ
    РегистрСведений.ЦеныНоменклатурыПоставщиков.СрезПоследних(&ТекДата, ) КАК ЦеныСрез
ГДЕ
    ЦеныСрез.Номенклатура В (&СписокНоменклатуры)
СГРУППИРОВАТЬ ПО
    ЦеныСрез.Номенклатура

ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    МаксПериод

Такая модификация позволяет значительно разгрузить процессор сервера и дисковую подсистему при работе с исторически большими базами данных.

Важный нюанс: исключение дублирования при совпадении периодов (коллизия времени)

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

Если в одну и ту же секунду для одной и той же номенклатуры были внесены две записи с разными ценами (например, от разных поставщиков или разными документами), то максимальный период для них совпадет. В результате внутреннее соединение во втором запросе пакета вернет обе эти записи, что приведет к появлению дублей строк в конечном отчете.

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


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

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

← На главную