오라클 SQL과 PL/SQL ( 누계 / 비율 / 차이 구하기 )

SUM() OVER을 활용한 누계 구하기

사용 예1. 
PANMAE 테이블을 사용하여 1000번 대리점의 판매 내역을 출력하되 판매일자, 제품코드, 판매량, 누적 판매금액을 아래와 같이 출력하세요.

 

SELECT P_DATE, P_CODE, P_QTY , P_TOTAL,
SUM(P_TOTAL) OVER(ORDER BY P_TOTAL) "TOTAL"
FROM PANMAE
WHERE P_STORE = 1000;

사용 예2. 
PANMAE 테이블을 사용하여 1000번 대리점의 판매 내역을 제품 코드별로 분류한 후 판매일자, 제품코드, 판매량, 판매금액, 누적판매금액을 아래와 같이 출력하세요.

SELECT P_DATE, P_CODE, P_QTY , P_TOTAL,
SUM(P_TOTAL) OVER(PARTITION BY P_CODE ORDER BY P_TOTAL) "TOTAL"
FROM PANMAE
WHERE P_STORE = 1000;

 

위 결과의 2번라인에 보면 PARTITION BY가 구문이 등장합니다. 이 구문으로 세부적인 그루핑을 하게 됩니다.
4번라인으로 우선 1000번 대리점의 내역만 걸러서 집계를 한 후 2번 라인의 PARTITION BY라는 구문의 P_CODE로 누적판매금액을 집계합니다.

 

사용 예3.
PANAME테이블을 조회하여 제품코드, 판매점, 판매날짜, 판매량, 판매금액과 판매점별로 누적 판매금액을 구하세요.

SELECT P_CODE, P_STORE, P_DATE, P_TOTAL, P_QTY,
SUM(P_TOTAL) OVER(PARTITION BY P_CODE, P_STORE ORDER BY P_DATE) "TOTAL"
FROM PANMAE;

RATION_TO_REPORT() 함수를 활용한 판매 비율 구하기

 

오라클에서 제공하는 비율을 구하는 함수인 RATION_TO_REPORT라는 함수를 사용하여 PANMAE테이블에서 100번 제품의 판매내역과 각 판매점별로 판매 비중을 구해보겠습니다.

SELECT P_CODE, SUM(SUM(P_QTY)) OVER() "TOTAL_QTY",
		SUM(SUM(P_TOTAL)) OVER() "TOTAL_PRICE", P_STORE, P_QTY, P_TOTAL,
		ROUND((RATIO_TO_REPORT(SUM(P_QTY)) OVER()) *100, 2) "QTY %",
		ROUND((RATIO_TO_REPORT(SUM(P_TOTAL)) OVER()) *100, 2) "P_TOTAL %"
FROM PANMAE
WHERE P_CODE = 100
GROUP BY P_CODE ,P_STORE ,P_QTY ,P_TOTAL;

위 내용은 전체 판매 내역중에서 100번 제품이 총 몇 개 팔렸으며 판매 금액은 얼마이고 그 중에서 각 판매점별로 판매량과 판매 금액을 구한 후 수량 대비 비중과 금액 대비 비중을 구한 화면입니다.
내용이 다소 복잡하지만 3번과 4번행을 보면 RATION_TO_REPORT()라는 함수가 사용되고 있음을 확인할수 있습니다. 위와 같이 비율을 출력할 때 아주 유용하게 사용되는 함수입니다.

 

LAG함수를 활용한 차이 구하기

 

1000번 판매점의 일자별 판매 내역과 금액 및 전일 판매 수량과 금액 차이를 출력하는 화면입니다.

SELECT P_STORE ,P_DATE ,P_CODE ,P_QTY ,
		LAG(P_QTY, 1) OVER(ORDER BY P_DATE) "QTY D-1",
		P_QTY  - LAG(P_QTY, 1) OVER(ORDER BY P_DATE) "DIFF-QRY",
		P_TOTAL ,
		LAG(P_TOTAL, 1) OVER(ORDER BY P_DATE) "PRICE D-1",
		P_TOTAL - LAG(P_TOTAL, 1) OVER(ORDER BY P_DATE) "DIFF-PRICE"
FROM PANMAE
WHERE P_STORE = 1000

 

1000번 판매점이 각 날짜에 각 제품을 몇 개 팔았는지 수량과 금액이 나오고 LAG함수를 활용하여 전일 판매량과 금액을 구하여 당일과 전일 판매 수량과 금액의 차이를 보여 줍니다.
만약 모든 판매점을 판매점별로 구분해서 다 출력하고 싶을 경우 아래와 같이 하면 됩니다.

SELECT P_STORE ,P_DATE ,P_CODE ,P_QTY ,
		LAG(P_QTY, 1) OVER(PARTITION BY P_STORE ORDER BY P_DATE) "QTY D-1",
		P_QTY  - LAG(P_QTY, 1) OVER(PARTITION BY P_STORE ORDER BY P_DATE) "DIFF-QRY",
		P_TOTAL ,
		LAG(P_TOTAL, 1) OVER(PARTITION BY P_STORE ORDER BY P_DATE) "PRICE D-1",
		P_TOTAL - LAG(P_TOTAL, 1) OVER(PARTITION BY P_STORE ORDER BY P_DATE) "DIFF-PRICE"
FROM PANMAE;

PARTITION BY P_STORE 구문을 활용해서 컬럼으로 구분하도록 설정하였습니다.