Как получить последнюю цену закупки из регистра накопления в СКД без дублирования строк

← На главную

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

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

В этой статье мы подробно разберем, как решить эту проблему несколькими способами, проанализируем частые ошибки новичков при работе со временными таблицами и изучим альтернативные механизмы самой платформы 1С.

---

Причина проблемы: почему дублируются строки?

Рассмотрим стандартный запрос, который часто пишут на начальном этапе работы с СКД:


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

При выполнении такого запроса СУБД делает классическое LEFT JOIN. Если товар «Кирпич красный» закупался 10 раз за текущий месяц, то для одной строки остатков этого кирпича СУБД вернет 10 строк. В каждой строке будут продублированы показатели начального и конечного остатка, а цена закупки будет разной. Нам же необходимо получить только одно — последнее по времени — значение цены.

---

Решение 1: Классический «Срез последних» через временные таблицы в запросе

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

Разберем этот алгоритм по шагам:

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

Важное замечание: Простого поиска максимума по полю Период может быть недостаточно, если в пределах одной секунды было проведено несколько документов закупки. Чтобы избежать дублей в таком редком, но возможном случае, рекомендуется использовать МоментВремени или связку Период + Регистратор (либо НомерСтроки).

Рассмотрим пример реализации такого запроса:


// ШАГ 1: Находим максимальный период закупки для каждого товара
ВЫБРАТЬ
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика,
    МАКСИМУМ(Закупки.Период) КАК МаксПериод
ПОМЕСТИТЬ втМаксПериоды
ИЗ
    РегистрНакопления.Закупки КАК Закупки
ГРУППИРОВАТЬ ПО
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика
;

////////////////////////////////////////////////////////////////////////////////
// ШАГ 2: Получаем цену, сумму и количество на этот максимальный период
ВЫБРАТЬ
    втМаксПериоды.Номенклатура КАК Номенклатура,
    втМаксПериоды.Характеристика КАК Характеристика,
    СУММА(Закупки.Сумма) КАК Сумма,
    СУММА(Закупки.Количество) КАК Количество,
    ВЫБОР
        КОГДА СУММА(Закупки.Количество) <> 0
            ТОГДА СУММА(Закупки.Сумма) / СУММА(Закупки.Количество)
        ИНАЧЕ 0
    КОНЕЦ КАК СтоимостьЕдиницы
ПОМЕСТИТЬ втПоследниеЦены
ИЗ
    втМаксПериоды КАК втМаксПериоды
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрНакопления.Закупки КАК Закупки
        ПО втМаксПериоды.Номенклатура = Закупки.АналитикаУчетаНоменклатуры.Номенклатура
        И втМаксПериоды.Характеристика = Закупки.АналитикаУчетаНоменклатуры.Характеристика
        И втМаксПериоды.МаксПериод = Закупки.Период
ГРУППИРОВАТЬ ПО
    втМаксПериоды.Номенклатура,
    втМаксПериоды.Характеристика
;

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

Этот метод является классическим, он наиболее производителен на стороне СУБД и гарантирует, что для каждой строки остатков будет выведено ровно одно значение цены.

---

Разбор ошибки: почему умножение на ноль дает нулевой итог при UNION ALL?

Начинающие разработчики часто пытаются обойтись без соединений и используют оператор ОБЪЕДИНИТЬ ВСЕ (UNION ALL) в одной временной таблице, надеясь потом схлопнуть строки группировкой. Рассмотрим типичный ошибочный запрос:


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

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

ВЫБРАТЬ
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика,
    СУММА(0),
    СУММА(Закупки.Сумма / Закупки.Количество)
ИЗ
    РегистрНакопления.Закупки КАК Закупки
СГРУППИРОВАТЬ ПО
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика
;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    втСтоимость.Номенклатура,
    втСтоимость.Характеристика,
    СУММА(втСтоимость.ВНаличииРасход) КАК ВНаличииРасход,
    СУММА(втСтоимость.СтоимостьЕдиницы) КАК СтоимостьЕдиницы,
    // ВНИМАНИЕ: ОШИБКА ЗДЕСЬ!
    СУММА(втСтоимость.СтоимостьЕдиницы * втСтоимость.ВНаличииРасход) КАК СредняяСтоимость
ИЗ
    втСтоимость КАК втСтоимость
СГРУППИРОВАТЬ ПО
    втСтоимость.Номенклатура,
    втСтоимость.Характеристика

Разработчик замечает странность: простое суммирование полей ВНаличииРасход и СтоимостьЕдиницы отрабатывает корректно и выводит цифры, а вот при расчете поля СредняяСтоимость (произведение цены на расход) всегда получается жесткий ноль. Почему так происходит?

Выясним причину этого поведения. При объединении таблиц с помощью ОБЪЕДИНИТЬ ВСЕ строки не склеиваются по колонкам «вбок». Они дописываются друг под друга. Во временной таблице втСтоимость для одного и того же товара образуются две независимые строки:

Строка из остатков: ВНаличииРасход = 10, СтоимостьЕдиницы = 0.
Строка из закупок: ВНаличииРасход = 0, СтоимостьЕдиницы = 150.

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

Для первой строки: 10 * 0 = 0.
Для второй строки: 0 * 150 = 0.

Затем эти промежуточные произведения складываются: 0 + 0 = 0. В итоге мы получаем стабильный ноль.

Чтобы исправить это вычисление в рамках архитектуры объединения, умножать данные нужно уже **после группировки и суммирования**, вот так:

СУММА(втСтоимость.СтоимостьЕдиницы) * СУММА(втСтоимость.ВНаличииРасход)

Однако помните, что метод объединения (UNION ALL) в данном контексте все равно концептуально неверен, поскольку он не решает проблему получения именно последней цены закупки, а просто суммирует все когда-либо зарегистрированные цены из регистра закупок.

---

Решение 2: Использование возможностей СКД (Функции «Первая» и «Последняя»)

Система компоновки данных предоставляет разработчику мощный функционал, позволяющий избежать усложнения исходных SQL-запросов. Речь идет о функциях Первая() и Последняя() (в англоязычной версии платформы — First() и Last()).

Эти функции работают аналогично оконным функциям в традиционном SQL и позволяют вычислить значение поля в первой или последней строке определенной группировки. Для этого нам даже не придется писать сложный пакетный запрос.

Разберем по шагам, как настроить этот механизм:

В схеме запроса мы оставляем стандартное левое соединение регистра остатков с регистром закупок (даже если строки будут дублироваться на уровне детальных записей, СКД все исправит при выводе).
Переходим на вкладку «Вычисляемые поля» и создаем новое поле ЦенаЗакупки с выражением:
Выбор Когда КоличествоЗакупки <> 0 Тогда СуммаЗакупки / КоличествоЗакупки Иначе 0 Конец
Переходим на вкладку «Ресурсы».
Добавляем поле ЦенаЗакупки в ресурсы.
В поле «Выражение» пишем специальную системную функцию:
ВычислитьВыражение("Сумма(ЦенаЗакупки)", "Номенклатура", , "Последняя", "Последняя")
В колонке «Рассчитывать по» указываем нашу детальную группировку — например, Номенклатура или Характеристика.

Такой подход заставляет СКД самостоятельно отсортировать детальные записи и вернуть в группировку товара значение цены только из самой последней строки. Это значительно упрощает запрос и снижает нагрузку на базу данных.

---

Решение 3: Использование связей наборов данных в СКД

Еще один элегантный и производительный способ решить задачу получения «среза последних» в СКД — настроить два независимых набора данных и связать их на уровне компоновщика.

Посмотрим на пример настройки этой схемы:

НаборДанных1 (Остатки): Обычный запрос, получающий данные по остаткам товаров на складах.


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

НаборДанных2 (ПоследняяЦена): Запрос к регистру закупок с ограничением выборки и сортировкой по дате назад. Здесь мы будем выбирать ровно одну запись.


ВЫБРАТЬ ПЕРВЫЕ 1
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура КАК Номенклатура,
    Закупки.АналитикаУчетаНоменклатуры.Характеристика КАК Характеристика,
    Закупки.Сумма / Закупки.Количество КАК ПоследняяЦена
ИЗ
    РегистрНакопления.Закупки КАК Закупки
ГДЕ
    Закупки.АналитикаУчетаНоменклатуры.Номенклатура = &ПараметрНоменклатура
    И Закупки.АналитикаУчетаНоменклатуры.Характеристика = &ПараметрХарактеристика
УПОРАДОЧИТЬ ПО
    Закупки.Период УБЫВ

Теперь перейдем на вкладку «Связи наборов данных» и свяжем первый набор со вторым:

Источник связи: НаборДанных1
Приемник связи: НаборДанных2
Условие связи: Номенклатура = Номенклатура и Характеристика = Характеристика
Параметры: В колонке параметров укажем передачу полей связи в параметры приемника: Номенклатура -> ПараметрНоменклатура, Характеристика -> ПараметрХарактеристика.

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