При разработке отчетов и обработок в 1С часто возникает задача отобрать элементы, удовлетворяющие определенному количественному критерию. Например, найти номенклатуру, которая числится более чем на двух складах по данным регистра сведений. Начинающие разработчики нередко решают эту задачу через создание временной таблицы: сначала помещают в нее сгруппированные данные с агрегатной функцией КОЛИЧЕСТВО, а затем во втором запросе пакета накладывают фильтр в секции ГДЕ.
Такой подход рабочий, однако он неоправданно усложняет текст запроса и создает лишнюю нагрузку на создание и чтение временных таблиц в базе данных. Разберем по шагам, как решить эту задачу в рамках одного запроса и какие существуют нюансы при работе с агрегатными функциями.
Проанализируем структуру языка запросов 1С. В то время как предложение ГДЕ фильтрует исходные строки до выполнения группировки, специальное предложение ИМЕЮЩИЕ предназначено именно для фильтрации уже сгруппированных строк по результатам вычисления агрегатных функций (КОЛИЧЕСТВО, СУММА, МИНИМУМ, МАКСИМУМ, СРЕДНЕЕ).
Рассмотрим правила написания условия в секции ИМЕЮЩИЕ:
ИМЕЮЩИЕ нельзя использовать псевдонимы полей из секции ВЫБРАТЬ. Агрегатное выражение необходимо указывать полностью.ИМЕЮЩИЕ может применяться только совместно с секцией СГРУППИРОВАТЬ ПО.> 2 (или нестрогое >= 3). Условие > 1 отберет позиции с двумя и более складами.Посмотрим на пример итогового запроса:
ВЫБРАТЬ
РегистрСведений1.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РегистрСведений1.Склад) КАК КоличествоВхожденийВСклад
ИЗ
РегистрСведений.РегистрСведений1 КАК РегистрСведений1
СГРУППИРОВАТЬ ПО
РегистрСведений1.Номенклатура
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РегистрСведений1.Склад) > 2
Выясним причину возможных расхождений в результатах: функция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) автоматически отсекает значения NULL, однако пустая ссылка на справочник складов считается полноценным уникальным значением. Если в регистре присутствуют некорректные записи с незаполненным измерением Склад, они будут посчитаны СУБД как отдельный склад.
Чтобы избежать искажения выборки, отсечем пустые значения еще до группировки в блоке ГДЕ:
ВЫБРАТЬ
РегистрСведений1.Номенклатура КАК Номенклатура
ИЗ
РегистрСведений.РегистрСведений1 КАК РегистрСведений1
ГДЕ
РегистрСведений1.Склад <> ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)
СГРУППИРОВАТЬ ПО
РегистрСведений1.Номенклатура
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ РегистрСведений1.Склад) > 2
Если по каким-либо причинам требуется обращаться к вычисленному агрегату по псевдониму без использования ИМЕЮЩИЕ, задачу можно решить через подзапрос в секции ИЗ без создания временной таблицы в tempdb СУБД:
ВЫБРАТЬ
ВложенныйЗапрос.Номенклатура КАК Номенклатура
ИЗ
(ВЫБРАТЬ
Регистр.Номенклатура КАК Номенклатура,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Регистр.Склад) КАК КолВоСкладов
ИЗ
РегистрСведений.РегистрСведений1 КАК Регистр
СГРУППИРОВАТЬ ПО
Регистр.Номенклатура) КАК ВложенныйЗапрос
ГДЕ
ВложенныйЗапрос.КолВоСкладов > 2
В этом случае СУБД оптимизирует план выполнения запроса, а во внешнем блоке можно применить стандартную фильтрацию ГДЕ.
Рассмотрим ситуацию на больших объемах данных. Конструкция КОЛИЧЕСТВО(РАЗЛИЧНЫЕ) требует построения хэш-таблицы или предварительной сортировки для исключения дубликатов. Если нам не нужно выводить точное число складов, а достаточно лишь факта их наличия более двух (то есть минимум трех разных складов), эффективным решением может стать соединение регистра с самим собой:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Т1.Номенклатура КАК Номенклатура
ИЗ
РегистрСведений.РегистрСведений1 КАК Т1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений1 КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.Склад < Т2.Склад
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.РегистрСведений1 КАК Т3
ПО Т1.Номенклатура = Т3.Номенклатура
И Т2.Склад < Т3.Склад
При таком подходе СУБД прерывает сопоставление по каждой номенклатуре сразу при нахождении третьей уникальной записи, не сканируя весь остальной массив строк по данной позиции.
Для непериодического независимого регистра сведений ключевое значение имеет порядок измерений в конфигураторе. СУБД строит основной индекс строго по порядку следования измерений. Если измерение Номенклатура находится первым, а Склад — вторым, то при группировке по номенклатуре СУБД задействует быстрый потоковый агрегат без необходимости промежуточной сортировки всего объема данных таблицы.