데이터베이스 퀴즈 및 튜닝 문제

[퀴즈]서로 짝을 이루는 자료 제외 1 17 4,325

by 마농 퀴즈 [2009.05.07 16:16:00]

다음과 같이 코드별로 순차적으로 금액값이 저장되는 데이터가 있습니다.
[자료]
WITH t AS
(
SELECT 'A' cd, 1 seq,  100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, -200 FROM dual
UNION ALL SELECT 'A', 4,  300 FROM dual
UNION ALL SELECT 'A', 5,  200 FROM dual
UNION ALL SELECT 'A', 6,  300 FROM dual
UNION ALL SELECT 'A', 7, -300 FROM dual
UNION ALL SELECT 'B', 1,  100 FROM dual
UNION ALL SELECT 'B', 2,  200 FROM dual
UNION ALL SELECT 'B', 3,  300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)

cd

seq

amt

A

1

100

A

2

-100

A

3

-200

A

4

300

A

5

200

A

6

300

A

7

-300

B

1

100

B

2

200

B

3

300

B

4

-200

B

5

-200

B

6

-100
[문제] 이 자료를 이용하여 순차적으로 들어온 자료중 서로 짝을 이루어 합계가 0이 되는 자료
즉, 색칠한 부분의 자료를 제외하고 조회하는 쿼리를 작성해 보세요.
색칠하지 않은 3건의 자료가 출력되어야 합니다.

[정답] <== 트리플클릭
SELECT cd, seq, amt
  FROM (SELECT cd, seq, amt
             , SUM(amt) OVER(PARTITION BY cd, ABS(amt), rn) ss
          FROM (SELECT cd, seq, amt
                     , ROW_NUMBER() OVER(PARTITION BY cd, amt ORDER BY seq) rn
                  FROM t
                )
        )
 WHERE ss != 0
 ORDER BY cd, seq
;

by 마농 [2009.05.07 17:36:50]
퀴즈에 응모해주셔서 감사합니다.
다음 자료로 테스트해보면 제대로 안나오네요.
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, 100 FROM dual
UNION ALL SELECT 'A', 3, -100 FROM dual
UNION ALL SELECT 'A', 4, -200 FROM dual
UNION ALL SELECT 'A', 5, 300 FROM dual
UNION ALL SELECT 'A', 6, 200 FROM dual
UNION ALL SELECT 'A', 7, 300 FROM dual
UNION ALL SELECT 'A', 8, -300 FROM dual
by 구경서 [2009.05.07 17:38:25]
넹 지금 발견해서 수정하고있어요 ㅎㅎ ㅡ_ㅜ 쿨럭
by 서성우 [2009.05.07 17:46:46]
WITH test AS
(
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, -200 FROM dual
UNION ALL SELECT 'A', 4, 300 FROM dual
UNION ALL SELECT 'A', 5, 200 FROM dual
UNION ALL SELECT 'A', 6, 300 FROM dual
UNION ALL SELECT 'A', 7, 300 FROM dual
UNION ALL SELECT 'A', 8, -300 FROM dual
UNION ALL SELECT 'B', 1, 100 FROM dual
UNION ALL SELECT 'B', 2, 200 FROM dual
UNION ALL SELECT 'B', 3, 300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)
SELECT cd,seq,amt FROM
(SELECT cd ,
seq ,
amt ,
Count(*) over(PARTITION BY ROWNUM-cnt) cnt,
cnt2
FROM
(SELECT cd , seq , amt,Abs(amt)
,Count(*) over(PARTITION BY cd,Abs(amt) ORDER BY amt,seq) cnt
,Row_Number() over(PARTITION BY cd,amt ORDER BY amt,seq) cnt2
FROM test)
)
WHERE (cnt=1 OR cnt2 != 1)
by 구경서 [2009.05.07 17:51:46]
수정입니다.

WITH t AS
(
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, -200 FROM dual
UNION ALL SELECT 'A', 4, 300 FROM dual
UNION ALL SELECT 'A', 5, 200 FROM dual
UNION ALL SELECT 'A', 6, 300 FROM dual
UNION ALL SELECT 'A', 7, -300 FROM dual
UNION ALL SELECT 'B', 1, 100 FROM dual
UNION ALL SELECT 'B', 2, 200 FROM dual
UNION ALL SELECT 'B', 3, 300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)
SELECT CD ,SEQ,AMT
FROM (
SELECT cd ,seq, amt,rn ,
sum(AMT) OVER(PARTITION BY cd, ABS(AMT) ORDER BY CD,ABS(AMT),RN ) CK
FROM(
SELECt CD ,SEQ,AMT ,
ROW_NUMBER() OVER(PARTITION BY cd , AMT ORDER BY CD ,SEQ ) rn
FROM t
)
)
WHERE CK <> 0
ORDER BY 1,2
by 마농 [2009.05.07 17:54:42]
서성우님도 오류가 있네요.
다음 자료로 테스트해보세요.
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, -100 FROM dual
UNION ALL SELECT 'A', 5, 100 FROM dual
by 마농 [2009.05.07 18:13:07]
구경서님 자료가 정확하게 나오네요.
잘 푸셨습니다. 그러나 불필요한 부분이 보이네요.
partition 절에 온 항목이 order by절에 다시 나올 필요는 없습니다.
by 서성우 [2009.05.07 18:16:10]
WITH test AS
(
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, -100 FROM dual
UNION ALL SELECT 'A', 5, 100 FROM dual
UNION ALL SELECT 'B', 1, 100 FROM dual
UNION ALL SELECT 'B', 2, 200 FROM dual
UNION ALL SELECT 'B', 3, 300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)
SELECT cd,seq,amt FROM
(SELECT cd ,
seq ,
amt ,
Count(*) over(PARTITION BY ROWNUM-cnt) cnt,
Count(*) over(PARTITION BY cd,cnt2) cnt2
FROM
(SELECT cd , seq , amt,Abs(amt)
,Count(*) over(PARTITION BY cd,Abs(amt) ORDER BY amt,seq) cnt
,Row_Number() over(PARTITION BY cd,amt ORDER BY amt,seq) cnt2
FROM test)
)
WHERE (cnt=1 OR cnt2 = 1)
by 마농 [2009.05.07 18:24:38]
서성우님 아직도 안되네요.
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, -100 FROM dual
UNION ALL SELECT 'A', 5, -200 FROM dual
UNION ALL SELECT 'A', 6, 300 FROM dual
UNION ALL SELECT 'A', 7, 200 FROM dual
UNION ALL SELECT 'A', 8, 300 FROM dual
UNION ALL SELECT 'A', 9, -300 FROM dual
by 서성우 [2009.05.07 18:31:51]
마지막으로...
한가지 간과 했던 부분이있었네요 ㅎㅎ

WITH test AS
(
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, 100 FROM dual
UNION ALL SELECT 'A', 4, -100 FROM dual
UNION ALL SELECT 'A', 5, -200 FROM dual
UNION ALL SELECT 'A', 6, 300 FROM dual
UNION ALL SELECT 'A', 7, 200 FROM dual
UNION ALL SELECT 'A', 8, 300 FROM dual
UNION ALL SELECT 'A', 9, -300 FROM dual
UNION ALL SELECT 'B', 1, 100 FROM dual
UNION ALL SELECT 'B', 2, 200 FROM dual
UNION ALL SELECT 'B', 3, 300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)
SELECT cd,seq,amt FROM
(SELECT cd ,
seq ,
amt ,
Count(*) over(PARTITION BY ROWNUM-cnt) cnt,
Count(*) over(PARTITION BY cd,cnt2,Abs(amt)) cnt2
FROM
(SELECT cd , seq , amt,Abs(amt)
,Count(*) over(PARTITION BY cd,Abs(amt) ORDER BY amt,seq) cnt
,Row_Number() over(PARTITION BY cd,amt ORDER BY amt,seq) cnt2
FROM test)
)
WHERE (cnt=1 OR cnt2 = 1)

by 구경서 [2009.05.08 01:04:25]
마농님 하나 배워갑니다 ~
by 쏘쿨 [2009.05.08 06:55:43]
인라인뷰를 한 번만 쓰도록 만들어 봤습니다.

SELECT cd, seq, amt
FROM (SELECT t.*, SUM (amt) OVER (PARTITION BY cd, ABS (amt)) s1,
SUM (amt) OVER (PARTITION BY cd, amt ORDER BY seq DESC) s2
FROM t)
WHERE SIGN (s1) = SIGN (s2) AND ABS (s1) >= ABS (s2)
ORDER BY cd, seq


http://www.soqool.com
by Ejql [2009.05.08 11:15:41]
또 배워갑니다.. 감사합니다.
by 마농 [2009.05.11 11:41:20]
서성우님의 쿼리에서는 cnt와 cnt2가 사용되었는데요. cnt는 필요 없는 듯 합니다.
cnt가 어떤 의미로 사용되었는가 한참 고민해 봤는데 복잡하기만 하네요.
아예 빼고 cnt2만으로도 결과가 나옵니다.
by cuteleem [2009.05.26 16:08:32]
궁금한게 있는데요 ㅠㅠ
이부분만 돌려보면

WITH t AS
(
SELECT 'A' cd, 1 seq, 100 amt FROM dual
UNION ALL SELECT 'A', 2, -100 FROM dual
UNION ALL SELECT 'A', 3, -200 FROM dual
UNION ALL SELECT 'A', 4, 300 FROM dual
UNION ALL SELECT 'A', 5, 200 FROM dual
UNION ALL SELECT 'A', 6, 300 FROM dual
UNION ALL SELECT 'A', 7, -300 FROM dual
UNION ALL SELECT 'B', 1, 100 FROM dual
UNION ALL SELECT 'B', 2, 200 FROM dual
UNION ALL SELECT 'B', 3, 300 FROM dual
UNION ALL SELECT 'B', 4, -200 FROM dual
UNION ALL SELECT 'B', 5, -200 FROM dual
UNION ALL SELECT 'B', 6, -100 FROM dual
)
SELECT cd , seq , amt,Abs(amt)
,Count(*) over(PARTITION BY cd,Abs(amt) ORDER BY amt,seq) cnt
,Row_Number() over(PARTITION BY cd,amt ORDER BY amt,seq) cnt2
FROM t
----------------------------------------------------------------

cd / seq / amt / Abs(amt) / cnt /cnt2
A 2 -100 100 1 1
--------------------------------------------
이렇게 첫줄이 나오는데요.. 여기에서 cnt 가 2 가 나와야 되는거 아닌가요?
partition by cd,Abs(amt) 로 나누었으니까 총갯수가 2가 되어야 하는데
왜 1이 나오는지 아시는 분좀 알려주세용.... ㅠㅠ
by 마농 [2009.05.26 18:02:42]
PARTITION BY 에 의한 그룹별 카운트는 2입니다.
그러나 ORDER BY 구문이 있으므로 순차적으로 누적카운트하게 됩니다.
by cuteleem [2009.05.27 11:20:36]
아그렇군요^^ 마농님 감사합니다.^^
by 준 [2009.10.16 09:17:17]
여기 퀴즈들을 하나씩 보며 배우고 있는데요..
약간 틀리게 풀어봤습니다.
select cd, max(seq) seq, max(amt) amt
from (select cd, seq, amt, row_number() over(partition by cd, amt order by seq) rn
from t
)
group by cd, rn, abs(amt)
having count(1) = 1
order by cd, seq
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입