При работе с языком запросов 1С начинающие разработчики часто сталкиваются с ситуацией, когда в итоговой выборке появляются лишние «пустые» строки или строки с нулевыми значениями. Попытка отфильтровать их с помощью конструкции ВЫБОР в секции ВЫБРАТЬ не приносит желаемого результата: строки остаются на месте, просто данные в них заменяются на пустые значения. В этой статье мы подробно разберем, почему так происходит, и изучим правильные механизмы фильтрации данных.
Проанализируем ситуацию, с которой столкнулся автор темы. Он пытался использовать следующий код непосредственно в списке выбора полей:
ВЫБОР
КОГДА ВРХ.Характеристика = &Характеристика
ТОГДА ОстаткиНоменклатурыОстаткиИОбороты.Номенклатура
ИНАЧЕ 0
КОНЕЦ
Важно понимать фундаментальный принцип работы СУБД: оператор ВЫБОР (аналог CASE в SQL) в секции ВЫБРАТЬ работает для каждой строки, которая уже попала в выборку. Если условие не выполняется, система записывает в ячейку значение из ветки ИНАЧЕ (в данном случае 0), но сама строка никуда не исчезает из результата запроса.
Представим, что в базе 100 записей. Если 10 из них подходят под условие, а 90 — нет, то при использовании ВЫБОР в секции ВЫБРАТЬ вы все равно получите 100 строк, где в 90 строках в колонке «Номенклатура» будет стоять ноль. Чтобы физически исключить эти 90 строк из результата, необходимо использовать механизмы отбора.
Самый простой и очевидный способ убрать ненужные строки — перенести условие в секцию ГДЕ. Этот блок предназначен именно для того, чтобы определять, какие записи должны попасть в итоговый результат, а какие — нет. Посмотрим, как должен выглядеть исправленный запрос:
ВЫБРАТЬ
ОстаткиНоменклатурыОстаткиИОбороты.Номенклатура КАК Номенклатура,
ОстаткиНоменклатурыОстаткиИОбороты.КоличествоОстаток КАК Количество
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, , , ) КАК ОстаткиНоменклатурыОстаткиИОбороты
ГДЕ
ОстаткиНоменклатурыОстаткиИОбороты.Характеристика = &Характеристика
В этом случае система сначала проверит условие в секции ГДЕ и выдаст только те строки, которые ему соответствуют. Никаких «пустых» или «нулевых» строк в результате не будет.
Рассмотрим более продвинутый и правильный с точки зрения производительности подход. Автор вопроса работает с виртуальной таблицей ОстаткиИОбороты. В 1С использование секции ГДЕ для виртуальных таблиц считается неэффективным.
Выясним причину: если вы ставите условие в ГДЕ, платформа сначала может выбрать все данные из регистра в память, а уже потом отфильтровать их. Если же вы используете параметры виртуальной таблицы, фильтрация происходит на уровне СУБД в момент получения данных. Это значительно ускоряет работу запроса.
Правильно настроить отбор в параметрах виртуальной таблицы можно следующим образом:
ВЫБРАТЬ
ОстаткиНоменклатурыОстаткиИОбороты.Номенклатура,
ОстаткиНоменклатурыОстаткиИОбороты.КоличествоОстаток
ИЗ
РегистрНакопления.ОстаткиНоменклатуры.ОстаткиИОбороты(
&НачалоПериода,
&КонецПериода,
,
,
Характеристика = &Характеристика // Вот здесь задается условие
) КАК ОстаткиНоменклатурыОстаткиИОбороты
В конструкторе запроса это делается на закладке «Таблицы и поля», нажатием кнопки «Параметры таблицы» (иконка с буквой «П» или скобками) рядом с выбранной виртуальной таблицей.
Часто пустые строки в запросах возникают из-за левых соединений (ЛЕВОЕ СОЕДИНЕНИЕ). Если в правой таблице нет соответствующей записи, 1С подставит значение NULL. Это не то же самое, что пустая ссылка или ноль.
Чтобы избежать проблем при дальнейшей обработке таких данных (например, если вы планируете суммировать эти поля), рекомендуется использовать функцию ЕСТЬNULL. Разберем на примере:
ВЫБРАТЬ
Номенклатура.Ссылка,
ЕСТЬNULL(Остатки.КоличествоОстаток, 0) КАК Остаток
ИЗ
Справочник.Номенклатура КАК Номенклатура
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки
ПО Номенклатура.Ссылка = Остатки.Номенклатура
ГДЕ
ЕСТЬNULL(Остатки.КоличествоОстаток, 0) > 0
Здесь мы сначала заменяем NULL на 0, а затем в секции ГДЕ отсекаем те позиции, по которым нет остатка. Это позволяет получить чистый список только тех товаров, которые реально есть на складе.
Иногда условие фильтрации нельзя наложить в ГДЕ, потому что оно касается итоговых (агрегированных) данных. Например, если мы хотим выбрать только ту номенклатуру, по которой общая сумма продаж превысила 1000 рублей. В этом случае мы используем ИМЕЮЩИЕ.
Проанализируем пример:
ВЫБРАТЬ
Продажи.Номенклатура,
СУММА(Продажи.СуммаОборот) КАК ОбщаяСумма
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
СГРУППИРОВАТЬ ПО
Продажи.Номенклатура
ИМЕЮЩИЕ
СУММА(Продажи.СуммаОборот) > 1000
Секция ИМЕЮЩИЕ работает уже после того, как все строки были сгруппированы, и позволяет «выкинуть» из результата те группы, которые не соответствуют условию.
Чтобы ваши отборы (в ГДЕ или в параметрах виртуальных таблиц) работали молниеносно, поля, по которым идет сравнение, должны быть проиндексированы.
Для эффективной работы с запросами в 1С придерживайтесь следующих правил:
ВЫБОР для фильтрации строк — он предназначен только для изменения данных внутри уже выбранной строки.Следуя этим принципам, вы не только избавитесь от пустых строк в отчетах, но и обеспечите высокую скорость работы ваших запросов даже на больших объемах данных.