[SQL] 윈도우 함수(RANK, DENSE_RANK, ROW_NUMBER, 집계 함수, LAG, LEAD), 그룹함수(ROLLUP, CUBE, GROUPING SETS)

학습 내용

---

• ✅ 윈도우 함수(1): 함수 구조와 순위 함수
  • 윈도우 함수란? 함수 구조 살펴보기
  • 순위 함수: RANK, DENSE_RANK, ROW_NUMBER
• ✅ 윈도우 함수(2): 집계 함수
  • 집계 함수: SUM, AVG, MAX, MIN
  • 행 순서 집계 함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD
• ✅ 그룹 함수
  • 그룹함수란? 함수 구조 살펴보기
  • ROLLUP, CUBE, GROUPING SETS

 

 

 

 

 

---

🛠 윈도우 함수

📌 형태

SELECT 컬럼 명,
WINDOW_FUNCTION(ARGUMENTS) OVER ([ PARTITION BY 컬럼 ] [ ORDER BY 절 ][WINDOWING 절]) 
FROM 테이블 명;

 

 

윈도우 함수는 over 구문이 필수다.

윈도우 함수의 종류에 대해 알아보겠다.

 

 

 

---

🛠 순위 함수: RANK, DENSE_RANK, ROW_NUMBER

윈도우 함수 종류로 첫 번째는 순위함수다.

 

📌 형태

SELECT 컬럼 명,
RANK() OVER ([ PARTITION BY 컬럼 ] [ ORDER BY 절 ][ WINDOWING 절 ]) 
FROM 테이블 명;

 

순위 함수로는 RANK, DENSE_RANK, ROW_NUMBER 가 있다.

순위를 매길 때 사용하는 윈도우 함수인데, 중복값 처리 방식이 각각 다르다.

내가 이해한 대로 정리해보았다.

 

 

📑  예시 테이블: 학생들의 점수

이름점수

A	100
B	100
C	90
D	80
E	80
F	70

 

🎯 목표: 점수 기준 내림차순으로 순위 매기기

이름                              점수                                                  RANK                     DENSE_RANK           ROW_NUMBER

A	100	1	1	1
B	100	1	1	2
C	90	3	2	3
D	80	4	3	4
E	80	4	3	5
F	70	6	4	6

🔍 차이점

🛠 RANK()
👉 동점자가 있을 경우 같은 순위를 주고, 다음 순위는 건너뜀
(예: 1등이 2명 → 다음은 3등)

 

🛠 DENSE_RANK()
👉 동점자가 있어도 다음 순위를 건너뛰지 않음.

💡  이어서 순위를 부여한다는 점에서 밀집되어있다 (= dense )

(예: 1등이 2명 → 다음은 2등)

 

🛠 ROW_NUMBER()
👉 무조건 순서대로 번호 매김 (중복 상관 X)

💡  행을 기준으로 개수 카운팅한다고 생각.

 

 

 

 

 

 

 

---

🛠 집계함수: SUM, AVG, MAX, MIN

윈도우 함수 종류로 두 번째는 집계함수다.

서브 쿼리문 -> 집계함수를 이용하면 코드가 간결해진다.

💡 또한, 기존의 데이터를 유지한 채, 컬럼 추가 형태로 사용할 수 있다.

 

출처: 모두의 연구소

 

출처: 모두의 연구소

 

 

 

🔎 굳이 윈도우 함수를 왜 사용하는 걸까?⭐

윈도우 함수가 아닌 일반 집계 함수(SUM, AVG, MAX, MIN)만으로는
기존 데이터(개별 행)를 유지하면서 합계나 평균 등의 정보를 추가하기가 어렵다.

 

기존 데이터를 유지한 채로 집계 결과를 함께 보고 싶을 때는
보통 서브쿼리나 조인을 사용했지만,
이 방식은 복잡하고 가독성이 떨어질 수 있다.

 

윈도우 함수는 행을 줄이지 않고 각 행 위에 계산된 집계값을 덧붙일 수 있기 때문에,
간결하고 효율적이다.

 

💡 이는 곧 GROUP BY와 PARTITION BY의 차이점과도 연결된다.
GROUP BY는 집계 결과만 보여주기 때문에 행이 줄어들지만,
PARTITION BY는 그룹 내에서만 연산 범위를 나누고, 기존 데이터를 그대로 유지한다.🔥

 

 

📌 형태

SELECT 컬럼명,
집계함수(컬럼) OVER ([ PARTITION BY 절] [ORDER BY 절] [WINDOWING 절])
from 테이블 

 

집계함수는 어떤 컬럼에 대해서 집계를 하는 것이기에

인수를 반드시 넣어주어야 한다.🔥

 

 

 

 

 

 

---

🛠 행 순서 집계함수: FIRST_VALUE, LAST_VALUE, LAG, LEAD

윈도우 함수 종류로 세 번째는 행 순서 집계함수다.

 

📌 형태

SELECT 컬럼명,
행 순서 함수(기준 컬럼) OVER ([ PARTITION BY 절] [ORDER BY 절] [WINDOWING 절])
from 테이블 

 

함수	설명
🛠 FIRST_VALUE(column)	현재 윈도우 범위에서 가장 첫 번째 값 반환 (예: LIMIT 1)
🛠 LAST_VALUE(column)	현재 윈도우 범위에서 가장 마지막 값 반환 (예: 내림차순 후 LIMIT 1)
🛠 LAG(column, N)	현재 행 기준으로 N행 앞의 값 반환
🛠 LEAD(column, N)	현재 행 기준으로 N행 뒤의 값 반환

 

 

 

---

⭐ WINDOWING 절 🔥

WINDOWING 절은 행의 범위 기준을 의미한다.

WINDOWING 절의 형태는 ROWS BETWEEN A AND B이다.

 

📌 형태

SELECT 컬럼명,
행 순서 함수(기준 컬럼) OVER ([ PARTITION BY 절] [ORDER BY 절] [ROWS BETWEEN A AND B])
from 테이블 

 

용어	설명
🛠 CURRENT ROW	현재 행
🛠 UNBOUNDED PRECEDING	윈도우의 시작 위치가 첫 번째 행
🛠 UNBOUNDED FOLLOWING	윈도우의 마지막 위치가 마지막 행
🛠 N PRECEDING	현재 행 기준으로 N행 앞
🛠 N FOLLOWING	현재 행 기준으로 N행 뒤

 

 

 

📑 예시 데이터) 

id	name	sales
1	Alice	100
2	Bob	150
3	Charlie	200
4	Dana	180
5	Evan	130

 

 

1) LAG, LEAD 개념

SELECT name, sales,
LAG(sales, 1) OVER (ORDER BY id) AS prev_sales, LEAD(sales, 1) OVER (ORDER BY id) AS next_sales
FROM sales;

 

📑

name	sales	prev_sales	next_sales
Alice	100	NULL	150
Bob	150	100	200
Charlie	200	150	180
Dana	180	200	130
Evan	130	180	NULL

 

윈도우 함수에 LAG or LEAD 함수를 넣게 되면 인수로 컬럼명과 숫자가 들어가게 되는데

💡 몇 번째 이전 혹은 이후의 행을 가져올 건지에 대한 의미다.

 

 

 

 

2) CURRENT ROW, UNBOUNDED PRECEDING, UNBOUNDED FOLLOWING, N PRECEDING, N FOLLOWING 개념

SELECT
  name,
  sales,
  SUM(sales) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_before,
  SUM(sales) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_after,
  SUM(sales) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS sum_near
FROM sales;

 

📑

name	sales	sum_before	sum_after	sum_near
Alice	100	100	760	250
Bob	150	250	660	450
Charlie	200	450	510	530
Dana	180	630	310	510
Evan	130	760	130	310

 

UNBOUNDED PRECEDING AND CURRENT ROW: 첫 행부터 한 줄 한 줄씩 내려가면 된다.

100, 100+150=250, 250+200=450... 

위의 데이터에서는 나오지 않지만, PARTITION BY 절이 있다면 그룹화된 것까지가 하나의 왼도우라고 보면 된다.🔥

 

CURRENT ROW  AND UNBOUNDED FOLLOWING: 현재 행부터 마지막 행까지 한 줄씩 내려가면 된다.

100+150+200+180+130=760, 150+200+180+130=660, ...

 

ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: 현재 행 기준으로 그 앞 한 줄과 그 뒤 한 줄까지 포함해서 계산

(n preceding and n following 개념은 처음 접한다..)🔥

 

 

 

 

 

 

---

🛠 그룹함수 ROLLUP, CUBE, GROUPING SETS

🛠 ROLLUP

ROLLUP(A, B)는 다음 순서로 집계한다.

1. (A, B) — A와 B로 그룹
2. (A) — A만 그룹 (B는 NULL로 표시됨)
3. () — 전체 요약 (A도 NULL)

a	b
a	null
null	null

➡️ 즉, 왼쪽부터 점점 생략하면서 요약하는 방식이기 때문에,
앞에 있는 컬럼이 더 큰 그룹 기준이 된다.

따라서 ROLLUP 함수만 유일하게 인수의 순서에 영향을 받는다.

 

 

 

🛠 CUBE

모든 경우의 수에 대해서 나온다고 생각하였다.

CUBE (A, B)는 다음 순서로 집계한다.

(A,B), (A), (B), ()

a	b
a	null
b	null
null	null

 

 

 

🛠 GROUPING SETS 🔥

➡️ 명시한 각 컬럼 단독 집계

GROUPING SETS (A, B)는 다음 순서로 집계한다.

a	null
null	b

 

 

만약 전체에 대한 집계가 필요하다면

💡 ( ) 괄호로 따로 지정해주어야 한다.

GROUPING SETS ( (A), (B), () )

a	null
null	b
null	null

 

 

 

 

 

 

 

 

 

 

---

윈도우 함수와 그룹함수까지도 SQLD에 나온걸로 기억한다.

SQLD가 생각보다 깊게 SQL을 다루구나 생각들었다.

(어제 보수교육 들었음)

 

시험 볼 때, 주관식에서 DENSE_RANK가 답인 문제가 있었는데

RANK_DENSE랑 엄청 헷갈렸던 기억이..

(다행히 맞히긴 했지만 😅)

GROUPING SETS도 언더바가 들어가는지, 뒤에 s가 붙는지

SQLD 시험을 앞두고 있는 사람이라면 이런 디테일한 부분도 꼭 짚고 넘어가면 좋겠다.

 

다음 포스팅에서는 실무에서 자주 쓰이는 주제로

직접 실습한 내용을 바탕으로 정리해볼 예정이다!