При разработке управленческих и аналитических отчетов в среде 1С программисты часто сталкиваются с ситуацией, когда данные по одной и той же номенклатуре в разных регистрах накопления расходятся. Это может происходить по разным причинам: из-за корректировок записей регистров, ручных операций или особенностей проведения документов по разным видам учета (например, по управленческому учету на складах и по регламентированному учету организаций).
Когда мы пытаемся построить сводный отчет в Системе компоновки данных (СКД), группируя данные по номенклатуре и выводя детальные строки по регистрам, возникает проблема. Если по какому-то из регистров остатка нет вообще, стандартное левое соединение таблиц остатков просто исключит этот регистр из выборки для данной номенклатуры. В итоге мы не увидим строку этого регистра, хотя для наглядности нам крайне важно вывести пустую строку, где будет заполнено только его наименование.
В этой статье мы подробно разберем, как решить эту задачу в СКД. Мы рассмотрим классический и наиболее надежный способ — создание декартова произведения (матрицы комбинаций), изучим альтернативный вариант с объединением запросов, а также поговорим об оптимизации производительности таких отчетов на больших базах данных.
Представим, что у нас есть три регистра: Регистр1, Регистр2 и Регистр3. По определенной номенклатурной позиции остатки есть только в Регистр3. Если мы попытаемся последовательно соединить таблицы остатков левым соединением, начиная с первого регистра, мы получим пустой результат для всей строки, так как левое соединение ожидает наличие ведущей записи.
Даже если мы сначала соберем уникальный список всей номенклатуры, которая есть хотя бы в одном из регистров, а затем начнем левым соединением пристыковывать к ней таблицы остатков каждого регистра, мы столкнемся с проблемой группировки в СКД. Нам нужно получить структуру данных следующего вида:
Номенклатура 1 -> Регистр 1 -> Остаток: [Пусто]
Номенклатура 1 -> Регистр 2 -> Остаток: [Пусто]
Номенклатура 1 -> Регистр 3 -> Остаток: 10 шт.
Если мы просто соединим таблицы в один плоский запрос, где остатки каждого регистра выводятся в отдельные колонки, СКД сгруппирует все в одну строчку. Нам же требуется детальный вывод по строкам (каждому регистру — своя строка). Чтобы гарантировать вывод пустых строк по тем регистрам, где остатков нет, нам нужно применить специальный подход в языке запросов.
---
Это самый гибкий и методологически верный способ. Суть его заключается в следующем: мы принудительно формируем виртуальную «сетку» или матрицу, в которой каждая номенклатура, имеющаяся в базе, связывается с абсолютно каждым интересующим нас регистром. И уже к этой готовой сетке мы левым соединением прикрепляем реальные остатки.
Давайте разберем этот алгоритм по шагам на примере реального запроса.
Шаг 1. Собираем уникальный список номенклатуры
Для начала нам нужно получить список всей номенклатуры, по которой есть хоть какие-то остатки в любом из проверяемых регистров. Поместим этот список во временную таблицу СписокНоменклатуры:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Рег1.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ СписокНоменклатуры
ИЗ
РегистрНакопления.Рег1.Остатки КАК Рег1
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ
Рег2.Номенклатура
ИЗ
РегистрНакопления.Рег2.Остатки КАК Рег2
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ
Рег3.Номенклатура
ИЗ
РегистрНакопления.Рег3.Остатки КАК Рег3
;
Шаг 2. Создаем статичную таблицу с именами регистров
Теперь нам нужно создать временную таблицу, которая будет содержать только текстовые наименования наших регистров. Это позволит нам в дальнейшем использовать их как аналитическое измерение:
ВЫБРАТЬ
"Регистр 1" КАК НаимРег
ПОМЕСТИТЬ СписокРегистров
ОБЪЕДИНИТЬ
ВЫБРАТЬ
"Регистр 2"
ОБЪЕДИНИТЬ
ВЫБРАТЬ
"Регистр 3"
;
Шаг 3. Получаем декартово произведение (Cross Join)
Теперь мы перемножаем таблицу номенклатуры на таблицу регистров. В языке запросов 1С для получения перекрестного (декартова) соединения достаточно перечислить таблицы в секции ИЗ через запятую, не указывая никаких условий связи в секции ПО. СУБД автоматически сопоставит каждую строку первой таблицы с каждой строкой второй таблицы:
ВЫБРАТЬ
СписокНоменклатуры.Номенклатура КАК Номенклатура,
СписокРегистров.НаимРег КАК НаимРег
ПОМЕСТИТЬ НомИСписокРег
ИЗ
СписокНоменклатуры КАК СписокНоменклатуры,
СписокРегистров КАК СписокРегистров
;
На выходе мы получили идеальную координатную сетку. Если у нас в списке было 10 товаров и 3 регистра, таблица НомИСписокРег будет содержать ровно 30 строк.
Шаг 4. Подготовка таблицы реальных остатков
Сведем все реальные остатки по регистрам в одну общую временную таблицу с помощью объединения:
ВЫБРАТЬ
"Регистр 1" КАК НаимРег,
Рег1.Номенклатура КАК Номенклатура,
Рег1.КоличествоОстаток КАК Количество,
Рег1.СуммаОстаток КАК Сумма
ПОМЕСТИТЬ ВТОстатки
ИЗ
РегистрНакопления.Рег1.Остатки КАК Рег1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регистр 2",
Рег2.Номенклатура,
Рег2.КоличествоОстаток,
Рег2.СуммаОстаток
ИЗ
РегистрНакопления.Рег2.Остатки КАК Рег2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регистр 3",
Рег3.Номенклатура,
Рег3.КоличествоОстаток,
Рег3.СуммаОстаток
ИЗ
РегистрНакопления.Рег3.Остатки КАК Рег3
;
Шаг 5. Финальное левое соединение
На последнем этапе мы берем нашу координатную сетку НомИСписокРег и левым соединением подключаем к ней таблицу реальных остатков ВТОстатки по двум полям связи: номенклатуре и имени регистра:
ВЫБРАТЬ
НомИСписокРег.Номенклатура КАК Номенклатура,
НомИСписокРег.НаимРег КАК ИмяРегистра,
ЕСТЬNULL(ВТОстатки.Количество, 0) КАК КоличествоОстаток,
ЕСТЬNULL(ВТОстатки.Сумма, 0) КАК СуммаОстаток
ИЗ
НомИСписокРег КАК НомИСписокРег
ЛЕВОЕ СОЕДИНЕНИЕ ВТОстатки КАК ВТОстатки
ПО НомИСписокРег.Номенклатура = ВТОстатки.Номенклатура
И НомИСписокРег.НаимРег = ВТОстатки.НаимРег
Благодаря этому запросу мы гарантированно получим строки по всем трем регистрам для каждой номенклатуры. Если реального остатка по регистру не было, поля КоличествоОстаток и СуммаОстаток заполнятся нулями благодаря функции ЕСТЬNULL, но сама строка с названием регистра никуда не исчезнет.
---
Если вам не хочется строить сложные перекрестные соединения, можно пойти по более простому пути — объединить реальные остатки с «пустыми» строками-заглушками для каждого регистра.
Рассмотрим, как это выглядит на практике:
ВЫБРАТЬ
"Регистр 1" КАК НаимРег,
Рег1.Номенклатура КАК Номенклатура,
Рег1.КоличествоОстаток КАК Количество
ИЗ
РегистрНакопления.Рег1.Остатки КАК Рег1
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регистр 1",
NULL,
0
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регистр 2" КАК НаимРег,
Рег2.Номенклатура КАК Номенклатура,
Рег2.КоличествоОстаток КАК Количество
ИЗ
РегистрНакопления.Рег2.Остатки КАК Рег2
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
"Регистр 2",
NULL,
0
Этот способ значительно проще в написании, однако у него есть существенный минус: он не гарантирует появление пустой строки регистра для каждой конкретной номенклатуры в детальных записях, если вы наложите строгие группировки. Он лишь обеспечивает то, что сам регистр вообще появится в структуре отчета. Для точечного сопоставления «номенклатура-регистр» мы рекомендуем использовать первый способ с декартовым произведением.
---
Использование декартова произведения (перемножения таблиц) таит в себе скрытую угрозу для производительности информационной базы. Представим реальную рабочую базу, в которой насчитывается 100 000 уникальных товаров и 4 сравниваемых регистра.
Если мы перемножим эти таблицы в запросе «в лоб», то СУБД сначала сформирует временную таблицу размером в 400 000 строк, выполнит по ней тяжелые левые соединения, и только потом СКД выведет результат. Если пользователь захочет сформировать отчет всего по одной конкретной номенклатуре или по одной группе товаров, без правильной настройки СКД все равно будет пересчитывать остатки по всей базе данных!
Чтобы этого избежать, мы должны использовать расширения языка запросов СКД (фигурные скобки {}). Нам нужно позволить СКД накладывать пользовательские отборы непосредственно внутрь подзапросов, собирающих исходную номенклатуру.
Модифицируем наш первый шаг с учетом требований оптимизации:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Рег1.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ СписокНоменклатуры
ИЗ
РегистрНакопления.Рег1.Остатки(&Период, {Номенклатура.* КАК Номенклатура}) КАК Рег1
ОБЪЕДИНИТЬ
ВЫБРАТЬ РАЗЛИЧНЫЕ
Рег2.Номенклатура
ИЗ
РегистрНакопления.Рег2.Остатки(&Период, {Номенклатура.* КАК Номенклатура}) КАК Рег2
Обратите внимание на конструкцию {Номенклатура.* КАК Номенклатура} в параметрах виртуальной таблицы остатков. Благодаря ей, когда пользователь установит в настройках отчета отбор по номенклатуре (например, "Товар 1"), СКД автоматически подставит этот отбор напрямую в запросы к регистрам. В результате временная таблица СписокНоменклатуры будет содержать только одну строку, декартово произведение будет состоять всего из 3 строк, и отчет выполнится мгновенно.
---
Многие разработчики забывают, что СКД умеет самостоятельно визуализировать пустые значения, если правильно спроектировать структуру вывода данных. Вместо того чтобы выводить регистры строками друг под другом, мы можем расположить их в колонках.
Для этого в настройках отчета на вкладке «Структура» мы можем создать элемент типа «Таблица»:
Номенклатура.ИмяРегистра.При такой схеме СКД автоматически строит двумерную матрицу. Даже если мы предоставим СКД «плоский» набор данных без декартова произведения, система компоновки сама создаст пересечения для всех колонок (регистров) и строк (номенклатуры). В ячейках, где данные отсутствуют, СКД по умолчанию отобразит пустые значения. Чтобы вместо пустоты выводились нули, достаточно настроить условное оформление для незаполненных ячеек или использовать выражение ЕСТЬNULL(Сумма, 0) в полях набора данных.
Такой подход экономит ресурсы сервера, так как СУБД не приходится выполнять операцию перемножения таблиц — всю работу по формированию матрицы берет на себя процессор компоновки данных 1С на клиенте или сервере приложений.
---
Для вывода незаполненных строк по регистрам в СКД мы можем использовать два основных пути:
{} для оптимизации отборов!