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