При построении отчетов и аналитических выборок в 1С часто возникает задача: получить количество или сумму документов по всем доступным категориям (статусам, типам событий, номенклатуре), даже если за выбранный период движений по некоторым позициям не было. В стандартном запросе без соединения строки с нулевыми значениями просто отсекаются, что искажает итоговую картину.
Разберем типичные ошибки при решении этой задачи и рассмотрим правильные варианты построения запроса с использованием левого соединения и предварительной фильтрации данных.
Проанализируем исходную ситуацию. Предположим, у нас есть список из 8 типов событий, но в выборку попадает только 6. Причиной отсутствия остальных двух позиций является фильтрация в секции ГДЕ по таблице документов: если документа с нужным типом события нет, строка вовсе не создается.
Посмотрим на типичные ошибки, которые часто допускают разработчики при попытке исправить эту ситуацию:
ГДЕ: если мы выполняем ЛЕВОЕ СОЕДИНЕНИЕ со справочником типов или временной таблицей, а затем в секции ГДЕ накладываем условие на поля правой таблицы (например, Событие.Дата МЕЖДУ &Дата И &Дата1), то для отсутствующих записей поля правой таблицы содержат NULL. Условие сравнения с NULL дает ЛОЖЬ, и левое соединение превращается во внутреннее, исключая нужные строки.ВЫБРАТЬ и СГРУППИРОВАТЬ ПО указать поле присоединяемой таблицы (например, Событие.МС_ТипСобытия), то для всех отсутствующих строк значением будет NULL. В итоге все категории без движений схлопнутся в одну строку с пустым значением. Всегда выбираем и группируем поле ведущей (левой) таблицы.КОЛИЧЕСТВО(*) вместо КОЛИЧЕСТВО(Поле): конструкция КОЛИЧЕСТВО(*) считает само наличие строки в результате соединения. Для записей левой таблицы, к которым ничего не присоединилось, такая конструкция вернет 1, а не 0. Для корректного подсчета используем КОЛИЧЕСТВО(Событие.Ссылка), так как агрегатная функция КОЛИЧЕСТВО игнорирует значения NULL.Рассмотрим первый способ решения. В качестве ведущей таблицы мы выбираем справочник категорий (или типов событий), фильтруем его по переданному списку в секции ГДЕ, а все условия отбора документов переносим непосредственно в условие связи ПО.
Посмотрим на текст запроса:
ВЫБРАТЬ
ТипыСобытий.Ссылка КАК МС_ТипСобытия,
КОЛИЧЕСТВО(Событие.Ссылка) КАК Количество
ИЗ
Справочник.ТипыСобытий КАК ТипыСобытий
ЛЕВОЕ СОЕДИНЕНИЕ Документ.Событие КАК Событие
ПО Событие.МС_ТипСобытия = ТипыСобытий.Ссылка
И Событие.Дата МЕЖДУ &Дата И &Дата1
И Событие.Состояние = &Состояние
И Событие.НачалоСледующегоСобытия < &НачалоСледующегоСобытия
ГДЕ
ТипыСобытий.Ссылка В(&МС_ТипСобытия)
СГРУППИРОВАТЬ ПО
ТипыСобытий.Ссылка
Выясним, как работает этот запрос: благодаря тому, что условия на даты и состояние документа находятся в секции ПО, они ограничивают только присоединение строк документа. Строки из таблицы Справочник.ТипыСобытий сохраняются в полном объеме, а функция КОЛИЧЕСТВО(Событие.Ссылка) возвращает 0 для тех статусов, по которым документы не найдены.
Если документов в базе много, соединение большой таблицы документов с условиями в ПО может создавать неоптимальный план выполнения в СУБД. Разберем более производительный подход с использованием временной таблицы, в которую предварительно отбираются только нужные документы за период.
Разберем по шагам реализацию через пакетный запрос:
ЛЕВОЕ СОЕДИНЕНИЕ справочника (или внешнего списка) с подготовленной временной таблицей.Посмотрим на пример программного кода на языке 1С:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Событие.Ссылка КАК Ссылка,
| Событие.МС_ТипСобытия КАК МС_ТипСобытия
|ПОМЕСТИТЬ ВТ_ОтобранныеСобытия
|ИЗ
| Документ.Событие КАК Событие
|ГДЕ
| Событие.Дата МЕЖДУ &Дата И &Дата1
| И Событие.Состояние = &Состояние
| И Событие.НачалоСледующегоСобытия < &НачалоСледующегоСобытия
| И Событие.МС_ТипСобытия В(&МС_ТипСобытия)
|
|ИНДЕКСИРОВАТЬ ПО
| МС_ТипСобытия
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| ТипыСобытий.Ссылка КАК МС_ТипСобытия,
| КОЛИЧЕСТВО(ВТ_ОтобранныеСобытия.Ссылка) КАК Количество
|ИЗ
| Справочник.ТипыСобытий КАК ТипыСобытий
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ОтобранныеСобытия КАК ВТ_ОтобранныеСобытия
| ПО ТипыСобытий.Ссылка = ВТ_ОтобранныеСобытия.МС_ТипСобытия
|ГДЕ
| ТипыСобытий.Ссылка В(&МС_ТипСобытия)
|
|СГРУППИРОВАТЬ ПО
| ТипыСобытий.Ссылка
|АВТОУПОРЯДОЧИВАНИЕ";
Запрос.УстановитьПараметр("Дата", НачалоДня(ДатаОтчета));
Запрос.УстановитьПараметр("Дата1", КонецДня(ДатаОтчета));
Запрос.УстановитьПараметр("МС_ТипСобытия", СписокТипов);
Запрос.УстановитьПараметр("Состояние", Справочники.СостоянияСобытий.Запланировано);
Запрос.УстановитьПараметр("НачалоСледующегоСобытия", ДатаОтчета);
РезультатЗапроса = Запрос.Выполнить();
Рассмотрим важные нюансы, которые следует учитывать при работе с подобными выборками:
ЕСТЬNULL: для функции КОЛИЧЕСТВО(Поле) оборачивание в ЕСТЬNULL(..., 0) не требуется, так как она всегда возвращает числовой тип. Однако при подсчете сумм с помощью СУММА(Поле) использование ЕСТЬNULL(СУММА(Поле), 0) строго обязательно, поскольку сумма по пустым записям вернет NULL.В(&Массив) к реальной таблице справочника, вместо передачи непримитивной ТаблицыЗначений во временную таблицу.ВЫРАЗИТЬ(Событие.МС_ТипСобытия КАК СправочникСсылка.ТипыСобытий) для корректной трансляции запроса на уровне СУБД.