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