Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите составить рекурсивный запрос / 7 сообщений из 7, страница 1 из 1
08.02.2021, 19:35
    #40043462
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Рекурсивные запросы использовать приходится редко, поэтому многие нюансы я забываю или упускаю из вида.
Помогите найти, что именно сейчас я сделал не так?
Задача простая — разделить строку по разделителю (пробелу).
В простейшем варианте такой пример дает вроде бы правильные результаты:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
with tmp as (select '1 2 3' str from dual union all select '4' from dual union all select '5 6' from dual)
select distinct str
, regexp_substr(str, '\S+', 1, level) re
, level
from tmp
connect by instr(str, ' ', 1, level - 1) > 0
order by 1, 2


Рабочий пример несколько более сложный:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
with NL as
(
  select 0 as Y, '' as IP from DUAL
  union all select 2020, '10.1.14.69' from dual
  union all select 2020, '10.1.12.102 10.1.12.146 10.1.12.195' from dual
...
  union all select 2021, '10.1.11.158 10.1.10.54 10.1.10.94 10.1.11.179 10.1.12.104 10.1.10.59 10.1.10.15 10.1.11.115 10.1.13.95 10.1.11.149 10.1.12.113 10.1.13.172 10.1.14.173' from dual
  union all select 2021, '10.1.14.199' from dual
)
, LST as (select Y, row_number() over(partition by Y order by rownum) as RN, IP from NL where Y > 0)
select distinct LST.Y, LST.RN
, regexp_substr(LST.IP, '\S+', 1, level) as IP
, LST.IP as STR
from LST
connect by instr(LST.IP, ' ', 1, level - 1) > 0
order by 1, 2, 3, 4


В принципе этот запрос так же отрабатывает корректно.
Но если попробовать применить его к рабочему набору данных (около сотни строк, в которых может быть до пары десятков подстрок), как запрос "зависает" (за несколько минут не завершается).
 

Код: 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.
71.
72.
73.
74.
with NL as
(
  select 0 as Y, '' as IP from DUAL
  union all select 2020, '10.1.14.69' from dual
  union all select 2020, '10.1.12.102' from dual
  union all select 2020, '10.1.12.102 10.1.12.146 10.1.12.195' from dual
  union all select 2020, '10.1.11.155 10.1.11.124 10.1.11.134 10.1.11.135 10.1.12.161 10.1.14.100 10.1.11.177 10.1.12.112 10.1.10.78 10.1.10.9 10.1.12.186 10.1.13.103 10.1.14.193 10.1.13.8 10.1.10.104' from dual
  union all select 2020, '10.1.10.66 10.1.10.96 10.1.12.107 10.1.13.47 10.1.14.31 10.1.10.64 10.1.13.97 10.1.10.106 10.1.11.128 10.1.10.97 10.1.12.183 10.1.10.63 10.1.12.180 10.1.12.150 10.1.11.183 10.1.13.132 10.1.14.151 10.1.14.153 10.1.14.80 10.1.14.112 10.1.14.158 10.1.10.74' from dual
  union all select 2020, '10.1.10.44' from dual
  union all select 2020, '10.1.13.48 10.1.14.41 10.1.13.169 10.1.14.95 10.1.12.55 10.1.12.147 10.1.14.77 10.1.13.171' from dual
  union all select 2020, '10.1.11.192' from dual
  union all select 2020, '10.1.12.79' from dual
  union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56' from dual
  union all select 2020, '10.1.10.125 10.1.14.65 10.1.14.92 10.1.14.88 10.1.14.13 10.1.13.151 10.1.14.109 10.1.13.152' from dual
  union all select 2020, '10.1.10.74' from dual
  union all select 2020, '10.1.10.20 10.1.14.181' from dual
  union all select 2020, '10.1.10.80' from dual
  union all select 2020, '10.1.12.86' from dual
  union all select 2020, '10.1.14.39' from dual
  union all select 2020, '10.1.10.6 10.1.11.155 10.1.11.124 10.1.13.159 10.1.14.166 10.1.13.142 10.1.12.152 10.1.11.135 10.1.12.193 10.1.11.191 10.1.12.192 10.1.13.181 10.1.14.45 10.1.11.116' from dual
  union all select 2020, '10.1.10.74 10.1.10.66 10.1.13.47 10.1.14.31 10.1.10.64 10.1.13.97 10.1.11.128 10.1.10.97 10.1.12.183 10.1.11.183' from dual
  union all select 2020, '10.1.10.106 10.1.12.180 10.1.12.150 10.1.12.107 10.1.14.80' from dual
  union all select 2020, '10.1.10.96 10.1.14.151 10.1.14.153 10.1.14.112 10.1.14.158 10.1.10.63 10.1.13.132' from dual
  union all select 2020, '10.1.14.101' from dual
  union all select 2020, '10.1.11.168 10.1.12.71' from dual
  union all select 2020, '10.1.12.79 10.1.14.60 10.1.13.102' from dual
  union all select 2020, '10.1.14.145' from dual
  union all select 2020, '10.1.13.35' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.10.17 10.1.10.98 10.1.13.177 10.1.13.105 10.1.14.11 10.1.11.163 10.1.13.187' from dual
  union all select 2020, '10.1.10.17 10.1.10.98 10.1.13.177 10.1.13.105 10.1.14.11 10.1.11.163 10.1.13.187' from dual
  union all select 2020, '10.1.11.169' from dual
  union all select 2020, '10.1.11.138' from dual
  union all select 2020, '10.1.14.172' from dual
  union all select 2020, '10.1.10.93 10.1.12.118 10.1.11.117 10.1.14.25 10.1.10.88' from dual
  union all select 2020, '10.1.14.99' from dual
  union all select 2020, '10.1.13.9 10.1.13.170 10.1.14.182 10.1.13.18 10.1.14.117 10.1.13.88 10.1.14.127' from dual
  union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56 10.1.12.79' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.12.124 10.1.14.51' from dual
  union all select 2020, '10.1.14.60 10.1.13.102 10.1.14.56' from dual
  union all select 2020, '10.1.12.79' from dual
  union all select 2020, '10.1.14.97' from dual
  union all select 2020, '10.1.11.148' from dual
  union all select 2020, '10.1.14.67' from dual
  union all select 2021, '10.1.10.65 10.1.13.93 10.1.13.81 10.1.10.192 10.1.11.171' from dual
  union all select 2021, '10.1.11.158 10.1.10.54 10.1.10.94 10.1.11.179 10.1.12.104 10.1.10.59 10.1.10.15 10.1.11.115 10.1.13.95 10.1.11.149 10.1.12.113 10.1.13.172 10.1.14.173' from dual
  union all select 2021, '10.1.14.199' from dual
  union all select 2021, '10.1.14.82' from dual
  union all select 2021, '10.1.11.198' from dual
  union all select 2021, '10.1.14.112 10.1.12.183' from dual
  union all select 2021, '10.1.10.34 10.1.12.82 10.1.11.127 10.1.13.185 10.1.11.181 10.1.14.40 10.1.14.174 10.1.12.134 10.1.14.169 10.1.14.159 10.1.14.78' from dual
  union all select 2021, '10.1.14.30 10.1.13.119 10.1.11.68 10.1.11.145 10.1.10.70 10.1.12.139' from dual
  union all select 2021, '10.1.10.60' from dual
  union all select 2021, '10.1.10.34 10.1.11.68 10.1.12.82 10.1.11.127 10.1.13.185 10.1.14.30 10.1.10.9 10.1.10.64' from dual
  union all select 2021, '10.1.11.153 10.1.13.98 10.1.14.62' from dual
  union all select 2021, '10.1.14.45 10.1.13.142 10.1.14.100 10.1.13.143 10.1.12.193 10.1.11.191 10.1.13.6 10.1.12.192 10.1.14.71 10.1.11.199 10.1.13.45 10.1.13.103 10.1.11.116 10.1.13.5 10.1.13.159 10.1.11.177' from dual
  union all select 2021, '10.1.14.92 10.1.14.88 10.1.13.151 10.1.13.152' from dual
  union all select 2021, '10.1.13.38' from dual
  union all select 2021, '10.1.10.74 10.1.13.47 10.1.10.97 10.1.12.183' from dual
  union all select 2021, '10.1.12.71' from dual
  union all select 2021, '10.1.14.83 10.1.14.70 10.1.13.77 10.1.14.6 10.1.11.168 10.1.14.15 10.1.14.18 10.1.14.178 10.1.13.188 10.1.13.28 10.1.13.35 10.1.10.21 10.1.14.86 10.1.12.71 10.1.14.20 10.1.11.73 10.1.12.136 10.1.14.19 10.1.14.111 10.1.14.152 10.1.12.110 10.1.10.43' from dual
  union all select 2021, '10.1.10.74 10.1.12.150 10.1.13.47 10.1.10.97 10.1.12.183' from dual
  union all select 2021, '10.1.13.26' from dual
  union all select 2021, '10.1.14.83 10.1.14.70 10.1.13.77 10.1.14.6 10.1.11.168 10.1.14.15 10.1.14.18 10.1.14.178 10.1.13.188 10.1.13.28 10.1.13.35 10.1.10.21 10.1.14.86 10.1.12.71 10.1.14.20 10.1.11.73 10.1.12.136 10.1.14.19 10.1.14.111 10.1.14.152 10.1.12.110 10.1.10.43' from dual
  union all select 2021, '10.1.10.128' from dual
  union all select 2021, '10.1.14.151 10.1.14.158' from dual
  union all select 2021, '10.1.14.109 10.1.13.152 10.1.13.151 10.1.14.88 10.1.14.92' from dual
  union all select 2021, '10.1.12.183 10.1.10.97 10.1.13.47 10.1.10.74' from dual
  union all select 2021, '10.1.11.142' from dual
)




Подозреваю, что причина в том, что я неправильно описал условия соединения, не указав Y и RN.
Но не соображу, как их указывать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
08.02.2021, 21:33
    #40043477
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Не нужна тут рекурсия: LATERAL/CROSS APPLY.

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2021, 09:53
    #40043539
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Моя ошибка, все время забываю уточнять версию Oracle 10g.
Попутно я нашел решения через ODCI*List или XML, но они какие-то избыточные и сложные.
connect by и instr выглядят более оптимальными.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2021, 11:10
    #40043563
Stax
Stax
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Alibek B.
Моя ошибка,

Код: 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.
SQL> ed
Wrote file afiedt.buf

  1  with NL as
  2  (
  3    select 0 as Y, '' as IP from DUAL
  4    union all select 2020, '10.1.14.69' from dual
  5    union all select 2020, '10.1.12.102 10.1.12.146 10.1.12.195' from dual
  6  --  union all select 2021, '10.1.11.158 10.1.10.54 10.1.10.94 10.1.11.179 10.1.12.104 10.1.10.59 10.1.10.15 10.1.11.115 10.1.13.95 10.1.11.149 10.1.12.113 10.1.13.172 10.1.14.173' from dual
  7    union all select 2021, '10.1.14.199 xxx' from dual
  8  )
  9  , LST as (select Y, row_number() over(partition by Y order by rownum) as RN, IP from NL where Y > 0)
 10  select
 11   LST.Y,
 12   LST.RN
 13   ,regexp_substr(LST.IP, '\S+', 1, level) as IP
 14   ,LST.IP as STR
 15   ,level l
 16  from LST
 17  connect by instr(LST.IP, ' ', 1, level - 1) > 0
 18      and prior y = y
 19      and prior rn = rn
 20      and prior dbms_random.value is not null --sys_guid
 21* order by 1, 2, 5
SQL> /

         Y         RN IP                                  STR                                          L
---------- ---------- ----------------------------------- ----------------------------------- ----------
      2020          1 10.1.14.69                          10.1.14.69                                   1
      2020          2 10.1.12.102                         10.1.12.102 10.1.12.146 10.1.12.195          1
      2020          2 10.1.12.146                         10.1.12.102 10.1.12.146 10.1.12.195          2
      2020          2 10.1.12.195                         10.1.12.102 10.1.12.146 10.1.12.195          3
      2021          1 10.1.14.199                         10.1.14.199 xxx                              1
      2021          1 xxx                                 10.1.14.199 xxx                              2

6 rows selected.

SQL>



.....
stax
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2021, 11:54
    #40043580
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Спасибо. Строки 18 и 19 я и сам догадался добавить, но у меня получалась зацикленность.
А про трюк с dbms_random.value я не догадался.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2021, 11:58
    #40043585
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Финальный рабочий запрос (исполняется доли секунды):
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
with NL as
(
  select 0 as Y, '' as IP from DUAL
...
)
, LST as (select Y, row_number() over(partition by Y order by rownum) as RN, IP||' ' as IP from NL where Y > 0)
select LST.Y, LST.RN
, substr(LST.IP
  , decode(level, 1, 1, instr(LST.IP, ' ', 1, level - 1) + 1)
  , instr(LST.IP, ' ', 1, level) - decode(level, 1, 1, instr(LST.IP, ' ', 1, level - 1) + 1)
  ) as IP
, LST.IP as STR
from LST
connect by nvl(instr(LST.IP, ' ', 1, level), 0) > 0
and prior y = y
and prior rn = rn
and prior dbms_random.value is not null


Добавить финальный пробел к строке оказалось проще и быстрее, чем учитывать единственное/последнее значение.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
09.02.2021, 18:35
    #40043786
andreymx
andreymx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Помогите составить рекурсивный запрос
Один раз сделал ьабличную функцию стрингТуТейбл
У нас все пользуются уже 17 лет
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Помогите составить рекурсивный запрос / 7 сообщений из 7, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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