Oracle - 선수금 월별 매출 처리 쿼리 - 카테시안곱, CASE 문을 이용한 조인 방법

카테시안(Cartesian) 곱을 이용한 조인 – 첨자 LOOP형 처리

이 사례는 대부분의 회사에서 발생할 수 있는 매출과 관련한 처리의 예이다

회사는 고객에게는 상품과 용역을 제공함으로써 매출이 발생한다 특히 이회사는 서비스 용역을 제공하기 전에 대부분의 금액을 고객으로부터 선수로 받는다 회계적인 입장에서 볼 때 미리 받은 대금은 아직 서비스 용역을 제공하지 않은 상태에서 발생한 금액이므로모두 매출로 인정할수 없고 선수금 계정으로 처리해야한다 아래와 같다

 

일련번호	발생일	시작일	종료일	월매출
1101	19970501	19970520	19970925	5,000,000
1102	19970601	19970615	19970805	3,800,000
1103	19970701	19970712	19971020	6,780,000
1104	19970901	19970507	19970718	2,890,000

 

위의 테이블을 참조하여 새로운 정보를 출력하는데 그 테이블이 5개의 로우로 분활되어야하며 각 로우의 적용기간에 대한 일수 와 해당 금액을 산정하여 ‘월별매출’ 테이블에 저장되어야 한다.

 

먼저 테이블을 생성하면서 기존 컬럼형 테이블을 로우 행으로 만들 때 사용할 넘버가 해당된다

 

CREATE TABLE COPY_T(NO, NO2) AS

 

SELECT ROWNUM, SUBSTR(TO_CHAR(ROWNUM, '09'), 2, 2)

FROM TEST99

WHERE ROWNUM <=31;

 

위와 같이 만들면 NO, NO2의 두 개의 컬럼과 31개의 로우 수를 가지는 테이블이 된다

 

윈도우 함수를 사용하면 쉽게 할 수 있을 것 같다 한번 확인해 보자 답은 다음에~

CREATE TABLE PAYMENT (

SERIAL NUMBER(4),

OCRNC_DATE NUMBER(8),

STRT_D NUMBER(8),

END_D NUMBER(8),

FRRGT_STMD NUMBER(38)

);

INSERT ALL

INTO PAYMENT

VALUES ( 1101, 19970501, 19970520, 19970925, 5000000 )

INTO PAYMENT

VALUES ( 1102, 19970601, 19970615, 19970805, 3800000 )

INTO PAYMENT

VALUES ( 1103, 19970701, 19970712, 19971020, 6780000 )

INTO PAYMENT

VALUES ( 1104, 19970501, 19970507, 19970718, 2890000 );

 

 

SELECT

  SERIAL

  , substr(OCRNC_DATE, 1, 4) || y.no2 AS 적용월

  , ROUND((CASE y.NO2 WHEN SUBSTR(END_D, 5, 2) THEN SUBSTR(END_D, 7, 2)

                                                 ELSE TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')),'dd' )

                       END

     -

                     (CASE y.NO2 WHEN substr(STRT_D, 5, 2) THEN SUBSTR(STRT_D,7 , 2) ELSE '01' END )+1)

    *

                     (FRRGT_STMD/(TO_DATE(END_D,'yyyymmdd') - TO_DATE(STRT_D,'yyyymmdd')+1))) AS 월별매출액

FROM PAYMENT x, COPY_T y

WHERE Y.NO BETWEEN SUBSTR(STRT_D, 5, 2) AND SUBSTR(END_D, 5, 2)

AND x.OCRNC_DATE LIKE x.OCRNC_DATE ||'%'

 

SERIAL 적용월   월별매출액

1101   199705   426357
1101   199706   1162791
1101   199707   1162791
1101   199708   1162791
1101   199709   968992
1102   199706   1242308
1102   199707   2265385
1102   199708   365385
1103   199707   1342574
1103   199708   2080990
1103   199709   2080990
1103   199710   1342574
1104   199705   989726
1104   199706   1227260
1104   199707   712603

 

카테시안 곱으로 조인을 할때에는 ROWNUM을 활용하여 현재의 데이터와 조인을 하여 사용해야 한다

지금 현재의 데이터는 일련번호 별로 05월부터 09월 까지이다 그렇기 때문에 01부터 31까지의 로우를 사용해도 추후에는  조건절에서 범위를 정하여 자기가 필요한 05부터 09월의 데이터를 추출하는 방식이다

 

데이터는 CASE문으로 종료일에 해당하는 09월에는 25일 (THEN SUBSTR(END_D, 7, 2))을 가져오고

나머지는 그 달의 마지막날TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')), 'dd' )을 가져오게된다

 

시작일에 해당하는 05월이 나오면 20일(SUBSTR(STRT_D,7 , 2))을 가져오고 나머지는 01로 치환한다

 

이렇게 되면 빠른 숫자인  05의 ROWNUM으로 인해

종요일은 05가 아니므로 TO_CHAR(LAST_DAY(TO_DATE(END_D,'yyyymmdd')), 'dd' ) 로 인해 

30일이 치환대어 뺄셈이 시작된다

 

시작일 05월에는 20일

그래서 결과 값은 10 이때 날자값에 +1를 잊지말자

그 10일로 일할매출액을 구하는 식이

(FRRGT_STMD/(TO_DATE(END_D,'yyyymmdd') - TO_DATE(STRT_D,'yyyymmdd')+1))

위와 같으며 TO_DATE을 사용하여 종요일과 시작일의 일수를 구하여 나누어 일할매출액을 구한다

 

그렇게 월별로 매출액을 구할수 있게된다