[정리노트] SQLD 2024년 개정판 - 윈도우 함수

 

[정리노트] SQLD 2024년 개정판 - 윈도우 함수

---

WINDOW FUNCTION

• 서로 다른 행의 비교나 연산을 위해 만든 함수
• GROUP BY를 쓰지 않고 그룹 연산 가능
• LAG, LEAD, SUM, AVG, MIN, MAX, COUNT, RANK
• 문법

SELECT 윈도우함수([대상]) OVER ([PARTITION BY 칼럼]
                                [ORDER BY 컬럼 ASC|DESC]
                                [ROWS|RANGE BETWEEN A AND B]);

** PARTITION BY 절: 출력할 총 데이터 수 변화 없이 그룹연산 수행할 GROUP BY 컬럼

** ORDER BY 절

• RANK 의 경우 필수(정렬 컬럼 및 정렬 순서에 따라 순위 변화
• SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력 시 사용

 

** ROUS|RANGE BETWEEN A AND B

• 연산 범위 설정
• ORDER BY 절 필수

※ PARTITION BY, ORDER BY, ROWS.. 절 전달 순서 중요 (ORDER BY 를 PARTITION BY 전에 사용 불가)

(예제) 그룹함수 오류 (윈도우 함수가 필요한 이유)

SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL)
  FROM EMP;

→ 전체를 출력하는 컬럼과 그룹함수 결과는 함께 출력할 수 없음

 

---

그룹함수의 형태

• SUM, COUNT, AVG, MIN, MAX 등
• OVER 절을 사용하여 윈도우 함수로 사용 가능
• 반드시 연산할 대상을 그룹함수의 입력값으로 전달
• 문법

SELECT SUM([대상]) OVER ([PARTITION BY 칼럼]
                                [ORDER BY 컬럼 ASC|DESC]
                                [ROWS|RANGE BETWEEN A AND B]);

 

1. SUM OVER()

• 전체 총 합, 그룹별 총 합 출력 가능

EX) 각 직원 정보와 함께 급여 총 합 출력

** 에러: 각 직원 정보와 급여 총 합(그룹함수 결과)을 동시에 출력 시도 시 에러 발생

SELECT EMPNO, ENAME, SAL, DEPTNO, SUM(SAL)
  FROM EMP;

 

**해결1: 서브쿼리 사용(스칼라 서브쿼리)

SELECT EMPNO, ENAME, SAL, DEPTNO,
      (SELECT SUM(SAL) FROM EMP) AS TOTAL
  FROM EMP;

 

**해결2: 윈도우 함수 사용

SELECT EMPNO, ENAME, SAL, DEPTNO,
       SUM(SAL) OVER() AS TOTAL
  FROM EMP;

 

2. AVG OVER(): SUM과 동일하게 사용

3. MIN / MAX OVER(): SUM과 동일하게 사용

4. COUNT: SUM과 동일하게 사용

** 윈도우 함수의 연산 범위: 집계 연산 시 행의 범위 설정 가능

---

윈도우 함수의 연산 범위

** 윈도우 함수의 연산 범위: 집계 연산 시 행의 범위 설정 가능

1. ROWS, RANGE 차이

1. ROWS: 값이 같더라도 각 행씩 연산
2. RANGE: 같은 값의 경우 하나의 RANGE로 묶어서 동시 연산(DEFAULT)

2. BETWEEN A AND B

1. 시작점 정의
   • CURRENT ROW: 현재행부터
   • UNBOUNDED PRECEDING: 처음부터 (DEFAULT)
   • N PRECEDING: N 이전부터
2. 마지막 시점 정의
   • CURRENT ROW: 현재행까지(DEFAULT)
   • UNBOUNDED FOLLOWING: 마지막까지
   • N FOLLOWING: N 이후까지

ex) RANGE_TEST 테이블에서의 범위 설정에 따른 누적합

** CASE1) RANGE 범위 전달(DEFAULT): 값이 같을 경우 같은 범위로 취급하여 동시 연산

SELECT R.*,
       SUM(SAL) OVER(ORDER BY SAL)
  FROM RANGE_TEST R;

 

** CASE2) ROWS 범위 설정 시: 각 행 별로 연산 수행

SELECT R.*,
       SUM(SAL) OVER(ORDER BY SAL
                     ROWS BETWEEN UNBOUNDED PRECEDING
                     AND CURRENT ROW) AS RESULT1
  FROM RANGE_TEST R;

 

** CASE3) BETWEEN A AND B 수정 시

SELECT R.*,
       SUM(SAL) OVER(ORDER BY SAL
                     ROWS BETWEEN UNBOUNDED PRECEDING
                     AND 1 FOLLOWNING) AS RESULT2
  FROM RANGE_TEST R;

→ UNBOUNDED PRECEDING AND 1 FOLLOWING: 각 행마다 누적합 계산 시 처음부터 다음행까지 연산

---

순위 관련 함수

1. RANK(순위)

1. RANK WITHIN GROUP
   • 특정값에 대한 순위 확인
   • 윈도우함수가 아닌 일반함수
   • 문법

     SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼);

     ex) EMP 에서 급여가 3000이면 전체 급여 순위가 얼마?
     

     SELECT RANK(3000) WITHIN GROUP(ORDER BY SAL DESC) AS RANK_VALUE
       FROM EMP;

     
2. RANK() OVER() 
   • 전체 중/특정 그룹 중 값의 순위 확인
   • ORDER BY 절 필수
   • 순위를 구할 대상을 ORDER BY 절에 명시(여러 개 나열 가능)
   • 그룹 내 순위 구할 시 PARTITION BY 절 사용
   • 문법
     

     SELECT RANK() OVER([PARTITION BY 컬럼]
                         ORDER BY 컬럼 ASC|DESC);

      ex) 각 직원의 급여의 전체 순위 (큰 순서대로)
     

     SELECT ENAME, DEPTNO, SAL, 
            RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1
       FROM EMP;

     
     → 동순위는 같은 동수
3. DENSE_RANK
   • 누적순위
   • 값이 같을 때 동일한 순위 ㅜㅂ여 후 다음 순위가 바로 이어지는 순위 부여 방식
   • EX) 1등이 5명이라도 그 다음 순위가 2
4. ROW_NUMBER
   • 연속된 행 번호
   • 동일한 순위를 인정하지 않고 단순히 순서대로 나열한대로의 순서 값 리턴

예제) RANK, DENSE, RANK, ROW_NUMBER 비교

SELECT ENAME, DEPTNO, SAL,
       RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE1,
       DENSE_RANK() OVER(ORDER BY SAL DESC) AS RANK_VALUE2,
       ROW_NUMBER() OVER(ORDER BY SAL DESC) AS RANK_VALUE3
  FROM EMP;

---

LAG, LEAD

• 행 순서대로 각각 이전 값(LAG), 이후 값 (LEAD) 가져오기
• ORDER BY 절 필수
• 문법

SELECT LAG(컬럼, -- 가져올 값을 갖는 컬럼
            [N]) -- 몇 번째 값을 가져올지 (DEFAULT:1)
            OVER ([PARTITION BY 컬럼] -- 행의 이동 그룹
                  ORDER BY 컬럼 [ASC|DESC]); -- 정렬컬럼

ex) EMP에서 바로 이전 입사자와 급여 비교

SELECT ENAME, HIREDATE, SAL
       LAG(SAL) OVER(ORDER BY HIREDATE) AS 바로직전상사급여
  FROM EMP;

 

참고) 이전/이후 값 가져올 때 이전 값이 같더라도 항상 행의 순서대로 이전, 이후 하나를 가져옴
따라서 사용자가 이전/이후 값을 가져올 원하는 행 배치를 ORDER BY를 통해 충분히 전달 한 후 
이전/이후 값을 가져오면 됨

SELECT EMP.*,
       LAG(SAL) OVER(ORDER BY DEPTNO, SAL) AS RESULT
  FROM EMP;

→ ORDER BY를 쓰지않고 PARTITION BY를 썼다면 4열의 RESULT는 NULL

---

FIRST_VALUE, LAST_VALUE

• 정렬 순서대로 정해진 범위에서의 처음 값, 마지막 값 출력
• 순서와 범위 정의에 따라 최솟값과 최댓값 리턴 가능
• PARTITION BY, ORDER BY 절 생략 가능
• 문법

SELECT FIRST_VALUE(대상) OVER([PARTITION BY 컬럼]
                              [ORDER BY 컬럼]
                              [RANGE|ROWS BETWEEN A AND B])

ex) FIRST_VALUE를 사용한 최소, 최대 출력

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

ex) LAST_VALUE 를 사용한 최소, 최대 출력

SELECT ENAME, DEPTNO, SAL,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) AS VALUE1,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL 
                            RANGE BETWEEN UNBOUNDED PRECEDING
                            AND UNBOUNDED FOLLOWING) AS MAX_VALUE,
       LAST_VALUE(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL DESC
                            RANGE BETWEEN UNBOUNDED PRECEDING
                            AND UNBOUNDED FOLLOWING) AS MIN_VALUE
  FROM EMP;
 WHERE DEPTNO IN (10,20);

---

NTILE

• 행을 특정 컬럼 순서에 따라 정해진 수의 그룹으로 나누기 위한 함수
• 그룹 번호가 리턴됨
• ORDER BY 필수
• PARTITION BY를 사용하여 특정 그룹을 또 원하는 수 만큼 그룹 분리 가능
• 총 행의 수가 명확히 나눠지지 않을 때 앞 그룹의 크기가 더 크게 분리됨
  EX) 14명 3개 그룹 분리 시 -> 5,5,4로 나뉨
• 문법

SELECT NTILE(N) OVER([PARTITION BY 컬럼]
                      ORDER BY 컬럼 ASC|DESC)

EX) NTILE을 사용한 그룹 분리

SELECT ENAME, SAL, DEPTNO
       NTILE(2) OVER (ORDER BY SAL) AS GROUP_NUMBER
  FROM EMP;

---

비율관련 함수

1. RATION_TO_REPORT

• 각 값의 비율 리턴(전체 비율 또는 특정 그룹 내 비율 가능)
• ORDER BY 사용 불가
• 문법

RATIO_TO_REPORT(대상) OVER([PARTITION BY 컬럼])

2. CUME_DIST: 각 행의 수에 대한 누적비율

• 특정 값이 데이터 집합에서 차지하는 위치를 백분위수로 계산하여 출력
• ORDER BY를 사용하여 누적비율을 구하는 순서 정할 수 있음
• ORDER BY 필수
• 값이 3개이면 1/3=0.33 부터 시작
• 문법

CUME_DIST() OVER([PARTITION BY 컬럼]
                 ORDER BY 컬럼 ASC|DESC)

3. PERCENT_RANK

• PERCENTILE(분위수) 출력
• 전체 COUNT 중 상대적 위치 출력 (0~1 범위 내)
• ORDER BY 필수
• 문법

PERCENT_RANK() OVER ([PARTITION BY 컬럼]
                     ORDER BY 컬럼 ASC|DESC)

예제) CUME_DIST와 PERCENT_RANK 비교

SELECT CUME_DIST() OVER(ORDER BY SAL) AS CUME_DIST,
       PERCENT_RANK() OVER(ORDER BY SAL) AS PERCENT_RANK, SAL
  FROM EMP
 WHERE DEPTNO = 10;

예제) 누적 비율 비교

 SELECT ENAME, DEPTNO, SAL
        ROUND(RATIO_TO_REPORT(SAL) OVER(PARTITION BY DEPTNO), 2) AS RATE1,
        ROUND(CUME_DIST() OVER(PARTITION BY DEPTNO
                               ORDER BY SAL), 2) AS RATE2,
        ROUND(CUME_DIST() OVER(PARTITION BY DEPTNO
                               ORDER BY SAL, ENAME), 2) AS RATE3
   FROM EMP;

→ RATE2: SAL에 대해서만 순서 정렬 후 RANGE로 누적비율을 구하므로 FORD와 SCOTT의 급여 누절비율이 같아짐 (SAL값이 같으니 하라의 범위로 처리),  RANGE가 디폴트이기 때문에 7번과 8번 같은 값은 하나로 판단 (RATE3과 같이 ENAME을 따로 두던가, ROWS BETWEEN으로 꾸면 됨)
→ RATE3: ORDER BY로 SAL, ENAME의 순서를 정릐하므로 두 값이 모두 같을 때만 RANGE 처리 하게 됨
  (FORD와 SCOTT이 SAL값이 같더라도 ENAME 값에 의해 두 행의 범위가 달라지므로 각각 연산됨)
→ CUME_DIST는 RATIO_TO_REPORT 처럼 비율을 계산할 값을 지정하지 않는다. 따라서 특정 값이 아닌, 각 행이 전체 혹은 PARTITITON 내에 차지하고 있는 비율을 의미함(MILLER는 10번 부서원 총 3명 중 1명의 비율을 의미)

EX) PERCENT_RANK 예제

SELECT ENAME, DEPTNO, SAL,
       PERCENT_RANK() OVER(ORDER BY SAL) AS TOTAL_PERCENTILE
  FROM EMP;