오라클 누적 합계 구하기
계층형 테이블 생성
테이블 생성
조직별 회원별 판매실적 테이블 생성
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
댓글 없음:
댓글 쓰기