Volder
Member
Откуда: Москва
Сообщений: 471
|
| Бабичев Сергей | Покажи свой запрос для 10-ки.
|
вот такая зверюшка у меня получилась:
SQL> with t as (select '6*(7*5+2*2/(5*3)+6)+4' s from dual),
2 --finding back poland notation
3 t1 as (
4 select * from t
5 model
6 dimension by (0 d)
7 measures (s, cast(null as varchar2(100)) stek, cast(null as varchar2(20)) symb, cast(null as varchar2(100)) temp, cast(null as varchar2(100)) str)
8 rules iterate(1000) until (symb[0] is null)
9 (symb[0]=regexp_substr(s[0], '[[:digit:]]+|[*+-/()]',1,iteration_number+1),
10 temp[0] = case when symb[0] in ('+','-',')') then regexp_substr(stek[0],'[-+*/]*$')
11 when symb[0] in ('*','/') then regexp_substr(stek[0],'[*/]*$')
12 else null
13 end,
14 str[0] = case when symb[0]=regexp_substr(symb[0],'[[:digit:]]*') then str[0]||symb[0]||''''
15 when symb[0] in ('+','-','*','/',')') then str[0]||reverse(temp[0])
16 when symb[0] is null then str[0]||reverse(stek[0])
17 else str[0]
18 end,
19 stek[0] = case when symb[0] in ('+','-') then regexp_replace(stek[0],'[-+*/]*$')||symb[0]
20 when symb[0] in ('*','/') then regexp_replace(stek[0],'[*/]*$')||symb[0]
21 when symb[0] = '(' then stek[0]||symb[0]
22 when symb[0] = ')' then regexp_replace(stek[0],'\([-+*/]*$')
23 else stek[0]
24 end)
25 ),
26 t2 as (
27 select str, level rn, rtrim(regexp_substr(str, '[[:digit:]]*''|[-+*/]', 1, level), '''') s
28 from t1
29 connect by regexp_substr(str, '[[:digit:]]*''|[-+*/]', 1, level) is not null
30 ),
31 --building tree
32 t3 as ( select * from t2
33 model
34 dimension by (rn)
35 measures(rn rn_const, rn r, rn r2,s, nvl(length(regexp_replace(s,'[[:digit:]]')),0) is_const, nvl(length(regexp_replace(s,'[[:digit:]]')),0) is_, (select length(regexp_replace(s,'[^-*+/]')) from t) cnt, cast(null as number) parent_rn, 0 parent_temp)
36 rules iterate(100000) until (cnt[1]=0)
37 (parent_temp[ANY] = parent_rn[CV()],
38 parent_rn[ANY] order by rn = case when parent_rn[CV()] is null and is_[CV()]=0 and is_[r[CV()]+1]=0 and is_[r[CV()]+2]=1 then rn_const[r[CV()]+2]
39 when parent_rn[CV()] is null and is_[CV()]=0 and is_[r2[CV()-1]] = 0 and is_[r[CV()]+1]=1 then rn_const[r[CV()]+1]
40 else parent_rn[CV()]
41 end,
42 is_[ANY] order by rn = case when is_const[CV()]=1 and parent_rn[CV()-1] = rn_const[CV()] then 0 else is_[CV()] end,
43 r[ANY] order by rn desc = case when parent_rn[CV()+1] is not null then r[CV()+1] else r[CV()] end,
44 r2[ANY] order by rn = case when parent_rn[CV()] is not null then r2[CV()-1] else r2[CV()] end,
45 cnt[1] = cnt[1]-1
46 )
47 )
48 select rn id, parent_rn parent_id, lpad(s, level * 2, ' ') val, level
49 from t3
50 connect by prior rn = parent_rn
51 start with parent_rn is null
52 order siblings by s
53 /
ID PARENT_ID VAL LEVEL
---------- ---------- -------------------------------------------------------------------------------- ----------
17 + 1
15 17 * 2
14 15 + 3
12 14 + 4
4 12 * 5
3 4 5 6
2 4 7 6
11 12 / 5
7 11 * 6
5 7 2 7
6 7 2 7
10 11 * 6
9 10 3 7
8 10 5 7
13 14 6 4
1 15 6 3
16 17 4 2
17 rows selected
SQL> |
Пятничная задачка: дерево арифметического выражения [new]
Re: Пятничная задачка: дерево арифметического выражения [new]
Re: Пятничная задачка: дерево арифметического выражения [new]
Re: Пятничная задачка: дерево арифметического выражения [new]