[SQL] 윈도우 함수 (Window Function)

---

01. 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 절
  • LANK의 경우 필수(정렬 컬럼 및 정렬 순서에 따라 순위 변화)
  • SUM, AVG, MIN, MAX, COUNT 등은 누적값 출력시 사용
• ROWS | 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;  -- 그룹함수 오류(윈도우 함수가 필요한 이유), 전체를 출력하는 컬럼과 그룹함수 결과를 함께 출력할 수 없음

---

02. 윈도우 함수의 형태

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

---

03. 윈도우 함수 이용

• SUM OVER()
  • 전체 총 합, 그룹별 총 합 출력 가능
  • 예제

SELECT empno, ename, sal, deptno, SUM(sal)
FROM emp;  --에러, 각 직원 정보와 급여 총 합(그룹함수 결과)를 동시에 출력 시도

SELECT empno, ename, sal, deptno,
(SELECT SUM(sal) FROM emp) AS total
FROM emp;  --해결1) 서브쿼리 사용(스칼라 서브쿼리)

SELECT empno, ename, sal, deptno,
SUM(sal) OVER() AS total
FROM emp;  --해결2) 윈도우 함수 사용

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

SELECT empno , ename, sal, deptno,
AVG(sal) OVER(PARTITION BY deptno) AS avg_sal
FROM emp;  --각 직원 정보와 해당 직원이 속한 부서의 평균 급여 출력

• MIN/MAX OVER(): SUM과 동일하게 사용
• COUNT OVER(): SUM과 동일하게 사용

---

04. 윈도우 함수의 연산 범위

• 집계 연산 시 행의 범위 설정 가능
• ROWS, RANGE 차이
  • ROWS: 값이 같더라도 각 행씩 연산
  • RANGE: 같은 값의 경우, 하나의 RANGE로 묶어서 동시 연산(DEFAULT)
• BETWEEN A AND B
  • A: 시작점 정의
    • CURRENT ROW: 현재 행부터
    • UNBOUNDED PRECEDING: (선행하는, 바로 앞의) 처음부터(DEAFULT)
    • N PRECEDING: N 이전부터
  • B: 마지막 시점 정의
    • CURRENT ROW: 현재행까지 (DEFAULT)
    • UNBOUNDED FOLLOWING: 마지막까지
    • N FOLLOWING: N 이후까지

SELECT R.*,
SUM(sal) OVER(ORDER BY sal)
FROM range_test R;  --RANGE, 범위 전달(DEFAULT): 값이 같을 경우 같은 범위로 취급하여 동시 연산

SELECT R.*,
SUM(sal) OVER(ORDER BY sal
ROWS BETWEEN UNBOUNDED PRECEDING  --값이 같더라도 각 행끼리 연산
AND CURRENT ROW) AS result1
FROM range_test R;  --ROWS 범위 설정 시: 각 행 별로 연산 수행

SELECT R.*
SUM(sal) OVER(ORDER BY sal ROWS 
BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS result2  --각 행마다 누적합 계산 시 처음부터 다음행까지 연산
FROM range_test R;  -- BETWEEN A AND B 수정 시

---

05. 순위 관련 함수

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

SELECT RANK(값) WITHIN GROUP(ORDER BY 컬럼 ASC | DESC)

SELECT RANK(3000) WITHIN GROUP(ORDER BY sal DESC) AS rank_value
FROM emp;  --emp 테이블에서 급여 3000이면 전체 급여 순위에서 몇 등인지

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

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

SELECT ename, deptno, sal, 
RANK() OVER(ORDER BY sal DESC) AS rank_value1
FROM emp;  --예시1) 각 직원의 급여의 전체 순위(큰 순서대로)

SELECT ename, deptno, sal
RANK() OVER(PARTITION BY deptno ORDER BY sal DESC) AS rank1
FROM emp;  --각 직원 이름, 부서번호, 급여, 부서별 급여 순위(큰 순서대로)

• DENSE_RANK
  • 누적 순위
  • 값이 같을 때: 동일한 순위 부여 + 다음 순위가 바로 이어짐
• ROW_NUMBER
  • 연속된 행 번호
  • 동일한 순위를 인정하지 않음 + 단순히 순서대로 나열한대로의 순서 값 리턴

SELECT ename, deptno, sal,
RANK() OVER(ORDER BY sal DESC) AS rank_value1,  --RANK: 같은 값(동일 순위) + 다음 값(다음 개수, IDX)
DENSE_RANK() OVER(ORDER BY sal DESC) AS rank_value2,  --DENSE_RANK: 같은 값(동일 순위) + 다음 순위(이어서 진행)
ROW_NUMBER() OVER(ORDER BY sal DESC) AS rank_value3  --ROW_NUMBER: 단순히 순서대로 나열된 값(IDX)
FROM emp;

---

06. LAG, LEAD

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

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

SELECT ename, hiredate, sal,
LAG(sal) OVER(ORDER BY hiredate) AS 바로직전입사자급여
FROM emp;  --바로 직전 입사자와의 급여 비교

---

07. FIRST_VALUE, LAST_VALUE

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

SELECT FIRST_VALUE(대상)  --첫 번째 값
OVER([PARTITION BY 컬럼]  --생략 가능, 그룹 연산 수행할 GROUP BY절
[ORDER BY 컬럼]  --생략 가능, 행 정렬
[RANGE | ROWS BETWEEN A AND B]);  --행의 범위 설정

---

08. NTILE

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

SELECT NTILE(n) 
OVER([PARTITION BY 컬럼]
ORDER BY 컬럼 ASC | DESC]);  --필수

---

09. 비율 관련 함수

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

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

• CUME_DIST: 각 행의 수에 대한 누적 비율
  • 특정 값이 전체 데이터 집합에서 차지하는 위치를 백분위 수로 계산하여 출력
  • ORDER BY 절을 이용하여 누적 비율을 구하는 순서를 정할 수 있음
  • ORDER BY절 필수
    • 예시: 값이 3개인 경우: 1/3 = 0.33부터 시작
  • 문법

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

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

SELECT PERCENT_RANK() OVER([PARTITION BY 컬럼] 
ORDER BY 컬럼 ASC | DESC);  --필수

-- CUME_DIST와 PERCENT_RANK() 비교 --
SELECT CUME_DIST() OVER(PARTITOIN BY sal) AS cume_dist_value,
PERCENT_RANK() OVER(ORDER BY sal) AS percent_rank_value  
FROM emp  
WHERE deptno = 10;  --부서 번호가 10일 때

-- 누적 비율 비교 --
SELECT ename, deptno, sal,
-- ROUND( ,n):n의 자리 까지 반올림 
ROUND(RATIO_TO_REPORT(sal) OVER(PARTITION BY deptno), 2) AS rate1,  --각 값의 비율
ROUND(CUME_DIST() OVER(PARTITION BY deptno), 2) AS rate2,  --각 행의 수에 대한 누적 비율
ROUND(CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal, ename), 2) AS rate3
FROM emp;

• rate2: sal에 대해서만 순서 정렬 후 RANGE로 누적 비율을 구하므로 sal값이 같으면 하나의 범위로 처리
• rate3: ORDER BY절로 sal, ename의 순서를 정의하므로 두 값이 모두 같을 때만 RANGE 처리 함
  • sal 값이 같더라도 ename 값에 의해 두 행의 범위가 달라지므로 각각 연산됨
• CUME_DIST: RATIO_TO_REPORT처럼 비율을 계산할 값을 미리 지정하지 않음
  • 특정 값이 아닌 각 행이 전체 혹은 PARTITION BY내에 차지하고 있는 비율을 의미함