Как в 1С быстро получить последние непустые значения из нескольких колонок большой таблицы?

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

Исходная задача: есть таблица документов с полями Дата, value1, value2, value3. Многие ячейки в полях value могут быть пустыми. Необходимо получить одну строку с последними по дате непустыми значениями для каждого из этих полей.

Неоптимальный, но прямолинейный подход

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

Посмотрим на пример такого запроса. Он состоит из нескольких шагов:

1. Шаг 1. С помощью ОБЪЕДИНИТЬ ВСЕ создаем временную таблицу, где каждая строка представляет собой пару "Имя поля - Значение", отфильтровывая пустые значения.
2. Шаг 2. Группируем полученную таблицу по имени поля, чтобы найти максимальную (последнюю) дату для каждого.
3. Шаг 3. Соединяем результат с таблицей из шага 1 по имени поля и максимальной дате, чтобы найти само значение.
4. Шаг 4. "Сворачиваем" результат обратно в одну строку.

Вот как это может выглядеть в коде:

ВЫБРАТЬ
    АвансовыйОтчет.Дата КАК Дата,
    АвансовыйОтчет.Кратность КАК Значение1,
    АвансовыйОтчет.Курс КАК Значение2,
    АвансовыйОтчет.СуммаДокумента КАК Значение3
ПОМЕСТИТЬ втДанные
ИЗ
    Документ.АвансовыйОтчет КАК АвансовыйОтчет
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    взНепустые.Дата КАК Дата,
    взНепустые.ИмяПоля КАК ИмяПоля,
    взНепустые.ЗначениеПоля КАК ЗначениеПоля
ПОМЕСТИТЬ втНепустые
ИЗ
    (ВЫБРАТЬ
        втДанные.Дата КАК Дата,
        "Первое" КАК ИмяПоля,
        втДанные.Значение1 КАК ЗначениеПоля
    ИЗ
        втДанные КАК втДанные
    ГДЕ
        втДанные.Значение1 <> НЕОПРЕДЕЛЕНО
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        втДанные.Дата,
        "Второе",
        втДанные.Значение2
    ИЗ
        втДанные КАК втДанные
    ГДЕ
        втДанные.Значение2 <> НЕОПРЕДЕЛЕНО
    
    ОБЪЕДИНИТЬ ВСЕ
    
    ВЫБРАТЬ
        втДанные.Дата,
        "Третье",
        втДанные.Значение3
    ИЗ
        втДанные КАК втДанные
    ГДЕ
        втДанные.Значение3 <> НЕОПРЕДЕЛЕНО) КАК взНепустые
ИНДЕКСИРОВАТЬ ПО
    Дата,
    ИмяПоля
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    взПоследниеЗначения.ИмяПоля КАК ИмяПоля,
    взПоследниеЗначения.ЗначениеПоля КАК ЗначениеПоля
ПОМЕСТИТЬ втПоследниеЗначения
ИЗ
    (ВЫБРАТЬ
        втНепустые.ИмяПоля КАК ИмяПоля,
        МАКСИМУМ(втНепустые.ЗначениеПоля) КАК ЗначениеПоля
    ИЗ
        (ВЫБРАТЬ
            втНепустые.ИмяПоля КАК ИмяПоля,
            МАКСИМУМ(втНепустые.Дата) КАК МаксДата
        ИЗ
            втНепустые КАК втНепустые
        
        СГРУППИРОВАТЬ ПО
            втНепустые.ИмяПоля) КАК взМаксДатаЗначений
            ВНУТРЕННЕЕ СОЕДИНЕНИЕ втНепустые КАК втНепустые
            ПО взМаксДатаЗначений.ИмяПоля = втНепустые.ИмяПоля
                И взМаксДатаЗначений.МаксДата = втНепустые.Дата
    
    СГРУППИРОВАТЬ ПО
        втНепустые.ИмяПоля) КАК взПоследниеЗначения
;
////////////////////////////////////////////////////////////////////////////////
// Финальная выборка и "сворачивание" в одну строку (здесь опущено для краткости)

Почему это медленно? Как справедливо отмечено в обсуждении, такой подход катастрофичен для производительности на больших таблицах:

• Происходит многократное полное сканирование исходной таблицы (три раза в секции ОБЪЕДИНИТЬ ВСЕ).
• Условие <> НЕОПРЕДЕЛЕНО заставляет СУБД проверять каждую из миллионов строк.
• Создается огромная промежуточная таблица втНепустые, которая может занимать гигабайты памяти.
• Последующие группировки и соединения над этой огромной таблицей также требуют значительных ресурсов.

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

Оптимальное решение: `ВЫБРАТЬ ПЕРВЫЕ 1` для каждой колонки

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

Ключевая идея — для каждого поля создать свой маленький, но очень быстрый запрос, который найдет ровно одно нужное значение. Для этого используем конструкцию ВЫБРАТЬ ПЕРВЫЕ 1 в связке с сортировкой по убыванию даты.

Разберем по шагам структуру такого подзапроса для одного поля:

1. ВЫБРАТЬ ПЕРВЫЕ 1 ... — эта инструкция говорит СУБД прекратить поиск сразу после нахождения первой же записи, удовлетворяющей условиям.
2. УПОРЯДОЧИТЬ ПО МоментВремени УБЫВ — это самый важный элемент. Мы сортируем записи по дате в обратном порядке. Таким образом, самая "свежая" запись оказывается наверху. Для документов надежнее использовать поле МоментВремени, так как оно уникально в пределах секунды.
3. ГДЕ ИскомоеПоле <> "" — отфильтровываем пустые значения.

Может показаться, что условие неравенства (<>) приведет к полному сканированию таблицы, но это не так. Благодаря связке ПЕРВЫЕ 1 и УПОРЯДОЧИТЬ ПО ... УБЫВ по проиндексированному полю (а Дата и МоментВремени в документах всегда индексированы), оптимизатор запросов СУБД действует очень умно: он использует индекс, чтобы сразу "перепрыгнуть" в конец таблицы, и начинает читать записи в обратном порядке. Как только он находит первую запись, где поле не пустое, он тут же возвращает результат и завершает работу. Это происходит практически мгновенно, даже на миллионах записей.

Практический тест на базе с 1.5 млн записей показал время выполнения в 5 миллисекунд.

Посмотрим на итоговый запрос, который объединяет несколько таких подзапросов для получения всех нужных значений:

ВЫБРАТЬ ПЕРВЫЕ 1
    "Комментарий" КАК НаименованиеПоля,
    ВЫРАЗИТЬ(ПередачаТоваров.Комментарий КАК СТРОКА(200)) КАК ЗначениеПоля
ИЗ
    Документ.ПередачаТоваров КАК ПередачаТоваров
ГДЕ
    (ВЫРАЗИТЬ(ПередачаТоваров.Комментарий КАК СТРОКА(200))) <> ""
УПОРЯДОЧИТЬ ПО
    ПередачаТоваров.МоментВремени УБЫВ

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ ПЕРВЫЕ 1
    "Адрес",
    ВЫРАЗИТЬ(ПередачаТоваров.АдресДоставки КАК СТРОКА(100))
ИЗ
    Документ.ПередачаТоваров КАК ПередачаТоваров
ГДЕ
    (ВЫРАЗИТЬ(ПередачаТоваров.АдресДоставки КАК СТРОКА(100))) <> ""
УПОРЯДОЧИТЬ ПО
    ПередачаТоваров.МоментВремени УБЫВ

Этот запрос вернет таблицу из двух строк (по одной на каждое поле). Далее результат можно легко обработать в коде 1С, чтобы получить нужный срез данных. Этот метод является наиболее производительным и рекомендуемым для решения данной задачи средствами языка запросов.

Архитектурное решение: Регистр сведений

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

Если получение последних значений — это частая и критичная по времени операция, рассмотрите следующий подход:

1. Создайте периодический (с периодичностью, например, "По позиции регистратора") регистр сведений.
2. В измерениях регистра укажите разрезы, в рамках которых нужно хранить значения (например, Организация, Контрагент).
3. В ресурсах создайте поля, соответствующие вашим value1, value2 и т.д.
4. При проведении документа, который изменяет эти значения, добавляйте запись в этот регистр.

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