[SQLD] #026 SQL 활용 - 윈도우 함수

목차

1. WINDOW FUNCTION 개요

2. 그룹 내 순위 함수

3. 일반 집계 함수

4. 그룹 내 행 순서 함수

5. 그룹 내 비율 함수

 

 

---

 

 

 

1. WINDOW FUNCTION 개요

기존 관계형 데이터베이스는 칼럼과 칼럼간의 연산, 비교, 연결이나 집합에 대한 집계는 쉬운 반면, 행과 행간의 관계를 정의하거나, 행과 행간을 비교, 연산하는 것을 하나의 SQL 문으로 처리하는 것은 매우 어려운 문제였다. 

 

 

1. WINDOW FUNCTION 종류

WINDOW FUNCTION의 종류는 크게 다섯 개의 그룹으로 분류할 수 있는데, 벤더별로 지원하는 함수에는 차이가 있다. 

 

- 그룹 내 순위 관련 함수 : RANK, DENSE_RANK, ROW_NUMBER  

- 그룹 내 집계 관련 함수 : SUM, MAX, MIN, AVG, COUNT 

- 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD 

- 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT 

 

 

 

2. WINDOW FUNCTION SYNTAX

SELECT WINDOW_FUNCTION (ARGUMENTS) OVER 
( [PARTITION BY 컬럼] [ORDER BY 절] [WINDOWING 절] )
FROM 테이블명;

- WINDOW_FUNCTION : 기존에 사용하던 함수도 있고, 새롭게 WINDOW 함수용으로 추가된 함수도 있다.

- ARGUMENTS (인수) : 함수에 따라 0~N개의 인수가 지정될 수 있다.

- PARTITION BY 절 : 전체 집합을 기준에 의해 소그룹으로 나눌 수 있다. 

- WINDOWING 절 : WINDOWING 절은 함수의 대상이 되는 행 기준의 범위를 강력하게 지정할 수 있다. ROWS는 물리적인 결과 행의 수를, RANGE는 논리적인 값에 의한 범위를 나타내는데, 둘 중의 하나를 선택해서 사용할 수 있다. (다만 WINDOWING절은 SQL Server에서는 지원하지 않음)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. 그룹 내 순위 함수

1. RANK 함수

RANK 함수는 ORDER BY를 포함한 쿼리문에서 특정 칼럼에 대한 순위를 구하는 함수이다. 이때 특정 범위내에서 순위를 구할 수 있고, 전체 데이터에 대한 순위를 구할 수도 있다. 또한 동일한 값에 대해서는 동일한 순위를 부여한다. 

 

예제) 사원 데이터에서 급여가 높은 순서와 JOB 별로 급여가 높은 순서를 같이 출력한다.

SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) ALL_RANK,
       RANK( ) OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

– 업무 구분이 없는 ALL_RANK 칼럼에서 동일한 SAL 값은 동일한 순위를 부여한다. 
– 업무를 PARTITION 으로 구분한 JOB_RANK 는 업무 내 범위에서만 순위를 부여한다.
– 하나의 SQL 문에 (ORDER BY SAL DESC) 와 (PARTITION BY JOB) 조건이 충돌하여 JOB 별로는 정렬되지 않고, SAL DESC 조건으로 정렬되었다.

 

 

 

 

예제) 전체 SALARY 순위를 구하는 ALL_RANK 칼럼은 제외하고, 업무별로 SALARY 순서를 구하는 JOB_RANK만 출력한다.

SELECT JOB, ENAME, SAL,
       RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK
FROM EMP;

업무별로 SALARY 순서를 구하는 JOB_RANK만 사용한 경우 파티션의 기준이 된 JOB과 SALARY별로 정렬이 된다. 

 

 

 

 

 

 

 

 

2. DENSE_RANK 함수

DENSE_RANK 함수는 RANK 함수와 흡사하나, 동일한 순위를 하나의 건수로 취급하는 것이 차이점이다.

 

 

예제) 사원데이터에서 급여가 높은 순서와, 동일한 순위를 하나의 등수로 간주한 결과도 같이 출력한다. 

SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) RANK,
       DENSE_RANK( ) OVER (ORDER BY SAL DESC) DENSE_RANK
FROM EMP;

 

 

 

 

 

3. ROW_NUMBER 함수

ROW_NUMBER 함수는 RANK나 DENSE_RANK 함수가 동일한 값에 대해서는 동일한 순위를 부여하는데 반해, 동일한 값이라도 고유한 순위를 부여한다. 

 

 

예제) 사원데이터에서 급여가 높은 순서와, 동일한 순위를 인정하지 않는 등수도 같이 출력한다.

SELECT JOB, ENAME, SAL,
       RANK( ) OVER (ORDER BY SAL DESC) RANK,
       ROW_NUMBER() OVER (ORDER BY SAL DESC) ROW_NUMBER
FROM EMP;

 

 

 

 

 

 

 

 

 

 

 

3. 일반 집계 함수

1. SUM 함수

SUM 함수를 이용해 파티션별 윈도우의 합을 구할 수 있다.

 

 

예제) 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 합을 구한다.

SELECT MGR, ENAME, SAL, SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM
FROM EMP;

 

 

예제) OVER 절 내에 ORDER BY 절을 추가해 파티션 내 데이터를 정렬하고, 이전 SALARY 데이터까지의 누적값을 출력한다. (단 SQL Server의 경우, OVER절 내의 ORDER BY 절을 지원하지 않음)

SELECT MGR, ENAME, SAL,
       SUM(SAL) OVER (PARTITION BY MGR ORDER BY SAL RANGE UNBOUNDED PRECEDING) AS MGR_SUM
FROM EMP;

 

 

 

 

 

 

2. MAX 함수

MAX 함수를 이용해 파티션별 윈도우의 최댓값을 구할 수 있다. 

 

 

예제) 사원들의 급여와 같은 매니저를 두고 있는 사원들의 SALARY 중 최댓값을 같이 구한다. 

SELECT MGR, ENAME, SAL,
       MAX(SAL) OVER (PARTITION BY MGR) AS MGR_MAX
FROM EMP;

 

추가로, INLINE VIEW를 이용해 파티션별 최댓값을 가진 행만 추출할 수도 있다. 

SELECT MGR, ENAME, SAL
FROM (SELECT MGR, ENAME, SAL,
               MAX(SAL) OVER (PARTITION BY MGR) AS IV_MAX_SAL
      FROM EMP) 
WHERE SAL = IV_MAX_SAL ;

 

 

 

 

 

 

3. MIN 함수

MIN 함수를 이용해 파티션별 윈도우의 최솟값을 구할 수 있다.

 

예제) 사원들의 급여와 같은 매니저를 두고있는 사원들을 입사일자를 기준으로 정렬하고, SALARY 최솟값을 같이 구한다. 

SELECT MGR, ENAME, HIREDATE, SAL,
       MIN(SAL) OVER(PARTITION BY MGR ORDER BY HIREDATE) AS MGR_MIN
FROM EMP;

 

 

 

 

 

4. AVG 함수

AVG 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다. 

 

예제) EMP 테이블에서 같은 매니저를 두고 있는 사원들의 평균 SALARY를 구하는데, 조건은 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원만 대상으로 한다. 

SELECT MGR, ENAME, HIREDATE, SAL,
       ROUND (AVG(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)) AS MGR_AVG
FROM EMP;

 

 

 

 

 

 

5. COUNT 함수

COUNT 함수와 파티션별 ROWS 윈도우를 이용해 원하는 조건에 맞는 데이터에 대한 통계값을 구할 수 있다. 

 

예제) 사원들을 급여 기준으로 정렬하고, 본인의 급여보다 50 이하가 적거나 150 이하로 많은 급여를 받는 인원수를 출력하라.

SELECT ENAME, SAL,
       COUNT(*) OVER (ORDER BY SAL RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS SIM_CNT
FROM EMP;

 

 

 

 

 

 

 

 

 

 

4. 그룹 내 행 순서 함수

1. FIRST_VALUE 함수

FIRST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 먼저 나온 값을 구한다. SQL Server에서는 지원하지 않는 함수이다. 

 

예제) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 먼저 나온 값을 출력한다. 

SELECT DEPTNO, ENAME, SAL,
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS UNBOUNDED PRECEDING) AS DEPT_RICH
FROM EMP;

– RANGE UNBOUNDED PRECEDING : 현재 행을 기준으로 파티션 내의 첫 번째 행까지의 범위를 지정한다.

 

 

예제) 앞의 SQL 문장에서 같은 값을 가진 FIRST_VALUE 를 처리하기 위해 ORDER BY 정렬 조건을 추가한다.

SELECT DEPTNO, ENAME, SAL,
       FIRST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC, ENAME ASC ROWS UNBOUNDED PRECEDING)
       AS RICH_EMP
FROM EMP;

– 같은 부서 내에 최고 급여를 받는 사람이 두명인 경우를 대비해서 이름을 두 번째 정렬 조건으로 추가한다.

 

 

 

 

 

 

 

2. LAST_VALUE 함수

LAST_VALUE 함수를 이용해 파티션별 윈도우에서 가장 나중에 나온 값을 구한다. SQL Server에서는 지원하지 않는 함수이다. 

 

예제) 부서별 직원들을 연봉이 높은 순서부터 정렬하고, 파티션 내에서 가장 마지막에 나온 값을 출력한다. 

SELECT DEPTNO, ENAME, SAL,
       LAST_VALUE(ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_POOR
FROM EMP;

 

 

 

 

 

3. LAG 함수

LAG 함수를 이용해 파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는다. 

 

예제) 직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한명 앞선 사원의 급여를 본인의 급여와 함께 출력한다.

SELECT ENAME, HIREDATE, SAL,
       LAG(SAL) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

 

 

LAG 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데, 두 번째 인자는 몇번 째 앞의 행을 가져올지 결정하는 것이고, 세 번째 인자는 파티션의 첫번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데 이 경우 다른 값으로 바꿔줄 수 있다. 결과적으로는 NVL이나 ISNULL 기능과 같다.

SELECT ENAME, HIREDATE, SAL,
       LAG(SAL, 2, 0) OVER (ORDER BY HIREDATE) AS PREV_SAL
FROM EMP
WHERE JOB = 'SALESMAN';

-- LAG(SAL, 2, 0)의 기능은 두 행 앞의 SALARY를 가져오고, 가져올 값이 없는 경우는 0으로 처리한다.

 

 

 

 

 

 

 

4. LEAD 함수

LEAD 함수를 이용해 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. SQL Server에서는 지원하지 않는 함수이다. 

 

예제) 직원들을 입사일자가 빠른 기준으로 정렬하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력한다. 

SELECT ENAME, HIREDATE,
       LEAD(HIREDATE, 1) OVER (ORDER BY HIREDATE) AS "NEXTHIRED"
FROM EMP;

LEAD 함수는 3개의 ARGUMENTS 까지 사용할 수 있는데, 두 번째 인자는 몇 번째 후의 행을 가져올지 결정하는 것이고, 세 번째 인자는 예를 들어 파티션의 마지막 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데, 이 경우 다른 값으로 바꿔줄 수 있다. 결과적으로 NVL이나 ISNULL 기능과 같다. 

 

 

 

 

 

 

5. 그룹 내 비율 함수

1. RATIO_TO_REPORT 함수

RATIO_TO_REPORT 함수를 이용해 파티션 내 전체 SUM값에 대한 행별 칼럼 값의 백분율을 소수점으로 구할 수 있다. 결과 값은 > 0 & <= 1 의 범위를 가진다. 그리고 개별 RATIO의 합을 구하면 1이 된다. SQL Server에서는 지원하지 않는다. 

 

예제) JOB이 SALESMAN인 사원들을 대상으로 전체 급여에서 본인이 차지하는 비율을 출력한다. 

SELECT ENAME, SAL,
       ROUND(RATIO_TO_REPORT(SAL) OVER (), 2) AS R_R
FROM EMP
WHERE JOB = 'SALESMAN';

 

 

 

 

 

 

 

2. PERCENT_RANK 함수

PERCENT_RANK 함수를 이용해 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여 값이 아닌 행의 순서별 백분율을 구한다. 결과값은 >= 0 & <= 1 의 범위를 가진다. 단, SQL Server에서는 지원하지 않는다. 

 

예제) 같은 부서 소속 사원들의 집합에서 본인의 급여가 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력한다.

SELECT DEPTNO, ENAME, SAL,
       PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS P_R
FROM EMP;

– DEPTNO 10 은 3건이므로 구간은 2개가 되며, 0과 1 사이를 2개로 나누면 0, 0.5, 1 이 된다.

– DEPTNO 20 은 5건이므로 구간은 4개가 되며, 0과 1 사이를 4개로 나누면 0, 0.25, 0.5, 0.75, 1 이 된다.

– DEPTNO 30 은 6건이므로 구간은 5개가 되며, 0과 1 사이를 5개로 나누면 0, 0.2, 0.4, 0.6, 0.8, 1 이 된다.

 

 

 

 

3. CUME_DIST 함수

CUME_DIST 함수를 이용해 파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구한다. 결과값은 > 0 & <= 1 의 범위를 가진다. 이것도 SQL Server에서는 지원하지 않는다. 

 

예제) 같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번째 위치쯤에 있는지 0과 1사이의 값으로 출력한다. 

SELECT DEPTNO, ENAME, SAL,
       CUME_DIST() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) AS CUME_DIST
FROM EMP;

 

 

 

 

 

4. NTILE 함수

NTILE 함수를 이용해 파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있다. 

 

예제) 전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개의 그룹으로 분류한다. 

SELECT ENAME, SAL,
       NTILE(4) OVER (ORDER BY SAL DESC) AS QUAR_TILE
FROM EMP;

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.