1일부터 월말까지의 매출을 일별로 가로로 표시하고싶습니다. 0 4 443

by 초보 [SQL Query] [2022.01.24 14:42:17]


아래와 같이 SQL을 짰는데 FOR문에 반복되는 항목들을 넣어도 코드가 너무 길고 유지보수하기 힘들것같아서 질문드립니다.
혹시 아래같은 방법 말고 1일부터 월말까지 일자별 매출을 표시할 수 있는 방법이 있을까요? 아이디어가 떠오르지 않네요 ㅜㅜ..


WITH SHOP_DATA AS
 (SELECT INFO.DIVISION_CD,
         INFO.CLIENT_CD,
         MAX(INFO.CLIENTNICKNAME) CLIENTNICKNAME,
         MAX(INFO.shoptype) shoptype,
         MAX(INFO.shop_level) shop_level,
         MAX(INFO.client_owner) client_owner,
         MAX(INFO.user_cd) user_cd,
         MAX(INFO.cg_cd) cg_cd,
         MAX(INFO.shop_area) shop_area,
         Max(VIEW_shoptype_Name.CodeName) shoptype_Name,
         Max(VIEW_shop_level_Name.CodeName) shop_level_Name,
         Max(VIEW_user_cd_Name.CodeName) user_cd_Name,
         Max(VIEW_cg_Name.CodeName) cg_Name,
         Max(VIEW_shop_area_Name.CodeName) shop_area_Name
    FROM (SELECT DIVISION_CD, GU, CODE, CODENAME
            FROM COMM_CODE
           WHERE GU = 'SHTYP') VIEW_shoptype_Name,
         (SELECT DIVISION_CD, GU, CODE, CODENAME
            FROM COMM_CODE
           WHERE GU = 'SHOPL') VIEW_shop_level_Name,
         (SELECT INFO.DIVISION_CD DIVISION_CD,
                 INFO.User_cd CODE,
                 MAX(USER_NAME.NAME) CODENAME
            FROM COMM_CLIENTINFO INFO, comm_user USER_NAME
           WHERE INFO.User_cd IS NOT NULL
             AND INFO.client_gu = 'M'
             AND USER_NAME.DIVISION_CD(+) = INFO.DIVISION_CD
             AND USER_NAME.USER_CD(+) = INFO.User_cd
           GROUP BY INFO.DIVISION_CD, INFO.User_cd) VIEW_user_cd_Name,
         (SELECT DIVISION_CD    DIVISION_CD,
                 CLIENT_CD      CODE,
                 CLIENTNICKNAME CODENAME
            FROM COMM_CLIENTINFO
           WHERE CLIENT_GU = 'M') VIEW_cg_Name,
         (SELECT DIVISION_CD, GU, CODE, CODENAME
            FROM COMM_CODE
           WHERE GU = 'SHOPA') VIEW_shop_area_Name,
         COMM_CLIENTINFO INFO
   WHERE INFO.DIVISION_CD = '0'
     AND INFO.CLIENT_GU = 'M'
     AND VIEW_shoptype_Name.DIVISION_CD(+) = INFO.DIVISION_CD
     AND VIEW_shoptype_Name
  .CODE(+) = INFO.shoptype
     AND VIEW_shop_level_Name.DIVISION_CD(+) = INFO.DIVISION_CD
     AND VIEW_shop_level_Name.CODE(+) = INFO.shop_level
     AND VIEW_user_cd_Name.DIVISION_CD(+) = INFO.DIVISION_CD
     AND VIEW_user_cd_Name.CODE(+) = INFO.user_cd
     AND VIEW_cg_Name.DIVISION_CD(+) = INFO.DIVISION_CD
     AND VIEW_cg_Name.CODE(+) = INFO.cg_cd
     AND VIEW_shop_area_Name.DIVISION_CD(+) = INFO.DIVISION_CD
     AND VIEW_shop_area_Name.CODE(+) = INFO.shop_area
   GROUP BY INFO.DIVISION_CD, INFO.CLIENT_CD)


SELECT A.SHOP_CD,
       A.SHOP_NAME,
       SUM(DAY_01 + DAY_02 + DAY_03 + DAY_04 + DAY_05 + DAY_06 + DAY_07 +
           DAY_08 + DAY_09 + DAY_10 + DAY_11 + DAY_12 + DAY_13 + DAY_14 +
           DAY_15 + DAY_16 + DAY_17 + DAY_18 + DAY_19 + DAY_20 + DAY_21 +
           DAY_22 + DAY_23 + DAY_24 + DAY_25 + DAY_26 + DAY_27 + DAY_28 +
           DAY_29 + DAY_30 + DAY_31) TOT,
       SUM(DAY_01) DAY_01,
       SUM(DAY_02) DAY_02,
       SUM(DAY_03) DAY_03,
       SUM(DAY_04) DAY_04,
       SUM(DAY_05) DAY_05,
       SUM(DAY_06) DAY_06,
       SUM(DAY_07) DAY_07,
       SUM(DAY_08) DAY_08,
       SUM(DAY_09) DAY_09,
       SUM(DAY_10) DAY_10,
       SUM(DAY_11) DAY_11,
       SUM(DAY_12) DAY_12,
       SUM(DAY_13) DAY_13,
       SUM(DAY_14) DAY_14,
       SUM(DAY_15) DAY_15,
       SUM(DAY_16) DAY_16,
       SUM(DAY_17) DAY_17,
       SUM(DAY_18) DAY_18,
       SUM(DAY_19) DAY_19,
       SUM(DAY_20) DAY_20,
       SUM(DAY_21) DAY_21,
       SUM(DAY_22) DAY_22,
       SUM(DAY_23) DAY_23,
       SUM(DAY_24) DAY_24,
       SUM(DAY_25) DAY_25,
       SUM(DAY_26) DAY_26,
       SUM(DAY_27) DAY_27,
       SUM(DAY_28) DAY_28,
       SUM(DAY_29) DAY_29,
       SUM(DAY_30) DAY_30,
       SUM(DAY_31) DAY_31
  FROM (SELECT A.SHOP_CD SHOP_CD,
               B.CLIENTNICKNAME SHOP_NAME,
               0 TOT,
               SUM(A.AMT) DAY_01,
               0 DAY_02,
               0 DAY_03,
               0 DAY_04,
               0 DAY_05,
               0 DAY_06,
               0 DAY_07,
               0 DAY_08,
               0 DAY_09,
               0 DAY_10,
               0 DAY_11,
               0 DAY_12,
               0 DAY_13,
               0 DAY_14,
               0 DAY_15,
               0 DAY_16,
               0 DAY_17,
               0 DAY_18,
               0 DAY_19,
               0 DAY_20,
               0 DAY_21,
               0 DAY_22,
               0 DAY_23,
               0 DAY_24,
               0 DAY_25,
               0 DAY_26,
               0 DAY_27,
               0 DAY_28,
               0 DAY_29,
               0 DAY_30,
               0 DAY_31
         FROM ONLINE_AMT A, SHOP_DATA B
         WHERE A.DIVISION_CD = '0'
           AND A.SHOP_CD = B.CLIENT_CD
           AND A.SALE_DT = 20210101
         GROUP BY A.SHOP_CD, B.CLIENTNICKNAME


        UNION ALL


        SELECT A.SHOP_CD SHOP_CD,
               B.CLIENTNICKNAME SHOP_NAME,
               0 TOT,
               0 DAY_01,
               SUM(A.AMT) DAY_02,
               0 DAY_03,
               0 DAY_04,
               0 DAY_05,
               0 DAY_06,
               0 DAY_07,
               0 DAY_08,
               0 DAY_09,
               0 DAY_10,
               0 DAY_11,
               0 DAY_12,
               0 DAY_13,
               0 DAY_14,
               0 DAY_15,
               0 DAY_16,
               0 DAY_17,
               0 DAY_18,
               0 DAY_19,
               0 DAY_20,
               0 DAY_21,
               0 DAY_22,
               0 DAY_23,
               0 DAY_24,
               0 DAY_25,
               0 DAY_26,
               0 DAY_27,
               0 DAY_28,
               0 DAY_29,
               0 DAY_30,
               0 DAY_31
          FROM ONLINE_AMT A, SHOP_DATA B
         WHERE A.DIVISION_CD = '0'
           AND A.SHOP_CD = B.CLIENT_CD
           AND A.SALE_DT = 20210102
         GROUP BY A.SHOP_CD, B.CLIENTNICKNAME


        UNION ALL

        ...

        31일까지 반복

 

by 마농 [2022.01.24 15:42:11]
SELECT shop_cd
     , shop_name
     , SUM(amt) tot
     , NVL(SUM(DECODE(dd, '01', amt)), 0) day_01
     , NVL(SUM(DECODE(dd, '02', amt)), 0) day_02
     , NVL(SUM(DECODE(dd, '03', amt)), 0) day_03
     , NVL(SUM(DECODE(dd, '04', amt)), 0) day_04
     , NVL(SUM(DECODE(dd, '05', amt)), 0) day_05
     , NVL(SUM(DECODE(dd, '06', amt)), 0) day_06
     , NVL(SUM(DECODE(dd, '07', amt)), 0) day_07
     , NVL(SUM(DECODE(dd, '08', amt)), 0) day_08
     , NVL(SUM(DECODE(dd, '09', amt)), 0) day_09
     , NVL(SUM(DECODE(dd, '10', amt)), 0) day_10
     , NVL(SUM(DECODE(dd, '11', amt)), 0) day_11
     , NVL(SUM(DECODE(dd, '12', amt)), 0) day_12
     , NVL(SUM(DECODE(dd, '13', amt)), 0) day_13
     , NVL(SUM(DECODE(dd, '14', amt)), 0) day_14
     , NVL(SUM(DECODE(dd, '15', amt)), 0) day_15
     , NVL(SUM(DECODE(dd, '16', amt)), 0) day_16
     , NVL(SUM(DECODE(dd, '17', amt)), 0) day_17
     , NVL(SUM(DECODE(dd, '18', amt)), 0) day_18
     , NVL(SUM(DECODE(dd, '19', amt)), 0) day_19
     , NVL(SUM(DECODE(dd, '20', amt)), 0) day_20
     , NVL(SUM(DECODE(dd, '21', amt)), 0) day_21
     , NVL(SUM(DECODE(dd, '22', amt)), 0) day_22
     , NVL(SUM(DECODE(dd, '23', amt)), 0) day_23
     , NVL(SUM(DECODE(dd, '24', amt)), 0) day_24
     , NVL(SUM(DECODE(dd, '25', amt)), 0) day_25
     , NVL(SUM(DECODE(dd, '26', amt)), 0) day_26
     , NVL(SUM(DECODE(dd, '27', amt)), 0) day_27
     , NVL(SUM(DECODE(dd, '28', amt)), 0) day_28
     , NVL(SUM(DECODE(dd, '29', amt)), 0) day_29
     , NVL(SUM(DECODE(dd, '30', amt)), 0) day_30
     , NVL(SUM(DECODE(dd, '31', amt)), 0) day_31
  FROM (SELECT a.shop_cd
             , b.clientnickname shop_name
             , SUBSTR(a.sale_dt, 7, 2) dd
             , a.amt
          FROM online_amt a
             , shop_data  b
         WHERE a.division_cd = '0'
           AND a.shop_cd = b.client_cd
           AND a.sale_dt BETWEEN '20210101' AND '20210131'
        )
 GROUP BY shop_cd, shop_name
;

 


by 마농 [2022.01.24 15:47:40]

SHOP_DATA 에서 그룹바이가 필요한지 모르겠구요. -> 필요 없을 듯?
SHOP_DATA 자체가 필요한지 모르겠네요. -> SHOP_DATA 대신 COMM_CLIENTINFO 직접 조인 가능.


by 초보 [2022.01.24 16:16:29]

답변 감사합니다! 매 번 올때마다 좋은 가르침 주셔서 정말 감사합니다.


by 마농 [2022.01.24 17:38:26]
-- comm_clientinfo 직접 조인 --
SELECT shop_cd
     , shop_name
     , SUM(amt) tot
     , NVL(SUM(DECODE(dd, '01', amt)), 0) day_01
       -- 중략 --
     , NVL(SUM(DECODE(dd, '31', amt)), 0) day_31
  FROM (SELECT a.shop_cd
             , b.clientnickname shop_name
             , SUBSTR(a.sale_dt, 7, 2) dd
             , a.amt
          FROM online_amt a
             , comm_clientinfo b
         WHERE a.division_cd = b.division_cd
           AND a.shop_cd     = b.client_cd
           AND b.division_cd = '0'
           AND b.client_gu   = 'M'
           AND a.sale_dt BETWEEN '20210101' AND '20210131'
        )
 GROUP BY shop_cd, shop_name
;

 

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입