Приветствуем вас! В процессе разработки отчетов в Системе компоновки данных (СКД) на платформе 1С:Предприятие часто возникают нетривиальные задачи. Одна из них — необходимость «размножить» или «расщепить» строки исходной таблицы по числовому показателю. Представим классический пример: в нашей базе данных есть запись о продаже товара в количестве трех штук. Нам требуется вывести этот товар в отчете не одной строкой с числом 3, а тремя отдельными строками, в каждой из которых количество будет равно единице. Это может понадобиться для последующей печати уникальных штрихкодов, генерации серийных номеров или детального анализа каждой единицы номенклатуры.
Основная сложность заключается в том, что максимальное количество в исходной выборке заранее неизвестно, а решать задачу хочется исключительно средствами СУБД, внутри схемы СКД, без привлечения кода на встроенном языке и передачи внешних таблиц значений. В этой статье мы подробно разберем, как элегантно решить эту проблему с помощью различных подходов в языке запросов 1С.
Давайте проанализируем базовый математический принцип. СУБД отлично умеет соединять таблицы. Если у нас будет вспомогательная таблица, содержащая последовательный ряд чисел от 1 до максимально возможного количества, мы сможем связать ее с нашими исходными данными. Для этого мы применим внутреннее соединение по условию «меньше или равно». В результате каждая исходная строка повторится ровно столько раз, сколько единиц содержится в ее исходном количестве.
Но как получить последовательность чисел «из воздуха», не создавая реальных таблиц в базе данных? Для этого мы воспользуемся генерацией таблицы цифр от 0 до 9 и последующим их перемножением. Рассмотрим этот процесс по шагам.
Сначала мы сформируем временную таблицу, в которую поместим базовый набор цифр первого разряда. Назовем эту таблицу ВТ_Цифры. Для этого мы напишем следующий пакет запроса:
ВЫБРАТЬ 0 КАК Цифра ПОМЕСТИТЬ ВТ_Цифры
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 2
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 3
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 4
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 5
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 6
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 7
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 8
ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 9
;
Теперь, когда у нас есть базовый набор цифр, мы можем сгенерировать последовательность чисел любой длины. Например, если нам нужна последовательность до 1000, мы соединим эту таблицу саму с собой трижды (для разрядов единиц, десятков и сотен). Связывать таблицы мы будем по условию ПО ИСТИНА, что заставит СУБД выполнить декартово произведение (каждая строка соединится с каждой).
Давайте посмотрим на то, как рассчитывается итоговое число в запросе:
ВЫБРАТЬ
Единицы.Цифра + Десятки.Цифра * 10 + Сотни.Цифра * 100 + 1 КАК Номер
ПОМЕСТИТЬ ВТ_Последовательность
ИЗ
ВТ_Цифры КАК Единицы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Цифры КАК Десятки ПО ИСТИНА
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Цифры КАК Сотни ПО ИСТИНА
;
Благодаря этому несложному математическому выражению мы получаем в таблице ВТ_Последовательность непрерывный ряд чисел от 1 до 1000. Если в вашей практике могут встретиться количества до 10 000, достаточно будет добавить еще одно соединение с разрядом тысяч (Тысячи.Цифра * 1000). СУБД выполняет такие операции молниеносно в оперативной памяти.
Теперь нам остается лишь связать наши исходные данные с полученной последовательностью. Допустим, наши исходные данные находятся в таблице ИсходныеДанные. Напишем финальное соединение:
ВЫБРАТЬ
ИсходныеДанные.Ссылка КАК Ссылка,
1 КАК Количество
ИЗ
ИсходныеДанные КАК ИсходныеДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Последовательность КАК ВТ_Последовательность
ПО ИсходныеДанные.Количество >= ВТ_Последовательность.Номер
Обратите внимание на условие связи: ИсходныеДанные.Количество >= ВТ_Последовательность.Номер. Если у нас была строка с количеством 3, то СУБД найдет в таблице последовательностей номера 1, 2 и 3. Все они удовлетворяют условию. Строка продублируется трижды, а в поле Количество мы жестко пропишем единицу.
Выясним, есть ли альтернативный способ получить последовательность чисел, если мы не хотим писать громоздкие объединения с цифрами. Да, такой способ существует, и он задействует встроенную функцию платформы АВТОНОМЕРЗАПИСИ().
Суть данного метода заключается в том, что в любой реальной базе данных всегда существуют таблицы с гарантированно большим объемом записей. Например, это может быть справочник Номенклатура, ФизическиеЛица или системный регистр сведений. Мы можем использовать любую из этих таблиц в качестве «донора» для генерации индексов.
Рассмотрим пример реализации этого подхода по шагам:
ВЫБРАТЬ ПЕРВЫЕ 10000
АВТОНОМЕРЗАПИСИ() КАК Номер
ПОМЕСТИТЬ ВТ_Номера
ИЗ
Справочник.Номенклатура КАК Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ИсходныеДанные.Ссылка КАК Ссылка,
1 КАК Количество
ИЗ
ИсходныеДанные КАК ИсходныеДанные
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Номера КАК ВТ_Номера
ПО ИсходныеДанные.Количество >= ВТ_Номера.Номер
Важный нюанс: функция АВТОНОМЕРЗАПИСИ() работает корректно только при помещении результатов во временную таблицу. Преимущество данного метода заключается в лаконичности кода. Однако помните о риске: если в вашей информационной базе в таблице-доноре (например, в справочнике номенклатуры) окажется записей меньше, чем максимальное число в поле количества, то вы не сможете размножить строки на нужную величину. Поэтому выбирайте максимально заполненные таблицы.
После того как мы размножили строки на уровне запроса, нам необходимо правильно настроить вывод информации в самой схеме компоновки данных. Если мы оставим настройки ресурсов по умолчанию, СКД может попытаться некорректно просуммировать наши данные.
Проанализируем поведение системы. Мы специально указали в результирующем запросе жесткое значение 1 КАК Количество для каждой разделенной строки. На уровне детальных записей отчет выведет красивый список единичных строк. А как поведет себя группировка по ссылке?
Поскольку в СКД для поля Количество настроено выражение ресурса СУММА(Количество), система сложит все единички для каждой ссылки. В итоге на уровне группировок мы получим исходное суммарное количество (например, 3), а при раскрытии группировки до детальных записей увидим три строки по 1. Это именно тот результат, которого мы добивались!
Давайте настроим это в СКД. Для этого выполним следующие действия:
Количество в список ресурсов.СУММА(Количество).Ссылка, а внутрь нее поместим детальные записи.Почему мы настоятельно рекомендуем использовать именно эти методы внутри СКД вместо постобработки данных кодом на сервере приложений? Рассмотрим основные причины:
Таким образом, используя декартово произведение или функцию автонумерации строк, мы получаем производительное, масштабируемое и чистое решение сложной задачи внутри СКД.