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