[Oracle] 분석 함수

분석 함수

• 대량의 데이터를 다차원적으로 분석하기 위한 함수이다.
• 여러 SQL 명령문을 사용하여야 하는 작업을 하나의 SQL 명령문으로 간결하게 표현 가능하다.

 

분석 함수의 실행 단계

1. 일반 질의 처리 : 기존 질의문을 처리하는 단계, SQL 명령문을 실행하여 출력 결과를 검색하는 단계
2. 분석 함수 적용 : 일반 질의 처리 결과에 분석 함수를 적용하는 단계
3. 정렬 : ORDER BY 절에 의해 결과 집합 정렬

 

RANK 분석 함수

• 특정 칼럼 값에 의해 정렬된 결과에 순위를 부여하는 함수이다.

 

RANK

RANK() OVER (
[PARTITION BY <value expression1>] [,...]
ORDER BY <value expression2> [ ASC | DESC ]
[NULLS FIRST | NULLS LAST] );

• OVER : 순위를 부여하기 위한 대상 집합의 정렬 기준 및 분할 기준 정의
• PARTITION BY : value expression1을 기준으로 분할, 생략하면 전체 집합을 대상으로 순위 부여
• ORDER BY : 각 분할내에서 데이터를 정렬하는 기준 칼럼 지정
• NULLS FIRST | NULLS LAST : 정렬 결과에서 NULL 값의 위치 설정

*동일한 순위가 있다면 다음 순위는 건너뛴다. 예를 들어 공동 1등이 2명이라면 2위는 없고 1등 3등으로 출력된다.

 

DENSE_RANK

RANK 함수는 순위가 동일한 경우 다음 순위를 건너뛰는데, DENSE_RANK 함수는 다음 순위를 연속적으로 출력한다.

DENSE_RANK() OVER (
[PARTITION BY <value expression1>] [,...]
ORDER BY <value expression2> [ ASC | DESC ]
[NULLS FIRST | NULLS LAST] );

 

EX) 키가 큰 사람부터 순위를 부여하여 출력

SELECT studno, name, height,
       RANK() OVER (ORDER BY height DESC) "height_rank",
       DENSE_RANK() OVER (ORDER BY height DESC) "height_dense"
FROM student;

보면 공동 8위가 2명일때 RANK 함수는 다음 순위는 10위부터 시작하고,

DESNE_RANK 함수는 연속적으로 9위부터 시작한다.

 

EX) 학년별로 키 순위를 출력

SELECT studno, name, grade, height,
       RANK() OVER (PARTITION BY grade ORDER BY height DESC) "height_rank",
       DENSE_RANK() OVER (PARTITION BY grade ORDER BY height DESC) "height_dense"
FROM student
ORDER BY grade;

 

TOP-N 분석 함수

• 상위 또는 하위 N개 출력
• 주로 RANK, DENSE_RANK 함수와 함께 사용

SELECT [column_list], ROWNUM
FROM ( SELECT [column_list]
       FROM table_name
       ORDER BY Top-N_column )
WHERE ROWNUM <= N;

 

EX) 전체 학생중에서 키가 가장 큰 상위 3명 출력.

SELECT studno, name, height, rank_value
FROM (SELECT studno, name, height,
             RANK() OVER(ORDER BY height DESC) AS rank_value
      FROM student)
WHERE rank_value <= 3;

 

NTILE 분석 함수

• 출력 결과를 사용자가 지정한 그룹 수로 나누어서 출력해준다.
• 10개의 결과를 2그룹으로 나눈다면 1~5까지는 1번 그룹, 6~10까지는 2번 그룹으로 출력.

NTILE(expr) OVER (
	[PARTITION BY <value expression1>] [,...]
    ORDER BY <value expression2> [collate clause] [ASC|DESC]
    [NULLS FIRST | NULLS LAST]
)

exrp : 그룹 수 지정

 

EX) 생년월일을 오름차순으로 정렬하여 4개의 그룹으로 나누어 출력

SELECT studno, name, birthdate,
	   NTILE(4) OVER (ORDER BY birthdate) class
FROM student;

 

ROW_NUMBER 분석 함수

• 분할 별로 정렬된 결과에 대해 순위를 부여하는 함수
• RANK, DENSE_RANK와 달리 같은 값에 대해 순서대로 순위를 매김 즉, 그냥 행 번호라고 생각하자.

ROW_NUMBER() OVER (
[PARTITION BY <value expression1>] [, ...]
ORDER BY <value expression2> [ASC|DESC]
[NULLS FIRST | NULLS LAST]
)

 

EX) 학과(deptno)별로 몸무게가 작은 사람부터 순위를 부여.

( RANK, DENSE_RANK, ROW_NUMBER 비교 )

SELECT deptno, weight, name,
       RANK() OVER (PARTITION BY deptno ORDER BY weight) weight_rank,
       DENSE_RANK() OVER (PARTITION BY deptno ORDER BY weight) weight_dense,
       ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY weight) weight_row
FROM student;

ROW_NUMBER는 weight가 동일하더라도 순서대로 순위를 매긴다.

 

윈도우 분석 함수

전체 결과 집합에서 연속선상에 있는 부분 집합을 대상으로 적용하는 함수이다.

 

문장 정리

• ROWS | RANGE : 윈도우의 크기를 결정하기 위한 행 집합을 정의
  • ROWS : 물리적인 단위에 의해 윈도우 크기 지정
  • RANGE : 논리적인 상대 번지에 의해 윈도우 크기 지정
• BETWEEN ... AND : 윈도우의 시작 위치와 마지막 위치 지정
• UNBOUNDED PRECEDING : 윈도우의 시작 위치는 각 분할의 첫번째 행
• UNBOUNDED FOLLOWING : 윈도우의 마지막 위치는 각 분할의 마지막 행

 

EX) 학과별 학생을 학번의 오름차순으로 정렬하여 학생의 몸무게와 이전 2명 학생의 몸무게 합을 출력

SELECT deptno, studno, weight,
       SUM(weight) OVER (PARTITION BY deptno
                         ORDER BY weight studno
                         ROWS 2 PRECEDING) AS weight_sum
FROM student;

학과별로 각 학생 행에 weight_sum은 자신의 몸무게와 이전 2개의 몸무게를 합한 것으로 나타난다.

 

+ 추가 예제

-- 생년월일을 기준으로 전, 후 2년간의 학생의 평균 키를 출력.
SELECT studno, birthdate, height,
       AVG(height) OVER (ORDER BY birthdate
                         RANGE BETWEEN INTERVAL '2' YEAR PRECEDING
                         AND INTERVAL '2' YEAR FOLLOWING) year2
FROM student;
-- 위의 문제 경우 행(ROWS)을 범위로 기준을 잡은게 아니라 현재 행의 생년월일을 기준으로 2년 전후 평균임.

-- 직급별 누적 월급 합계 출력.
SELECT d.dname, p.position, sal,
SUM(p.sal) OVER (PARTITION BY position
                 ORDER BY p.sal
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) "누적합계"
FROM professor p, department d
WHERE p.deptno = d.deptno;

UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING : 전체 누적

UNBOUNDED PRECEDING AND CURRENT ROW : 현재행까지 누적

 

FIRST_VALUE 와 LAST_VALUE

FIRST_VALUE : 윈도우에서 정렬된 값 중에서 첫번째 값을 반환

LAST_VALUE : 윈도우에서 정렬된 값 중에서 마지막 값을 반환

FIRST_VALUE | LAST_VALUE (value_expression1)
OVER ( [PARTITION BY <value_expression2>] [, ...]
        ORDER BY <value expression> [ASC|DESC]
        [NULLS FIRST | NULLS LAST] );

 

EX) 생년월일을 기준으로 전, 후 2년간의 학생들의 평균 키와 2년 전, 후의 처음과 마지막 키를 출력

SELECT studno, birthdate,
       AVG(height) OVER (ORDER BY birthdate
                         RANGE BETWEEN INTERVAL '2' YEAR PRECEDING
                         AND INTERVAL '2' YEAR FOLLOWING) year2,
       FIRST_VALUE(height) OVER (ORDER BY birthdate
                                 RANGE BETWEEN INTERVAL '2' YEAR PRECEDING
                                 AND INTERVAL '2' YEAR FOLLOWING) first_val,
       LAST_VALUE(height) OVER (ORDER BY birthdate
                                RANGE BETWEEN INTERVAL '2' YEAR PRECEDING
                                AND INTERVAL '2' YEAR FOLLOWING) last_val
FROM student;

 

+ 추가 예제

-- 학과별로 월급의 누적 합계와 첫번째 값, 마지막 값을 출력
SELECT p.profno, p.name, d.dname, p.position, p.sal,
       ROUND(SUM(p.sal) OVER (PARTITION BY dname ORDER BY p.sal
       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) sum_sal,
       
       ROUND(FIRST_VALUE(p.sal) OVER (PARTITION BY dname ORDER BY p.sal
       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) FIRST_VALUE_sal,
       
       ROUND(LAST_VALUE(p.sal) OVER (PARTITION BY dname ORDER BY p.sal
       RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 2) LAST_VALUE_sal
FROM professor p, department d
WHERE p.deptno=d.deptno;

 

LAG, LEAD 분석 함수

• 동일한 테이블에 있는 다른 행의 값을 참조하는 함수.
• LAG : 현재 행을 기준으로 이전 값을 참조
• LEAD : 현재 행을 기준으로 이후 값을 참조

LEAD | LAG (value expression1)
OVER (
    [PARTITION BY <value expression2>] [, ...]
     ORDER BY <value expression2> [ASC|DESC]
     [NULLS FIRST | NULLS LAST]
)

 

EX) 101번 학과 학생들에 대해 임의의 학생의 height 칼럼 값을 기준으로 한 행 다음 값과 이전 값을 출력

SELECT name, height,
       LEAD(height, 1) OVER (ORDER BY height) AS next_height,
       LAG(height, 1) OVER (ORDER BY height) AS prev_height
FROM student
WHERE deptno=101;