В практической разработке на платформе 1С:Предприятие одной из классических задач является сопоставление данных из разных источников по временной шкале. Яркий пример — подготовка отчета «План-фактный анализ», где плановые показатели хранятся в одной таблице (например, в регистре сведений или накопления), а фактические — в другой. Периоды в этих таблицах далеко не всегда совпадают: план может быть установлен на начало месяца, а фактические движения происходят в произвольные дни.
В этой статье мы подробно рассмотрим, как правильно, красиво и, главное, максимально производительно объединить две таблицы по периоду в запросе 1С. Мы разберем классические ошибки, проанализируем стандарты разработки 1С и изучим различные способы дополнения периодов, включая использование Системы компоновки данных (СКД) и механизмов расчета итогов.
Первое решение, которое приходит в голову многим начинающим разработчикам, — использовать ПОЛНОЕ СОЕДИНЕНИЕ двух исходных таблиц по полю Период, оборачивая выбираемые поля в функцию ЕСТЬNULL. На первый взгляд, это кажется логичным: нам нужны все даты как из первой, так и из второй таблицы. Однако у этого подхода есть серьезные недостатки:
ПОЛНОЕ СОЕДИНЕНИЕ. В СУБД PostgreSQL отсутствует прямая оптимальная реализация этой операции. Платформа 1С вынуждена транслировать такой запрос в сложную обходную конструкцию с использованием LEFT JOIN, RIGHT JOIN и UNION, что приводит к колоссальному падению скорости выполнения запроса на больших объемах данных.Рассмотрим, как решить эту задачу изящно и в полном соответствии со стандартами разработки фирмы «1С».
Самым быстрым, красивым и легко масштабируемым решением является сбор всех данных в одну плоскую таблицу с помощью оператора ОБЪЕДИНИТЬ ВСЕ, с последующим схлопыванием (группировкой) строк по полю Период. При таком подходе СУБД выполняет линейное чтение данных и быструю группировку по индексу, что работает в разы быстрее любого соединения.
Разберем этот алгоритм по шагам на конкретном примере. Создадим временную таблицу, в которую поместим объединенные данные из таблиц «План» и «Результат» (Факт). Для разделения показателей мы будем использовать «разнесение» сумм по разным колонкам, заполняя противоположные колонки нулями.
Посмотрим на текст запроса:
ВЫБРАТЬ
План.Период КАК Период,
План.СуммаПлановая КАК СуммаПлановая,
0 КАК СуммаФактическая
ПОМЕСТИТЬ вт_Данные
ИЗ
План КАК План
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Результат.Период,
0,
Результат.СуммаФактическая
ИЗ
Результат КАК Результат;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
вт_Данные.Период КАК Период,
СУММА(вт_Данные.СуммаПлановая) КАК СуммаПлановая,
СУММА(вт_Данные.СуммаФактическая) КАК СуммаФактическая
ИЗ
вт_Данные КАК вт_Данные
СГРУППИРОВАТЬ ПО
вт_Данные.Period
УПОРЯДОЧИТЬ ПО
Период
Проанализируем, как работает этот запрос. В первом пакете мы выбираем плановые данные и жестко задаем, что колонка СуммаФактическая для этих строк равна 0. Во второй части объединения мы выбираем фактические данные, устанавливая СуммаПлановая в 0. Объединение ОБЪЕДИНИТЬ ВСЕ работает мгновенно, так как база данных просто «склеивает» две выборки по вертикали без проведения ресурсоемких проверок на уникальность строк.
Во втором пакете мы группируем полученную временную таблицу вт_Данные по полю Период. Применяя агрегатную функцию СУММА, мы объединяем строки с одинаковыми датами. В результате для одного и того же дня мы получим одну строку, где плановые и фактические суммы займут свои места в соответствующих колонках.
Если ваши исходные таблицы содержат сотни тысяч или миллионы строк, производительность группировки может снизиться. Чтобы этого избежать, всегда индексируйте временные таблицы по полям группировки или соединения. В нашем случае необходимо добавить индекс по полю Период:
ВЫБРАТЬ
План.Период КАК Период,
План.СуммаПлановая КАК СуммаПлановая,
0 КАК СуммаФактическая
ПОМЕСТИТЬ вт_Данные
ИЗ
План КАК План
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Результат.Период,
0,
Результат.СуммаФактическая
ИЗ
Результат КАК Результат
ИНДЕКСИРОВАТЬ ПО
Период
Наличие индекса позволит СУБД мгновенно выполнить свертку данных на втором этапе, исключив операцию полного сканирования временной таблицы (Table Scan).
Часто бизнес-требования к отчетам звучат так: «Нам нужно видеть все периоды в интервале от Даты А до Даты Б, даже если по ним не было ни плановых, ни фактических движений». Если использовать предыдущий запрос, то дни (или месяцы), в которых не было записей ни в Плане, ни в Факте, просто выпадут из итоговой таблицы.
Чтобы решить эту задачу в обычном коде без построения сложных «математических генераторов дат» из сотен строк кода, мы можем применить штатную конструкцию языка запросов 1С — расчет итогов с дополнением периодов. Выясним, как это выглядит на практике:
ВЫБРАТЬ
вт_Данные.Период КАК Период,
СУММА(вт_Данные.СуммаПлановая) КАК СуммаПлановая,
СУММА(вт_Данные.СуммаФактическая) КАК СуммаФактическая
ИЗ
вт_Данные КАК вт_Данные
СГРУППИРОВАТЬ ПО
вт_Данные.Период
ИТОГИ
СУММА(СуммаПлановая),
СУММА(СуммаФактическая)
ПО
Период ПЕРИОДАМИ(МЕСЯЦ, &ДатаНачала, &ДатаОкончания)
Обратите внимание на конструкцию ПЕРИОДАМИ(МЕСЯЦ, &ДатаНачала, &ДатаОкончания). Она указывает платформе 1С на необходимость автоматически сгенерировать строки для всех недостающих месяцев в указанном диапазоне дат. Если данных за конкретный месяц не было ни в одной из таблиц, платформа сама подставит пустые значения (нули или NULL в зависимости от настроек).
Важная деталь программной обработки такого запроса: чтобы при обходе выборки в коде 1С получить абсолютно все сгенерированные периоды, обход результата запроса нужно выполнять специальным образом, передавая третий параметр ВСЕ в метод выбора группировки:
ВыборкаПериод = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "ВСЕ");
Пока ВыборкаПериод.Следующий() Цикл
// Здесь мы гарантированно зайдем во все месяцы периода, даже в пустые
Сообщить("Период: " + ВыборкаПериод.Период + " План: " + ВыборкаПериод.СуммаПлановая);
КонецЦикла;
Если вы разрабатываете отчет на базе СКД, то писать генераторы дат или использовать обход выборки с параметром «ВСЕ» вообще не требуется. СКД берет эту рутину на себя благодаря механизму роли поля «Период».
Рассмотрим по шагам, как красиво настроить дополнение периодов в СКД:
ОБЪЕДИНИТЬ ВСЕ и группировку) без всяких итогов и поместим его в Схему компоновки данных в качестве набора данных - запроса.Период.День, Месяц, Квартал).Период в окне свойств группировки найдем свойство «Тип дополнения» и установим его в значение Месяц (или другой нужный период), а также укажем параметры «Начало периода» и «Конец периода» (обычно это параметры отчета &НачалоПериода и &КонецПериода).Система компоновки данных автоматически спроецирует ваши данные на непрерывный календарный график. СКД сама добавит недостающие строки и красиво выведет нули вместо отсутствующих сумм.
В крупных учетных системах (таких как 1С:ERP, 1С:Комплексная автоматизация или 1С:Бухгалтерия) существует прекрасная альтернатива генераторам дат «из коробки» — регистр сведений, хранящий информацию о календарных днях. Как правило, он называется РегламентированныйПроизводственныйКалендарь или КалендарныеГрафики.
Мы можем использовать данные этого регистра в качестве опорной таблицы (таймлайна), к которой левым соединением будем подключать наши плановые и фактические данные. Разберем этот пример:
ВЫБРАТЬ
Календарь.Дата КАК Период
ПОМЕСТИТЬ вт_Календарь
ИЗ
РегистрСведений.РегламентированныйПроизводственныйКалендарь КАК Календарь
ГДЕ
Календарь.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
И Календарь.Календарь = &ОсновнойКалендарь
ИНДЕКСИРОВАТЬ ПО
Период;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
вт_Календарь.Период КАК Период,
ЕСТЬNULL(вт_Данные.СуммаПлановая, 0) КАК СуммаПлановая,
ЕСТЬNULL(вт_Данные.СуммаФактическая, 0) КАК СуммаФактическая
ИЗ
вт_Календарь КАК вт_Календарь
ЛЕВОЕ СОЕДИНЕНИЕ вт_Данные КАК вт_Данные
ПО вт_Календарь.Период = вт_Данные.Период
УПОРЯДОЧИТЬ ПО
Период
В качестве таблицы вт_Данные здесь выступает результат группировки из Способа №1. Такое решение выглядит очень профессионально, легко читается другими разработчиками и работает стабильно быстро, так как СУБД делает простое левое соединение двух подготовленных и проиндексированных таблиц.
Подведем итоги нашего анализа и сформулируем правила «красивого» написания запросов для объединения таблиц по периоду: