При написании запросов к документам в 1С начинающие (и не только) разработчики часто сталкиваются с неприятной особенностью: при попытке соединить основную таблицу документа сразу с несколькими его табличными частями суммы начинают неконтролируемо расти. Вместо ожидаемого итога по товарам или оплатам запрос выдает значения, увеличенные в несколько раз. Разберемся, почему это происходит и как правильно составить запрос, чтобы получить корректные данные.
Прежде чем переходить к коду, проанализируем ситуацию. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ основной таблицы документа с его табличной частью, SQL-сервер создает по одной строке для каждой строки из табличной части. Если мы добавляем второе ЛЕВОЕ СОЕДИНЕНИЕ с другой табличной частью, происходит так называемое умножение множеств или декартово произведение.
Например, если в документе "Отчет о розничных продажах" есть 5 строк в таблице Товары и 2 строки в таблице ОплатаПлатежнымиКартами, то при простом соединении общее количество строк для этого документа в результате запроса станет равным 10 (5 * 2). Соответственно, когда мы применяем функцию СУММА(), итоги по первой таблице удвоятся, а по второй — умножатся на пять. Чем больше строк в каждой таблице, тем сильнее будет искажен результат.
Рассмотрим подход, который часто выбирают разработчики для обеспечения чистоты данных и удобства отладки. Суть метода заключается в том, чтобы сначала сгруппировать данные по каждой табличной части отдельно во временных таблицах, а затем соединить эти готовые итоги с основной таблицей.
Разберем по шагам, как это реализовать:
Ссылка.Ссылка.Ссылка.Посмотрим на пример кода, реализующего эту логику:
// 1. Получаем итоги по товарам
ВЫБРАТЬ
ОтчетТовары.Ссылка КАК Ссылка,
СУММА(ОтчетТовары.Сумма) КАК СуммаТовары
ПОМЕСТИТЬ ВТ_ИтогиТовары
ИЗ
Документ.ОтчетОРозничныхПродажах.Товары КАК ОтчетТовары
ГДЕ
ОтчетТовары.Ссылка.Проведен
СГРУППИРОВАТЬ ПО
ОтчетТовары.Ссылка;
// 2. Получаем итоги по оплатам
ВЫБРАТЬ
ОтчетОплаты.Ссылка КАК Ссылка,
СУММА(ОтчетОплаты.Сумма) КАК СуммаОплаты
ПОМЕСТИТЬ ВТ_ИтогиОплаты
ИЗ
Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами КАК ОтчетОплаты
ГДЕ
ОтчетОплаты.Ссылка.Проведен
СГРУППИРОВАТЬ ПО
ОтчетОплаты.Ссылка;
// 3. Соединяем результаты
ВЫБРАТЬ
Отчет.Ссылка КАК Ссылка,
Отчет.Номер КАК Номер,
Отчет.Дата КАК Дата,
ЕСТЬNULL(ВТ_ИтогиТовары.СуммаТовары, 0) КАК ИтогТовары,
ЕСТЬNULL(ВТ_ИтогиОплаты.СуммаОплаты, 0) КАК ИтогОплаты
ИЗ
Документ.ОтчетОРозничныхПродажах КАК Отчет
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИтогиТовары КАК ВТ_ИтогиТовары
ПО Отчет.Ссылка = ВТ_ИтогиТовары.Ссылка
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ИтогиОплаты КАК ВТ_ИтогиОплаты
ПО Отчет.Ссылка = ВТ_ИтогиОплаты.Ссылка
ГДЕ
Отчет.Проведен;
Важный нюанс: При использовании временных таблиц крайне рекомендуется индексировать поле Ссылка в таблицах ПОМЕСТИТЬ ... ИНДЕКСИРОВАТЬ ПО .... Это значительно ускорит выполнение ЛЕВОГО СОЕДИНЕНИЯ в финальной части запроса, особенно если объем данных велик.
Этот метод считается более профессиональным с точки зрения производительности SQL-сервера. Вместо того чтобы "раздувать" запрос в ширину (через соединения), мы "растим" его в высоту, объединяя выборки из разных табличных частей. Это позволяет избежать создания промежуточных физических структур в базе tempdb.
Алгоритм действий следующий:
ОБЪЕДИНИТЬ ВСЕ) выбираем ту же ссылку и итоги по второй табличной части, подставляя 0 в поля для первой части.Проанализируем пример такого запроса:
ВЫБРАТЬ
Итоги.Ссылка КАК Ссылка,
СУММА(Итоги.СуммаТовары) КАК СуммаТовары,
СУММА(Итоги.СуммаОплаты) КАК СуммаОплаты
ИЗ (
ВЫБРАТЬ
ТчТовары.Ссылка КАК Ссылка,
ТчТовары.Сумма КАК СуммаТовары,
0 КАК СуммаОплаты
ИЗ
Документ.ОтчетОРозничныхПродажах.Товары КАК ТчТовары
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТчОплаты.Ссылка,
0,
ТчОплаты.Сумма
ИЗ
Документ.ОтчетОРозничныхПродажах.ОплатаПлатежнымиКартами КАК ТчОплаты
) КАК Итоги
СГРУППИРОВАТЬ ПО
Итоги.Ссылка
Этот подход гарантирует, что ни одна сумма не будет умножена, так как данные из разных таблиц физически находятся в разных строках до финальной группировки.
Выясним причину возможных проблем при масштабировании этих решений. Если вы создаете универсальный реестр документов (как автор темы), ваш запрос может разрастись до сотен строк. Вот на что стоит обратить внимание:
1. Неявные соединения (проблема "точки"): Старайтесь избегать обращений вида ТабличнаяЧасть.Ссылка.Дата в блоке ГДЕ. Это создает неявное соединение с основной таблицей документа для каждой строки ТЧ. Правильнее сначала отобрать ссылки документов во временную таблицу по нужным критериям (дата, проведенность), а затем соединять ТЧ с этой компактной таблицей.
2. Использование Системы компоновки данных (СКД): Если задача позволяет, лучше использовать СКД. В ней можно создать несколько наборов данных (по одному на каждую табличную часть) и связать их на вкладке "Связи наборов данных". СКД самостоятельно выполнит объединение и расчет ресурсов без риска дублирования строк.
3. Лимиты таблиц: Помните, что у MS SQL Server есть лимит на количество таблиц в одном запросе (обычно 256). При программной генерации сложного отчета по многим видам документов с множеством табличных частей этот лимит можно превысить. В таких случаях разбивайте один огромный запрос на несколько этапов с использованием временных таблиц.
4. Обработка пустых значений: Всегда используйте функцию ЕСТЬNULL() при работе с левыми соединениями. Если в документе заполнена таблица товаров, но нет оплат картами, без этой функции вы можете получить NULL в итогах, что приведет к ошибкам в дальнейших расчетах.
Подведем итог: выбор между ВТ и ОБЪЕДИНИТЬ ВСЕ часто зависит от предпочтений разработчика и сложности динамической генерации текста запроса. Временные таблицы нагляднее и проще в отладке, в то время как объединение запросов может показать лучшую производительность на экстремальных объемах данных.