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

← На главную

В практике разработки на платформе 1С:Предприятие часто возникает задача построения отчетов, где данные должны быть представлены непрерывно по шкале времени. Например, нам нужно увидеть остатки или цены поставщиков за каждый день месяца. Однако в базе данных записи могут присутствовать только за те дни, когда происходили изменения. Если 1-го числа цена была установлена, а следующее изменение произошло только 10-го, то в интервале со 2-го по 9-е число в таблице будет «дырка». Простой запрос к физической таблице не вернет эти строки, что ломает структуру отчета.

В данной статье мы подробно разберем, как программно «восстановить» эти данные в запросе, проанализируем различные подходы — от классического использования временных таблиц до специализированных механизмов Системы компоновки данных (СКД).

Решение проблемы с помощью временных таблиц и декартова произведения

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

Для начала нам необходимо подготовить «сетку» или «матрицу» всех возможных комбинаций поставщиков и периодов. Мы не можем просто соединить поставщиков с датами, если этих дат нет в записях конкретного поставщика. Поэтому мы используем полное соединение (декартово произведение) уникальных поставщиков и уникальных дат. Рассмотрим, как это реализовать в коде запроса:


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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
    ВТ.Поставщик КАК Поставщик
ПОМЕСТИТЬ втВсеПоставщики
ИЗ
    ВТ_ИсходныеДанные КАК ВТ;

////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Создаем декартово произведение - все даты для каждого поставщика
ВЫБРАТЬ
    втВсеДаты.Период КАК Период,
    втВсеПоставщики.Поставщик КАК Поставщик
ПОМЕСТИТЬ втКомбинации
ИЗ
    втВсеДаты КАК втВсеДаты,
    втВсеПоставщики КАК втВсеПоставщики;

На данном этапе в таблице втКомбинации у нас есть строки для каждого поставщика на каждую дату. Теперь нам нужно выяснить, какое значение было актуально для поставщика в эти «пустые» даты. Обычно актуальным считается последнее известное значение на дату или ранее. Проанализируем, как найти дату последней записи для каждой комбинации:


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

Важный момент: Обратите внимание на условие соединения ВТ.Период <= втКомбинации.Период. Именно оно позволяет нам заглянуть в «прошлое» и найти самую близкую дату, в которой значение было заполнено. Использование агрегатной функции МАКСИМУМ гарантирует, что мы возьмем именно последнее актуальное значение, а не какое-то случайное из прошлого.

Завершающим шагом мы соединяем нашу подготовленную сетку с исходными данными по найденной максимальной дате, чтобы получить само Значение:


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

Использование Системы компоновки данных (СКД) для дополнения периодов

Хотя метод с запросами универсален, в 1С существует более элегантный способ решения этой задачи — механизм дополнения периодов в СКД. Если ваша задача — вывести отчет, мы настоятельно рекомендуем использовать именно его. Рассмотрим подробнее преимущества этого метода:

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

Чтобы задействовать этот функционал, в конструкторе схемы компоновки данных на закладке «Наборы данных» для поля периода в колонке «Роль» необходимо указать тип дополнения и границы. В этом случае, если по поставщику за какой-то день нет данных, СКД создаст строку с NULL, который можно обработать функцией ЕстьNull() или вычислить с помощью ресурсов.

Оптимизация производительности: Индексы и СрезПоследних

Выясним причину, по которой сложные запросы с условием <= могут работать медленно. При соединении таблиц по неравенству СУБД выполняет гораздо больший объем работы, чем при соединении по равенству. Чтобы запрос не «повесил» систему на больших данных, проанализируем следующие рекомендации:

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

Альтернативный подход: Обработка результата в коде

Иногда, если запрос получается слишком громоздким или его невозможно оптимизировать под требования СУБД, задачу можно решить при обходе результата запроса на языке 1С. Разберем этот алгоритм:

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


// Пример логики на языке 1С
Пока Выборка.Следующий() Цикл
    Если Поставщик <> Выборка.Поставщик Тогда
        // Новый поставщик, сбрасываем счетчик дат
        ТекущаяДата = НачалоПериода;
    КонецЕсли;

    // Заполняем пропуски, если они есть
    Пока ТекущаяДата < Выборка.Период Цикл
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.Период = ТекущаяДата;
        НоваяСтрока.Поставщик = Выборка.Поставщик;
        НоваяСтрока.Значение = ПоследнееЗначение;
        
        ТекущаяДата = ДобавитьДень(ТекущаяДата, 1);
    КонецЦикла;

    // Выводим текущую строку из базы
    // ...
    ПоследнееЗначение = Выборка.Значение;
КонецЦикла;

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

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