В практике разработки на платформе 1С:Предприятие часто возникает задача, когда необходимо обработать таблицу данных и заполнить пустые (или нулевые) значения в определенной колонке предыдущими заполненными значениями из этой же колонки. Такая потребность регулярно встречается при формировании прайс-листов, анализе динамики цен, расчете остатков или выводе периодической отчетности.
В этой статье мы подробно и по шагам разберем несколько способов решения данной задачи: классический запрос с самосоединением, высокопроизводительный обход на встроенном языке 1С, использование механизмов Системы компоновки данных (СКД), а также рассмотрим частый практический случай — срез последних цен или остатков на каждую дату периода.
Представим, что у нас есть исходная таблица значений со следующей структурой:
| Код | Цена |
|---|---|
| 0001 | 10 |
| 0002 | 0 (пусто) |
| 0003 | 14 |
| 0004 | 15 |
| 0005 | 0 (пусто) |
| 0006 | 0 (пусто) |
| 0007 | 11 |
| 0008 | 0 (пусто) |
| 0009 | 0 (пусто) |
| 0010 | 19 |
Наша цель — получить результирующую таблицу, в которой все нулевые значения колонки Цена будут заменены последними предшествующими им ненулевыми значениями. То есть для кода "0002" цена должна стать равной 10, для кодов "0005" и "0006" — 15, а для кодов "0008" и "0009" — 11.
Рассмотрим вариант решения, предложенный в обсуждении. Основной принцип работы запроса в 1С заключается в обработке всей таблицы целиком, а не в построчном переборе. Чтобы сопоставить строку с ее предыдущими значениями, нам потребуется соединить таблицу саму с собой.
Выясним логику работы этого алгоритма. Мы связываем исходную таблицу с ее же копией по условию неравенства кодов (или дат): ТекущийКод > ПредыдущийКод. При этом мы накладываем условие, что в копии таблицы цена не должна быть нулевой. После этого мы группируем результат и находим максимальный код среди всех предыдущих строк с заполненной ценой. На последнем этапе мы соединяем полученную схему с исходными данными еще раз, чтобы вытащить значение цены для этого максимального предыдущего кода.
Разберем по шагам текст запроса, реализующий этот алгоритм:
// Шаг 1: Подготавливаем исходный временный пакет данных (для примера)
ВЫБРАТЬ "0001" КАК Код, 10 КАК Цена ПОМЕСТИТЬ вт ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0002", 0 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0003", 14 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0004", 15 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0005", 0 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0006", 0 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0007", 11 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0008", 0 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0009", 0 ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ "0010", 19 ;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Для каждой строки находим код ближайшей предыдущей строки с заполненной ценой
ВЫБРАТЬ
вт.Код КАК Код,
вт.Цена КАК Цена,
МАКСИМУМ(вт1.Код) КАК КодПред
ПОМЕСТИТЬ данные
ИЗ
вт КАК вт
ЛЕВОЕ СОЕДИНЕНИЕ вт КАК вт1 ПО вт.Код > вт1.Код И (вт1.Цена <> 0)
СГРУППИРОВАТЬ ПО
вт.Код,
вт.Цена
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 3: Соединяем таблицу саму с собой по найденному коду и подставляем цену
ВЫБРАТЬ
данные.Код КАК Код,
ВЫБОР
КОГДА данные.Цена = 0 ТОГДА ЕСТЬNULL(вт.Цена, 0)
ИНАЧЕ данные.Цена
КОНЕЦ КАК Цена
ИЗ
данные КАК данные
ЛЕВОЕ СОЕДИНЕНИЕ вт КАК вт ПО данные.КодПред = вт.Код
УПОРЯДОЧИТЬ ПО
Код
Проанализируем производительность этого запроса. Этот метод отлично работает на демонстрационном примере из 10 строк. Однако при работе с реальными информационными базами таится серьезная скрытая угроза. Соединение по условию неравенства (вт.Код > вт1.Код) относится к классу Theta-join. На уровне СУБД (MS SQL, PostgreSQL) такая операция близка к декартову произведению (cross join).
Это означает, что сложность алгоритма является квадратичной — O(N²). Каждая строка сопоставляется со всеми предыдущими строками. Если в вашей таблице будет 10 000 строк, количество операций сравнения в СУБД составит миллионы, а при 100 000 строк запрос гарантированно вызовет критическую перегрузку процессора сервера, переполнение базы временных таблиц (tempdb) и завершится по таймауту. Поэтому использовать этот способ для больших объемов данных категорически не рекомендуется.
Если архитектура вашего решения позволяет выполнить обработку данных после получения из СУБД (например, в модуле обработки, отчета или документа), то самым правильным и быстрым решением будет постобработка выборки на встроенном языке 1С.
В этом случае СУБД выполняет простой, легкий и мгновенный запрос без тяжелых соединений, а вся логика заполнения пустых ячеек переносится на уровень сервера приложений. Обход коллекции выполняется за один единственный проход — сложность алгоритма линейная O(N), что гарантирует моментальное выполнение даже на миллионах строк данных.
Рассмотрим по шагам пример эффективного кода:
// 1. Получаем данные из СУБД простым линейным запросом
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| вт.Код КАК Код,
| вт.Цена КАК Цена
|ИЗ
| вт КАК вт
|УПОРЯДОЧИТЬ ПО
| Код";
РезультатЗапроса = Запрос.Выполнить();
// Выгружаем результат в ТаблицуЗначений для возможности модификации строк
ТаблицаДанных = РезультатЗапроса.Выгрузить();
// Инициализируем переменную для хранения последней известной цены
ПредыдущаяЦена = 0;
// 2. Выполняем обход коллекции в один проход
Для Каждого Строка Из ТаблицаДанных Цикл
Если Строка.Цена = 0 Тогда // Здесь также может быть проверка на NULL или Неопределено
Строка.Цена = ПредыдущаяЦена;
Иначе
// Если значение заполнено, запоминаем его для последующих строк
ПредыдущаяЦена = Строка.Цена;
КонецЕсли;
КонецЦикла;
// На выходе получаем полностью готовую и заполненную таблицу значений
Этот простой код работает стабильно, предсказуемо и не создает лишней нагрузки на СУБД. В подавляющем большинстве бизнес-сценариев именно этот подход является наиболее предпочтительным.
В тех случаях, когда данные готовятся для вывода в отчет и у разработчика нет возможности написать код на встроенном языке (например, при разработке отчета на схеме компоновки данных без использования кода), на помощь приходит мощный функционал СКД.
В стандартном языке запросов 1С, основанном на стандарте SQL-92, отсутствуют современные оконные функции смещения (такие как LAG() или LEAD()). Однако разработчики платформы реализовали мощный аналог этих функций непосредственно внутри вычислительного процессора СКД.
Для решения нашей задачи мы можем использовать функцию языка выражений СКД — ВычислитьВыражение. Рассмотрим порядок настройки схемы:
ЦенаДляВывода).Выбор Когда Цена = 0 Тогда ВычислитьВыражение("Цена", , , "Предыдущая", "Предыдущая") Иначе Цена Конец
Преимущество этого метода в том, что СКД самостоятельно рассчитывает эти показатели «на лету» в оперативной памяти во время компоновки результата, избавляя СУБД от необходимости выполнять тяжелые самосоединения таблиц.
В реальной разработке 1С аналогичная задача чаще всего возникает при работе с регистрами сведений (например, получение актуальных цен номенклатуры или курсов валют на каждый день выбранного периода, включая те дни, когда записей в регистре не было).
Для решения этой задачи существует классический, проверенный временем и оптимизированный шаблон запроса. Разберем по шагам его логику:
ТаблицаДат.Дата >= ЦеныНоменклатуры.Период.ТаблицаДат.Дата и поиском функции МАКСИМУМ(ЦеныНоменклатуры.Период) определяется точная дата последней установки цены для каждого дня.Благодаря наличию индексов по полям даты в физических таблицах СУБД для регистров сведений 1С, этот алгоритм отрабатывает быстро и эффективно.
Подводя итоги нашего анализа, сформулируем рекомендации по выбору оптимального решения: