[DB/postgres] 윈도우 함수(Window Function) 이해 및 활용방법

해당 글에서는 데이터베이스 PostgreSQL을 기반으로 윈도우 함수(Window Function)에 대해 알아봅니다.

 

 

1) 윈도우 함수(Window Function)

---

 💡 윈도우 함수(Window Function)

- 행과 행 간에 비교, 연산, 정의를 하기 위해 사용되는 함수를 의미합니다.
- 각 행에 대한 결과를 계산하는 일반적인 집계함수와 달리 ‘행 그룹’에 대한 계산을 수행합니다.

- 윈도우 함수는 PARTITION BY 절을 사용하여 윈도우를 정의하고 ORDER BY 절을 사용하여 윈도우 내에서 행의 순서를 지정합니다.
- 윈도우 함수를 이용하여 행 그룹 내에 순위를 계산하거나 이동 평균 또는 누적 합을 계산할 수 있습니다. 주로 데이터 분석 및 보고서 작성에 유용합니다.

https://medium.com/geekculture/window-functions-in-sql-b630b0c11a57

 

 

2) 윈도우 함수의 구조

---

구조	설명
FUNCTION_NAME	사용할 윈도우 함수의 이름입니다.
arg1, arg2, ..., argN	함수에 전달할 인수(값 또는 열)입니다.
PARTITION BY	파티션을 정의하기 위해 사용되는 열입니다. 이 열을 기준으로 결과 집합이 분할됩니다.
ORDER BY	정렬에 사용되는 열입니다. 윈도우 함수가 적용되는 행의 순서를 결정합니다.

FUNCTION_NAME(arg1, arg2, ..., argN) 
OVER (PARTITION BY partition_column ORDER BY sort_column)

 

 

 

1. ROW_NUMBER() 함수

---

💡 ROW_NUMBER() 함수

- 정렬된 컬럼 또는 표현식을 기준으로 각 ‘로우에 번호’를 할당하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 일련번호를 할당합니다.

 

💡 기본구조

- 파라미터 없음.

-- 기본구조
ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

 

💡 예시 테이블 구조

 

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡 예시 SQL문

- ROW_NUMBER() 함수를 통해 Alice의 경우 일련번호가 1로 나타나며, David는 일련번호가 5로 나타납니다.

-- 예시 SQL문
SELECT 이름, 점수, ROW_NUMBER() OVER (ORDER BY 점수) AS 일련번호
FROM 테이블명;

 

이름	점수	일련번호
Alice	80	1
Carol	85	2
Emily	85	3
Bob	90	4
David	95	5

 

 

 

 

2. CUME_DIST() 함수

---

 

💡 CUME_DIST() 함수

- 특정 값보다 작거나 같은 값의 ‘비율’을 계산하는 윈도우 함수입니다.

- 이 함수는 정렬된 컬럼 또는 표현식에서 사용됩니다.

 

💡 기본구조

- 파라미터 없음.

-- 기본구조
CUME_DIST() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

 

 💡 예시 SQL문

- 해당 테이블에서 점수를 기준으로 오름차순으로 정렬하고 CUME_DIST 함수를 사용하여 각 사람의 상대적인 순위를 계산합니다.

-- 예시 SQL문
SELECT 이름, 점수, CUME_DIST() OVER (ORDER BY 점수) AS 상대적인_순위
FROM 테이블명;

 

이름	점수	상대적인_위치
Alice	80	0.2
Carol	85	0.6
Emily	85	0.6
Bob	90	0.8
David	95	1.0

 

 

 

3. RANK() 함수

---

💡 RANK() 함수

- 정렬된 컬럼 또는 표현식을 기준으로 각 ‘로우(row)의 순위’를 계산하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 순위를 할당합니다.

 

💡 기본구조

- 파라미터 없음.

RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡 예시 SQL문

- RANK 함수를 통해 David의 경우 순위가 1로 나타나며, Alice는 순위가 5로 나타납니다.

SELECT 이름, 점수, RANK() OVER (ORDER BY 점수 DESC) AS 순위
FROM 테이블명;

 

이름	점수	순위
David	95	1
Bob	90	2
Carol	85	3
Emily	85	3
Alice	80	5

 

 

[ 더 알아보기 ]

💡 DENSE_RANK와 RANK의 차이점

- DENSE_RANK: 결과 집합에서 각 고유한 값을 가진 항목에 고유한 랭크를 할당하며, 랭킹에는 빈 공간이 없습니다. 동점이 있는 경우, 동일한 랭크가 동점인 행에 할당되고, 다음 랭크는 건너뜁니다.

- RANK: 각 고유한 값을 가진 항목에 고유한 랭크를 할당하지만, 동점인 경우 랭킹에 빈 공간을 남깁니다. 예를 들어, 동일한 랭크를 가진 두 행이 있는 경우, 다음 랭크는 건너뛰고, 다음 행이 더 높은 랭크를 받습니다.

 

 

 

4. DENSE_RANK() 함수

---

💡 DENSE_RANK() 함수

- 정렬된 컬럼 또는 표현식에서 중복을 제외한 고유한 값에 대해 ‘상대적인 순위’를 계산하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 순위를 할당합니다.

 

 

💡 기본구조

- 파라미터 없음.

-- 기본구조 인자
DENSE_RANK() OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡 예시 SQL문

- David의 경우 상대적인_순위가 1로 나타납니다. 이는 David의 점수가 다른 사람들의 점수 중에서 가장 높다는 의미입니다.

SELECT 이름, 점수, DENSE_RANK() OVER (ORDER BY 점수 DESC) AS 상대적인_순위
FROM 테이블명;

 

이름	점수	상대적인_위치
David	95	1
Bob	90	2
Carol	85	3
Emily	85	3
Alice	80	4

 

 

 

5. PERCENT_RANK() 함수

---

💡 PERCENT_RANK() 함수

- 정렬된 컬럼 또는 표현식을 기준으로 각 로우의 ‘백분위 순위’를 계산하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 백분위 순위를 할당합니다.

 

 

💡 기본구조

- 파라미터 없음.

PERCENT_RANK() OVER (
    [PARTITION BY partition_expression]
    ORDER BY sort_expression [ASC | DESC]
)

 

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡 예시 SQL문

- Alice의 경우 백분위_순위가 0.0으로 나타납니다. 이는 Alice의 점수가 최소값이기 때문입니다.
- Bob과 David의 경우 백분위_순위가 1.0으로 나타납니다. 이는 Bob과 David의 점수가 최대값이기 때문입니다.
- Carol과 Emily의 경우 백분위_순위가 0.5로 나타납니다. 이는 Carol과 Emily의 점수가 중간값이기 때문입니다.

SELECT 이름, 점수, PERCENT_RANK() OVER (ORDER BY 점수) AS 백분위_순위
FROM 테이블명;

 

이름	점수	백분위_순위
Alice	80	0.0
Carol	85	0.5
Emily	85	0.5
Bob	90	1.0
David	95	1.0

 

 

 

6. FIRST_VALUE() 함수

---

💡 FIRST_VALUE() 함수

- 정렬된 컬럼 또는 표현식에서 '첫 번째 값'에 대해 반환하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 첫 번째 값을 할당합니다.

 

💡 기본구조 인자

- expression: FIRST_VALUE 함수의 결과로 반환할 값의 표현식을 지정합니다.

-- 기본구조 
FIRST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

💡 예시 테이블 구조

 

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

 

💡 예시 SQL문

- 점수를 기준으로 오름차순으로 정렬하고, FIRST_VALUE 함수를 사용하여 각 그룹의 첫 번째 값을 계산할 수 있습니다.

SELECT 이름, 점수, FIRST_VALUE(이름) OVER (ORDER BY 점수) AS 첫_값
FROM 테이블명;

 

이름	점수	첫_값
Alice	80	Alice
Carol	85	Alice
Emily	85	Alice
Bob	90	Alice
David	95	Alice

 

 

 

7. LAST_VALUE() 함수

---

💡 LAST_VALUE() 함수

- 정렬된 컬럼 또는 표현식에서 ‘마지막 값’에 대해 반환하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 마지막 값을 할당합니다.

 

 

 

💡 기본구조 인자

- expression: LAST_VALUE 함수의 결과로 반환할 값의 표현식을 지정합니다.

LAST_VALUE ( expression )  
OVER ( 
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

 

 

💡 예시 SQL문

- LAST_VALUE 함수를 기반으로 마지막_값은 각 그룹의 마지막 값을 나타냅니다. 모든 로우에 대해 마지막 값인 "David"가 반환되었습니다.

SELECT 이름, 점수, LAST_VALUE(이름) OVER (ORDER BY 점수) AS 마지막_값
FROM 테이블명;

 

이름	점수	마지막_값
Alice	80	David
Carol	85	David
Emily	85	David
Bob	90	David
David	95	David

 

 

 

8. LAG() 함수

---

💡 LAG() 함수

- '현재 로우'의 '이전 로우'에 대한 값을 반환하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 이전 로우의 값을 할당합니다.

 

💡 기본구조 인자

- expression: LAG 함수의 결과로 반환할 값의 표현식을 지정합니다.

- offset (선택사항): 이전 로우로부터 얼마나 떨어진 로우의 값을 반환할지를 지정합니다. 기본값은 1입니다.

- default_value (선택사항): 이전 로우가 없을 경우 반환할 기본값을 지정합니다. 기본값은 NULL입니다.

LAG(expression [,offset [,default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

💡 예시 테이블 구조

 

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡 예시 SQL문

- LAG 함수를 기반으로 Carol의 경우 이전_점수가 80으로 나타납니다. 이는 Carol의 이전 로우인 Alice의 점수가 80이기 때문입니다.

SELECT 이름, 점수, LAG(점수) OVER (ORDER BY 점수) AS 이전_점수
FROM 테이블명;

 

이름	점수	이전_점수
Alice	80	NULL
Carol	85	80
Emily	85	85
Bob	90	85
David	95	90

 

 

 

9. LEAD() 함수

---

 💡 LEAD() 함수

- '현재 로우'의 '이전 로우'에 대한 값을 반환하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 다음 로우의 값을 할당합니다.

 

 

💡 기본구조 인자

- expression: LEAD 함수의 결과로 반환할 값의 표현식을 지정합니다.

- offset (선택사항): 다음 로우로부터 얼마나 떨어진 로우의 값을 반환할지를 지정합니다. 기본값은 1입니다.

- default_value (선택사항): 다음 로우가 없을 경우 반환할 기본값을 지정합니다. 기본값은 NULL입니다.

LEAD(expression [,offset [,default_value]]) 
OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
)

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

SELECT 이름, 점수, LEAD(점수) OVER (ORDER BY 점수) AS 다음_점수
FROM 테이블명;

 

💡 예시 SQL문

- LEAD 함수를 기반으로 Alice의 경우 다음_점수가 85로 나타납니다. 이는 Alice의 다음 로우인 Carol의 점수가 85이기 때문입니다. 마지막 로우인 David의 경우 다음 로우가 없으므로 NULL이 반환됩니다.

이름	점수	다음_점수
Alice	80	85
Carol	85	85
Emily	85	90
Bob	90	95
David	95	NULL

 

 

 

10. NTILE() 함수

---

💡 NTILE() 함수

- 정렬된 컬럼 또는 표현식을 지정된 수의 그룹으로 분할하여 각 그룹에 번호를 할당하는 함수입니다.

- 이 함수는 주어진 정렬 순서에 따라 각 로우에 번호를 할당합니다.

 

 

💡 기본구조 인자

- buckets: 그룹의 수를 지정합니다.

NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    [ORDER BY sort_expression [ASC | DESC], ...]
)

 

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

💡예시 SQL문

- NTILE 함수를 기반으로 Alice와 Carol은 그룹_번호가 1로 나타나며, David는 그룹_번호가 3으로 나타납니다.

SELECT 이름, 점수, NTILE(3) OVER (ORDER BY 점수) AS 그룹_번호
FROM 테이블명;

이름	점수	그룹_번호
Alice	80	1
Carol	85	1
Emily	85	2
Bob	90	2
David	95	3

 

 

11. NTH_VALUE() 함수

---

 💡 NTH_VALUE() 함수

- 정렬된 컬럼 또는 표현식에서 지정된 ‘위치의 값’을 반환하는 함수입니다.
- 이 함수는 주어진 정렬 순서에 따라 각 로우에 해당 위치의 값을 할당합니다.

 

💡 기본구조 인자

- expression: NTH_VALUE 함수의 결과로 반환할 값의 표현식을 지정합니다.

- offset: 반환할 값의 위치를 지정합니다. 1부터 시작하며, 1은 최소값을 나타냅니다.

NTH_VALUE(expression, offset) 
OVER (
    [PARTITION BY partition_expression]
    [ ORDER BY sort_expression [ASC | DESC]
    frame_clause ]
)

 

 

💡 예시 테이블 구조

이름	점수
Alice	80
Bob	90
Carol	85
David	95
Emily	85

 

 

💡예시 SQL문

-  NTH_VALUE 함수를 기반으로 Alice의 경우 N번째_값이 "Emily"로 나타납니다. 이는 Alice의 3번째로 작은 값이 "Emily"이기 때문입니다.

SELECT 이름, 점수, NTH_VALUE(이름, 3) OVER (ORDER BY 점수) AS N번째_값
FROM 테이블명;

이름	점수	N번째_값
Alice	80	Emily
Carol	85	Emily
Emily	85	Emily
Bob	90	Emily
David	95	Emily

 

 

 

 

 

오늘도 감사합니다. 😀