by 마농 누적합계 조건에따른 누적합 MODEL DIMENSION BY MEASURES RULES GREATEST 분석함수 IGNORE NAV RULES AUTOMATIC ORDER [2012.08.13]
이번 퀴즈로 배워보는 SQL 시간에는 조건에 따른 누적 합계를 구하는 쿼리를 어떻게 작성하는지에 대해 알아본다.
지면 특성상 문제와 정답 그리고 해설이 같이 있다. 진정으로 자신의 SQL 실력을 키우고 싶다면 스스로 문제를 해결 한후 정답과 해설을 참조하길 바란다.
공부를 잘 하는 학생의 문제집은 항상 문제지면의 밑바닥은 까맣지만 정답과 해설지면은 하얗다는 사실을 잊지 말자.
문제
다음과 같은 순차적인 번호(seq)와 금액(amt)을 가진 테이블([표 1] 참조)에서 번호 순서대로 금액을 누적하여 합산하되 누적합계가 0보다 작은 경우 결과가 0이 되어야 합니다.
[리스트 1]의 쿼리를 실행하면 [표 1]의 원본 테이블 자료가 조회됩니다.
[리스트 1]의 테이블을 이용하여 [표 2]의 결과 테이블 자료가 조회되는 쿼리를 작성하세요.
- [리스트1] 원본 리스트
CREATE TABLE t AS SELECT 1 seq, -2000 amt FROM dual UNION ALL SELECT 2, 4000 FROM dual UNION ALL SELECT 3, -5000 FROM dual UNION ALL SELECT 4, -2000 FROM dual UNION ALL SELECT 5, 3000 FROM dual UNION ALL SELECT 6, 1500 FROM dual UNION ALL SELECT 7, -250 FROM dual UNION ALL SELECT 8, 320 FROM dual UNION ALL SELECT 9, -4000 FROM dual UNION ALL SELECT 10, 10000 FROM dual UNION ALL SELECT 11, -20000 FROM dual ;
- [표 1] 원본 테이블
| SEQ | AMT |
|---|---|
| 1 | -2000 |
| 2 | 4000 |
| 3 | -5000 |
| 4 | -2000 |
| 5 | 3000 |
| 6 | 1500 |
| 7 | -250 |
| 8 | 320 |
| 9 | -4000 |
| 10 | 10000 |
| 11 | -20000 |
- [표 2] 결과 테이블
| SEQ | AMT | RESULT |
|---|---|---|
| 1 | -2000 | 0 |
| 2 | 4000 | 4000 |
| 3 | -5000 | 0 |
| 4 | -2000 | 0 |
| 5 | 3000 | 3000 |
| 6 | 1500 | 4500 |
| 7 | -250 | 4250 |
| 8 | 320 | 4570 |
| 9 | -4000 | 570 |
| 10 | 10000 | 10570 |
| 11 | -20000 | 0 |
문제설명
이 문제는 금액을 순차적으로 합산해 나가는 문제입니다.단 0보다 작은 결과는 0으로 치환이 된다는 조건이 있습니다.
[표 2]의 결과 집합의 Result 항목의 계산방법에 대해 설명해보겠습니다.
- - 제일 첫 번째 금액은 -2000 으로 0보다 작은 음수입니다. 따라서 결과는 0이 됩니다.
- - 첫 번째 행의 결과 0에 두 번째 금액을 합산합니다. 0 + 4000 = 4000
- - 4000은 0보다 큰 수 이므로 결과에 그대로 반영됩니다.
- - 3 번에서는 4000 + (-5000) = -1000 이므로 결과는 0입니다.
- - 4 번에서는 0 + (-2000) = -2000 이므로 결과는 0입니다.
- - 5 번에서는 0 + 3000 = 3000 이므로 결과는 3000입니다.
- - 6 번에서는 3000 + 1500 = 4500 이므로 결과는 4500입니다.
- - 7 번에서는 4500 + (-250) = 4250 이므로 결과는 4250입니다.
- - 8 번에서는 4250 + 320 = 4570 이므로 결과는 4570입니다.
- - 9 번에서는 4570 + (-4000) = 570 이므로 결과는 570입니다.
- - 10 번에서는 570 + 10000 = 10570 이므로 결과는 10570입니다.
- - 11 번에서는 10570 + (-2000) = -9430 이므로 결과는 0입니다.
- 결과가 나오는 과정에 대해 이해 하셨나요?
정답
문제를 스스로 해결해 보셨나요? 이제 정답을 알아보겠습니다.
- [리스트2] 정답 리스트
SELECT * FROM t MODEL DIMENSION BY (seq) MEASURES (amt, 0 result) IGNORE NAV RULES AUTOMATIC ORDER (result[ANY] = GREATEST(0, result[CV()-1] + amt[CV()])) ORDER BY seq ;
어떤가요? 여러분이 만들어본 리스트와 같은가요? 틀렸다고 좌절할 필요는 없답니다. 첫 술에 배부를 순 없는 것이니까요. 해설을 꼼꼼히 보고 자신이 잘못한 점을 비교해 보는 것이 더 중요합니다.
해설
생소한 구문들이 눈에 띠네요. 오라클 10G 버전부터 제공되는 MODEL 기능을 이용하여 문제를 해결했습니다.
MODEL 구문의 기본적인 사용법에 대해 알아보고, 이 문제를 풀기 위해 MODEL 구문을 어떻게 응용했는지 알아보도록 하겠습니다.
그 전에 먼저 MODEL 구문이 아닌 다른 방법을 이용해 누적합산을 구해보고 차이점을 비교해 보도록 하겠습니다.
누적 합계을 구하기 위해서 우선 분석함수를 이용해 보도록 하겠습니다.
- [리스트 4] 분석함수를 이용한 누적 합계
SELECT seq
, amt
, SUM(amt) OVER(ORDER BY seq) result
FROM t
;
- [표 4] [리스트 4] 쿼리의 결과 테이블
SEQ AMT RESULT
--------- ---------- ----------
1 -2000 -2000
2 4000 2000
3 -5000 -3000
4 -2000 -5000
5 3000 -2000
6 1500 -500
7 -250 -750
8 320 -430
9 -4000 -4430
10 10000 5570
11 -20000 -14430
분석함수에 대해 모르시는 분들도 계시계지만 MODEL구문보다는, 그래도 더 많이 알려져 있고, 분석함수 사용도 어느 정도 보편화 되어 가는 추세인 듯 합니다.
분석함수에 대해서는 따로 자세한 설명을 드리진 않고 결과만 보도록 하겠습니다.
[표 4] 결과 테이블을 보면 누적 합산된 금액이 나오는 것을 확인 할 수 있습니다. 여기에 0보다 작은 결과는 0으로 치환하는 조건을 적용해 보도록 하겠습니다.
- [리스트 5] 분석함수를 이용한 누적 합계
SELECT seq
, amt
, SUM(amt) OVER(ORDER BY seq) result_1
, GREATEST(0, SUM(amt) OVER(ORDER BY seq)) result_2
FROM t
;
- [표 5] [리스트 5] 쿼리의 결과 테이블
SEQ AMT RESULT_1 RESULT_2
------ ---------- ---------- ----------
1 -2000 -2000 0
2 4000 2000 2000
3 -5000 -3000 0
4 -2000 -5000 0
5 3000 -2000 0
6 1500 -500 0
7 -250 -750 0
8 320 -430 0
9 -4000 -4430 0
10 10000 5570 5570
11 -20000 -14430 0
[리스트5] 쿼리를 보면 0보다 작은 수를 0으로 대체하기 위해 GREATEST 함수를 사용 했습니다. 유용한 팁이니 기억해 두시면 편리합니다.
[표 5]의 결과 테이블을 보면 원하는 결과가 나오지 않은 것을 확인 할 수 있습니다.
누적 합계 결과인 RESULT_1 의 값이 음수일 경우 0으로 대체되어 RESULT_2 가 나온것을 확인 할 수 있습니다.
즉, 최종 결과에 대해서만 은수를 0으로 바꾸는 조건이 적용되었을 뿐, 차례 차례 합산하면서 조건이 적용되지는 않은 것입니다.
그렇다면 최종 결과에 조건을 적용하는 대신 합산하기 전에 미리 적용시켜 보면 어떨까요?
- [리스트 6] 분석함수를 이용한 누적 합계
SELECT seq
, amt
, GREATEST(0, amt) result_1
, SUM(GREATEST(0, amt)) OVER(ORDER BY seq) result_2
FROM t
;
- [표 6] [리스트 6] 쿼리의 결과 테이블
SEQ AMT RESULT_1 RESULT_2
------ ---------- ---------- ----------
1 -2000 0 0
2 4000 4000 4000
3 -5000 0 4000
4 -2000 0 4000
5 3000 3000 7000
6 1500 1500 8500
7 -250 0 8500
8 320 320 8820
9 -4000 0 8820
10 10000 10000 18820
11 -20000 0 18820
[표 6] 결과 테이블을 보면 우선 음수 금액을 0으로 바꾼뒤 SUM() OVER()를 이용해 누적합계를 구했습니다. 그러나, 이 역시도 원하는 결과를 구하지는 못했습니다.
왜 이런 결과가 나왔을까요? 이는 SQL이 가진 태생적인 한계 때문입니다.
SQL은 비절차적, 구조적, 집합적 언어입니다. 비절차적이므로 사용이 편리했던 점이,이와 같은 절차적인 문제와 마주하게 되었을 때 오히려 단점이 되어 버리는 경우입니다.
앞단의 결과가 나와야지만 그 결과를 이용해 다음 결과를 도출해 낼 수 있는 문제입니다. 이러한 절차적인 문제를 해결하기 위해서 우리는 PL/SQL 을 사용해야만 합니다.
하지만 MODEL 구문은 이러한 절차적인 문제도 접근 가능하도록 해줍니다.
MODEL 구문은 다차원 배열 형태로 복잡한 자기 참조가 가능합니다.
정답 쿼리에 사용된 MODEL 구문에 대해 간략하게 설명해 보도록 하겠습니다.
- - MODEL : MODEL 기능을 사용하겠다고 선언하는 부분입니다.
- - DIMENSION BY (seq) : 레코드를 구별하는 키를 선언하는 부분입니다.
- - MEASURES (amt, 0 result) : 키가 아닌 다른 항목들을 표시합니다.
- - RULES () : 여기서는 배열 구조의 값을 지정하는 구문입니다.
간단한 예로 RULES(result[1] = 100) 이라고 하면 이는 seq=1 인 레코드의 result 값은 100이 된다는 의미입니다.
result[ANY]는 모든 레코드의 result를 의미합니다. 즉, 모든 레코드들에 대해 순차적으로 값을 대입하는 것이구요. 우변에 amt[CV()]는 현재행의 금액을 의미합니다.
즉 CV() 는 현재 레코드의 키값(Dimension에서 선언한 seq)을 의미합니다. 다섯 번째 행의 CV() 값이 5라면 [CV()-1] 은 4번째 행. 즉, 이전 행을 의미합니다.
- result[ANY] = GREATEST(0, result[CV()-1] + amt[CV()])
이 구문은 모든 레코드들의 result 값(result[ANY])을 차례로 지정하는데 그 계산식은 이전 행의 result 값(result[CV()-1]) 에 현재행의 금액(amt[CV()])을 더하고 그 값이 0보다 작을 경우 0으로 바꿉니다.
이러한 과정을 ANY 키워드에 의해서 1행부터 11행까지 차례대로 반복하게 됩니다.
- IGNORE NAV : 참조되는 배열의 값이 없을 경우 기본값(0)을 가지도록 합니다.
예를 들면 첫 번째 행에서 [CV()-1] 을 참조 하게 되면 그 값이 없으므로 0이 나오게 됩니다. 만약 이 옵션을 주지 않는다면 0 이 아닌 Null 이 나오게 됩니다.
- RULES AUTOMATIC ORDER : 배열의 키 값 순서대로 처리하라는 구문입니다.
이 문제에서 [표 1]의 원본테이블이 순서대로 되어 있지만, 현실에선 항상 순서대로 되어있으란 법은 없습니다.
순서가 뒤죽박죽일 경우 이 옵션이 없이 위와 같이 직전 행을 차례로 참조하는 형태의 자기 참조 계산식을 사용하게 되면 에러가 나게 됩니다.
이 옵션을 주므로 에러가 나지 않고 순차적으로 처리하게 됩니다.
첫 번째 행부터 마지막 행까지 result 를 계산하고 이전행의 result값을 참조하여 다시 result값을 계산하는 형태로 MODEL 절을 이용해 SQL을 작성해 봤습니다.
이번 퀴즈로 배우는 SQL 시간에는 간략하게 MODEL 절에 대해 소개했습니다.
실제로 MODEL 절은 지금 소개드린 내용보다 더 복잡합니다만. 여기서는 퀴즈에서 사용된 아주 일부 기본적인 기능만을 설명드렸습니다.
MODEL의 기능과 구문을 익힐 수 있는 간단한 퀴즈 하나 더 풀어보도록 하겠습니다.
다음과 같이 월별 금액 자료가 있을 경우 12개월치 자료를 모두 조회하는 SQL을 작성해 보세요. 해당월의 자료가 없을때는 전월의 자료를 보여줘야 합니다.
- [리스트 7] 원본 리스트
WITH t AS ( SELECT 1 mm, 100 amt FROM dual UNION ALL SELECT 5, 200 FROM dual UNION ALL SELECT 6, 100 FROM dual UNION ALL SELECT 9, 300 FROM dual ) SELECT * FROM t ;
- [표 7] 원본 테이블
MM AMT
------ ----------
1 100
5 200
6 100
9 300
- [표 8] 결과 테이블
MM AMT
----- ----------
1 100
2 100
3 100
4 100
5 200
6 100
7 100
8 100
9 300
10 300
11 300
12 300
- [리스트 8] 정답 리스트
WITH t AS ( SELECT 1 mm, 100 amt FROM dual UNION ALL SELECT 5, 200 FROM dual UNION ALL SELECT 6, 100 FROM dual UNION ALL SELECT 9, 300 FROM dual ) SELECT * FROM t MODEL DIMENSION BY (mm) MEASURES (amt) RULES AUTOMATIC ORDER (amt[FOR mm FROM 1 TO 12 INCREMENT 1] = NVL(amt[CV()], amt[CV()-1])) ORDER BY mm ;
간단하게 퀴즈와 정답을 살펴 봤습니다. 두 번째 퀴즈의 정답에 대한 해설은 따로 하지 않겠습니다. 독자분들께서 직접 해설을 작성해 보시기 바랍니다.
- 강좌 URL : http://www.gurubee.net/lecture/2203
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
휴 MODEL 어렵네요
게시판에 있는 MODEL 한글 메뉴얼 까지 정독 몇번을 했는데도 틀만 외우는 형식이 대버렸네요
우선 공부를 우선 더 한 후에.. 재도전 해봐야겠어여
배워갑니다.
처음 알게된 MODEL..저두 다른책 함 더보고 공부하고 있어요.
신기하고 좋은거 같아요. 또하나 배웁니다. ^^
-- 11G Recursive SQL(재귀쿼리) 을 이용하니 Model 절이 아니어도 가능하군요.
WITH t AS
(
SELECT 1 seq, -2000 amt FROM dual
UNION ALL SELECT 2, 4000 FROM dual
UNION ALL SELECT 3, -5000 FROM dual
UNION ALL SELECT 4, -2000 FROM dual
UNION ALL SELECT 5, 3000 FROM dual
UNION ALL SELECT 6, 1500 FROM dual
UNION ALL SELECT 7, -250 FROM dual
UNION ALL SELECT 8, 320 FROM dual
UNION ALL SELECT 9, -4000 FROM dual
UNION ALL SELECT 10, 10000 FROM dual
UNION ALL SELECT 11, -20000 FROM dual
)
, t1(seq, amt, result) AS
(
SELECT seq, amt
, GREATEST(0, amt) result
FROM t
WHERE seq = 1
UNION ALL
SELECT b.seq, b.amt
, GREATEST(0, a.result + b.amt) result
FROM t1 a, t b
WHERE a.seq + 1 = b.seq
)
SELECT * FROM t1
;
설명을 워낙 잘하셔서 처음 접하는 내용임에도 불구하고 감이 잡히네요 대단하세요.
select seq, amt, result
from (select * from t order by seq)
model dimension by (seq)
measures (amt, 0 result)
ignore nav
rules (
result[ANY] = case when amt[cv(seq)] + nvl(result[cv(seq)-1],0) <=0 then 0 else amt[cv(seq)] + nvl(result[cv(seq)-1],0) end );
greatest 함수를 활용못하니 코드가 난잡해졌네요.
) 버튼을 클릭하여 작성 하시면 됩니다.