0. ditp 50 13.10.15 10:54 Сейчас в теме

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

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

Перейти к публикации

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

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

Допускаю, что в каких-либо других задачах, на каких-то более специфичных данных, предложенный алгоритм будет уступать процедурной обработке. Все ж зависит...
3. ildarovich 6770 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. ditp 50 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 6770 23.10.15 11:21 Сейчас в теме
(4)

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. ditp 50 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)));
Показать

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

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

Вакансии

Программист 1С
Москва
зарплата от 100 000 руб. до 200 000 руб.
Полный день

Тестировщик 1С
Москва
зарплата от 70 000 руб.
Полный день

Программист 1С
Новосибирск
зарплата от 50 000 руб. до 80 000 руб.
Полный день

Программист 1С
Новосибирск
зарплата от 80 000 руб. до 120 000 руб.
Полный день

Программист, аналитик, эксперт 1С
Санкт-Петербург
По совместительству