윈도우 함수 개념에 대해서 이야기해봅시다.
윈도우 함수 개념에 대해 알기
윈도우 함수(Window Function)는 SQL(Structured Query Langauge)에서 행과 행 간을 비교하고 연산, 정의하기 위해서 사용하는 함수입니다. 윈도우 함수를 분석 함수나 순위 함수라고도 부릅니다.
윈도우 함수는 다른 함수들과 다르게 중첩 해서 사용할 수 없지만 서브 쿼리에서 사용할 수 있습니다.
윈도우 함수(Window Function)는 OVER 키워드와 함께 사용합니다.
윈도우 함수의 종류에는 순위 함수, 집계 함수, 행 순서 함수, 비율 함수가 있습니다.
[윈도우 함수 기본 문법]
SELECT
WINDOW_FUNCTION (ARGUMENTS) OVER([PARTITION BY 컬럼1] [ORDER BY 컬럼1] [WINDOWING 절])
FROM 테이블1
GROUP BY 컬럼1 ;
윈도우 함수 개념에 대한 설명
윈도우 함수(Window Function)는 데이터 베이스(DB, DataBase)의 테이블 데이터에서 행과 행 간의 관계를 보다 쉽게 정의하기 위해서 만든 함수입니다.
윈도우 함수는 SQL(Structured Query Language)에서 데이터의 행과 행 간을 비교하거나 연산하거나 정의하기 위해서 사용합니다. 윈도우 함수에서는 OVER 문구가 필수적으로 들어 갑니다.
윈도우 함수의 대표적인 함수로는 순위 함수, 집계 함수, 행 순서 함수, 비율 함수가 있습니다.
[윈도우 함수의 종류]
| 구분 | 내용 |
| 순위 함수 | RANK, DENSE_RANK, ROW_NUMBER |
| 집계 함수 | SUM, MAX, MIN, AVG, COUNT |
| 행 순서 함수 | FIRST_VALUE, LAST_VALUE, LAG, LEAD |
| 비율 함수 | CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT |
1. 순위 함수
순위 함수에는 RANK, DENSE_RANK, ROW_NUMBER가 있습니다.
RANK 함수의 경우 1, 2, 2, 4, 5, 5, 7,.. 과 같은 형태가 되며 DENSE_RANK 함수는 1, 2, 2, 3, 4, 4, 5, … 와 같은 형태로 출력 되며 ROW_NUMBER 함수는 1, 2, 3, 4, 5, 6, 7, … 형태로 조회 됩니다.
RANK는 동일한 순위가 존재할 대 존재하는 수만큼 건너 뛰어서 다음 순위를 매기는데 DENSE_RANK 함수는 동일한 순위를 하나의 건수로 취급하여 바로 이어서 다음 순위를 매기게 됩니다. ROW_NUMBER 함수는 무조건 유니크(Unique) 한 순위를 매깁니다.
RANK 함수는 순위를 구하는 함수입니다. 특정 범위 안에서의 순위를 구할 수도 있고 전체 데이터에 대한 순위를 구할 수 있습니다. 동일한 값에 대해서는 동일한 순위를 부여 하는 함수입니다.
RANK는 순위를 매기면서 같은 순위가 존재하게 되면 존재하는 수 만큼 다음 순위를 건너뛰게 됩니다.
[RANK 함수 사용 예시]
SELECT 컬럼1, COUNT(*)
RANK () OVER (ORDER BY COUNT(*) DESC) AS RANK
FROM 테이블1
GROUP BY 컬럼1 ;
DENSE_RANK 함수는 RANK 함수와 비슷하지만 동일한 순위를 하나의 건수로 취급하는 함수입니다. RANK는 1순위, 2순위, 3순위로 표기되지만 DENSE_RANK 함수는 1, 1, 3 순위와 같이 부여됩니다. 1위가 2개이기 때문에 2위가 없어지고 3위로 이어지게 됩니다.
DENSE_RANK는 순위를 매기면서 같은 순위가 존재하더라도 다음 순위를 건너 뛰지 않고 이어서 매기는 형태의 함수입니다. DENSE는 사전적으로 “밀집한” 이라는 의미를 가지고 있습니다. 따라서 DENSE_RANK는 순위가 밀집되어 있다라는 의미로 이해할 수 있습니다.
[DENSE_RANK 함수 사용 예시]
SELECT 컬럼1, COUNT(*)
DENSE_RANK () OVER (ORDER BY COUNT(*) DESC) AS DENSE_RANK
FROM 테이블1
GROUP BY 컬럼1 ;
ROW_NUMBER 함수는 동일한 값이더라도 고유한 순위를 부여하는 함수입니다. 동일한 순위를 배제하며 유니크(Unique) 한 순위를 정합니다. 따라서 같은 값에 대해서는 어떤 결과가 먼저 나오게 할지 순서를 정하고자 한다면 ORDER BY를 같이 기재합니다.
ROW_NUMBER는 순위를 매기면서 동일한 값이더라도 각각 다른 순위를 부여하는 것입니다.
[ROW_NUMBER 함수 사용 예시]
SELECT 컬럼1, COUNT(*)
ROW_NUMBER () OVER (ORDER BY COUNT(*) DESC) AS ROW_NUMBER
FROM 테이블1
GROUP BY 컬럼1 ;
2. 집계 함수
집계 함수에는 SUM, MAX, MIN, AVG, COUNT가 있습니다.
SUM 함수는 데이터의 합계를 구하는 함수입니다. SUM 은 인자 값으로 숫자만 올 수 있습니다. SUM 함수를 사용하여 파티션별 윈도우의 합을 구할 수 있습니다. OVER 절에 ORDER BY 절을 추가하여 파티션 내에서 데이터를 정렬하고 이전 데이터까지의 누적 값을 출력하는 형태로 사용될 수 있습니다.
MAX 함수는 데이터의 최대 값을 구하는 함수입니다. MAX 함수를 사용하여 파티션별 윈도우의 최대값을 구할 수 있게 됩니다.
MIN 함수는 데이터의 최소 값을 구하는 함수입니다. 파티션별 윈도우의 최소값을 구할 수 있습니다.
AVG 함수는 데이터의 평균 값을 구하는 함수입니다. 파티션별 통계 값을 구할 수 있습니다.
COUNT 함수는 데이터의 건수를 구하는 함수입니다. COUNT 함수는 정말 많이 사용하는 함수입니다.
3. 행 순서 함수
행 순서 함수에는 FIRST_VALUE, LAST_VALUE, LAG, LEAD가 있습니다.
FIRST_VALUE 함수는 피티션별 가장 선두에 위치한 데이터를 구하는 함수입니다. 파티션별 윈도우에서 가장 먼저 나온 값을 구할 수 있습니다. FIRST_VALUE 함수는 MIN 함수와 동일한 결과를 출력합니다.
LAST_VALUE 함수는 파티션별 가장 끝에 위치한 데이터를 구하는 함수입니다. 파티션별 윈도우에서 가장 먼저 나중에 나온 값을 구할 수 있습니다. LAST_VALUE 함수는 MAX 함수를 사용해도 동일한 결과를 얻을 수 있습니다.
LAG 함수는 파티션 별로 특정 수만큼 앞선 데이터를 구하는 함수입니다. 파티션별로 윈도우에서 이전 몇 번째 행의 값을 가져올 수 있습니다.
LEAD 함수는 파티션별 특정 수만큼 뒤에 있는 데이터를 구할 수 있는 함수입니다. 파티션별 윈도우에서 이후 몇 번째 행의 값을 가져올 수 있습니다.
4. 비율 함수
비율 함수에는 CUME_DIST, PERCENT_RANK, NTILE, RATIO_TO_REPORT가 있습니다.
CUME_DIST 함수는 해당 파티션에서 누적 백분율을 구하는 함수입니다. 파티션별 윈도우의 전체 건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구하게 됩니다.
PERCENT_RANK 함수는 해당 파티션의 맨 위 끝 행을 0으로 하고 맨 아래 끝 행을 1로 놓고 현재 행이 위치하는 백분위 순위 값을 구하는 함수입니다. PERCENT_RANK 함수는 파티션별 함수를 사용하여 파티션별 윈도우에서 가장 먼저 나오는 것을 0으로 하고 가장 마지막으로 오는 것을 1로 하여 행의 순서별 백분율을 구하는 것입니다. 즉 결과적으로 값이라기 보다 행의 순서별 백분율이 됩니다.
NTILE 함수는 주어진 수만큼 행들을 n 등분한 다음 현재 행에 해당하는 등급을 구하는 함수입니다. NTILE 함수는 파티션별 전체 건수를 ARGUMENT 값으로 n 등분한 결과를 구할 수 있게 됩니다.
RATIO_TO_REPORT 함수는 파티션 별 합계에서 차지하는 비율을 구하는 함수입니다. RATIO_TO_REPORT 함수는 파티션 안의 전체 SUM 값에 대한 행 별 컬럼 값의 백분율을 소수점으로 구할 수 있습니다.