При разработке отчетов в Системе компоновки данных (СКД) программисты часто сталкиваются с необходимостью объединения данных из различных таблиц. Типичный пример — связь основной таблицы бизнес-процесса (или его табличной части) с подчиненными задачами для получения дат выполнения определенных этапов. При реализации подобных связей легко допустить неочевидные ошибки, которые приводят либо к потере важных записей, либо к лавинообразному дублированию строк и искажению итоговых показателей отчета.
В этой статье мы подробно разберем, как правильно связывать таблицы левым соединением в запросах 1С, почему наложение условий в секции ГДЕ может сломать логику выборки, как бороться с дублями строк и как правильно выводить даты по нескольким различным точкам маршрута бизнес-процесса.
Представим стандартную задачу: нам необходимо вывести список всех документов (или бизнес-процессов) определенного вида, и, если по ним были созданы задачи на определенной точке маршрута, вывести дату создания этих задач. Если задачи на этой точке еще нет, документ все равно должен отображаться в отчете, а поле с датой задачи должно остаться пустым (значение NULL).
Для решения этой задачи логично использовать ЛЕВОЕ СОЕДИНЕНИЕ, где левой (главной) таблицей выступает наш документ/бизнес-процесс, а правой (подчиненной) — таблица задач. Однако часто разработчики пишут запрос следующим образом:
ВЫБРАТЬ
Закупки.Ссылка КАК Ссылка,
Закупки.Номер КАК Номер,
Закупки.Дата КАК Дата,
УниверсальнаяЗадача.Дата КАК ДатаЗадачи
ИЗ
БизнесПроцесс.ЗакупкиКомпьютернойТехникиИМебели.Продукция КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК УниверсальнаяЗадача
ПО Закупки.Ссылка = УниверсальнаяЗадача.БизнесПроцесс
ГДЕ
УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута
Запустив такой запрос, мы обнаружим, что из базы данных выбрались далеко не все бизнес-процессы. Пропали те процессы, которые еще не дошли до указанной точки маршрута или по которым задача еще не создана. Почему так произошло, ведь мы явно указали ЛЕВОЕ СОЕДИНЕНИЕ?
Причина кроется в работе СУБД и платформы 1С. Когда мы накладываем условие фильтрации на поля правой (подчиненной) таблицы в секции ГДЕ, СУБД сначала выполняет левое соединение (при котором для отсутствующих задач возвращается значение NULL), а затем применяет фильтр из секции ГДЕ.
Условие УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута не может быть выполнено для записей, где задача отсутствует, так как сравнение NULL = &ТочкаМаршрута возвращает ложь (или UNKNOWN). В результате все строки, для которых левое соединение не нашло соответствия, безжалостно отсекаются. Наше левое соединение фактически превратилось во внутреннее соединение.
Правильное решение: перенести все условия, накладываемые на правую таблицу, непосредственно в секцию описания связи ПО. В этом случае СУБД сначала отфильтрует саму таблицу задач по нужной точке маршрута, и только после этого выполнит левое соединение с результатом. Запрос примет следующий вид:
ВЫБРАТЬ
Закупки.Ссылка КАК Ссылка,
Закупки.Номер КАК Номер,
Закупки.Дата КАК Дата,
УниверсальнаяЗадача.Дата КАК ДатаЗадачи
ИЗ
БизнесПроцесс.ЗакупкиКомпьютернойТехникиИМебели.Продукция КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК УниверсальнаяЗадача
ПО Закупки.Ссылка = УниверсальнаяЗадача.БизнесПроцесс
И УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута
Теперь запрос отработает корректно: мы получим абсолютно все записи бизнес-процессов, а для тех из них, где задача по указанной точке маршрута отсутствует, в поле ДатаЗадачи вернется пустое значение (NULL).
Исправив предыдущую ошибку, мы можем столкнуться с другой проблемой: количество записей в отчете внезапно превысило реальное количество документов в базе данных. Например, у нас всего 236 бизнес-процессов, а отчет выводит 255 строк.
Проанализируем причину этой проблемы. Бизнес-процессы в реальной жизни редко движутся строго линейно. Зачастую на определенном этапе задача может быть отклонена исполнителем или отправлена на доработку. В этом случае бизнес-процесс возвращается на предыдущую точку маршрута, и система создает по ней **новую задачу**.
В результате в базе данных для одного и того же бизнес-процесса на одной и той же точке маршрута может быть создано две или более задач (например, первая отклоненная и вторая актуальная). При левом соединении по условию соответствия ссылки СУБД сопоставит одну строку бизнес-процесса со всеми найденными задачами. Строка документа продублируется столько раз, сколько задач было создано по этой точке.
Это критическая ошибка для отчетов, так как числовые показатели (сумма, количество продукции) в продублированных строках будут просуммированы повторно, что приведет к искажению итогов.
Давайте рассмотрим, как решить эту проблему.
Если бизнес-требования отчета диктуют, что в случае наличия нескольких задач нам необходимо получить дату самой последней из них (наиболее актуальной), мы можем применить группировку с использованием агрегатной функции МАКСИМУМ.
Однако просто сгруппировать весь результирующий запрос — не самое лучшее решение. Если мы добавим секцию СГРУППИРОВАТЬ ПО в основной запрос, нам придется перечислить в ней все выбираемые поля (номенклатуру, цены, автора, контрагента и т.д.), что негативно скажется на производительности СУБД.
Гораздо эффективнее использовать механизм **Временных Таблиц (ВТ)**. Мы предварительно выберем уникальные задачи, сгруппируем их по бизнес-процессам, найдем максимальную дату и поместим этот результат во временную таблицу. Затем свяжем ее с основной таблицей.
Разберем этот алгоритм по шагам в коде запроса:
// Шаг 1: Получаем список уникальных бизнес-процессов и дат последних задач по нужной точке
ВЫБРАТЬ
УниверсальнаяЗадача.БизнесПроцесс КАК БизнесПроцесс,
МАКСИМУМ(УниверсальнаяЗадача.Дата) КАК МаксДатаЗадачи
ПОМЕСТИТЬ ВТ_ПоследниеЗадачи
ИЗ
Задача.УниверсальнаяЗадача КАК УниверсальнаяЗадача
ГДЕ
УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута
СГРУППИРОВАТЬ ПО
УниверсальнаяЗадача.БизнесПроцесс
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Выбираем основные данные и соединяем их с подготовленной временной таблицей
ВЫБРАТЬ
Закупки.Ссылка.Автор КАК Автор,
Закупки.Ссылка.Номер КАК Номер,
Закупки.Ссылка.Дата КАК Дата,
Закупки.Номенклатура КАК Номенклатура,
Закупки.Количество КАК Количество,
Закупки.Сумма КАК Сумма,
ЕСТЬNULL(ВТ_ПоследниеЗадачи.МаксДатаЗадачи, ДАТАВРЕМЯ(1, 1, 1)) КАК ДатаЗадачи
ИЗ
БизнесПроцесс.ЗакупкиКомпьютернойТехникиИМебели.Продукция КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеЗадачи КАК ВТ_ПоследниеЗадачи
ПО Закупки.Ссылка = ВТ_ПоследниеЗадачи.БизнесПроцесс
Благодаря такому подходу мы гарантированно избегаем дублирования записей, так как временная таблица содержит строго одну запись на один бизнес-процесс. При этом нагрузка на СУБД минимизируется.
В некоторых случаях мы можем избавиться от дублей, добавив в условия связи проверку на состояние задачи. Например, если в процессе работы может быть только одна успешно выполненная задача по этой точке маршрута, мы можем указать это свойство в соединении:
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК УниверсальнаяЗадача
ПО Закупки.Ссылка = УниверсальнаяЗадача.БизнесПроцесс
И УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута
И УниверсальнаяЗадача.Выполнена = ИСТИНА
Однако данный способ не всегда гарантирует отсутствие дублей на 100% (например, если задачу выполняли и возвращали несколько раз, выполненных задач тоже может оказаться несколько). Поэтому использование группировки во временной таблице (Решение 1) является более надежным архитектурным стандартом разработки в 1С.
Часто перед разработчиком встает задача вывести в одной строке отчета даты прохождения сразу нескольких ключевых точек бизнес-процесса (например, дату согласования инициатором и дату согласования бухгалтером).
Попытка объединить эти условия через логическое ИЛИ в одном соединении приведет к тому, что строки опять начнут размножаться, так как для одного бизнес-процесса найдутся сразу две разные задачи:
// ОШИБОЧНЫЙ ВАРЯНТ (приведет к дублированию строк БП)
ПО Закупки.Ссылка = УниверсальнаяЗадача.БизнесПроцесс
И (УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута1 ИЛИ УниверсальнаяЗадача.ТочкаМаршрута = &ТочкаМаршрута2)
Как сделать правильно? Для получения данных по разным точкам маршрута в отдельные колонки отчета нам необходимо выполнить несколько левых соединений к одной и той же таблице задач, используя при этом уникальные псевдонимы для каждой присоединяемой таблицы.
Давайте посмотрим на пример реализации такого запроса:
ВЫБРАТЬ
Закупки.Ссылка.Автор КАК Автор,
Закупки.Ссылка.Номер КАК Номер,
Закупки.Ссылка.Дата КАК Дата,
Закупки.Номенклатура КАК Номенклатура,
Закупки.Сумма КАК Сумма,
ЗадачаИнициатора.Дата КАК ДатаСогласованияИнициатором,
ЗадачаБухгалтера.Дата КАК ДатаСогласованияБухгалтером
ИЗ
БизнесПроцесс.ЗакупкиКомпьютернойТехникиИМебели.Продукция КАК Закупки
// Первое соединение для получения даты первой точки маршрута
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК ЗадачаИнициатора
ПО Закупки.Ссылка = ЗадачаИнициатора.БизнесПроцесс
И ЗадачаИнициатора.ТочкаМаршрута = &ТочкаМаршрутаИнициатор
// Второе соединение (к той же таблице, но с другим псевдонимом) для второй точки
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК ЗадачаБухгалтера
ПО Закупки.Ссылка = ЗадачаБухгалтера.БизнесПроцесс
И ЗадачаБухгалтера.ТочкаМаршрута = &ТочкаМаршрутаБухгалтер
Обратите внимание на использование уникальных псевдонимов таблиц: ЗадачаИнициатора и ЗадачаБухгалтера. Это позволяет СУБД четко разделить логику связей и вывести две независимые даты в разные колонки одной строки отчета.
Допустим, по бизнес-логике нам нужно рассчитать вычисляемое поле: Дата Поступления = Дата Задачи + Плановый Срок (в днях).
Если мы используем СКД, расчет этого значения обычно переносится в вычисляемые поля или ресурсы. Но что произойдет, если задача еще не создана и левое соединение вернуло для даты значение NULL?
Если мы попытаемся сложить NULL с числом или использовать его в функциях работы с датами, результат операции также будет равен NULL. Платформа 1С корректно обрабатывает такие ситуации и не выдает критических ошибок в процессе формирования отчета — в ячейке просто отобразится пустое значение.
Однако если вы выполняете сложение непосредственно в тексте запроса с помощью функции ДОБАВИТЬКДАТЕ, хорошим тоном будет подстраховаться от непредвиденных результатов с помощью конструкции ВЫБОР КОГДА ... ТОГДА или функции ЕСТЬNULL.
Рассмотрим пример безопасного вычисления даты непосредственно внутри запроса:
ВЫБРАТЬ
Закупки.Ссылка КАК Ссылка,
ВЫБОР
КОГДА ЗадачаИнициатора.Дата ЕСТЬ НЕ NULL И Закупки.Ссылка.ПлановыйСрок > 0
ТОГДА ДОБАВИТЬКДАТЕ(ЗадачаИнициатора.Дата, ДЕНЬ, Закупки.Ссылка.ПлановыйСрок)
ИНАЧЕ ДАТАВРЕМЯ(1, 1, 1)
КОНЕЦ КАК ПлановаяДатаПоступления
ИЗ
БизнесПроцесс.ЗакупкиКомпьютернойТехникиИМебели.Продукция КАК Закупки
ЛЕВОЕ СОЕДИНЕНИЕ Задача.УниверсальнаяЗадача КАК ЗадачаИнициатора
ПО Закупки.Ссылка = ЗадачаИнициатора.БизнесПроцесс
И ЗадачаИнициатора.ТочкаМаршрута = &ТочкаМаршрутаИнициатор
В данном примере, если задача еще не создана, поле ПлановаяДатаПоступления вернет пустую дату 01.01.0001, что позволит избежать некорректного поведения при дальнейшей сортировке или расчетах.
Поскольку наш отчет разрабатывается на Системе компоновки данных, мы можем использовать еще один мощный инструмент платформы — Связи наборов данных на уровне самой СКД. Это позволяет не перегружать один единственный запрос многочисленными левыми соединениями.
Проанализируем этот подход подробнее:
Ссылка = БизнесПроцесс&ТочкаМаршрута в подчиненный набор данных.Такой подход гарантирует, что СКД самостоятельно соединит данные на уровне результирующего вывода. При этом итоговые ресурсы (суммы, количества) по главному набору никогда не будут удваиваться или утраиваться, даже если в подчиненном наборе данных по какой-то причине окажется несколько строк. Это делает разработку архитектурно чистой и защищает отчет от математических ошибок в расчетах итогов.