Почему в запросе к таблице «ОстаткиИОбороты» искажаются остатки при добавлении условия с измерением?

← На главную

При разработке отчетов и написании запросов к регистрам накопления разработчики нередко сталкиваются с ситуацией: запрос к виртуальной таблице ОстаткиИОбороты возвращает корректную сквозную цепочку остатков, но стоит добавить в список выборки логическое выражение с проверкой измерения (например, на заполненность номера ГТД), как начальные и конечные остатки перестают сходиться, а порядок строк меняется. Разберем детально, почему платформа 1С ведет себя подобным образом и как правильно строить такие запросы.

Рассмотрим исходный запрос, вызвавший проблему:


ВЫБРАТЬ
    ТоварыОрганизаций.Регистратор КАК Регистратор,
    ТоварыОрганизаций.КоличествоНачальныйОстаток,
    ТоварыОрганизаций.КоличествоПриход,
    ТоварыОрганизаций.КоличествоРасход,
    ТоварыОрганизаций.КоличествоКонечныйОстаток,
    ТоварыОрганизаций.НомерГТД <> ЗНАЧЕНИЕ(Справочник.НомераГТД.ПустаяСсылка) КАК ЕстьНомерГТД
ИЗ
    РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(
            &НачалоПериода,
            &КонецПериода,
            Авто,
            ,
            АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура
                И АналитикаУчетаНоменклатуры.Склад = &Склад) КАК ТоварыОрганизаций

Если исключить поле ЕстьНомерГТД, начальный остаток каждой последующей строки совпадает с конечным остатком предыдущей. При добавлении этого поля остатки начинают обнуляться или рассчитываться неожиданным образом, а строки перегруппировываются.

Выясним причину: как виртуальная таблица обрабатывает измерения

Проанализируем внутренние механизмы платформы при трансляции запроса к виртуальной таблице ОстаткиИОбороты в запрос к СУБД:

Автоматическое включение измерений в группировку. Когда мы обращаемся к любому измерению регистра в секции ВЫБРАТЬ, платформа автоматически включает это физическое измерение в итоговую группировку запроса СУБД. При этом совершенно не важно, выбираем ли мы само поле НомерГТД напрямую или используем его внутри формулы, булева сравнения (НомерГТД <> ...) или конструкции ВЫБОР КОГДА.
Расчет остатков в разрезе значений, а не выражений. Платформа 1С не умеет агрегировать остатки виртуальной таблицы по результату произвольного вычисляемого выражения. На уровне СУБД остатки всегда рассчитываются по конкретным ссылкам измерения НомерГТД. Если по определенной ГТД на начало периода движений не было, начальный остаток по ней будет равен 0, несмотря на то, что общий остаток номенклатуры на складе положительный.
Отсутствие понятия сквозного остатка в плоской таблице. В плоской выборке каждая запись виртуальной таблицы отображает остатки строго для своей уникальной комбинации выбранных измерений на указанную точку времени. Непрерывная цепочка, где конечный остаток строки N равен начальному остатку строки N+1, существует исключительно при неизменном наборе измерений. Как только в выборку вовлекается дополнительное измерение, остатки дробятся на независимые потоки.
Порядок записей в СУБД. В реляционных базах данных порядок строк не гарантирован, пока он явно не задан предложением УПОРЯДОЧИТЬ ПО. При добавлении булева выражения СУБД может выбрать другой план выполнения, из-за чего строки визуально разделятся (например, сначала сгруппируются все записи со значением Ложь, затем — с Истина).

Ограничения при использовании периодичности «Авто» и регистратора

В параметрах виртуальной таблицы указана периодичность Авто. При детализации до документа движения поле Регистратор не является самостоятельным периодом — оно служит лишь уточнением момента времени. Чтобы платформа могла корректно выстроить хронологическую шкалу, в секцию ВЫБРАТЬ необходимо обязательно включать вышестоящее поле периода — ПериодСекунда, а также накладывать явную сортировку.

Способы решения проблемы

Рассмотрим практические варианты построения запроса, позволяющие получить признак документа или движения без искажения общих остатков по номенклатуре.

Способ 1. Получение признака через соединение с движениями регистра

Если цель запроса — видеть общие остатки по номенклатуре и складу, но при этом для каждой строки документа знать, было ли заполнено измерение НомерГТД, правильнее всего исключить измерение из виртуальной таблицы и присоединить физическую таблицу движений:


ВЫБРАТЬ
    ОстаткиОбороты.ПериодСекунда КАК ПериодСекунда,
    ОстаткиОбороты.Регистратор КАК Регистратор,
    ОстаткиОбороты.КоличествоНачальныйОстаток КАК КоличествоНачальныйОстаток,
    ОстаткиОбороты.КоличествоПриход КАК КоличествоПриход,
    ОстаткиОбороты.КоличествоРасход КАК КоличествоРасход,
    ОстаткиОбороты.КоличествоКонечныйОстаток КАК КоличествоКонечныйОстаток,
    ЕСТЬNULL(Движения.ЕстьНомерГТД, ЛОЖЬ) КАК ЕстьНомерГТД
ИЗ
    РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(
            &НачалоПериода,
            &КонецПериода,
            Авто,
            ,
            АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура
                И АналитикаУчетаНоменклатуры.Склад = &Склад) КАК ОстаткиОбороты
        ЛЕВОЕ СОЕДИНЕНИЕ (ВЫБРАТЬ РАЗЛИЧНЫЕ
            Т.Регистратор КАК Регистратор,
            ИСТИНА КАК ЕстьНомерГТД
        ИЗ
            РегистрНакопления.ТоварыОрганизаций КАК Т
        ГДЕ
            Т.Период МЕЖДУ &НачалоПериода И &КонецПериода
            И Т.АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура
            И Т.АналитикаУчетаНоменклатуры.Склад = &Склад
            И Т.НомерГТД <> ЗНАЧЕНИЕ(Справочник.НомераГТД.ПустаяСсылка)) КАК Движения
        ПО ОстаткиОбороты.Регистратор = Движения.Регистратор
УПОРЯДОЧИТЬ ПО
    ПериодСекунда,
    Регистратор

В этом варианте виртуальная таблица рассчитывает остатки исключительно по номенклатуре и складу. Измерение НомерГТД не участвует в ее формировании, поэтому цепочка начальных и конечных остатков остается непрерывной.

Способ 2. Использование пакета запросов с временной таблицей

Если требуется выполнить более сложную постобработку данных, разделим получение агрегатов и расчет признаков на два шага:


ВЫБРАТЬ
    ТоварыОрганизаций.ПериодСекунда КАК ПериодСекунда,
    ТоварыОрганизаций.Регистратор КАК Регистратор,
    ТоварыОрганизаций.КоличествоНачальныйОстаток КАК НачальныйОстаток,
    ТоварыОрганизаций.КоличествоПриход КАК Приход,
    ТоварыОрганизаций.КоличествоРасход КАК Расход,
    ТоварыОрганизаций.КоличествоКонечныйОстаток КАК КонечныйОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(
            &НачалоПериода,
            &КонецПериода,
            Авто,
            ,
            АналитикаУчетаНоменклатуры.Номенклатура = &Номенклатура
                И АналитикаУчетаНоменклатуры.Склад = &Склад) КАК ТоварыОрганизаций
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ.ПериодСекунда,
    ВТ.Регистратор,
    ВТ.НачальныйОстаток,
    ВТ.Приход,
    ВТ.Расход,
    ВТ.КонечныйОстаток,
    ВЫБОР
        КОГДА Движения.НомерГТД ЕСТЬ NULL
            ТОГДА ЛОЖЬ
        ИНАЧЕ ИСТИНА
    КОНЕЦ КАК ЕстьНомерГТД
ИЗ
    ВТ_Остатки КАК ВТ
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыОрганизаций КАК Движения
        ПО ВТ.Регистратор = Движения.Регистратор
            И Движения.НомерГТД <> ЗНАЧЕНИЕ(Справочник.НомераГТД.ПустаяСсылка)
УПОРЯДОЧИТЬ ПО
    ВТ.ПериодСекунда,
    ВТ.Регистратор

Способ 3. Расчет итогов средствами СКД

Если отчет строится с использованием системы компоновки данных (СКД), то сквозные остатки по иерархии группировок должны рассчитываться платформой автоматически. Для этого настроим роли полей в наборе данных:

Обязательно включим в запрос поле ТоварыОрганизаций.ПериодСекунда.
На вкладке Наборы данных в колонке Роль для поля ПериодСекунда установим флажок Период и укажем порядковый номер 1.
Для поля Регистратор установим флажок Период с порядковым номером 2 и укажем родительское поле ПериодСекунда.
Полям КоличествоНачальныйОстаток и КоличествоКонечныйОстаток назначим соответствующие роли остатков с единым именем группы.

При такой настройке компоновка данных самостоятельно рассчитает остатки на всех уровнях группировок, даже если в детальных записях выводятся подчиненные реквизиты и измерения.

Таким образом, для получения корректных данных важно помнить главное правило: любое измерение, упомянутое в выборке из таблицы ОстаткиИОбороты, неизбежно дробит расчет остатков. Если разбивка остатков по этому измерению не требуется, извлекать его следует из реальных таблиц движений отдельным соединением.