В практике разработки на платформе 1С часто возникает задача построения отчета или получения данных, где остатки и обороты по регистру накопления должны быть выведены строго по дням за определенный период. Основная сложность заключается в том, что виртуальные таблицы 1С (например, ОстаткиИОбороты) по умолчанию не возвращают записи за те дни, в которых не было никаких движений (регистраций документов). Если остаток на складе составлял 10 единиц и в течение недели товар не продавался и не покупался, обычный запрос вернет либо пустую таблицу, либо одну строку на начало периода, но не 7 строк для каждого дня.
В этой статье мы подробно разберем, как "заставить" систему выдать данные на каждую дату, проанализируем типичные ошибки и рассмотрим три надежных способа решения задачи: через СКД, через расчет итогов в запросе и через "чистый" SQL-подход с временными таблицами.
Разберем ситуацию, с которой столкнулся автор темы. Он создал временную таблицу втДниПериода, содержащую все даты выбранного интервала, и попытался соединить ее левым соединением с виртуальной таблицей ОстаткиИОбороты. На первый взгляд логика верна: мы берем все даты и "подтягиваем" к ним данные регистра. Однако на практике результат часто оказывается пустым или неполным по двум причинам:
ГДЕ накладываете условие на поля правой (присоединяемой) таблицы (например, ТоварыНаСкладахОстаткиИОбороты.Склад = &Склад), то все строки с датами, для которых нет движений (где склад будет NULL), отсекутся. Это превращает левое соединение во внутреннее.NULL. Система не знает автоматически, что нужно взять остаток из предыдущего дня, где движение было.Рассмотрим способы, которые решают эти проблемы.
Это самый эффективный и рекомендуемый платформенный механизм. В СКД встроена функция автоматического дополнения периодов. Разберем по шагам, как это настроить:
Шаг 1. Настройка запроса. В наборе данных — запросе мы используем обращение к виртуальной таблице ОстаткиИОбороты. В параметрах таблицы важно указать периодичность День. Текст запроса может быть стандартным, без попыток ручного соединения с датами.
Шаг 2. Настройка ролей полей. Это критически важный момент. Перейдем на закладку "Набор данных" и проверим колонку "Роль":
Период должна быть указана роль Период с типом "День" и порядковым номером 1.Шаг 3. Настройка группировки. В настройках отчета создадим группировку по полю Период. Выделим эту группировку и в нижней части окна настроек перейдем на вкладку "Иерархия и группировка". Найдем свойство Тип дополнения и установим значение День. Также необходимо указать параметры "Начало периода" и "Конец периода", чтобы СКД знала границы, которые нужно заполнить.
При такой настройке СКД сама обнаружит "дыры" в датах и заполнит их, автоматически транслируя остаток с предыдущего дня на следующий.
Если нам нужно получить данные в коде (например, для вывода в табличный документ вручную) без использования полноценной схемы СКД, мы можем воспользоваться секцией ИТОГИ в языке запросов. Рассмотрим пример кода:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| ТоварыОстатки.Склад КАК Склад,
| ТоварыОстатки.Номенклатура КАК Номенклатура,
| ТоварыОстатки.Период КАК Период,
| ТоварыОстатки.КоличествоНачальныйОстаток КАК НачОст,
| ТоварыОстатки.КоличествоКонечныйОстаток КАК КонОст
|ИЗ
| РегистрНакопления.ТоварыНаСкладах.ОстаткиИОбороты(&НачДата, &КонДата, День, , ) КАК ТоварыОстатки
|ИТОГИ
| СУММА(НачОст),
| СУММА(КонОст)
|ПО
| Период ПЕРИОДАМИ(ДЕНЬ, &НачДата, &КонДата)";
Запрос.УстановитьПараметр("НачДата", НачалоДня(Объект.ДатаНачала));
Запрос.УстановитьПараметр("КонДата", КонецДня(Объект.ДатаОкончания));
РезультатЗапроса = Запрос.Выполнить();
Ключевое слово здесь — ПЕРИОДАМИ(ДЕНЬ, ...). Оно заставляет систему подготовить выборку с учетом всех дней. Однако, чтобы получить эти данные в обходе, нужно использовать специальный метод:
ВыборкаПериод = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам, "Период", "ВСЕ");
Пока ВыборкаПериод.Следующий() Цикл
// Здесь мы получим каждый день периода, даже если движений не было
// Остатки будут рассчитаны платформой автоматически
КонецЦикла;
Параметр "ВСЕ" в методе Выбрать является решающим — именно он заставляет выборку генерировать строки для пустых интервалов.
Иногда требуется получить плоскую таблицу данных одним запросом (например, для передачи во внешнюю систему). Проанализируем алгоритм создания такого запроса:
1. Формируем таблицу всех дат. Это можно сделать, передав готовую таблицу значений в запрос или воспользовавшись соединением какой-либо системной таблицы с самой собой для генерации чисел.
2. Формируем таблицу всех аналитик. Нам нужно знать все пары Склад-Номенклатура, по которым вообще были движения или есть остатки. Выбираем их во временную таблицу втАналитика.
3. Декартово произведение. Соединяем втДаты и втАналитика без условий соединения (Cross Join). В результате получаем "сетку": на каждую дату у нас есть строка для каждого склада и каждой номенклатуры.
4. Присоединяем данные. К этой "сетке" мы присоединяем реальные движения. Но делаем это хитро: для каждой строки из сетки ищем максимальную дату в регистре, которая меньше или равна текущей дате из сетки. Это позволит нам найти "точку актуальности" для каждого пустого дня.
Разберем пример логики получения остатка на каждый день через вложенный запрос:
ВЫБРАТЬ
втКалендарь.Дата КАК ДатаКалендаря,
втАналитика.Номенклатура КАК Номенклатура,
(ВЫБРАТЬ ТОП 1
Рег.КоличествоОстаток
ИЗ
РегистрНакопления.ТоварыНаСкладах.Остатки(&ДатаГраница, ) КАК Рег
ГДЕ
Рег.Номенклатура = втАналитика.Номенклатура
// ... условия и упорядочивание
) КАК ОстатокНаДату
ИЗ
втКалендарь КАК втКалендарь
СОЕДИНЕНИЕ втАналитика КАК втАналитика ПО ИСТИНА
Примечание: Использование виртуальной таблицы Остатки внутри цикла или подзапроса может быть непроизводительным. В реальных задачах лучше использовать таблицу ОстаткиИОбороты с периодичностью Запись и соединять ее по условию ДатаРегистра <= ДатаКалендаря, выбирая максимум.
Проанализируем типичные "подводные камни", которые могут встретиться при реализации:
ЕСТЬNULL(Значение, 0). При левых соединениях в дни отсутствия оборотов приход и расход будут равны NULL. Математические операции с NULL всегда дают NULL, что приведет к потере данных в итоговых вычислениях.НачалоПериода в виртуальных таблицах включается, а данные по остаткам на дату X в 1С — это остатки на 00:00:00 этой даты. Конечный остаток в таблице ОстаткиИОбороты за день — это остаток на конец дня (включая все движения дня).Авто в виртуальной таблице, система выберет минимально необходимую периодичность для корректного расчета итогов, но она не гарантирует наличие всех дней. Для решения нашей задачи всегда явно указывайте День.ГДЕ. Это значительно ускоряет работу запроса, так как СУБД фильтрует данные еще на этапе расчета виртуальной таблицы, а не после получения всего набора записей.Выяснив причину отсутствия строк (отсечение по NULL и отсутствие механизмов дополнения), мы видим, что наиболее современным и простым в поддержке способом является СКД с включенным дополнением периодов. Если же требуется низкоуровневая работа с данными, использование ИТОГИ ПО Период ПЕРИОДАМИ является вторым по приоритету вариантом.