농부지기

☞  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 ;

☞   LAST_VALUE  

1. last_value(c1 ignore nulls) over(order by  C1)

1. Syntax

SELECT ANALYTIC_FUNCTION ( arguments )
       OVER ( [ Partition By 컬럼List ]
              [ Order By 컬럼List ]
              [ Windowing 절]
            )
  FROM 테이블 명;

1. Aynalytic Function : 함수명
2. OVER : 분석함수임을 나타낸다
3. Partition By : 계산 대상 그룹을 정한다.
4. Order By : 대상 그룹에 대한 정렬을 수행한다.
5. Windowing 절 : 분석함수의 대상이 되는 범위를 지정한다.
     - Order by 절에 종속적이다.
     - 기본 생략구문 : 정렬된 결과의 처음부터 현재 행까지
        [rows between unbounded preceding and current row]
 

2. Syntax

SELECT  {SUM | AVG | MAX | MIN | COUNT | STDDEV | VARIANCE | FIRST VALUE | LAST VALUE}
        ({<Value Expression1> | *}) 
         OVER ([PARTITION BY <Value Expression2>]
        ORDER BY <Value Expression3> [Collate Clause] [ASC | DESC] [NULLS FIRST | NULLS LAST]
        ROWS | RANGE
        {{UNBOUNDED PRECEDING | <Value Expression4> PRECEDING} | BETWEEN
          {UNBOUNDED PRECEDING | <Value Expression5> PRECEDING} | 
          AND {CURRENT ROW | VALUE Ecpression6> FOLLOWING}}

1. OVER : FROM, WHERE, GROUP BY, HAVING 절이 처리된 후에 적용되며, 함수를 적용하기 위한 행의
            정렬 기준 또는 대상 행 집합에 대한 윈도우 정의

2.  ROWS | RANGE : 윈도우 크기를 결정하기 위한 행 집합을 정의
    ① ROWS는 물리적인 단위에 의해 윈도우 크기 지정
    ② RANGE는 논리적인 상대 번지에 의해 윈도우 크기 지정

3. BETWEEN AND : 윈도우의 시작 위치와 마지막 위치 지정

4. UNBOUNDED PRECEDING : 윈도우의 시작 위치는 각 분할의 첫 번째 행

5. UNBOUNDED FOLLOWING : 윈도우의 마지막 위치는 각 분할의 마지막 행

첫 번째. joins, where조건절, GROUP BY, HAVING절

두 번째. Partiton 생성
         각각의 Partition의 ROW에 Anyalytic Function 적용

세 번째. ORDER BY
 

4. Analytic Function의 3요소

1. Result Set Partitions : query processing with analytic function의 1단계 수행결과를 column이나
  expression을 기준으로 grouping한 것, 1단계 수행결과 전체가 하나의 partition에 속할 수도 있고, 
  적은 rows를 가진 여러 개의 작은 partition으로 쪼개질 수도 있다. 그러나, 한 row는 반드시 하나의
  partition에 속한다.

2. (Sliding) Window : current row에 대한 analytic calculation 수행의 대상이 되는 row의 범위(range),
  window는 current row를 기준으로 하나의 partition 내에서 sliding하며, 반드시 starting row와 ending
  row를 가진다. window size는 partition 전체가 될 수도 있고 partition의 부분범위가 될 수도 있으나
  하나의 partition을 넘을 수는 없다.partition의 부분범위로서 window size를 정할 때는 physical
  number of rows로 정할 수도 있고 logical interval로 정할 수도 있다.

3. Current Row : 모든 Analytic Function의 적용은 항상 Partition내의 Current Row를 기준으로 
   수행된다.
  Current Row는 항상 Window의 Start와 End를 결정하는 기준(Reference Point)으로서 역할을 하므로
  Current Row가 없는 Window는 존재하지 않는다.

4. Analytic Function의 종류

1. Ranking Family : 대상 집합에 대하여 특정 컬럼(들) 기준으로 순위나 등급을 매기는 
   Analytic Function 류로서 다음과 같은 종류가 있다.
  - RANK(), DENSE_RANK(), CUME_DIST(), PERCENT_RANK(), NTILE(), ROW_NUMBER()

2. Window Aggregate Family : 현재 Row(Current Row)를 기준으로 지정된 윈도우(Window) 내의 로우들을
  대상으로 집단화(aggregate)를 수행하여 여러 가지 유용한 집계정보(Running Summary, Moving Average
  등)를 구하는 Analytic Function 류이며 다음과 같은 종류가 있다.
  - SUM, AVG, MIN, MAX, STDDEV, VARIANCE, COUNT, FIRST_VALUE, LAST_VALUE

3. Reporting Aggregate Family : 서로 다른 두 가지의 Aggregation Level을 비교하고자 하는 목적으로
  사용하는 Analytic Function으로 다음과 같은 종류가 있다.
  - SUM, AVG, MIN, MAX, COUNT, STDDEV, VARINCE
 
4. LEAD/LAG Family : 서로 다른 두 Row 값을 비교하기 위한 Analytic Function으로 LEAD와 LAG가 있다.

5. Analytic Function의 절

① PARTITION BY 구 : 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고, 하나 이상의 컬럼
    또는 표현 식에 의한 그룹으로 쿼리의 결과를 파티션한다. 이 구가 생략되면 단일그룹처럼 쿼리
    결과 집합이 처리된다.

② ORDER BY 구 : 하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고, 하나 이상의 컬럼 또는
     표현식을 기준으로 파티션 내의 데이터를 정렬한다. 표현식은 컬럼의 별칭 또는 위치를 나타내는
     숫자를 사용할 수 없다.
③ WINDOW 구
   ⓐ Window 구의 예약어
      ⊙ CURRENT ROW - 윈도우의 시작 위치 또는 마지막 위치가 현재 로우임을 지시하는 예약어
      ⊙ UNBOUNDED PRECEDING - 윈도우의 시작 위치가 Partition의 첫 번째 로우임을 지시하는 예약어
      ⊙ UNBOUNDED FOLLOWING - 윈도우의 마지막 위치가 Partition의 마지막 로우임을 지시하는 예약어
   ⓑ Physical Window - Physical Window Size는 Rows를 환산하여 표현한다.
   ⓒ Logical Window
      ⊙ Time Interval - Logical Window Size는 Time Interval로 환산하여 표현한다.
      ⊙ Value Range - Logical Window Size는 정렬된 순서에서 Current Value와 이전
                       Value들과의 차로 환산하여 표현한다

6. 각 절명 설명

 -- ROWS 절 : 물리적인 단위에 의해 윈도우 크기 지정
 -- RANGE절 : 논리적인 상대 번지에 의해 윈도우 크기 지정

 -- BETWEEN AND : 윈도우의 시작 위치와 마지막 위치 지정

 -- UNBOUNDED PRECEDING : 윈도우의 시작 위치는 각 분할의 첫 번째 행
 -- UNBOUNDED FOLLOWING : 윈도우의 마지막 위치는 각 분할 의 마지막 행

☞  [구문형식]

  SELECT ...
  FROM   ...
  MODEL [main]
        [ reference models ]
        [ PARTITION BY (<컬럼>) ]
        DIMENSION BY (<컬럼>)    
                         <------------ 로우를 식별할 기준이 되는 컬럼을 명시
        MEASURES (<컬럼>)                    
                         <------------ 새로 계산이 되는 컬럼 명시
               [ IGNORE NAV ] | [ KEEP NAV ]
        [ RULES
          <------------ RULES 절에는 MEASURES 절에서 명시한 컬럼들만 기술 할 수 있다.
                   [ UPSERT | UPDATE ]
                   [ AUTOMATIC ORDER | SEQUENTIAL ORDER ]
                   [ ITERATE (n) [UNTIL <CONDITION> ]
            ]
                   (<CELL_ASSIGNMENT > = <expression> ...) ;

☞ 예제

WITH MODEL_T AS
( SELECT '201101' TERM, 100000 SALARY FROM DUAL UNION ALL
  SELECT '201102' TERM, 112000 SALARY FROM DUAL UNION ALL
  SELECT '201103' TERM, 123000 SALARY FROM DUAL UNION ALL
  SELECT '201104' TERM, 131000 SALARY FROM DUAL UNION ALL
  SELECT '201105' TERM, 122000 SALARY FROM DUAL UNION ALL
  SELECT '201106' TERM, 140000 SALARY FROM DUAL UNION ALL
  SELECT '201107' TERM, 160000 SALARY FROM DUAL
) 
SELECT TERM, ORI_SAL, TAXES 
  FROM MODEL_T
MODEL
    DIMENSION BY (TERM) 
    MEASURES (SALARY ORI_SAL, SALARY TAXES )
RULES(
    ORI_SAL['200701'] = ORI_SAL['200701'] * 0.33,      -- 순서를 함 바꿔봤다.^^
    TAXES  ['200702'] = TAXES  ['200702'] * 0.33,
    TAXES  ['200703'] = TAXES  ['200703'] * 0.33,
    TAXES  ['200704'] = TAXES  ['200704'] * 0.33,
    TAXES  ['200705'] = TAXES  ['200705'] * 0.33,
    TAXES  ['200706'] = TAXES  ['200706'] * 0.33  )
ORDER BY 1

☞  

※ DIMENSION 컬럼이 TERM 이다. 
   즉, DIMENSION 컬럼의 값을 연관배열의 KEY 처럼 사용해서 로우를 식별한다.
   여기서는 '20070x' 라는 컬럼값으로 로우를 식별하고 있다. 

SELECT TERM
     , SAL 
  FROM model_t
MODEL
    DIMENSION BY (term )
    MEASURES (SALARY sal)
RULES(
   SAL['QTR1']   = SAL['200701']+SAL['200702']+SAL['200703'],
   SAL['TOTAL1'] = sum(sal)[term IN ('200701','200702') ]
   SAL['TOTAL2'] = sum(sal)[term IN ('200701','200702') ]
)
ORDER BY 1;

※ sum() 함수도 사용할 수 있는데 배열키값에 저런식으로도 사용이 가능.
※ MODEL 절을 사용할 경우 SELECT 문장의 컬럼명은 MEASURES 절에서 반드시 명시한
    것들만 올수 있다. 
    따라서 그룹쿼리를 사용하기전에 MODEL절을 사용하여 계산을 마친 결과셋을 인라인뷰로
    사용해야한다. 
    왜냐면 SELECT 구문의 컬럼명이 MODEL 절에 종속적이기 때문이다.

 WITH TOTAL_T AS (
    SELECT NO, CAMP_CODE, GUBUN
         , MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5, MONTH_6, MONTH_7,MONTH_8
         , MONTH_9, MONTH_10,MONTH_11,MONTH_12 
         , TOTAL 
      FROM DATA_1_T
    MODEL 
        DIMENSION BY (ID )
        MEASURES (TOTAL,MONTH_1, MONTH_2,MONTH_3, MONTH_4, MONTH_5,MONTH_6, MONTH_7
                 ,MONTH_8, MONTH_9,MONTH_10, MONTH_11,MONTH_12
                 ,ID NO,GUBUN,CAMP_CODE)
    RULES(
      TOTAL[ ANY ] = MONTH_1[cv(ID)] + MONTH_2[CV(ID)] + MONTH_3[CV(ID)] +MONTH_4[CV(ID)] + MONTH_5[CV(ID)] + MONTH_6[CV(ID)] + MONTH_7[CV(ID)] +MONTH_8[CV(ID)] + MONTH_9[CV(ID)] + MONTH_10[CV(ID)] + MONTH_11[CV(ID)] +MONTH_12[CV(ID)]
         )
)
 SELECT ROW_NUMBER() OVER (ORDER BY 1) RN1
      , ROW_NUMBER() OVER( PARTITION BY DTL_RGN_CODE ORDER BY GUBUN ) RN2
      , DECODE( ROW_NUMBER() OVER(PARTITION BY DTL_RGN_CODE ORDER BY GUBUN ) 
              , 1, DTL_RGN_CODE,' ') "그 룹"
      , NVL2(GUBUN,GUBUN,'합계') GUBUN
      , sum(MONTH_1) jan
      , sum(MONTH_2) feb
      , sum(MONTH_3) mar
      , sum(MONTH_4) apr
      , sum(MONTH_5) may
      , sum(MONTH_6) jun
      , sum(MONTH_7) jul
      , sum(total)   total
   from total_t a, CAMP_INFO_T b
  where a.CAMP_CODE = b.CAMP_CODE
  group by rollup(DTL_RGN_CODE,gubun) ;