При разработке отчетов в Системе Компоновки Данных (СКД) на платформе 1С:Предприятие 8.3 программисты часто сталкиваются с двумя типами задач, которые требуют внимательного подхода к агрегации данных и построению запросов. Первая задача — это корректное суммирование итогов для ресурсов, значения которых уже были агрегированы на предыдущих этапах запроса, но при детализации отчета начинают суммироваться многократно. Вторая задача связана с переносом сложной логики, использующей специфические функции СКД, такие как ВычислитьВыражение или ВычислитьВыражениеСГруппировкойМассив, непосредственно в текст запроса 1С.
В этом руководстве мы подробно разберем эти проблемы, объясним их причины и предложим эффективные пути решения.
Рассмотрим ситуацию, когда в вашем отчете СКД есть поля, такие как "Выручка" или "Лизинг", которые уже были рассчитаны и агрегированы во временных таблицах на определенном уровне детализации (например, по транспортному средству и месяцу). Если эти поля затем соединяются с основным набором данных, который имеет более высокую детализацию (например, по "Заявке"), и вы просто указываете для них агрегатную функцию Сумма() в ресурсах СКД, то это приведет к их многократному и некорректному суммированию. Каждая строка с "Заявкой" будет содержать полную сумму "Выручки" или "Лизинга" для соответствующего ТС и месяца, а при группировке по ТС или месяцу, эти значения будут просуммированы столько раз, сколько было заявок за этот период, что даст неверный результат.
Когда вы формируете отчет в СКД, система выполняет запросы и затем накладывает на полученные данные настройки группировок, полей и ресурсов. Если в вашем базовом запросе (или во временных таблицах) поле "Выручка" для определенного ТС и месяца уже содержит итоговую сумму (например, 1000 руб.), и это ТС за месяц имело 5 заявок, то при соединении с таблицей заявок у вас получится 5 строк, каждая из которых будет содержать эту же сумму выручки 1000 руб. Если затем вы в ресурсах СКД укажете Сумма(Выручка), то на уровне группировки по ТС и месяцу вы получите 5 * 1000 = 5000 руб., что является ошибочным результатом.
Если поле уже содержит агрегированное значение на том уровне, на котором оно будет выводиться в отчете, то вместо Сумма() для него следует использовать агрегатные функции Максимум() или Минимум(). Эти функции гарантируют, что на уровне группировки СКД будет взято одно уникальное значение из всех дублирующихся строк, а при агрегации вверх по иерархии группировок это значение будет правильно отображаться, не умножаясь на количество подчиненных строк.
Перейдите в настройки ресурсов СКД: Откройте вкладку "Ресурсы" в конфигураторе отчета.
Измените агрегатную функцию: Для полей, которые уже агрегированы во временных таблицах (например, "Выручка", "Лизинг"), измените выражение ресурса с Сумма(Выручка) на Максимум(Выручка) или Минимум(Выручка).
Пример:
// Вместо:
// Сумма(Выручка)
// Сумма(Лизинг)
// Используйте:
Максимум(Выручка)
Максимум(Лизинг)
Выбор между Максимум() и Минимум() в данном контексте обычно не имеет значения, поскольку все значения в группе должны быть одинаковыми. Главное – избежать повторного суммирования.
Другой способ решения этой проблемы — это перестройка запроса таким образом, чтобы агрегированные поля соединялись с основным набором данных только после того, как все остальные данные будут сгруппированы до нужного уровня. Это сложнее, но может быть полезно в особо запутанных случаях:
Сначала выполняем основной запрос, группируя его до уровня, который соответствует детализации уже агрегированных полей (например, до ТС и месяца).
Затем левым соединением (ЛЕВОЕ СОЕДИНЕНИЕ) присоединяем временные таблицы с "Выручкой" и "Лизингом" к уже сгруппированным данным.
В этом случае, каждое значение "Выручки" и "Лизинга" будет соответствовать одной строке сгруппированных данных, и функция Сумма() в ресурсах СКД уже не приведет к многократному увеличению.
Одна из частых ошибок, с которой сталкиваются разработчики, — попытка использовать функции, специфичные для языка выражений СКД, такие как ВычислитьВыражение и ВычислитьВыражениеСГруппировкойМассив, непосредственно в тексте запроса на языке 1С. Эти функции предназначены исключительно для использования в выражениях полей, параметров, ресурсов и других настройках внутри самой СКД. Они не являются частью стандартного языка запросов 1С, который транслируется в SQL, и поэтому их использование в запросе всегда приводит к ошибке "Синтаксическая ошибка".
Язык запросов 1С имеет свой строгий синтаксис и набор доступных операторов, функций и ключевых слов. Когда вы пишете запрос в конфигураторе или программно, платформа 1С преобразует его в SQL-запрос, который затем отправляется в базу данных (MS SQL, PostgreSQL, Oracle и т.д.). Функции СКД, такие как ВычислитьВыражение, не имеют прямых аналогов в SQL и являются внутренними механизмами СКД для работы с данными, уже полученными из запроса и скомпонованными в отчет. Пытаясь использовать их в запросе, вы нарушаете синтаксис языка запросов 1С, что и приводит к ошибке.
Вот пример некорректного использования, который вы пытались применить:
ВЫБРАТЬ
Сумма(ВТ_Общ.СуммаКВыплате2) + ВЫБОР КОГДА ВычислитьВыражение(ВТ_Общ.Дата, ВТ_Общ.Дата, ДобавитьКДате(ВТ_Общ.Дата, День, - 1), &Текущая) = Дата И ... КОНЕЦ КАК СуммаКВыплате2,
// ... другие поля
ИЗ
ВТ_Общ КАК ВТ_Общ
Здесь функция ВычислитьВыражение воспринимается языком запросов как неизвестный оператор, что и порождает синтаксическую ошибку.
Перенос сложной логики, использующей возможности СКД по работе с соседними строками (например, "Предыдущая/Текущая") или агрегации по массивам, непосредственно в запрос 1С является нетривиальной задачей, поскольку в языке запросов 1С нет прямых аналогов оконных функций SQL (таких как LAG, LEAD, ROW_NUMBER), которые позволяют легко получить данные из соседних строк без явного соединения. Однако, мы можем эмулировать это поведение, используя другие подходы.
Этот метод позволяет получить данные "предыдущей" или "следующей" записи, соединяя таблицу саму с собой по определенным условиям. Это эффективный способ для эмуляции поведения оконных функций в запросах 1С.
Подготовка данных: Убедитесь, что у вас есть уникальный ключ или набор полей, который однозначно определяет порядок строк для каждой группы, по которой вы хотите сравнивать "предыдущую" и "текущую" запись. В вашем случае, это могут быть "Исполнитель", "Дата", "Заявка" или "ПериодРасходов". Важно, чтобы была возможность определить "предыдущую" запись по порядку.
Создание временной таблицы с упорядочиванием: На первом шаге создадим временную таблицу с исходными данными, добавив к ней поле, которое будет использоваться для упорядочивания. Если для сравнения нужна уникальная дата или порядковый номер, мы можем его сформировать.
ПОМЕСТИТЬ ВТ_ДанныеСПорядком ИЗ
(ВЫБРАТЬ
ВТ_Общ.Исполнитель КАК Исполнитель,
ВТ_Общ.Дата КАК Дата,
ВТ_Общ.Заявка КАК Заявка,
ВТ_Общ.РасходНаТопливо КАК РасходНаТопливо,
ВТ_Общ.КомпенсацияЗаТопливо КАК КомпенсацияЗаТопливо,
ВТ_Общ.СуммаКВыплате2 КАК СуммаКВыплате2,
// ... другие поля
ВТ_Общ.Дата КАК ДатаДляСравнения // или другое поле для упорядочивания
ИЗ
ВТ_Общ КАК ВТ_Общ) КАК ИсходныеДанные
;
Самосоединение для получения "предыдущей" записи: Теперь мы можем соединить эту временную таблицу саму с собой, чтобы получить данные "предыдущей" записи по определенным критериям.
ВЫБРАТЬ
Тек.Исполнитель,
Тек.Дата,
Тек.Заявка,
Тек.РасходНаТопливо КАК РасходНаТопливоТекущий,
Пред.Дата КАК ДатаПредыдущая,
Пред.РасходНаТопливо КАК РасходНаТопливоПредыдущий,
Пред.Заявка КАК ЗаявкаПредыдущая,
Тек.КомпенсацияЗаТопливо КАК КомпенсацияЗаТопливоТекущая,
Пред.КомпенсацияЗаТопливо КАК КомпенсацияЗаТопливоПредыдущая,
Тек.СуммаКВыплате2 КАК СуммаКВыплатеТекущая
// ... другие поля, которые нужно сравнить
ИЗ
ВТ_ДанныеСПорядком КАК Тек
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ДанныеСПорядком КАК Пред
ПО Тек.Исполнитель = Пред.Исполнитель
И (Тек.Дата = ДОБАВИТЬКДАТЕ(Пред.Дата, ДЕНЬ, 1)) // Если сравниваем по дате
// Или по другому полю, если есть уникальный номер строки или другой ключ для сравнения
// ИЛИ: Если нужно предыдущее значение в рамках дня, но по другому критерию,
// например, по уникальному идентификатору записи, отсортированному по времени
// В данном случае, логика может быть сложнее, требуя промежуточных ВТ с ROW_NUMBER (эмулированным)
Важные моменты при самосоединении:
Условия соединения: Условия должны точно определять, что является "предыдущей" записью. Это может быть Дата + 1 ДЕНЬ для получения предыдущего дня, или, если есть уникальный порядковый номер, НомерСтрокиТекущий = НомерСтрокиПредыдущий + 1.
Уникальность и порядок: Для корректной работы этого метода крайне важна уникальность и последовательность данных. Если по Исполнителю и Дате может быть несколько записей, то вам понадобится дополнительное поле для упорядочивания (например, время или уникальный идентификатор строки, полученный с помощью генерации). Если нет возможности однозначно определить "предыдущую" строку, этот метод становится сложнее.
Производительность: Самосоединения могут быть ресурсоемкими на больших объемах данных. Оптимизируйте временные таблицы и условия соединения.
Разбиение сложной логики на несколько простых шагов, каждый из которых формирует отдельную временную таблицу, позволяет управлять сложностью и отлаживать каждый этап отдельно. Этот метод особенно полезен, когда нужно выполнить несколько агрегаций или промежуточных вычислений.
Первичная агрегация: Сначала агрегируйте данные до нужного уровня, который потребуется для дальнейших расчетов.
ПОМЕСТИТЬ ВТ_АгрегированныеДанные
ИЗ
ВТ_Общ КАК ВТ_Общ
СГРУППИРОВАТЬ ПО
ВТ_Общ.Дата,
ВТ_Общ.Исполнитель
Добавление вычисляемых полей: На следующем шаге, используя результат предыдущей временной таблицы, вычисляйте необходимые поля, возможно, с помощью вложенных запросов или соединений с другими временными таблицами.
Финальное соединение: Затем соедините полученные промежуточные результаты с основным набором данных, если это необходимо для окончательного отчета.
В некоторых случаях, когда логика слишком сложна для реализации в запросе 1С, или когда требуется более гибкий контроль над порядком обработки данных, часть вычислений можно реализовать в коде на встроенном языке 1С после получения данных из запроса. Этот подход менее производителен для больших объемов данных, так как обработка происходит на стороне приложения, а не на стороне СУБД, но позволяет реализовать практически любую логику.
Минимальный запрос: Запрос должен получить все исходные данные, которые понадобятся для расчетов, без сложной логики сравнения соседних строк.
Обход результата запроса: После выполнения запроса, обходите его результат (например, с помощью объекта ВыборкаДетальныхЗаписей или ТаблицаЗначений).
Программные вычисления: В цикле выполняйте необходимые вычисления, используя значения текущей и предыдущей строки, сохраняя предыдущие значения в переменных. При этом вы можете использовать полные возможности встроенного языка 1С.
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
ПредыдущаяДата = Неопределено;
ПредыдущийРасход = Неопределено;
ПредыдущаяЗаявка = Неопределено;
Для Каждого СтрокаТЗ Из РезультатЗапроса Цикл
Если ПредыдущаяДата = СтрокаТЗ.Дата
И ПредыдущийРасход = СтрокаТЗ.РасходНаТопливо
И ПредыдущаяЗаявка <> СтрокаТЗ.Заявка Тогда
// Вычисляем что-то, используя СтрокаТЗ и сохраненные Предыдущая... значения
СтрокаТЗ.СуммаКВыплате2 = СтрокаТЗ.СуммаКВыплате2 + 0; // Пример
Иначе
СтрокаТЗ.СуммаКВыплате2 = СтрокаТЗ.СуммаКВыплате2 + СтрокаТЗ.КомпенсацияЗаТопливо;
КонецЕсли;
ПредыдущаяДата = СтрокаТЗ.Дата;
ПредыдущийРасход = СтрокаТЗ.РасходНаТопливо;
ПредыдущаяЗаявка = СтрокаТЗ.Заявка;
КонецЦикла;
Этот подход наименее предпочтителен для производительности, но является универсальным, когда логика слишком сложна для запроса.
Столкнувшись с ошибкой "Синтаксическая ошибка" в запросе 1С, особенно в сложных конструкциях, мы можем использовать следующие приемы для быстрой локализации и исправления проблемы:
Используйте консоль запросов: Консоль запросов в 1С:Предприятии (доступна через стандартные обработки или конфигуратор) — это ваш лучший друг. Она не только покажет текст ошибки, но и укажет на конкретную строку и позицию, где обнаружена синтаксическая ошибка. Это значительно сужает область поиска.
Разбивайте сложный запрос на части: Если запрос большой и содержит множество временных таблиц и соединений, последовательно комментируйте или удаляйте части запроса, начиная с конца, пока ошибка не исчезнет. Это позволит изолировать проблемный участок. Затем проверяйте каждую временную таблицу и каждое соединение отдельно, убеждаясь в их корректности.
Внимательно проверяйте операторы и функции: Убедитесь, что все используемые операторы (СУММА, ВЫБОР, ЕСТЬNULL и т.д.) и функции (ДОБАВИТЬКДАТЕ, НАЧАЛОПЕРИОДА, РАЗНОСТЬДАТ и т.д.) соответствуют синтаксису языка запросов 1С. Помните, что функции СКД не могут использоваться в запросе.
Проверяйте условия группировки (СГРУППИРОВАТЬ ПО): Если в секции ВЫБРАТЬ вашего запроса используются агрегатные функции (например, СУММА, МАКСИМУМ, МИНИМУМ, КОЛИЧЕСТВО), то все неагрегированные поля, которые присутствуют в этой секции, обязательно должны быть перечислены в секции СГРУППИРОВАТЬ ПО. Отсутствие хотя бы одного такого поля приведет к синтаксической ошибке. В вашем примере ошибки в сообщении 13, несмотря на добавление СГРУППИРОВАТЬ ПО, могли быть вызваны либо оставшимися функциями ВычислитьВыражение, либо несоответствием списка полей в ВЫБРАТЬ и СГРУППИРОВАТЬ ПО.
Пример корректной группировки:
ВЫБРАТЬ
ВТ_Общ.Исполнитель КАК Исполнитель,
ВТ_Общ.Дата КАК Дата,
СУММА(ВТ_Общ.СуммаКВыплате2) КАК ОбщаяСуммаКВыплате
ИЗ
ВТ_Общ КАК ВТ_Общ
СГРУППИРОВАТЬ ПО
ВТ_Общ.Исполнитель,
ВТ_Общ.Дата // Все неагрегированные поля из ВЫБРАТЬ
Мы выяснили, что для корректного расчета итогов в СКД для уже агрегированных полей часто достаточно изменить агрегатную функцию на Максимум() или Минимум(). Также мы подробно разобрали, почему функции СКД не могут быть использованы в запросах 1С и какие подходы можно применить для эмуляции сложной логики (например, работы с соседними строками) средствами языка запросов 1С или встроенного языка. Надеемся, что эти рекомендации помогут вам создавать более эффективные и безошибочные отчеты.