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