Как заполнить пустые строки в запросе 1С значениями из последующих строк (заполнение снизу вверх)

← На главную

В практике разработки на платформе 1С часто возникает задача распределения или дополнения данных в таблицах. Классический пример — это получение среза последних на каждую дату, когда пустые значения заполняются сверху вниз (от предыдущих к последующим). Однако иногда возникает зеркальная задача: необходимо заполнить пропуски в таблице «снизу вверх», то есть взять ближайшее заполненное значение из будущих строк и перенести его на предшествующие пустые строки. В теории баз данных этот класс задач называется Gaps and Islands, а конкретно этот метод — Backward Fill (обратное заполнение пропусков).

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

Строка 10 — значение пустое (должно заполниться значением «А» из строки 30).
Строка 30 — значение «А».
Строка 40 — значение пустое (должно заполниться значением «Б» из строки 60).
Строка 50 — значение пустое (должно заполниться значением «Б» из строки 60).
Строка 60 — значение «Б».
Строка 70 — значение пустое (должно заполниться значением «В» из строки 80).
Строка 80 — значение «В».

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

Решение 1. Классический запрос 1С через левое соединение (базовый алгоритм)

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

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

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


// Шаг 1: Готовим демонстрационный набор данных во временной таблице ВТ_ИсходныеДанные
ВЫБРАТЬ
    10 КАК НомерСтроки,
    "" КАК Буква
ПОМЕСТИТЬ ВТ_ИсходныеДанные
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 30, "А" ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 40, "" ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 50, "" ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 60, "Б" ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 70, "" ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ 80, "В"
;

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

// Шаг 3: Соединяем исходную таблицу с сопоставленными значениями и заполняем пропуски
ВЫБРАТЬ
    ВТ_Сопоставление.НомерСтроки КАК НомерСтроки,
    ЕСТЬNULL(ВТ_ПолучениеЗначения.Буква, ВТ_Сопоставление.ИсходнаяБуква) КАК Буква
ИЗ
    ВТ_Сопоставление КАК ВТ_Сопоставление
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИсходныеДанные КАК ВТ_ПолучениеЗначения
        ПО ВТ_Сопоставление.НомерСтрокиСБуквой = ВТ_ПолучениеЗначения.НомерСтроки
УПОРЯДОЧИТЬ ПО
    НомерСтроки
;

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

Проблема производительности базового алгоритма

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

Для СУБД такое соединение означает необходимость выполнения операции Nested Loops (вложенные циклы). Фактически СУБД вынуждена сопоставить каждую строку с каждой последующей строкой. Сложность такого алгоритма оценивается как O(N²). Если в таблице будет 100 000 строк, СУБД придется выполнить миллиарды сравнений в оперативной памяти или во временных таблицах на диске, что приведет к перегрузке tempdb.

Решение 2. Оптимизированный запрос 1С с разделением таблиц и индексами

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

Выясним по шагам, как это реализовать:

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

Рассмотрим оптимизированный текст запроса:


// 1. Помещаем исходные данные во временную таблицу и индексируем
ВЫБРАТЬ
    ИсходныеДанные.НомерСтроки КАК НомерСтроки,
    ИсходныеДанные.Буква КАК Буква
ПОМЕСТИТЬ ВТ_Исходные
ИЗ
    &ВходнойИсточник КАК ИсходныеДанные
ИНДЕКСИРОВАТЬ ПО
    НомерСтроки
;

// 2. Отбираем только пустые строки
ВЫБРАТЬ
    ВТ_Исходные.НомерСтроки КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Пустые
ИЗ
    ВТ_Исходные КАК ВТ_Исходные
ГДЕ
    ВТ_Исходные.Буква = ""
ИНДЕКСИРОВАТЬ ПО
    НомерСтроки
;

// 3. Отбираем только заполненные строки
ВЫБРАТЬ
    ВТ_Исходные.НомерСтроки КАК НомерСтроки,
    ВТ_Исходные.Буква КАК Буква
ПОМЕСТИТЬ ВТ_Заполненные
ИЗ
    ВТ_Исходные КАК ВТ_Исходные
ГДЕ
    ВТ_Исходные.Буква <> ""
ИНДЕКСИРОВАТЬ ПО
    НомерСтроки
;

// 4. Связываем пустые строки с минимальной следующей заполненной строкой
ВЫБРАТЬ
    ВТ_Пустые.НомерСтроки КАК НомерСтроки,
    МИНИМУМ(ВТ_Заполненные.НомерСтроки) КАК НомерСтрокиСБуквой
ПОМЕСТИТЬ ВТ_КартаСвязей
ИЗ
    ВТ_Пустые КАК ВТ_Пустые
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Заполненные КАК ВТ_Заполненные
        ПО ВТ_Пустые.НомерСтроки < ВТ_Заполненные.НомерСтроки
СГРУППИРОВАТЬ ПО
    ВТ_Пустые.НомерСтроки
ИНДЕКСИРОВАТЬ ПО
    НомерСтрокиСБуквой
;

// 5. Объединяем изначально заполненные строки и вычисленные пустые строки
ВЫБРАТЬ
    ВТ_КартаСвязей.НомерСтроки КАК НомерСтроки,
    ВТ_Заполненные.Буква КАК Буква
ИЗ
    ВТ_КартаСвязей КАК ВТ_КартаСвязей
        ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Заполненные КАК ВТ_Заполненные
        ПО ВТ_КартаСвязей.НомерСтрокиСБуквой = ВТ_Заполненные.НомерСтроки

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

ВЫБРАТЬ
    ВТ_Заполненные.НомерСтроки,
    ВТ_Заполненные.Буква
ИЗ
    ВТ_Заполненные КАК ВТ_Заполненные
;

Благодаря разделению наборов данных и индексации СУБД моментально обработает даже большие таблицы, так как операции сравнения будут производиться по сжатым подмножествам с использованием индексов B-Tree.

Решение 3. Использование возможностей Системы компоновки данных (СКД)

Если результатом вашей работы должен стать отчет, то перегружать СУБД сложными соединениями в запросе вообще не нужно. В платформе 1С реализован мощный механизм вычислений на уровне процессора компоновки данных. Мы можем использовать аналог оконных функций SQL, доступный в языке выражений СКД.

Для реализации обратного заполнения выполним следующие действия:

В запросе схемы компоновки данных выберем данные «как есть» — без соединений и группировок.
Перейдем на вкладку Вычисляемые поля и добавим поле, например, БукваЗаполненная.
Перейдем на вкладку Ресурсы, добавим наше поле в ресурсы и в качестве выражения укажем специальную функцию ВычислитьВыражение.

Выражение ресурса для заполнения снизу вверх будет выглядеть следующим образом:


Выбор 
    Когда Буква = "" 
        Тогда ВычислитьВыражение("Максимум(Буква)", , , "Текущая", "Последняя") 
    Иначе Буква 
Конец

Разберем, как работает эта функция. Конструкция указывает СКД проанализировать группировку строк от текущей позиции ("Текущая") до самого конца выборки ("Последняя") и вернуть максимальное (или минимальное, если это строковые значения одинакового приоритета) значение поля Буква. Расчет происходит в памяти локального компьютера при формировании отчета, что исключает нагрузку на сервер баз данных.

Решение 4. Прямой запрос к СУБД (Оконные функции SQL)

В случаях, когда интеграция происходит через внешние источники данных или вы работаете напрямую с СУБД (например, MS SQL или PostgreSQL), задачу «Backward Fill» можно решить за один проход по таблице с помощью стандартных оконных функций. В платформе 1С (за исключением новых бета-версий) оконные функции напрямую в запросах не поддерживаются, но данный пример полезен для понимания теории баз данных.

Посмотрим на классический SQL-запрос с использованием конструкции OVER:


SELECT 
    НомерСтроки,
    COALESCE(
        NULLIF(Буква, ''), 
        MIN(NULLIF(Буква, '')) OVER (
            ORDER BY НомерСтроки 
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        )
    ) AS Буква
FROM ВТ_ИсходныеДанные;

В данном примере функция NULLIF(Буква, '') превращает пустые строки в значения NULL. Оконная функция MIN(...) OVER (...) считывает данные в рамке (frame) от текущей строки (CURRENT ROW) до самого конца таблицы (UNBOUNDED FOLLOWING), выбирая первое непустое значение. Этот запрос выполняется СУБД крайне эффективно со сложностью O(N log N) за один физический проход по индексу.