При разработке отчетов и обработок в платформе 1С периодически возникает задача разворота (транспонирования) данных из вертикального списка строк в горизонтальные колонки — аналог конструкции PIVOT в классическом SQL. Типичный пример: в регистре сведений хранится список картинок или файлов, привязаных к номенклатуре. В результате требуется сформировать таблицу, где на каждую номенклатуру приходится ровно одна строка, а имеющиеся картинки располагаются по колонкам: Картинка1, Картинка2, Картинка3, Картинка4.
В языке запросов 1С отсутствует встроенный оператор PIVOT, однако эту задачу можно решить несколькими техническими приемами. Рассмотрим подробнее основные варианты реализации и проанализируем их особенности.
Рассмотрим вариант разворота строк, когда в запросе применяется последовательное внутреннее или левое соединение временной таблицы с самой собой по строгому неравенству значений (Таб.Картинка < Таб1.Картинка). Это позволяет отсечь повторяющиеся сочетания и сгруппировать данные по номенклатуре с применением функции МАКСИМУМ().
Посмотрим на пример такого запроса:
ВЫБРАТЬ
Регистр.Номенклатура КАК Номенклатура,
Регистр.Картинка КАК Картинка
ПОМЕСТИТЬ Таб
ИЗ
РегистрСведений.КартинкиНоменклатуры КАК Регистр
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Таб.Номенклатура КАК Номенклатура,
МАКСИМУМ(Таб.Картинка) КАК Картинка1,
МАКСИМУМ(Таб1.Картинка) КАК Картинка2,
МАКСИМУМ(Таб2.Картинка) КАК Картинка3,
МАКСИМУМ(Таб3.Картинка) КАК Картинка4
ИЗ
Таб КАК Таб
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таб КАК Таб1
ПО Таб.Номенклатура = Таб1.Номенклатура
И Таб.Картинка < Таб1.Картинка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таб КАК Таб2
ПО Таб1.Номенклатура = Таб2.Номенклатура
И Таб1.Картинка < Таб2.Картинка
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Таб КАК Таб3
ПО Таб2.Номенклатура = Таб3.Номенклатура
И Таб2.Картинка < Таб3.Картинка
СГРУППИРОВАТЬ ПО
Таб.Номенклатура
Разберем особенности этого подхода:
NULL.Более универсальный способ разворота заключается в двухэтапном формировании результата: сначала каждой картинке внутри конкретной номенклатуры присваивается порядковый номер (1, 2, 3, 4...), после чего применяется группировка с условной конструкцией ВЫБОР КОГДА ... ТОГДА ... КОНЕЦ.
Для нумерации строк внутри группы по номенклатуре используется связывание таблицы с самой собой по условию Таб1.Картинка >= Таб2.Картинка и подсчет количества уникальных элементов.
Проанализируем структуру такого пакета запросов:
ВЫБРАТЬ
Регистр.Номенклатура КАК Номенклатура,
Регистр.Картинка КАК Картинка
ПОМЕСТИТЬ ИсходныеДанные
ИЗ
РегистрСведений.КартинкиНоменклатуры КАК Регистр
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Т1.Номенклатура КАК Номенклатура,
Т1.Картинка КАК Картинка,
КОЛИЧЕСТВО(РАЗЛИЧНЫЕ Т2.Картинка) КАК НомерКартинки
ПОМЕСТИТЬ ПронумерованныеДанные
ИЗ
ИсходныеДанные КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ИсходныеДанные КАК Т2
ПО Т1.Номенклатура = Т2.Номенклатура
И Т1.Картинка >= Т2.Картинка
СГРУППИРОВАТЬ ПО
Т1.Номенклатура,
Т1.Картинка
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Данные.Номенклатура КАК Номенклатура,
МАКСИМУМ(ВЫБОР КОГДА Данные.НомерКартинки = 1 ТОГДА Данные.Картинка КОНЕЦ) КАК Картинка1,
МАКСИМУМ(ВЫБОР КОГДА Данные.НомерКартинки = 2 ТОГДА Данные.Картинка КОНЕЦ) КАК Картинка2,
МАКСИМУМ(ВЫБОР КОГДА Данные.НомерКартинки = 3 ТОГДА Данные.Картинка КОНЕЦ) КАК Картинка3,
МАКСИМУМ(ВЫБОР КОГДА Данные.НомерКартинки = 4 ТОГДА Данные.Картинка КОНЕЦ) КАК Картинка4
ИЗ
ПронумерованныеДанные КАК Данные
ГДЕ
Данные.НомерКартинки <= 4
СГРУППИРОВАТЬ ПО
Данные.Номенклатура
Этот вариант гарантирует получение ровно одной строки для каждой номенклатуры независимо от того, сколько у нее зарегистрировано картинок — 0, 1 или 10. Если картинок меньше 4, незаполненные поля содержат NULL.
Выясним причину, почему решения с транспонированием внутри запроса не всегда подходят для реальных задач с большими объемами данных. Построение групповой нумерации и нестрогих самосоединений (>= или <) создает на стороне СУБД вычислительную сложность порядка $O(N^2)$. Если в базе данных тысячи товаров и у каждого по несколько картинок, такой запрос может выполняться очень долго.
Самым эффективным решением с точки зрения производительности является сочетание простого плоского запроса и обхода выборки в коде 1С.
Рассмотрим алгоритм постобработки по шагам:
ТаблицаЗначений с колонками Номенклатура, Картинка1, Картинка2, Картинка3, Картинка4.Выборка.Следующий() и заполняем новую строку таблицы при смене номенклатуры.Пример программной постобработки:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| Регистр.Номенклатура КАК Номенклатура,
| Регистр.Картинка КАК Картинка
|ИЗ
| РегистрСведений.КартинкиНоменклатуры КАК Регистр
|УПОРЯДОЧИТЬ ПО
| Номенклатура";
Выборка = Запрос.Выполнить().Выбрать();
ТЗ = Новый ТаблицаЗначений;
ТЗ.Колонки.Добавить("Номенклатура");
ТЗ.Колонки.Добавить("Картинка1");
ТЗ.Колонки.Добавить("Картинка2");
ТЗ.Колонки.Добавить("Картинка3");
ТЗ.Колонки.Добавить("Картинка4");
ТекущаяНоменклатура = Неопределено;
НоваяСтрока = Неопределено;
Счетчик = 0;
Пока Выборка.Следующий() Цикл
Если Выборка.Номенклатура <> ТекущаяНоменклатура Тогда
ТекущаяНоменклатура = Выборка.Номенклатура;
НоваяСтрока = ТЗ.Добавить();
НоваяСтрока.Номенклатура = ТекущаяНоменклатура;
Счетчик = 1;
Иначе
Счетчик = Счетчик + 1;
КонецЕсли;
Если Счетчик <= 4 Тогда
НоваяСтрока["Картинка" + Счетчик] = Выборка.Картинка;
КонецЕсли;
КонецЦикла;
Если конечной целью является вывод горизонтально развернутой структуры в отчет или печатную форму, писать сложные запросы или код постобработки не требуется. В Системе компоновки данных (СКД) транспонирование выполняется автоматически через элемент настройки Таблица. Достаточно в схеме компоновки данных поместить Номенклатуру в строки таблицы, а вычисляемый порядковый номер картинки или сама группировка по картинкам выносится в колонки таблицы.
Для выбора оптимального способа транспонирования руководствуйтесь следующими правилами:
МАКСИМУМ(ВЫБОР...).