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