Как рассчитать нарастающий итог и свободные остатки в запросе СКД

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

Причина ошибки: почему остатки «размножаются»

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

Проанализируем ситуацию на примере арифметики в запросе:

ЗаказыПоставщикамОстатки.КоличествоОстаток - РазмещениеЗаказовПокупателейОстатки.КоличествоОстаток КАК ОсталосьСвободно

В этой конструкции КоличествоОстаток — это статичная величина всего прихода. Она не уменьшается автоматически в следующей строке. Чтобы получить корректный результат, нам нужно вычитать не текущий резерв из общего остатка, а сумму всех предыдущих резервов вместе с текущим.

Способ 1: Использование функций СКД (ВычислитьВыражение)

Это наиболее современный и эффективный метод, который не перегружает запрос сложными соединениями. Мы можем использовать встроенную функцию языка выражений СКД — ВычислитьВыражение. Рассмотрим подробнее, как это настроить в ресурсах или вычисляемых полях.

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

Максимум(КоличествоОстаток) - ВычислитьВыражение("Сумма(Зарезервировано)", , , "Первая", "Текущая")

Разберем параметры функции ВычислитьВыражение:

1. "Сумма(Зарезервировано)" — выражение, которое нужно вычислить.
2. "Первая" — начало диапазона расчета (от первой строки группировки).
3. "Текущая" — конец диапазона (до текущей строки включительно).

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

Способ 2: Реализация нарастающего итога в запросе (Self-Join)

Если по каким-то причинам использование функций компоновки невозможно, мы можем решить задачу на уровне SQL-запроса. Для этого используется соединение таблицы самой с собой. Идея заключается в том, чтобы для каждой строки с резервом найти все «предыдущие» строки и просуммировать их значения.

Выясним причину, почему простого сравнения по дате недостаточно. В системе 1С за одну секунду может быть проведено несколько документов. Поэтому для однозначного определения порядка строк в запросе необходимо использовать МоментВремени или комбинацию Дата + Ссылка.

Пример структуры запроса для получения накопленного резерва:

ВЫБРАТЬ
    Т1.ЗаказПоставщику,
    Т1.ЗаказПокупателя,
    Т1.Зарезервировано КАК ТекущийРезерв,
    СУММА(Т2.Зарезервировано) КАК НакопленныйРезерв
ИЗ
    ВТ_Размещения КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Размещения КАК Т2
    ПО Т1.ЗаказПоставщику = Т2.ЗаказПоставщику
    И (Т1.ЗаказПокупателя.МоментВремени >= Т2.ЗаказПокупателя.МоментВремени)
ГРУППИРОВАТЬ ПО
    Т1.ЗаказПоставщику,
    Т1.ЗаказПокупателя,
    Т1.Зарезервировано

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

Важные настройки СКД для работы с остатками

При работе с подобными отчетами в СКД крайне важно правильно настроить Роли полей на закладке «Набор данных». Это позволит системе понимать природу данных и правильно рассчитывать итоги в иерархических группировках.

• Для поля КоличествоОстаток необходимо указать роль Остатки.
• Нужно выбрать имя регистра, из которого берутся данные.
• Указать тип остатка (Начальный, Конечный или Нет).
• Обязательно проверьте использование функции ЕСТЬNULL. При левом соединении, если резервов по заказу поставщика еще нет, в поле резерва будет NULL. Арифметика с NULL всегда дает NULL, поэтому используйте конструкцию ЕСТЬNULL(Зарезервировано, 0).

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

Таким образом, мы рассмотрели два основных подхода к решению задачи. Выбор метода зависит от ваших предпочтений и сложности отчета, но использование функций ВычислитьВыражение в СКД считается более гибким и производительным для платформы 1С:Предприятие 8.