아래 쿼리문에서 상품권 충전포인트 가져오는 쿼리문과 소멸예정일자 가져오는 쿼리문이 각각 두개로
작성했는데, WHERE 조건문만 조금 차이나는 부분이 있습니다.
상품권 충전포인트 쿼리문에서는 원적립추가 이하부분과 소멸예정일자 가져오는 쿼리문에서는
WHERE 조건문에서 소멸예정일자 조건문이 추가된 부분이 상품권 충전포인트 쿼리문과 다릅니다.
대용량 테이블이라 상품권충전 포인트 쿼리문과, 소멸예정일자 쿼리문을 한개의 쿼리문으로 작성이
가능 할까요?
필드명 : RVG_PNT(적립포인트), UG_PNT(사용포인트)
SELECT
NVL(MAX(( SELECT SUM(GIFT_CARD_RM_PNT)
FROM (SELECT CASE WHEN A.TR_DS_C = '20'
THEN - A.RVG_PNT
ELSE A.RVG_PNT
END
- NVL(SUM(B.UG_PNT), 0) GIFT_CARD_RM_PNT
FROM TB_MP_SA_AH_BASERVGBRK A
, TB_MP_SA_AH_BASEUGBRK B
, TB_MP_SA_AH_BASERVGBRK C
WHERE A.RVG_APV_LED_DTL_NO = B.RVG_APV_LED_DTL_NO (+)
AND A.USCAN_ATF_APV_LED_DTL_NO = B.USCAN_ATF_APV_LED_DTL_NO (+)
AND A.RVG_APV_LED_DTL_NO = C.RVG_APV_LED_DTL_NO
AND A.INTG_MB_NO = '5000040267'
AND A.XHT_STS_YN = 'N'
AND C.USCAN_ATF_APV_LED_DTL_NO = LPAD(0, 18, '0') -- 원적립추가
AND C.TR_RSN_C = '0503'
GROUP BY A.RVG_APV_LED_DTL_NO
, CASE WHEN A.TR_DS_C = '20' THEN - A.RVG_PNT ELSE A.RVG_PNT END
)
)), 0) AS GIFT_CARD_RM_PNT /* 상품권 충전 포인트 */
, NVL(MAX(( SELECT SUM(XTT_PLA_PNT)
FROM (SELECT CASE WHEN A.TR_DS_C = '20' THEN - A.RVG_PNT ELSE A.RVG_PNT END
- NVL(SUM(CASE WHEN B.TR_TP_C = '14' THEN - B.UG_PNT
ELSE B.UG_PNT END), 0) XTT_PLA_PNT
FROM TB_MP_SA_AH_BASERVGBRK A
, TB_MP_SA_AH_BASEUGBRK B
WHERE A.RVG_APV_LED_DTL_NO = B.RVG_APV_LED_DTL_NO (+)
AND A.USCAN_ATF_APV_LED_DTL_NO = B.USCAN_ATF_APV_LED_DTL_NO (+)
AND A.INTG_MB_NO = '5000040267'
AND A.XHT_STS_YN = 'N'
AND A.XTT_PLA_DT BETWEEN TO_CHAR(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') || '01', 'YYYYMMDD'), 'YYYYMMDD') -- 소멸예정일자
AND TO_CHAR(LAST_DAY(TO_DATE(TO_CHAR(SYSDATE, 'YYYYMM') || '01', 'YYYYMMDD')), 'YYYYMMDD')
GROUP BY CASE WHEN A.TR_DS_C = '20' THEN - A.RVG_PNT ELSE A.RVG_PNT END
)
)), 0) AS XTT_PLA_PNT /* 소멸예정 포인트 */
FROM TB_MP_AP_MH_PHTINF A
WHERE A.INTG_MB_NO = '5000040267'
;
TB_MP_AP_MH_PNTINF:(포인트마스터테이블, pk는 회원번호:INTG_MB_NO)
TB_MP_SA_AH_BASERVGBRK(포인트적립테이블, pk는 적립원장번호:RVG_APV_LED_DTL_NO,적립원장상세번호:USCAN_ATF_APV_LED_DTL_NO)
TB_MP_SA_AH_BASERUGBRK:(포인트사용 테이블,pk는 사용원장번호:UG_APV_LED_DTL_NO,
적립원장번호:RVG_APV_LED_DTL_NO,
사용원장상세번호:USCAN_ATF_APV_LED_DTL_NO) 입니다
Where 절 쿼리문 설명 하자면 XHT_STS_YN는 포인트소멸여부 필드이며,
XTT_PLA_DT는 포인트소멸예정일자 이며
상품권 충전포인트의 합과 소멸예정인 포인트의 합을 구하는 쿼리문 입니다.
c는 위쪽에만 조인하는 이유는 원래의 포인트 적립건을 가져와야 하는 경우라서
AND C.USCAN_ATF_APV_LED_DTL_NO = LPAD(0, 18, '0') 이 조건문을 추가하게 되었고
TR_DS_C = '10' : 거래구분이 포인트 적립, TR_DS_C = '20' : 거래구분이 포인트 적립취소 의미임
tr_tp_c = '14' 조건이 아래쪽에 있는 것은 소멸예정 포인트를 계산하기 위해 상계증가분에 대한 조건이 추가된 건입니다.
아무튼 상품권 충전포인트의 합과 소멸예정 포인트 합을 구하는 where 조건이 조금 상이합니다.
(제가 직접적인 업무 담당자가 아니라 자세한 설명을 못드려서 죄송합니다)
결론적으로
TB_MP_SA_AH_BASERVGBRK(포인트적립테이블) 과 TB_MP_SA_AH_BASERUGBRK(포인트사용 테이블)은
대용량 데이터가 있어서 상품권 충전포인트의 합과 소멸예정 포인트 합 계산시 조건은 상이 하더라도
위 두테이블을 한번만 사용해서 상품권 충전포인트와 소멸예정 포인트 합의 쿼리문을 구현할수 있는지와
아니면 다른 방법으로 쿼리문을 작성할수 있는지 알려 주시면 고맙겠습니다.
-- 개선전 -- SELECT d.deptno , d.dname , (SELECT COUNT(*) FROM emp e WHERE e.deptno = d.deptno AND e.mgr IS NOT NULL ) cnt_tot , (SELECT COUNT(*) FROM emp e WHERE e.deptno = d.deptno AND e.mgr IS NOT NULL AND e.sal >= 2000 ) cnt_sal_2000 , (SELECT SUM(e.sal) FROM emp e WHERE e.deptno = d.deptno AND e.mgr IS NOT NULL AND e.job = 'CLERK' ) clerk_sal FROM dept d WHERE d.deptno < 50 ORDER BY d.deptno ; -- 개선후 -- SELECT d.deptno , d.dname , COUNT(e.deptno) cnt_tot , COUNT(CASE WHEN e.sal >= 2000 THEN 1 END) cnt_sal_2000 , SUM(CASE WHEN e.job = 'CLERK' THEN e.sal END) clerk_sal FROM dept d , emp e WHERE d.deptno = e.deptno(+) AND e.mgr(+) IS NOT NULL AND d.deptno < 50 GROUP BY d.deptno, d.dname ORDER BY d.deptno ;