[SQL 활용] WINDOW FUNCTION (윈도우 함수)

윈도우 함수란?

• 윈도우 함수는 행과 행 간의 관계를 정의 하기 위해서 제공되는 함수
• 윈도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있다.
• 윈도우 함수는 GROUP BY 구문과 병행하여 사용할 수 없다.
• 윈도우 함수로 인해 결과 건수가 줄어들지 않는다.
• 윈도우 함수의 PARTITION 구문과 GROUP BY 구문은 둘 다 파티션을 분할한다는 의미에서는 유사하다.
• SUM, MAX, MIN 등과 같은 집계 윈도우 함수를 사용할 때 윈도우 절과 함께 사용하면 집계 대상이 되는 레코드 범위를 지정할 수 있다.

 

윈도우 함수 구조

select window_function(arguments)
	over (partition by 칼럼 order by windowing 절) 
from 테이블명;

WINDOWING (행 지정)

 

WINDOWING 예시

전체합계

select empno, ename, sal
sum(sal) over (order by sal 
		rows between unbounded preceding
		and unbounded following) totsal
from emp;

• unbounded preceding은 첫 행을 의미하며, unbounded following은 마지막 행을 의미한다.
• 그러므로 totsal의 처음부터 마지막까지의 합계 (sum(sal))를 계산한 것이다.

누적합계

select empno, ename, sal
sum(sal) over (order by sal
		rows between unbounded

• 처음부터 current row(현재까지)의 합계를 계산한다. 즉, 누적합계가 된다.
• 첫 번째 행의 값이 1이었으면 1, 두 번째 행의 값이 2였으면 1+2=3 .. 값이 누적됨!

 

순위함수 (RANK function)

• 윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공한다.

• 순위 함수는 RANK(), DENSE_RANK(), ROW_NUMBER() 함수가 있다.

 

RANK FUNCTION 예시

내림차순 및 파티션

select ename, sal
	rank() over (order by sal desc) all_rank,
	dense_rank() over (order by sal desc) dense_rank
from emp;

 

• RANK() 함수는 순위를 계산하며, 동일한 순위에는 같은 순위가 부여된다.
  • 1,2,2,4, ...
  • rank() over(order by sal desc)는 sal로 등수를 계산하고, 내림차순으로 조회된다.
  • rank() over(partition by

DENSE RANK

select ename, sal
	rank() over (order by sal desc) all_rank,
	dense_rank() over (order by sal desc) dense_rank
from emp;

• DENSE_RANK는 동일한 순위를 하나의 건수로 인식해서 조회한다.
  • 1,2,2,3,4,5,6,7 ...

ROW NUMBER

select ename, sal
	rank() over (order by sal desc) all_rank
	row_number() over (order by sal) row_num
from emp;

---

 

집계 함수 (AGGREGATE function)

집계함수 예시

SUM

select ename, sal
sum(sal) over (partition by mgr) sum_mgr
from emp; 

• 같은 관리자(mgr)에 파티션을 만들고 합계(sum)를 계산한다.

---

 

행 순서 관련 함수

• 행 순서 관련 함수는 상위 행 값을 하위에 출력하거나 하위 행 값을 상위에 출력할 수 있다.

• 특정 위치의 행을 출력할 수 있다.

 

행 순서 관련 함수 예시

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; 

부서로 파티션을 나누고, 부서별로 급여가 가장 많은 직원의 이름을 4번째 칼럼에 배치한다. 

 

 

LAST_VALUE

select deptno, ename, sal,
	last_value(ename) over (partition by deptno 
	order by sal desc rows between current row and
	unbounded following) as dept_a 
from emp; 

• last_value는 마지막 행을 가져온다.
• between current row and unbounded following(=현재 행에서 마지막 행까지의 파티션을 의미)은 부서 내에서 급여가 가장 적은 사람을 가져오는 것이다.

LAG (이전의 값을 가져옴)

select deptno, ename, sal,
	lead(sal,2) over (order by sal desc) as pre_sal
from emp; 

• lead()함수는 지정된 행의 값을 가지고 오는 것이다.
  • 위의 예시는 sal에서 2번째의 행의 값을 가지고 온다.
• lead의 기본값은 1이며, 첫 번째 행의 값을 가지고 온다.

---

 

비율 관련 함수

• 비울 관련 함수는 누적 백분율, 순서 백분율, 파티션을 N분으로 분한할 결과 등을 조회할 수 있다.

비율 관련 함수 예시

PERCENT_RANK()

select deptno, ename, sal
percent_rank() over (partition by deptno order by sal desc) as percent_sal
from emp; 

• percent_rank() 함수는 파티션에서 등수의 퍼센트를 구하는 함수
• 부서 내의 등수를 백분율을 구한다.

NTILE(n)

select deptno, ename, sal, 
	ntile(4) over (order by sal desc) as n_title
from emp;

• NTILE은 n등분으로 분할하라는 의미로 위의 예에서는 급여가 높은 순으로 1~4등분으로 분할한다.

select var, count(*) as cnt
from (select ntile(4) over (order by col1) as val
	from table_a)
where 1=1
groub by val order by 1

• WHERE 1=1은 참이라는 의미로 사용된다. WHERE 1=2는 거짓이라는 의미이다.
• ORDER BY 1은 첫번째 컬럼을 기준으로 ASC(오름차순) 정렬을 하라는 뜻이다.
• NTILE(4)는 TABLE_A의 COL1의 값을 4개의 파티션으로 나눌 것이다.