При разработке конфигураций и отчетов на платформе 1С:Предприятие программисты часто сталкиваются с необходимостью получить не просто актуальное состояние данных на текущий момент (что легко делается стандартным срезом последних), а историю изменений. Классический пример такой задачи — вывести в одной строке отчета или обработать в алгоритме проведения документа сразу два последних значения периодического регистра сведений. Например, нам нужно получить текущий класс товара и его предыдущий класс, актуальную фамилию физического лица и его девичью (предыдущую) фамилию, или же текущий курс валюты в сравнении с курсом за предыдущий день.
В этой статье мы подробно разберем несколько практических способов решения этой задачи с помощью языка запросов 1С. Мы проанализируем различные подходы — от простых соединений виртуальных таблиц до сложных коррелированных запросов и высокопроизводительных методов с использованием временных таблиц, а также разберем типичные ошибки разработчиков, из-за которых запросы могут работать некорректно или сильно замедлять работу базы данных.
Рассмотрим первый, наиболее популярный среди разработчиков метод. Его суть заключается в том, что мы соединяем две виртуальные таблицы СрезПоследних одного и того же регистра сведений. При этом из второй таблицы (которая должна вернуть предпоследнее значение) мы исключаем записи, которые являются абсолютно последними на данный момент.
Для реализации этого подхода мы используем конструкцию сравнения кортежей в предложении ГДЕ параметров виртуальной таблицы. Давайте разберем готовый шаблон такого запроса на примере регистра сведений ПериодическиеПараметрыТовара, где для каждого товара хранится история изменения его класса:
ВЫБРАТЬ
ПоследниеПоказатели.Товар КАК Товар,
ПоследниеПоказатели.Период КАК ПоследнийПериод,
ПоследниеПоказатели.КлассТовара КАК ПоследнийКлассТовара,
ПредПоследниеПоказатели.Период КАК ПредПоследнийПериод,
ПредПоследниеПоказатели.КлассТовара КАК ПредПоследнийКлассТовара
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара.СрезПоследних КАК ПоследниеПоказатели
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПериодическиеПараметрыТовара.СрезПоследних(
,
НЕ (Товар, Период) В
(ВЫБРАТЬ
ПериодическиеПараметрыТовара.Товар,
ПериодическиеПараметрыТовара.Период
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара.СрезПоследних КАК ПериодическиеПараметрыТовара)
) КАК ПредПоследниеПоказатели
ПО ПоследниеПоказатели.Товар = ПредПоследниеПоказатели.Товар
Важный технический нюанс: Обратите пристальное внимание на условие исключения НЕ (Товар, Период) В (...). Очень распространенная ошибка начинающих разработчиков 1С состоит в том, что они нарушают порядок полей при сравнении кортежей. Если написать НЕ (Период, Товар) В (ВЫБРАТЬ Товар, Период...), то запрос выполнится без синтаксических ошибок, но СУБД вернет неверный результат (будут выдаваться одинаковые последние данные для обеих колонок или пустые значения). СУБД сравнивает поля строго по их позиции в списке: первое поле сопоставляется с первым полем выборки подзапроса, второе — со вторым. Поэтому строго следите за тем, чтобы порядок измерений и реквизитов совпадал на 100%.
Этот метод показывает отличную скорость работы на средних объемах данных (до нескольких десятков тысяч записей). В реальных тестах обработка таблицы объемом 16 000 строк занимает всего около 0.8 секунды, что является прекрасным результатом.
Вторым способом получения последних записей является использование коррелированного (взаимосвязанного) подзапроса. Этот метод часто применяется, если нам нужно получить не просто две последние записи в одну строку, а произвольное количество последних записей (например, 2, 3 или 5 последних курсов валют списком строк) для последующей обработки.
Проанализируем пример такого запроса для регистра КурсыВалют. Здесь мы выбираем физические записи регистра и фильтруем их по условию вхождения периода во временной интервал последних двух записей для конкретной валюты:
ВЫБРАТЬ
Курсы.Валюта КАК Валюта,
Курсы.Период КАК Период,
Курсы.Курс КАК Курс
ИЗ
РегистрСведений.КурсыВалют КАК Курсы
ГДЕ
Курсы.Период В
(ВЫБРАТЬ ПЕРВЫЕ 2
К.Период
ИЗ
РегистрСведений.КурсыВалют КАК К
ГДЕ
К.Валюта = Курсы.Валюта
УПОРЯДОЧИТЬ ПО
К.Период УБЫВ)
Давайте выясним, как работает данный механизм. Для каждой строки внешней таблицы Курсы платформа 1С заставляет СУБД выполнять подзапрос, который выбирает ровно две самые свежие даты (благодаря конструкции ПЕРВЫЕ 2 и упорядочиванию по убыванию периода) по текущей валюте. Если период текущей строки совпадает с одной из этих двух дат, запись попадает в итоговый результат.
Минусы коррелированных запросов: Несмотря на свою лаконичность и гибкость (мы можем легко поменять ПЕРВЫЕ 2 на ПЕРВЫЕ 5), данный подход обладает серьезной проблемой производительности на больших базах данных. Так как подзапрос зависит от полей внешнего запроса (К.Валюта = Курсы.Валюта), СУБД не может выполнить его один раз и закешировать. Ей приходится строить планы запроса типа "вложенный цикл" (Nested Loops), что на миллионных таблицах приводит к катастрофическому падению скорости выполнения.
Если ваша база данных содержит сотни тысяч или миллионы записей по товарам или контрагентам, использование виртуальных таблиц СрезПоследних со сложными условиями соединения напрямую "в лоб" может привести к зависанию сервера СУБД (особенно под управлением PostgreSQL). Дело в том, что виртуальная таблица в 1С сама по себе генерирует тяжелый SQL-запрос с подзапросами, и накладывание дополнительных условий `НЕ В` поверх нее лишает оптимизатор СУБД возможности использовать индексы.
Для оптимизации этой задачи мы применим профессиональный подход: разобьем один сложный запрос на пакет простых шагов с использованием проиндексированных Временных Таблиц (ВТ). Это гарантирует стабильную и быструю работу на СУБД MS SQL и PostgreSQL.
Рассмотрим этот оптимизированный алгоритм по шагам:
// Шаг 1. Получаем абсолютно последние записи и помещаем их в индексированную ВТ
ВЫБРАТЬ
Срез.Товар КАК Товар,
Срез.Период КАК Период,
Срез.КлассТовара КАК КлассТовара
ПОМЕСТИТЬ ВТ_ПоследниеЗаписи
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара.СрезПоследних КАК Срез
ИНДЕКСИРОВАТЬ ПО
Товар,
Период
;
// Шаг 2. Получаем предпоследние записи, исключая данные из первой ВТ по индексу
ВЫБРАТЬ
СрезПред.Товар КАК Товар,
СрезПред.Период КАК Период,
СрезПред.КлассТовара КАК КлассТовара
ПОМЕСТИТЬ ВТ_ПредпоследниеЗаписи
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара.СрезПоследних(
,
НЕ (Товар, Период) В
(ВЫБРАТЬ
ВТ.Товар,
ВТ.Период
ИЗ
ВТ_ПоследниеЗаписи КАК ВТ)
) КАК СрезПред
ИНДЕКСИРОВАТЬ ПО
Товар
;
// Шаг 3. Соединяем две легкие временные таблицы по индексу
ВЫБРАТЬ
Последние.Товар КАК Товар,
Последние.Период КАК ПоследнийПериод,
Последние.КлассТовара КАК ПоследнийКлассТовара,
ЕСТЬNULL(Предпоследние.Период, ДАТАВРЕМЯ(1, 1, 1)) КАК ПредпоследнийПериод,
ЕСТЬNULL(Предпоследние.КлассТовара, "") КАК ПредпоследнийКлассТовара
ИЗ
ВТ_ПоследниеЗаписи КАК Последние
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПредпоследниеЗаписи КАК Предпоследние
ПО Последние.Товар = Предпоследние.Товар
Почему этот код будет работать в разы быстрее на крупных базах? Во-первых, первая таблица ВТ_ПоследниеЗаписи создается очень быстро, так как это простой срез последних. Во-вторых, мы явно индексируем ее по полям Товар, Период. При создании второй временной таблицы СУБД выполняет операцию исключения по уже готовому, упорядоченному индексу временной таблицы, что исключает операцию полного сканирования (Table Scan) основной физической таблицы регистра. В конце мы соединяем две небольшие подготовленные выборки.
Существует еще один изящный чисто математический способ получить $N$ последних записей из физической таблицы регистра сведений без использования механизма виртуальных таблиц СрезПоследних. Этот метод базируется на левом соединении таблицы самой с собой по условию «Период меньше другого периода».
Посмотрим на пример реализации данного алгоритма:
ВЫБРАТЬ
Т1.Товар КАК Товар,
Т1.Период КАК Период,
Т1.КлассТовара КАК КлассТовара
ПОМЕСТИТЬ ВТ_ДвеПоследниеЗаписи
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ПериодическиеПараметрыТовара КАК Т2
ПО Т1.Товар = Т2.Товар
И Т1.Период < Т2.Период
СГРУППИРОВАТЬ ПО
Т1.Товар,
Т1.Период,
Т1.КлассТовара
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) < 2
Проанализируем логику этого решения. Мы берем физическую таблицу регистра Т1 и присоединяем к ней ту же самую таблицу Т2 по тому же товару, но с условием, что период в Т2 строго больше периода в Т1.
Т2 не найдется ни одной строки с более поздней датой. Количество различных периодов в Т2 будет равно 0.Т2 найдется ровно одна запись (самая последняя). Количество различных периодов в Т2 будет равно 1.Т2 будет равно уже 2.
Условие в секции ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Период) < 2 отсекает все записи, у которых есть 2 или более поздних периода, оставляя в результате ровно две самые свежие строки для каждого товара. Данный метод невероятно удобен тем, что легко масштабируется: если вам потребуется выбрать 3 или 5 последних записей, достаточно просто изменить условие в ИМЕЮЩИЕ на < 3 или < 5 соответственно.
Если ваша конечная цель — вывести отчет со списком последних двух записей для пользователей, то перегружать текст SQL-запроса сложными группировками и соединениями нет никакой практической необходимости. Платформа 1С предоставляет мощный встроенный инструмент фильтрации данных на уровне СКД.
Разберем по шагам, как решить эту задачу в схеме компоновки данных:
ВЫБРАТЬ
Параметры.Товар КАК Товар,
Параметры.Период КАК Период,
Параметры.КлассТовара КАК КлассТовара
ИЗ
РегистрСведений.ПериодическиеПараметрыТовара КАК Параметры
УПОРЯДОЧИТЬ ПО
Товар,
Период УБЫВ
Товар.Товар добавьте подчиненную группировку Детальные записи (или детальную таблицу).Детальные записи, перейдите на вкладку «Отбор» для этой конкретной группировки.СистемныеПоля.НомерВГруппе (в некоторых версиях платформы оно называется НомерСтроки).При формировании отчета система компоновки данных автоматически сгруппирует данные по каждому товару, пронумерует детальные строки внутри каждого товара по порядку (а так как у нас задана сортировка по убыванию периода, первыми строками пойдут самые свежие записи) и выведет на экран ровно первые две строки. Этот метод является наиболее предпочтительным для отчетов, так как он не нагружает СУБД сложными вычислениями и использует штатные оптимизированные механизмы СКД.
При работе с периодическими регистрами сведений всегда существует вероятность возникновения коллизии времени. Это ситуация, когда в один и тот же период (вплоть до секунды) по одному и тому же товару (или физическому лицу) в базу данных вносится несколько разных записей. В таком случае сортировка по полю Период перестает быть однозначной, и СУБД может возвращать непредсказуемый результат.
Как же решить эту проблему?
Период во всех операциях соединения и сравнения используйте связку Период и Регистратор.МоментВремени. Поле МоментВремени гарантирует строгий хронологический порядок проведения документов в системе 1С, даже если они проведены в одну секунду.НомерСтроки (он автоматически создается платформой, если в один период записывается набор данных из нескольких строк) для дополнительного упорядочивания в запросах.Мы подробно проанализировали четыре абсолютно разных подхода к получению двух последних записей периодического регистра сведений в 1С. Подведем итоги и определим, какой метод когда использовать:
НЕ В!