Почему запрос в 1С выводит всю историю вместо последних записей регистра сведений и как это исправить

← На главную

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

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

Разбор ошибки: почему группировка выводит все записи?

Проанализируем исходный запрос, с которым столкнулся автор темы на форуме:


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

Выясним причину, почему СУБД возвращает все строки истории изменений цен (например, и 1200 рублей за январь, и 1300 рублей за февраль), несмотря на использование функции МАКСИМУМ для поля Период. Дело в фундаментальных правилах работы оператора СГРУППИРОВАТЬ ПО в языке SQL и платформе 1С.

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

Поле Цена: Поскольку цена товара меняется во времени (в январе — 1200, в феврале — 1300), эти две записи имеют разные значения в поле Цена. СУБД не может объединить их в одну строку, так как значения группировочного поля различаются. В итоге создаются две независимые группы, для каждой из которых вычисляется свой «максимальный» период (в данном случае он просто совпадает с периодом самой записи).
Поле НомерСтроки: Это системное поле, которое уникально для каждой записи в пределах одной секунды (или даже всей таблицы при определенных условиях записи). Добавление этого поля в секцию группировки окончательно убивает саму идею свертки строк — каждая запись физической таблицы становится уникальной группой.

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

Правильное решение №1: Использование виртуальной таблицы «СрезПоследних»

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

Перепишем запрос автора темы, используя этот механизм:


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

Рассмотрим подробнее преимущества и особенности этого запроса:

Нам больше не требуется использовать громоздкую секцию СГРУППИРОВАТЬ ПО и вычислять максимум периода вручную.
Платформа самостоятельно отберет по одной последней записи для каждой уникальной комбинации измерений (в данном случае — Номенклатура и ВидЦены).
Мы избавились от поля НомерСтроки, которое мешало свертке данных.

Важнейшее правило производительности: Параметры виртуальной таблицы

Обратите внимание на то, куда именно помещено условие отбора по виду цен. В исходном некорректном запросе автора условие находилось в секции ГДЕ. При переходе на виртуальную таблицу крайне важно перенести все отборы во второй параметр виртуальной таблицы СрезПоследних.

Проанализируем разницу в поведении СУБД при этих двух подходах:

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

Правильное решение №2: Ручная эмуляция «Среза последних» на больших базах данных

Несмотря на удобство виртуальной таблицы СрезПоследних, на крупных базах данных (особенно под управлением СУБД PostgreSQL) она может работать медленно. Это связано с тем, что под капотом платформа 1С генерирует сложный SQL-запрос с вложенными соединениями, который оптимизатор СУБД не всегда может обработать по оптимальному плану.

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

Шаг 1. Создаем временную таблицу, в которую выбираем только уникальные измерения регистра и максимальный период на нужную дату. Здесь группировка будет работать максимально быстро, так как мы группируем только по индексированным полям-измерениям.

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

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


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

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

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

Сложный случай: Срез последних на каждую дату списка документов

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

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

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

Давайте посмотрим на практический пример реализации такого запроса:


// Шаг 1: Собираем уникальные пары Документ-Номенклатура-ДатаДокумента
ВЫБРАТЬ РАЗЛИЧНЫЕ
    РасходнаяНакладнаяСодержимое.Ссылка КАК ДокументСсылка,
    РасходнаяНакладнаяСодержимое.Ссылка.Дата КАК ДатаДокумента,
    РасходнаяНакладнаяСодержимое.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_ТоварыДокументов
ИЗ
    Документ.РасходнаяНакладная.Товары КАК РасходнаяНакладнаяСодержимое
ГДЕ
    РасходнаяНакладнаяСодержимое.Ссылка В(&СписокДокументов)
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2 и 3: Находим максимальную дату цены, которая была установлена ДО даты документа
ВЫБРАТЬ
    ВТ_ТоварыДокументов.ДокументСсылка КАК ДокументСсылка,
    ВТ_ТоварыДокументов.Номенклатура КАК Номенклатура,
    МАКСИМУМ(ЦеныНоменклатуры.Период) КАК ПериодЦены
ПОМЕСТИТЬ ВТ_ДатыЦенНаДокументы
ИЗ
    ВТ_ТоварыДокументов КАК ВТ_ТоварыДокументов
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
        ПО ВТ_ТоварыДокументов.Номенклатура = ЦеныНоменклатуры.Номенклатура
        И ЦеныНоменклатуры.Период <= ВТ_ТоварыДокументов.ДатаДокумента
        И ЦеныНоменклатуры.ВидЦены = &ВидЦены
СГРУППИРОВАТЬ ПО
    ВТ_ТоварыДокументов.ДокументСсылка,
    ВТ_ТоварыДокументов.Номенклатура
;

////////////////////////////////////////////////////////////////////////////////
// Шаг 4: Получаем саму цену по найденной максимальной дате периода
ВЫБРАТЬ
    ВТ_ДатыЦенНаДокументы.ДокументСсылка КАК ДокументСсылка,
    ВТ_ДатыЦенНаДокументы.Номенклатура КАК Номенклатура,
    ВТ_ДатыЦенНаДокументы.ПериодЦены КАК ПериодУстановкиЦены,
    ЕСТЬNULL(ФизическийРегистрЦен.Цена, 0) КАК Цена
ИЗ
    ВТ_ДатыЦенНаДокументы КАК ВТ_ДатыЦенНаДокументы
    ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ФизическийРегистрЦен
        ПО ВТ_ДатыЦенНаДокументы.Номенклатура = ФизическийРегистрЦен.Номенклатура
        И ВТ_ДатыЦенНаДокументы.ПериодЦены = ФизическийРегистрЦен.Период
        И ФизическийРегистрЦен.ВидЦены = &ВидЦены

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

Резюме

Подведем итоги нашего разбора:

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