Как правильно соединить виртуальную таблицу регистра и документы по дате в запросе 1С?

← На главную

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

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

Рассмотрим исходный запрос, в котором проявилась эта проблема:


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

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

В чем причина ошибки соединения?

В виртуальных таблицах оборотов регистров накопления с периодичностью День поле Период всегда возвращает начало суток, то есть дату со временем 00:00:00 (например, 01.02.2017 00:00:00).

В то же время реквизит Дата документа в платформе 1С хранит точный момент времени с точностью до секунды (например, 01.02.2017 14:35:12). Точное совпадение возможно только в том случае, если документ записан строго на начало дня без указания времени (00:00:00). Все остальные документы отсекаются условием соединения ПО КассыОбороты.Период = РасходнойДокумент.Дата.

Решение 1. Использование функции НАЧАЛОПЕРИОДА в условии соединения

Наиболее очевидный способ устранения проблемы — привести дату документа к началу дня прямо в секции ПО с помощью встроенной функции языка запросов НАЧАЛОПЕРИОДА:


ЛЕВОЕ СОЕДИНЕНИЕ Документ.РасходнойДокумент КАК РасходнойДокумент
    ПО (КассыОбороты.Период = НАЧАЛОПЕРИОДА(РасходнойДокумент.Дата, ДЕНЬ))

В этом случае обе стороны условия имеют нулевое время, и документы корректно распределяются по дням.

Важное ограничение: применение функции к индексированному полю таблицы документа делает условие соединения несаргабельным (non-sargable). СУБД (например, MS SQL Server или PostgreSQL) не сможет использовать стандартный индекс по полю Дата и будет вынуждена сканировать всю таблицу документов (Table Scan или Index Scan). Для небольших баз этот вариант допустим, но на высоконагруженных системах он вызовет заметные тормоза.

Решение 2. Сопоставление через диапазон дат (МЕЖДУ ... И ...)

Чтобы СУБД могла задействовать индекс по дате документа, можно проверить попадание даты документа в суточный интервал регистра:


ЛЕВОЕ СОЕДИНЕНИЕ Документ.РасходнойДокумент КАК РасходнойДокумент
    ПО РасходнойДокумент.Дата МЕЖДУ КассыОбороты.Период И КОНЕЦПЕРИОДА(КассыОбороты.Период, ДЕНЬ)

Посмотрим на особенности этого подхода:

Поле РасходнойДокумент.Дата участвует в сравнении без функций-оберток, что позволяет СУБД отсекать ненужные записи по индексу.
Однако соединение по диапазону исключает использование таких быстрых алгоритмов реляционной СУБД, как Hash Match или Merge Join, заставляя оптимизатор переключаться на соединение вложенными циклами (Nested Loops), что на широких интервалах дат также может снижать быстродействие.

Критические архитектурные ошибки исходного запроса

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

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

Решение 3. Предварительная агрегация документов во временной таблице (Рекомендуемое)

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

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


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

ИНДЕКСИРОВАТЬ ПО
    ПериодДень
;

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

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

Так как задача решается в отчете СКД, наилучшей практикой платформы является разделение несвязанных разнородных источников на отдельные наборы данных:

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

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