Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg / connect by ? / 10 сообщений из 10, страница 1 из 1
23.07.2016, 18:00:28
    #39279167
trumon
trumon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
Не могу найти простое решение, может кто подскажет?

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

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:16:51
    #39279172
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
trumonПонимаю, что тут listagg или connect by, но их применить по назначению не удалось.как пытался, где пример
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
24.07.2016, 07:27:24
    #39279228
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
Код: 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, 13:31:26
    #39279263
trumon
trumon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
Премного благодарен, а то уже начал циклы на 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, 14:11:15
    #39279269
trumon
trumon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
Сделал кастыль, но мне кажется работает он только в частном случае. Проверяю лишь группу предыдущего уровня, хотя она может быть и на уровень дальше.

Код: 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, 16:01:04
    #39279296
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
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, 17:20:03
    #39279304
trumon
trumon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
автор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:50:29
    #39279310
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
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, 18:05:47
    #39279312
dbms_photoshop
dbms_photoshop
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
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, 21:51:57
    #39279340
trumon
trumon
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
listagg / connect by ?
Всем спасибо за помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / listagg / connect by ? / 10 сообщений из 10, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 493ms
Закрыть
start [/forum/topic.php?fid=52&mobile=1&tid=1887820]:
 0ms
get settings:
 9ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 161ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 49ms
get tp. blocked users:
 2ms
others: 240ms
total:  493ms
x
x
Закрыть


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