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

Откуда:
Сообщений: 90
Путь у нас есть таблица
create table t1 (id integer, text varchar2(4000))
В ней id - это первичный ключ, а text - строка переменной длины, имеющая следующий вид: 21-32-43-54-34 (произвольные двузначные числа, идущие через чёрточку; количество двузначных чисел не постоянное, а отличается для разных id).
Требуется написать один sql запрос (без использования PL/SQL), который вернул бы следующее:

id | sub_text
1  |  21
1  |  32
1  |  43
1  |  54
1  |  34
2  |  11
2  |  37
...

Т. е. для каждого айдишника надо вернуть все соответствующие ему двузначные числа, только вывести их в виде отдельных строк.

Если в t1 всего одна строка, то с задачей прекрасно справляется следующий запрос:
SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  FROM T1
CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL;

Если же строк несколько, то этот запрос, естественно, не пройдёт, нужно как-то сделать так, чтобы "иерархия" строилась по каждой строке t1 отдельно. Для этого можно было бы дописать в connect by что-то типа prior id = id, но добавление этого условия сразу вызывает ошибку ORA-01436 (CONNECT BY loop in user data).

К слову, я заметил, что если в условии connect by есть слово prior, то строка не может являться ребенком самой себя (или внуком и т. д.), будет возникать ошибка 1436. А если в connect by нет слова prior, то строки становятся родителями и потомками самих себя вполне нормально, без ошибок (как в запросе
select sysdate+level from dual connect by level < 100
, здесь единственная строка таблицы dual без всяких ошибок стала родителем и потомком от самой себя)
Если описанное выше верно, то, похоже, мою задачу вообще невозможно решить чистым sql запросом. :( Было бы обидно!
17 июн 11, 22:55    [10832165]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ASFK,

вы почти всё решение и описали. а ошибку зацикливания преодолевает, например
and prior dbms_random.value is not null
17 июн 11, 23:28    [10832258]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
ORA__SQL
Member

Откуда: Moscow
Сообщений: 1774
ASFK,
WITH t AS (
SELECT 1 ID, '21-32' text FROM dual
UNION ALL
SELECT 2 ID, '34-77-12' text FROM dual
UNION ALL
SELECT 3 ID, '22-74-12-57' text FROM dual
),
t1 AS (
SELECT ID, text, length(TRANSLATE(text,'-01234567890','-')) n
FROM t)
SELECT ID,
       text,
       LEVEL,
       CASE
       WHEN LEVEL=1 
       THEN SUBSTR(text, 1, 2)
       ELSE SUBSTR(text, INSTR(text,'-',1,LEVEL-1)+1, 2)
       END val
from t1
where n>0
connect by level<=n+1 
AND prior n=n
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL
ORDER BY ID, LEVEL
17 июн 11, 23:36    [10832296]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
AmKad
Member

Откуда:
Сообщений: 5222
+
with s as (
select 1 id, '21-32-43-54-34' str from dual union all
select 2 id, '10-20-20-40'    str from dual 
)
select id, c.column_value
from s,
table(select collect(regexp_substr(str, '\d{2}', 1, level)) 
      from dual
      connect by regexp_substr(str, '\d{2}', 1, level) is not null
     ) c;

        ID COLUMN_VALUE  
---------- --------------
         1 21            
         1 32            
         1 43            
         1 54            
         1 34            
         2 10            
         2 20            
         2 20            
         2 40            

9 rows selected.
17 июн 11, 23:41    [10832328]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
ASFK
Member

Откуда:
Сообщений: 90
orawish,

По поводу условия
and prior dbms_random.value is not null

Если я перепишу запрос так:
SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  FROM T1
CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
AND PRIOR ID = ID
AND PRIOR dbms_random.value IS NOT NULL;

то выдастся та же ошибка. Что неправильно?
18 июн 11, 00:11    [10832470]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ASFK
orawish,

По поводу условия
and prior dbms_random.value is not null

Если я перепишу запрос так:
SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  FROM T1
CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
AND PRIOR ID = ID
AND PRIOR dbms_random.value IS NOT NULL;

то выдастся та же ошибка. Что неправильно?

не надо гадать. таки попробуйте
+
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> WITH t1 AS (
  2  SELECT 1 ID, '21-32' text FROM dual
  3  UNION ALL
  4  SELECT 2 ID, '34-77-12' text FROM dual
  5  UNION ALL
  6  SELECT 3 ID, '22-74-12-57' text FROM dual
  7  )SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  8    FROM T1
  9  CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
 10  AND PRIOR ID = ID
 11  AND PRIOR dbms_random.value IS NOT NULL;

        ID REGEXP_SUBS
---------- -----------
         1 21
         1 32
         2 34
         2 77
         2 12
         3 22
         3 74
         3 12
         3 57

9 rows selected.
18 июн 11, 02:02    [10832917]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
ASFK
Member

Откуда:
Сообщений: 90
orawish
не надо гадать. таки попробуйте


Так я ж и попробовал, зачем бы я стал писать про ошибку, не попробовав. :)
Странно - с with блоком выполняется, а с таблицей нет:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> WITH T1 AS
  2   (SELECT 1 ID, '21-32' TEXT
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 2 ID, '34-77-12' TEXT
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 3 ID, '22-74-12-57' TEXT FROM DUAL)
  9  SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
 10    FROM T1
 11  CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
 12         AND PRIOR ID = ID
 13         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

        ID REGEXP_SUBS
---------- -----------
         1 21
         1 32
         2 34
         2 77
         2 12
         3 22
         3 74
         3 12
         3 57

9 rows selected.

SQL> CREATE TABLE T1 AS
  2    SELECT 1 ID, '21-32' TEXT
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 2 ID, '34-77-12' TEXT
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 3 ID, '22-74-12-57' TEXT FROM DUAL;

Table created.

SQL> SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  2    FROM T1
  3  CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
  4         AND PRIOR ID = ID
  5         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
ERROR:
ORA-01436: CONNECT BY loop in user data

no rows selected
18 июн 11, 10:55    [10833198]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
ASFK
Member

Откуда:
Сообщений: 90
ORA__SQL,

Ваш запрос тоже выполняется с блоком with, но не выполняется с таблицей, + концептуально он такой же, как предложил orawish.

AmKad,

Ваш запрос работает и с таблицами, правда, я пока незнаком с подобными конструкциями и сейчас не понимаю вообще, как это работает, буду разбираться. :)
Хотя всё равно хотелось бы узнать, можно ли переписать на чисто иерархический запрос.
18 июн 11, 11:00    [10833206]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
ASFK
ORA__SQL,

Ваш запрос тоже выполняется с блоком with, но не выполняется с таблицей, + концептуально он такой же, как предложил orawish.

AmKad,

Ваш запрос работает и с таблицами, правда, я пока незнаком с подобными конструкциями и сейчас не понимаю вообще, как это работает, буду разбираться. :)
Хотя всё равно хотелось бы узнать, можно ли переписать на чисто иерархический запрос.

про это - очевидно вам план надо смотреть. +, как вариант, привести его к аналогичному примеру виду.
1) with из таблицы, с материализацией
2)само древо
18 июн 11, 12:30    [10833389]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
stax..
Guest
ASFK
ORA__SQL,

Ваш запрос тоже выполняется с блоком with, но не выполняется с таблицей, + концептуально он такой же, как предложил orawish.

AmKad,

Ваш запрос работает и с таблицами, правда, я пока незнаком с подобными конструкциями и сейчас не понимаю вообще, как это работает, буду разбираться. :)
Хотя всё равно хотелось бы узнать, можно ли переписать на чисто иерархический запрос.


мож от версии оракля зависит
SQL> select * from t1;

        ID TEXT
---------- -----------
         1 21-32
         2 34-77-12
         3 22-74-12-57

SQL> select
  2    id
  3   ,substr(trim(text), instr(' '||text, '-', 1, level),
  4                instr('-'||text||'-', '-', 1, level+1)-instr('-'||text, '-', 1, level)-1) w
  5  from
  6   t1
  7  connect by --NOCYCLE
  8           id=prior id
  9    and level <= length(trim(text)) - length(trim(replace(text, '-'))) + 1
 10    and prior DBMS_RANDOM.VALUE IS NOT NULL
 11  /

        ID W
---------- -----------
         1 32
         1 21
         2 77
         2 12
         2 34
         3 74
         3 12
         3 57
         3 22

9 rows selected.

......
stax
18 июн 11, 12:33    [10833401]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
stax..
Guest
ASFK
orawish
не надо гадать. таки попробуйте


Так я ж и попробовал, зачем бы я стал писать про ошибку, не попробовав. :)
Странно - с with блоком выполняется, а с таблицей нет:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> WITH T1 AS
  2   (SELECT 1 ID, '21-32' TEXT
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 2 ID, '34-77-12' TEXT
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 3 ID, '22-74-12-57' TEXT FROM DUAL)
  9  SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
 10    FROM T1
 11  CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
 12         AND PRIOR ID = ID
 13         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

        ID REGEXP_SUBS
---------- -----------
         1 21
         1 32
         2 34
         2 77
         2 12
         3 22
         3 74
         3 12
         3 57

9 rows selected.

SQL> CREATE TABLE T1 AS
  2    SELECT 1 ID, '21-32' TEXT
  3      FROM DUAL
  4    UNION ALL
  5    SELECT 2 ID, '34-77-12' TEXT
  6      FROM DUAL
  7    UNION ALL
  8    SELECT 3 ID, '22-74-12-57' TEXT FROM DUAL;

Table created.

SQL> SELECT ID, REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL)
  2    FROM T1
  3  CONNECT BY REGEXP_SUBSTR(TEXT, '\d\d', 1, LEVEL) IS NOT NULL
  4         AND PRIOR ID = ID
  5         AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;
ERROR:
ORA-01436: CONNECT BY loop in user data
no rows selected

імхо
чисто случайно DBMS_RANDOM.VALUE может вернуть одинаковые значения тогда тоже будет ORA-01436: CONNECT BY loop in user data

.....
stax
18 июн 11, 12:47    [10833465]     Ответить | Цитировать Сообщить модератору
 Re: Как с помощью SQL разложить данные из столбца по нескольким строкам?  [new]
ASFK
Member

Откуда:
Сообщений: 90
Оказалось, что действительно зависит от версии Oracle (у меня 10.2.0.4). Даже такой запрос:
select level from dual connect by level < 5 and prior dummy = dummy and prior dbms_random.value is not null
на моей базе не работает, а работает на многих друих версиях.
Исправить ситуацию помогает хинт RULE или хинт NO_QUERY_TRANSFORMATION (второй предпочтительнее, естественно).
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select level from dual connect by level < 5
  2  and prior dummy = dummy and prior dbms_random.value is not null;
ERROR:
ORA-01436: CONNECT BY loop in user data



no rows selected

SQL> select /*+rule*/ level from dual connect by level < 5
  2  and prior dummy = dummy and prior dbms_random.value is not null;

     LEVEL
----------
         1
         2
         3
         4

SQL> select /*+no_query_transformation*/ level from dual connect by level < 5
  2  and prior dummy = dummy and prior dbms_random.value is not null;

     LEVEL
----------
         1
         2
         3
         4
19 июн 11, 12:22    [10836175]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить