oracle 상품 판매 통계 쿼리 union all case group by 활용

상품 판매 통계 쿼리

 

오늘은 상품 판매 통계 쿼리를 만들어 보겠습니다

상품은 여러 종류가 있습니다

그것을 어떻게 구분할지는 스스로가 정해야겠지요

 

아래에서는 상품구분을 단순하게 알파벳으로 표현하였습니다

그 알파벳으로 CASE문을 사용하여 상품명을 가져왔습니다

 

보통은 공통코드가 만들어져 조인을 걸어서 사용해야만 합니다

그래야 지만 상품이 늘어날 때마다 쿼리를 수정하지 않고 데이터를 추가하는 것만으로도

상품이 추가되어서 쿼리가 작동되는 범용성을 가져야 한다는 것이지요

 

그래서 솔직히 아래의 코드는 좋은 쿼리라고 할 수 없습니다

 

하지만 CASE문을 어떻게 써서 열 데이터를 행열로 만들어서 카운팅 하는 지만 신경 써서 보시길 바랍니다

 

<select id="selectProductSaleTotalIncrease" parameterType="Map" resultType="Map">
    SELECT
           DI_POM_SP.PRD_TYPE
          ,TO_CHAR(DI_POM_SP.PAY_CNT,'FM9999,999') AS PAY_CNT
          ,TO_CHAR(DI_POM_SP.PAY_AMT,'FM999,999,999,999') AS PAY_AMT
      FROM
           (SELECT
           		   /* 상품에 대한 구분(소분류, 중분류, 대분류등등) */
                   (CASE WHEN SP.PRD_DVCD = 'A' THEN '이용권'
                         WHEN SP.PRD_DVCD = 'B' THEN '상품 구매'
                         WHEN SP.PRD_DVCD = 'C' THEN '내부 서비스 수수료'
                         WHEN SP.PRD_DVCD = 'D' THEN '외부 서비스 수수료'
                         WHEN SP.PRD_DVCD = 'E' THEN '기타 수수료'
                         ELSE 'SKIP' END) AS PRD_TYPE
                   ,SUM(NVL(DI_POM.PAY_CNT,0)) AS PAY_CNT
                   ,SUM(NVL(DI_POM.PAY_AMT,0)) AS PAY_AMT
              FROM
                  (SELECT DI.YMD
                         ,DI.MMDD
                         ,NVL(POM.PRD_DVCD,'SKIP') AS PRD_DVCD
                         ,NVL(POM.PAY_CNT,0) AS PAY_CNT
                         ,NVL(POM.PAY_AMT,0) AS PAY_AMT
                     FROM
                          (SELECT TO_CHAR(TO_DATE(YMD),'YYYYMMDD') AS YMD, TO_CHAR(TO_DATE(YMD),'MM.DD') AS MMDD
                             FROM DAY_INFO
                             /* 기간을 정해서 판매량을 추출할 수 있다 */
                            WHERE TO_CHAR(TO_DATE(YMD),'YYYYMMDD') BETWEEN #{startDt} AND #{endDt}
                          ) DI
                         ,(SELECT
                                   OD.PRD_DVCD
                                  ,PM.PAY_DT
                                  ,SUM(1) AS PAY_CNT
                                  ,SUM(NVL(PM.PAY_AMT,0)) AS PAY_AMT
                             FROM PAY_MST PM
                                  INNER JOIN ODR_DTL OD ON OD.ODR_NUM = PM.ODR_NUM
                                  LEFT OUTER JOIN (SELECT A.ODR_NUM, A.ODR_DTL_SEQ, A.TRANS_SPEC_NUM, A.SEQ_NO, A.JRNR_NUM, A.SAP_NO
                                                     FROM STTL_JRNL_DTL A
                                                    WHERE A.STTL_DIV_CD = 'S1'
                                                      AND A.JRNR_NUM = (SELECT MAX(JRNR_NUM)
                                                                          FROM STTL_JRNL_DTL B
                                                                         WHERE B.STTL_DIV_CD = A.STTL_DIV_CD
                                                                           AND B.ODR_NUM = A.ODR_NUM
                                                                           AND B.ODR_DTL_SEQ  = A.ODR_DTL_SEQ
                                                                           AND B.SEQ_NO = A.SEQ_NO)
                                                   ) SJD ON SJD.ODR_NUM = OD.ODR_NUM AND SJD.ODR_DTL_SEQ = OD.ODR_DTL_SEQ
                              WHERE PM.PAY_DT BETWEEN #{startDt} AND #{endDt}
                              AND NVL(PM.PAY_AMT,0) > 0
                              GROUP BY PM.PAY_DT, OD.PRD_DVCD
							/* union all을 사용해서  group by (특정 상품에 대한 조인이 쉽지않을 때 사용)*/
                           UNION ALL
                           
                           SELECT
                                   'BA16' AS PRD_DVCD
                                  ,FINALBUYCFDT AS PAY_DT
                                  ,SUM(1) AS PAY_CNT
                                  ,SUM(NVL(TO_NUMBER(COVINSAMT),0)) AS PAY_AMT
                             FROM HSVC_EW_INSUPRD
                             WHERE FINALBUYCFDT BETWEEN #{startDt} AND #{endDt}
                             AND NVL(TO_NUMBER(COVINSAMT),0) > 0
                             GROUP BY FINALBUYCFDT
                           ) POM
                    WHERE DI.YMD = POM.PAY_DT(+)
                  ) DI_POM,
                  (SELECT PRD_DVCD
                    FROM SL_PRD) SP
             WHERE SP.PRD_DVCD = DI_POM.PRD_DVCD(+)
             GROUP BY SP.PRD_TYPE
             ORDER BY SP.PRD_TYPE
          ) DI_POM_SP
     WHERE A.PRD_TYPE != 'SKIP'
</select>

 

 

우선 상품이 판매된 날짜와 상품 구분 값으로 SUM을 하게 됩니다

 

또 한,

상품 금액의 총합계를 구하여  상품별 총판매금액을 구하게 됩니다

 

상품	총판매금액
이용권	10000
상품구매	20000
내부 서비스 수수료	30000

 

 

그럼 쿼리를 분석하고 곱씹어보는 것도 공부가 되겠지요

그럼 오늘도 즐거운 공부 하셨길 바랍니다

그럼 다음 포스팅도 기대해주세요

 

우리의 대모험이 끝나기까지 말이지요