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