[SQL] 윈도우 함수와 순위 및 그룹 함수

Posted Mar 1, 2024

By Ennik 14 min read

윈도우 함수(Window Functions)

SQL의 고급 기능 중 하나로, 데이터를 분석하고 집계하는 데 사용된다.
이 함수들은 특정 “윈도우” 또는 “파티션” 내에서 작동하여, 그룹화된 데이터에 대해 계산을 수행하지만, 행을 그룹으로 축소하지 않고 각 행에 대한 결과를 반환한다.
이러한 특성으로 데이터의 상세한 분석과 복잡한 질의를 가능하게 힌다.

주요 개념

파티셔닝(Partitioning): 데이터를 특정 기준에 따라 분할하는 것. 윈도우 함수는 각 파티션 내에서 독립적으로 작동하여, 파티션마다 별도의 집계나 계산을 수행할 수 있다.
순서화(Ordering): 윈도우 내의 데이터를 특정 순서로 정렬하는 것. 순위나 누적 집계를 계산할 때 중요하다.
프레임 명시(Frame Specification): 계산에 포함할 윈도우 내의 행의 범위를 지정하는 것. 예를 들어, 현재 행으로부터 앞뒤 3행을 대상으로 계산을 수행할 수 있다.

유형

집계 함수(Aggregate Functions): SUM(), AVG(), COUNT() 등 기존의 집계 함수를 윈도우 버전으로 사용하여, 전체 집합이 아닌 특정 윈도우 내에서 집계 결과를 계산한다.
순위 함수(Ranking Functions): 데이터 내 각 행의 순위를 결정힌다. 예를 들어, ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE() 등이 있다.
분석 함수(Analytic Functions): 특정 윈도우 내에서의 데이터 분석을 위해 사용된다. 예를 들어, LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE() 등이 있다. 특정 행에 대한 이전 행이나 다음 행의 값을 가져오거나, 윈도우 내의 첫 번째 또는 마지막 값을 조회할 수 있다.

순위 함수

ROW_NUMBER(), RANK(), DENSE_RANK()

데이터 내에서 각 행의 순위를 부여하는 데 사용되는 윈도우 함수이다.

ROW_NUMBER()

결과 세트의 각 행에 대해 고유한 순서 번호를 할당한다. 이 순서는 OVER 절에서 정의된 정렬 순서에 따라 결정된다.

기본 구문
1 ROW_NUMBER() OVER (ORDER BY column_name)
- ORDER BY column_name: 순서 번호를 할당하기 위한 정렬 기준
특징
- 각 행에 대해 고유한 숫자가 할당된다.
- 중복 값이 있어도 각각 다른 순서 번호를 매긴다.
예시 : 1, 2, 3, 4, 5

RANK()

중복 값을 고려하여 순위를 할당한다. 동일한 값에 대해 동일한 순위를 부여하고, 다음 순위는 중복된 항목 수만큼 건너뛴다.

기본 구문
1 RANK() OVER (ORDER BY column_name)
특징
- 동일한 값은 동일한 순위를 매긴다.
- 예를 들어, 두 행이 1위를 공유할 경우 다음 순위는 3위가 된다.
예시 : 1, 2, 2, 4, 5

DENSE_RANK()

중복 값을 고려하여 순위를 할당하지만, 중복된 항목 사이에 ‘간격’을 두지 않는다.

기본 구문

  
DENSE_RANK() OVER (ORDER BY column_name)

특징
- 동일한 값은 동일한 순위를 매긴다.
- 중복된 항목 다음의 순위에 간격이 없다. 예를 들어, 두 행이 1위를 공유할 경우 다음 순위는 2위가 된다.
예시 : 1, 2, 2, 3, 4

NTILE

기본 개념
- SQL의 창 함수(window function) 중 하나로, 결과 집합을 지정된 수의 동등한 부분으로 분할하는 데 사용된다.
- 각 부분은 “타일(tile)”이라고 하며, NTILE 함수는 각 행에 대해 해당 타일 번호를 반환한다.
- 데이터를 순위나 백분위 등으로 나눌 때 유용하게 사용할 수 있다.
기본 구문
1 2 3 4 NTILE(number_of_tiles) OVER ( PARTITION BY column_list ORDER BY column )
- number_of_tiles는 데이터를 나누고자 하는 타일의 수를 지정한다.
- PARTITION BY 절은 선택 사항으로, 데이터를 더 작은 그룹으로 분할하는 데 사용되며 각 그룹은 독립적으로 NTILE 계산을 수행한다.
- ORDER BY 절은 데이터를 어떤 순서로 타일에 할당할지 결정하며 필수 사항이다.
작동 방식
1. 전체 결과 집합을 ORDER BY 절에 지정된 순서대로 정렬한다.
2. 정렬된 결과를 number_of_tiles에 지정된 수의 동일한 크기(또는 가능한 한 가까운 크기)의 그룹으로 나눈다.
3. 각 행에 대해 해당하는 타일 번호를 반환하고, 타일 번호는 1부터 number_of_tiles까지의 값이다.

예시(49회 단답형 5번)

[TAB1]

COL1	COL2
1	A
2	B
3	B
4	C
5	C
6	C
7	D

[SQL]

  
SELECT NT, COUNT(*)
FROM (SELECT NTILE(3) OVER (ORDER BY COL1) AS NT
    FROM TAB1)
WHERE 1=1
GROUP BY NT
ORDER BY 1;

[RESULT]

NT	COUNT(*)
1	3
2	2
3	2

그룹 분석 쿼리

ROLLUP

기본 개념
- 집계 함수와 함께 사용되어, 그룹화된 데이터의 서브토탈과 총계를 계산하는 데 사용되는 확장된 GROUP BY 기능
- 여러 수준의 요약 데이터(예: 합계, 평균 등)를 한 번의 쿼리로 쉽게 생성할 수 있어 보고서 생성 및 데이터 분석에서 유용하다.

기본 구문(MySQL)

  
SELECT column1, column2, SUM(column3)
FROM table_name
GROUP BY column1, column2 WITH ROLLUP;

기본 구문(Oracle)
1 2 3 SELECT column1, column2, AGG_FUNC(column3) FROM table GROUP BY ROLLUP (column1, column2);
- AGG_FUNC는 집계 함수(예: SUM, AVG, COUNT 등)를 나타낸다.
- column1, column2는 그룹화하고자 하는 컬럼을 나타낸다.
- ROLLUP은 지정된 컬럼들에 대해 가능한 모든 수준의 서브토탈을 생성한다.
- 서브토탈은 지정된 컬럼의 순서에 따라 달라진다.
작동 방식
1. ROLLUP은 지정된 컬럼 리스트에 대해 계층적으로 서브토탈을 계산한다.
2. 가장 낮은 수준(가장 오른쪽 컬럼)부터 시작하여, 각 단계에서 한 컬럼씩 더 포함하여 서브토탈을 계산한다.
3. 마지막으로 전체 총계를 계산한다.

예시

[SQL]

  
SELECT 구매고객, 구매월, COUNT(*) "총 구매건", SUM(구매금액) "총 구매액"
FROM 구매이력
GROUP BY ROLLUP (구매고객, 구매월)

[RESULT]

구매고객	구매월	총 구매건	총 구매액
AAA	202301	1	1000
AAA	202302	2	3000
AAA	202303	1	1000
AAA		4	5000
BBB	202301	3	2000
BBB	202302	5	3000
BBB	202303	1	2000
BBB		9	7000
CCC	202301	1	2000
CCC	202302	1	5000
CCC	202303	1	1000
CCC		3	8000
		16	20000

CUBE

기본 개념
- CUBE는 ROLLUP과 달리 주어진 컬럼들에 대해 모든 가능한 조합의 서브토탈과 총계를 계산
- 여러 차원에서의 집계를 통해, 데이터를 다양한 각도에서 분석하고 보고할 수 있다.
기본 구문(MySQL)
- MySQL은 기본적으로 CUBE 연산을 직접 지원하지 않는다.
- GROUP BY의 WITH ROLLUP 옵션을 사용하여 부분적으로 비슷한 기능을 구현할 수 있다.
기본 구문(Oracle)
1 2 3 SELECT column1, column2, AGG_FUNC(column3) FROM table GROUP BY CUBE (column1, column2);
- AGG_FUNC는 집계 함수(예: SUM, AVG, COUNT 등)를 나타낸다.
- column1, column2는 그룹화하고자 하는 컬럼을 나타낸다.
- CUBE는 지정된 컬럼들에 대해 가능한 모든 조합의 서브토탈과 총계를 생성한다.
작동 방식
- CUBE는 주어진 컬럼 리스트에 대해 모든 조합의 서브토탈을 계산한다.
- 컬럼 A, B, C에 대해 CUBE를 사용하면, A, B, C의 각각에 대한 서브토탈, A와 B, A와 C, B와 C의 조합에 대한 서브토탈, 그리고 A, B, C 모두를 포함하는 총계를 계산한다.

예시

[SQL]

  
SELECT 구매고객, 구매월, COUNT(*) "총 구매건", SUM(구매금액) "총 구매액"
FROM 구매이력
GROUP BY CUBE (구매고객, 구매월);

[RESULT]

구매고객	구매월	총 구매건	총 구매액
AAA	202301	1	1000
AAA	202302	2	3000
AAA	202303	1	1000
AAA		4	5000
BBB	202301	3	2000
BBB	202302	5	3000
BBB	202303	1	2000
BBB		9	7000
CCC	202301	1	2000
CCC	202302	1	5000
CCC	202303	1	1000
CCC		3	8000
	202301	5	5000
	202302	8	11000
	202303	3	4000
		16	20000

GROUPING SET

기본 개념
- 특정 컬럼들의 조합으로 그룹화된 결과를 생성할 수 있다.
- ROLLUP과 CUBE의 기능을 더 세밀하게 제어할 수 있게 해준다.
- GROUPING SETS으로 데이터를 여러 차원에서 분석할 수 있으며, 특정 그룹화 조합에 대한 집계만을 선택적으로 계산할 수 있다.
기본 구문(SQL 표준 및 Oracle)
1 2 3 SELECT column1, column2, AGG_FUNC(column3) FROM table GROUP BY GROUPING SETS ((column1, column2), (column1), (column2), ());
- AGG_FUNC는 집계 함수(예: SUM, AVG, COUNT 등)를 나타낸다.
- column1, column2는 그룹화하고자 하는 컬럼을 나타낸다.
- 빈 괄호 ()는 전체 합계를 나타낸다.
- GROUPING SETS는 지정된 컬럼 조합에 대한 집계만을 계산한다.
작동 방식
- GROUPING SETS는 지정된 각 컬럼 조합에 대해 별도의 그룹화를 수행한다.
- 예를 들어, (column1, column2), (column1), (column2), 그리고 전체 합계에 해당하는 빈 조합 () 등으로, 다양한 수준의 데이터 집계를 한 번의 쿼리로 얻고자 할 때 유용하다.
예시

[SQL]

  
SELECT 구매고객, 구매월, COUNT(*) "총 구매건", SUM(구매금액) "총 구매액"
FROM 구매이력
GROUP BY GROUPING SETS ((구매고객, 구매월), (구매고객), (구매월), ());

[RESULT]

구매고객	구매월	총 구매건	총 구매액
AAA	202301	1	1000
AAA	202302	2	3000
AAA	202303	1	1000
AAA		4	5000
BBB	202301	3	2000
BBB	202302	5	3000
BBB	202303	1	2000
BBB		9	7000
	202301	5	5000
	202302	8	11000
	202303	3	4000
		16	20000

데이터베이스

This post is licensed under CC BY 4.0 by the author.

윈도우 함수(Window Functions)

주요 개념

유형

순위 함수

ROW_NUMBER(), RANK(), DENSE_RANK()

NTILE

그룹 분석 쿼리

ROLLUP

CUBE

GROUPING SET

Trending Tags