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