При разработке отчетов и сложных аналитических запросов разработчики нередко сталкиваются с ситуацией: после добавления периодичности Регистратор в виртуальную таблицу ОстаткиИОбороты итоговые показатели начальных и конечных остатков начинают многократно увеличиваться (задваиваться, затраиваться). Разберем природу этой проблемы, выясним, почему так происходит на уровне платформы 1С, и рассмотрим правильные архитектурные способы построения таких запросов.
Проанализируем механику работы виртуальной таблицы ОстаткиИОбороты. Если периодичность не задана (или установлена в значение Период без детализации), платформа возвращает остаток на границу начала периода, обороты за период и остаток на конец периода для каждой уникальной комбинации измерений.
Однако, как только мы указываем периодичность Регистратор, виртуальная таблица возвращает остаток до и после каждого документа-регистратора. Остаток по своей экономической сути не является аддитивной величиной во времени. Его нельзя суммировать между разными моментами времени.
Посмотрим на наглядный пример расчета остатка товара на складе:
Если в запросе применить агрегатную функцию СУММА(КоличествоКонечныйОстаток) и сгруппировать строки по номенклатуре, то платформа сложит остаток после первого документа (1 шт.) и остаток после второго документа (4 шт.). В итоге мы получим 5 шт. вместо фактических 4 шт. Чем больше документов-регистраторов попадает в выборку за указанный период, тем сильнее будут завышены итоговые суммы остатков.
Рассмотрим структуру исходного запроса, приводящего к ошибке. Разработчик пытается в одной таблице рассчитать общие остатки и одновременно отфильтровать расход только по конкретному виду документа (например, по перемещениям):
ВЫБРАТЬ
ТоварыВРозницеОстаткиИОбороты.Склад,
ТоварыВРозницеОстаткиИОбороты.Номенклатура КАК Товар,
СУММА(ТоварыВРозницеОстаткиИОбороты.КоличествоНачальныйОстаток) КАК КоличествоНачальныйОстаток,
СУММА(ТоварыВРозницеОстаткиИОбороты.КоличествоКонечныйОстаток) КАК КоличествоКонечныйОстаток,
СУММА(ТоварыВРозницеОстаткиИОбороты.КоличествоПриход) КАК КоличествоПриход,
СУММА(ВЫБОР
КОГДА ТоварыВРозницеОстаткиИОбороты.Регистратор ССЫЛКА Документ.ПеремещениеТоваров
ТОГДА ТоварыВРозницеОстаткиИОбороты.КоличествоРасход
ИНАЧЕ 0
КОНЕЦ) КАК КоличествоРасход
ПОМЕСТИТЬ ВТ_РозОстатки
ИЗ
РегистрНакопления.ТоварыВРознице.ОстаткиИОбороты({(&НачалоПериода)}, {(&КонецПериода)}, Регистратор, , ) КАК ТоварыВРозницеОстаткиИОбороты
СГРУППИРОВАТЬ ПО
ТоварыВРозницеОстаткиИОбороты.Склад,
ТоварыВРозницеОстаткиИОбороты.Номенклатура;
В данном фрагменте указание периодичности Регистратор потребовалось исключительно ради условия КОГДА ... Регистратор ССЫЛКА Документ.ПеремещениеТоваров. Но из-за группировки и суммирования полей КоличествоНачальныйОстаток и КоличествоКонечныйОстаток результат расчета остатков становится полностью недостоверным.
Разберем по шагам правильную организацию выборки. Если нам нужны корректные начальные и конечные остатки за весь период, мы должны получать их из виртуальной таблицы без периодичности. Обороты же по конкретным документам-регистраторам следует получать отдельно.
Рассмотрим пример запроса, в котором обороты по перемещениям вынесены во вложенную выборку из таблицы Обороты с периодичностью Регистратор, а остатки берутся из общей таблицы ОстаткиИОбороты:
ВЫБРАТЬ
ТоварыОстатки.Склад КАК Склад,
ТоварыОстатки.Номенклатура КАК Номенклатура,
ТоварыОстатки.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
ТоварыОстатки.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
ТоварыОстатки.КоличествоПриход КАК КоличествоПриход,
ЕСТЬNULL(ПеремещенияРасход.КоличествоРасход, 0) КАК КоличествоРасход
ИЗ
РегистрНакопления.ТоварыВРознице.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ТоварыОстатки
ЛЕВОЕ СОЕДИНЕНИЕ
(ВЫБРАТЬ
Обороты.Склад КАК Склад,
Обороты.Номенклатура КАК Номенклатура,
СУММА(Обороты.КоличествоРасход) КАК КоличествоРасход
ИЗ
РегистрНакопления.ТоварыВРознице.Обороты(&НачалоПериода, &КонецПериода, Регистратор, ) КАК Обороты
ГДЕ
Обороты.Регистратор ССЫЛКА Документ.ПеремещениеТоваров
СГРУППИРОВАТЬ ПО
Обороты.Склад,
Обороты.Номенклатура) КАК ПеремещенияРасход
ПО ТоварыОстатки.Склад = ПеремещенияРасход.Склад
И ТоварыОстатки.Номенклатура = ПеремещенияРасход.Номенклатура;
В этом варианте таблица ТоварыВРознице.ОстаткиИОбороты возвращает ровно одну строку на комбинацию склада и номенклатуры с правильными остатками на границы интервала, а подзапрос возвращает строго свернутый оборот по нужному документу.
Проанализируем ситуацию глубже: использование ЛЕВОЕ СОЕДИНЕНИЕ несет в себе скрытый риск. Если по товару не было начального и конечного остатка (например, товар поступил и был сразу полностью перемещен внутри выбранного периода), то в левой таблице остатков запись может отсутствовать (или потребовать полного соединения), что приведет к потере оборотов.
Стандартным и наиболее производительным шаблоном платформы 1С для сведения остатков и оборотов является объединение потоков данных через ОБЪЕДИНИТЬ ВСЕ с последующей единой группировкой:
ОстаткиИОбороты без детализации по регистратору. В поля оборотов перемещения подставляем 0.РегистрНакопления.ТоварыВРознице, накладывая условие на период, вид движения (Расход) и тип регистратора (ССЫЛКА Документ.ПеремещениеТоваров). Поля остатков заполняем нулями.Такой подход полностью исключает проблему задвоения данных, гарантирует учет всех позиций без потери записей и выполняется значительно быстрее тяжелых соединений виртуальных таблиц.
При написании выборок по регистрам накопления важно учитывать следующие правила:
ПродажиОбороты.Регистратор.Склад заставляют СУБД формировать внешние соединения со всеми возможными таблицами документов-регистраторов. Если измерение отсутствует в регистре, надежнее связать запрос напрямую с реальной таблицей конкретного документа по полю Ссылка.ГДЕ (например, ГДЕ Склад.Наименование ПОДОБНО ...) выполняется уже после того, как платформа рассчитала итоги по всей базе. Условия по измерениям следует передавать строго в параметры виртуальной таблицы.