При работе с запросами в 1С часто возникает задача агрегации данных по сущностям, которые могут фигурировать в различных ролях в рамках одного или нескольких документов. В данной теме мы рассмотрим типичную ситуацию, когда необходимо получить консолидированную сумму по каждому сотруднику, который может быть указан как бригадир или как рабочий в документах. Мы разберем, почему стандартная группировка может давать нежелательные "повторы", и предложим эффективное решение с использованием операторов ОБЪЕДИНИТЬ ВСЕ и ПОМЕСТИТЬ (временные таблицы).
Наш пользователь столкнулся с проблемой, когда запрос не группирует данные по сотрудникам так, как ожидалось. Исходный запрос, который он использовал для вывода информации из документа "Заказ", выглядел следующим образом:
| | Заказ.Бригадир.Наименование КАК Бригадир,
| | Заказ.Рабочий.Наименование КАК Рабочий,
| | СУММА(Заказ.БригадирСум) КАК БригадирСум,
| | СУММА(Заказ.РабочийСум) КАК РабочийСум,
|ИЗ
| | Документ.Заказ КАК Заказ
| |ГДЕ
| | Заказ .Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ)
|СГРУППИРОВАТЬ ПО
| | Заказ .Бригадир.Наименование,
| | Заказ .Рабочий.Наименование
При выполнении такого запроса, пользователь ожидал, что если один и тот же человек (например, "Иванов И.") фигурирует в разных документах или разных ролях, его суммы будут сгруппированы в одну строку. Однако, как он отметил, в результате присутствовали "повторы людей", хотя фамилии были абсолютно одинаковыми.
Давайте посмотрим, почему исходный запрос не дал желаемого результата. Оператор СГРУППИРОВАТЬ ПО в 1С (как и в стандартном SQL) работает следующим образом: он объединяет строки, для которых значения всех полей, перечисленных в разделе СГРУППИРОВАТЬ ПО, являются идентичными. В нашем случае, группировка происходит по паре полей: Заказ.Бригадир.Наименование и Заказ.Рабочий.Наименование.
Представим себе следующую ситуацию, которую пользователь проиллюстрировал для понимания ожидаемого результата:
Исходный запрос сформировал бы две отдельные строки, поскольку пары "Бригадир-Рабочий" уникальны:
Мы видим "Иванова" в двух разных строках, потому что в первом случае он был бригадиром в связке с Петровым, а во втором — рабочим в связке с Сидоровым. Эти комбинации (Иванов-Петров) и (Сидоров-Иванов) являются уникальными. Запрос корректно сгруппировал данные по указанной паре, но это не то, что требовалось. Пользователь желает получить общий итог по "Иванову" как по единой сущности, независимо от его роли.
Ожидаемый результат, как пояснил пользователь, должен быть таким:
Это означает, что нам нужно "свести" все суммы по каждому уникальному человеку, независимо от его роли в документе.
ОБЪЕДИНИТЬ ВСЕ и временные таблицыДля решения этой задачи нам потребуется выполнить несколько шагов в рамках одного пакетного запроса:
Именно для такой многоступенчатой обработки данных в запросах 1С используются пакетные запросы с временными таблицами (оператор ПОМЕСТИТЬ) и оператор ОБЪЕДИНИТЬ ВСЕ.
Оператор ОБЪЕДИНИТЬ ВСЕ позволяет "вертикально" сложить результаты нескольких запросов, то есть записи одного запроса добавляются к записям другого. Важно, чтобы объединяемые запросы возвращали одинаковое количество полей с совместимыми типами данных. При использовании ОБЪЕДИНИТЬ ВСЕ дубликаты строк сохраняются, что для нашей задачи является преимуществом, так как позволяет собрать все частичные суммы по одному человеку перед финальной агрегацией.
Временные таблицы, создаваемые с помощью оператора ПОМЕСТИТЬ, служат для сохранения промежуточных результатов запроса. Это делает сложные запросы более структурированными, читаемыми и зачастую более производительными, поскольку позволяет базе данных эффективно обрабатывать данные поэтапно.
Давайте пошагово рассмотрим окончательный вариант запроса, который эффективно решает поставленную задачу. Мы будем использовать пакетный запрос, где сначала сформируем временную таблицу, а затем проведем по ней финальную группировку.
На первом этапе мы создаем временную таблицу под названием втНеСвернутыеСуммы. В эту таблицу мы помещаем всех бригадиров с их суммами и всех рабочих с их суммами. Важно, что для обеих ролей мы используем одно и то же имя поля — СотрудникФИО — для наименования человека и Сумма для денежного значения. Это позволит нам унифицировать данные и затем легко сгруппировать их.
ВЫБРАТЬ Заказ.Бригадир.Наименование КАК СотрудникФИО, Заказ.БригадирСум КАК Сумма
ПОМЕСТИТЬ втНеСвернутыеСуммы
ИЗ Документ.Заказ КАК Заказ
ГДЕ Заказ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Заказ.Рабочий.Наименование, Заказ.РабочийСум
ИЗ Документ.Заказ КАК Заказ
ГДЕ Заказ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ)
Разберем этот фрагмент:
ВЫБРАТЬ получает Наименование бригадира из документа Заказ и его сумму БригадирСум. Мы даем этим полям псевдонимы СотрудникФИО и Сумма соответственно.ПОМЕСТИТЬ втНеСвернутыеСуммы указывает, что результат этого запроса (и всех, что будут объединены с ним) будет сохранен во временной таблице с таким именем.ОБЪЕДИНИТЬ ВСЕ. Он присоединяет результаты следующего блока ВЫБРАТЬ к уже полученным данным, помещая их также во временную таблицу.ВЫБРАТЬ аналогично получает Наименование рабочего из документа Заказ и его сумму РабочийСум, также используя псевдонимы СотрудникФИО и Сумма.ГДЕ Заказ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ) применяется к обоим подзапросам, обеспечивая фильтрацию по заданному периоду.В результате выполнения этого шага, временная таблица втНеСвернутыеСуммы будет содержать строки типа:
Обратите внимание, что "Иванов" здесь присутствует дважды, но уже в унифицированной структуре, готовой для окончательной агрегации.
После того как все данные по сотрудникам и их суммам собраны в одной временной таблице, мы можем выполнить окончательную группировку. Теперь мы группируем по единому полю СотрудникФИО и суммируем значения по полю Сумма.
;
ВЫБРАТЬ втНеСвернутыеСуммы.СотрудникФИО КАК СотрудникФИО, СУММА(втНеСвернутыеСуммы.Сумма) КАК Сумма
ИЗ втНеСвернутыеСуммы КАК втНеСвернутыеСуммы
СГРУППИРОВАТЬ ПО втНеСвернутыеСуммы.СотрудникФИО
Разберем этот фрагмент:
;) разделяет запросы в пакете. Это обязательный разделитель между оператором ПОМЕСТИТЬ и последующим запросом в пакетных запросах.СотрудникФИО и применяем агрегатную функцию СУММА() к полю Сумма из нашей временной таблицы втНеСвернутыеСуммы.ИЗ втНеСвернутыеСуммы КАК втНеСвернутыеСуммы указывает, что источником данных для этого запроса является ранее созданная временная таблица.СГРУППИРОВАТЬ ПО втНеСвернутыеСуммы.СотрудникФИО указывает, что мы хотим сгруппировать все строки, имеющие одинаковое значение в поле СотрудникФИО, и для каждой такой группы вычислить сумму по полю Сумма.В результате выполнения этого финального запроса мы получим требуемый консолидированный результат:
Именно такой результат и ожидал получить пользователь: каждый уникальный сотрудник представлен одной строкой с общей суммой его вознаграждения, независимо от того, был ли он бригадиром или рабочим в разных документах.
Объединив оба шага, мы получаем полный код пакетного запроса, который эффективно решает задачу группировки по одной сущности, участвующей в разных ролях:
Запрос.Текст =
"ВЫБРАТЬ Заказ.Бригадир.Наименование КАК СотрудникФИО, Заказ.БригадирСум КАК Сумма
|ПОМЕСТИТЬ втНеСвернутыеСуммы
|ИЗ Документ.Заказ КАК Заказ
|ГДЕ Заказ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ)
|ОБЪЕДИНИТЬ ВСЕ
|ВЫБРАТЬ Заказ.Рабочий.Наименование, Заказ.РабочийСум
|ИЗ Документ.Заказ КАК Заказ
|ГДЕ Заказ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонПериода, ДЕНЬ)
|;
|ВЫБРАТЬ втНеСвернутыеСуммы.СотрудникФИО КАК СотрудникФИО, СУММА(втНеСвернутыеСуммы.Сумма) КАК Сумма
|ИЗ втНеСвернутыеСуммы КАК втНеСвернутыеСуммы
|СГРУППИРОВАТЬ ПО втНеСвернутыеСуммы.СотрудникФИО";
В представленном решении мы использовали для группировки Наименование сотрудника. В некоторых случаях более надежным является группировка по самому объекту-ссылке (например, Заказ.Бригадир вместо Заказ.Бригадир.Наименование), особенно если в системе могут быть люди с одинаковыми ФИО, но являющиеся при этом разными элементами справочника. Если объекты Бригадир и Рабочий ссылаются на один и тот же справочник (например, "Сотрудники" или "ФизическиеЛица"), то группировка по самим ссылкам будет более точной и исключит возможные коллизии одинаковых имен.
Также стоит отметить, что начиная с определенных версий платформы 1С (8.3 и выше), появилась возможность использования конструкции GROUPING SETS (или ГРУППИРУЮЩИМ НАБОРАМ). Эта конструкция позволяет выполнять группировку по нескольким наборам полей за одно сканирование таблицы, что в некоторых случаях может быть более компактной и производительной альтернативой использованию ОБЪЕДИНИТЬ ВСЕ с последующей агрегацией. Однако для нашей задачи, где требуется сначала унифицировать поле, а затем группировать, подход с ОБЪЕДИНИТЬ ВСЕ и временной таблицей остается наиболее наглядным и универсальным.
Мы видим, что проблема "неправильной" группировки в запросах 1С часто кроется в неполном понимании того, как оператор СГРУППИРОВАТЬ ПО обрабатывает множественные поля. Для сбора данных из разных ролей и их последующей консолидации мы успешно применили пакетный запрос с временными таблицами и оператором ОБЪЕДИНИТЬ ВСЕ. Этот подход позволяет гибко манипулировать данными и получать именно тот агрегированный результат, который требуется для аналитических или отчетных задач, обеспечивая правильное суммирование по каждому уникальному сотруднику, независимо от его роли в документе.