210524 oracle - 분석함수

1. 분석 함수 종류를 살펴보고 활용하자
2. join을 살펴보고 구현해보자

============================================================================

분석함수를 사용하게 된 이유
  - RDBS 상에서 컬럼과 컬럼 연산, 비교, 연결은 쉬운 반면 
    행과 행간의 관계를 정의하거나 비교, 연산하는 것을 하나의 SQL로 처리하기 힘든점을 해결할려고

  - 그 전에는 프로그램을 작성, 인라인 뷰를 이용해서 복잡한 SQL문 작성

  - 분석함수를 도입해서 행간의 연산을 원할하게 연동한다. 단, 중첩사용은 불가능
    서브쿼리에는 사용 가능

[형식]
    SELECT Analytic_Function(arguments) OVER
                               ([PARTITION By 절] [ORDER BY 절] [WINDOWING 절])
    FROM 테이블명;

  - Analytic_Function : AVG, COUNT, LAG, LEAD, MAX, MIN, RANK, RATIO_TO_REPORT
                             ROW_NUMBER, SUM 등
                             arguments 0 ~ 3개 까지만 올 수 있다. 
                             *는 COUNT(*)만 허용. DISTINCT는 해당 집계 함수가 허용할 때만 가능

  - OVER : 해당 함수가 쿼리 결과 집합에 따라서 적용되는 지시어이다.
              FROM, WHERE, GROUP BY, HAVING 이후에 계산된다.
              SELECT, ORDER BY 구문 뒤에 사용할 수 있다.

  - PARTITION By : 쿼리 결과를 < expr_list > 별로 그룹핑한다. 생략시에는 하나의 그룹으로 리턴

  - ORDER BY : ORDER BY < expr_list > [ (ASC | DESC), (NULL FIRST | LAST) ]
                    < expr_list >에서는 별칭이나 숫자를 사용할 수 없다.

  - WINDOWING : ROWS, RANGE가 일단 핵심
                        ROWS는 물리적인 단위(ROW위치)고 RANGE는 논리적인 단위(ROW값)이다.
                        ROWS[RANGE] BETWEEN start_point AND end_point

  - start_point : 그룹별 시작점을 의미 UNBOUNDED PRECEDING, CURRENT ROW,
                     value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.

  - end_point : 그룹별 시작점을 의미 UNBOUNDED FOLLOWING, CURRENT ROW,
                     value_expr PRECEDING or value_expr FOLLOWING 이 올 수 있다.


분석함수의 장점
  - JOIN이나 프로그램의 OVERHEAD를 줄임 
    (QUERY SPEED의 향상된 SELF-JOIN, 절차적 로직으로 표현하는 것을 NATIVE SQL에서 
      바로 적용할 수 있도록 JOIN이나 프로그램의 OVERHEAD를 줄임)

  - 간결한 SQL로 복잡한 분석 작업을 수행가능 (유지보수가 간편하고 생산성 향상)

  - 이해 및 활용이 용이 (기존 SQL - syntax를 그대로 쓰기 때문에 ANSI SQL로 채택된다)

============================================================================

Ranking Family 특징
  - 대상집합에 대해서 특정 컬럼들을 기준으로 순위나 등급을 부여한다.
  - 오름차순, 내림차순 가능
  - NULL 은 순위의 가장 처음 또는 마지막으로 강제 처리된다.
  - Rank function은 각 PARTITOIN마다 초기화 된다.
  - 순위 또는 등급은 GROUP BY, CUBE, ROLLUP절 마다 초기화가 된다.

RANK() : 각 로우마다 순위를 매긴다.
            P -> O -> 1부터 시작하여 동일한 값은 동일한 순위를 가지고,
            동일한 순위의 수만큼 다음 순위는 건너 뛴다.

DENSE_RANK() : RANK와 다르게 동률있는 수만큼 순위를 미루지 않는다 ex) 1, 1, 2등

CUME_DIST() : CUMULATIVE DISTRIBUTION FUNCTION
  - PARTITION 나누어진 블럭별로 각 ROW를 ORDER BY에 명시된 순서대로 정렬한 후
    그룹별 상대적인 위치(누적된 분산정보)를 구한다

  - 상대적인 위치는 구하고자 하는 값보다 작거나 같은 값을 가진 ROW수를
    그룹 내의 전체 ROW수로 나눈 것을 의미한다.

  - 결과값의 범위는 0< X <=1 이다.

NTILE() : () 안의 숫자만큼 나눠라
  ex) PARTITION 내에 100개의 ROW가 있는데 4개의 BUCKET값을 나누고 싶다. NTILE(4)
                 =>  1개의 BUCKET 당 25씩 ROW가 배정된다.
  ex) 103개의 ROW에 대해서 NTILE(5)를 적용
      - 근사치로 배분한 후 남는 값에 대해서 최초 PARTITION부터 한 개씩 배분

ROW_NUMBER()
  - PARTITION 내에 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 리턴한다.
  - ROWNUM 과는 전혀 상관없다.

============================================================================

WINDOWING : 윈도우 집계함수는 ROW에 대한 집계함수

REPORTING : 한 집합 레벨에 대한 집계 값과 다른 집합 레벨에 대한 집계 값의 비교를 통해
                 분석하고자 하는 경우
    ex) 사원의 급여와 해당 부서의 평균 급여를 비교할 때
         사원의 급여를 제외한 부서의 평균 급여를 알고 싶을 때

RATIO_TO_REPORT() : 해당 구간에서 차지하는 비율을 리턴하는 함수

Lead / Lag Family : 
  - 특정 ROW가 속한 파티션 내에서 상대적 상하 위치에 있는 특정 ROW의 COLUMN 값을
    참조하거나 상호 비교할 때 사용하는 함수

============================================================================

예제모음

Q1) 사원테이블에서 사원의 이름, 부서번호, 급여, 급여가 많은 사원으로부터 순위를 조회하자

SELECT ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) "RANK"
FROM EMP;

SELECT ENAME, DEPTNO, SAL, RANK() OVER(ORDER BY SAL DESC) "RANK"
FROM EMP
ORDER BY 3;

SELECT ENAME, DEPTNO, SAL, 
                      RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM EMP;


Q2) 샘플 테이블 생성, 값 넣기

CREATE TABLE TEST_EMP
AS
SELECT * FROM EMP;

INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 111, 111, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 222, 222, 3000, 30);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 333, 333, 3000, 20);
INSERT INTO TEST_EMP (EMPNO, ENAME, SAL, DEPTNO) VALUES ( 444, 444, 3000, 20);


Q3) 

SELECT ENAME, DEPTNO, SAL, 
                      RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;


Q4) TEST_EMP 테이블에서 DENSE_RANK() PARTITON, ORDER BY로 구해보자

SELECT ENAME, DEPTNO, SAL, 
                      DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) "RANK"
FROM TEST_EMP;


Q5) 20번 부서 사원의 이름, 봉급, 누적분산 정보를 조회하자

SELECT ENAME, SAL, CUME_DIST() OVER ( ORDER BY SAL DESC) "상위 몇%"
FROM TEST_EMP
WHERE DEPTNO = 20;


Q6) 사원의 봉급을 기준으로 4등급으로 분류하자

SELECT ENAME, SAL, NTILE(3) OVER(ORDER BY SAL DESC)
FROM TEST_EMP;


Q7) 사원번호, 이름, 봉급, 입사일을 조회하는데 순번을 매기자 ( ROW_NUMBER() )

SELECT EMPNO, ENAME, SAL, HIREDATE, 
ROW_NUMBER() OVER( ORDER BY SAL DESC, HIREDATE ASC) AS "순번"
FROM TEST_EMP;


SELECT EMPNO, ENAME, SAL, HIREDATE, 
ROW_NUMBER() OVER( ORDER BY SAL DESC, HIREDATE ASC) AS "순번"
FROM TEST_EMP
ORDER BY 1;


Q8) 사원의 이름, 부서번호, 급여, 전체급여 합계, 부서별 합계를 리턴
SELECT ENAME, DEPTNO, SAL, SUM(SAL)
FROM EMP;  --> 안됨

SELECT ENAME, DEPTNO, SAL, 
SUM(SAL) OVER(PARTITION BY DEPTNO) "DEPT SUM",
SUM(SAL) OVER() "TOTAL SUM"
FROM EMP; 


Q9) 사원의 이름, 부서번호, 급여, 업무별 급여평균, 해당 업무의 최대 급여를 조회
SELECT ENAME, DEPTNO, JOB, SAL, 
AVG(SAL) OVER(PARTITION BY JOB) "AVG JOB",
MAX(SAL) OVER(PARTITION BY JOB) "MAX JOB"
FROM EMP;


Q10) 사원이름, 부서번호, 봉급 합계를 3줄씩 더한 결과, 누적 합계를 구해보자
SELECT ENAME, DEPTNO, SAL,
SUM(SAL) OVER(ORDER BY SAL ROWS BETWEEN 1 preceding AND 1 following) "SUM 1",
SUM(SAL) OVER(ORDER BY SAL ROWS UNBOUNDED PRECEDING) "SUM 2"
FROM TEST_EMP;

2번 줄은 ROWS를 기준으로 위 ROW와 아래 ROW의 SAL을 합친 값
3번 줄은 누적 값


Q11) 사원의 총 월급을 50000으로 증가했을 때 기존 월급 비율로 적용했을 경우
       각 사원은 얼마씩 받게되는지 확인해보자

SELECT ENAME, SAL, 
RATIO_TO_REPORT(SAL) OVER() AS "비율",
TRUNC( RATIO_TO_REPORT(SAL) OVER() * 50000) AS "받을 급여"
FROM TEST_EMP;


Q12) 사원이름, 부서번호, 봉급, 본인 이전의 봉급 값을 조회 LAG

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LAG(SAL, 1, SAL) OVER (ORDER BY SAL) AS SAL2
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;


Q13) 사원이름, 부서번호, 봉급, 본인 이전의 봉급 값을 조회 LEAD

SELECT ENAME, DEPTNO, SAL, 
LEAD(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LEAD(SAL, 1, SAL) OVER (ORDER BY SAL) AS SAL2
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LEAD(SAL, 1, 0) OVER (PARTITION BY DEPTNO ORDER BY SAL) AS SAL3
FROM TEST_EMP;

SELECT ENAME, DEPTNO, SAL, 
LAG(SAL, 1, 0) OVER (ORDER BY SAL) AS NET_SAL,
LEAD(SAL, 1, 0) OVER (ORDER BY SAL DESC) AS NET_SAL
FROM TEST_EMP;  --> 이게 결과가 같음