[oracle]누적합계 구하기

오라클 누적 합계 구하기

계층형 테이블 생성

테이블 생성

조직별 회원별 판매실적 테이블 생성

CREATE TABLE SALE( 
	ITEM_CD VARCHAR2(10) , 
	MEMBER_ID NUMBER(10),
	SAL NUMBER(20)
);

데이터준비

INSERT ALL
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,10000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,20000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',4,40000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,560000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,30000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,70000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',4,870000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',2,330000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',2,260000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',1,110000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',5,60000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',3,770000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',3,1700000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I004',1,320000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I005',2,220000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I001',1,890000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I002',1,750000)
INTO SALE(ITEM_CD,MEMBER_ID,SAL)VALUES('I003',1,740000)
SELECT 1 FROM DUAL;

금액 별로 정렬후 누적합계 붙이기

SELECT MEMBER_ID , SAL, SUM(SAL) OVER (ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) TOTAL
FROM SALE 
WHERE MEMBER_ID = 1;

결과

		MEMBER_ID			SAL			TOTAL

1 1 10,000 10,000
2 1 20,000 30,000
3 1 110,000 140,000
4 1 320,000 460,000
5 1 560,000 1,020,000
6 1 740,000 1,760,000
7 1 750,000 2,510,000
8 1 890,000 3,400,000

누적차감(?)

SELECT MEMBER_ID , SAL, SUM(SAL) OVER (ORDER BY SAL ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING  ) TOTAL
FROM SALE 
WHERE MEMBER_ID = 1;

결과
MEMBER_ID SAL TOTAL
1 1 10,000 3,400,000
2 1 20,000 3,390,000
3 1 110,000 3,370,000
4 1 320,000 3,260,000
5 1 560,000 2,940,000
6 1 740,000 2,380,000
7 1 750,000 1,640,000
8 1 890,000 890,000