Oracle 10g SQL MODEL Clase.pdf
참고 : http://radiocom.kunsan.ac.kr/lecture/oracle/statement_select/model.html
http://www.oracleclub.com/article/26172
위 첨부파일을 꼭 참고하세요
다음과 같은 seq, amt 자료가 있을때 result를 구하는 쿼리를 작성하세요.
순차적으로 amt값을 누적합산하되 그 값이 음수일경우엔 0이 되어야 합니다.
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
)
SELECT * FROM t
|
SEQ |
AMT |
RESULT |
계산 방법 참고 |
|
1 |
-2000 | 0 | -2000 이 음수이므로 0 |
|
2 |
4000 | 4000 | 0 + 4000 = 4000 |
|
3 |
-5000 | 0 | 4000 - 5000 = -1000 = 0 |
|
4 |
-2000 | 0 | 0 - 2000 = -2000 = 0 |
|
5 |
3000 | 3000 | 0 + 3000 = 3000 |
|
6 |
1500 | 4500 | 3000 + 1500 = 4500 |
|
7 |
-250 | 4250 | 4500 - 250 = 4250 |
|
8 |
320 | 4570 | 4250 + 320 = 4570 |
|
9 |
-4000 | 570 | 4570 - 4000 = 570 |
|
10 |
10000 | 10570 | 570 + 10000 = 10570 |
|
11 |
-20000 | 0 | 10570 - 20000 = -9430 = 0 |
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 8, 320 FROM dual
UNION ALL SELECT 7, -250 FROM dual
UNION ALL SELECT 9, -4000 FROM dual
UNION ALL SELECT 10, 10000 FROM dual
UNION ALL SELECT 11, -20000 FROM dual
)
SELECT SEQ, AMT, RESULT FROM T
MODEL
DIMENSION BY (SEQ)
MEASURES (AMT, 0 RESULT)
IGNORE NAV
RULES
AUTOMATIC ORDER
(
RESULT[SEQ] = GREATEST(0, RESULT[CV(SEQ) - 1] + AMT[CV(SEQ)])
)
ORDER BY SEQ
================================================================================================================
================================================================================================================
참고로 누적 구하는 쿼리
|
WITH TEST AS |