[빅데이터 및 지식관리시스템] Business question에 대해 답하기 위한 Analytic Functions 정리

SQL에서 좀 더 복잡한(?) 질문에 대해 출력하기 위해서, 중요한 business question에 대해 답을 하기 위해 Analytic Function을 사용한다.

 

예를 들어 각 부서 상위 10명의 salesman을 추출하여라.

지난 90일간 이동 평균은?

전년도 대비 성장률은?

과 같은 질문이 있다.

 

SQL로는 이런 것을 하기에는 좀 복잡하고 비효율적이다.

따라서 여러 Analytic function을 이용하게 된다.

 

Analytic Functions

그에 반해 analytic function을 이용하면 query의 performance를 향상해 주고 효율적이게 작업을 할 수 있다.

종류는 다음과 같다.

• Ranking functions: rank, dense_rank, percent_rank, ntile
• Window Aggregate functions: avg, sum, min, max
• LAG/LEAD
• Reporting Aggregate functions

Analytic function의 핵심 구성 요소는 PARTITION BY, ORDER BY, ROWS/RANGE으로 이뤄진다.

일단 Basic Syntax는 다음과 같이 표현할 수 있다.

Order By

예를 들어 salary에 따라 rank를 정하라고 하면 다음과 같다.

SAL에 대해 오름차순으로 정렬하고 RANK() function을 통해 RANK라는 column을 만든다.

내부적으로 정렬했을 때고 만약 출력을 EMPNO에 대해 정렬하면 어떻게 될까?

위의 query 마지막에 ORDER BY EMPNO만 붙이면

다음과 같이 EMPNO에 대해 정렬됨을 알 수 있다. 

이때 Rank로 정렬됬던게 없어진 것을 볼 수 있는데 둘의 정렬이 다른 것을 잘 구분해야 한다.

Partition BY

결과를 작은 집합 단위로 나눌 때 사용한다.

GROUP BY와 뭐가 다른지 헷갈릴 수 있는데, GROUP BY는 하나의 행으로 압축해서 표현하는 반면 Partition BY는 행을 유지한 채 계산한다.

 

부서별로 연봉순대로 출력해 보라. 를 query로 어떻게 작성할까?

다음과 같이 RANK() 함수에 대해 Parition BY로 DEPTNO끼리 묶어 ORDER BY로 순위를 정한다.

이후 그 column을 DEPT_RANK로 정하여 출력한다.

 

그럼 Top-N에 대해는 어떻게 표현할까?

row에 조건을 걸면 되겠다 생각하여

다음과 같이 표현할 수 있는데

Analytic function은 Where절에는 사용할 수 없다.

따라서 RANK라는 column으로 만들어주고 그때의 값을 where절에서 조건을 걸어 Top-N을 출력해야 한다.

 

Top-N 계산을 할 때 동점자가 있다면 어떻게 할까? 각 function마다 처리하는 방식이 다른데

• RANK(): 동점자가 있으면 그만큼 건너뛴다. 예를 들어 1등이 2명이면 다음은 3등인 것이다.
• DENSE_RANK(): 동점자가 있더라도 연속적이게 순위를 매긴다. 예를 들어 1등이 2명이면 다음은 2등인 것이다.
• ROW_NUMBER(): 동점자가 있더라도 고유한 번호를 매긴다. 1등이 2명이어도 위에서부터 1등, 2등이 된다.

ROWNUM과 ROW_NUMBER()의 차이에 대해 궁금할 수 있는데 정리해 보면

ROWNUM은 물리적으로 읽어낸 값이고 ROW_NUMBER()는 논리적인 연산과정 이후에 읽어낸 값이다.

 

NULL값은 기본적으로 높은 값으로 취급하여 가장 앞부분에 배치되지만(NULLS LAST라는 query를 통해 마지막에 배치할 수도 있다.)

Aggregates

Aggregates를 Analytic function들과 같이 사용할 수 있다.

예를 들어 부서별 속한 직무의 salary 평균에 대해 순위를 매기려면 다음과 같이 작성할 수 있다.

GROUP BY로 (부서, 직무) 단위로 그룹화하고 그 그룹 안의 직원들의 급여 평균(AVG(SAL))을 계산한다.

AVG(SAL)은 직무별 평균이 된다.

 

다음은 집계함수(AVG)를 Analytic Function으로 사용할 경우에 대해 알아보면

예를 들어 (AVG(SAL) OVER()) AS AVGSAL 이면 전체 평균을 계산하여 AVGSAL column을 구할 수 있다.

 

만약 여기에 PARTITION BY를 추가하면 

(AVG(SAL) OVER(PARTITION BY DEPTNO)) AS AVGSAL와 같이 쓸 수 있으며, 부서별로 평균을 매겨서 AVGSAL column에 저장한다.

다음과 같이 정리할 수 있다.

Percentiles

Basic syntax는 다음과 같이 표현할 수 있다.

XXXX부분에는 continous값을 출력할지 discrete값을 출력할지 정하는 것이다.

다음과 같이 fraction에 몇 % 값을 출력할지 정할 수 있다.

특정값이 상위 몇 %에 위치하는지 출력하는 방법은 다음과 같다.

OVER() 내부에 PARTITION BY를 통해 부서별 백분율을 판단할 수도 있다.

 

NTILE은 row를 특정 간격으로 나누는 것이다.

다음과 같이 2칸, 4칸, 10칸으로 나눌 수 있다.

 

Window Function

Window는 현재 행을 기준으로 계산에 포함되는 범위를 의미한다.

Analytic function은 이 window 범위 안에서만 계산을 수행한다.

 

예를 들어 누적합은 다음과 같이 표현할 수 있다.

ROWS BETWEEN은 범위를 나타내는 연산자로 내부는 항상 정렬되어있어야 한다.

UNBOUNDED PRECEDING은 첫 행을 뜻하고 CURRENT ROW는 현재 자신의 행을 뜻한다.

 

1 PRECEDING AND 1 FOLLOWING을 통해 physical 하게 자신의 앞뒤 1을 포함할 수 있다. 

 

범위를 지정할 때 Physical, Logical Offset을 설정할 수 있다.

 

BETWEEN ROWS를 이용하면 개수로 판단하여 앞뒤 몇 개로 정하는 것이고(Physical)

BETWEEN RANGE를 이용하면 값으로 보는 것이기 때문에 특정값 사이로 표현한다.(Logical)

예시를 살펴보면

다음과 같이 있을 때 current row가 empno=7499라 할 때, 7499-100부터 7499+200까지의 sal을 더하는 것이다.

 

누적합을 구하는 SUM(AVG) function을 이용할 때 ORDER BY 뒤 범위가 지정이 안돼있으면 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 defualt로 들어간다.

 

이것도 PARTITION BY를 추가하여 ~별로 연산을 할 수 있다.

 

지금까지 Window연산을 numeric intervals로 했지만, time interval에 대해 할 수 있다.

 

Additional Function

• FIRST_VALUE()와 LAST_VALUE() function을 이용하여 특정 범위에서 가장 처음 값 또는 마지막 값을 출력할 수 있다.

• RATIO_TO_RESORT(SAL) OVER()은 전체 합계에서 차지하는 비율을 출력한다.

• LAG(), LEAD() function을 통해 앞 몇 행까지, 뒤 몇행까지 출력할지 정하는 것이다.

특히 이 경우는 tuple단위로 비교하는 것이기 때문에 범위를 지정하여 집계하는 BETWEEN ROW와는 다르다.