Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Агрегация строк  [new]
Alibek B.
Member

Откуда:
Сообщений: 3721
Есть запрос, который возвращает несколько строк.
Хотелось бы результат запроса сгруппировать в одну строку, чтобы текстовые значения были объединены через запятую.
В MSSQL я подобное делал с помощью FOR XML.
Подскажите, как это сделать в Oracle?
________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
2 авг 11, 11:17    [11057305]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
поискать по форуму агрегацию строк.
2 авг 11, 11:19    [11057320]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
_plus_
Member

Откуда:
Сообщений: 43
Alibek B.,

connect by
sys_connect_by_path
2 авг 11, 11:20    [11057323]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Alibek B.
Member

Откуда:
Сообщений: 3721
Vint
поискать по форуму агрегацию строк.

По форуму и искал.
Самое близкое, что нашел, было обратной операцией — разделить строку на несколько записей.

_plus_

connect_by относится к иерархическим записям, а у меня просто плоский список.
Вроде бы мне подходит WS_CONCAT, только непонятно, как в ней можно настроить символ, используемый в качестве разделителя.
2 авг 11, 11:40    [11057509]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
ILoveCoffee
Member

Откуда: Astana
Сообщений: 986
Alibek B.,
автор
как в ней можно настроить символ,

replace().

11.2+ Listagg.
2 авг 11, 11:42    [11057527]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
Иногда полезно читать. вопрос №5
2 авг 11, 11:50    [11057625]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
Alibek B.
Вроде бы мне подходит WS_CONCAT
не рекомендую.
Если версия позволяет - listagg.
Если версия пониже 11.2 и нужно эффективное решение - свой агрегат.
Если хочешь походить на ораклиста традиционной ориентации - connect by.
Если хочешь, чтобы коллеги по мсскл вопрошали "что это?!" - model.
Если придерживаешься трехбуквенных традиций, где первая X - XMLagg.
Для извращенцев на 11.2 - рекурсивный with.
2 авг 11, 11:59    [11057701]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Alibek B.
Member

Откуда:
Сообщений: 3721
У меня версия Oracle Database 10g Release 10.2.0.4.0 - 64bit Production.
listagg недоступен, model — это помоему overkill.
Своя функция исключается, изменять что-то в структуре БД нежелательно.
Почему WS_CONCAT не рекомендуется? Чем он хуже XMLagg? Или connect by, про который сейчас почитаю?
2 авг 11, 12:06    [11057774]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
Alibek B.
Почему WS_CONCAT не рекомендуется?
потому что не документирован и меняет тип результата в зависимости от патча.
2 авг 11, 12:10    [11057813]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Vint
Member

Откуда: Москва
Сообщений: 4564
-2-
ну вообще главное даже не тип результата и недокументированность, которую можно обойти, а не гарантированная сортировка результата.
2 авг 11, 13:40    [11058577]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Светлый_Дайвер
Member

Откуда: Киев-братъ городов Руськихъ
Сообщений: 903
Вот пример моего говнокода: поля note складываю через "-" вначале и спецсимвола перевода каретки вконце.

      WITH txt_value AS
           ( 
            SELECT ROWNUM rn, e.note note
            FROM  pmerror_field e 
            WHERE e.payment_id = p_paymnet_id
            )
      SELECT replace(ltrim(max(sys_connect_by_path(note,'/n-')),'/n'),'/n',chr(13)) 
      INTO l_text
      FROM txt_value      
      START WITH rn = 1
      CONNECT BY PRIOR rn = rn - 1;

2 авг 11, 15:16    [11059402]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
Светлый_Дайвер
Member

Откуда: Киев-братъ городов Руськихъ
Сообщений: 903
P.S. Вырвано из говнофункции по этому INTO l_text не забыть убрать еси чо.
2 авг 11, 15:26    [11059497]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
Светлый_Дайвер
replace(ltrim(max(sys_connect_by_path(note,'/n-')),'/n'),'/n',chr(13))
при таком количестве наворотов моделька, да даже xml, выглядят логичнее:
select xmltransform(xmlagg(xmlelement(x, username)),
'<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >   
   <xsl:template match="*">{<xsl:apply-templates/>}</xsl:template>
</xsl:stylesheet>'
) строка
from all_users 
where username like '%SYS%';

СТРОКА                                                                                               
-----------------------------------------------------------------------------------------------------
{SYS}{SYSTEM}{APPQOSSYS}{WMSYS}{EXFSYS}{CTXSYS}{ORDSYS}{MDSYS}{OLAPSYS}{OWBSYS_AUDIT}{SYSMAN}{OWBSYS}

select строка
from all_users 
where username like '%SYS%'
model return updated rows
dimension by (rownum r)
measures (username u, cast(null as varchar2(100)) строка)
rules iterate (100) until (presentv(u[iteration_number+1], 1, 0) = 0) (
   строка[0] = строка[0]||u[iteration_number+1]||presentv(u[iteration_number+2],';','')
);

СТРОКА                                                                                               
---------------------------------------------------------------------------------------------------- 
SYS;SYSTEM;APPQOSSYS;WMSYS;EXFSYS;CTXSYS;ORDSYS;MDSYS;OLAPSYS;OWBSYS_AUDIT;SYSMAN;OWBSYS             

2 авг 11, 17:17    [11060456]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
sworn
Member

Откуда: Point of no return
Сообщений: 127
-2-
Если версия пониже 11.2 и нужно эффективное решение - свой агрегат.

Недавиче отчет делал, строка аггрегировалась со строгой сортировкой, завязанной на некоем поле code, т.е. значениям на каждый code соответствует определенная позиция в строке:
col_1col_ncodeval
smthsmth340
smthsmth8500
smthsmth420
.....

резалт: smth;560;500;40;20
Написал на это UDAG, в котором в ODCIAggregateTerminate, сверял с массивом соответствий кодов - позиций. 100-200 тыщ, как мне показалось, проходят на ура. Всё ничего, но вот после прочтения Томми появились сомнения в состоятельности выноса излишней логики в UDAG:
Том
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2196162600402#7699115457285
...
Just write a custom function instead of an aggregate. Aggregates are "SET" things and "sets"
really don't have any concept of order.
...

СтОит ли продолжать в том же духе с UDAG (накапливать NestedTable(как пример), потом ее сортировать, конкатенировать элементы в строку), или с такими заморочками с чистой совестью в PL/SQL?
2 авг 11, 18:38    [11060968]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
sworn
СтОит ли продолжать в том же духе с UDAG (накапливать NestedTable(как пример), потом ее сортировать, конкатенировать элементы в строку)...
На миллионах элементов с коллекциями начинаются глюки. К сожалению, мне не удалось в агрегате использовать dml для использования временных таблиц. Для получения определенного порядка и программного дистинкта может быть выгоднее заполнять ассоциативный массив.
sworn
или с такими заморочками с чистой совестью в PL/SQL?
Передавать в процедуру курсор или ключи для вынужденного nested loops? Помимо неуниверсальности и тормозов на большом количестве групп, возможно получение несогласованных данных.
2 авг 11, 20:29    [11061235]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63950
Блог
sworn
Aggregates are "SET" things and "sets" really don't have any concept of order

Эта фраза как бы верна, но не по делу. Они set things в том смысле, что "от перестановки строк результат не меняется", но не в том, что результат функции не должен иметь какой-то внутренней логики.

sworn
или с такими заморочками с чистой совестью в PL/SQL?

Если есть возможность избежать PL/SQL, лучше избежать, имхо.
2 авг 11, 23:07    [11061778]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
sworn
Member

Откуда: Point of no return
Сообщений: 127
Спасибо за отзывы.
-2-
Передавать в процедуру курсор или ключи для вынужденного nested loops?

Уупс.. передаю в аггрегат пару ключ-значение, как раз для вынужденного nested loops, правда в той задаче их (пар) там несколько десятков на каждую с-агрегированную запись. Чую что-то тут не так...

Зная, что количество кодов конечное и небольшое , предыдущие люди просто сделали вью с pivot (max(decode...)..) на эти нескольколо десятков кодов, и потом в цикле тупо лепили строку из уже колонок записей курсора.... Т.о. изменение структуры этих записей влекло за собой рытьё в пакетах и вьюхах.
3 авг 11, 09:11    [11062513]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
sworn
Member

Откуда: Point of no return
Сообщений: 127
softwarer
Эта фраза как бы верна, но не по делу.

Приведенная мною ссылка - ссылка на пост, где паренёк спрашивает о примерно такой же сортировке в аггрегате, как у меня. А Том сказал, что нефиг сортировать в аггрегате. Вот я и вдумался. Или Вы про то, что Том о чем-то другом?
3 авг 11, 09:28    [11062575]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 63950
Блог
sworn
Приведенная мною ссылка - ссылка на пост, где паренёк спрашивает о примерно такой же сортировке в аггрегате, как у меня. А Том сказал, что нефиг сортировать в аггрегате. Вот я и вдумался. Или Вы про то, что Том о чем-то другом?

Ага, о том, что Том брякнул что-то не по делу.

Судите сами. Вот у меня была задача - куча поставщиков, группируем, например, по датам, хотим видеть, какие товары поставляли эти поставщики (ну там "столы, стулья, тарелки"), группируем по регионам, опять же хотим видеть. Агрегат, самое оно. Теперь усложняем задачу: хотим, чтобы в результате значения были отсортированы по алфавиту. Красивее и удобнее. И что, это уже не агрегат?
3 авг 11, 11:36    [11063462]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
OraHome92
Guest
А при конкатенации строк через xmlagg есть возможность сортировку использовать?
может есть какой-то хитрый способ там в шаблоне что-то указать...

вот listagg - клевая вещь - указываешь сортировку какую хочешь...
но ограничены мы varcharом ;(

или какой способ (без PL/SQL) подскажите для конкатенации строк с сортировкой и очень больших?
22 мар 12, 20:01    [12298045]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
-2-
Member

Откуда:
Сообщений: 15330
OraHome92
А при конкатенации строк через xmlagg есть возможность сортировку использовать?
глянуть в sql reference не судьба?
22 мар 12, 20:13    [12298085]     Ответить | Цитировать Сообщить модератору
 Re: Агрегация строк  [new]
OraHome92
Guest
о чудо! действительно всё есть ;)

премного благодарен!
22 мар 12, 21:00    [12298259]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить