본문 바로가기
  • 오늘도 한걸음. 수고많았어요.^^
  • 조금씩 꾸준히 오래 가자.ㅎ
IT기술/DB

[sql] 윈도우 함수, 알고 넘어가기

by 미노드 2023. 7. 17.

윈도우 함수는 고급 SQL 기능인데요. 이는 분석가뿐만 아니라 보고서를 만드는 사람, 데이터를 가공하는 그 누구에게도 상당히 유용한 기능입니다.

윈도우 함수(Window Function)가 무엇인가요?

윈도우 함수는 테이블 내부에 '윈도우 프레임'이라고 부르는 범위를 정의하고, 해당 범위 내부에 포함된 값을 자유롭게 사용할 수 있습니다.
윈도우 함수는 GROUP BY와 상당히 유사합니다.
이유는 윈도우 함수도 GROUP BY처럼 여러 행의 집계 값을 계산할 수 있기 때문입니다.
하지만, GROUP BY와 달리 윈도우 함수는 여러 행들을 합쳐 1개의 행으로 만들지 않습니다. 기존에 있던 원래 값은 그대로 두고 집계 값만 추가로 구할 수 있습니다.

이 기능은 데이터 분석가, 마케터, 재무 담당자에게 꽤나 유용합니다.
오늘날의 대부분의 관계형 데이터베이스도 해당 함수를 지원합니다.
MySQL의 경우 최근까지만 해도 윈도우 함수를 지원하지 않았으나 버전 8.0 이후부터 해당 기능을 추가했습니다.

윈도우 함수의 종류

크게 5가지 그룹으로 분류할 수 있습니다.(벤더별로 지원하는 함수에 차이가 있을 수 있음)

  1. 그룹 내 순위(RANK) 관련 함수: RANK, DENSE_RANK, ROW_NUMBER
  2. 그룹 내 집계(Aggregate) 관련 함수 : SUM, MAX, MIN, AVG, COUNT (sql server는 OVER 절의 OREDER BY 지원 X)
  3. 그룹 내 행 순서 관련 함수 : FIRST_VALUE, LAST_VALUE, LAG, LEAD (오라클에서만 지원)
  4. 그룹 내 비율 관련 함수 : CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT  
  5. 선형 분석을 포함한 통계 분석 함수 (postgre)

윈도우 함수는 상황에 따라 서브쿼리를 대체할 수 있기도 합니다.
이용함으로써 쿼리가 간결해집니다.
사용되는 형식을 보면 다음처럼 생겼습니다.

윈도우함수(ARGUMENTS) OVER ([PARTITION BY 컬럼]  [ORDER BY 컬럼]  [WINDOWING 절])

다음처럼 윈도우 함수를 작성하면, 하나의 컬럼으로써 결과값을 출력할 수 있습니다.
AS로 별칭을 붙여 사용할 수도 있습니다.

 DENSE_RANK() OVER(ORDER BY ranking_score DESC) AS dense_rank_number
 ROW_NUMBER() OVER(ORDER BY ranking_score) AS row_number
 RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number
 LEAD(sale_value) OVER(PARTITION BY toy_name ORDER BY month)
 LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS prev_month_value
 SUM(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS total_toy_value

자주 쓰이는 윈도우 함수

- RANK

순위를 구하는 함수.
특정 범위(PARTITION) 내 또는 전체 데이터에 대한 순위를 구할 수 있습니다.
동일한 값에 대해서는 동일한 순위를 부여합니다.

사원 데이터에서 급여가 높은 순서와 JOB별로 급여가 높은 순서를 출력하는 예제

SELECT   JOB, ENAME, SAL,
         RANK() OVER (ORDER BY SAL DESC) ALL_RANK,  -- 급여 높은 순
         RANK() OVER (PARTITION BY JOB ORDER BY SAL DESC) JOB_RANK -- job 별로 급여 높은 순
FROM     EMP ;

동일한 급여가 있다면 같은 순위를 부여합니다.
PARTITION 으로 구분한 JOB_RANK 는 같은 업무(JOB) 범위 내에서만 순위를 부여합니다.

하나의 문장에 ORDER BY SAL DESC와  PARTITION BY JOB 의 조건이 충돌 났기 때문에 JOB별로는 정렬되지 않고, ORDER BY SAL DESC 조건으로 정렬됩니다.

- DENSE_RANK

RANK와 흡사하지만, 동일한 순위를 하나의 건수로 취급합니다.
RANK는 1, 1, 2, 3, 4순위로 표기하지만, DENSE_RANK는 1, 1, 3, 4, 5 순위를 부여합니다. (1위가 두개 이니 2위 없이 3위로 넘어감)

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

- ROW_NUMBER

RANK, DENSE_RANK 가 동일한 값에 대해서는 동일한 순위를 부여하지만,
ROW_NUMBER 는 동일한 값이라도 고유한 순위를 부여합니다.

때문에 ROW_NUMBER 는 동일한 순위를 배제하기 위해 유니크한 순위를 정해야 하는데,
이를 위한 추가 조건으로 ORDER BY를 참고합니다.

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

- SUM

SUM 함수를 이용해서 파티션별 윈도우의 합을 구할 수 있습니다.
사원들의 급여(SAL)와 같은 매니저(MGR)를 두고 있는 사원들(ENAME)의 급여(SAL) 합을 구하는 예제

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

OVER 절에 ORDER BY 절을 추가해서 파티션 내 데이터를 정렬하고 이전 급여 데이터까지의 누적값을 출력하는 예제

SELECT MGR, ENAME, SAL, 
       SUM(SAL) OVER (PARTITION BY MGR OE=RDER BY SAL RANGE UNBOUNDED PRECEDING) MGR_SUM
FROM   EMP ;

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

- MAX

파티션별 윈도우의 최대값을 구할 수 있습니다.
사원들의 급여(SAL)와 같은 매니저(MGR)를 두고 있는 사원들(ENAME)의 급여 중 최대값을 구하는 예제

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

INLINE VIEW를 이용해서 파티션별 최대값을 가진 행만 출력하기(매니저별 급여 제일많이받는 사원 출력하기)

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

- MIN

파티션별 윈도우의 최소값을 구할 수 있습니다. 
사원들의 급여(SAL)와 같은 매니저(MGR)를 두고 있는 사원(ENAME)을 입사일자(HIREDATE) 기준으로 정렬하고, 급여(SAL) 최소값을 같이 구하는 예제

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

- AVG

파티션별 통계값을 구할 수 있습니다.

같은 매니저(MGR)를 두고 있는 사원(ENAME)들의 평균 급여를 구하되, 같은 매니저 내에서 자기 바로 앞의 사번과 바로 뒤의 사번인 직원을 대상으로만 하는 예제 ( 앞 줄 + 나 + 뒷 줄의 합을 3으로 나누는 형식. 만약 앞줄이 없다면 나 + 뒷 줄의 합을 2로 나누게 된다)

SELECT MGR, ENAME, HIREDEAT, SAL, 
       ROUND (AGV(SAL) OVER (PARTITION BY MGR ORDER BY HIREDATE) 
       ROW BETWEEN 1 PRECEDING AND 1 FOLLOWING) ) MGR_AVG
FROM   EMP ;

- COUNT

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

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

RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING ;
-- 현재 행의 급여값을 기준으로 금여가 -50 ~ +150 범위 내에 포함된 모든 행이 대상이 된다. 
-- range는 현재 행의 데이터 값을 기준으로 앞 뒤 데이터 값의 범위를 표시하는 것임.

 

그룹 내 행 순서 관련 함수

- FIRST_VALUE

파티션별 윈도우에서 가장 먼저 나온 값을 구할 수 있습니다.
mssql 에서는 지원하지 않음.
정렬기준에 따라 먼저 나온 값을 출력하는데 사용합니다.

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

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

FIRST_VALUE 는 공동 등수를 인정하지 않고 처음 나온 행을 처리합니다.
원하는 값을 출력하려면 정렬 기준을 맞춰야 할 경우도 있습니다.

정렬을 추가한 예제. 같은 급여가 있다면 이름 오름차순으로 나오게 된다.

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

- LAST_VALUE

파티션별 윈도우에서 가장 마지막에 나올 값을 출력합니다.

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

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

ROW BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
-- 현재 행을 포함해서 파티션 내의 마지막 행까지의 범위를 지정한다.

- LAG

파티션별 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다.(먼저 출력 된 것을 출력하는 것이며, 가장 첫번째 행에선 이보다 먼저 출력된 행이 없으므로 값이 NULL로 표시되거나 빈값에 어떻게 표시할지 정할 수 있습니다.)
직원들을 입사일자가 빠른 기준으로 정렬하고, 본인보다 입사일자가 한 명 앞선 사원의 급여를 본이의 급여와 함께 출력하는 예제

SELECT  ENAME, HIREDATE, SAL,
        LAG(SAL) OVER (ORDER BY HIREDATE) PREV_SAL
FROM    EMP
WHERE   JOB = 'SALESMAN' ;
[실행 결과] 
ENAME   HIREDATE  SAL  PREV_SAL 
------- --------- ---- ------- 
ALLEN   1981-02-20 1600 
WARD    1981-02-22 1250 1600 
TURNER  1981-09-08 1500 1250 
MARTIN  1981-09-28 1250 1500 

4개의 행이 선택되었다.

LAG 함수는 3개의 인수까지 사용할 수 있습니다.

LAG(SAL, 2, 0) <-- 두번째 인수는 몇 번째 앞의 행을 가져올지 결정하는 것이고 (디폴트는 1. 여기서는 2을 지정했으니까 2번째 앞에 있는 행을 가져오는 것), 세번째 인수는 파티셧 첫 번째 행의 경우 가져올 데이터가 없어 NULL 값이 들어오는데, 이 경우 NULL대신 0으로 출력하도록 예시 작성 합니다. (NVL/ ISNULL 과 같은 기능)

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

-- LAG(SAL,2,0) : 두 행 앞의 급여를 가져오고, 가져올 값이 없으면 0으로 처리하라.
[실행 결과] 
ENAME   HIREDATE  SAL  PREV_SAL 
------- --------- ---- ------- 
ALLEN   1981-02-20 1600 0
WARD    1981-02-22 1250 0 
TURNER  1981-09-08 1500 1600 
MARTIN  1981-09-28 1250 1250 

4개의 행이 선택되었다.

- LEAD

파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있다. 
sql server 에서는 지원하지 않음.
직원들을 입사일자가 빠른 기준으로 정렬하고, 바로 다음에 입사한 인력의 입사일자를 함께 출력하는 예제

SELECT  ENAME, HIREDATE,
        LEAD(HIREDATE) OVER (ORDER BY HIREDATE) NEXTHIRED
FROM    EMP ;
[실행 결과] 
ENAME    HIREDATE   NEXTHIRED 
-------- ---------  --------- 
ALLEN    1981-02-20 1981-02-22 
WARD     1981-02-22 1981-04-02 
TURNER   1981-09-08 1981-09-28 
MARTIN   1981-09-28 

4개의 행이 선택되었다.

LAG 처럼 LEAD 함수도 3개의 인수까지 사용할 수 있다. 

 

그룹 내 비율 관련 함수

- CUME_DIST

파티션별 윈도우의 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적백분율 출력
mssql은 지원안됨
같은 부서 소속 사원들의 집합에서 본인의 급여가 누적 순서상 몇 번재 위치쯤에 있는지 0~1 사이의 값으로 출력하는 예제

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

- PERCENT_RANK

파티션별 함수를 이용해서 파티션별 윈도우에서 제일 먼저 나오는 것을 0으로, 제일 늦게 나오는 것을 1로 하여, 행의 순서별 백분율 출력. (값이 아니라 행의 순서별 백분율이다....)
mssql은 지원안됨

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

SELECT  ENAME, SAL,
        PERCENT_RANK() OVER (PARTITION BY DEPTNO ORDER BY SAL DESC) P_R
FROM    EMP ;
[실행 결과] 
DEPTNO  ENAME SAL  P_R 
------ ------ ---- ---- 
10      KING   5000 0 
10      CLARK  2450 0.5 
10      MILLER 1300 1 
20      SCOTT  3000 0 
20      FORD   3000 0 
20      JONES  2975 0.5 
20      ADAMS  1100 0.75 
20      SMITH  800  1 
30      BLAKE  2850 0 
30      ALLEN  1600 0.2 
30      TURNER 1500 0.4 
30      MARTIN 1250 0.6 
30      WARD   1250 0.6 
30      JAMES  950  1 

14개의 행이 선택되었다.

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이 된다. 

- NTILE

파티션별 전체 건수를 ARGUMENT 값으로 N등분한 결과를 구할 수 있습니다.
전체 사원을 급여가 높은 순서로 정렬하고, 급여를 기준으로 4개 그룹으로 분류. 

SELECT  ENAME, SAL,
        NTILE(4) OVER (ORDER BY SAL DESC) QUAR_TILE
FROM    EMP ;
[실행 결과] 
DEPTNO ENAME    SAL  QUAR_TILE 
------ ------- ---- -------- 
10     KING    5000 1 
10     FORD    3000 1 
10     SCOT    3000 1 
20     JONES   2975 1 
20     BLAKE   2850 2 
20     CLARK   2450 2 
20     ALLEN   1600 2 
20     TURNER  1500 2 
30     MILLER  1300 3 
30     WARD    1250 3 
30     MARTIN  1250 3 
30     ADAMS   1100 4 
30     JAMES   950  4 
30     SMITH   800  4 

14개의 행이 선택되었다.

 NTILE(4) 의 의미는 14명의 팀원을 4개 조로 나눈다는 의미입니다. 
14명을 4개의 집합으로 나누면 몫이 3, 나머지가 2가 이므로 나머지 두 명의 앞의 조부터 할당됩니다.

따라서 4명 + 4명 + 3명 + 3명 으로 조를 나누게 됩니다.

- RATIO_TO_REPORT 

파티션 내 전체 SUM(컬럼) 값에 대한 행별 컬럼 값의 백분율을 소수점으로 구할 수 있습니다.
결과값은 > 0 & <= 1 의 범위를 가지며, 개별 ratio 의 합을 구하면 1이 됩니다.
mssql 에서는 지원하지 않음.

세일즈맨 대상으로, 전체 급여에서 본인이 차지하는 비율을 구하는 예제

SELECT  ENAME, SAL,
        ROUND (RATIO_TO_REPORT(SAL) OVER (), 2) P_R
FROM    EMP
WHERE   JOB = 'SALESMAN' ;
[실행 결과] 
ENAME  SAL   R_R 
------ ---- ---- 
ALLEN  1600 0.29 (1600 / 5600) 
WARD   1250 0.22 (1250 / 5600) 
MARTIN 1250 0.22 (1250 / 5600) 
TURNER 1500 0.27 (1500 / 5600) 

4개의 행이 선택되었다.

윈도우 함수 너무나 종류가 많으므로 자주써야 외워질 것 같습니다.

필요한 용도에 따라 활용하면 좋은 부분이 많아 보입니다.

'IT기술 > DB' 카테고리의 다른 글

[OracleDB] 오라클 DB 설치 및 사용방법(Express Edition(XE), sql plus, sql developer)  (2) 2023.07.17
[sql] NVL 함수, 쿼리 null 처리하기  (0) 2023.07.17
[sql] join 문법 정리, inner join, outer join  (0) 2023.07.14
[sql] group by, partition by 사용방법, 그룹화/ 묶어서 표현하기  (0) 2023.07.14
[sql] DB 설계, 어떻게 하는걸까?  (0) 2023.07.14

관련글

티스토리툴바