농부지기

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

☞  

PARTITION BY 구
   하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 하나 이상의 컬럼 또는 
   표현식에 의한 그룹으로 쿼리의 결과를 파티션한다. 
   이 구가 생략되면 단일 그룹처럼 쿼리 결과 집합이 처리된다. 
ORDER BY 구
   하나 이상의 컬럼 또는 적합한 표현식이 사용될 수 있고 
   하나 이상의 컬럼 또는 표현식을 기준으로 파티션 내의 데이터를 정렬한다. 
   표현식은 컬럼의 별칭 또는 위치를 나타내는 숫자를 사용할 수 없다. 

☞ 1. 문제

  자료 : 기준일자 영업유무
         19810101 Y
         19810103 N
         19810104 Y
         19810105 N
         19810106 N
         19810107 N
         19810108 N
         19810109 N
         19810111 Y
    일 경우

결과 : 기준일자로 정렬해서 위에서 부터 Y 갯수를 COUNT 하기
             기준일자 영업유무  Y갯수
             19810101 Y         1
             19810103 N         1
             19810104 Y         2
             19810105 N         2
             19810106 N         2
             19810107 N         2
             19810108 N         2
             19810109 N         2
             19810111 Y         3;

WITH T AS ( 
     SELECT '19810101' DAT, 'Y' YN FROM DUAL UNION ALL
     SELECT '19810103' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810104' DAT, 'Y' YN FROM DUAL UNION ALL
     SELECT '19810105' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810106' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810107' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810108' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810109' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810111' DAT, 'Y' YN FROM DUAL 
) 
  SELECT DAT, YN, COUNT( DECODE( YN, 'Y', 1 ) ) OVER( ORDER BY DAT ASC ) GRP 
    FROM T  ;   

☞  문제2

     자료 : 기준일자 영업유무
            19810101 Y
            19810103 N
            19810104 Y
            19810105 N
            19810106 N
            19810107 N
            19810108 N
            19810109 N
            19810111 Y
    일 경우

    결과 : 기준일자로 정렬해서 위에서 휴무일자를 제외한 영업일자를 찾아오기
             기준일자 영업유무  영업일자
             19810101 Y         19810101
             19810103 N         19810104
             19810104 Y         19810104  
             19810105 N         19810111 
             19810106 N         19810111    
             19810107 N         19810111 
             19810108 N         19810111 
             19810109 N         19810111 
             19810111 Y         19810111;

WITH T AS ( 
     SELECT '19810101' DAT, 'Y' YN FROM DUAL UNION ALL
     SELECT '19810103' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810104' DAT, 'Y' YN FROM DUAL UNION ALL
     SELECT '19810105' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810106' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810107' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810108' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810109' DAT, 'N' YN FROM DUAL UNION ALL
     SELECT '19810111' DAT, 'Y' YN FROM DUAL UNION ALL
     SELECT '19810112' DAT, 'N' YN FROM DUAL
) 
SELECT DAT, YN, GRP, MAX( DAT ) OVER( PARTITION BY GRP ) A_DAT 
FROM ( 
       SELECT DAT, YN, COUNT( DECODE( YN, 'Y', 1 ) ) OVER( ORDER BY DAT DESC ) GRP 
         FROM T 
) 
ORDER BY DAT ;

☞  

1. GROUPING(name)
  - name 컬럼으로 GROUP BY에 의해서 SUM된 결과는 0, CUBE, ROLLUP의 의해 합산된 결과는 1
2. GROUPING_ID(name1, name2)
  - GROUPING(name1) || GROUPING(name2)의 결과를 2진수로 판단해서 그 값을 10진수로 환산한 값
  - 예) 00 => 0
        01 => 1
        10 => 2
        11 => 3
3. GROUP_ID()
   - ROLLUP이나 CUBE에 의한 결과에서 중복된 자료가 존재시 구별가능하다.
   - 필요이유는 중복자료가 여러번 나올 수 있기 때문에 필터링하는데 유용하다.
   - 만약 n이 특정 그룹핑에서 중복이 존재한다면, GROUP_ID는 0~n-1 범위의 수를 반환한다.

☞

WITH salary AS
(
    SELECT '총무부' DEPT, '저미주' NAME, '01' SMONTH, 100000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '02' SMONTH, 115000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '저미주' NAME, '03' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '07' SMONTH, 510000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '08' SMONTH, 450000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '강모리' NAME, '09' SMONTH, 610000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '05' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '06' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '07' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '총무부' DEPT, '구하라' NAME, '08' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '01' SMONTH, 200000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '02' SMONTH, 210000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '03' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '구하라' NAME, '04' SMONTH, 230000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '01' SMONTH, 150000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '02' SMONTH, 160000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '이주리' NAME, '03' SMONTH, 170000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '04' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '05' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '06' SMONTH, 112000 SALARY FROM dual UNION ALL
    SELECT '인사부' DEPT, '저미주' NAME, '07' SMONTH, 112000 SALARY FROM dual
)
SELECT DEPT, NAME, COUNT(SMONTH) CNT, SUM(SALARY) SALARY
     , GROUPING(DEPT) G_DEPT, GROUPING(NAME) G_NAME
     , GROUPING(DEPT) + GROUPING(NAME) G_SUM
     , GROUPING_ID(DEPT, NAME) GING_ID
     , GROUP_ID()              G_ID
  FROM salary
 WHERE SMONTH BETWEEN '01' AND '12'
 GROUP BY CUBE(DEPT, NAME)   --GROUP BY ROLLUP(DEPT, NAME) 
 ORDER BY GROUPING(DEPT), DEPT, GROUPING(NAME), NAME ;

   DEPT    NAME     CNT    SALARY     G_DEPT  G_NAME  G_SUM  GING_ID  G_ID
 -------------------------------------------------------------------------
   인사부   구하라    4    870000       0         0      0       0      0
   인사부   이주리    3    480000       0         0      0       0      0
   인사부   저미주    4    448000       0         0      0       0      0
   인사부            11   1798000       0         1      1       1      0
   총무부   강모리    3   1570000       0         0      0       0      0
   총무부   구하라    4    840000       0         0      0       0      0
   총무부   저미주    3    327000       0         0      0       0      0
   총무부            10   2737000       0         1      1       1      0
            강모리    3   1570000       1         0      1       2      0
            구하라    8   1710000       1         0      1       2      0
            이주리    3    480000       1         0      1       2      0
            저미주    7    775000       1         0      1       2      0
                     21   4535000       1         1      2       3      0