Агрегатное суммирование строк в запросе – сложно, но не невозможно

16.05.2017 53 Comments

Математика и алгоритмы, Разработка

,

    АгрегатнаяКонкатенация.png
Описывается метод соединения строк из одной колонки таблицы в единую результирующую строку в запросе на языке 1С. Метод сложный и по сравнению с внезапросной техникой представляет больше спортивный, чем практический интерес.

Во многих типовых конфигурациях в документах есть реквизит «Содержание» типа строка, где перечислены фамилии сотрудников, упоминающиеся в строках табличной части, или список номенклатуры, имеющейся в таблице товаров. При заполнении этого реквизита естественно было бы сгруппировать строки по ссылке на документ, определив реквизит «Содержание» с помощью агрегатной функции суммы как СУММА(СсылкаНаОбъектТЧ.Наименование). К сожалению, агрегатная функция СУММА не воспринимает аргумент строкового типа.

Подобных задач довольно много, но если число исходных строк не фиксировано и достаточно велико, все они в общем случае решаются дополнительной обработкой результата запроса. Начиная с релиза 8.2.13(?), в СКД для решения этой задачи даже появилась специальная функция «СоединениеСтрок», что также свидетельствует о популярности задачи «агрегатной конкатенации». Многие сомневаются в возможности решения этой задачи одним пакетным запросом, а зря!

Далее будет приведено доказательство такой возможности. В качестве метода доказательства использован самый простой способ – через демонстрацию построения работающего запроса.

Для начала придется пояснить, чем конкатенация отличается от других агрегатных функций: суммирования, усреднения, выбора максимума и минимума. Дело в том, что все перечисленные функции коммутативны, то есть их результат не зависит от порядка операций. А у конкатенации – зависит. Это означает, что для конкатенации недостаточно одного столбца. Обязательно должен существовать второй (а лучше — первый) столбец, который будет определять порядок подстрок в результирующей строке. Это следует из теории реляционных СУБД, с точки зрения которой таблицы

А    Т   О   Т     Р
 В    О   Т    А    В
 Т    В   В    В    О
 О    А   А    Р    Т
 Р    Р   Р    О    А

это представление одной и той же таблицы. По которой нельзя определить, что должно получиться в результате конкатенации. А по таблице 

 3 Р
 4 А
 5 В
 1 Н
 2 И
 6 Н
7 А

можно.

Если же вдруг порядкового столбца нет, его можно скопировать из столбца строк (это предложил наш коллега andrewks), что будет означать, что перед конкатенацией строки будут отсортированы по алфавиту.    

Для простоты будем считать, что порядок соединения задан числами от 1 до N, где N – число строк. Следовательно, исходная таблица «Таб» имеет вид

ё а
1 Строка1
2 Строка2
   
N СтрокаN

Соединим для начала попарно все соседние нечетные и четные строки. Для этого определим число «е» как округление результата деления номера строки на два и построим таблицу «Шаг»

е ё а
1 1 Строка1
1 2 Строка2
2 3 Строка3
2 4 Строка4
3 5 Строка5
     

После этого сгруппируем строки по полю «е», соединив операцией «+» две строки: левую – нечетную, которая отличается тем, что «2 * е – ё = 1», и правую – четную, для которой «2 * е – ё <> 1». В результате получим таблицу

ё а
1 Строка1Строка2
2 Строка3Строка4
3 Строка5Строка6
   

 

Повторяя этот приём нужное количество раз, получим в первой строке конкатенацию всех строк.

Соответствующий описанному приему запрос имеет вид:

ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб;
УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е;
УНИЧТОЖИТЬ Шаг;

В запросе нарочно сокращены необязательные поля, чтобы его запись было короче, а местами имела смешной вид. Например, такой вид имеют рядом написанные буквы «е» и «ё».

Если в исходной таблице было 2 строки, для завершения алгоритма понадобится один повтор, 4 и менее – два, 8 и менее – три, 16 и менее – четыре,32 и менее — пять, 64 и менее – шесть, 128 и менее – семь, 256 и менее – восемь, … 2048 и менее – одиннадцать. Принцип, думаю, понятен, обойдемся без формулы с логарифмом по основанию два.   

А как определить нужное число повторений заранее? Не зная числа соединяемых строк? И вот тут-то и нужно вспомнить, что хотя число строк ничем не ограничено, но ограничен размер строки, который будет принимать результат наших вычислений. Результирующая строка – это строка ограниченного типа, которая в 1С не может содержать больше 2047 символов! Следовательно, использовать более 11-ти соединений вообще не нужно, так как результат будет просто некуда поместить.

Можно успокоить тех, кого это обстоятельство будет расстраивать: не поместившиеся в первой строке символы с помощью этого алгоритма волшебным образом окажутся записанными во второй строке и так далее.

Кроме того, если ограничение будет когда-либо повышено, то нужно будет всего немного увеличить число повторений.  Даже строка неограниченного типа не может быть длиннее 2#k8SjZc9Dxk31 -1 символов. Что означает, что будет нужно добавить еще всего лишь двадцать повторений. Отметим, что операции группировки для данного случая выполняются достаточно быстро. Притом, что строки идут по порядку.

На практике есть еще два обстоятельства, которые требуется учесть и которые существенно усложняют, а главное, к сожалению,  – замедляют вычисления.

Первое обстоятельство — это способ реализации суммирования двух полей строкового типа при получении нового поля в запросе, о котором мы обычно не задумываемся. Точнее, способ вычисления длины поля строкового типа, содержащего результат. Во-первых, можно складывать только поля строк ограниченного типа (ну или приведенного к нему неограниченного типа с использованием оператора «ВЫРАЗИТЬ»). Во-вторых, результирующее поле будет также строкой ограниченного типа, длина которой будет равна сумме длин соединяемых полей. Независимо от того, сколько символов на самом деле занято в этих полях!

Для нас это означает, что имея на первом шаге строки длины 32, например, мы не сможем выполнить  более шести повторений (64->128->256->512->1024->2048), так как длины строк будут на каждом шаге считаться по-максимуму, а не по реальному содержанию. А шесть повторений – это всего 64 сложенные строки, причем в результирующей 2047-символьной строке может быть реально занято совсем немного места.

Максимальную плотность упаковки символов в результирующей строке можно обеспечить, если перед соединением предварительно разбить строки на отдельные символы. И не только разбить, но и затем правильно последовательно пронумеровать. Это может быть довольно дорогая в  вычислительном отношении операция, поскольку глобальный номер отдельного символа конкретной строки будет зависеть от числа символов в строках впереди нее. То есть потребуется медленное тэта-соединение таблицы строк с самой собой.

В самом сложном случае также нужно предусмотреть разбивку на отдельные символы 2047-символьных строк. Если изначально строки будут короче, время разбивки сократится.

Разбивка строк на символы выполняется с использованием соединения с искусственной таблицей, содержащей числа от 0 до 2047, полученной методом «Порождающего запроса» [//infostart.ru/public/90367/].

Числа от 0 до 2047 записываются в таблицу РА9876543210, название которой составлено из первой буквы слова «Разряд» и номеров содержащейся в ней двоичных разрядов (в шестнадцатеричной системе – цифр не хватило, десятый разряд обозначен буквой «А»). Это делает следующий фрагмент запроса:

ВЫБРАТЬ РАЗЛИЧНЫЕ Дано.Колонка НомерСтроки, Дано.Колонка а ПОМЕСТИТЬ Дано ИЗ &Дано КАК Дано;
ВЫБРАТЬ 0 ё ПОМЕСТИТЬ Р0 ОБЪЕДИНИТЬ ВЫБРАТЬ 1;
ВЫБРАТЬ 2 * Р1.ё + Р0.ё ё ПОМЕСТИТЬ Р10 ИЗ Р0 Р1, Р0;
ВЫБРАТЬ 4 * Р32.ё + Р10.ё ё ПОМЕСТИТЬ Р3210 ИЗ Р10 Р32, Р10;
ВЫБРАТЬ 16 * Р7654.ё + Р3210.ё ё ПОМЕСТИТЬ Р76543210 ИЗ Р3210 Р7654, Р3210;
ВЫБРАТЬ 8 * Р76543210.ё + 2 * Р10.ё + Р0.ё + 1 ё ПОМЕСТИТЬ РА9876543210 ИЗ Р76543210, Р10, Р0 ГДЕ 8 * Р76543210.ё + 2 * Р10.ё + Р0.ё < &ШиринаКолонки

Параметр «ШиринаКолонки» обозначает длину строки в поле «КОЛОНКА» (если 0, то 2047).

Собственно разбивка на символы выполняется в следующем фрагменте запроса. Результат помещается в таблицу «Буквы»

ВЫБРАТЬ НомерСтроки, ё, ПОДСТРОКА(а, ё, 1) а ПОМЕСТИТЬ Буквы ИЗ Дано, РА9876543210 ГДЕ ПОДСТРОКА(а, ё, 1) + "!" <> "!"

После этого получается таблица длин строк и с ее использованием буквы «а» получают глобальные номера «ё» в таблице «Таб».

ВЫБРАТЬ НомерСтроки, МАКСИМУМ(ё) СтрДлина ПОМЕСТИТЬ Длины ИЗ Буквы СГРУППИРОВАТЬ ПО НомерСтроки;
ВЫБРАТЬ ё + СУММА(ЕСТЬNULL(СтрДлина, 0)) ё, а Поместить Таб ИЗ Буквы КАК Буквы ЛЕВОЕ СОЕДИНЕНИЕ Длины ПО Буквы.НомерСтроки > Длины.НомерСтроки
СГРУППИРОВАТЬ ПО Буквы.НомерСтроки, ё, а

Второе усложняющее обстоятельство – это «проблема пробелов». Дело в том, что при работе со строками в запросе к результату как будто бы автоматически применяется функция «СокрП». То есть становится невозможно зафиксировать добавление пробела справа. Операция вроде бы выполняется, а добавленные пробелы сразу же пропадают. В результирующей строке это выглядит как исчезнувшие пробелы. «пока лечили» превращается в «покалечили», «несу разное» в «несуразное» и так далее.

Выход был найден в том, что обычный пробел в строках при их разбивке на символы заменялся на неразрывный пробел – символ с кодом 160 (Символы.НПП). Поскольку внешне эти символы выглядят неотличимо, то запрос принимает крайне любопытный вид. Он получает буквально неочевидные свойства. Честно говоря, такой «невидимый» трюк приходится использовать впервые. Кроме того, при каком-либо копипасте запроса этот особенный пробел может подмениться обычным. Будьте внимательнее – следите за своими пробелами!

С учетом последнего уточнения предпоследний фрагмент принимает следующий вид (в кавычках после «Тогда» — неразрывный пробел!):

ВЫБРАТЬ НомерСтроки, ё, ВЫБОР ПОДСТРОКА(а, ё, 1) КОГДА " " ТОГДА " " ИНАЧЕ ПОДСТРОКА(а, ё, 1) КОНЕЦ а ПОМЕСТИТЬ Буквы ИЗ Дано, РА9876543210
ГДЕ ПОДСТРОКА(а, ё, 1) + "!" <> "!"

Ну а весь собранный в единое целое запрос получает в итоге следующий законченный вид:

ВЫБРАТЬ РАЗЛИЧНЫЕ Дано.Колонка НомерСтроки, Дано.Колонка а ПОМЕСТИТЬ Дано ИЗ &Дано КАК Дано;
ВЫБРАТЬ 0 ё ПОМЕСТИТЬ Р0 ОБЪЕДИНИТЬ ВЫБРАТЬ 1;
ВЫБРАТЬ 2 * Р1.ё + Р0.ё ё ПОМЕСТИТЬ Р10 ИЗ Р0 Р1, Р0;
ВЫБРАТЬ 4 * Р32.ё + Р10.ё ё ПОМЕСТИТЬ Р3210 ИЗ Р10 Р32, Р10;
ВЫБРАТЬ 16 * Р7654.ё + Р3210.ё ё ПОМЕСТИТЬ Р76543210 ИЗ Р3210 Р7654, Р3210;
ВЫБРАТЬ 8 * Р76543210.ё + 2 * Р10.ё + Р0.ё + 1 ё ПОМЕСТИТЬ РА9876543210 ИЗ Р76543210, Р10, Р0 ГДЕ 8 * Р76543210.ё + 2 * Р10.ё + Р0.ё < &ШиринаКолонки;
ВЫБРАТЬ НомерСтроки, ё, ВЫБОР ПОДСТРОКА(а, ё, 1) КОГДА " " ТОГДА "_" ИНАЧЕ ПОДСТРОКА(а, ё, 1) КОНЕЦ а ПОМЕСТИТЬ Буквы ИЗ Дано, РА9876543210 ГДЕ ПОДСТРОКА(а, ё, 1) + "!" <> "!";
ВЫБРАТЬ НомерСтроки, МАКСИМУМ(ё) СтрДлина ПОМЕСТИТЬ Длины ИЗ Буквы СГРУППИРОВАТЬ ПО НомерСтроки;
ВЫБРАТЬ ё + СУММА(ЕСТЬNULL(СтрДлина, 0)) ё, а Поместить Таб ИЗ Буквы КАК Буквы ЛЕВОЕ СОЕДИНЕНИЕ Длины ПО Буквы.НомерСтроки > Длины.НомерСтроки СГРУППИРОВАТЬ ПО Буквы.НомерСтроки, ё, а;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;
ВЫБРАТЬ е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ "" КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА "" ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО е; УНИЧТОЖИТЬ Шаг;
ВЫБРАТЬ а ИЗ Таб

Здесь приведена версия «лайт» запроса. Рабочий запрос отличается от данного тем, что во фрагментах со второго по четвертый добавлена еще одна группировка по полю, по которому собственно и выполняется «агрегатная конкатенация». К статье приложена обработка (отчет), с помощью которой можно испытать данный запрос, замерить время его выполнения. Впрочем, он будет работать и в консоли, если она умеет задавать параметры в виде таблицы значений. Приведен также скриншот формы обработки.

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

Ну, а насколько этот достаточно сложный запрос практичен, судите сами. Его можно использовать не только для генерации реального или виртуального реквизита «Содержание» документов, но и для других задач простой обработки текстовой информации непосредственно в запросах. Например, для замены или удаления символов в наименованиях, артикулах, транслитерации, перевода слов, для форматирования адресов, телефонов и прочее, прочее, прочее. То есть везде, где есть желание использовать несуществующую агрегатную функцию СУММА(ТекстоваяСтрока).

Related Posts

53 Comments

  1. Новиков

    Снимаю шляпу. Одного скептика, в моем лице, вы убедили 🙂

    Reply ↓
  2. Поручик

    (0) Начиная с релиза 8.2.14, в СКД появилась специальная функция «СоединениеСтрок» и прочие нужные функции для ТЗ и массивов, которые выручили меня при построении некоторых упоротых отчетов.

    Но применить Такое в обычном запросе, у меня бы ума не хватило.

    Reply ↓
  3. ildarovich

    Еще несколько соображений, которые потом нужно будет включить в статью:

    1) Соединив фрагменты с первого по начало третьего, можно получить несложный и быстрый запрос для определения длины строк в запросе, реализовав отсутствующую в запросе функцию СтрДлина.

    2) Если длина соединяемых строк равна 25-ти (длина наименования элементов в справочниках по умолчанию), а число соединяемых строк не более 64-х, то можно использовать только концовку запроса (одиннадцать повторений), чтобы ОЧЕНЬ БЫСТРО получить желаемый результат. Зависимость, наверное, понятна: если исходная длина соединяемых строк равна или менее 8, то ограничение на число строк — 2048 / 8 = 256; если равна или менее 16, то ограничение — 2048 / 16 = 128 и так далее.

    3) Поскольку последний фрагмент является конкатенацией абсолютно одинаковых кусков кода, то запись запроса в программе можно существенно (более чем в три раза) сократить за счет динамического формирования текста запроса. Здесь это не сделано из-за того, что динамическое формирование текста запроса было запрещено условиями задачи.

    4) Также можно сэкономить на переписываниях таблиц Таб и Шаг, объединив действие двух запросов в одном. Это не было сделано потому, что запись запроса в таком виде является более громоздкой из-за отсутствия возможности запомнить результат целого от деления на два для его последующего использования в трех местах. Есть и вариант вообще без деления — за счет представления номера символа в текстовой строке из «0» и «1». При этом вместо деления используется сдвиг. Он также оказался длиннее в записи (но быстрее).

    Reply ↓
  4. AlexO

    потом выяснится, что все уперлось в сортировку букв…

    я думал об этом на заре 1С, но создавать ТЗ с «1 буква — одна запись» это такая ерунда… а уж в на базе 1С — совсем бесперспективное дело.

    Это ж сколько миллионов записей получится при такой разбивке хотя б 100 тыс наименований элементов справочников….

    Ведь здесь сколько букв в наименовании — столько строк, и так по каждому наименованию….

    Reply ↓
  5. AlexO

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

    Reply ↓
  6. mymyka

    (5)Уважаемым ildarovich имеет весьма своеобразную технику написания кода, но в данном конкретном случае зря хаешь, это действительно достойная и оригинальная работа. Тем более, что в самом начале публикации написано же:

    Метод сложный и по сравнению с внезапросной техникой представляет больше спортивный, чем практический интерес.
    Reply ↓
  7. andrewks

    в плане решения реальных задач — конечно, изврат 🙂

    зато как красиво! и в алгоритмическом смысле тоже очень полезно баловаться подобными задачками

    Reply ↓
  8. andrewks

    (6) всё правильно, задача изначально ставилась как практическая (в ветке форума), однако к моменту, когда спор зашёл о том, возможно ли подобное в чистом запросе в принципе, задача (а точнее, её решение) представляла по большей части сугубо научный интерес

    Reply ↓
  9. ildarovich

    Могу сказать, что в одном проекте эта штука сейчас действительно используется. В конфигурации «1С: Консолидация» выбирать данные из консолидируемых информационных баз требуется принципиально запросами. Там текст запроса прямо в справочник правил подключения пишется. Конфигурация очень непростая, место для постобработки найти трудно. Артикулы в базах филиалов сильно замусорены. Где точка добавлена, где вместо нуля буква «О», где вместо латинских русские буквы. Народ в филиалах у клиента косячит как может. Вот этим самым запросом артикулы разбираются на символы, заменяются по таблице, затем по указанному принципу собираются, и в Консолидацию уже приходят «чистые».

    Reply ↓
  10. AlexO

    (9)

    А обработки не справляются? Почему нельзя обработками сделать?

    Reply ↓
  11. Новиков

    (10) это академический интерес, направленный на разубежденье критиков, что сделать это можно. Практический аспект применения этого алгоритма автор описывал.

    Reply ↓
  12. andrewks
    Числа от 0 до 2047 записываются в таблицу РА9876543210, название которой составлено из первой буквы слова «Разряд» и номеров содержащейся в ней двоичных разрядов (в шестнадцатеричной системе – цифр не хватило, десятый разряд обозначен буквой «А»)

    A — одиннадцатый разряд

    Reply ↓
  13. andrewks

    в последнем запросе


    ВЫБРАТЬ НомерСтроки, ё, ВЫБОР ПОДСТРОКА(а, ё, 1) КОГДА » » ТОГДА «_»

    подчёркивание сделано специально, чтобы выделить необходимость замены этого символа на НПП?

    Reply ↓
  14. ildarovich

    (13) Нет, это получилось случайно, забыл убрать. У меня в коде в приложенной обработке, в которой я отлаживал запрос, используется «_», которое реплейсом заменяется на Символы.НПП. Но так даже лучше, потому что неразрывный пробел или на сайт или с сайта не копируется.

    (12) Все же я считал разряды от нулевого и в этом смысле это десятый разряд (одиннадцатая цифра, но разряд номер 10).

    Тут осталась более важная неточность — сколько символов можно максимально получать в запросе: 2047 или 2048? В 1С есть ограничение 1024 на длину ограниченной строки. 2048 — это видимо, что-то связанное с юникодом. Я ошибочно написал 2047(вроде тест это показал, но сейчас перепроверил — не так), хотя на самом деле — ограничение — 2048 символов! Я поправлю потом в статье.

    Кстати, возможно, получится ускорить фрагмент с нумерацией символов (есть некоторые идеи). Тогда планирую написать универсальную обработку для конкатенации наименований номенклатуры и т.п. в ТЧ документов и сравнить три подхода: код, СКД и этот по скорости.

    Также было бы интересно сравнить быстродействие метода при объединении 2048 односимвольных строк (концовка запроса) с работой RCTE на чистом SQL. Сдается мне, что из-за того, что в SQL строки «наращиваются»(?), RCTE может проиграть по быстродействию данному подходу (см. статью «Опять двойка»).

    Reply ↓
  15. bulpi

    Автор, а что за странное условие

    ГДЕ ПОДСТРОКА(а, ё, 1) + «!» <> «!»

    Почему нельзя было написать

    ГДЕ ПОДСТРОКА(а, ё, 1)<> «»

    ????

    Reply ↓
  16. andrewks

    (14)

    Все же я считал разряды от нулевого

    мне кажется, нумерация разрядов с нуля — не совсем корректно.

    так можно прийти к «единичной», «семичной», «девятичной» и «пятнадцатиричной» системам счисления 🙂

    Reply ↓
  18. ildarovich

    (15) — Отличный вопрос! Думаю, над ним уже поломал голову andrewks. Вообще-то еще немного информации есть в обсуждении темы, с которой все началось: http://forum.infostart.ru/forum26/topic69221/.

    Но оказалось, что если подстрока в запросе равно пробелу » «, то она равна и пустышке «». То есть фактически » » = «». Из-за этого поначалу я не мог при разбивке отличить выход за границы строки от пробела внутри строки, поэтому приходилось обрезать пробелы справа сравнением с максимальным не пробельным символом чтобы отличить середину строки от заграницы — использовать лишний запрос. В посте (84) приведен такой вариант. Потом появилось такое сравнение. Равенство свидетельствует, что подстрока — точно пустышка, а мы за границей.

    Reply ↓
  19. MiCe

    так генерировать лучше

    ВЫБРАТЬ  РАЗЛИЧНЫЕ ПЕРВЫЕ 2048
АдресныйКлассификатор.Код
поместить таб
ИЗ
РегистрСведений.АдресныйКлассификатор КАК АдресныйКлассификатор;
ВЫБРАТЬ
КОЛИЧЕСТВО(таб.Код) номер
ИЗ
таб КАК таб
ВНУТРЕННЕЕ СОЕДИНЕНИЕ таб КАК таб1
ПО таб.Код >= таб1.Код

СГРУППИРОВАТЬ ПО
таб.Код
упорядочить по КОЛИЧЕСТВО(таб.Код)

    Показать

    Reply ↓
  20. MiCe

    да… еще… в 1Сы используется кодировка utf8…. а в mssql utf16….

    так что делайте выводы…. все строковые данные обрабатывает 1с сервер…. оттуда и проблема пробела….

    Reply ↓
  21. ildarovich

    (19) Категорически не согласен! — Ваш способ невероятно долог по сравнению с «Порождающим запросом» (с). Для примера попробуйте сгенерировать таблицу из миллиона чисел. Если хватит памяти, Вам придется подождать несколько дней. Порождающий запрос тратит на это доли секунды. Во вторых, не в каждой конфигурации есть адресный классификатор. Кстати, в конце статьи про порождающий запрос есть функция, которая генерирует ТЕКСТ ЗАПРОСА, формирующий в указанной таблице искусственную таблицу заданного диапазона чисел.

    Вот как с использованием этой функции выглядит процедура генерации «ребер звезды» для обработки ГрафоГраф

    Функция РебраЗвезды() Экспорт
Возврат НовыйЗапрос(
СтрЗаменить(ProtoText(ВсегоВершин), «r» + ВсегоВершин, «Числа») + «;» +
«ВЫБРАТЬ
| 1 КАК Имя1,
| А.Х + 1 КАК Имя2,
| 0.1 КАК Упругость
|ИЗ
| Числа КАК А
|ГДЕ
| А.Х > 0
|
|УПОРЯДОЧИТЬ ПО
| Имя1,
| Имя2»
).Выполнить().Выгрузить()
КонецФункции

    Показать

    ProtoText — это упомянутая функция. Видно, что код получается простым.

    Reply ↓
  22. таксяк

    «Многие сомневаются в возможности решения этой задачи одним пакетным запросом, а зря!» а кто сомневался-то? Сомнения были, что можно задачу решить не создавая код запроса программно — так и есть. Если есть возможность кодировать — то создавать запрос вообще не нужно.

    Имхо — бессмысленное творение.

    Reply ↓
  23. ildarovich

    (22) Видимо, Вы что-то недопоняли: код запроса не формируется программно!!! Он записан в тексте статьи ПОЛНОСТЬЮ. В таком виде копируется в консоль запросов и решает задачу. Автор задачи согласился с тем, что задача решена в соответствии с заданием.

    А у меня к Вам другой вопрос: в процессе обсуждения задачи Вы говорили о RCTE и прочих возможностях «сборки» агрегатной строки на SQL.

    Если Вы такой большой знаток MSSQL, не могли бы Вы написать CTE (RCTE), которая собирала бы в одну строку таблицу из 2048 строк, в каждой строке которой будет по одному символу. Меня интересует время выполнения данной CTE. Вполне возможно, что оно будет больше, чем время выполнения запроса на 1С, составленный по предлагаемой методике.

    Reply ↓
  24. p1l1gr1m

    Прочитав заголовок статьи, решил, что сначала надо самому додуматься до решения. Целый час ломал голову — как это сделать без заранее определенного количества соединений или подзапросов, в итоге сдался, прочитал — а вы оказывается пользуетесь определенным заранее количеством подзапросов в пакетном запросе. Да, это работает, да, это обусловлено ограничениями передаваемого типа строки, но все же это не flawless victory, а решение задачи с «допущениями»)

    Reply ↓
  25. ildarovich

    (24) Ваше разочарование похоже на разочарование ребенка, которому объяснили секрет фокуса. Но не становитесь, пожалуйста на сторону обывателя, который считает фокусы обманом и надувательством. С точки зрения фокусника, все его секреты — это хорошее знание законов физики, математики, химии и психологии.

    В тот момент, когда я первый раз сказал, что знаю решение, я знал тот факт, что число записей в таблице любой СУБД КОНЕЧНО, помнил притчу о падишахе, зернышке и шахматной доске и имел запрос, который каскадно соединяет две строки в одну. Я полез разбираться в ограничениях СУБД, файловых систем и начал переводить терабайты в байты, чтобы узнать число необходимых соединений. Тогда в тех же ограничениях прочитал про максимальный размер строки, который может храниться в колонке таблицы. Ну и поскольку запрос возвращает именно таблицу, а не формирует файл, то стало понятно, что пользоваться нужно меньшим лимитирующим ограничением. Оно оказалось очень удобным — хватило всего 11 соединений. Так что в решении используется не допущение, а научный факт!

    Кстати, на практике я использую динамическое построение запроса, которое здесь было запрещено правилами. При этом решение выглядит гораздо менее громоздким.

    Reply ↓
  26. MrFlanker

    Ну в принципе очевидное решение. Не очевиден расчет 11 итераций,….здесь присутствует элемент научной работы. Работа интересная в научном плане (для студента 1-3 курса например), имеет мало практического значения.

    Выбор языка программирования и реализация монстроподобны.

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

    Reply ↓
  27. kiruha

    Интересно, забавно и не практично.

    Еще с 7.7 народ понял что неограниченные строковые поля — это особы случай и нет никакого смысла пихать его в колонку для этого не предназначенную. В таких полях может быть сотни строк теоретически

    В БД для этого даже используют спец типы.

    На практике (1С!) достаточно получить «содержание» в отдельную таблицу, проиндексировать и получать его по мере вывода в табл документ. Аналогично со «сложением»

    Reply ↓
  28. Pawlick

    Ув. ildarovich.

    Вы пишите,что этим способом решена задача чистки артикулов. Ради спортивного интереса пробую решить подобную задачу с помощью Вашего метода в надежде добиться лучшего по времени результата по сравнению с тупым перебором. Очевидно, что в случае с артикулами, первоначальная таблица «Дано» должна распознаваться алгоритмом именно как коллекция значений для обработки (т.е. таблица артикулов, в которых что ли бо нужно изменить), а не как разобранное предложение, которое необходимо собрать.

    К сожалению, у меня не хватило ума переписать запрос нужным образом и собрать передаваемые артикулы в первоначальное состояние. Если не затруднит: ткните носом в решение. Без Вашей помощи я видимо не разберусь.

    PS.

    Или же чистка артикулов происходит по одному, перебором?

    Reply ↓
  29. ildarovich

    (28) Pawlick, вот адаптированная обработка. Потребовалось добавить группировку по номеру строки. В расчете на то, что артикул будет не длиннее 64-х символов запрос получился таким

    ВЫБРАТЬ Дано.НомерСтроки НомерСтроки, Дано.Колонка а ПОМЕСТИТЬ Дано ИЗ &Дано КАК Дано;

    ВЫБРАТЬ 0 ё ПОМЕСТИТЬ Р0 ОБЪЕДИНИТЬ ВЫБРАТЬ 1;

    ВЫБРАТЬ 2 * Р1.ё + Р0.ё ё ПОМЕСТИТЬ Р10 ИЗ Р0 Р1, Р0;

    ВЫБРАТЬ 4 * Р32.ё + Р10.ё ё ПОМЕСТИТЬ Р3210 ИЗ Р10 Р32, Р10;

    ВЫБРАТЬ 16 * Р54.ё + Р3210.ё + 1 ё ПОМЕСТИТЬ Р543210 ИЗ Р10 Р54, Р3210;

    ВЫБРАТЬ НомерСтроки ж, ё, ВЫБОР ПОДСТРОКА(а, ё, 1) КОГДА «» «» ТОГДА «»_»» ИНАЧЕ ПОДСТРОКА(а, ё, 1) КОНЕЦ а ПОМЕСТИТЬ Буквы ИЗ Дано, Р543210 ГДЕ ПОДСТРОКА(а, ё, 1) + «»!»» <> «»!»»;

    ВЫБРАТЬ ж, ё, ВЫБОР КОГДА а ПОДОБНО «»[a-z,0-9]»» ТОГДА а ИНАЧЕ «»_»» КОНЕЦ а ПОМЕСТИТЬ Таб ИЗ Буквы;

    ВЫБРАТЬ ж, ВЫРАЗИТЬ(ё/2 КАК ЧИСЛО(15,0)) е, ё, а ПОМЕСТИТЬ Шаг ИЗ Таб; УНИЧТОЖИТЬ Таб;

    ВЫБРАТЬ ж, е ё, МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА а ИНАЧЕ «»»» КОНЕЦ) + МАКСИМУМ(ВЫБОР е*2-ё КОГДА 1 ТОГДА «»»» ИНАЧЕ а КОНЕЦ) а ПОМЕСТИТЬ Таб ИЗ Шаг СГРУППИРОВАТЬ ПО ж, е; УНИЧТОЖИТЬ Шаг;

    …еще пять повторов…

    ВЫБРАТЬ ж НомерСтроки, а КОЛОНКА ИЗ Таб УПОРЯДОЧИТЬ ПО ж

    Условие правильности символа в артикуле — это либо латинские буквы, либо цифры. Все другие символы заменяются неразрывным пробелом. Правило легко поменять вплоть до использования таблицы замен.

    Reply ↓
  30. Pawlick

    (29)

    спасибо огромное, Щас буду пробовать.

    Reply ↓
  31. u_n_k_n_o_w_n

    Добрый день!

    Пытаюсь разобраться с Вашей статьей.

    Ника не могу понять фразу: «Соединим для начала попарно все соседние нечетные и четные строки.»

    Тогда у вас должна получиться примерно следующая таблица:

    е ё а

    1 1 Строка1

    1 2 Строка2

    2 пропускаем так, как четная

    3 2 Строка2

    3 4 Строка4

    4 пропускаем так, как четная

    5 4 Строка5

    5 6 Строка5

    и т.д.

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

    Правильней я думаю будет фраза: «Соединим для начала попарно все нечетные и четную строку, следующую за нечетной.»

    Reply ↓
  32. ildarovich

    (31) u_n_k_n_o_w_n, да, возможно, предложение получилось двусмысленным. Можно было написать так:

    …Соединим для начала попарно каждую нечетную со следующей за ней четной строкой…

    Чуть длиннее, но, кажется, более точно. — Поправлю в статье при случае.

    Reply ↓
  33. u_n_k_n_o_w_n

    Добрый день!

    Есть ТЗ со следующей структурой:

    ПГ1; ПГ2,КОД, ТС.

    Где ПГ1 – поле группировки 1, ПГ2 – поле группировки 2, Код – уникальные номера (начинаются с 1) в рамках полей группировки, ТС – текстовое поле для суммирования.

    ТЗ содержит N количество строк.

    Подскажите можно ли применить Ваш запрос в моем случае.

    Спасибо.

    PS: Долго думал, как мне можно доработать Запрос под свои цели, но пока не получается.

    Reply ↓
  34. u_n_k_n_o_w_n

    Спасибо огромное, буду пытаться освоить науку.

    Reply ↓
  35. ildarovich

    (33) u_n_k_n_o_w_n, вот обработка с готовым запросом. От запроса в статье он отличается дополнительными группировками по ПГ1, ПГ2

    Reply ↓
  36. a1ex4ndr

    (24)

    Да, это работает, да, это обусловлено ограничениями передаваемого типа строки, но все же это не flawless victory, а решение задачи с «допущениями»)

    Вроде претензий никаких и не было 🙂

    Reply ↓
  37. BigB

    БРАВО! (стоя хлопаю в ладоши)

    Reply ↓
  38. Steelvan

    Заголовок «Агрегатное суммирование строк в запросе – сложно, но не невозможно» как то звучит по-американски (двойное отрицание усложняет восприятие).

    Думается мне, что

    «Агрегатное суммирование строк в запросе – сложно, но возможно» будет звучать по-русски (нормально, и даже немного в рифму).

    Reply ↓
  39. ildarovich

    (38) Steelvan, спасибо за совет

    Reply ↓
  40. нормальный такой

    Привет, чет ошибку сыпет на 8,3,4

    эт нормально?

    http://take.ms/ObhsZ

    Reply ↓
  41. ildarovich

    (40) нормальный такой, это НЕ нормально, все должно работать. Если меняли запрос, приведите его полный текст. И что задавали в параметрах. Чтобы я мог попытаться воспроизвести ошибку.

    Вообще поле «а» формируется на предыдущих шагах запроса. Как подстрока длины 1. Как оно стало неограниченным мне непонятно.

    Давайте разбираться.

    Reply ↓
  42. нормальный такой

    (41) неа, не менял.

    Скопировал как представлено в шапке, вставил в конструктор запроса и получил такую ошибку.

    Сейчас посидел поковырял Ваш запрос и понял примерно что происходит.

    На N итерации возникает вот такая ошибка как я привел, если сократить запрос до N-1 итераций, то ошибки не возникает.

    Reply ↓
  43. AlexO

    (18)

    Но оказалось, что если подстрока в запросе равно пробелу » «, то она равна и пустышке «».

    А с Символ(32) сравнить?

    И что-то ни разу не попадалось, что пустая строка — равна при сравнении «пробелу».

    Reply ↓
  44. ildarovich

    (44) AlexO, в запросе это так, там все приводится к CHARVAR, поэтому ВЫБОР КОГДА «» = » » ТОГДА «всегда» ИНАЧЕ «никогда» КОНЕЦ. Также будет ВЫБОР КОГДА «» = &СимволПробела ТОГДА «всегда» ИНАЧЕ «никогда» КОНЕЦ, где Запрос.УстановитьПараметр(«СимволПробела», Сивол(32)).

    Reply ↓
  45. ildarovich

    (42) нормальный такой, извините, забыл в свое время ответить, в чем я разобрался.

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

    Но при практических экспериментах в большинстве случаев ограничением является большее число — 2048, что и было использовано мною в статье. Система (где 1024, где 2048) безусловно есть, но я не стал детально это исследовать.

    Reply ↓
  46. proger1c81

    Добрый день, Маэстро запросов!

    Думал, что никогда мне на практике не понадобится суммирование строк….

    но как говорится: «никогда не говори никогда»….

    Помогите, пожалуйста, написать запрос для ТЗ со следующей структурой:

    ПГ; ТС.

    Где ПГ – поле группировки, ТС – текстовое поле для суммирования.

    ТЗ содержит N количество строк.

    и подскажите, пожалуйста, как можно задать разделитель строк?

    например: как у вас показано на скриншоте выше:

    петух ку

    петух ка

    петух ре

    петух куу

    корова му

    корова муу

    собака

    Если задать разделитель строк «-«, в результате должно получиться три строки:

    петух ку-ка-ре-куу

    корова му-муу

    собака

    причем в конце разделитель осутствует

    Reply ↓
  47. ildarovich

    (47) proger1c81, мне кажется, что лучше всего скачать файл, прикрепленный к сообщению (34) в данной теме. Там можно вообще ничего не менять, определив поле ПГ2 = 0. То есть там все уже готово. Если что-то будет не получаться, сообщите.

    По поводу разделителей: то же самое. Достаточно вместо поля ТС в исходной таблице взять Разделитель + ТС КАК ТС. Получится все, как вам нужно, но в самом начале итоговой строки будет один лишний разделитель (-ку-ка-ре-ку). Поэтому потребуется к итоговому полю применить выражение ПОДСТРОКА(а, 2, 1024), чтобы убрать этот лишний начальный разделитель.

    Reply ↓
  48. proger1c81

    (48) я попробовал указанный файл прежде чем задать вопрос. Т.к. там запрос сделан с учетом уникального поля «Код», а моя структура ТЗ отличается как раз отсутствием этого уникального поля «Код». Если я беру ту структуру и делаю ПГ2=0 и Код=0, то суммирование строк не происходит.

    Reply ↓
  49. ildarovich

    (49) proger1c81, теперь понял в чем дело.

    Но это уже принципиальный вопрос. Дело в том, что сложить строки без наличия такого уникального кода НЕЛЬЗЯ. Принципиально нельзя. И вне запроса, кстати, тоже. Это и в статье и в комментариях к исходному обсуждению обсуждалось. Так как сложение строк не коммутативно, то есть принципиально требует указания их порядка в сложении. Поэтому поле код (или номер) нужно получить. Например, соединив исходную таблицу саму с собой по условию ПГ = ПГ И КакоеЛибоПоле >= КакоеЛибоПоле и посчитав число записей самосоединяемой таблицы, удовлетворяющих этому условию.

    Reply ↓
  50. proger1c81

    (50) Спасибо за данную статью! Невозможное стало возможным!

    Встроенная функция СКД СуммироватьСтроки() в моем случае не помогала, т.к. все ресурсы (числовые, строковые через СуммироватьСтроки()) выводятся в конце таблицы. Может быть СКД и позволяет выводить ресурсы в начале или в середине таблицы, но я не знаю, как это сделать. А у меня задача стояла, чтобы результирующее поле «суммированная строка» располагалось в любом месте (в начале, середине). В результате получил таблицу следующего вида (см.скриншот)

    Reply ↓
  51. herfis

    (51) proger1c81, Задача вывода ресурсов в произвольном месте в СКД легко решалась с помощью специальной группировки. А с какого-то релиза это штатная возможность: в настройках СКД «Другие настройки», опция «Авто позиция ресурсов». По дефолту установлено старое поведение «После всех полей» для лучшей обратной совместимости. Достаточно переклацнуть на «Не использовать».

    Reply ↓
  52. proger1c81

    (52) herfis, Спасибо за подсказку о штатной возможности в СКД размещения ресурсов.

    Подскажите, как в СКД с помощью функции СуммироватьСтроки() указать нужный разделитель. СКД сцепляет строки с помощью символа Переноса строки, что по моему условию задачи не подходит.

    Вот как получается с помощью СКД (см.скриншот):

    Reply ↓
  53. palsergeich

    (53) 2м параметром передайте разделитель в строковом ввиде

    Reply ↓

Leave a Comment

Ваш адрес email не будет опубликован. Обязательные поля помечены *