ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg / connect by ?
10 сообщений из 10, страница 1 из 1
listagg / connect by ?
    #39279167
trumon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
trumon
Гость
Не могу найти простое решение, может кто подскажет?

Есть набор, типа:

str_numgroup_num ref_grouptext 1 1 null 'a' 2 1 null 'b' 3 2 1 null 4 2 null 'd' 5 2 null 'e' 6 null 2 'f'

Из него нужно получить набор в виде сконкатенированных значений столбца text на каждую запись с учетом связки с группой (ref_group ссылается на группу дочерних элементов, определенных через group_num). Нужно учесть, что группа может включать в себя другую группу.

Итого в результате должно получиться:

str_num text1 'a'2 'b'3 'a;b'4 'd'5 'e'6 'a;b;d;e;f'

Понимаю, что тут listagg или connect by, но их применить по назначению не удалось.
...
Рейтинг: 0 / 0
23.07.2016, 18:00:28
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279172
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
trumonПонимаю, что тут listagg или connect by, но их применить по назначению не удалось.как пытался, где пример
...
Рейтинг: 0 / 0
23.07.2016, 18:16:51
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279228
andreymx
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andreymx
Участник
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with t as
(
select 1 id, 1 group_num,   null parent_group_num,   'a' text from dual union all
select 2 id, 1 group_num,   null parent_group_num,   'b' text from dual union all
select 3 id, 2 group_num,   1    parent_group_num,  null text from dual union all
select 4 id, 2 group_num,   null parent_group_num,   'd' text from dual union all
select 5 id, 2 group_num,   null parent_group_num,   'e' text from dual union all
select 6 id, null group_num,2    parent_group_num,   'f' text from dual
)
select id, listagg(text, ';') within group(order by null) from
(
select distinct id, connect_by_root text text
  from t
connect by parent_group_num = prior group_num
--start with parent_group_num is null
)
group by id
...
Рейтинг: 0 / 0
24.07.2016, 07:27:24
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279263
trumon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
trumon
Гость
Премного благодарен, а то уже начал циклы на PL/SQL писать.
Но ситуация теперь несколько сложнее.

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

str_num group_num ref_group group_attr text1 1 null null 'a'2 1 null null 'b'3 2 1 'a1' null4 2 null null 'd'5 2 null null 'e'6 null 2 'a2' 'f'

Надо получить:

str_num text1 'a'2 'b'3 'a1(a;b)'4 'd'5 'e'6 'a2(a1(a;b);d;e;f)'
...
Рейтинг: 0 / 0
24.07.2016, 13:31:26
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279269
trumon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
trumon
Гость
Сделал кастыль, но мне кажется работает он только в частном случае. Проверяю лишь группу предыдущего уровня, хотя она может быть и на уровень дальше.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
with t as
(
select 1 id, 1 group_num,   null parent_group_num, null group_attr,    'a' text   from dual union all
select 2 id, 1 group_num,   null parent_group_num, null,               'b' text   from dual union all
select 3 id, 2 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 4 id, 2 group_num,   null parent_group_num, null,               'd' text   from dual union all
select 5 id, 2 group_num,   null parent_group_num, null,               'e' text   from dual union all
select 6 id, null group_num,2    parent_group_num, 'a2',              'f' text   from dual
)
, a as (
select distinct id, 
       level lvl, 
       group_attr,
       prior group_attr group_attr_prior,
       connect_by_root text text_root,
       count(1) over (partition by id, level) cnt
  from t
connect by parent_group_num = prior group_num
)
,b as 
(select  distinct 
        a.id, a.group_attr, a.group_attr_prior, 
        case when group_attr_prior is not null and text_root is not null 
          then group_attr_prior||'('||listagg(text_root, ';') within group(order by id) over (partition by a.id, a.lvl)||')'
          else text_root
        end lt
from a
where text_root is not null
)
select distinct
  b.id,
  group_attr||'('||listagg(lt, ';') within group(order by id) over (partition by id)||')' lt
from b
...
Рейтинг: 0 / 0
24.07.2016, 14:11:15
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279296
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
trumon,

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
select id,
       replace(listagg(group_attr || case
                         when group_attr is not null then
                          '('
                       end || result || case
                         when group_attr is null and prior_group_attr is not null then
                          ')'
                       end,
                       ';') within group(order by rn),
               '(;',
               '(') result
  from (select t.*, rownum rn, prior group_attr prior_group_attr
          from (select id,
                       group_attr,
                       listagg(text, ';') within group(order by text) result,
                       lvl
                  from (select id,
                               connect_by_root text text,
                               connect_by_root group_attr group_attr,
                               level           lvl
                          from t
                        connect by parent_group_num = prior group_num)
                 group by id, group_attr, lvl) t
         start with lvl = 1
        connect by prior id = id
               and prior lvl + 1 = lvl
               and prior group_attr is not null) z
 group by id;

        ID RESULT
---------- ------------------------------
         1 a
         2 b
         3 a1(a;b)
         4 d
         5 e
         6 a2(f;a1(a;b);d;e)
...
Рейтинг: 0 / 0
24.07.2016, 16:01:04
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279304
trumon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
trumon
Гость
авторdbms_photoshop
огромное человеческое спасибище..

Но как оказалось и это не все (.. на фактических данных получилось, что элемент может входить в разные группы. Т.е. уже не классическое дерево.

Типа:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t as
(
select 1 id, 1 group_num,   null parent_group_num, null group_attr,   'a' text   from dual union all
select 2 id, 1 group_num,   null parent_group_num, null,              'b' text   from dual union all
select 3 id, 2 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 3 id, 4 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 4 id, 2 group_num,   null parent_group_num, null,              'd' text   from dual union all
select 5 id, 2 group_num,   null parent_group_num, null,              'e' text   from dual union all
select 6 id, 3 group_num,   2    parent_group_num, 'a2',              'f' text   from dual union all
select 7 id, 3 group_num,   null parent_group_num, null,              'g' text   from dual union all
select 8 id, 3 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 8 id, 4 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 9 id, null group_num,3    parent_group_num, 'a3',              null text   from dual union all
select 10 id, null group_num,4    parent_group_num, 'a4',              null text   from dual --union all
)




Вот думаю regexp-ом воспользоваться уже по результирующей выборке или еще есть варианты?
...
Рейтинг: 0 / 0
24.07.2016, 17:20:03
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279310
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
trumon,

То что было ID становится ELEMENT_ID а ID нумеруется построчно:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
with t as
(
select 1 id, 1 element_id, 1 group_num,   null parent_group_num, null group_attr,   'a' text   from dual union all
select 2 id, 2 element_id, 1 group_num,   null parent_group_num, null,              'b' text   from dual union all
select 3 id, 3 element_id, 2 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 4 id, 3 element_id, 4 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 5 id, 4 element_id, 2 group_num,   null parent_group_num, null,              'd' text   from dual union all
select 6 id, 5 element_id, 2 group_num,   null parent_group_num, null,              'e' text   from dual union all
select 7 id, 6 element_id, 3 group_num,   2    parent_group_num, 'a2',              'f' text   from dual union all
select 8 id, 7 element_id, 3 group_num,   null parent_group_num, null,              'g' text   from dual union all
select 9 id, 8 element_id, 3 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 10 id, 8 element_id, 4 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 11 id, 9 element_id, null group_num,3    parent_group_num, 'a3',              null text   from dual union all
select 12 id, 10 element_id, null group_num,4    parent_group_num, 'a4',              null text   from dual --union all
)
select id,
       element_id,
       replace(listagg(group_attr || case
                         when group_attr is not null then
                          '('
                       end || result || case
                         when group_attr is null and prior_group_attr is not null then
                          ')'
                       end,
                       ';') within group(order by rn),
               '(;',
               '(') result
  from (select t.*, rownum rn, prior group_attr prior_group_attr
          from (select id,
                       element_id,
                       group_attr,
                       listagg(text, ';') within group(order by text) result,
                       lvl
                  from (select id,
                               element_id,
                               connect_by_root text text,
                               connect_by_root group_attr group_attr,
                               level           lvl
                          from t
                        connect by parent_group_num = prior group_num)
                 group by id, element_id, group_attr, lvl) t
         start with lvl = 1
        connect by prior id = id
               and prior lvl + 1 = lvl
               and prior group_attr is not null) z
 group by id,
       element_id
/

        ID ELEMENT_ID RESULT
---------- ---------- ------------------------------
         1          1 a
         2          2 b
         3          3 a1(a;b)
         4          3 a1(a;b)
         5          4 d
         6          5 e
         7          6 a2(f;a1(a;b);d;e)
         8          7 g
         9          8 h
        10          8 h
        11          9 a3(a2(f;a1(a;b);d;e);g;h)

        ID ELEMENT_ID RESULT
---------- ---------- ------------------------------
        12         10 a4(a1(a;b);h)

12 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
24.07.2016, 17:50:29
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279312
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
trumonВот думаю regexp-ом воспользоваться уже по результирующей выборке или еще есть варианты?Ну добавь group_num в группировку, чтоб различать строки с одинаковым ID, в чем проблема?
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
with t as
(
select 1 id, 1 group_num,   null parent_group_num, null group_attr,   'a' text   from dual union all
select 2 id, 1 group_num,   null parent_group_num, null,              'b' text   from dual union all
select 3 id, 2 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 3 id, 4 group_num,   1    parent_group_num, 'a1',              null text  from dual union all
select 4 id, 2 group_num,   null parent_group_num, null,              'd' text   from dual union all
select 5 id, 2 group_num,   null parent_group_num, null,              'e' text   from dual union all
select 6 id, 3 group_num,   2    parent_group_num, 'a2',              'f' text   from dual union all
select 7 id, 3 group_num,   null parent_group_num, null,              'g' text   from dual union all
select 8 id, 3 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 8 id, 4 group_num,   null parent_group_num, null,              'h' text   from dual union all
select 9 id, null group_num,3    parent_group_num, 'a3',              null text   from dual union all
select 10 id, null group_num,4    parent_group_num, 'a4',              null text   from dual --union all
)
select id,
       nullif(group_num, 0) group_num,
       replace(listagg(group_attr || case
                         when group_attr is not null then
                          '('
                       end || result || case
                         when group_attr is null and prior_group_attr is not null then
                          ')'
                       end,
                       ';') within group(order by rn),
               '(;',
               '(') result
  from (select t.*, rownum rn, prior group_attr prior_group_attr
          from (select id,
                       nvl(group_num, 0) group_num,
                       group_attr,
                       listagg(text, ';') within group(order by text) result,
                       lvl
                  from (select id,
                               group_num,
                               connect_by_root text text,
                               connect_by_root group_attr group_attr,
                               level           lvl
                          from t
                        connect by parent_group_num = prior group_num)
                 group by id, group_num, group_attr, lvl) t
         start with lvl = 1
        connect by prior id = id
               and prior group_num = group_num
               and prior lvl + 1 = lvl
               and prior group_attr is not null) z
 group by id, group_num;

        ID  GROUP_NUM RESULT
---------- ---------- ------------------------------
         1          1 a
         2          1 b
         3          2 a1(a;b)
         3          4 a1(a;b)
         4          2 d
         5          2 e
         6          3 a2(f;a1(a;b);d;e)
         7          3 g
         8          3 h
         8          4 h
         9            a3(a2(f;a1(a;b);d;e);g;h)
        10            a4(a1(a;b);h)
...
Рейтинг: 0 / 0
24.07.2016, 18:05:47
| Ответить | Цитировать | Написать  
listagg / connect by ?
    #39279340
trumon
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
trumon
Гость
Всем спасибо за помощь.
...
Рейтинг: 0 / 0
24.07.2016, 21:51:57
| Ответить | Цитировать | Написать  
10 сообщений из 10, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg / connect by ?
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&fpage=210&tid=1887820]:
 0ms
get settings:
 10ms
get forum list:
 20ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 55ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 59ms
get tp. blocked users:
 1ms
others: 229ms
total:  394ms
созд. / загр.: 394ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]