При разработке отчетов в системе 1С:Предприятие разработчики часто сталкиваются с классической проблемой реляционных баз данных — задвоением, затроением или умножением суммовых показателей при использовании левого соединения. Эта ситуация возникает, когда мы пытаемся соединить две таблицы, между которыми существует связь «один ко многим».
Давайте проанализируем типичный пример. У нас есть две таблицы: таблица приходов и таблица расходов. В обеих таблицах присутствует ключевое поле — НомерТрубы. На один приход по конкретной трубе может приходиться несколько расходов. Если мы соединяем эти таблицы по полю НомерТрубы через ЛЕВОЕ СОЕДИНЕНИЕ, то для каждой строки расхода строка прихода будет продублирована. В результате стандартное суммирование по колонке прихода выдаст некорректный, многократно завышенный итог.
В этой статье мы подробно разберем природу этой проблемы и рассмотрим четыре практических способа ее решения: от классического перестроения запроса до использования современных возможностей платформы 1С.
Рассмотрим детально, как работает СУБД при левом соединении. Представим, что по трубе №123 у нас была одна приходная накладная на сумму 10 000 рублей. Позже эту трубу расходовали частями, и у нас зафиксировано три расходных документа на суммы 3 000, 4 000 и 3 000 рублей.
Когда мы выполняем запрос с левым соединением таблицы прихода с таблицей расхода по номеру трубы, СУБД строит результирующую выборку, которая на физическом уровне будет содержать три строки:
При попытке рассчитать итог по приходу функция СУММА(СуммаПриход) послушно сложит значения из всех трех строк и вернет результат в 30 000 рублей вместо реальных 10 000. В то же время сумма расхода будет рассчитана абсолютно верно (10 000 рублей).
Выяснив причину проблемы, перейдем к методам ее решения.
Самым эффективным и производительным решением в среде 1С является отказ от горизонтального соединения таблиц в пользу их вертикального объединения с последующей группировкой. Вместо того чтобы сопоставлять строки бок о бок, мы помещаем их друг под друга, разводя числовые показатели по разным колонкам.
При таком подходе в таблице прихода колонка расхода будет принудительно заполнена нулями, а в таблице расхода нулями будет заполнена колонка прихода. Затем мы сгруппируем результат по ключевому полю.
Рассмотрим по шагам, как выглядит такой запрос:
ВЫБРАТЬ
ТрубыСводно.НомерТрубы КАК НомерТрубы,
СУММА(ТрубыСводно.СуммаПриход) КАК СуммаПриход,
СУММА(ТрубыСводно.СуммаРасход) КАК СуммаРасход
ИЗ
(ВЫБРАТЬ
Приход.НомерТрубы КАК НомерТрубы,
Приход.Сумма КАК СуммаПриход,
0 КАК СуммаРасход
ИЗ
ТаблицаПриход КАК Приход
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Расход.НомерТрубы КАК НомерТрубы,
0 КАК СуммаПриход,
Расход.Сумма КАК СуммаРасход
ИЗ
ТаблицаРасход КАК Расход) КАК ТрубыСводно
СГРУППИРОВАТЬ ПО
ТрубыСводно.НомерТрубы
Преимущества данного метода:
ОБЪЕДИНИТЬ ВСЕ выполняется СУБД гораздо быстрее, чем ЛЕВОЕ СОЕДИНЕНИЕ, так как не требует построения тяжелых хэш-таблиц для сопоставления строк в оперативной памяти.ОБЪЕДИНИТЬ ВСЕ с соответствующим обнулением колонок.Если вы разрабатываете отчет на базе Системы компоновки данных (СКД), то вы можете переложить задачу сопоставления данных с плеч СУБД на саму СКД. Для этого мы не будем соединять таблицы внутри одного запроса, а создадим два независимых набора данных.
Выполним настройку по следующему алгоритму:
НомерТрубы и СуммаПриход.НомерТрубы и СуммаРасход.НаборДанных1, приемником — НаборДанных2, а в качестве условия соединения (выражение источника и приемника) выберем поле НомерТрубы.При формировании отчета СКД выполнит два абсолютно независимых запроса к базе данных. На уровне СУБД никакого соединения таблиц происходить не будет, а значит, физические строки прихода не продублируются. Компоновщик данных соединит эти наборы уже в оперативной памяти компьютера в момент вывода отчета. Суммирование ресурсов при этом отработает корректно по каждому набору в отдельности.
Бывают ситуации, когда запрос отчета является комплексным, изменять его структуру нежелательно, и данные в СКД поступают уже в соединенном (и, как следствие, задвоенном) виде. Даже в этом случае проблему можно решить без изменения текста запроса — с помощью правильной настройки ресурсов СКД.
Обычно разработчики настраивают ресурс для поля прихода простым выражением Сумма(СуммаПриход). Чтобы обойти задвоение, мы настроим расчет ресурса индивидуально для разных уровней группировки отчета:
НомерТрубы выражение ресурса должно быть определено как Максимум(СуммаПриход) или Среднее(СуммаПриход). Поскольку в рамках одной трубы сумма прихода везде продублирована одинаково, выбор максимального значения вернет нам исходную корректную сумму.Сумма(ВычислитьВыражениеСГруппировкойМассив("Максимум(СуммаПриход)", "НомерТрубы"))
Этот способ незаменим, когда в отчете необходимо сохранить вывод детальных записей по расходам, но при этом иметь абсолютно верные итоговые показатели приходов на верхних уровнях иерархии.
Начиная с версии платформы 8.3.20, разработчикам 1С стала доступна поддержка оконных функций в языке запросов. Это позволяет реализовать изящное решение: пронумеровать строки расходов внутри каждой трубы и выводить сумму прихода только для первой строки расхода, а для всех последующих строк принудительно выводить 0.
Проанализируем пример запроса с использованием оконной функции ROW_NUMBER() (в синтаксисе 1С — АВТОНОМЕРЗАПИСИ()):
ВЫБРАТЬ
Соединение.НомерТрубы КАК НомерТрубы,
// Если это первая строка для данной трубы — выводим сумму прихода, иначе выводим 0
ВЫБОР
КОГДА Соединение.НомерСтрокиВТрубе = 1
ТОГДА Соединение.СуммаПриход
ИНАЧЕ 0
КОНЕЦ КАК СуммаПриходОбнуленная,
Соединение.СуммаРасход КАК СуммаРасход
ИЗ
(ВЫБРАТЬ
Приход.НомерТрубы КАК НомерТрубы,
Приход.Сумма КАК СуммаПриход,
Расход.Сумма КАК СуммаРасход,
// Нумеруем строки расхода индивидуально для каждой трубы
ROW_NUMBER() OVER (PARTITION BY Приход.НомерТрубы ПОДЧИНЕННЫЙ ПОРЯДОК ПО Расход.Ссылка) КАК НомерСтрокиВТрубе
ИЗ
ТаблицаПриход КАК Приход
ЛЕВОЕ СОЕДИНЕНИЕ ТаблицаРасход КАК Расход
ПО Приход.НомерТрубы = Расход.НомерТрубы) КАК Соединение
Как это работает:
В подзапросе мы выполняем левое соединение. Конструкция ROW_NUMBER() OVER (PARTITION BY Приход.НомерТрубы...) разбивает результирующий набор на группы (секции) по полю НомерТрубы и внутри каждой группы нумерует строки по порядку. В основном запросе с помощью условного оператора ВЫБОР КОГДА ... мы обнуляем сумму прихода для всех строк, у которых порядковый номер внутри группы больше 1.
В результате на уровне детальных записей сумма прихода отобразится только один раз (напротив первой расходной накладной), а во всех последующих строках будет стоять 0. При этом итоговая сумма по всему отчету посчитается абсолютно корректно при обычном суммировании.
Мы рассмотрели различные подходы к решению проблемы задвоения сумм. Выбор конкретного метода зависит от архитектуры вашего решения: