window 함수

WINDOW FUNCTION

• 행과 행 사이의 관계를 쉽게 정의할 수 있다.
• 다른 함수와는 달리 중첩하여 사용할 수 없다.(단, 서브쿼리에는 사용 가능)
• OVER 문이 필수로 사용된다. (쓸거 없으면 OVER() 이라도 써야한다.)

SELECT 윈도우함수명(매개변수)

OVER ([PARTITION BY] [ORDER BY] [WINDOWING])

FROM 테이블명

ex)

SELECT JOB, ENAME, SAL,

RANK() OVER (PARTITION BY JOB

   ORDER BY SAL DESC

   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) JOB_RANK

FROM EMP;

- PARTITION BY 절 : 전체 집합을 특정 칼럼을 기준으로 소그룹으로 나눈다.

- WINDOWING 절 : 함수의 대상이 될 행의 범위를 지정할 수 있다.(ROWS 또는 RANGE 둘 중 하나 선택해 사용 가능)

   + ROWS : 물리적인 결과 행 수를 지정

   + RANGE 논리적 값의 범위를 지정

키워드	설명
ROWS	물리적으로 행을 지정할 것임을 명시
RANGE	논리적으로 행을 지정할 것임을 명시
BETWEEN ~ AND ~	windowing 범위 지정
UNBOUNDED PRECEDING	첫 번째 행 --> endpoint에는 사용 불가
UNBOUNDED FOLLOWING	마지막 행
CURRENT ROW	현재 행

ex) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

-> 물리적으로 행을 지정할 것이며, 범위는 첫 번째 행부터 맨 마지막 행 까지다. 

---

WINDOW FUNCTION 종류

• 집계 함수 : SUM, MAX, MIN, AVG 등
• 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER
• 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD
• 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT
• 통계 분석 함수 : (생략)

---

1. 집계 함수

• SUM, AVG, COUNT, MAX, MIN
  

SELECT ENAME, SAL,

SUM(SAL) OVER (PARTITION BY MGR) SUM_MGR

FROM EMP;

---

2. 순위 관련 함수

1.1 RANK

• 랭킹을 계산
  
• 동순위는 동일한 값을 부여 ex) 1등 두명이면 둘다 1등된다. 2등 없이 다음은 3등이다.

SELECT ENAME, SAL,

RANK() OVER (ORDER BY SAL DESC) ALL_RANK

FROM EMP;

1.2 DENSE_RANK

• 랭킹을 계산
  
• 동순위는 하나의 건수로 처리 ex) 1등 두명이면 둘다 1등된다. 다음은 2등이다.

SELECT ENAME, SAL,

DENSE_RANK() OVER (ORDER BY SAL DESC) DENSE_RANK

FROM EMP;

1.3 ROW_NUMBER

• 랭킹을 계산
  
• 동순위에 대해 고유한 값을 부여 ex) 1등 두명이면 그냥 한명은 2등 처리한다.

SELECT ENAME, SAL,

ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUM

FROM EMP;

---

3. 행 순서 관련 함수

1.1 FIRST_VALUE : 첫 행의 값을 가져온다.

SELECT DEPTNO, ENAME, SAL,

FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO

ORDER BY SAL DESC 

ROWS UNBOUNDED PRECEDING) AS DEPT_A 

FROM EMP;

1.2 LAST_VALUE : 마지막 행의 값을 가져온다.

SELECT DEPTNO, ENAME, SAL,

LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO

ORDER BY SAL DESC 

ROWS UNBOUNDED PRECEDING) AS DEPT_A 

FROM EMP;

1.3 LAG : 이전 행의 값을 가져온다.

SELECT DEPTNO, ENAME, SAL,

LAG(SAL) OVER (ORDER BY SAL DESC) AS PRE_SAL //이천 행의 SAL 필드 값을 가져온다.

FROM EMP;

1.4 LEAD : 이후 N번째 행의 값을 가져온다.

- 가져올 행 번호의 기본 값은 1이다.

SELECT DEPTNO, ENAME, SAL,

LEAD(SAL,2) OVER (ORDER BY SAL DESC) AS PRE_SAL //SAL에서 두번재 행의 값을 가져온다.

FROM EMP;

---

4. 비율 관련 함수

1.1 RATIO_TO_REPORT

- 각 칼럼 값의 백분율을 소수점으로 구할 수 있다. (전체 합은 1)

1.2 RATIO_TO_REPORT

- 등수를 백분율로 구한다. (전체 합은 1)

1.3 NTILE

- 조회된 내용을 N등분한다.