[SQLD] 4-5: 윈도우 함수 (Window Functions) 와 윈도우 프레임(Window Frame)

 

이전 포스트:

2025.02.16 - [자격증/SQLD] - [SQLD] 4-4 : 그룹(소계) 함수 및 집계 연산자

[SQLD] 4-4 : 그룹(소계) 함수 및 집계 연산자

 

 

SQLD Chapter 4-5: 윈도우 함수 (Window Functions)

---

1. 윈도우 함수(Window Functions)란?

윈도우 함수(Window Functions)는 행(row) 단위의 연산을 수행하면서도, 그룹 함수와 달리 각 행의 개별 값을 유지하는 함수입니다. 즉, 특정 그룹 내에서 순위 계산, 누적 합계, 이동 평균 등을 계산하는 데 사용됩니다.

1.1 윈도우 함수의 특징

• GROUP BY 없이도 집계 연산 가능: 특정 그룹 내에서 연산을 수행하면서 개별 행을 유지
• OVER() 절과 함께 사용: 윈도우 함수는 OVER() 절을 사용하여 연산 범위를 지정
• PARTITION BY와 ORDER BY 활용: 데이터를 특정 그룹으로 나누거나 정렬한 상태에서 연산 수행 가능
  • PARTITION BY 절과 GROUP BY 절은 의미적으로 유사하다.
  • PARTITION BY 절이 없으면 전체 집합을 하나의 Partition으로 정의한 것과 동일하다

1.2 시험에 자주 나오는 내용

• RANK() - 동일 순위 발생, 다음 순위 건너뜀
• DENSE_RANK() - 동일 순위 발생, 다음 순위 건너뛰지 않음
• ROW_NUMBER() - 무조건 연속된 순위 부여 다음 순위 건너뛰지 않음
• LAG() - 이전 행 값 참조
•  LEAD() - 다음 행 값 참조

---

2. 윈도우 함수의 기본 구조

윈도우 함수는 다음과 같은 기본 구조를 가집니다.

SELECT 컬럼명,
       윈도우함수() OVER(PARTITION BY 그룹컬럼 ORDER BY 정렬컬럼) AS 별칭
FROM 테이블명;

• PARTITION BY: 그룹을 나누는 역할 (선택사항)
• ORDER BY: 연산을 수행할 순서를 정함

 

---

3. 주요 윈도우 함수 및 예제

윈도우 함수는 순위 함수(RANK, DENSE_RANK, ROW_NUMBER), 집계 함수(SUM, AVG, COUNT), 이전/다음 행 참조 함수(LAG, LEAD) 등으로 나뉩니다.

 

3.1 순위 함수 (Ranking Functions)

EMPLOYEES 테이블

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	SALARY
1	홍길동	10	6000
2	김철수	10	5000
3	이영희	20	7000
4	박민수	20	8000
5	최민호	20	7000

(1) RANK() - 동일 순위 발생, 다음 순위 건너뜀

SELECT EMPLOYEE_NAME, SALARY,
       RANK() OVER (ORDER BY SALARY DESC) AS RANKING
FROM EMPLOYEES;

결과

EMPLOYEE_NAME	SALARY	RANKING
박민수	8000	1
이영희	7000	2
최민호	7000	2
홍길동	6000	4
김철수	5000	5

 

✅ 설명: 동일한 값이 있으면 동일 순위를 부여하고, 다음 순위는 건너뜀

 

(2) DENSE_RANK() - 동일 순위 발생, 다음 순위 건너뛰지 않음

SELECT EMPLOYEE_NAME, SALARY,
       DENSE_RANK() OVER (ORDER BY SALARY DESC) AS RANKING
FROM EMPLOYEES;

결과

EMPLOYEE_NAME	SALARY	RANKING
박민수	8000	1
이영희	7000	2
최민호	7000	2
홍길동	6000	3
김철수	5000	4

✅ 설명: 동일한 값이 있으면 동일 순위를 부여하되, 다음 순위를 건너뛰지 않음

 

(3) ROW_NUMBER() - 무조건 연속된 순위 부여

SELECT EMPLOYEE_NAME, SALARY,
       ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROW_NUM
FROM EMPLOYEES;

결과

EMPLOYEE_NAME	SALARY	ROW_NUM
박민수	8000	1
이영희	7000	2
최민호	7000	3
홍길동	6000	4
김철수	5000	5

✅ 설명: 동일한 값이 있어도 무조건 연속된 순위를 부여함

 

(4) NTILE(n) - 데이터를 n개 그룹으로 나누기

SELECT EMPLOYEE_NAME, SALARY,
       NTILE(3) OVER (ORDER BY SALARY DESC) AS GROUP_NUM
FROM EMPLOYEES;

 

✅ 설명: NTILE(3)은 데이터를 3개 그룹으로 나누고 각 행에 그룹 번호를 부여

---

3.2 집계 함수 (Aggregate Functions with OVER())

윈도우 함수는 일반적인 집계 함수(SUM, AVG, COUNT)와 함께 사용할 수도 있습니다.

(4) SUM() - 부서별 누적 합계

 

EMPLOYEES 테이블

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	SALARY
1	홍길동	10	6000
2	김철수	10	5000
3	이영희	20	7000
4	박민수	20	8000
5	최민호	20	7000

 

SELECT EMPLOYEE_NAME, DEPARTMENT_ID, SALARY,
       SUM(SALARY) OVER (PARTITION BY DEPARTMENT_ID) AS TOTAL_SALARY
FROM EMPLOYEES;

결과

EMPLOYEE_NAME	DEPARTMENT_ID	SALARY	TOTAL_SALARY
홍길동	10	6000	11000
김철수	10	5000	11000
이영희	20	7000	22000
박민수	20	8000	22000
최민호	20	7000	22000

✅ 설명: 같은 부서(DEPARTMENT_ID) 내에서 급여 합계를 계산하여 출력

 

---

3.3 이전/다음 행 참조 함수 (LAG, LEAD)

EMPLOYEES 테이블

EMPLOYEE_ID	EMPLOYEE_NAME	DEPARTMENT_ID	SALARY
1	홍길동	10	6000
2	김철수	10	5000
3	이영희	20	7000
4	박민수	20	8000
5	최민호	20	7000

 

(5) LAG() - 이전 행 값 참조

SELECT EMPLOYEE_NAME, SALARY,
       LAG(SALARY, 1, 0) OVER (ORDER BY SALARY DESC) AS PREV_SALARY
FROM EMPLOYEES;

 

결과

EMPLOYEE_NAME	SALARY	PREV_SALARY
박민수	8000	0
이영희	7000	8000
최민호	7000	7000
홍길동	6000	7000
김철수	5000	6000

✅ 설명: 현재 행에서 이전 행의 급여(SALARY) 값을 가져옴

 

(6) LEAD() - 다음 행 값 참조

SELECT EMPLOYEE_NAME, SALARY,
       LEAD(SALARY, 1, 0) OVER (ORDER BY SALARY DESC) AS NEXT_SALARY
FROM EMPLOYEES;

결과

EMPLOYEE_NAME	SALARY	NEXT_SALARY
박민수	8000	7000
이영희	7000	7000
최민호	7000	6000
홍길동	6000	5000
김철수	5000	0

✅ 설명: 현재 행에서 다음 행의 급여(SALARY) 값을 가져옴

 

---

 

4. 기타 윈도우 함수

 

 EMPLOYEES 테이블

EMPLOYEE_NAME	SALARY
박민수	8000
이영희	7000
최민호	7000
홍길동	6000
김철수	5000

 

(7) RATIO_TO_REPORT() 

 

RATIO_TO_REPORT()는 각 행의 값이 전체 합에서 차지하는 비율을 계산하는 함수입니다.
즉, 특정 그룹 내에서 개별 값이 전체 값 대비 몇 퍼센트인지 구하는 역할을 합니다.

 

SELECT EMPLOYEE_NAME, SALARY,
       RATIO_TO_REPORT(SALARY) OVER() AS SALARY_RATIO
FROM EMPLOYEES;

 

EMPLOYEE_NAME	SALARY	SALARY_RATIO
박민수	8000	0.2857
이영희	7000	0.2500
최민호	7000	0.2500
홍길동	6000	0.2143
김철수	5000	0.1786

 

 

(8) CUME_DIST() - 누적 분포도 값 반환

SELECT EMPLOYEE_NAME, SALARY,
       CUME_DIST() OVER (ORDER BY SALARY DESC) AS CUMULATIVE_DISTRIBUTION
FROM EMPLOYEES;

 

✅ 설명:
CUME_DIST() 함수는 주어진 그룹 내에서 특정 값이 차지하는 누적 분포도를 계산합니다.

 

결과값은 0 초과 1 이하의 값으로 표현되며, 해당 행보다 같거나 작은 값의 비율을 나타냅니다.
즉, 해당 행의 값이 전체 데이터에서 어느 위치에 있는지를 상대적으로 보여줍니다.

---

5. 윈도우 프레임이란?

 

 

윈도우 프레임(Window Frame)은 윈도우 함수(Window Function)가 연산을 수행할 데이터의 범위를 지정하는 개념입니다.
즉, 현재 행을 기준으로 어느 범위까지 연산을 적용할지 설정하는 역할을 합니다.

✅ 윈도우 프레임은 ROWS 또는 RANGE와 함께 사용되며, OVER() 절 내에서 정의됩니다.
✅ 기본적으로 ORDER BY와 함께 사용하여 연산 대상 범위를 설정합니다.

---

5.1. 윈도우 프레임 문법 및 종류

윈도우함수() OVER (
    PARTITION BY 그룹컬럼 
    ORDER BY 정렬컬럼 
    ROWS|RANGE BETWEEN 시작점 AND 끝점
)

• PARTITION BY: 데이터를 그룹화 (선택사항)
• ORDER BY: 정렬 기준 설정
• ROWS | RANGE BETWEEN 시작점 AND 끝점: 연산할 데이터 범위 지정

윈도우 프레임 함수	설명
UNBOUNDED PRECEDING	첫 번째 행부터 포함
CURRENT ROW	현재 행을 기준으로 포함
UNBOUNDED FOLLOWING	마지막 행까지 포함
n PRECEDING	현재 행의 n개 앞까지 포함
n FOLLOWING	현재 행의 n개 뒤까지 포함
ROWS	물리적인 행 개수 기준으로 범위 설정
RANGE	값의 범위를 기준으로 범위 설정

 

---

6. 윈도우 프레임 함수 예제

 

 

 EMPLOYEES 테이블

EMPLOYEE_NAME	SALARY
박민수	8000
이영희	7000
최민호	7000
홍길동	6000
김철수	5000

 

1)  UNBOUNDED PRECEDING & CURRENT ROW

SELECT EMPLOYEE_NAME, SALARY,
       SUM(SALARY) OVER (ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUMULATIVE_SUM
FROM EMPLOYEES;

📌 실행 결과

EMPLOYEE_NAME	SALARY	CUMULATIVE_SUM
김철수	5000	5000
홍길동	6000	11000
최민호	7000	18000
이영희	7000	25000
박민수	8000	33000

✅ 설명:

• ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  → 첫 번째 행부터 현재 행까지의 모든 값을 합산
• 즉, 누적 합계를 구하는 방식

 

2) 이동 평균 (1 PRECEDING AND 1 FOLLOWING 사용)

현재 행을 중심으로 앞뒤 1개씩 포함하여 이동 평균을 계산하는 SQL을 작성해보겠습니다.

SELECT EMPLOYEE_NAME, SALARY,
       AVG(SALARY) OVER (ORDER BY SALARY ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS MOVING_AVG
FROM EMPLOYEES;

EMPLOYEE_NAME	SALARY	MOVING_AVG
김철수	5000	5500.00
홍길동	6000	6000.00
최민호	7000	7000.00
이영희	7000	7333.33
박민수	8000	7500.00

✅ 설명:

• ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  → 현재 행을 기준으로 앞뒤 1개씩 포함한 3개 행의 평균을 구함
• 즉, 이동 평균(Moving Average) 계산에 사용 가능

---

7. 마무리

이번 장에서는 윈도우 함수의 개념과 종류(RANK, DENSE_RANK, SUM, LAG, LEAD 등)에 대해 학습했습니다.

윈도우 함수는 순위 계산, 누적 합계, 이전/다음 행 조회 등의 기능을 제공하며, 데이터 분석과 보고서 작성에서 매우 유용하게 활용됩니다.

 

✅ 윈도우 프레임(Window Frame)은 윈도우 함수가 연산할 데이터 범위를 지정하는 기능
✅ ROWS는 정확한 행 개수를 기준으로, RANGE는 값의 범위를 기준으로 연산 수행
✅ 누적 합계, 이동 평균, 전체 합계 등의 계산을 가능하게 함

 

---

 

💡 도움이 되셨다면 댓글과 공감 부탁드립니다! 😊
📌 더 많은 알고리즘 풀이와 프로그래밍 자료는 블로그에서 확인하세요!
✉️ 문의나 피드백은 댓글이나 이메일로 남겨주세요.