|
저번에 recursive with으로 피보나치 수열 문제가 풀리는 것을 알게 되고 연립방정식도 되겠다고 생각을 했습니다.
그런데 연립방정식은 직전행만을 참조하지 않다는 것을 생각하고 안되겠다... 생각했었는데요...
갑자기 꽁수가 생각나서 해보니 풀렸습니다.
다음의 sql은 :rowcount에 미지수 갯수를 넣으면 가우스 조르단 법으로 연립방정식을 풉니다.
단 대각행열에 0이 없어야 합니다.
with 기초자료 as
(
SELECT 1 R, 1 C , 1 V FROM DUAL
UNION ALL SELECT 1 R, 2 C , 2 V FROM DUAL
UNION ALL SELECT 1 R, 3 C , 3 V FROM DUAL
UNION ALL SELECT 1 R, 4 C , 1 V FROM DUAL
UNION ALL SELECT 2 R, 1 C , 1 V FROM DUAL
UNION ALL SELECT 2 R, 2 C , 3 V FROM DUAL
UNION ALL SELECT 2 R, 3 C , 6 V FROM DUAL
UNION ALL SELECT 2 R, 4 C , 1 V FROM DUAL
UNION ALL SELECT 3 R, 1 C , 2 V FROM DUAL
UNION ALL SELECT 3 R, 2 C , 6 V FROM DUAL
UNION ALL SELECT 3 R, 3 C , 13 V FROM DUAL
UNION ALL SELECT 3 R, 4 C , 5 V FROM DUAL
)
, 기초자료_열병합_재귀 ( R , C, V , RN) AS
(
SELECT
A.R
, A.C
, TO_CHAR(A.V) V
, 0 RN
FROM 기초자료 A
UNION ALL
SELECT
A.R
, A.C
, TO_CHAR(A.V)||','||B.V V
, B.RN + 1
FROM 기초자료 A
, 기초자료_열병합_재귀 B
WHERE B.R = A.R
AND B.C = A.C + 1
)
, 기초자료_열병합 as
(
SELECT
a.r
,a.c
,','||a.v||',' V
FROM 기초자료_열병합_재귀 a
where a.rN = :rowcount
)
, 기초자료_행병합_재귀 (R,V,RN) AS
(
SELECT
R,V,0 RN
FROM 기초자료_열병합 A
UNION ALL
SELECT
A.R
,B.V || '|'||A.V
,B.RN + 1
FROM 기초자료_열병합 A
, 기초자료_행병합_재귀 B
WHERE A.R = B.R + 1
)
, 기초자료_행열병합 AS
(
SELECT
'|'||V V
FROM 기초자료_행병합_재귀 A
WHERE A.RN = :rowcount - 1
)
, 대각행열_재귀(itr,r,c,v,rn,vv) as
(
select
0 itr
,0 r
,0 c
,a.v
,0 rn
, to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,2) + 1,1) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,2) + 1,1+1) - INSTR(A.V,',',INSTR(A.V,'|',1,2) + 1,1) - 1 )) / to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,1) + 1,1) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,1) + 1,1+1) - INSTR(A.V,',',INSTR(A.V,'|',1,1) + 1,1) - 1 ))
from 기초자료_행열병합 a
union all
select
(floor((a.rn ) / ((:rowcount+1) * :rowcount)) + 1) itr
,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1) r
,(mod((a.rn ),(:rowcount+1)) + 1) c
, case when (floor((a.rn ) / ((:rowcount+1) * :rowcount)) + 1) = (mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1) then
a.v
else
SUBSTR(A.V,1,INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)))||
to_char(to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) - 1 ))
- to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) - 1 )) * a.vv) ||
SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor((a.rn ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)+1))
end v
,a.rn + 1 rn
, case when (mod((a.rn ),(:rowcount+1)) + 1) = :rowcount + 1 then
nvl(to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor(((a.rn+1) ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor(((a.rn+1) ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(mod((floor(((a.rn+1) ) / (:rowcount+1)) + 1) - 1 ,3) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) - 1 )) /
nullif(to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) + 1,(floor(((a.rn+1) ) / ((:rowcount+1) * :rowcount)) + 1)) - 1 )),0),0)
else
a.vv
end vv
from 대각행열_재귀 a
where a.rn < (:rowcount + 1 ) * :rowcount * :rowcount
)
, 대각행열 as
(
select
a.itr
,a.r
,a.c
,a.v
,a.rn
,a.vv
from 대각행열_재귀 a
where a.rn = (:rowcount + 1 ) * :rowcount * :rowcount
)
, 단위행열_재귀 (r,c,v,rn) as
(
select
0 r
,0 c
, a.v
,0 rn
from 대각행열 a
union all
select
(floor((a.rn ) / (:rowcount+1)) + 1) r
,(mod((a.rn ),(:rowcount+1)) + 1) c
,SUBSTR(A.V,1,INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1))) ||
to_char(to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)) - 1 )) /
to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(floor((a.rn ) / (:rowcount+1)) + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(floor((a.rn ) / (:rowcount+1)) + 1)) - 1 )))
||SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,(floor((a.rn ) / (:rowcount+1)) + 1)) + 1,(mod((a.rn ),(:rowcount+1)) + 1)+1)) v
,a.rn + 1
from 단위행열_재귀 a
where a.rn < (:rowcount + 1 ) * :rowcount
)
, 단위행열 as
(
select
a.v
from 단위행열_재귀 a
where a.rn = (:rowcount + 1 ) * :rowcount
)
select
b.rn 미지수번호
, to_number(SUBSTR(A.V,INSTR(A.V,',',INSTR(A.V,'|',1,b.rn) + 1,(:rowcount + 1)) + 1 , INSTR(A.V,',',INSTR(A.V,'|',1,b.rn) + 1,(:rowcount + 1)+1) - INSTR(A.V,',',INSTR(A.V,'|',1,b.rn) + 1,(:rowcount + 1)) - 1 )) 미지수값
from 단위행열 a
, ( select level rn from dual connect by level < :rowcount + 1 ) b
/
재미삼아 보시는 것도 좋으시리라...
감사합니다^^ |
