IE 2017

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

Программирование - Практика программирования

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

Вместо предисловия: статья о запросах не может обойтись без ссылки на 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.

См. также

Комментарии
1. Алексей Т. (CratosX) 99 14.10.15 12:19 Сейчас в теме
А что по производительности по сравнению с постобработкой? Стоит ли оно того в свете поддержки/доработки?
2. A X (ditp) 49 14.10.15 12:53 Сейчас в теме
(1) CratosX, а что имеется ввиду под "постобработкой"?

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

Допускаю, что в каких-либо других задачах, на каких-то более специфичных данных, предложенный алгоритм будет уступать процедурной обработке. Все ж зависит...
3. Сергей (ildarovich) 5199 19.10.15 15:44 Сейчас в теме
Поставил плюс за развитие темы.

По задаче 2 есть три замечания.

1) Сначала мелкое:
Условие проверки нечетности
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 1)))

лучше записать как
ГДЕ (ВЫРАЗИТЬ(т0.Номер / 2 КАК ЧИСЛО(10, 0))) <> т0.Номер / 2


2) Замена группировки на соединение не было сделано вполне сознательно, учитывая, что вот это условие
(т0.Номер + 1 = т1.Номер)
в соединении почти наверняка приведет к сканированию второй таблицы, то есть для 1000 соединяемых строчек потребуется 500х1000 проверок. Тогда как при группировке операций будет не больше 1000. Чтобы работал ХэшМатч в этом соединении сравнение должно быть на равенство полей. Этого можно добиться, вычисляя номер пары для соединения заранее (в предшествующем запросе), поэтому при большом желании соединение все же можно использовать, но запись получается длиннее.

3) Упрощение, достигнутое отказом от предварительного разбиения строк на буквы, имеет границы применимости (об этом в исходной статье написано). Это как ответ на вопрос: сколько сигарет поместится в объеме блока сигарет: всего двадцать, если в каждой пачке будет лежать по одной. Здесь также: если строка будет объявлена длиной 50, а равна "а", то в итоге можно будет получить только строку "аааааааааааааааа" (длиной 16), выполнив вполовину меньшее максимального число соединений.
4. A X (ditp) 49 23.10.15 10:20 Сейчас в теме
(3) ildarovich, спасибо за комментарий.

По пунктам:

1) согласен, в какой-то момент при написании включилось "безобразно, зато единообразно".

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


3) возможно, мне не хватает понимания глубинных механизмов работы запросов в 1С, но каким образом длина строки повлияет на количество соединений, мне не ясно.
5. Сергей (ildarovich) 5199 23.10.15 11:21 Сейчас в теме
(4) ditp,

2) можно и так

3) Я провел эксперимент и выяснил, что при выполнении запроса
ВЫБРАТЬ Строка1 + Строка2 КАК Поле1
возникает ошибка, если длина строк в сумме превышает 1024 (или 2048 в некоторых случаях).
Аналогично
ВЫРАЗИТЬ Строка1 + Строка2 + Строка3 + ... + Строка32 КАК Поле1
тоже будет давать ошибку, если длина исходных строк будет, например, равна 40. Потому, что тогда итоговая строка окажется длиной 1280 и не поместится в длину 1024, которая является ограничением при манипуляции со строками в запросе. Например, нельзя написать ВЫРАЗИТЬ(СтрокаХ КАК Строка(1280)). Я сделал вывод, что при выполнении операции Строка1 + Строка2 длина итогового поля не переменная, а фиксированная и равна сумме фиксированных длин строк-аргументов. Это логично для операций с таблицами.
Используя пять парных соединений, мы фактически реализуем то же самое выражение
ВЫРАЗИТЬ Строка1 + Строка2 + Строка3 + ... + Строка32 КАК Поле1
. Значит, длина исходных строк не может быть больше 32. Или должно быть меньше соединений.
Максимальное количество соединений можно сделать тогда, когда исходные строки имеют длину 1. Тогда можно сделать десять соединений и получить в итоге строку длиной 1024, заполненную "до отказа". Поэтому и нужно предварительно разбивать строки на отдельные буквы.
Если написал непонятно, просто попробуйте задать длину наименования справочника 500, записать четыре элемента с наименованием "Первый", "Второй", "Третий", "Четвертый" и двумя парными соединениями получить конкатенацию вида "ПервыйВторойТретийЧетвертый".
6. A X (ditp) 49 23.10.15 11:46 Сейчас в теме
(5) ildarovich, готово!

и
ВЫБРАТЬ
	"" КАК Номенклатура,
	т1.Реквизит1 КАК Характеристика,
	КОЛИЧЕСТВО(РАЗЛИЧНЫЕ т2.Ссылка) КАК Номер
ПОМЕСТИТЬ т0
ИЗ
	Справочник.Справочник1 КАК т1
		ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Справочник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)));
...Показать Скрыть

в результате:
7. A X (ditp) 49 23.10.15 11:52 Сейчас в теме
Также без проблем отработало
выбрать 
выразить("1" как строка(1000))+
выразить("2" как строка(1000))+
выразить("3" как строка(1000))+
выразить("4" как строка(1000))+
выразить("5" как строка(1000))+
выразить("6" как строка(1000))+
выразить("7" как строка(1000))+
выразить("8" как строка(1000))+
выразить("9" как строка(1000))+
выразить("0" как строка(1000)) рез
...Показать Скрыть


P.S. Я допускаю возможность получить ошибку или некорректный результат, если в результате сложения получим "непустую" строку длиной свыше ХХХХ символов.
При разбиении сначала на единичные символы и фиксированном числе повторений, чтобы результат не был длинее этого ХХХХ, мы гарантировано ошибки избежим, тут я согласен.

Скриншоты из предыдущего сообщения в нормальном размере: http://imgur.com/a/kv28Z
8. Сергей (ildarovich) 5199 23.10.15 13:05 Сейчас в теме
(6) ditp, да, сделали все в точности, но ... не могли бы (все уже настроено) проверить вариант с длиной 600. Дело в том, что 500х4 = 2000 < 2048, а 600х4 = 2400 > 2048. Максимальная 2048 - это не документированная фитча для файловой (?) версии.
9. A X (ditp) 49 23.10.15 14:22 Сейчас в теме
(8) ildarovich, при длине в 600 интересная вещь получается: просто скопировать текст запроса как в посте выше не удается.
Вызов конструктора выдает ошибку, в обработке написать что типа
построитель 	= Новый ПостроительОтчета;
построитель.Текст= "ВЫБРАТЬ...";

то ошибка возникает при попытке открыть обработку.
Видимо, 1С на этапе анализа запроса проверяет, какой длины строки получатся. Сужу по тому, что при замене
т0.Характеристика + ""; "" + т1.Характеристика
на
выразить(т0.Характеристика как строка(1000))+ ""; "" + выразить(т1.Характеристика как строка(1000))
все нормально.
Так что, видимо, можно остановиться таки на том, что
1) если итоговая строка может быть больше 2000 символов - требуется разбиение на символы и ограничение повторений в запросе, дальше только процедурно.
2) если длина строки 2000 не превысит - можно мой механизм использовать.
10. Сергей (ildarovich) 5199 23.10.15 17:24 Сейчас в теме
(9) ditp, я бы написал: ... если суммарная максимальная(объявленная) длина соединяемых строк может быть больше 1024 символов, то ...
(1024 - чтобы учесть MS SQL и Postgre). Но занудствовать не люблю и спорить больше не буду.
Оставьте свое сообщение