SQLD | SQL 활용 - 윈도우 함수(Window Function)

이 글은 SQLD를 공부하며 윈도우 함수(Window Function)에 대해 정리한 글이다.

---

윈도우 함수

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

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

 

윈도우 함수 구조

• ARGUMENTS
  • 윈도우 함수에 따라서 0~n개의 인수를 설정
• PARTITION BY
  • 전체 집합을 기준에 의해 소그룹으로 나눔
• ORDER BY
  • 어떤 항목에 대해서 정렬
• WINDOWING
  • 행 기준의 범위를 정함
  • ROWS는 물리적 결과의 행 수이며, RANGE는 논리적인 값에 의한 범위

 

WINDOWING

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

SELECT STDNO, SNAME, AGE
	SUM(AGE) OVER(ORDER BY AGE
         		  ROWS BETWEEN UNBOUNDED PRECEDING
         		  AND CURRENT ROW) CURRENT_TOTAL
    FROM STD;

• STD 테이블에서 STDNO, SNAME, AGE 칼럼을 선택
• 선택한 칼럼을 AGE 순서로 정렬
• CURRENT_TOTAL 칼럼에는 첫 행(UNBOUNDED PRECEDING)부터 현재 행(CURRENT ROW)까지의 합계(SUM(AGE))를 조회

윈도우함수 실행 결과

---

순위 함수

• 윈도우 함수는 특정 항목과 파티션에 대해서 순위를 계산할 수 있는 함수를 제공함
• 순위 함수는 RANK, DENSE_RANK, ROW_NUMBER 함수가 있음 

 

순위 관련 윈도우 함수

• RANK
  • 특정 항목 및 파티션에 대해서 순위를 계산
  • 동일한 순위는 동일한 값이 부여

SELECT STDNO, SNAME, AGE,
    RANK() OVER(ORDER BY AGE) ALL_RANK
    FROM STD;

• DENSE_RANK
  • 동일한 순위를 하나의 건수로 계산

SELECT STDNO, SNAME, AGE,
    RANK() OVER(ORDER BY AGE) ALL_RANK,
    DENSE_RANK() OVER(ORDER BY AGE) ALL_RANK
    FROM STD;

RANK 함수와 DENSE_RANK 함수의 실행 결과 차이

• ROW_NUMBER
  • 동일한 순위에 대해서 고유의 순위를 부여

SELECT STDNO, SNAME, AGE,
    RANK() OVER(ORDER BY AGE) ALL_RANK,
    ROW_NUMBER() OVER(ORDER BY AGE) ROW_NUMBER
    FROM STD;

RANK 함수와 ROW_NUMBER 함수의 실행 결과 차이

---

집계 함수(AGGERGATE Function)

• 윈도우 함수를 제공

 

집계 관련 윈도우 함수

• SUM
  • 파티션 별로 합계를 계산
• AVG
  • 파티션 별로 평균을 계산
• COUNT
  • 파티션 별로 행 수를 계산
• MAX & MIN
  • 파티션 별로 최댓값과 최솟값을 계산

SELECT STDNO, SNAME, AGE,
    SUM(AGE) OVER(PARTITION BY DEPTNO) SUM_DEPT
    FROM STD;

---

행 순서 관련 함수

• 행 순서 관련 함수는 상위 행의 값을 하위에 출력하거나 하위 행의 값을 상위 행에 출력할 수 있음
• 특정 위치의 행을 출력할 수 있음

 

행 순서 관련 윈도우 함수

• FIRST_VALUE
  • 파티션에서 가장 처음에 나오는 값을 구함
  • MIN 함수를 사용해서 같은 결과를 구할 수 있음
  • 아래 코드는 STD 테이블에서(FROM STD) 각 학과 별(PARTITION BY DEPTNO) 나이가 제일 많은(ODER BY AGE DESC) 학생(FIRST_VALUE(SNAME))을 DEPT_A 칼럼(AS DEPT_A)에 학과 별로 조회하는 코드

SELECT DEPTNO, SNAME, AGE,
    FIRST_VALUE(SNAME)
        OVER(PARTITION BY DEPTNO 
             ORDER BY AGE DESC ROWS UNBOUNDED PRECEDING) AS DEPT_A
    FROM STD;

FIRST_VALUE 실행 결과

 

• LAST_VALUE
  • 파티션에서 가장 나중에 나오는 값을 구함
  • MAX 함수를 사용해서 같은 결과를 구할 수 있음
  • 아래 코드는 STD 테이블에서 각 학과 별 나이가 가장 어린 학생을 DEPT_A 칼럼에 학과 별로 조회하는 코드

SELECT DEPTNO, SNAME, AGE,
    LAST_VALUE(SNAME)
        OVER(PARTITION BY DEPTNO 
             ORDER BY AGE DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS DEPT_A
    FROM STD;

• LAG
  • 이전 행을 가지고 옴
  • 아래 코드는 STD 테이블에서 나이 순으로 정렬한 후, 바로 앞 행의 학생 이름을 가져와 PRE_SDT 칼럼에 조회하는 코드

SELECT DEPTNO, SNAME, AGE,
    LAG(SNAME) OVER(ORDER BY AGE) AS PRE_STD
    FROM STD;

• LEAD
  • 윈도우에서 특정 위치의 행을 가지고 옴
  • 기본값은 1

SELECT DEPTNO, SNAME, AGE,
    LEAD(SNAME, 2) OVER(ORDER BY AGE) AS LEAD_A
    FROM STD;

LEAD 함수 실행 결과

---

비율 관련 함수

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

 

비율 관련 윈도우 함수

• CUME_DIST
  • 파티션 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 조회
  • 누적 분포상에 위치를 0~1 사이의 값으로 표현
• PERCENT_RANK
  • 파티션에서 제일 먼저 나온 것을 0으로 제일 늦게 나온 것을 1로 하여 값이 아닌 행의 순서별 백분율을 조회
• NTILE
  • 파티션별로 전체 건수를 ARGUMENT 값으로 N 등분한 결과를 조회
• RATIO_TO_REPROT
  • 파티션 내에 전체 SUM(칼럼)에 대한 행 별 칼럼 값의 백분율을 소수점까지 조회

---

Reference

1. 2024 이기적 SQL 개발자 이론서 + 기출문제 / https://product.kyobobook.co.kr/detail/S000212055970

2024 이기적 SQL 개발자 이론서+기출문제 | 임호진 - 교보문고