В практике разработки на платформе 1С часто возникает задача трансформации данных из «вертикального» вида в «горизонтальный». Например, когда для одной и той же аналитики (точки, контрагента, номенклатуры) есть несколько записей типа «А» и несколько записей типа «Б», и нам нужно вывести их в одну строку: «Колонка А — Колонка Б». Основная сложность заключается в том, что обычное соединение по ключевому полю при разном количестве записей разных типов приводит к «эффекту декартова произведения» — данные начинают дублироваться, и итоговая таблица становится некорректной.
В этой статье мы разберем, как решить эту задачу исключительно средствами языка запросов 1С, рассмотрим классический метод имитации оконных функций и упомянем современные возможности платформы.
Представим ситуацию: у нас есть «точка-1», для которой в базе хранятся две записи типа «А» (с количеством 20 и 40) и одна запись типа «Б» (с количеством 10). Если мы просто соединим таблицу саму с собой по полю КП (Контрольный Пункт), то для каждой записи типа «А» подтянется запись типа «Б». В итоге мы получим:
В данном случае значение 10 для типа «Б» продублировалось. А если бы типов «Б» тоже было два, мы бы получили четыре строки (2х2). Наша же задача — вывести данные так, чтобы записи сопоставлялись «один к одному» по порядку, а лишние строки оставались пустыми. Чтобы достичь этого, нам нужно соединять таблицы не только по КП, но и по порядковому номеру записи внутри этой группы.
Этот метод основан на создании временных таблиц и вычислении порядкового номера записи (аналог ROW_NUMBER()) через левое соединение таблицы самой с собой. Разберем этот процесс по шагам.
Шаг 1. Подготовка данных. Сначала мы разделяем исходные данные на две временные таблицы: для типа «А» и для типа «Б». Чтобы нумерация была корректной, крайне важно иметь уникальный идентификатор для каждой строки. Если его нет в исходных данных, используем функцию АВТОНОМЕРЗАПИСИ().
// Выбираем данные типа А
ВЫБРАТЬ
Исходник.КП КАК КП,
Исходник.Тип КАК Тип,
Исходник.Количество КАК Количество,
АВТОНОМЕРЗАПИСИ() КАК ИД
ПОМЕСТИТЬ ВТ_ТипА
ИЗ
ВТ_ИсходныеДанные КАК Исходник
ГДЕ
Исходник.Тип = "А";
// Выбираем данные типа Б
ВЫБРАТЬ
Исходник.КП КАК КП,
Исходник.Тип КАК Тип,
Исходник.Количество КАК Количество,
АВТОНОМЕРЗАПИСИ() КАК ИД
ПОМЕСТИТЬ ВТ_ТипБ
ИЗ
ВТ_ИсходныеДанные КАК Исходник
ГДЕ
Исходник.Тип = "Б";
Шаг 2. Расчет порядкового номера (НПП). Теперь для каждой таблицы мы считаем количество записей, у которых ИД меньше или равен текущему в рамках одного КП. Это и будет наш порядковый номер.
ВЫБРАТЬ
Т1.КП,
Т1.Тип,
Т1.Количество,
КОЛИЧЕСТВО(Т2.ИД) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_А_С_Номером
ИЗ
ВТ_ТипА КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТипА КАК Т2 ПО Т1.КП = Т2.КП И Т1.ИД >= Т2.ИД
СГРУППИРОВАТЬ ПО Т1.КП, Т1.Тип, Т1.Количество, Т1.ИД;
ВЫБРАТЬ
Т1.КП,
Т1.Тип,
Т1.Количество,
КОЛИЧЕСТВО(Т2.ИД) КАК НомерСтроки
ПОМЕСТИТЬ ВТ_Б_С_Номером
ИЗ
ВТ_ТипБ КАК Т1
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ТипБ КАК Т2 ПО Т1.КП = Т2.КП И Т1.ИД >= Т2.ИД
СГРУППИРОВАТЬ ПО Т1.КП, Т1.Тип, Т1.Количество, Т1.ИД;
Шаг 3. Финальное соединение. Теперь мы выполняем ПОЛНОЕ СОЕДИНЕНИЕ этих двух таблиц по полям КП и НомерСтроки. Это позволит нам сопоставить первую строку «А» с первой строкой «Б», вторую со второй и так далее.
ВЫБРАТЬ
ЕСТЬNULL(ВТ_А.КП, ВТ_Б.КП) КАК КП,
ВТ_А.Тип КАК ТипА,
ВТ_А.Количество КАК КоличествоА,
ВТ_Б.Тип КАК ТипБ,
ВТ_Б.Количество КАК КоличествоБ
ИЗ
ВТ_А_С_Номером КАК ВТ_А
ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Б_С_Номером КАК ВТ_Б ПО ВТ_А.КП = ВТ_Б.КП И ВТ_А.НомерСтроки = ВТ_Б.НомерСтроки;
Проанализируем ситуацию в современных реалиях. Начиная с версии платформы 8.3.20, в языке запросов 1С появились штатные оконные функции. Это избавляет нас от необходимости делать тяжелые самосоединения таблиц, которые работают медленно на больших объемах данных.
Рассмотрим, как упростится наш запрос с использованием функции ПОРЯДКОВЫЙ_НОМЕР(). Мы можем пронумеровать строки прямо «на лету» в рамках каждой группы КП и Тип.
ВЫБРАТЬ
Данные.КП,
Данные.Тип,
Данные.Количество,
ПОРЯДКОВЫЙ_НОМЕР() ОБРАБОТКА (РАЗБИТЬ ПО Данные.КП, Данные.Тип УПОРЯДОЧИТЬ ПО Данные.Количество) КАК НомерВГруппе
ПОМЕСТИТЬ ВТ_ПронумерованныеДанные
ИЗ
ВТ_ИсходныеДанные КАК Данные;
// Далее разделяем на А и Б и соединяем по НомерВГруппе, как в первом способе.
Этот метод значительно эффективнее, так как платформа выполняет нумерацию за один проход по данным, не создавая временных таблиц для каждой стадии расчета номера.
При использовании любого из этих методов стоит учитывать следующие моменты:
Т1.ИД >= Т2.ИД) имеет квадратичную сложность $O(n^2)$. Если у вас в рамках одной «точки» будет 10 000 записей, запрос может выполняться очень долго или вызвать переполнение памяти. В таких случаях лучше использовать оконные функции или обрабатывать результат программно в ТаблицеЗначений.УПОРЯДОЧИТЬ ПО при формировании временных таблиц, если вам важен конкретный порядок сопоставления записей А и Б (например, по дате или по возрастанию количества).ПОЛНОЕ СОЕДИНЕНИЕ всегда используйте функцию ЕСТЬNULL для ключевых полей, чтобы не потерять аналитику в строках, где данные одного из типов отсутствуют.Таким образом, мы выяснили, что ключ к решению задачи — создание искусственного индекса внутри каждой группы данных. Выбор конкретного инструмента зависит от версии вашей платформы и требований к производительности системы.