농부지기

-- 정의   : PIVOT()함수를 사용할때 컬럼갯수를 알아야만 한다.
--          결과 컬럼을 정의해 주지 않으면 PIVOT()함수를 사용할 수 없다.
--          
--          월갯수(from~to)를 알수 없을 경우에는 PIVOT()함수를 사용할 없게 된다.
--          
--          그런데, Dynamic SQL 문장을 사용하게 되면 SQL 문장을 작성할 때는 컬럼을 정의하지 않고
--          PIVOT()함수 정의 된 SQL 문장을 실행하기 전에  
--          월 갯수를 파악하여  PIVOT()함수에 추가한다.
--          (즉, myBatis 에서  FOR PLAN_YM IN ($COLUMN_LIST$)  와 같이 함)

SELECT (CASE WHEN GRP_ID   = '0' THEN PLAN_YM
             WHEN MM_COUNT <  6  THEN '소계'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '0' THEN '상반기'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '1' THEN '하반기'
             WHEN MM_COUNT = 12                             THEN '년계'
             ELSE '총계' END) AS PLAN_YM
     , FIRST_SECOND_TYPE         --0:상반기,  1:하반기
     , MM_COUNT                  --1:Table 컬럼 sum()값.  2이상:소계, 합계 컬럼임
     , GRP_ID                    --GROUPING_ID()함수 결과 값.
     , COL_ROWNUM                --컬럼 ROWNUM
  FROM (
        SELECT YEAR_DATA
             , PLAN_YM
             , FIRST_SECOND_TYPE
             , COUNT(FIRST_SECOND_TYPE) AS MM_COUNT
             , GROUPING_ID(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM) AS GRP_ID
          FROM (
                --2013.10 ~ 201502 월까지 존재 하는 테이블일 경우
                WITH YY_MM AS
                    ( SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201310', 'YYYYMM'), ROWNUM), 'YYYYMM') AS PLAN_YM
                        FROM DUAL
                      CONNECT BY LEVEL <= 17 )
                SELECT SUBSTR(B.PLAN_YM, 1,4)                   AS YEAR_DATA
                     , B.PLAN_YM                                AS PLAN_YM
                     , FLOOR ((SUBSTR(B.PLAN_YM,5,2) - 1) / 6)  AS FIRST_SECOND_TYPE
                  FROM YY_MM B
               )
        GROUP BY ROLLUP(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM)
       )
 WHERE GRP_ID IN ('0', '1', '7') 
    OR (MM_COUNT = 12 AND GRP_ID = 3) ;

-- 정의   : 반복 컬럼이 1 ~ 3개 일 경우
--          PIVOT 함수 사용중  시작년월~종료년월 까지 자료를 조회 할 경우
-- 사용처 : PIVOT 조회결과를 화면에 보여 주려면  화면.Grid.Header를 dynamic하게 그려줘야 된다.
--          이때 고정컬럼을 항상 정해졌기 때문에 문제가 없고
--          반복컬럼과 중간에 소계, 합계가 들어 갈 경우 
--          아래 SQL문 결과를 통해서 Grid.Header를 그려줄 수 있다.

SELECT (CASE WHEN GRP_ID   = '0' THEN PLAN_YM
             WHEN MM_COUNT <  6  THEN '소계'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '0' THEN '상반기'
             WHEN MM_COUNT =  6 AND FIRST_SECOND_TYPE = '1' THEN '하반기'
             WHEN MM_COUNT = 12                             THEN '년계'
             ELSE '총계' END) AS PLAN_YM
       --실 컬럼명은 변수로 받아야 됨
     , DECODE(COL_GUBN_GRP, 1, '비중', 2, '매출액', 3, '이익금') AS COL_NAME
     , FIRST_SECOND_TYPE         --0:상반기,  1:하반기
     , MM_COUNT                  --1:Table 컬럼 sum()값.  2이상:소계, 합계 컬럼임
     , GRP_ID                    --GROUPING_ID()함수 결과 값.
     , COL_ROWNUM                --컬럼 ROWNUM
     , COL_GUBN_GRP              --반복컬럼이 1개 이상일 경우 순서
  FROM (
        WITH YY_MM AS
           (
            SELECT YEAR_DATA
                 , PLAN_YM
                 , FIRST_SECOND_TYPE
                 , COUNT(FIRST_SECOND_TYPE) AS MM_COUNT
                 , GROUPING_ID(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM) AS GRP_ID
              FROM (SELECT SUBSTR(A.PLAN_YM, 1,4)                     AS  YEAR_DATA
                         , A.PLAN_YM                                  AS PLAN_YM
                         , FLOOR ((SUBSTR(A.PLAN_YM,5,2) - 1) / 6)    AS  FIRST_SECOND_TYPE
                      FROM ( --2013.10 ~ 201502 월까지 존재 하는 테이블
                             SELECT TO_CHAR(ADD_MONTHS(TO_DATE('201310', 'YYYYMM'), ROWNUM), 'YYYYMM') AS PLAN_YM
                               FROM DUAL
                            CONNECT BY LEVEL <= 17 
                           ) A
                   )
            GROUP BY ROLLUP(YEAR_DATA, FIRST_SECOND_TYPE, PLAN_YM)
           )
        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             --** 맨 앞쪽  1은  변수로 받아야 됨. (반복컬럼 갯수 임)
             , DECODE(3, 1, ROWNUM, 2, (ROWNUM * 2) - 1, (ROWNUM * 3) - 2) AS COL_ROWNUM   
             , 1      AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE  GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3)

        UNION ALL

        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             --** 맨 앞쪽  2는  변수로 받아야 됨. (반복컬럼 갯수 임)
             , DECODE(3, 2, (ROWNUM * 2) - 0, (ROWNUM * 3) - 1) AS COL_ROWNUM  
             , 2      AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) ) 
           AND 2 <= 2    --** 오른쪽  2는  변수로 받아야 됨. (반복컬럼 갯수 임)

       UNION ALL

        SELECT PLAN_YM
             , FIRST_SECOND_TYPE
             , MM_COUNT
             , GRP_ID
             , (ROWNUM * 3) - 0 AS COL_ROWNUM  
             , 3                AS COL_GUBN_GRP
          FROM  YY_MM
         WHERE ( GRP_ID IN ('0', '1', '7') OR (MM_COUNT = 12 AND GRP_ID = 3) ) 
           AND 3 = 3    --** 오른쪽  3은  변수로 받아야 됨. (반복컬럼 갯수 임)
       )
 ORDER BY COL_ROWNUM ;
 

1. 정의 : 매장별, 월별 이익금, 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼을 기술하지 않은 경우
           2. PIVOT 컬럼이 두 개 인 경우
           3. SUM() 한 후 컬럼을 지정 (이때 지정하지 않으면  오류 발생)

WITH SALE_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
          FROM SALE_DATA
        )
PIVOT (SUM(PROFIT_AMT)   AS PT_AMT, SUM(SALE_AMT) AS SA_AMT   -- <-- 이곳에 컬럼 지정
                       FOR SALE_YM IN ('201401'               -- <-- 이곳에 컬럼을 지정 하지 않은 경우
                                     , '201402'
                                     , '201403'
                                     , '201404'
                                     , '201405'
                                     , '201406'
                                     )
      );
 

결과 컬럼이 : 년월 과 AS 후 컬럼이 결합되어  이익금컬럼과  매출액컬럼이 쌍으로 해서 반복되어 나온다..
                

2. 정의 : 매장별, 월별 이익금, 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼을 기술 한 경우
           2. PIVOT 컬럼이 두 개 인 경우
           3. SUM() 한 후 컬럼을 지정 (이때 지정하지 않으면  오류 발생)

WITH SALE_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
             , SALE_AMT
          FROM SALE_DATA
        )
PIVOT (SUM(PROFIT_AMT)   AS PT_AMT, SUM(SALE_AMT) AS SA_AMT   -- <-- 이곳에 컬럼 지정
                       FOR SALE_YM IN ('201401' AS IDX1       -- <-- 이곳에 컬럼 지정
                                     , '201402' AS IDX2
                                     , '201403' AS IDX3
                                     , '201404' AS IDX4
                                     , '201405' AS IDX5
                                     , '201406' AS IDX6
                                     )
      );
 

1. 정의 : 매장별, 월별 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼을 기술하지 않은 경우
           2. PIVOT 컬럼이 한 개 인 경우
           3. SUM() 한 후 컬럼을 지정하지 않은 경우

WITH MONTH_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
          FROM MONTH_DATA
        )
PIVOT (SUM(PROFIT_AMT)                                   -- <-- 이곳에 컬럼을 지정 하지 않은 경우
                       FOR SALE_YM IN ('201401'          -- <-- 이곳에 컬럼을 지정 하지 않은 경우
                                     , '201402'
                                     , '201403'
                                     , '201404'
                                     , '201405'
                                     , '201406'
                                     )
      );

2. 정의 : 매장별, 월별 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼 기술 한 경우
           2. PIVOT 컬럼이 한 개 인 경우

WITH MONTH_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
          FROM MONTH_DATA
        )
PIVOT (SUM(PROFIT_AMT)                                                -- <-- 이곳에 컬럼을 지정 하지 않은 경우
                       FOR SALE_YM IN ('201401' AS PI_201401          -- <-- 이곳에 컬럼을 지정 한 경우
                                     , '201402' AS PI_201402
                                     , '201403' AS PI_201403
                                     , '201404' AS PI_201404
                                     , '201405' AS PI_201405
                                     , '201406' AS PI_201406
                                     )
      );
 

3. 정의 : 매장별, 월별 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼을 기술하지 않은 경우
           2. PIVOT 컬럼이 한 개 인 경우
           3. SUM() 한 후 컬럼을 지정하지 않은 경우

WITH MONTH_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
          FROM MONTH_DATA
        )
PIVOT (SUM(PROFIT_AMT)  AS PT_AMT                        -- <-- 이곳에 컬럼을 지정 한 경우 
                       FOR SALE_YM IN ('201401'          -- <-- 이곳에 컬럼을 지정 하지 않은 경우
                                     , '201402'
                                     , '201403'
                                     , '201404'
                                     , '201405'
                                     , '201406'
                                     )
      );
 

4. 정의 : 매장별, 월별 매출액 조회(2014.01 ~ 2014.06)

  - 참고 : 1. PIVOT 년월 에  컬럼 기술 한 경우
           2. PIVOT 컬럼이 한 개 인 경우
           3. SUM() 한 후 컬럼을 지정한 경우

WITH MONTH_DATA AS
    ( SELECT DECODE(MOD(ROWNUM, 3), 0, '강남점', 1, '역삼점', 2, '평촌점') AS SHOP_NM
           , TO_CHAR(ADD_MONTHS(TO_DATE('201401', 'YYYYMM'), ROUND(dbms_random.value(0,5),0)), 'YYYYMM') AS SALE_YM
           , ROUND(dbms_random.value(   1, 5000),0) AS PROFIT_AMT
           , ROUND(dbms_random.value(5000,10000),0) AS SALE_AMT
        FROM DUAL
      CONNECT  BY LEVEL <= 25 )
SELECT *
  FROM (
        SELECT SHOP_NM
             , SALE_YM
             , PROFIT_AMT
          FROM MONTH_DATA
        )
PIVOT (SUM(PROFIT_AMT)  AS PT_AMT                                     -- <-- 이곳에 컬럼을 지정 한 경우
                       FOR SALE_YM IN ('201401' AS PI_201401          -- <-- 이곳에 컬럼을 지정 한 경우
                                     , '201402' AS PI_201402
                                     , '201403' AS PI_201403
                                     , '201404' AS PI_201404
                                     , '201405' AS PI_201405
                                     , '201406' AS PI_201406
                                     )
      );
 

결과 컬럼이 : 지정한 명칭 과 AS 컬럼이 결합되어 결과 컬럼이 만들어 진다.

☞ Windowing절은 반드시 OVER()함수 내부에 ORDER BY 가 존재 해야 됨
   Windowing절인 ROWS를 사용 앞쪽 특정ROW~ 뒤쪽 특정ROW까지의 범위를 지정한다.
   P_SUM_AMT  : PRECEDING만 사용 시 앞1ROW~현재ROW까지 SUM()이 수행 됨
   PF_SUM_AMT : 앞1ROW 부터 ~ 뒤1ROW까지 범위지정 후 SUM()이 수행 됨
   F_SUM_AMT  : FOLLOWING만 사용 시 오류 발생(이유 모름)

WITH T AS
(             SELECT 'GS몰'   AS SHOP, '201405' AS YM, 22000 AS AMT FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201409'      , 55000        FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201412'      , 55000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 10000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 20000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 11000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201403'      , 22000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201405'      , 33000        FROM DUAL
)
SELECT SHOP, YM
     , SUM(AMT) OVER(ORDER BY SHOP, YM 
                     ROWS 1 PRECEDING
                     )                                          AS P_SUM_AMT
     , SUM(AMT) OVER(ORDER BY SHOP, YM 
                     ROWS BETWEEN1 PRECEDING AND 1 FOLLOWING
                     )                                          AS PF_SUM_AMT
--     , SUM(AMT) OVER(ORDER BY SHOP, YM 
--                     ROWS  1 FOLLOWING
--                     )                                        AS F_SUM_AMT
  FROM T
 ORDER BY SHOP, YM ;

☞  YM_SEQ_SUM_AMT : OVER()함수안에 ORDER BY 를 사용하면
                     YM(년월)을 정렬한 후 첫번째ROW~ 현재ROW 까지의 SUM()이 수행 됨
                     중요 : 이 때 SHOP, YM으로 정렬 후 같은 SHOP, YM이 존재 시 같은 순서로 본다.
    YM_SEQ_SUM_AMT : OVER()함수안에 ORDER BY와 ROW를 사용하면
                     SHOP, YM(년월)을 정렬한 후 첫번째ROW~ 현재ROW 까지의 SUM()이 수행 됨
                     중요 : 이 때 위 SQL 문과 다른점은 CURRENT ROW를 했기 때문에 같은 
                            SHOP, YM도 각각 ROW로 인식 한다.
    ROWS절 : 물리적인 단위에 의해 윈도우 크기 지정
 

WITH T AS
(             SELECT 'GS몰'   AS SHOP, '201405' AS YM, 22000 AS AMT FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201409'      , 55000        FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201412'      , 55000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 10000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 20000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 11000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201403'      , 22000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201405'      , 33000        FROM DUAL
)
SELECT SHOP, YM
     , SUM(AMT) OVER(ORDER BY SHOP, YM ASC) AS YM_SEQ_SUM_AMT
     , SUM(AMT) OVER(ORDER BY SHOP, YM ASC
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                     ) AS YM_SEQ_SUM_AMT
  FROM T
 ORDER BY SHOP, YM ;

☞ SUM() 집계함수를 사용하게 되면 GROUP BY를 반드시 사용해야 된다.

WITH T AS
(             SELECT 'GS몰'   AS SHOP, '201405' AS YM, 22000 AS AMT FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201409'      , 55000        FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201412'      , 55000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 10000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 20000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 11000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201403'      , 22000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201405'      , 33000        FROM DUAL
)
SELECT SHOP, YM
     , SUM(AMT) AS SUM_AMT
  FROM T
 GROUP BY SHOP, YM
 ORDER BY SHOP, YM ;

☞ OVER() 분석 함수를 사용하게 되면 GROUP BY절을 사용해도 되고, 안해도 동일함
   OVER() 함수 괄호한에 아무 절도 없으면 모든 결과 레코드를 대상으로 SUM()이 수행 됨

WITH T AS
(             SELECT 'GS몰'   AS SHOP, '201405' AS YM, 22000 AS AMT FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201409'      , 55000        FROM DUAL
    UNION ALL SELECT 'GS몰'          , '201412'      , 55000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 10000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 20000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201401'      , 11000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201403'      , 22000        FROM DUAL
    UNION ALL SELECT '양동점'        , '201405'      , 33000        FROM DUAL
)
SELECT SHOP, YM
     , SUM(AMT) OVER()                AS SUM_AMT
  FROM T
 --GROUP BY SHOP, YM, AMT
 ORDER BY SHOP, YM ;