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

Есть вопрос:

Допустим у меня есть такой набор данных:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
field1 (int)       full_path (varchar2)

3242             ,3242,  
104550           ,3242,104550,
146951           ,3242,146951,
14               ,3242,146951,14,
15               ,3242,146951,14,15



короче говоря field1 - это уникальный ID записи, а full_path - полный путь этой записи в дереве.

Из этого набора необходимо получить такое:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
field2 (int)       field3 (int)

3242             3242  

104550           3242
104550           104550

146951           3242
146951           146951

14               3242
14               146951
14               14 

15               3242
15               146951
15               14 
15               15



то есть, если допустить, что первый набор был получен при помощи listagg (хотя это не так и использовалась sys_connect_by_path)
то в данном случае нужно обратно всё "разгруппировать".

Есть ли какое-то решение ?

Да, исходный набор данных в таблице выглядит так:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
id, parent_id

3242     NULL
104550   3242
146951   3242
14       146951
15       14




Спасибо!
...
Рейтинг: 0 / 0
23.05.2013, 18:57
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270724
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Станислав БабинЕсть ли какое-то решение ?
Да: не создавать самому себе трудности и сразу использовать иерархические запросы для
получения нужного результата прямо из исходного набора данных. Без промежуточных
извращений с listagg.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
23.05.2013, 19:02
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270731
Станислав Бабин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Станислав Бабин
Гость
Dimitry SibiryakovСтанислав БабинЕсть ли какое-то решение ?
Да: не создавать самому себе трудности и сразу использовать иерархические запросы для
получения нужного результата прямо из исходного набора данных. Без промежуточных
извращений с listagg.


Можете ли составить текст запроса для приведенного примера?
Изначальный набор данных 3, нужно получить набор данных 2.
...
Рейтинг: 0 / 0
23.05.2013, 19:06
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270749
Dimitry Sibiryakov
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Dimitry Sibiryakov
Участник
Станислав БабинМожете ли составить текст запроса для приведенного примера?

Могу, но зачем оно мне? RTFM connect by или with recursive, студент.
Posted via ActualForum NNTP Server 1.5
...
Рейтинг: 0 / 0
23.05.2013, 19:25
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270759
Lecter
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lecter
Участник
Станислав Бабин,

connect by + regexp_substr спасут отца русской демократии.
...
Рейтинг: 0 / 0
23.05.2013, 19:37
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270786
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Lecterconnect by + regexp_substr connect by + union all
...
Рейтинг: 0 / 0
23.05.2013, 19:52
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270800
Тряхнём перечницей
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Тряхнём перечницей
Гость
-2-Lecterconnect by + regexp_substr connect by + union all
Код: plsql
1.
select connect_by_root id, id from t connect by id = prior parent_id;
...
Рейтинг: 0 / 0
23.05.2013, 19:57
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270845
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
Тряхнём перечницей,

да, про union я перемудрил с равенством паренту.
...
Рейтинг: 0 / 0
23.05.2013, 20:34
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270852
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
Станислав Бабин,
мона по старинке вместо connect by "pivot" фурумный
Код: 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.
  1  WITH t AS
  2  (SELECT 1 id ,',1234,567,89,0' str FROM dual union all
  3   SELECT 2 id ,',asdf,dfg,fg,g' str FROM dual
  4  )
  5  select id,
  6   substr(str,
  7           instr(str, ',', 1, level)+1
  8         , instr(str||',', ',', 1, level+1)-instr(str, ',', 1, level)-1) s
  9    from t /*, pivot  ....*/
 10  connect by level <= length(trim(str)) - length(trim(replace(str, ',')))
 11   and id = prior id
 12   and prior DBMS_RANDOM.VALUE IS NOT NULL
 13* order by 1
SQL> /

        ID S
---------- --------------
         1 1234
         1 567
         1 89
         1 0
         2 asdf
         2 dfg
         2 fg
         2 g

8 rows selected.




ps
несколько напрягает DBMS_RANDOM.VALUE
....
stax
...
Рейтинг: 0 / 0
23.05.2013, 20:41
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270867
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
stax..Станислав Бабин,
несколько напрягает DBMS_RANDOM.VALUE


Посему надежней SYS_GUID().

SY.
...
Рейтинг: 0 / 0
23.05.2013, 20:56
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270897
stax..
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
stax..
Участник
SYstax..Станислав Бабин,
несколько напрягает DBMS_RANDOM.VALUE


Посему надежней SYS_GUID().

SY.

спасиб

я пользую/пользовал табличку "pivot" c 10000 записями

....
stax
...
Рейтинг: 0 / 0
23.05.2013, 21:36
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38270911
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Если производительность особо не напрягает:

Код: 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.
WITH t AS (
           SELECT 1 id ,',1234,567,89,0' str FROM dual union all
           SELECT 2 id ,',asdf,dfg,fg,g' str FROM dual
          )
select  id,
        s
  from  t,
        xmltable(
                 'ora:tokenize($s,",")'
                 passing ltrim(str,',') as "s"
                 columns
                   s varchar2(20) path '.'
                )
/

        ID S
---------- -----
         1 1234
         1 567
         1 89
         1 0
         2 asdf
         2 dfg
         2 fg
         2 g

8 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
23.05.2013, 21:48
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38271354
Станислав Бабин
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Станислав Бабин
Гость
SY,

Спасибо за помощь.
...
Рейтинг: 0 / 0
24.05.2013, 10:03
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38501982
Козурман И.А.
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Козурман И.А.
Гость
Станислав Бабин, Аналогичный пример с использованием рекурсивного запроса
--':p1 12 45 68'
with q1(wx,n) as
(select substr(:p1,1,INSTR(:p1,' ',1,1)-1) , 1 as n from dual
union all
select substr(:p1,INSTR(:p1,' ',1,n)+1,
case when INSTR(:p1,' ',1,n+1)>0 then
INSTR(:p1,' ',1,n+1)-1-INSTR(:p1,' ',1,n)
else
length(:p1)-INSTR(:p1,' ',1,n)
end
)
, n+1 as n from q1
where INSTR(:p1,' ',1,n)>0
)
select wx from q1
...
Рейтинг: 0 / 0
16.12.2013, 12:28
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Обратная функция по отношению к listagg
    #38984892
Фотография Ryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ryuu
Участник
Есть ли более изящное решение, с использованием xml, чтобы не было ошибки, в случае, если у нас нет разделителя и строчку в общем-то разбивать не нужно?
Код: 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.
WITH t1 AS
  ( SELECT 1 id ,'1234 567 89 0' str FROM dual
  UNION ALL
  SELECT 2 id ,'asdf dfg fg g' str FROM dual
  UNION ALL
  SELECT 3 id , 'gg' str FROM dual
  UNION ALL
  SELECT 4 id , NULL str FROM dual
  )
SELECT id,
  s
FROM
  (SELECT id,
    (
    CASE
      WHEN instr(str, ' ') = 0
      THEN '666 '
        ||str
      ELSE str
    END) AS str
  FROM t1
  ) t1,
  xmltable( 'ora:tokenize($s," ")' passing ltrim(str,' ') AS "s" columns s VARCHAR2(20) path '.' )
WHERE s!='666';
...
Рейтинг: 0 / 0
16.06.2015, 15:00
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38984934
Фотография -2-
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
-2-
Участник
RyuuЕсть ли более изящное решениенаписать/позаимствовать функцию.

Ryuuчтобы не было ошибки, в случае, если у нас нет разделителяпредконкатенация с разделителем или if match внутри xquery
...
Рейтинг: 0 / 0
16.06.2015, 15:36
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38984935
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Код: plsql
1.
select * from t1, xmltable(nvl(rtrim(regexp_replace(str||' ', '(\S+)\s+', '"\1",'), ', '), '1 to 0'))
...
Рейтинг: 0 / 0
16.06.2015, 15:36
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985440
Фотография Ryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ryuu
Участник
Elic, хмм, 12 оракл? У меня не работает, 11. Причем код ошибки не пишет.
...
Рейтинг: 0 / 0
17.06.2015, 10:04
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985451
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
Ryuu12 оракл? У меня не работает, 11 http://www.bugtraq.ru/forum/faq/general/smart-questions.html] RTFM
Работает как минимум начиная с 11.2.0.3.

P.S. В 11.2.0.2 XML-движок был коренным образом заменён.
...
Рейтинг: 0 / 0
17.06.2015, 10:13
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985452
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
Ryuu,

все работает на 11g. пробей тчательнее.
...
Рейтинг: 0 / 0
17.06.2015, 10:13
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985480
Фотография Ryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ryuu
Участник
х3, у меня Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production и выдает это
 

Видимо админы окончательно поломали базу... В любом случае, это я так, для общего развития, так что и бог с ним.
...
Рейтинг: 0 / 0
17.06.2015, 10:34
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985492
Фотография Ryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ryuu
Участник
Спасибо.
...
Рейтинг: 0 / 0
17.06.2015, 10:42
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985500
Добрый Э - Эх
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Добрый Э - Эх
Гость
RyuuВидимо админы окончательно поломали базу....для начала как минимум смотреть статус компонент:
Код: plsql
1.
2.
select comp_name, version, status, modified
from dba_registry


Дальше думать...
Если ставили патч, то компоненты должны соответствовать версии патча и иметь стату VALID
...
Рейтинг: 0 / 0
17.06.2015, 10:46
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985512
Фотография Ryuu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ryuu
Участник
У меня нет прав даже на просмотр данной таблицы. :)
...
Рейтинг: 0 / 0
17.06.2015, 10:55
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985842
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Elic
Код: plsql
1.
select * from t1, xmltable(nvl(rtrim(regexp_replace(str||' ', '(\S+)\s+', '"\1",'), ', '), '1 to 0'))



1. Возвращаемое XMLTABLE поле COLUMN_VALUE имеет тип XMLTYPE, так-что лучше XMLCAST(COLUMN_VALUE AS VARCHAR2(размер))
2. При условии str не содержит кавычек (одинарных и двойных), амперсандов и.т.д.

В общем случае что-то типа:

Код: plsql
1.
2.
3.
select t1.*,dbms_xmlgen.convert(xmlcast(column_value as varchar2(10)),1) column_value
 from t1, xmltable(nvl(rtrim(regexp_replace(dbms_xmlgen.convert(str)||' ', '(\S+)\s+', '"\1",'), ', '), '1 to 0')) x
/



SY.
...
Рейтинг: 0 / 0
17.06.2015, 14:26
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985861
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Ну и left correlation:

Код: 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.
WITH t1 AS
  ( SELECT 1 id ,'1234 567 89 0' str FROM dual
  UNION ALL
  SELECT 2 id ,'as&df dfg fg g' str FROM dual
  UNION ALL
  SELECT 3 id , 'gg' str FROM dual
  UNION ALL
  SELECT 4 id , NULL str FROM dual
  )
select t1.*,dbms_xmlgen.convert(xmlcast(column_value as varchar2(10)),1) column_value
 from t1, xmltable(nvl(rtrim(regexp_replace(dbms_xmlgen.convert(str)||' ', '(\S+)\s+', '"\1",'), ', '), '1 to 0')) x
/

        ID STR            COLUMN_VALUE
---------- -------------- ------------
         1 1234 567 89 0  1234
         1 1234 567 89 0  567
         1 1234 567 89 0  89
         1 1234 567 89 0  0
         2 as&df dfg fg g as&df
         2 as&df dfg fg g dfg
         2 as&df dfg fg g fg
         2 as&df dfg fg g g
         3 gg             gg

9 rows selected.

WITH t1 AS
  ( SELECT 1 id ,'1234 567 89 0' str FROM dual
  UNION ALL
  SELECT 2 id ,'as&df dfg fg g' str FROM dual
  UNION ALL
  SELECT 3 id , 'gg' str FROM dual
  UNION ALL
  SELECT 4 id , NULL str FROM dual
  )
select t1.*,dbms_xmlgen.convert(xmlcast(column_value as varchar2(10)),1) column_value
 from t1, 
xmltable(nvl(rtrim(regexp_replace(dbms_xmlgen.convert(str)||' ','(\S+)\s+','"\1",'),', '),'""'))(+) x -- ну и "" вместо 1 to 0 - буковок меньше
/

        ID STR            COLUMN_VALUE
---------- -------------- --------------
         1 1234 567 89 0  1234
         1 1234 567 89 0  567
         1 1234 567 89 0  89
         1 1234 567 89 0  0
         2 as&df dfg fg g as&df
         2 as&df dfg fg g dfg
         2 as&df dfg fg g fg
         2 as&df dfg fg g g
         3 gg             gg
         4

10 rows selected.



SY.
...
Рейтинг: 0 / 0
17.06.2015, 14:43
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985866
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SYВ общем случае что-то типа:Да баловство это всё. Более правильный путь - написать свою табличную функцию с прогнозируемым поведением.
...
Рейтинг: 0 / 0
17.06.2015, 14:46
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985876
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SYНу и left correlation:
Код: plsql
1.
(+)

Это лишнее.
...
Рейтинг: 0 / 0
17.06.2015, 14:51
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985905
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
ElicЭто лишнее.

А куда делась

SELECT 4 id , NULL str FROM dual

Тут или left correlation или

WHERE TRIM(str) IS NOT NULL

и NVL не нужен.

SY.
...
Рейтинг: 0 / 0
17.06.2015, 15:20
| Ответить | Цитировать | Написать  
Обратная функция по отношению к listagg
    #38985908
Фотография Elic
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Elic
Участник
SYА куда деласьСравни с 17777002
...
Рейтинг: 0 / 0
17.06.2015, 15:23
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
Обратная функция по отношению к listagg
    #39319101
sgibnev
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
sgibnev
Гость
--&str='4,5,6,2,3,4,5,6'
select regexp_substr(&str,'[^,]+',1,level) s from dual
connect by regexp_substr(&str,'[^,]+',1,level) is not null;
...
Рейтинг: 0 / 0
01.10.2016, 14:23
| Ответить | Цитировать | Написать  
31 сообщений из 31, показаны все 2 страниц
  все  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Обратная функция по отношению к listagg
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?all=1&fid=52&tid=1887343]:
 0ms
get settings:
 10ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 176ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 83ms
get tp. blocked users:
 2ms
others: 202ms
total:  509ms
созд. / загр.: 509ms
x
x
Закрыть


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