При формировании отчетов или аналитических данных в 1С часто возникает задача отобразить информацию за полный период (например, за все месяцы года), даже если по некоторым из этих периодов отсутствуют фактические записи в базе данных. Стандартный запрос, выбирающий данные, покажет только те месяцы, по которым есть движения. В результате, мы получаем неполную картину, что может ввести в заблуждение.
Давайте вместе разберем, как решить эту проблему и вывести в итоговой выборке все необходимые периоды (например, месяцы), подставляя нулевые значения там, где данных нет. Мы рассмотрим несколько подходов, от чисто запросных до комбинированных, и выясним, какой из них наиболее подходит для разных ситуаций.
Этот подход является одним из наиболее универсальных и часто используемых. Мы сначала создаем временную таблицу, содержащую полный список необходимых периодов (например, месяцев), а затем соединяем ее с таблицей, содержащей наши фактические данные.
ТаблицаЗначений.ЛЕВОЕ СОЕДИНЕНИЕ с нашей основной таблицей данных (например, регистром накопления). Это гарантирует, что все месяцы из временной таблицы будут присутствовать в результате, даже если для них нет соответствующих записей в основной таблице.NULL. Мы используем функцию ЕСТЬNULL, чтобы заменить эти NULL на 0.Посмотрим на пример кода запроса:
// Предполагаем, что у нас есть ТаблицаЗначений "ТаблицаМесяцы"
// со столбцом "Месяц" типа Дата, заполненная началами нужных месяцев.
// 1. Помещаем таблицу месяцев во временную таблицу
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ТаблицаМесяцы.Месяц КАК Месяц
ПОМЕСТИТЬ втМесяцы
ИЗ
&ТаблицаМесяцы КАК ТаблицаМесяцы
;
// 2. Выбираем данные, используя ЛЕВОЕ СОЕДИНЕНИЕ с временной таблицей
ВЫБРАТЬ РАЗРЕШЕННЫЕ
втМесяцы.Месяц КАК Месяц,
ЕСТЬNULL(СУММА(СведенияОДоходахСтраховыеВзносы.Сумма - СведенияОДоходахСтраховыеВзносы.Скидка), 0) КАК Доход
ИЗ
втМесяцы КАК втМесяцы
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СведенияОДоходахСтраховыеВзносы КАК СведенияОДоходахСтраховыеВзносы
ПО втМесяцы.Месяц = НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ)
ГДЕ
СведенияОДоходахСтраховыеВзносы.Сотрудник = &Сотрудник
И СведенияОДоходахСтраховыеВзносы.Период МЕЖДУ &НачалоИнтервала И &КонецИнтервала
СГРУППИРОВАТЬ ПО
втМесяцы.Месяц
УПОРЯДОЧИТЬ ПО
втМесяцы.Месяц
Для выполнения этого запроса на стороне 1С, нам потребуется создать ТаблицаЗначений и передать ее как параметр запроса:
Запрос = Новый Запрос;
Запрос.Текст = "
// Запрос, приведенный выше
";
// Создаем ТаблицуЗначений и заполняем ее месяцами
ТаблицаМесяцы = Новый ТаблицаЗначений;
ТаблицаМесяцы.Колонки.Добавить("Месяц", Новый ОписаниеТипов("Дата"));
НачалоИнтервала = НачалоГода(ТекущаяДата()); // Например, с начала текущего года
КонецИнтервала = КонецГода(ТекущаяДата()); // До конца текущего года
ТекущаяДатаЦикла = НачалоИнтервала;
Пока ТекущаяДатаЦикла <= КонецИнтервала Цикл
НоваяСтрока = ТаблицаМесяцы.Добавить();
НоваяСтрока.Месяц = НачалоМесяца(ТекущаяДатаЦикла);
ТекущаяДатаЦикла = ДобавитьМесяц(ТекущаяДатаЦикла, 1);
КонецЦикла;
Запрос.УстановитьПараметр("ТаблицаМесяцы", ТаблицаМесяцы);
Запрос.УстановитьПараметр("Сотрудник", ОбъектСотрудник); // Ваш объект Сотрудник
Запрос.УстановитьПараметр("НачалоИнтервала", НачалоИнтервала);
Запрос.УстановитьПараметр("КонецИнтервала", КонецИнтервала);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Важный момент: Обратите внимание на использование функции НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ) в условии соединения. Это критически важно для корректного сопоставления записей по месяцам, так как поле Период в регистре может содержать точную дату и время, а нам нужно соединять именно по началу месяца.
Этот метод позволяет полностью сформировать таблицу периодов средствами языка запросов 1С, без необходимости предварительной подготовки ТаблицаЗначений на стороне клиента. Мы будем использовать технику декартова произведения для генерации числовой последовательности, которую затем преобразуем в даты.
СОЕДИНЕНИЕ ... ПО ИСТИНА (аналог CROSS JOIN в SQL), мы перемножаем строки этих таблиц, получая на выходе последовательность чисел. Например, четыре таблицы по два значения (0 и 1) могут сгенерировать $2^4 = 16$ чисел. Десять значений в четырех таблицах дадут $10^4 = 10000$ чисел.ДОБАВИТЬКДАТЕ для формирования дат. Полученные числа мы используем как смещение в месяцах от начальной даты интервала с помощью функции ДОБАВИТЬКДАТЕ, формируя таким образом все необходимые даты начала месяцев.ЕСТЬNULL. Дальнейшие шаги аналогичны предыдущему решению: ЛЕВОЕ СОЕДИНЕНИЕ с данными и обработка NULL с помощью ЕСТЬNULL.Рассмотрим пример запроса, генерирующего даты:
ВЫБРАТЬ
ВсеДаты.Месяц КАК Месяц,
ЕСТЬNULL(СУММА(СведенияОДоходахСтраховыеВзносы.Сумма - СведенияОДоходахСтраховыеВзносы.Скидка), 0) КАК Доход
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&НачалоИнтервала, МЕСЯЦ, aa.a * 1000 + bb.a * 100 + cc.a * 10 + dd.a), МЕСЯЦ) КАК Месяц
ИЗ
(ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК aa
СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК bb ПО ИСТИНА
СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК cc ПО ИСТИНА
СОЕДИНЕНИЕ (ВЫБРАТЬ 0 КАК a ОБЪЕДИНИТЬ ВЫБРАТЬ 1 ОБЪЕДИНИТЬ ВЫБРАТЬ 2 ОБЪЕДИНИТЬ ВЫБРАТЬ 3 ОБЪЕДИНИТЬ ВЫБРАТЬ 4 ОБЪЕДИНИТЬ ВЫБРАТЬ 5 ОБЪЕДИНИТЬ ВЫБРАТЬ 6 ОБЪЕДИНИТЬ ВЫБРАТЬ 7 ОБЪЕДИНИТЬ ВЫБРАТЬ 8 ОБЪЕДИНИТЬ ВЫБРАТЬ 9) КАК dd ПО ИСТИНА
ГДЕ
ДОБАВИТЬКДАТЕ(&НачалоИнтервала, МЕСЯЦ, aa.a * 1000 + bb.a * 100 + cc.a * 10 + dd.a) <= &КонецИнтервала
) КАК ВсеДаты
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СведенияОДоходахСтраховыеВзносы КАК СведенияОДоходахСтраховыеВзносы
ПО ВсеДаты.Месяц = НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ)
И СведенияОДоходахСтраховыеВзносы.Сотрудник = &Сотрудник
СГРУППИРОВАТЬ ПО
ВсеДаты.Месяц
УПОРЯДОЧИТЬ ПО
ВсеДаты.Месяц
Этот запрос генерирует до 10 000 месяцев, что обычно избыточно. Для более коротких интервалов (например, до 12 месяцев) можно использовать более компактный вариант:
// Подзапрос для генерации 12 месяцев
ВЫБРАТЬ
Периоды.ДатаНачалаМесяца КАК Период,
ЕСТЬNULL(СведенияОДоходахСтраховыеВзносыОбороты.СуммаОборот, 0) - ЕСТЬNULL(СведенияОДоходахСтраховыеВзносыОбороты.СкидкаОборот, 0) КАК Доход
ИЗ
(ВЫБРАТЬ
НАЧАЛОПЕРИОДА(ДОБАВИТЬКДАТЕ(&КонПериода, МЕСЯЦ, -(Бит3.Бит*8 + Бит2.Бит*4 + Бит1.Бит*2 + Бит0.Бит)), МЕСЯЦ) КАК ДатаНачалаМесяца
ИЗ
(ВЫБРАТЬ 0 КАК Бит ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1) КАК Бит0,
(ВЫБРАТЬ 0 КАК Бит ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1) КАК Бит1,
(ВЫБРАТЬ 0 КАК Бит ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1) КАК Бит2,
(ВЫБРАТЬ 0 КАК Бит ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1) КАК Бит3
ГДЕ
Бит3.Бит*8 + Бит2.Бит*4 + Бит1.Бит*2 + Бит0.Бит < &КоличествоМесяцев // &КоличествоМесяцев = 12
) КАК Периоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СведенияОДоходахСтраховыеВзносы.Обороты(ДОБАВИТЬКДАТЕ(&КонПериода, МЕСЯЦ, -&КоличествоМесяцев), &КонПериода, Месяц, ФизическоеЛицо = &Физлицо) КАК СведенияОДоходахСтраховыеВзносыОбороты
ПО СведенияОДоходахСтраховыеВзносыОбороты.Период = Периоды.ДатаНачалаМесяца
УПОРЯДОЧИТЬ ПО
Периоды.ДатаНачалаМесяца
Преимущества: Полностью реализуется на уровне запроса, без клиентского кода для формирования таблицы периодов. Недостатки: Запрос становится значительно сложнее для чтения и отладки. Генерация очень больших последовательностей может быть неэффективной.
В некоторых типовых конфигурациях 1С уже есть объекты, которые содержат информацию о периодах. Мы можем их использовать в качестве источника для нашей таблицы периодов.
РегистрСведений.ДанныеПроизводственногоКалендаряПомесячноЕсли в вашей конфигурации есть регистр сведений, подобный ДанныеПроизводственногоКалендаряПомесячно, он может служить отличным источником для получения списка месяцев. Этот регистр обычно содержит записи по каждому месяцу.
ВЫБРАТЬ
РС.Месяц КАК Месяц,
ЕСТЬNULL(РН.СуммаОборот - РН.СкидкаОборот, 0) КАК Сумма
ИЗ
РегистрСведений.ДанныеПроизводственногоКалендаряПомесячно КАК РС
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СведенияОДоходахСтраховыеВзносы.Обороты(&Начало, &Конец, Месяц, ФизическоеЛицо = &ФизическоеЛицо) КАК РН
ПО (РС.Месяц = РН.Период)
ГДЕ
РС.Месяц МЕЖДУ НАЧАЛОПЕРИОДА(&Начало, МЕСЯЦ) И &Конец
УПОРЯДОЧИТЬ ПО
РС.Месяц
Преимущества: Простота и надежность, так как используется уже готовый и, как правило, правильно заполненный источник данных. Недостатки: Доступность такого регистра зависит от конкретной конфигурации. Для его использования необходим параметр &ФизическоеЛицо, который можно получить из &Сотрудник, например, так: ВЫРАЗИТЬ(&Сотрудник КАК Справочник.Сотрудники).ФизическоеЛицо.
Некоторые конфигурации, такие как "Зарплата и управление персоналом" (ЗУП), предоставляют специальные функции для работы с временными таблицами периодов. Мы можем воспользоваться ими для создания временной таблицы месяцев.
Запрос = Новый Запрос;
Запрос.МенеджерВременныхТаблиц = Новый МенеджерВременныхТаблиц;
// Используем стандартную функцию для создания ВТПериоды
ЗарплатаКадрыОбщиеНаборыДанных.СоздатьВТПериоды(Запрос.МенеджерВременныхТаблиц, НачалоИнтервала, КонецИнтервала, "МЕСЯЦ");
Запрос.Текст = "
ВЫБРАТЬ
Периоды.Период КАК Месяц,
СУММА(Выбор КОГДА СведенияОДоходахСтраховыеВзносы.Период ЕСТЬ NULL
ТОГДА 0
ИНАЧЕ СведенияОДоходахСтраховыеВзносы.Сумма - СведенияОДоходахСтраховыеВзносы.Скидка
КОНЕЦ) КАК Доход
ИЗ
ВТПериоды КАК Периоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.СведенияОДоходахСтраховыеВзносы КАК СведенияОДоходахСтраховыеВзносы
ПО НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ) = Периоды.Период
ГДЕ
СведенияОДоходахСтраховыеВзносы.Сотрудник = &Сотрудник
И СведенияОДоходахСтраховыеВзносы.Период МЕЖДУ &НачалоИнтервала И &КонецИнтервала
СГРУППИРОВАТЬ ПО
Периоды.Период
УПОРЯДОЧИТЬ ПО
Периоды.Период
";
Запрос.УстановитьПараметр("Сотрудник", ОбъектСотрудник);
Запрос.УстановитьПараметр("НачалоИнтервала", НачалоИнтервала);
Запрос.УстановитьПараметр("КонецИнтервала", КонецИнтервала);
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
Преимущества: Максимальная простота и производительность, так как используются оптимизированные платформенные или типовые механизмы. Недостатки: Применимо только в тех конфигурациях, где такие функции существуют.
Этот подход предполагает, что мы сначала формируем полную таблицу периодов с нулевыми значениями на стороне клиента (в коде 1С), а затем выполняем обычный запрос, который выбирает только существующие данные. После этого мы объединяем результаты, обновляя нулевые значения в нашей клиентской таблице.
ТаблицаЗначений с нулями. Мы программно создаем ТаблицаЗначений и заполняем ее всеми необходимыми месяцами, присваивая полю "Доход" значение 0.ТаблицаЗначений.Рассмотрим пример клиентского кода:
// Предполагаем, что НачалоИнтервала и КонецИнтервала уже определены
// и Отчет.ТаблицаДоходов - это ТаблицаЗначений, которая будет содержать результат
// 1. Создаем пустую таблицу доходов на весь интервал (с нулями)
Отчет.ТаблицаДоходов.Очистить();
Для МесяцСмещение = 0 По РазностьДат(НачалоИнтервала, КонецИнтервала, МЕСЯЦ) Цикл
НоваяСтрока = Отчет.ТаблицаДоходов.Добавить();
ДатаМесяца = НачалоМесяца(ДобавитьМесяц(НачалоИнтервала, МесяцСмещение));
НоваяСтрока.Месяц = ДатаМесяца;
НоваяСтрока.Доход = 0; // пока нули
КонецЦикла;
// 2. Выполняем запрос для получения фактических данных
Запрос = Новый Запрос;
Запрос.Текст = "
ВЫБРАТЬ
НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ) КАК Месяц,
СУММА(СведенияОДоходахСтраховыеВзносы.Сумма - СведенияОДоходахСтраховыеВзносы.Скидка) КАК Доход
ИЗ
РегистрНакопления.СведенияОДоходахСтраховыеВзносы КАК СведенияОДоходахСтраховыеВзносы
ГДЕ
СведенияОДоходахСтраховыеВзносы.Сотрудник.ФизическоеЛицо = &ФизЛицо
И СведенияОДоходахСтраховыеВзносы.Период МЕЖДУ &НачалоИнтервала И &КонецИнтервала
СГРУППИРОВАТЬ ПО
НАЧАЛОПЕРИОДА(СведенияОДоходахСтраховыеВзносы.Период, МЕСЯЦ)
";
Запрос.УстановитьПараметр("КонецИнтервала", КонецИнтервала);
Запрос.УстановитьПараметр("НачалоИнтервала", НачалоИнтервала);
Запрос.УстановитьПараметр("ФизЛицо", Отчет.Сотрудник.ФизическоеЛицо); // Предполагаем, что Отчет.Сотрудник доступен
РезультатЗапроса = Запрос.Выполнить().Выгрузить();
// 3. Обновляем значения в предварительно заполненной таблице
Для Каждого СтрокаДоходов Из РезультатЗапроса Цикл
Для Каждого СтрокаТаблицы Из Отчет.ТаблицаДоходов Цикл
Если СтрокаТаблицы.Месяц = СтрокаДоходов.Месяц Тогда
СтрокаТаблицы.Доход = СтрокаДоходов.Доход;
Прервать; // Идем к следующей строке результата
КонецЕсли;
КонецЦикла;
КонецЦикла;
Отчет.ТаблицаДоходов.Сортировать("Месяц");
Преимущества: Запрос становится простым и понятным, так как не занимается генерацией дат. Подходит для относительно небольших интервалов и объемов данных. Недостатки: Может быть менее производительным для очень больших объемов данных из-за итераций на стороне клиента. Требует больше кода на языке 1С.
Если ваша задача состоит в построении отчета, то Система Компоновки Данных (СКД) предоставляет самый простой и элегантный способ решения этой проблемы без написания сложного кода запроса.
Месяц), в группировку.СКД автоматически сгенерирует все недостающие периоды в указанном интервале и подставит нулевые значения для ресурсов, что значительно упрощает разработку отчетов.
Преимущества: Максимальная простота и удобство для разработчика отчетов. Высокая производительность, так как логика генерации периодов и дополнения выполняется платформой. Недостатки: Применимо только для отчетов, построенных на СКД.
Независимо от выбранного метода, существуют общие рекомендации, которые помогут вам писать более надежные и производительные запросы:
ЕСТЬNULL: После любого ЛЕВОЕ СОЕДИНЕНИЕ, если вы ожидаете, что по некоторым записям данных не будет, всегда применяйте функцию ЕСТЬNULL(Поле, 0) (или другое значение по умолчанию) для полей, которые могут быть NULL. Это предотвратит ошибки и обеспечит корректное отображение нулей.НАЧАЛОПЕРИОДА: При соединении или группировке по датам, всегда приводите их к началу нужного периода (месяца, дня, года) с помощью функции НАЧАЛОПЕРИОДА(Дата, ТипПериода). Это гарантирует, что записи с разными датами внутри одного периода будут корректно сгруппированы или соединены.ПОМЕСТИТЬ и МенеджерВременныхТаблиц: Для сложных запросов с несколькими этапами обработки данных, активно используйте временные таблицы (оператор ПОМЕСТИТЬ). Они помогают разбить запрос на логические части, улучшить читаемость и часто повысить производительность, особенно при повторном использовании промежуточных результатов. Не забывайте создавать МенеджерВременныхТаблиц, если используете временные таблицы в нескольких запросах или в разных вызовах.Мы проанализировали различные подходы к решению задачи добавления нулевых строк после запроса в 1С. Выбор конкретного метода зависит от контекста задачи (отчет СКД, произвольный запрос, необходимость клиентской обработки), а также от версии конфигурации и платформы. Надеемся, что это подробное руководство поможет вам эффективно работать с данными и получать нужные результаты.
← К списку