Почему в запросе 1С некорректно рассчитываются начальные остатки и обороты регистра?

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

Анализ типичных ошибок в запросе

Посмотрим на пример фрагмента запроса, в котором разработчик пытается получить остатки и обороты по банку в разрезе типов хозяйственных операций документов-регистраторов:

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

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

1. Особенности периодичности «Авто» и суммирование остатков. При использовании периодичности Авто (или по регистратору) виртуальная таблица ОстаткиИОбороты возвращает значения начального и конечного остатков на момент проведения каждого документа. Применение агрегатной функции СУММА() к полям остатков приводит к тому, что промежуточные остатки от каждого движения складываются друг с другом. Чем больше документов было по счету, тем сильнее завышается итоговый остаток. Остатки суммировать нельзя!
2. Размножение строк при соединении. Левое соединение с временной таблицей, сгруппированной по регистратору и хозяйственной операции, может создавать дубли. Если один документ-регистратор сформировал несколько движений с разными типами хозяйственных операций, соединение размножит строки основной выборки. Из-за этого задвоятся не только остатки, но и суммы прихода и расхода.
3. Фильтрация после получения данных. Использование отбора по организации в секции ГДЕ заставляет платформу сначала рассчитать остатки и обороты по всем организациям в базе, а лишь затем отфильтровать результат. Это приводит к серьезным проблемам с производительностью при больших объемах данных.
4. Остаток не имеет хозяйственной операции. В платформе 1С остатки хранятся исключительно в разрезе измерений регистра (например, Организация, БанковскийСчет). Попытка классифицировать остаток денежных средств по реквизиту документа (хозяйственной операции) лишена физического и математического смысла. Текущий остаток на счете — это результат множества прошлых операций, и его нельзя отнести к одному виду движения.

Правильные способы решения задачи

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

1. Получение аналитики через атрибуты измерений

Если нам необходимо разделить счета на «Депозитные» и «Расчетные», самый надежный способ — анализировать не документы движения, а сам справочник банковских счетов. Если в справочнике есть соответствующий реквизит (например, тип или вид счета), классификацию нужно делать через обращение к свойствам поля БанковскийСчет напрямую из таблицы остатков и оборотов. Это полностью исключит необходимость соединения с данными регистратора.

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

Отбор Организация = &Организация необходимо перенести из секции ГДЕ в параметры самой виртуальной таблицы ОстаткиИОбороты. Это ограничит расчет итогов на уровне СУБД только нужной организацией и значительно ускорит выполнение запроса.

3. Разделение таблиц (если не используется СКД)

Обычно подобные отчеты строятся с помощью Системы Компоновки Данных (СКД), которая умеет правильно обрабатывать остатки и рассчитывать их по периодам без агрегации СУММА() в самом запросе. Но если вам необходимо получить корректные итоги именно процедурным кодом (например, в консоли запросов или при выгрузке данных), следует отказаться от таблицы ОстаткиИОбороты.

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

• Остатки на начало периода.
• Обороты за период.
• Остатки на конец периода.

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

4. Типизация полей связи

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

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