데이터베이스 퀴즈 및 튜닝 문제
by 마농 sys_conect_by_path [2010.01.13 14:38:48]
[퀴즈] 계층구조쿼리의 응용 - 부호를 적용하여 하위계층 누적합계 구하기
코드(cd)와 부모코드(pcd)를 가진 테이블 test 가 있습니다.
이 테이블을 이용하여 계층구조의 쿼리를 작성하세요.
이때, 자기 자신을 포함한 하위 노드들의 누적합을 구해야 합니다.
누적합을 구할때는 최하위 노드로부터 주어진 부호를 적용시켜서 합산하고,
합산된 결과는 상위노드의 결과에 합산하면서 상위노드의 부호를 다시 적용시켜야 합니다.
계산방식은 아래 결과표를 참조하세요.
WITH test AS
(
SELECT 1000 cd, NULL pcd, 0 amt, '+' sign FROM dual
UNION ALL SELECT 1100, 1000, 100, '-' FROM dual
UNION ALL SELECT 1110, 1100, 200, '+' FROM dual
UNION ALL SELECT 1111, 1110, 50, '-' FROM dual
UNION ALL SELECT 1120, 1100, 100, '+' FROM dual
UNION ALL SELECT 1200, 1000, 150, '-' FROM dual
UNION ALL SELECT 1210, 1200, 300, '+' FROM dual
)
SELECT * FROM test;
CD PCD AMT SIGN CD LV AMT SIGN SUM_AMT + 1 + - 2 - + 3 + - 4 - + 3 + - 2 - + 3 +
[원본]
[결과]
계산방법 참조
1000
0
1000
0
-800
+(0 - 350 - 450) = -800
1100
1000
100
--1100
100
-350
-(100 + 150 + 100) = -350
1110
1100
200
----1110
200
150
+(200 - 50) = 150
1111
1110
50
------1111
50
-50
-(50) = -50
1120
1100
100
----1120
100
100
+(100) = 100
1200
1000
150
--1200
150
-450
-(150 + 300) = -450
1210
1200
300
----1210
300
300
+(300) = 300
정답은 몇일 뒤에 올리도록 하겠습니다.
[정답보기] <=== 트리플클릭
SELECT LPAD(cd,2+LEVEL*2,'-') cd
, LEVEL lv
, amt
, sign
, (SELECT SUM(DECODE(
MOD(LENGTH(REPLACE(SYS_CONNECT_BY_PATH(sign,','),'-')),2)
,0,1,-1) * amt)
FROM test
START WITH cd = main.cd
CONNECT BY PRIOR cd = pcd
) sum_amt
FROM test main
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
;
이번 퀴즈는 저번 퀴즈의 응용편인데요. 난이도가 너무 높았나 봅니다. 난이도 조절 실패~
저번 퀴즈 참조 : http://www.gurubee.net/article/26066
힌트좀 드릴께요.
하위 금액의 합계를 구하는 방식은 저번 퀴즈와 동일합니다.
다만 합계를 구할때 부호를 순차적으로 적용시켜야 하는 부분이 문제네요.
2레벨의 값은 2레벨의 부호와 직상위 1레벨의 부호를 두번 적용시켜서 합산해야 합니다.
3레벨의 값은 자신의 부호와 직상위 2, 1 레벨의 부호를 적용시켜야 하구요.
예를 들면 다음과 같습니다.
최상위 합은 각 하위노드 값들에 다음과 같이 부호를 적용시킨 결과라고 보셔도 되겠습니다.
+0 ===> 0
+-100 ===> -100
+-+200 ===> -200
+-+-50 ===> 50
+-+100 ===> -100
+-150 ===> -150
+-+300 ===> -300
결국 이수들을 합치면 -800이 되는거죠.
여기서 주목할 것은 +의 갯수는 부호 변화에 상관이 없구요
-의 갯수가 홀수개이면 음수가 되고 짝수개이면 다시 양수가 된다는 특징입니다.
이 성질을 잘 이용하시면 실마리를 찾으실 수 있을듯 하네요.
(
SELECT 1000 cd, NULL pcd, 0 amt, '+' sig FROM dual
UNION ALL SELECT 1100, 1000, 100, '-' FROM dual
UNION ALL SELECT 1110, 1100, 200, '+' FROM dual
UNION ALL SELECT 1111, 1110, 50, '-' FROM dual
UNION ALL SELECT 1120, 1100, 100, '+' FROM dual
UNION ALL SELECT 1200, 1000, 150, '-' FROM dual
UNION ALL SELECT 1210, 1200, 300, '+' FROM dual
)
SELECT cd ,
lv ,
amt ,
sig ,
Decode(pcd,NULL,Sum(amt2) over(PARTITION BY 1),Sum(amt2) over(PARTITION BY grp ORDER BY lv DESC)) sum_amt
FROM
(SELECT LPad(cd,Length(cd)+(LEVEL-1)*2,'-') cd,
pcd ,
amt ,
sig ,
LEVEL lv,
Decode(Mod(Length(translate(sys_connect_by_path(sig,'t'),'-t+ ','-')),2),1,amt * -1 , amt * 1) amt2,
LEVEL - ROWNUM grp ,
ROWNUM rn
FROM test
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd)
ORDER BY rn
위와 같이 풀어봤습니다.
퀴즈를 아무리 읽어봐도 어떻게 풀어야 할지 가닥을 못잡았었는데
힌트를 보니 길이 보이는 군요... 역시 아직 멀었나봅니다... ㅠ.ㅠ
정답과는 멀게 푼거 같아 가슴이 아프네요 ㅠ.ㅠ
각 노드별로 하위그룹들의 합을 구해야 하기 때문입니다.
4레벨의 코드는 3레벨에도 속해야 하고 2레벨에도 속해야 하고 1레벨에도 속해야 하며
각각의 그룹은 독립적으로 별개이므로 어떤 한그룹으로 표현할수 없습니다.
따라서 각각의 코드별로 스칼라서브쿼리로 구현해야 합니다.
하위코드의 합계를 구하는 방식은 저번 퀴즈 참조 링크를 통해 확인해 보세요.
(
SELECT 1000 cd, NULL pcd, 0 amt, '+' sig FROM dual
UNION ALL SELECT 1100, 1000, 100, '-' FROM dual
UNION ALL SELECT 1110, 1100, 200, '+' FROM dual
UNION ALL SELECT 1111, 1110, 50, '-' FROM dual
UNION ALL SELECT 1120, 1100, 100, '+' FROM dual
UNION ALL SELECT 1200, 1000, 150, '-' FROM dual
UNION ALL SELECT 1210, 1200, 300, '+' FROM dual
)
SELECT LPad(cd,Length(cd)+(LEVEL-1)*2,'-') cd,
LEVEL lv,
amt ,
sig ,
(SELECT Sum(amt*Decode(Mod(Length(translate(sys_connect_by_path(sig,'t'),'-t+ ','-')),2),1, -1 , 1))
FROM test
START WITH cd = a.cd
CONNECT BY prior cd = pcd)
-- Decode(Mod(Length(translate(sys_connect_by_path(sig,'t'),'-t+ ','-')),2),1,amt * -1 , amt * 1) amt2,
FROM test a
START WITH pcd IS NULL
CONNECT BY PRIOR cd = pcd
눈앞에 있는 결과물만 해결하느라
다른 생각을 못했네요 ㅠ.ㅠ
마농님과 서성우님은 나이를 떠난 사제지간같습니다.
부럽네요. 힌트를 보더라도 풀수있을지 없을지.
그래도 힌트보려가봐야겠습니다. ^^
) 버튼을 클릭하여 작성 하시면 됩니다.