[SQL] 원도우 함수(WINDOW FUNCTION)1

1. 원도우 함수

• 행과 행간의 관계를 정의하기 위해서 제공되는 함수
• 원도우 함수를 사용해서 순위, 합계, 평균, 행 위치 등을 조작할 수 있음.

 

• 윈도우 함수 구조

#구조
SELECT WINDOW_FUNCTION('인수')
		OVER (PARTITION BY '컬럼' ORDER BY '컬럼' WINDOWING 절) 
FROM 테이블명;

• ARGUMENTS(인수): 윈도우 함수에 따라 0 ~ N개의 인수를 설정
• PARTITION BY: 전체 집합 기준에 의해 소그룹으로 분할
• ORDER BY : 어떤 항목에 대해 순위를 지정할지 order by 절을 기술
• WINDOWING: 행 기준의 범위를 지정

 

• WINDOWING
  • ROWS: 부분집합인 원도우의 크기를 물리적 단위로 행의 집합을 지정
  • RANGE: 논리적 주소에 의해 행 집합을 지정
  • BETWEEN ~ AND : 원도우의 시작과 끝 위치를 지정
  • UNBOUNDED PRECEDING: 원도우의 시작 위치가 첫 번째 행임을 의미
  • UNBOUNDED FOLLOWING: 원도우의 마지막 위치가 마지막 행임을 의미
  • CURRENT ROW: 원도우의 시작 위치가 현재 행임을 의미

---

2. 그룹 내 순위(RANK)관련 함수

• RANK(): 특정 함수 및 파티션에 대해서 순위를 계산하며 동일한 순위는 동일값이 부여됨
  • 예를 들어 table의 'age' 변수가 30, 30, 20인 경우가 있다고 할 때, RANK함수는 순위를 1, 1, 3을 출력   
• DENSE_RANK(): 동일한 순위를 하나의 건수로 계산
  • DENSE_RANK는 1, 1, 2 출력
• ROW_NUMBER(): 동일한 순위에 대해서 고유의 순의 부여
  • ROW_NUMBER은 1, 2, 3을 출력. (1순의의 값과 2순위의 값을 동일해도 고유의 순위를 출력.)

 

 

 

 

RNAK 함수

emp 테이블에서 직업이 'ANALYST', 'MANAGER'인 경우에 이름, 직업, 월급 월급의 순위를 출력해 보겠습니다. 

SELECT ename, job, sal, RANK() OVER(ORCER BY sal DESC) 순위
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

 

순위를 보면 SCOTT와 FORD의 월급이 3000으로 동일해서 동일한 순위 2를 출력하였고, JONES는 4를 출력했습니다. 

 

 

이번에는 직업별로 월급이 높은 순서대로 순위를 부여해 보겠습니다.

SELECT ename, sal, job, RANKE() OVER(PARTTITION BY job 
                                    ORDER BY sal DESC) AS 순위
    FROM emp;

ORDER BY절 앞에 PARTITION BY를 사용하면 직업별로 묶어서 순위를 출력하는 것을 확인할 수 있습니다.

 

 

 

DENSE_RANK

마찬가지로 emp 테이블에서 직업이 'ANALYST', 'MANAGER'인 경우에 이름, 직업, 월급 월급의 순위를 출력해 보겠습니다.

그런데 이때 순위가 1인 사원이 두 명이 있을 경우 다음 순위가 3으로 출력되지 않고 2위로 출력해 보겠습니다.

 

SELECT ename, job, sal, RANK() OVER (ORDER BY sal DESC) AS RANK,
                        DENSE_RANK() OVER (ORDER BY sal DESC) AS DENSE_RANK
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

비교하기 편하라고 RANK함수와 같이 사용해 봤습니다.

RANK함수는 순위 2이가 두 명이어서 다음에 바로 4를 출력했지만, DENSE_RANK는 3위로 출력합니다.

 

 

 

DENSE_RANK 바로 다음에 나오는 괄호에도 다음과 같이 데이터를 넣고 사용할 수도 있습니다.

SELECT DENSE_RANK(3000) WITHIN GROUP (ORDER BY sal desc) AS 순위
    FROM emp;

월급이 3000인 사원은 사원 테이블에서 월급의 순위가 어떻게 되는지 출력하는 쿼리입니다. 

WINTIN GROUP은 어느 그룹 이내에서 3000의 순위가 어떻게 되는지 보겠다는 의미입니다.

여기서 어느 그룹은 바로 다음에 나오는 괄호 안의 문법에 해당합니다.

즉, 월급이 높은 순서대로 정렬해 놓은 데이터의 그룹 안에서 3000의 순위를 출력하는 것입니다.

RANK함수도 마찬가지로 적용 가능합니다.

 

 

 

ROW_NUMBER함수

마지막으로 ROW_NUMBER함수는 어떻게 작동하는지 살펴보겠습니다.

SELECT ename, job, sal, RANK() OVER (ORDER BY sal DESC) AS RANK,
                        DENSE_RANK() OVER (ORDER BY sal DESC) AS DENSE_RANK,
                        ROW_NUMBER() OVER (ORDER BY sal DESC) AS ROW_RANK
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

---

3. 집계 함수(AGGREGATE)

• 집계 함수: 여러 행으로부터 하나의 결과값을 반환하는 함수. 
  • COUNT(*)를 제외하고 집계 함수는 NULL값을 무시
  • SELECT 구문에서만 사용되며, 컬럼끼리 연산 수행.
  • WHERE절에서 바로 집계 함수 사용할 수 없음. GROUP BY를 하고 HAVING 절에서 집계 함수를 사용하거나 WHERE절에 서브 쿼리에서 사용 가능
  • 종류로 SUM, AVG, COUNT, MIN, MAX가 있음.

 

 

SUM

• 파티션 별 합을 계산

 

직업이 'ANALYST', 'MANAGER'인 사원들의 월급 합계를 출력해 보겠습니다.

SELECT ename, sal, job , SUM(sal) OVER (PARTITION BY JOB) AS SUM_SAL
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

 

 

 

 

AVG

• 파티션 별 평균을 계산

직업이 'ANALYST', 'MANAGER'인 사원들의 월급 평균을 출력해 보겠습니다.

SELECT ename, sal, job , AVG(sal) OVER (PARTITION BY JOB) AS AVG_SAL
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

 

 

 

COUNT

• 파티션 별 행의 개수 계산
• COUNT(*)는 NULL의 수도 세지만 COUNT('변수')의 경우 NULL값 제외

위의 그림은 EMP 테이블에 해당합니다. 

여기서 COUNT(*)과 COUNT('변수')의 차이를 확인하기 위해 임시로 데이터 하나를 추가해보겠습니다.

 

# 데이터 추가
insert into emp values (7945, 'JACK','MANAGER' , 7839, '82/12/20', null, null, 20); )

15번째에 데이터가 추가된 것을 확인할 수 있습니다.

이제 window function에서 작동하는 count함수를 적용하는지 확인하기 위해 직업별 월급을 카운팅 해 보면

select ename, job, sal, count(sal) over (partition by job) as count_sal,
                   count(*) over (partition by job) as count_start
    from emp
    where job = 'MANAGER';

특정 변수를 지정한 경우는 null값을 제외해서 3, 그렇지 않은 경우는 null값을 포함해서 4를 출력한 것을 알 수 있습니다.

 

 

 

MIN/ MAX

• 파티션 별 최솟값/ 최댓값 계산

직업이 'ANALYST', 'MANAGER'인 사원들의 각 월급의 최솟값과 최댓값을 출력해 보겠습니다.

 

SELECT ename, job, sal, MIN(sal) OVER (PARTITION BY job) AS min_sal,
                        MAX(sal) OVER (PARTITION BY job) AS max_sal
    FROM emp
    WHERE job IN ('ANALYST', 'MANAGER');

---

포스팅이 길어지는 관계로 행 순서 관련 함수와 비율 관련 함수 파트는 다음 글로 나눠서 게시하겠습니다.