При разработке сложных отчетов и обработок в 1С часто возникает необходимость собрать в одну итоговую таблицу данные из различных регистров: остатки, движения, обороты за период, сопоставив их с календарем дат. Когда мы пытаемся объединить три и более источника по составному ключу (например, Дата, Склад, Номенклатура), мы сталкиваемся с проблемой: если в одной из таблиц за определенный день нет движений по конкретной номенклатуре, в итоговом результате появляются пустые значения NULL, а ключевые поля дублируются или теряются.
Проанализируем типичную ошибку при использовании цепочки полных соединений, разберем причины некорректного поведения выборки и рассмотрим надежные способы сведения нескольких таблиц в единый результат.
Рассмотрим распространенный подход: разработчик получает несколько временных таблиц (например, Календарь, ДвиженияТоплива, ОстаткиТоплива, Перемещения) и пытается объединить их последовательными конструкциями ПОЛНОЕ СОЕДИНЕНИЕ:
ВЫБРАТЬ
Календарь.ДатаКалендаря,
ДвиженияТоплива.Номенклатура,
ДвиженияТоплива.Склад,
ОстаткиТоплива.ОстатокН,
ДвиженияТоплива.Количество,
Перемещения.Приход,
Перемещения.Расход
ИЗ
Календарь КАК Календарь
ПОЛНОЕ СОЕДИНЕНИЕ ДвиженияТоплива КАК ДвиженияТоплива
ПО Календарь.ДатаКалендаря = ДвиженияТоплива.ДатаКалендаря
ПОЛНОЕ СОЕДИНЕНИЕ ОстаткиТоплива КАК ОстаткиТоплива
ПО Календарь.ДатаКалендаря = ОстаткиТоплива.ДатаКалендаря
И ДвиженияТоплива.Склад = ОстаткиТоплива.Склад
И ДвиженияТоплива.Номенклатура = ОстаткиТоплива.Номенклатура
ПОЛНОЕ СОЕДИНЕНИЕ Перемещения КАК Перемещения
ПО Календарь.ДатаКалендаря = Перемещения.ДатаКалендаря
И ДвиженияТоплива.Склад = Перемещения.Склад
И ДвиженияТоплива.Номенклатура = Перемещения.Номенклатура
Выясним причину, почему такой запрос возвращает некорректные данные:
NULL в условиях соединения: когда во второй таблице (ОстаткиТоплива) или в третьей (Перемещения) есть данные по номенклатуре, которой не было в первой таблице (ДвиженияТоплива), поле ДвиженияТоплива.Номенклатура принимает значение NULL. Сравнение NULL = Перемещения.Номенклатура возвращает значение «Ложь», и строки третьей таблицы не соединяются с остальными данными, образуя паразитные раздельные строки.СУММА) многократно завысят числовые показатели.Если требуется сохранить структуру с соединениями, необходимо корректно извлекать значения ключевых полей из всех участвующих таблиц. Функция ЕСТЬNULL принимает два параметра, однако мы можем вкладывать вызовы функции друг в друга для проверки произвольного количества источников.
Разберем по шагам, как построить выборку полей для трех таблиц:
ВЫБРАТЬ
Календарь.ДатаКалендаря КАК Период,
ЕСТЬNULL(Таблица1.Номенклатура, ЕСТЬNULL(Таблица2.Номенклатура, Таблица3.Номенклатура)) КАК Номенклатура,
ЕСТЬNULL(Таблица1.Склад, ЕСТЬNULL(Таблица2.Склад, Таблица3.Склад)) КАК Склад,
ЕСТЬNULL(Таблица1.Количество, 0) КАК КоличествоДвижений,
ЕСТЬNULL(Таблица2.ОстатокН, 0) КАК Остаток,
ЕСТЬNULL(Таблица3.Приход, 0) КАК Приход,
ЕСТЬNULL(Таблица3.Расход, 0) КАК Расход
ИЗ
Таблица1 КАК Таблица1
ПОЛНОЕ СОЕДИНЕНИЕ Таблица2 КАК Таблица2
ПО Таблица1.Дата = Таблица2.Дата
И Таблица1.Склад = Таблица2.Склад
И Таблица1.Номенклатура = Таблица2.Номенклатура
ПОЛНОЕ СОЕДИНЕНИЕ Таблица3 КАК Таблица3
ПО ЕСТЬNULL(Таблица1.Дата, Таблица2.Дата) = Таблица3.Дата
И ЕСТЬNULL(Таблица1.Склад, Таблица2.Склад) = Таблица3.Склад
И ЕСТЬNULL(Таблица1.Номенклатура, Таблица2.Номенклатура) = Таблица3.Номенклатура
Обратите внимание: условия последующих соединений обязательно должны связываться с вычисленными через ЕСТЬNULL полями предыдущих таблиц, иначе строки с уникальными ключами из второй таблицы потеряют связь с третьей.
Наиболее производительным, надежным и прозрачным подходом для платформы 1С является приведение всех источников к единой структуре колонок с последующей группировкой. Заблуждение о том, что таблицы с разным набором показателей нельзя объединить, легко решается подстановкой нулевых значений для отсутствующих числовых полей.
Рассмотрим реализацию этого метода на практическом примере:
// Помещаем все выборки в единый поток через ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Движения.ДатаКалендаря КАК Период,
Движения.Склад КАК Склад,
Движения.Номенклатура КАК Номенклатура,
Движения.Количество КАК Количество,
0 КАК ОстатокН,
0 КАК Приход,
0 КАК Расход
ПОМЕСТИТЬ ДанныеВсехРегистров
ИЗ
ДвиженияТоплива КАК Движения
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Остатки.ДатаКалендаря,
Остатки.Склад,
Остатки.Номенклатура,
0,
Остатки.ОстатокН,
0,
0
ИЗ
ОстаткиТоплива КАК Остатки
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Перемещения.ДатаКалендаря,
Перемещения.Склад,
Перемещения.Номенклатура,
0,
0,
Перемещения.Приход,
Перемещения.Расход
ИЗ
Перемещения КАК Перемещения
;
// Итоговое сворачивание данных по составному ключу
ВЫБРАТЬ
Данные.Период КАК Период,
Данные.Склад КАК Склад,
Данные.Номенклатура КАК Номенклатура,
СУММА(Данные.Количество) КАК Количество,
СУММА(Данные.ОстатокН) КАК ОстатокН,
СУММА(Данные.Приход) КАК Приход,
СУММА(Данные.Расход) КАК Расход
ИЗ
ДанныеВсехРегистров КАК Данные
СГРУППИРОВАТЬ ПО
Данные.Период,
Данные.Склад,
Данные.Номенклатура
Преимущества такого подхода:
ОБЪЕДИНИТЬ ВСЕ выполняется СУБД линейно и не требует построения сложных планов соединений с проверками на NULL.ОБЪЕДИНИТЬ ВСЕ, не переписывая условия связи.Если логика алгоритма требует выполнения именно левых соединений (например, при получении срезов последних или строгих иерархических выборок), сформируем сначала опорную таблицу уникальных комбинаций ключей:
Дата, Склад, Номенклатура из всех источников с помощью оператора ОБЪЕДИНИТЬ (без слова ВСЕ, чтобы удалить повторяющиеся строки).ЛЕВОЕ СОЕДИНЕНИЕ от таблицы ключей ко всем остальным таблицам данных по полному набору измерений.
ВЫБРАТЬ
Движения.ДатаКалендаря КАК Период,
Движения.Склад КАК Склад,
Движения.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ТаблицаКлючей
ИЗ
ДвиженияТоплива КАК Движения
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Остатки.ДатаКалендаря,
Остатки.Склад,
Остатки.Номенклатура
ИЗ
ОстаткиТоплива КАК Остатки
ОБЪЕДИНИТЬ
ВЫБРАТЬ
Перемещения.ДатаКалендаря,
Перемещения.Склад,
Перемещения.Номенклатура
ИЗ
Перемещения КАК Перемещения
ИНДЕКСИРОВАТЬ ПО
Период,
Склад,
Номенклатура
;
ВЫБРАТЬ
Ключи.Период КАК Период,
Ключи.Склад КАК Склад,
Ключи.Номенклатура КАК Номенклатура,
ЕСТЬNULL(Движения.Количество, 0) КАК Количество,
ЕСТЬNULL(Остатки.ОстатокН, 0) КАК ОстатокН,
ЕСТЬNULL(Перемещения.Приход, 0) КАК Приход,
ЕСТЬNULL(Перемещения.Расход, 0) КАК Расход
ИЗ
ТаблицаКлючей КАК Ключи
ЛЕВОЕ СОЕДИНЕНИЕ ДвиженияТоплива КАК Движения
ПО Ключи.Период = Движения.ДатаКалендаря
И Ключи.Склад = Движения.Склад
И Ключи.Номенклатура = Движения.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ ОстаткиТоплива КАК Остатки
ПО Ключи.Период = Остатки.ДатаКалендаря
И Ключи.Склад = Остатки.Склад
И Ключи.Номенклатура = Остатки.Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ Перемещения КАК Перемещения
ПО Ключи.Период = Перемещения.ДатаКалендаря
И Ключи.Склад = Перемещения.Склад
И Ключи.Номенклатура = Перемещения.Номенклатура
При построении запросов к регистрам обратим внимание на важные правила оптимизации:
РегистрНакопления.ТоварыВРознице.Обороты(&ДатаНач, &ДатаКон, День, Склад = &Склад)), а не накладывать их в секции ГДЕ.ВЫБОР КОГДА &Склад <> Значение(...) ТОГДА Склад = &Склад ИНАЧЕ ИСТИНА КОНЕЦ отключает использование индексов СУБД. При необходимости опционального отбора формируйте текст запроса динамически в коде 1С перед выполнением.