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