데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

아래는 제가 임시로 만들어본 예제입니다.

* Informix SQL 문법으로 작성된거라 Oracle 용으로 변경해주셔야 합니다.

(

일시 datetime year to second,

수량 int,

구분 char(1),

금액 int

);

insert into shchoi_temp values ( '2009-03-16 00:01:00', 1, 1, 10000 );

insert into shchoi_temp values ( '2009-03-16 00:02:00', 2, 1 ,20000 );

insert into shchoi_temp values ( '2009-03-16 00:03:00', 3 ,2, 10000 );

insert into shchoi_temp values ( '2009-03-31 00:01:00', 10, 1, 10 );

insert into shchoi_temp values ( '2009-04-01 00:01:00', 1, 1, 10 );

insert into shchoi_temp values ( '2009-04-02 00:02:00', 1, 2, 20 );

-- 테스트용 프로시져 예제

CREATE PROCEDURE sp_shchoi_temp()

RETURNING CHAR(6), CHAR(8), INT, INT, INT, INT;

DEFINE v_년월 CHAR(6);

DEFINE v_월계_수량 INT;

DEFINE v_월계_금액 INT;

DEFINE v_월계_대변 INT;

DEFINE v_월계_차변 INT;

DEFINE v_누계_수량 INT;

DEFINE v_누계_금액 INT;

DEFINE v_누계_대변 INT;

DEFINE v_누계_차변 INT;

DEFINE v_일시 CHAR(8);

DEFINE v_수량 INT;

DEFINE v_구분 CHAR(1);

DEFINE v_금액 INT;

-- 변수초기화

SELECT TO_CHAR(MIN(일시), '%Y%m')

INTO v_년월

FROM SHCHOI_TEMP

;

LET v_월계_수량 = 0;

LET v_월계_금액 = 0;

LET v_월계_대변 = 0;

LET v_월계_차변 = 0;

LET v_누계_수량 = 0;

LET v_누계_금액 = 0;

LET v_누계_대변 = 0;

LET v_누계_차변 = 0;

FOREACH

SELECT to_char(일시, '%Y%m%d'), 수량, 구분, 금액

INTO v_일시, v_수량, v_구분, v_금액

FROM SHCHOI_TEMP

ORDER BY 일시

-- 년월이 바뀌면 월계, 누계 출력 후 월계 초기화

IF v_년월 <> SUBSTR(v_일시,1,6) THEN

RETURN v_년월, '월계', v_월계_수량, v_월계_대변, v_월계_차변, v_월계_금액

WITH RESUME;

RETURN v_년월, '누계', v_누계_수량, v_누계_대변, v_누계_차변, v_누계_금액

WITH RESUME;

LET v_년월 = SUBSTR(v_일시,1,6);

LET v_월계_수량 = 0;

LET v_월계_금액 = 0;

LET v_월계_대변 = 0;

LET v_월계_차변 = 0;

END IF;

-- 월계 누계 적용 후 해당월 출력

IF v_년월 = SUBSTR(v_일시,1,6) THEN

LET v_월계_수량 = v_월계_수량 + v_수량;

LET v_누계_수량 = v_누계_수량 + v_수량;

-- 대변

IF v_구분 = 1 THEN

LET v_월계_대변 = v_월계_대변 + v_금액;

LET v_월계_금액 = v_월계_금액 + v_금액;

LET v_누계_대변 = v_누계_대변 + v_금액;

LET v_누계_금액 = v_누계_금액 + v_금액;

RETURN v_년월, v_일시, v_수량, v_금액, 0, v_누계_금액

WITH RESUME;

-- 차변

ELIF v_구분 = 2 THEN

LET v_월계_차변 = v_월계_차변 + v_금액;

LET v_월계_금액 = v_월계_금액 - v_금액;

LET v_누계_차변 = v_누계_차변 + v_금액;

LET v_누계_금액 = v_누계_금액 - v_금액;

RETURN v_년월, v_일시, v_수량, 0, v_금액, v_누계_금액

WITH RESUME;

END IF;

END FOREACH;

-- 마지막으로 월계 누계 출력

RETURN v_년월, '월계', v_월계_수량, v_월계_대변, v_월계_차변, v_월계_금액

WITH RESUME;

RETURN v_년월, '누계', v_누계_수량, v_누계_대변, v_누계_차변, v_누계_금액

WITH RESUME;

END PROCEDURE;

-- 결과 화면

(expression) (expression) (expression) (expression) (expression) (expression)

200903 20090316 1 10000 0 10000

200903 20090316 2 20000 0 30000

200903 20090316 3 0 10000 20000

200903 20090331 10 10 0 20010

200903 월계 16 30010 10000 20010

200903 누계 16 30010 10000 20010

200904 20090401 1 10 0 20020

200904 20090402 1 0 20 20000

200904 월계 2 10 20 -10

200904 누계 18 30020 10020 20000

최성혁(blutgere)님이 2015-10-12 16:57에 작성한 댓글입니다.
이 댓글은 2015-10-12 16:58에 마지막으로 수정되었습니다.

-- 날짜가 중복되네요.
-- 중복이 없는 유니크 키가 필요합니다.
SELECT ym
     , sdate
     , DECODE(GROUPING(1), 0, SUM(qt)
       , SUM(SUM(qt))   OVER(PARTITION BY GROUPING(1) ORDER BY ym) ) qt
     , DECODE(GROUPING(1), 0, SUM(amt1)
       , SUM(SUM(amt1)) OVER(PARTITION BY GROUPING(1) ORDER BY ym) ) amt1
     , DECODE(GROUPING(1), 0, SUM(amt2)
       , SUM(SUM(amt2)) OVER(PARTITION BY GROUPING(1) ORDER BY ym) ) amt2
     , DECODE(GROUPING_ID(1, sdate)
       , 0, SUM(SUM(amt)) OVER(ORDER BY sdate, rn)
       , 1, SUM(amt)
       , 3, SUM(SUM(amt)) OVER(PARTITION BY GROUPING(1) ORDER BY ym)
       ) nu_amt
FROM (SELECT SUBSTR(sdate, 1, 6) ym
             , sdate
             , ROWNUM rn -- 중복제거용 Unique Key
             , qt
             , DECODE(gubun, 1, amt, 2, -amt) amt
             , DECODE(gubun, 1, amt, 0) amt1
             , DECODE(gubun, 2, amt, 0) amt2
          FROM temp
        ) a
GROUP BY ym, ROLLUP(1, (sdate, rn))
ORDER BY ym, GROUPING_ID(1, sdate), sdate, rn
;

마농(manon94)님이 2015-10-13 17:32에 작성한 댓글입니다.