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

← На главную

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

Анализ исходной проблемы

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


ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныНоменклатуры КАК ЦеныНоменклатуры
ПО ПродажиОбороты.Номенклатура = ЦеныНоменклатуры.Номенклатура
И ПродажиОбороты.Характеристика = ЦеныНоменклатуры.Характеристика
И (ЦеныНоменклатуры.ВидЦен = &ТипЦен)
И ПродажиОбороты.Период >= ЦеныНоменклатуры.Период

Проанализируем, как работает условие ПродажиОбороты.Период >= ЦеныНоменклатуры.Период. Предположим, у товара «Монитор» цена менялась дважды: 01.01.2018 (10 000 руб.) и 01.11.2018 (12 000 руб.). В ноябре произошла продажа одной единицы товара. При выполнении запроса система найдет все записи в регистре цен, период которых меньше или равен дате продажи. В нашем примере под условие попадают обе записи. В результате вместо одной строки продажи в выборке появятся две. При последующей группировке функция СУММА сложит показатели из обеих строк, что и приведет к «задвоению» суммы и количества.

Решение №1: Использование виртуальной таблицы СрезПоследних

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

Перепишем запрос, заменив обращение к физической таблице регистра сведений:


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

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

Решение №2: Получение среза цен на каждую дату продажи («Двойное соединение»)

Если необходимо получить историческую цену для каждой строки продажи, применяется классический алгоритм «максимального периода». Разберем этот метод по шагам:

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

Рассмотрим пример кода:


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

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

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

Этот метод исключает задвоение, так как на шаге 2 для каждой продажи выбирается ровно одна (максимально близкая по времени) дата установки цены.

Решение №3: Оптимизация через Систему компоновки данных (СКД)

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

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

Дополнительные рекомендации по качеству кода

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

Использование ВЫБОР в условиях соединения: В сообщении 6 справедливо отмечено, что использование конструкций ВЫБОР КОГДА... непосредственно в секции ПО (условие связи) может негативно сказаться на производительности. Старайтесь выносить такие фильтры в параметры виртуальных таблиц или в секцию ГДЕ.
Индексация: При использовании временных таблиц (как в Решении №2) всегда индексируйте поля, по которым происходит соединение (Номенклатура, Характеристика). Это значительно ускорит работу запроса на больших объемах данных.
Обработка характеристик: Если в вашей конфигурации используются характеристики, помните, что цена может быть установлена как на конкретную характеристику, так и на номенклатуру в целом (с пустой характеристикой). В профессиональных отчетах часто используется функция ЕСТЬNULL для приоритетного поиска цены по характеристике и последующего поиска по «пустой» ссылке, если частная цена не задана.

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