Как в запросе 1С 7.7 отобрать товары с заданным итоговым количеством продаж за период?

← На главную

При разработке отчетов в платформе 1С:Предприятие 7.7 разработчики нередко сталкиваются с необходимостью отфильтровать позиции номенклатуры по агрегированным показателям за выбранный период — например, вывести только те товары, суммарные продажи которых за месяц составили ровно 10 штук.

Попытка наложить условие непосредственно на итоговую агрегатную функцию в тексте запроса приводит к ошибке выполнения. Разберем подробно, почему возникает эта ошибка, как устроен механизм фильтрации в 1С 7.7 и какими способами можно корректно решить данную задачу.

Почему возникает ошибка при обращении к агрегатной функции в условии?

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


//{{ЗАПРОС(Сформировать)
|Период с НачДата по КонДата;
|Док = Регистр.ОстаткиТМЦ.ТекущийДокумент;
|Номенклатура = Регистр.ОстаткиТМЦ.Номенклатура;
|Количество = Регистр.ОстаткиТМЦ.Количество;
|Функция КоличествоРасход = Расход(Количество) когда (Док.Вид() = "Реализация");
|Группировка Номенклатура без групп;
|Условие(КоличествоРасход = 10);
//}}ЗАПРОС

При запуске такого кода система сообщает, что переменная КоличествоРасход не определена. Выясним причину такого поведения встроенного языка запросов 1С 7.7.

В синтаксисе запросов 1С 7.7 секция Условие(...) является прямым аналогом конструкции WHERE в языке SQL. Она отрабатывает на этапе построчного сканирования физических таблиц базы данных — до того, как система начнет группировать данные и вычислять агрегаты. Конструкции Функция ... = ... (такие как Расход(), Приход(), Сумма()) рассчитываются позже, при обходе группировок. Платформа 1С 7.7 не поддерживает аналог конструкции HAVING в тексте запроса, поэтому наложить условие на агрегированный итог внутри самого запроса невозможно.

Если же попытаться вызвать пользовательскую функцию в секции Условие(...), проверяющую количество, фильтрация будет применяться к каждой отдельной строке документа, а не к итогу за весь период. Это приведет к неверным результатам — в отчет попадут документы с разовой продажей в 10 штук, а не позиции с суммарным месячным оборотом, равным 10.

Способ 1. Двухэтапный отбор через СписокЗначений

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

Выполняем первичный запрос по продажам с группировкой по номенклатуре без иерархии (без групп).
Обходим результат запроса и отбираем в объект СписокЗначений только те элементы, у которых агрегатная функция равна требуемому числу.
Формируем итоговый запрос или передаем полученный список в фильтр отчета через Условие(Номенклатура в СписокОтбора).

Посмотрим на пример реализации данного алгоритма:


Перем ЗапросПервичный, ЗапросИтоговый, СписокОтбора;

СписокОтбора = СоздатьОбъект("СписокЗначений");

// Шаг 1. Первичный сбор данных
ЗапросПервичный = СоздатьОбъект("Запрос");
ТекстПервичного = "
|Период с НачДата по КонДата;
|Номенклатура = Регистр.ОстаткиТМЦ.Номенклатура;
|Количество = Регистр.ОстаткиТМЦ.Количество;
|Док = Регистр.ОстаткиТМЦ.ТекущийДокумент;
|Функция КоличествоРасход = Расход(Количество) когда (Док.Вид() = ""Реализация"");
|Группировка Номенклатура без групп;
|Условие(Номенклатура в ВыбранныйСписок);
|";

Если ЗапросПервичный.Выполнить(ТекстПервичного) = 1 Тогда
    Пока ЗапросПервичный.Группировка("Номенклатура") = 1 Цикл
        Если ЗапросПервичный.КоличествоРасход = 10 Тогда
            СписокОтбора.ДобавитьЗначение(ЗапросПервичный.Номенклатура);
        КонецЕсли;
    КонецЦикла;
КонецЕсли;

// Шаг 2. Итоговый отчет с корректной иерархией
Если СписокОтбора.РазмерСписка() > 0 Тогда
    ЗапросИтоговый = СоздатьОбъект("Запрос");
    ТекстИтогового = "
    |Период с НачДата по КонДата;
    |Номенклатура = Регистр.ОстаткиТМЦ.Номенклатура;
    |Количество = Регистр.ОстаткиТМЦ.Количество;
    |Док = Регистр.ОстаткиТМЦ.ТекущийДокумент;
    |Функция КоличествоРасход = Расход(Количество) когда (Док.Вид() = ""Реализация"");
    |Группировка Номенклатура все;
    |Условие(Номенклатура в СписокОтбора);
    |";
    
    Если ЗапросИтоговый.Выполнить(ТекстИтогового) = 1 Тогда
        // Вывод данных в печатную форму с сохранением групп и итогов
        Пока ЗапросИтоговый.Группировка("Номенклатура") = 1 Цикл
            // Вывод строк и групп отчета
        КонецЦикла;
    КонецЕсли;
КонецЕсли;

Важное ограничение: данный способ отлично подходит для небольших и средних выборок. Если в СписокЗначений попадает более нескольких тысяч элементов, в файловой версии (DBF) может произойти падение производительности или переполнение стека, а в SQL-версии сгенерируется слишком длинное условие IN (...).

Способ 2. Выгрузка в ТаблицуЗначений и программная обработка

Рассмотрим вариант, при котором выполняется только один запрос к базе данных, а вся последующая фильтрация и расчет итогов производятся в оперативной памяти с помощью объекта ТаблицаЗначений.

Выполняем запрос без условий на количество.
Выгружаем выборку в ТаблицаЗначений методом Запрос.Выгрузить(ТЗ).
Удаляем строки, не удовлетворяющие условию (КоличествоРасход <> 10).
Обходим результирующую таблицу для вывода в печатную форму.

Посмотрим на пример кода:


Перем Запрос, ТЗ, НомСтр;

Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса = "
|Период с НачДата по КонДата;
|Номенклатура = Регистр.ОстаткиТМЦ.Номенклатура;
|Количество = Регистр.ОстаткиТМЦ.Количество;
|Док = Регистр.ОстаткиТМЦ.ТекущийДокумент;
|Функция КоличествоРасход = Расход(Количество) когда (Док.Вид() = ""Реализация"");
|Группировка Номенклатура без групп;
|";

Если Запрос.Выполнить(ТекстЗапроса) = 1 Тогда
    ТЗ = СоздатьОбъект("ТаблицаЗначений");
    Запрос.Выгрузить(ТЗ);
    
    // Фильтруем строки с конца к началу, чтобы не сбивать индексы при удалении
    Для НомСтр = -ТЗ.КоличествоСтрок() По -1 Цикл
        ТЗ.ТекущаяСтрока(-НомСтр);
        Если ТЗ.КоличествоРасход <> 10 Тогда
            ТЗ.УдалитьСтроку(-НомСтр);
        КонецЕсли;
    КонецЦикла;
    
    // Вывод отфильтрованных данных
    ТЗ.ВыбратьСтроки();
    Пока ТЗ.ПолучитьСтроку() = 1 Цикл
        // Формирование строк отчета
    КонецЦикла;
КонецЕсли;

Способ 3. Двухпроходный обход выборки объекта «Запрос»

Разберем еще один эффективный метод без повторных обращений к базе данных и без использования вспомогательных таблиц. Если отчет строится с иерархией по номенклатуре, мы можем выполнить двухпроходный анализ уже созданного объекта Запрос.

Алгоритм действий следующий:

Выполняем запрос с группировкой Группировка Номенклатура все;.
В первом цикле обходим только детальные записи элементов (Если Запрос.ЭтоГруппа() = 0). Если количество равно 10, добавляем сам элемент и всех его вышестоящих родителей (через свойство Номенклатура.Родитель) в СписокЗначений.
Сбрасываем выборку в начало и запускаем второй цикл обхода для непосредственного вывода в таблицу отчета. В секцию вывода отправляем только те группы и элементы, которые присутствуют в сформированном списке.

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

Способ 4. Использование внешней компоненты 1С++ и прямых запросов

Если в информационной базе используется внешняя компонента 1С++, задачу фильтрации итогов можно решить на уровне СУБД с помощью чистого SQL. Конструкция HAVING поддерживается напрямую:


SELECT
    $Рег.Номенклатура AS [Номенклатура $Справочник.Номенклатура],
    SUM($Рег.Количество) AS [КоличествоРасход]
FROM
    $Регистр.ОстаткиТМЦ AS Рег (NOLOCK)
INNER JOIN
    _1SJOURN AS Жур (NOLOCK) ON Жур.IDDOC = Рег.IDDOC
WHERE
    Жур.DATE_TIME_IDDOC BETWEEN :НачДата AND :КонДата
    AND $Рег.ФлагДвижения = 0 -- Расход
    AND Жур.IDDOCDEF = $ВидДокумента.Реализация
GROUP BY
    $Рег.Номенклатура
HAVING
    SUM($Рег.Количество) = 10

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