SQL 윈도우 함수(집계함수와 윈도우 함수, 순위함수)

--윈도우 함수--

select * from emp;

select sum(sal) as sum_sal
from emp; 

select empno, ename, job, deptno, sum(sal) as sum_sal
from emp; --에러!--

합계, 평균, 갯수 등 집계된 값을 확인하기 위해서는 보통 group by를 사용하지만 group by를 사용할 경우 그룹된 값과 집계된 값 이외에는 값을 확인할 수 없다. 만약, 부서별로 급여의 합계를 구하고싶은데, 그 부서에 누가있는지, 직업은 뭔지 등 구체적인 정보와 함께 출력하고 싶다면 윈도우 함수를 사용하면 된다.
 
 

windowing function

--윈도우 함수_집계함수와 함께 사용하기--

--급여전체합계 구하기--
select empno, ename, job, deptno,
sum(sal) over() as sal_total
from emp
order by deptno;


--전체중 최대급여 구하기--
select empno, ename, job, deptno,
max(sal) over() as sal_max
from emp order by deptno;


--전체중 최소급여 구하기--
select empno, ename, job, deptno,
min(sal) over() as min_sal
from emp order by deptno;


--전체중 평균 급여 구하기--
select empno, ename, job,deptno,
round(avg(sal) over(),2) as sal_avg
from emp order by deptno;
--윈도우 함수에서 round를 쓸 경우 over()절까지 같이 묶어서 괄호안에 넣어야함!--


--전체 행 개수 구하기--
select empno, ename, job, deptno,
count(empno) over() as count_emp
from emp;

window함수는 select 절에 사용하며, 보통 구문이 길기때문에 as를 붙여서 window함수를 쓴 값을 볼 컬럼의 이름을 지정해야한다.

select empno, ename, job, deptno, sum(sal) over() as sal_total from emp order by deptno;

group by를 사용했다면 sum값인 단일값 하나만 확인할 수 있겠지만 이처럼 윈도우 함수를 사용하게 되면 급여의 전체합계와 동시에 다른 행들의 값도 확인이 가능하다.
 
 

partiton by

--patition by--

--부서별 급여 합계구하기--
select empno, ename, job, deptno, sal,
sum(sal) over(partition by deptno) as deptno_sal_sum
from emp;


--부서별 급여 평균 구하기--
select empno, ename, job, deptno, sal,
round(avg(sal) over(partition by deptno),2) deptno_sal_avg
from emp;

select empno, ename, job, deptno, sal, sum(sal) over(partition by deptno) as deptno_sal_sum from emp;

window함수에서 가장많이 사용되는 함수이다. partiton by는 파티션이라는 단어 그대로 구역을 나눠 계산할 수 있는 함수다.
급여의 합계를 보고싶은데 전체 합계가 아니라 부서별로 나눠서 부서별 급여 합계를 계산해야할때, partiton을 이용해 나눌 구역을 지정한다면 같은 partition by에 적혀진 컬럼의 값이 같은것들끼리 구역을 나눠 계산한 값을 반환한다.
 
 

order by / 구역설정

--order by를 추가하면 누적계산이 가능하다--
select empno, ename, job, deptno, sal,
sum(sal) over(partition by deptno order by sal) as sum_sal
from emp;

--누적계산시 범위 설정하기--
select empno, ename, job, deptno, sal,
sum(sal) over(partition by deptno order by sal rows between 1 preceding and 2 following) as sum_sal
from emp;

--누적 계산 --
select empno, ename, job, deptno, sal,
round(avg(sal) over(partition by deptno order by sal 
rows between unbounded preceding and 1 following),2) as avg_sal
from emp; --처음부터 현재행 하나 밑까지--

select empno, ename, job, deptno, sal, sum(sal) over(partition by deptno order by sal) as sum_sal from emp;

order by는 오름차순, 내림차순의 순서를 정하는 구문이지만 window함수에서 집계함수와 사용된다면 누적계산을 하겠다는 뜻을 가진다. over절 안에 order by를 적고 그 뒤에 계산할 행을 적는다면 값이 누적되어 계산된다. (적지않으면 전체 합)
order by를 사용할때 계산할 값의 범위를 따로 지정할 수있는데 만약 이를 지정하지 않는다면 누적계산시 디폴트 값은
unbounded preceding(맨처음부터) current row(현재행)이 된다.

select empno, ename, job, deptno, sal, sum(sal) over(partition by deptno order by sal rows between 1 preceding and 2 following) as sum_sal from emp;

범위 지정은 rows between n preceding and n following의 형태로 적으며 n에 숫자를 적으면 그 숫자에 따른 행 수만큼 범위가 적용된다. 이때 적용한 범위는 파티션을 벗어나지 않는다. 2 following인데 파티션기준으로 마지막에 위치한 행이라면 2 following은 진행되지 않는것이다.
 
 

range

--range--
select empno, ename, job, deptno, sal,
sum(sal) over(partition by deptno order by sal) as sum_sal
from emp; --같은 값은 하나로 출력--

--ragne로 값 범위 설정--
select empno, ename, job, deptno, sal,
count(*) over(order by sal range between 1000 preceding and 1000 following) as range_count
from emp; 
--현재행 기준에서 1000을 뺀 수 사이에 있고 현재행 기준에서 1000을 더한 수의 사이에 있는 값--

select empno, ename, job, deptno, sal, sum(sal) over(partition by deptno order by sal) as sum_sal from emp;

range는 window 함수의 기본으로 설정되어있는 옵션이다. 위의 결과처럼 누적값을 진행할때 값이 같은 것이 있다면 하나로 계산하여, 순서대로 계산한 값이 아니라 이미 값이 같은 값중 두번째 값까지 계산한 결과로 값을 출력한다. 이렇게 출력되는 것이 싫다면
rows로 범위를 설정하면 된다.

select empno, ename, job, deptno, sal, count(*) over(order by sal range between 1000 preceding and 1000 following) as range_count from emp;

이처럼 range로도 계산할 값의 범위를 지정할 수 있는데 range의 경우에는 rows와 다르게 행의 개수를 지정하는것이 아니라
계산된 값의 결과에 반영할 계산된 값의 범위를 지정한다. 위의 구문처럼 range between 1000 preceding and 1000 following
라고 적인 경우, 현재 행이 800이라고 가정했을때 800에서 1000을 뺸 값인(preceding) -200사이의 값과 800에서 1000을 더한 값인(following) 1800사이에 있는 값들만 출력하는 것이다. 
 
 

순위함수

순위함수는 해당 값의 순위를 출력하는 함수다. window함수에서 집계함수와 함께 사용할경우 order by는 누적계산을 의미 했지만 순위와함께 사용할 경우에는원래사용의미 대로 순서를 의미한다. 그렇기때문에 순위를 출력할때도 작은값을 1등으로 순위를 매기고 싶다면 asc를 적거나 아무것도 적지 않으면 된다.(큰 수부터 출력하고싶다면 당연히 desc적기..!)
 
순위함수 종류

• rank
• dense_rank
• row_number

 

rank

--rank--
select empno, ename, job, deptno, sal,
rank() over(order by sal) as sal_rank
from emp;

rank의 경우 동일 순위가 있을때 연속적인 값을 부여하는 것이 아니라, 동순위를 부여하되, 해당값을 하나 건너뛰고 다음 값을 정의하는걸 알 수 있다.
 
 

dense_rank

--dense_rank--
select empno, ename, job, deptno, sal,
dense_rank() over(order by sal) as dense_rank
from emp;

dense_rank는 값이 같을때 동일한 순위 부여 후 다음 순위가 바로 이어지는 순위부여 방식이다. 1등이 10명이 있더라도 바로 다음순위는 2등이 되는 형식이다. 
 
 

row_number

--row number--
select empno, ename, job, deptno, sal,
row_number() over(order by sal) as row_number
from emp;

연속된 행번호를 부여하며, 동일한 값이 있더라도 동순위로 인정하는 것이 아니라 단순히 행이 나열된 순서대로 값을 매겨 출력한다.
 
 

3가지 차이점 비교하기

--순위 함수 비교--
select empno, ename, job, deptno, sal,
rank() over(order by sal) as rank_sal,
dense_rank() over(order by sal) as dense_rank,
row_number() over(order by sal) as row_number
from emp;

 

---

 

학습일기

윈도우 함수를 처음 배웠을때는 쿼리문 익히는게 어려워서 그냥 group by를 사용했었었는데 쿼리문이 손에 익고나서는 한동안 group by보다 윈도우를 더 썼었다. 윈도우 함수도 부서별,  직업별 등 그룹으로 나누고 그와동시에 구체적인 값도 확인할수있기때문에 실무에서 사용하기 굉장히 좋은 쿼리문인것같아,  쿼리문 연습할때 실무상황이라고 가정 한 후 연습하면 더 좋을것같다..!