[SQL 입문] 분석함수(WINDOW 함수)

분석함수(WINDOW 윈도우 함수) 구문

- 관계형 데이터베이스
  : 컬럼간 연산, 비교, 연결, 집합에 대한 집계 처리 용이
  : 행과 행간의 관계 정의, 비교, 연산은 하나의 sql문으로 처리 어려움
   (성적이 나보다 다음 등수에 해당하는 사람과 얼마나 차이가 나는지 이런 것)
=> 절차적 데이터베이스 사용 필요

- 절차적 데이터베이스
  > PL/SQL, SQL/PL, T-SQL, PRO*C(금융권에서 사용) 같은 절차형 프로그램을 작성해서 처리
  > INLINE VIEW로 굉장히 복잡하지만 행간 비교 가능

>> 이러한 행간 관계정의, 비교, 연산 개선하는 것 = 분석함수(WINDOW 함수)

SELECT 분석함수(arguments)  --2
       over (PARTITION BY 컬럼list ORDER BY 컬럼list WINDOW절[rows | range] )   --2
             >> 정렬된 파티션 컬럼들을 선택해서 넘겨줄 수 있음
                / 윈도우절에는 행, 값 모두 지정가능
FROM 테이블명   --1(join)
[WHERE~]   --1
[GROUP BY~] > 여기서는 PARTITION BY   --1
[HAVING~]   --1
[ORDER BY~]  --3

- partition by = 분석함수 계산 대상 그룹을 선언
- order by = 대상 그룹에 대해 정렬을 수행
- window 절 = 분석함수(arguments)의  partition 그룹내에 계산 대상 부분 범위를 지정

구문 수행 순서

1단계 - where조건절, group by절, having절, join을 먼저 수행
2단계  - 1단계의 수행 결과집합에서 over 절 (partition by ...) 수행
             분석함수(arguments) 
3단계 - select의 order by절을 최종 결과에 대해 수행

 

윈도우함수의 종류

- 파티션 내의 순위 처리 함수 

• rank() : 동일 순위의 수 만큼 다음 순위는 건너뜀(1위 3명, 4위 1명 이렇게 2, 3위는 없음)
• dense_rank() : 동일 순위의 수가 많아도 다음 순위는 동일순위+1 (1위 3명, 2위 1명 > 1위가 몇 명인지 상관x 무조건 2위)
• row_number() : 똑같은 값이라도 순차적 순위 (1위 1명, 2위 1명, 3위 1명 , 4위 1명 >> 1~3위는 모두 동일 값)

- 파티션된 레코드들의 집계 처리 함수

• sum(), avg(), max(), min(), count(), stddev(), variance()
        > over절과 함께 사용 Moving & Cumulative Processing을 지원

- 파티션 내의 행 순서 처리 함수 

• first_value(), last_value(), lead(arg1  [,arg2, arg3 ]), lag(arg1  [,arg2, arg3 ])

- 파이션 내에 비율처리함수

• RATIO_TO_REPORT()
  : 비율 값 반환 0< AND <= 1, 리턴되는 비율의 합은 1 
  : 파티션 내의 비율 > 그룹해서 그 안에서 비율지정할때 좋음
• PERCENT_RANK()
  : 파티션 별 윈도우에서 제일 먼저 나오는 것은 0, 마지막행의 percent값은 1 반환(행의 순서별 백분율 반환)
  : 안에 함수 들어가면 안됨 > 나눈 것(같은 행에 대해서는 건너뜀)
• CUME_DIST()
• NTILE() : 파티션별 전체 건수를 argument값으로 n 등분한 결과를 구할 수 있음

- 전문 통계 함수

• VAR_POP :  모집단의 분산
• VAR_SAMP :  표본집단의 분산
• STDDEV_POP :  모집단의 표준편차
• STDDEV_SAMP : 표본집단의 표준편차
• CORVAR_POP : 모집단의 공 분산
• CORVAR_SAMP : 표본집단의 공 분산
• CORR : 상관계수

 

-- rank(), dense_rank(), row_number()

SELECT empno, ename, sal, rank() over (order by sal desc) "rank"
       , dense_rank() over (order by sal desc) "dense_rank"
       , row_number() over (order by sal desc) "rank"
FROM emp;

- 집계 관련 함수

SELECT deptno, ename, sal, sum(sal) over (partition by deptno)
FROM emp;  --group by를 사용한 것도 동일한 결과 나옴

 

SELECT deptno, ename, sal, sum(sal) over (partition by deptno order by sal range unbounded preceding)
FROM emp; --값 기준으로 해서 범위

SELECT deptno, ename, sal, sum(sal) over (partition by deptno order by sal range unbounded preceding)
FROM emp; --값 기준으로 해서 내 앞의 모든 행

SELECT deptno, ename, sal, sum(sal) over (partition by deptno order by sal range unbounded preceding)
FROM emp; --값 기준으로 해서 범위(누적합 계산) 동일한 연속된 수는 그룹으로 묶어서 한번에 처리

SELECT deptno, ename, sal, sum(sal) over (partition by deptno order by sal rows unbounded preceding)
FROM emp; --값 기준으로 해서 내 앞의 모든 행 > 한행한행을 구분해서 행마다 누적합 반환

-- 둘다 결과자체는 같은 결과값을 반환함/ 그 과정이 좀 다름

SELECT deptno, ename, sal, sum(sal) 
    over (partition by deptno order by sal 
    rows between 1 preceding and 1 following) a
FROM emp;

SELECT deptno, ename, sal, sum(sal) 
    over (partition by deptno order by sal 
    range between 300 preceding and 300 following) b
FROM emp; --나보다 300적으면 800, 300많으면 1900(다음행포함됨)

--lag > 인수 없을 때 디폴트 값은(column, [null, 1])

SELECT deptno, ename, sal, lag(sal) -- 내 앞의 행
    over (partition by deptno order by sal desc) lag1,
    lag(sal, 2) over (partition by deptno order by sal desc) lag2,
    lag(sal, 2, 0) over (partition by deptno order by sal desc) lag3
FROM emp;

--lead > lag와 동일

SELECT deptno, ename, sal, lead(sal) -- 내 뒤의 행
    over (partition by deptno order by sal desc) lead1,
    lead(sal, 2) over (partition by deptno order by sal desc) lead2,
    lead(sal, 2, 0) over (partition by deptno order by sal desc) lead3
FROM emp;

--  RATIO_TO_REPORT 

SELECT deptno, ename, sal, 
    round(RATIO_TO_REPORT(sal) over(partition by deptno), 2) r
FROM emp; --각 부서마다 전체 비율이 1

--PERCENT_RANK(), cume_dist()

SELECT deptno, ename, sal, 
    round(PERCENT_RANK() over(partition by deptno 
    order by sal desc), 2) p
FROM emp; 


SELECT deptno, ename, sal, 
    round(cume_dist() over(partition by deptno 
    order by sal desc), 2) c
FROM emp;

--NTILE

SELECT ename, sal, 
    ntile(4) over(order by sal desc) n_4
FROM emp; 

SELECT ename, sal, 
    ntile(5) over(order by sal desc) n_5
FROM emp;

 

이해를 돕기위한 참고링크 : http://wiki.gurubee.net/pages/viewpage.action?pageId=27427796 

 

* 내용참고&출처 : 태그의 수업을 복습 목적으로 정리한 내용입니다.