Разбиение / "суммирование" строк в запросе

Рассматривается работа со строками в запросе на примере реальных задач из практики: разбиение неких строк на нужные подстроки, "суммирование" строковых данных из различных строк выборки запроса.

Категория: Публикации 1С

Подробнее

Описание
Подробнее

Описание

Вместо предисловия: статья о запросах не может обойтись без ссылки на ildarovich’a, в частности — на эту публикацию.

Не прошло и недели, как в теме Можно ли в запросе для поля убрать пробелы? было дано теоретическое решение, как жизнь подкинула реальную пару тесно связанных задач.

Задача 1. В справочнике Номенклатура помимо артикула «2630035503» хранится строка замен вида «2630035501 2630035502 …» (нет, я не несу ответственности за выбранный способ хранения этой информации!).

При формировании прайса хотелось бы видеть предложения по артикулам замены отдельными строками, не «2630035503 фильтр 150 руб. 10 шт», а

2630035501 фильтр 150 руб. 10 шт

2630035502 фильтр 150 руб. 10 шт

2630035503 фильтр 150 руб. 10 шт

Задача 2. Внезапно оказалось, что есть и обратная ситуация. В регистре сведений хранятся замены номенклатуры, а при формировании прайса их нужно уже слеплять в одну строку. В регистре

Номенклатура — Замена

26300035501 — 2630035502

26300035501 — 2630035503

а в прайсе должно быть

2630035501, 2630035502, 2630035503 фильтр 150 руб. 10 шт.

Тут необходимо сделать оговорку, что, хотя постановка задачи изобилует артикулами, в примерах решения я буду использовать наименования и/или характеристики — просто на момент написания статьи под рукой только демка УПП оказалась.

Для решения первой задачи был использован адаптированный механизм из темы про пробелы.

— Подготовка служебной таблицы чисел от 0 до 255.

ВЫБРАТЬ 0 КАК зн ПОМЕСТИТЬ т01 ОБЪЕДИНИТЬ ВСЕ ВЫБРАТЬ 1;
ВЫБРАТЬ т1.зн * 2 + т0.зн КАК зн ПОМЕСТИТЬ т02 ИЗ т01 КАК т0, т01 КАК т1;
ВЫБРАТЬ т1.зн * 4 + т0.зн КАК зн ПОМЕСТИТЬ т03 ИЗ т02 КАК т0, т02 КАК т1;
ВЫБРАТЬ т1.зн * 16 + т0.зн КАК зн ПОМЕСТИТЬ т04 ИЗ т03 КАК т0, т03 КАК т1;

— Выборка исходных данных, я решил поразделять наименования номенклатуры. Попутно вычисляются позиции разделителей.

ВЫБРАТЬ
	Номенклатура.Ссылка КАК Ссылка,
	Номенклатура.Наименование,
	т04.зн КАК Позиция
ПОМЕСТИТЬ тНоменИсходная
ИЗ
	Справочник.Номенклатура КАК Номенклатура,
	т04 КАК т04
ГДЕ ПОДСТРОКА(Номенклатура.Наименование, т04.зн, 1) + "#" = " #"

ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Номенклатура.Ссылка, Номенклатура.Наименование, 0 ИЗ Справочник.Номенклатура КАК Номенклатура;

— Позиции разделителей преобразуем в позции начала и окончания содержательных частей наименований:

ВЫБРАТЬ
	т1.Ссылка КАК Ссылка,
	т1.Наименование,
	т1.Позиция КАК Позиция,
	МИНИМУМ(т2.Позиция) КАК Позиция1
ПОМЕСТИТЬ тНоменГотовая
ИЗ
	тНоменИсходная КАК т1 ЛЕВОЕ СОЕДИНЕНИЕ тНоменИсходная КАК т2 ПО т1.Ссылка = т2.Ссылка И т1.Позиция < т2.Позиция
СГРУППИРОВАТЬ ПО
	т1.Ссылка,
	т1.Наименование,
	т1.Позиция
ИМЕЮЩИЕ (т1.Позиция + 1 <> МИНИМУМ(т2.Позиция) ИЛИ МИНИМУМ(т2.Позиция) ЕСТЬ NULL );

— И наконец получаем нужные подстроки.

ВЫБРАТЬ
	тНоменГотовая.Ссылка КАК Ссылка,
	ВЫБОР
		КОГДА тНоменГотовая.Позиция1 ЕСТЬ NULL 
			ТОГДА ПОДСТРОКА(тНоменГотовая.Наименование, тНоменГотовая.Позиция + 1, 1000)
		ИНАЧЕ ПОДСТРОКА(тНоменГотовая.Наименование, тНоменГотовая.Позиция + 1, тНоменГотовая.Позиция1 - тНоменГотовая.Позиция - 1)
	КОНЕЦ КАК Часть,
	тНоменГотовая.Позиция КАК Позиция
ИЗ
	тНоменГотовая КАК тНоменГотовая

УПОРЯДОЧИТЬ ПО
	Ссылка,
	Позиция

В результате получаем что-то наподобие

Что с этим делать дальше?

В моей задаче — про строки замен — я получал их разбиение на конкретные артикулы, лепил остатки по полю ссылки и получал требуемый вид прайса.

Также можно обратно слепить полученные части (см. дальше), и в результате поиметь наименования с удаленными лишними пробелами, как в задаче из вступления, но без оговорок типа замены символов «пробел» на неразрывные пробелы:

Решение второй задачи — склейка строк — была уже рассмотрена в публикации ildarovich’a, но есть и альтернативный вариант:

— Нумеруем строки

ВЫБРАТЬ
	т1.Владелец КАК Номенклатура,
	т1.Наименование КАК Характеристика,
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ т2.Ссылка) КАК Номер
ПОМЕСТИТЬ т0
ИЗ
	Справочник.ХарактеристикиНоменклатуры КАК т1 ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ХарактеристикиНоменклатуры КАК т2 ПО т1.Владелец = т2.Владелец И т1.Ссылка >= т2.Ссылка
СГРУППИРОВАТЬ ПО
	т1.Владелец, т1.Наименование;

— И складываем, подставляя при необходимости требуемый разделитель:

ВЫБРАТЬ
	т0.Номенклатура,
	ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
	ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ПОМЕСТИТЬ т1
ИЗ
	т0 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т0 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)));

— Предыдущий шаг повторяем нужное кличество раз, мне для примера хватило еще двух:

ВЫБРАТЬ
	т0.Номенклатура, ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
	ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ПОМЕСТИТЬ т2
ИЗ т1 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т1 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)));

ВЫБРАТЬ
	т0.Номенклатура,
	ВЫБОР КОГДА т1.Номенклатура ЕСТЬ NULL ТОГДА т0.Характеристика ИНАЧЕ т0.Характеристика + "; " + т1.Характеристика КОНЕЦ КАК Характеристика,
	ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0)) КАК Номер
ИЗ т2 КАК т0 ЛЕВОЕ СОЕДИНЕНИЕ т2 КАК т1 ПО т0.Номенклатура = т1.Номенклатура И (т0.Номер + 1 = т1.Номер)
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)))

Понятно, что такой запрос писать вручную не имеет смысла, построить его автоматически элементарно. Нужное число повторений определяется эмпирически, десяти хватит на объединение до 1014 подстрок. В результате

превращается в

Для моей задачи с артикулами все выглядело точно также, только вместо справочника регистр сведений использовался.

Для чего еще это можно использовать? Например, построение строки со свойствами какого либо объекта. Задача вывести список документов с указанием каких-либо примечаний, записанных в дополнительные свойства не так уж редка. Фантазируйте.

P.S. Данные алгоритмы применимы не только для 8х — первая задача была также успешно переписана на запросы 1sqlite для 7.7.

Каталог решений - Разбиение / "суммирование" строк в запросе