При работе с отчетами и выборками данных часто возникает задача скрыть положительные результаты и оставить только те строки, где конечное сальдо или сумма по группировке меньше нуля. В языке запросов 1С существует несколько способов решения этой задачи в зависимости от того, как именно строятся данные: через группировку, через секцию расчета итогов или в системе компоновки данных (СКД). Рассмотрим эти способы подробно, проанализируем их различия и выберем оптимальный подход.
Прежде всего, нам необходимо четко разграничить два механизма фильтрации: конструкцию ГДЕ и конструкцию ИМЕЮЩИЕ. Это критически важный момент, так как их неправильное использование приведет к неверным результатам в отчете.
Рассмотрим ситуацию: у нас есть клиент, у которого было два движения — приход 100 рублей и расход 150 рублей. Итоговый остаток составляет -50 рублей.
ГДЕ СуммаВзаиморасчетов < 0, система сначала отсечет строку с приходом (100 руб.), так как она больше нуля. В расчет попадет только строка -150 руб. Итоговый результат будет -150, что не соответствует действительности.Меньше 0. Это именно то, что обычно требуется при анализе задолженности.Если вы используете в запросе секцию СГРУППИРОВАТЬ ПО, то для фильтрации итоговых значений следует применять ключевое слово ИМЕЮЩИЕ. Важно помнить, что в этой секции нельзя использовать псевдонимы полей, заданные через КАК. Нужно полностью повторять агрегатную функцию.
Проанализируем пример кода:
ВЫБРАТЬ
ВзаиморасчетыОстатки.Контрагент КАК Контрагент,
СУММА(ВзаиморасчетыОстатки.СуммаОстаток) КАК СуммаИтог
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки(&Период, ) КАК ВзаиморасчетыОстатки
СГРУППИРОВАТЬ ПО
ВзаиморасчетыОстатки.Контрагент
ИМЕЮЩИЕ
СУММА(ВзаиморасчетыОстатки.СуммаОстаток) < 0
В данном примере мы сначала группируем все записи по контрагентам, вычисляем общую сумму остатка для каждого, а затем отсекаем тех, у кого итоговая сумма оказалась положительной или равной нулю. Обратите внимание на использование СУММА(...) внутри условия ИМЕЮЩИЕ.
Если в вашем запросе используется специфическая для 1С секция ИТОГИ ... ПО ... для построения иерархических выборок, ситуация усложняется. Конструкция ИМЕЮЩИЕ фильтрует только детальные записи или записи группировок, но она не всегда корректно скрывает "ветки" дерева в секции ИТОГИ.
Если вам нужно, чтобы в иерархическом дереве не отображались пустые группы, в которых нет отрицательных итогов, то чисто средствами текста запроса это сделать сложно. В таких случаях мы рекомендуем использовать временные таблицы.
Для сложных запросов, где расчет итогов идет по большим массивам данных, использование ИМЕЮЩИЕ может замедлять работу СУБД, так как фильтрация происходит на финальном этапе выборки. Рассмотрим более производительный подход с использованием временной таблицы.
Разберем ситуацию по шагам:
ГДЕ.
// Помещаем сгруппированные данные во временную таблицу
ВЫБРАТЬ
Взаиморасчеты.Контрагент,
СУММА(Взаиморасчеты.СуммаОстаток) КАК СуммаОстаток
ПОМЕСТИТЬ ВТ_Итоги
ИЗ
РегистрНакопления.Взаиморасчеты.Остатки КАК Взаиморасчеты
СГРУППИРОВАТЬ ПО
Взаиморасчеты.Контрагент
;
// Выбираем только отрицательные значения из временной таблицы
ВЫБРАТЬ
ВТ_Итоги.Контрагент,
ВТ_Итоги.СуммаОстаток
ИЗ
ВТ_Итоги КАК ВТ_Итоги
ГДЕ
ВТ_Итоги.СуммаОстаток < 0
Такой метод позволяет СУБД построить более эффективный план запроса, особенно если в дальнейшем вам нужно соединять эти отрицательные итоги с другими таблицами (например, с контактной информацией или данными договоров).
Если вы разрабатываете отчет, наиболее правильным и гибким способом будет вынос условия на уровень настроек СКД. Это позволит пользователю при необходимости самостоятельно изменить фильтр (например, посмотреть только положительные итоги или все сразу).
Выясним, как настроить это в схеме:
СуммаОстаток) и установите вид сравнения Меньше, а значение 0.Важный нюанс: СКД автоматически понимает, что условие накладывается на ресурс (агрегат), и сама добавит конструкцию HAVING (ИМЕЮЩИЕ) в итоговый SQL-запрос, который отправится на сервер базы данных.
Проанализируем ситуацию, когда данных для суммирования нет вообще. В языке запросов 1С результат агрегатной функции СУММА от отсутствующих записей вернет NULL. Условие NULL < 0 не является истинным, поэтому такие строки не попадут в результат.
Если по какой-то причине вам нужно рассматривать отсутствие данных как ноль (хотя в контексте "только отрицательные" это обычно не требуется), используйте функцию ЕСТЬNULL:
ИМЕЮЩИЕ ЕСТЬNULL(СУММА(СуммаОстаток), 0) < 0
Для решения задачи вывода только отрицательных итогов мы рекомендуем:
ИМЕЮЩИЕ СУММА(...) < 0 в простых запросах с группировкой.