database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 40965 게시물 읽기
No. 40965
누계 어렵네여...
작성자
ORA2015
작성일
2015-10-11 03:06ⓒ
2015-10-11 03:16ⓜ
조회수
10,010

WITH temp AS

(

SELECT '20090316' SDATE, 1 QT, '1' GUBUN, 10000 AMT FROM dual

UNION ALL SELECT '20090316',2, '1', 20000 FROM dual

UNION ALL SELECT '20090316', 3,'2', 10000 FROM dual

UNION ALL SELECT '20090316', 1, '1', 50000 FROM dual

UNION ALL SELECT '20090331', 10, '1', 10 FROM dual

UNION ALL SELECT '20090401', 1, '1', 10 FROM dual

UNION ALL SELECT '20090402',1, '2', 20 FROM dual

)

--원하는 결과는 내역은 모든 row가 나오고 맨오른쪽 컬럼은 차변-대변을 뺀 금액을

계속 합산해서 누계로 표시하고, 중간에 월계,누계가 아래처럼 나오는것인데

월누계는 전월분의 합산으로, 즉 4월의 누계는 3월누계가 누적된 것으로요..

200903 20090316 1 10000 0 10000

200903 20090316 2 20000 0 30000

200903 20090316 3 0 10000 20000

200903 20090316 1 50000 0 70000

200903 20090331 10 10 0 70010

200903 월계 17 80010 10000

200903 누계 17 80010 10000 70010

200904 20090401 1 10 0 70020

200904 20090402 1 0 20 70000

200904 월계 2 10 20

200904 누계 19 80020 10020 70000

 

SELECT YM,

DECODE(GROUPING(1), 0, NVL(SDATE, YM || '월계'), '누계') SDATE

, DECODE(GROUPING(1), 0, SUM(QT), SUM(SUM(QT)) OVER(PARTITION BY YM ORDER BY YM, SDATE) / 3 ) AS QTY

, DECODE(GROUPING(1), 0, SUM(AMT1) , SUM(SUM(AMT1)) OVER(PARTITION BY YM ORDER BY YM, SDATE) / 3 ) AS AMT1

, DECODE(GROUPING(1), 0, SUM(AMT2) , SUM(SUM(AMT2)) OVER(PARTITION BY YM ORDER BY YM, SDATE) / 3 ) AS AMT2

, SUM(DECODE(GROUPING(SDATE), 0, SUM(NVL(AMT1,0) - NVL(AMT2,0)))) OVER(ORDER BY YM, SDATE) AS NU_AMT

FROM

(

SELECT SUBSTR(SDATE,1,6) YM,

SDATE,

QT,

CASE GUBUN WHEN '1' THEN AMT ELSE 0 END AS AMT1,

CASE GUBUN WHEN '2' THEN AMT ELSE 0 END AS AMT2

FROM temp

)

GROUP BY YM, ROLLUP(1, SDATE)

ORDER BY YM, SDATE

 

아래처럼 같은 날짜끼리 SUM 되어서 집계되 버리네여,..

원하는 결과를 뽑으러면 어찌해야될까요...? --;

200903 20090316 7 80000 10000 70000

200903 20090331 10 10 0 70010

200903 200903월계 17 80010 10000 70010

200903 누계 17 80010 10000 70010

200904 20090401 1 10 0 70020

200904 20090402 1 0 20 70000

200904 200904월계 2 10 20 70000

200904 누계 2 10 20 70000

 

 

이 글에 대한 댓글이 총 3건 있습니다.

굳이 SQL만으로 접근해야되는 이유가 있는건가요?

그게 아니라면 각각의 행에 대해서 누적계산을 수행하는 Procedure나 Function을 작성해서 수행시키고 해당 결과만 출력하시는게 훨씬 쉽게 접근가능하실 것 같습니다.

최성혁(blutgere)님이 2015-10-12 15:53에 작성한 댓글입니다.
아래는 제가 임시로 만들어본 예제입니다.
* 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 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에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
40969datafile의 max_size를 반영한 df -h 결과 값 출력..
검콤이
2015-10-14
8275
40967저장 프로시저에서 insert into select...
왕초보
2015-10-13
8121
40966View 테이블에서 rownum이 안먹힐 경우
김동철
2015-10-12
7930
40965누계 어렵네여... [3]
ORA2015
2015-10-11
10010
40964datetime끼리 계산
초보2
2015-10-08
8703
40963패키지/프로시져 사용 테이블
홍승종
2015-10-08
8555
40962GIS 좌표 관련 연산 방법
쿼리OTL
2015-10-07
8658
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다