[Oracle] 분석 함수(Analytic Function)

Analytic Function

• Row들의 집합으로 Partition이라는 Result Set을 만들며 연산의 범위를 결정하기 위해 각각 Row들을 대상으로 Data Sliding Window를 구성

 

Analytic Function 수행 과정

1. Joins, Where, Group by, Having 등의 수행으로 Data를 추출
2. Analytic Function이 이용할 수 있는 Result Set 생성,
3. Order by 절이 있다면 Order by를 수행하여 최종 Result Set 반환

User Defined Function을 정의하여 Over 절과 같이 사용가능

 

Analytic Function 예

select deptno, sum(sal) over (order by deptno)
from emp;

 

Category

Type	설명	예	방법
Ranking Function	대상 집합에 대해 특정 컬럼을 기준으로 순위나 등급을 매기는 Analytic Function	각 회원사 영업점 별 거래량 상위 10위안에 포함되는 계조라를 찾아라	RANK, DENSE_RANK, TOP_N, BOTTOM_N, NTILE, CUMI_DEST, PRCENT_RANK, ROW_NUMBER
Windowing Function	Current row를 기준으로 지정된 window 내의 row들을 대상으로 집단화(aggregation)을 수행하여 여러가지 유용한 집계 정보를 구하는 Analytic Function류	영업점별로 거래량 누적치를 나타내라	SUM, AVG, MIN, MAX, STDDEV, VARIANCE, COUNT, FIRST_VALUE, LAST_VALUE
Reporting Function	서로 다른 두 가지 aggregation level을 비교하고자 하는 목적으로 사용하는 Analytic Function	영업점 거래량이 회원사에서 차지하는 비율을 나타내라	RATIO_TO_REPORT
LEAD/LAG	서로 다른 두 row 값 비교	2009년 6월, 특정 영업점의 거래량에 대한 작년 거래량 대비 증가비율 나타내기	LAG, LEAD

 

예시 데이터 삽입

create table sales_tables
(sales_person varchar2(15),
region varchar(10),
amount number(10));

insert into sales_tables values('Davis', 'East', 100);
insert into sales_tables values('Baker','East',89);
insert into sales_tables values('Smith','East',89);
insert into sales_tables values('Fitzpatrik','East',75);
insert into sales_tables values('Jones','West',98);
insert into sales_tables values('Peterson','West',85);
insert into sales_tables values('Lee','West',77);
commit;

 

RANK()

각 row마다 순위를 매겨주는 함수로 Partition 내에서 Order by 절에 명시된 대로 정렬한 후 순위를 의미하고, 1부터 시작하며 동일한 순위를 가지며 동일한 순위의 수만큼 다음 순위는 건너 뛴다.

select sales_person, region, amount,
rank() over(partition by region order by amount desc) as rank
from sales_tables;

 

TOP_N / BOTTOM_N

RANK 함수 작성시 해당하는 순위만 출력하기 위해 사용하는 함수

SELECT *
  FROM (SELECT SALES_PERSON,
               REGION,
               AMOUNT,
               RANK() OVER(PARTITION BY REGION ORDER BY AMOUNT DESC) AS RANK
          FROM SALES_TABLES) A
 WHERE RANK <= 2;

 

NTILE()

NTILE 함수는 정렬된 PARTITON을 BUCKET 이라 불리는 그룹별로 나누고 PARTITION 내의 각 ROW등을 BUCKET에 배치하는 함수로 각 BUCKET에는 동일한 수의 ROW가 배치된다.

 SELECT SALES_PERSON, AMOUNT, NTILE(4) OVER(ORDER BY AMOUNT DESC) AS NTILE
   FROM SALES_TABLES;

 

ROW_NUMBER()

ROW_NUMBER는 각 PARTITION 내에서 ORDER BY 절에 의해 정렬된 순서로 유일한 값을 돌려주는 함수로 ROWNUM과는 관계가 없다.

SELECT SALES_PERSON, AMOUNT, ROW_NUMBER() OVER(ORDER BY AMOUNT DESC) AS RN
  FROM SALES_TABLES;

 

Windowing

• 정렬된 순서에 따라 누적, 증감, 평균값 등을 각각 Row에 대하여 반환
• ROWS | RANGE: ROW 또는 논리적인 범위로 WINDOW를 지정
• BETWEEN … AND: WNDOW에서 START POINT와 END POINT를 지정
• UNBOUNDED PRECEDING: PARTITION의 첫 번째 ROW에서 WINDOW가 시작
• UNBOUNDED FOLLOWING: PARTITION의 마지막 ROW에서 WINDOW가 끝남

SELECT DEPTNO,
       JOB,
       EMPNO,
       ENAME,
       HIREDATE,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY JOB ROWS UNBOUNDED PRECEDING) 누계
  FROM EMP;

 

Windowing 예제 데이터 삽입

create table ledger (
acct_number varchar(10), trans_date date, trans_amount number(10,2));

insert into ledger values('73829',to_date('19981101','yyyymmdd'),113.45);
insert into ledger values('73829',to_date('19981105','yyyymmdd'),-52.01);
insert into ledger values('73829',to_date('19981113','yyyymmdd'),36.25);
insert into ledger values('73829',to_date('19981205','yyyymmdd'),12.10);
insert into ledger values('73829',to_date('19981213','yyyymmdd'),50.10);
insert into ledger values('82930',to_date('19981101','yyyymmdd'),10.56);
insert into ledger values('82930',to_date('19981121','yyyymmdd'),32.55);
insert into ledger values('82930',to_date('19981129','yyyymmdd'),-5.02);
insert into ledger values('82930',to_date('19981212','yyyymmdd'),10.02);
insert into ledger values('82930',to_date('19981221','yyyymmdd'),20.05);
commit;

 

Cumulative Aggregation

각 파티션의 시작 위치가 Window의 사작 위치가 되고,현재 Row의 물리적 또는 논리적 위치가 Window의 종료 위치가 되어 여기에 해당하는 집계함수를 적용하는 형태

SELECT ACCT_NUMBER,
       TRANS_DATE,
       TRANS_AMOUNT,
       SUM(TRANS_AMOUNT) OVER(PARTITION BY ACCT_NUMBER ORDER BY TRANS_DATE ROWS UNBOUNDED PRECEDING) AS BALANCE
  FROM LEDGER
 ORDER BY ACCT_NUMBER, TRANS_DATE;

 

Moving Aggregation

각 파티션 내의 현재 row의 물리적 또는 논리적 위치가 윈도우의 종료위치가 되고, 이 종료 위치를 기준으로 offset을 적용하여 시작위치가 결정되고 여기에 해당하는 집계함수를 적용하는 형태

SELECT ACCT_NUMBER,
       TRANS_DATE,
       TRANS_AMOUNT,
       AVG(TRANS_AMOUNT) OVER(PARTITION BY ACCT_NUMBER ORDER BY TRANS_DATE RANGE INTERVAL '7' DAY PRECEDING) AS MAVG_7DAY
  FROM LEDGER;

 

Reporting: RATIO_TO_REPORT

RETIO_TO_REPORT 함수는 Window 내의 합계에 대한 비율을 계산하는 함수

SELECT DEPTNO,
       JOB,
       SUM(SAL) SUM_SAL,
       SUM(SUM(SAL)) OVER(PARTITION BY DEPTNO) SUM_TOTAL,
       RATIO_TO_REPORT(SUM(SAL)) OVER(PARTITION BY DEPTNO) RATIO_TO_SUM
  FROM EMP
 GROUP BY DEPTNO, JOB;

 

LAG/LEAD

오름차순 또는 내림차순으로 정렬된 파티션 내에서 상대적으로 상위 또는 하위에 위치하고 있는 특정 로우의 컬럼 값을 offset 지정에 의해 참조

파티션 내에서 참조할 로우가 없을 경우 지정한 값(default = NULL) 으로 출력

SELECT DEPTNO,
       EMPNO,
       SAL,
       LAG(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS SAL_LAG,
       LEAD(SAL, 1) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS SAL_LEAD
  FROM EMP;

 

Analytic Function의 장점

• Query Speed의 향상
• Self-join, 절차적 로직으로 표현한 것을 native SQL에서 바로 적용할 수 있도록 하여 Join이나 프로그램의 Over head를 줄임
• 향상된 개발 생산력
• 간결한 SQL로 복잡한 분석 작업을 수행 가능하며, 유지보수가 간편하여 생산성 향상
• 이해 및 활용이 용이
• 기존 SQL Syntax를 그대로 따르기 때문에 ANSI SQL로 채택

 

Reference.

http://www.gurubee.net/