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