В практике разработки на платформе 1С:Предприятие часто возникает задача получения данных из разных источников (разных счетов, регистров или разных условий одного регистра) с их последующим сведением в единую таблицу. Типичный пример — получение оборотов по основному счету расчетов с поставщиками (60.01) и счету авансов (60.02) с целью увидеть общую картину по контрагенту.
В этой статье мы подробно разберем, как правильно использовать механизмы языка запросов для объединения таблиц, почему простая конструкция ОБЪЕДИНИТЬ ВСЕ не всегда дает ожидаемый результат "в одну строку" и как применить итоговую группировку для получения чистого результата.
Рассмотрим ситуацию: у нас есть два запроса. Первый выбирает обороты по основному счету, второй — по авансовому. При использовании оператора ОБЪЕДИНИТЬ ВСЕ система просто "склеивает" результаты один под другим. Если один и тот же контрагент встречается в обоих запросах, в результирующей таблице он появится двумя отдельными строками. Наша задача — просуммировать данные так, чтобы каждому контрагенту соответствовала ровно одна строка с общими суммами.
Проанализируем наиболее надежный и читаемый способ решения этой задачи — использование временной таблицы. Мы сначала объединяем данные "как есть", помещаем их в промежуточное хранилище, а затем выбираем из него с применением агрегатных функций.
Рассмотрим по шагам, как это реализовать:
ПОМЕСТИТЬ.СГРУППИРОВАТЬ ПО.Посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
ХозрасчетныйОбороты.Субконто1 КАК Контр,
ХозрасчетныйОбороты.СуммаОборот КАК СуммаОборот,
ХозрасчетныйОбороты.СуммаОборотДт КАК Оплачено,
ХозрасчетныйОбороты.СуммаОборотКт КАК Поставлено
ПОМЕСТИТЬ ВТ_СводныеДанные
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&Начало, &Конец, , Счет = &Счет, , , , ) КАК ХозрасчетныйОбороты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ХозрасчетныйОбороты.Субконто1,
ХозрасчетныйОбороты.СуммаОборот,
ХозрасчетныйОбороты.СуммаОборотДт,
ХозрасчетныйОбороты.СуммаОборотКт
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&Начало, &Конец, , Счет = &Счет2, , , , ) КАК ХозрасчетныйОбороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_СводныеДанные.Контр КАК Контрагент,
СУММА(ВТ_СводныеДанные.СуммаОборот) КАК СуммаОбщая,
СУММА(ВТ_СводныеДанные.Оплачено) КАК ОплаченоИтого,
СУММА(ВТ_СводныеДанные.Поставлено) КАК ПоставленоИтого
ИЗ
ВТ_СводныеДанные КАК ВТ_СводныеДанные
СГРУППИРОВАТЬ ПО
ВТ_СводныеДанные.Контр
Важный момент: обратите внимание, что во втором запросе пакета мы обязательно используем функции СУММА для всех числовых полей. Это и есть тот механизм, который "схлопывает" строки по одинаковым контрагентам.
Часто бывает, что в первой таблице нам нужно получить одни показатели (например, "Количество"), а во второй — другие (например, "Стоимость"). Чтобы корректно объединить такие данные, мы должны "выровнять" состав полей в обоих запросах, используя пустые значения или нули.
Проанализируем ситуацию на примере регистра накопления:
ВЫБРАТЬ
Обороты.Номенклатура КАК Номенклатура,
Обороты.КоличествоОборот КАК Количество,
0 КАК Стоимость
ПОМЕСТИТЬ ВТ_Объединение
ИЗ
РегистрНакопления.Закупки.Обороты(&Начало, &Конец) КАК Обороты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Обороты.Номенклатура,
0,
Обороты.СтоимостьОборот
ИЗ
РегистрНакопления.Продажи.Обороты(&Начало, &Конец) КАК Обороты
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ВТ_Объединение.Номенклатура,
СУММА(ВТ_Объединение.Количество) КАК Количество,
СУММА(ВТ_Объединение.Стоимость) КАК Стоимость
ИЗ
ВТ_Объединение КАК ВТ_Объединение
СГРУППИРОВАТЬ ПО
ВТ_Объединение.Номенклатура
Выясним причину использования 0 вместо NULL. В языке запросов 1С использование NULL в арифметических операциях или функциях агрегации может привести к тому, что итоговый результат тоже станет NULL. Использование нуля гарантирует, что тип поля останется числовым и функция СУММА отработает корректно.
Рассмотрим распространенную ошибку, которая существенно замедляет выполнение запроса. В сообщениях форума часто можно встретить фильтрацию через секцию ГДЕ. Для виртуальных таблиц (таких как Хозрасчетный.Обороты или РегистрыНакопления...Обороты) крайне критично передавать условия в параметры виртуальной таблицы.
Сравним два подхода:
Неправильно: данные сначала выбираются из всей базы, а потом отсекаются.
ИЗ РегистрБухгалтерии.Хозрасчетный.Обороты(...) ГДЕ Счет = &Счет
Правильно: данные выбираются базой данных уже отфильтрованными.
ИЗ РегистрБухгалтерии.Хозрасчетный.Обороты(&Начало, &Конец, , Счет = &Счет, ...)
Проанализируем, почему это важно: при использовании параметров виртуальной таблицы SQL-сервер строит более эффективный план запроса, используя существующие индексы по периоду и счету. Это особенно заметно в крупных базах данных.
Разберем различие между этими операторами. Оператор ОБЪЕДИНИТЬ (без слова ВСЕ) выполняет дополнительную операцию — поиск и удаление дублирующихся строк во всем результирующем наборе. Это очень "дорогая" операция для сервера базы данных.
Поскольку мы в любом случае планируем использовать СГРУППИРОВАТЬ ПО в следующем шаге, проверка на уникальность при объединении нам не нужна. Всегда используйте ОБЪЕДИНИТЬ ВСЕ для повышения производительности.
При объединении полей типа "Субконто" важно помнить, что они имеют составной тип данных. Если мы точно знаем, что в конкретном запросе в Субконто1 находится справочник Контрагенты, стоит использовать оператор ВЫРАЗИТЬ. Это упростит группировку и ускорит работу запроса.
Пример использования:
ВЫРАЗИТЬ(ХозрасчетныйОбороты.Субконто1 КАК Справочник.Контрагенты) КАК Контр
Подведем итог: для объединения данных из разных источников в 1С с итоговым суммированием необходимо:
ОБЪЕДИНИТЬ ВСЕ для слияния сырых данных.Такой подход обеспечит не только корректность получаемых данных, но и высокую скорость работы отчета даже при больших объемах информации в учетной системе.