В практике разработки на платформе 1С:Предприятие часто возникают задачи, связанные с нетривиальным анализом содержимого табличных частей документов. Одна из таких задач — поиск документов, у которых внутри табличной части на одну и ту же дату приходится несколько разных номенклатурных позиций. При этом, если за конкретную дату отгружался только один и тот же товар (даже если строк было несколько), такой документ должен быть пропущен.
Давайте подробно разберем эту задачу на конкретном примере. Представим, что у нас есть два документа:
Ниже мы подробно рассмотрим три способа решения этой задачи на языке запросов 1С: от простого классического варианта с форума до профессиональных высокопроизводительных методов оптимизации.
Это решение, к которому пришли участники обсуждения в процессе поиска лаконичного кода. Оно позволяет решить задачу в рамках одного запроса без использования временных таблиц. Основная идея заключается в группировке строк табличной части по документу и дате с последующей фильтрацией через предложение ИМЕЮЩИЕ.
Рассмотрим структуру этого запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПоступлениеТовары.Ссылка КАК Ссылка
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК ПоступлениеТовары
СГРУППИРОВАТЬ ПО
ПоступлениеТовары.Ссылка,
ПоступлениеТовары.Дата
ИМЕЮЩИЕ
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ПоступлениеТовары.Номенклатура) > 1
УПОРЯДОЧИТЬ ПО
Ссылка
Давайте разберем по шагам, как работает этот запрос:
Товары документа ПоступлениеТоваровУслуг.Ссылка (сам документ) и Дата (дата отгрузки внутри табличной части).ИМЕЮЩИЕ КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) мы накладываем условие на агрегатную функцию. Платформа подсчитывает количество уникальных значений в поле Номенклатура для каждой группы (Документ + Дата). Если уникальных товаров больше одного, эта группа проходит фильтр.ВЫБРАТЬ РАЗЛИЧНЫЕ на самом верхнем уровне гарантирует, что если в одном документе было несколько дат с «разнородными» товарами, сам документ (Ссылка) появится в итоговой выборке только один раз.Важное замечание: Несмотря на лаконичность и внешнюю простоту, данный вариант имеет скрытую проблему производительности на уровне СУБД (MS SQL, PostgreSQL и др.). Операция COUNT(DISTINCT ...) заставляет СУБД выполнять ресурсоемкую сортировку уникальных значений для каждой группы «на лету». На больших базах данных этот запрос может выполняться крайне медленно.
Выясним, как решить задачу максимально эффективно с точки зрения СУБД. Профессиональные разработчики 1С стараются избегать тяжелых группировок с КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...), заменяя их на внутреннее соединение таблицы самой с собой (самосоединение). Мы ищем ситуацию, когда для одной и той же ссылки и одной и той же даты в таблице существуют две записи с разными значениями номенклатуры.
Посмотрим на пример такого запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Товары1.Ссылка КАК Ссылка
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары1
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Документ.ПоступлениеТоваровУслуг.Товары КАК Товары2
ПО Товары1.Ссылка = Товары2.Ссылка
И Товары1.Дата = Товары2.Дата
И Товары1.Номенклатура <> Товары2.Номенклатура
Проанализируем ситуацию и поймем, почему этот вариант работает в разы быстрее:
Ссылка, который автоматически создается для всех табличных частей документов 1С.Товары1.Номенклатура <> Товары2.Номенклатура), СУБД сразу выдает результат. Ей не нужно сканировать и пересчитывать абсолютно все строки документа, чтобы узнать точное число уникальных позиций.СГРУППИРОВАТЬ ПО и ИМЕЮЩИЕ, снизив нагрузку на процессор сервера СУБД.Если специфика вашей задачи всё же требует использования агрегатных функций или подсчета точного количества различных позиций, хорошей практикой разработки является разделение сложной операции на простые шаги с использованием временных таблиц. Это позволяет СУБД построить оптимальный план запроса.
Рассмотрим этот двухшаговый пакетный запрос:
// Шаг 1: Сворачиваем дубли номенклатуры за одинаковые даты в рамках каждого документа
ВЫБРАТЬ РАЗЛИЧНЫЕ
Товары.Ссылка КАК Ссылка,
Товары.Дата КАК Дата,
Товары.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_УникальныеСтроки
ИЗ
Документ.ПоступлениеТоваровУслуг.Товары КАК Товары
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Группируем свернутые данные и считаем простым КОЛИЧЕСТВО (без РАЗЛИЧНЫЕ)
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ.Ссылка КАК Ссылка
ИЗ
ВТ_УникальныеСтроки КАК ВТ
СГРУППИРОВАТЬ ПО
ВТ.Ссылка,
ВТ.Дата
ИМЕЮЩИЕ
КОЛИЧЕСТВО(ВТ.Номенклатура) > 1
Почему этот вариант превосходит первый вариант с форума?
В первом запросе пакета СУБД быстро выбирает уникальные тройки полей и сохраняет их во временную таблицу ВТ_УникальныеСтроки. На втором шаге мы работаем уже со «свернутыми» данными. Вместо ресурсоемкой функции КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ...) мы применяем обычную функцию КОЛИЧЕСТВО(...), которая выполняется мгновенно, так как уникальность записей уже гарантирована структурой временной таблицы.
При переносе любого из этих запросов в рабочую конфигурацию крайне важно учесть следующие архитектурные особенности платформы 1С:
Обращение к табличной части документа напрямую без ограничений (условий ГДЕ) приведет к полному сканированию всей таблицы БД (Full Table Scan). В реальной системе обязательно ограничивайте выборку периодом, признаком проведения документа или конкретной организацией:
ГДЕ
Товары1.Ссылка.Проведен = ИСТИНА
И Товары1.Ссылка.Дата МЕЖДУ &НачалоПериода И &КонецПериода
Если в табличной части окажутся строки с незаполненной номенклатурой (например, случайно добавленная пустая строка), запрос может посчитать пустую ссылку за «разную номенклатуру» по сравнению с заполненным товаром. Во избежание ложных срабатываний рекомендуется исключать пустые ссылки:
ГДЕ
Товары1.Номенклатура <> ЗНАЧЕНИЕ(Справочник.Номенклатура.ПустаяСсылка)
В типовых конфигурациях 1С реквизиты табличных частей по умолчанию не индексируются. Если реквизит Дата (дата отгрузки) внутри табличной части используется регулярно для подобных выборок, в свойствах этого реквизита в конфигураторе рекомендуется установить свойство «Индексировать». Это значительно ускорит выполнение соединений и группировок на СУБД.