При работе с данными в 1С, особенно при формировании отчетов или аналитических справок, часто возникает задача преобразования данных, когда значения из одной колонки (например, тип материала) нужно представить в виде отдельных колонок, содержащих агрегированные данные (например, общие веса) для каждого типа. Этот процесс известен как пивотинг или "поворот таблицы".
Давайте разберем типичную ситуацию и предложим эффективные решения для ее реализации в языке запросов 1С.
Предположим, у нас есть временная таблица ВТ_ЗагруженныеМатериалы, которая содержит информацию о загруженных в производственный котел материалах. В этой таблице каждая строка представляет собой запись о конкретной номенклатуре с характеристикой, типом композиции и загруженным весом. Пример такой таблицы может выглядеть следующим образом:
Номенклатура | Хар-ка | тип Композиции | Вес загружено
------------------------------------------------------
ном1 | хар1 | ПВД | 5
ном2 | хар2 | ПВД | 5
ном3 | хар3 | ПНД | 3
ном4 | хар4 | ЛИН | 4
Наша цель – преобразовать эту таблицу таким образом, чтобы получить одну строку, где каждая колонка будет соответствовать определенному типу композиции, а значение в этой колонке будет суммарным весом всех материалов данного типа. То есть, желаемый результат должен быть таким:
ВсегоПВД | ВсегоПНД | ВсегоЛИН
-------------------------------
10 | 3 | 4
Рассмотрим, как мы можем достичь этого результата, анализируя как ошибочные, так и правильные подходы.
ГДЕАвтор исходного запроса попытался решить задачу, используя несколько левых соединений (ЛЕВОЕ СОЕДИНЕНИЕ) одной и той же временной таблицы с самой собой, а затем применяя условия по типу композиции в секции ГДЕ. Давайте подробно рассмотрим этот подход и выясним, почему он привел к пустому результату.
Исходный запрос автора выглядел так:
|ВЫБРАТЬ
| ЗагруженныеВКотёлМатериалы.Номенклатура КАК Номенклатура,
| ЗагруженныеВКотёлМатериалы.Характеристика КАК Характеристика,
| ЗагруженныеВКотёлМатериалы.Упаковка КАК Упаковка,
| ЗагруженныеВКотёлМатериалы.ВидКомпозиции КАК ВидКомпозиции,
| ЗагруженныеВКотёлМатериалы.ВесНетто КАК ВесНетто,
| ЗагруженныеВКотёлМатериалы.Количество КАК Количество
|ПОМЕСТИТЬ ВТ_ЗагруженныеВКотёлМатериалы
|ИЗ
| РегистрСведений.ЗагруженныеВКотёлМатериалы КАК ЗагруженныеВКотёлМатериалы
|ГДЕ
| ЗагруженныеВКотёлМатериалы.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
| И НЕ ЗагруженныеВКотёлМатериалы.Распределена
| И ЗагруженныеВКотёлМатериалы.ПроизводственнаяПлощадка = &ПроизводственнаяПлощадка
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| СУММА(ЕСТЬNULL(ВТ_ЗагруженныеВКотёлМатериалы1.ВесНетто, 0)) КАК ЗагруженоПВД,
| СУММА(ЕСТЬNULL(ВТ_ЗагруженныеВКотёлМатериалы2.ВесНетто, 0)) КАК ЗагруженоПНД,
| СУММА(ЕСТЬNULL(ВТ_ЗагруженныеВКотёлМатериалы3.ВесНетто, 0)) КАК ЗагруженоЛИН
|ПОМЕСТИТЬ ВТ_ЗагруженныеВКотёлМатериалыСгруп
|ИЗ
| ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы1
| ПО ВТ_ЗагруженныеВКотёлМатериалы.Номенклатура = ВТ_ЗагруженныеВКотёлМатериалы1.Номенклатура
| И ВТ_ЗагруженныеВКотёлМатериалы.Характеристика = ВТ_ЗагруженныеВКотёлМатериалы1.Характеристика
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы2
| ПО ВТ_ЗагруженныеВКотёлМатериалы.Номенклатура = ВТ_ЗагруженныеВКотёлМатериалы2.Номенклатура
| И ВТ_ЗагруженныеВКотёлМатериалы.Характеристика = ВТ_ЗагруженныеВКотёлМатериалы2.Характеристика
| ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы3
| ПО ВТ_ЗагруженныеВКотёлМатериалы.Номенклатура = ВТ_ЗагруженныеВКотёлМатериалы3.Номенклатура
| И ВТ_ЗагруженныеВКотёлМатериалы.Характеристика = ВТ_ЗагруженныеВКотёлМатериалы3.Характеристика
|ГДЕ
| ВТ_ЗагруженныеВКотёлМатериалы1.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД
| И ВТ_ЗагруженныеВКотёлМатериалы2.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПНД
| И ВТ_ЗагруженныеВКотёлМатериалы3.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииЛИН
|;
Этот запрос не сработает должным образом и вернет пустой результат. Давайте проанализируем причину.
ЛЕВОЕ СОЕДИНЕНИЕ: Автор присоединяет одну и ту же таблицу ВТ_ЗагруженныеВКотёлМатериалы трижды (ВТ_ЗагруженныеВКотёлМатериалы1, ВТ_ЗагруженныеВКотёлМатериалы2, ВТ_ЗагруженныеВКотёлМатериалы3). Условие соединения основано на Номенклатура и Характеристика. Само по себе это не является ошибкой, но не совсем соответствует логике "пивотинга".ГДЕ: Главная проблема заключается в условиях, примененных в секции ГДЕ ко вторичным таблицам (1, 2, 3) после ЛЕВОГО СОЕДИНЕНИЯ:
ГДЕ
ВТ_ЗагруженныеВКотёлМатериалы1.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД
И ВТ_ЗагруженныеВКотёлМатериалы2.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПНД
И ВТ_ЗагруженныеВКотёлМатериалы3.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииЛИН
ЛЕВОЕ СОЕДИНЕНИЕ, если для какой-то строки основной таблицы (ВТ_ЗагруженныеВКотёлМатериалы) не находится соответствие в присоединяемой таблице (например, в ВТ_ЗагруженныеВКотёлМатериалы1), то все поля из присоединяемой таблицы будут заполнены значениями NULL.
Если затем мы применяем условие в ГДЕ, например, ВТ_ЗагруженныеВКотёлМатериалы1.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД, и ВТ_ЗагруженныеВКотёлМатериалы1.ВидКомпозиции является NULL (из-за отсутствия совпадения при соединении), то данное условие не будет истинным (NULL = Значение всегда ложно). Следовательно, вся строка будет отфильтрована. Поскольку для любой данной строки из исходной таблицы крайне маловероятно, что она будет иметь одновременно ВидКомпозиции ПВД, ПНД и ЛИН (поскольку это разные типы), все эти условия в ГДЕ будут истинными только в исключительно редких, если не невозможных, случаях. В итоге запрос отфильтрует все строки, и результат будет пустым.
ГДЕ применяется *до* агрегации (СУММА) и *после* выполнения соединений. Таким образом, к моменту, когда должна произойти агрегация, все нужные данные уже отфильтрованы.Таким образом, подход с множественными левыми соединениями и фильтрацией в ГДЕ не подходит для решения задачи пивотинга в данном случае.
СУММА(ВЫБОР КОГДА...)Наиболее распространенным, эффективным и гибким способом преобразования строк в столбцы (пивотинга) в языке запросов 1С является использование агрегатной функции СУММА в сочетании с условным оператором ВЫБОР КОГДА. Этот метод позволяет обрабатывать все данные за один проход, создавая новые столбцы на основе условий.
Давайте рассмотрим подробнее, как мы можем применить этот подход к нашей задаче.
ВТ_ЗагруженныеВКотёлМатериалы. Для каждого желаемого нового столбца (ВсегоПВД, ВсегоПНД, ВсегоЛИН) мы будем создавать отдельное выражение внутри секции ВЫБРАТЬ.ВЫБОР КОГДА: Для каждого элемента мы проверяем ВидКомпозиции. Если он соответствует определенному типу (например, ПВД), то мы берем значение ВесНетто этой строки. Если же тип не соответствует, мы подставляем 0.СУММА: Затем мы суммируем все эти значения по всем строкам. Таким образом, для колонки "ВсегоПВД" будут просуммированы только те ВесНетто, у которых ВидКомпозиции был "ПВД", а для остальных строк будет добавлен 0, что не повлияет на сумму.Пример запроса:
ВЫБРАТЬ
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоПВД,
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПНД ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоПНД,
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииЛИН ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоЛИН
ИЗ
ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы
Давайте подробно разберем каждый элемент этого запроса:
ВЫБРАТЬ: Секция, где мы определяем, какие данные хотим получить в результате.СУММА(...): Агрегатная функция, которая суммирует значения, переданные ей в качестве аргумента. В нашем случае, она просуммирует веса, соответствующие определенным условиям.ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ 0 КОНЕЦ: Это условный оператор. Для каждой строки исходной таблицы он выполняет проверку:
ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД: Проверяет, соответствует ли текущий ВидКомпозиции значению параметра &ПеречислениеВидыПолиэтиленаКомпозицииПВД (т.е., ПВД).ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто: Если условие истинно, то для данной строки будет взято значение ВесНетто.ИНАЧЕ 0: Если условие ложно (то есть ВидКомпозиции не равен ПВД), то для данной строки будет взято значение 0. Это критически важный элемент. Если бы мы его не указали или указали ИНАЧЕ NULL, то функция СУММА проигнорировала бы эти значения, что могло бы привести к неверным результатам или необходимости использовать ЕСТЬNULL вокруг всего выражения ВЫБОР КОГДА. Указание ИНАЧЕ 0 гарантирует, что строки, не соответствующие условию, корректно обрабатываются как нулевой вклад в сумму.КАК ВсегоПВД: Присваиваем результирующей колонке понятное имя.ИЗ ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы: Указываем исходную временную таблицу, из которой берем данные.Преимущества этого подхода:
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииСПЛАВ ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоСПЛАВ, не меняя структуру соединений.ИНАЧЕ 0, если для какого-либо типа композиции нет ни одной записи в таблице, соответствующая колонка будет иметь значение 0, а не NULL.Прежде чем выполнять запрос на пивотинг, нам необходимо корректно сформировать исходную временную таблицу ВТ_ЗагруженныеВКотёлМатериалы. Авторский код для ее создания вполне корректен:
|ВЫБРАТЬ
| ЗагруженныеВКотёлМатериалы.Номенклатура КАК Номенклатура,
| ЗагруженныеВКотёлМатериалы.Характеристика КАК Характеристика,
| ЗагруженныеВКотёлМатериалы.Упаковка КАК Упаковка,
| ЗагруженныеВКотёлМатериалы.ВидКомпозиции КАК ВидКомпозиции,
| ЗагруженныеВКотёлМатериалы.ВесНетто КАК ВесНетто,
| ЗагруженныеВКотёлМатериалы.Количество КАК Количество
|ПОМЕСТИТЬ ВТ_ЗагруженныеВКотёлМатериалы
|ИЗ
| РегистрСведений.ЗагруженныеВКотёлМатериалы КАК ЗагруженныеВКотёлМатериалы
|ГДЕ
| ЗагруженныеВКотёлМатериалы.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
| И НЕ ЗагруженныеВКотёлМатериалы.Распределена
| И ЗагруженныеВКотёлМатериалы.ПроизводственнаяПлощадка = &ПроизводственнаяПлощадка
|;
Здесь мы выбираем необходимые поля из регистра сведений ЗагруженныеВКотёлМатериалы, применяем фильтры по периоду, признаку распределения и производственной площадке, а затем помещаем результат во временную таблицу ВТ_ЗагруженныеВКотёлМатериалы. Именно эта временная таблица и будет использоваться в последующем запросе для пивотинга.
Итак, давайте соберем весь запрос целиком:
////////////////////////////////////////////////////////////////////////////////
// 1. Формирование исходной временной таблицы с данными
ВЫБРАТЬ
ЗагруженныеВКотёлМатериалы.Номенклатура КАК Номенклатура,
ЗагруженныеВКотёлМатериалы.Характеристика КАК Характеристика,
ЗагруженныеВКотёлМатериалы.Упаковка КАК Упаковка,
ЗагруженныеВКотёлМатериалы.ВидКомпозиции КАК ВидКомпозиции,
ЗагруженныеВКотёлМатериалы.ВесНетто КАК ВесНетто,
ЗагруженныеВКотёлМатериалы.Количество КАК Количество
ПОМЕСТИТЬ ВТ_ЗагруженныеВКотёлМатериалы
ИЗ
РегистрСведений.ЗагруженныеВКотёлМатериалы КАК ЗагруженныеВКотёлМатериалы
ГДЕ
ЗагруженныеВКотёлМатериалы.Период МЕЖДУ &НачалоПериода И &ОкончаниеПериода
И НЕ ЗагруженныеВКотёлМатериалы.Распределена
И ЗагруженныеВКотёлМатериалы.ПроизводственнаяПлощадка = &ПроизводственнаяПлощадка
;
////////////////////////////////////////////////////////////////////////////////
// 2. Преобразование строк в столбцы (пивотинг)
ВЫБРАТЬ
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПВД ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоПВД,
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииПНД ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоПНД,
СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ПеречислениеВидыПолиэтиленаКомпозицииЛИН ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК ВсегоЛИН
ИЗ
ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы
Этот двухпакетный запрос сначала формирует необходимую базу данных во временной таблице, а затем эффективно преобразует ее в желаемый формат с агрегированными суммами по типам композиций. Таким образом, мы получаем одну строку с итоговыми весами для каждого типа.
Рассмотренный нами метод СУММА(ВЫБОР КОГДА...) отлично подходит, когда количество категорий (видов композиции) известно заранее и статично. Однако, что если список видов композиции может меняться? Например, в будущем могут появиться новые типы, и мы не хотим каждый раз переписывать запрос. В таких случаях мы обращаемся к концепции динамического пивотинга.
В языке запросов 1С, в отличие от некоторых других СУБД (например, SQL Server с его оператором PIVOT), нет встроенного синтаксиса для динамического пивотинга. Вместо этого, динамический запрос формируется программно на встроенном языке 1С. Разберем основные шаги:
ВидКомпозиции из нашей временной таблицы ВТ_ЗагруженныеВКотёлМатериалы.
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции КАК ВидКомпозиции
ИЗ
ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы
Мы получаем этот список программно, например, в объект Запрос, а затем выгружаем результат в ТаблицаЗначений или Массив.
СУММА(ВЫБОР КОГДА ... ТОГДА ... ИНАЧЕ 0 КОНЕЦ).
// Пример фрагмента кода на встроенном языке 1С
СтрокаВыбора = Новый Строка;
Для Каждого СтрокаВида Из РезультатЗапросаСВидами Цикл
Если Не СтрокаВыбора.Пустая() Тогда
СтрокаВыбора = СтрокаВыбора + ",
";
КонецЕсли;
СтрокаВыбора = СтрокаВыбора + " СУММА(ВЫБОР КОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВидКомпозиции = &ВидКомпозиции_" + Строка(СтрокаВида.ВидКомпозиции.Код) + " ТОГДА ВТ_ЗагруженныеВКотёлМатериалы.ВесНетто ИНАЧЕ 0 КОНЕЦ) КАК Всего" + Строка(СтрокаВида.ВидКомпозиции);
КонецЦикла;
ТекстЗапроса = "ВЫБРАТЬ
" + СтрокаВыбора + "
ИЗ
ВТ_ЗагруженныеВКотёлМатериалы КАК ВТ_ЗагруженныеВКотёлМатериалы";
Запрос.Текст = ТекстЗапроса;
// Добавление параметров
Для Каждого СтрокаВида Из РезультатЗапросаСВидами Цикл
Запрос.УстановитьПараметр("ВидКомпозиции_" + Строка(СтрокаВида.ВидКомпозиции.Код), СтрокаВида.ВидКомпозиции);
КонецЦикла;
Обратите внимание, что для каждого вида композиции мы формируем уникальный параметр запроса (например, &ВидКомпозиции_ПВД, &ВидКомпозиции_ПНД), а затем программно устанавливаем эти параметры перед выполнением запроса.
Запрос и выполняем.Преимущества динамического пивотинга:
Недостатки динамического пивотинга:
Мы рекомендуем использовать динамический пивотинг только тогда, когда статический подход с заранее известным набором колонок неприменим из-за изменчивости данных.
ГДЕ применяются до агрегации и фильтруют строки. Условия, которые должны применяться к агрегируемым значениям, но не к фильтрации строк целиком, следует помещать *внутрь* агрегатной функции, как в ВЫБОР КОГДА.ИНАЧЕ 0: Как мы уже обсуждали, явное указание ИНАЧЕ 0 в операторе ВЫБОР КОГДА при агрегации с СУММА является хорошей практикой. Это гарантирует, что строки, не соответствующие условию, корректно учитываются как ноль, а не NULL, который может быть проигнорирован агрегатной функцией или потребовать дополнительной обработки через ЕСТЬNULL.ПОМЕСТИТЬ) позволяет разбить сложные запросы на более простые и понятные части, а также повысить производительность, если одни и те же промежуточные данные используются в нескольких местах.&НачалоПериода, &ПроизводственнаяПлощадка) вместо жесткого кодирования значений. Это повышает безопасность, производительность и предотвращает SQL-инъекции.Итак, мы разобрали, как эффективно и корректно преобразовать данные из множества строк в одну строку с агрегированными суммами по колонкам в запросе 1С. Используя метод СУММА(ВЫБОР КОГДА...ИНАЧЕ 0 КОНЕЦ), мы достигаем поставленной цели, создавая производительный, читаемый и легко поддерживаемый запрос.