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

← На главную

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

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

Физический смысл проблемы: почему остатки «умножаются»?

Для начала проанализируем, как устроена виртуальная таблица ОстаткиИОбороты на уровне СУБД. В физических таблицах базы данных остатки не хранятся на каждую секунду или на момент каждого документа. Платформа рассчитывает их динамически, используя таблицы реальных итогов и таблицы движений регистра накопления.

Когда мы обращаемся к таблице ОстаткиИОбороты с периодичностью Регистратор (или Авто), платформа строит SQL-запрос, который для каждого документа-регистратора рассчитывает текущий остаток на момент времени этого документа.

Рассмотрим простой пример. Пусть на начало периода на складе было 100 единиц товара «А»:

Документ 1 (Приход): пришло 50 шт. Остаток после документа стал равен 150 шт.
Документ 2 (Расход): ушло 30 шт. Остаток после документа стал равен 120 шт.

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

Строка 1 (Документ 1): Начальный остаток = 100, Приход = 50, Конечный остаток = 150.
Строка 2 (Документ 2): Начальный остаток = 150, Расход = 30, Конечный остаток = 120.

Если мы выполняем обычный запрос в консоли и используем агрегатную функцию СУММА по полям остатков с группировкой по номенклатуре, СУБД честно складывает числа по строкам:

Сумма начальных остатков: 100 + 150 = 250 шт.
Сумма конечных остатков: 150 + 120 = 270 шт.

С точки зрения математики SQL результат верен, но с точки зрения бизнес-логики учета — это грубая ошибка. Реальный начальный остаток за весь период составлял 100 шт., а реальный конечный — 120 шт.

---

Решение проблемы в Системе компоновки данных (СКД)

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

Обязательное использование полей периодов. Чтобы СКД могла понять хронологическую последовательность документов, в запросе обязательно должно присутствовать поле ПериодСекунда (или другие поля периодов, такие как ПериодДень, ПериодМесяц). Без этого СКД не сможет определить, какой документ был «первым» (для взятия начального остатка), а какой — «последним» (для конечного).
Парность полей остатков. В запросе обязательно должны выбираться оба поля: и начальный остаток (КоличествоНачальныйОстаток), и конечный остаток (КоличествоКонечныйОстаток). Если выбрать только одно из них, СКД не сможет построить корректный балансовый баланс.
Использование периодичности «Авто». В параметрах виртуальной таблицы на вкладке «Таблицы» запроса в качестве периодичности необходимо указывать значение Авто. Это дает СКД возможность самостоятельно управлять выборкой периодов в зависимости от настроек структуры отчета.
Настройка ролей полей в СКД. На вкладке «Наборы данных» схемы компоновки для полей остатков должны быть автоматически или вручную прописаны роли НачальныйОстаток и КонечныйОстаток с обязательным указанием связи с полем ПериодСекунда.

Рассмотрим пример правильного запроса для СКД:


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

При выполнении этого запроса через механизм компоновки данных (с выводом в отчет) СКД сама сгруппирует остатки правильно. На уровне общих итогов мы увидим реальные остатки на начало и конец всего периода, а на уровне регистраторов — остатки на момент совершения конкретных движений.

---

Критическая ошибка: фильтрация во внешнем условии ГДЕ

Обратим внимание на важный нюанс оптимизации запросов в 1С, который обсуждался участниками темы. Использование условий фильтрации в секции ГДЕ для виртуальных таблиц (например, ГДЕ Склад = &Склад) — это грубейшее нарушение стандартов разработки фирмы «1С».

Почему это плохо? СУБД сначала рассчитает остатки и обороты по всем складам и номенклатурным позициям базы данных в оперативной памяти, и только потом отсечет ненужные склады с помощью фильтра ГДЕ. Это приводит к колоссальной нагрузке на сервер и замедлению работы системы.

Как правильно? Фильтры необходимо передавать строго в параметры самой виртуальной таблицы. В этом случае СУБД наложит фильтр еще на этапе чтения физических таблиц и рассчитает остатки только по целевым аналитикам.

Разберем пример неправильного и правильного наложения условий:


// НЕПРАВИЛЬНО (Низкая производительность):
ИЗ
    РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(&НачПериода, &КонПериода, Авто, , ) КАК Таблица
ГДЕ
    Таблица.Склад = &Склад И Таблица.Организация = &Организация

// ПРАВИЛЬНО (Максимальная скорость выполнения):
ИЗ
    РегистрНакопления.ТоварыОрганизаций.ОстаткиИОбороты(
        &НачПериода, 
        &КонПериода, 
        Авто, 
        , 
        Склад = &Склад И Организация = &Организация
    ) КАК Таблица

---

Как получить правильные остатки с регистратором без СКД (в коде или консоли)

Если мы пишем запрос, результат которого будет выгружен в ТаблицуЗначений или выведен на форму без использования СКД, обычное суммирование остатков по регистраторам применять категорически нельзя. Нам придется использовать архитектурный паттерн разделения остатков и оборотов через временные таблицы.

Проанализируем этот алгоритм по шагам:

В первой временной таблице получаем только чистые остатки на начало периода (без привязки к регистраторам). Здесь данные гарантированно не задвоятся.
Во второй временной таблице получаем только чистые обороты (приход, расход) с детализацией по регистраторам. Поля остатков в этой таблице вообще не выбираем.
Объединяем эти таблицы через секцию ОБЪЕДИНИТЬ ВСЕ, приводя их к общему виду.
В итоговом запросе группируем данные по номенклатуре и регистратору, а конечный остаток рассчитываем программно или математически по формуле НачальныйОстаток + Приход - Расход.

Посмотрим на пример практической реализации такого запроса:


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

ОБЪЕДИНИТЬ ВСЕ

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

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВТ_Данные.Номенклатура КАК Номенклатура,
    ВТ_Данные.Склад КАК Склад,
    ВТ_Данные.Регистратор КАК Регистратор,
    ВТ_Данные.ПериодСекунда КАК ПериодСекунда,
    СУММА(ВТ_Данные.КоличествоНачальныйОстаток) КАК КоличествоНачальныйОстаток,
    СУММА(ВТ_Данные.КоличествоПриход) КАК КоличествоПриход,
    СУММА(ВТ_Данные.КоличествоРасход) КАК КоличествоРасход
ИЗ
    ВТ_Данные КАК ВТ_Данные

СГРУППИРОВАТЬ ПО
    ВТ_Данные.Номенклатура,
    ВТ_Данные.Склад,
    ВТ_Данные.Регистратор,
    ВТ_Данные.ПериодСекунда

УПОРЯДОЧИТЬ ПО
    Номенклатура,
    ПериодСекунда

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

Первая строка по товару содержит только начальный остаток на начало периода (поле Регистратор будет заполнено как НЕОПРЕДЕЛЕНО).
Все последующие строки содержат конкретные обороты (приходы и расходы) по документам-регистраторам в хронологическом порядке.
Суммирование данных строк по полям прихода и расхода даст абсолютно верные обороты, а начальный остаток не будет умножен, так как он присутствует в выборке ровно один раз.

Конечный остаток по каждой строке при таком подходе можно легко рассчитать при программном обходе выборки в цикле Пока Выборка.Следующий(), аккумулируя нарастающий итог в переменной.

← На главную