해당 글에서는 데이터베이스 PostgreSQL을 기반으로 윈도우 함수(Window Function)에 대해 알아봅니다.
1) 윈도우 함수(Window Function)
💡 윈도우 함수(Window Function)
- 행과 행 간에 비교, 연산, 정의를 하기 위해 사용되는 함수를 의미합니다. - 각 행에 대한 결과를 계산하는 일반적인 집계함수와 달리 ‘행 그룹’에 대한 계산을 수행합니다.
- 윈도우 함수는 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 함수의 결과로 반환할 값의 표현식을 지정합니다.
