대표적인 윈도우 함수 6가지 알아보기

 

Photo by Annie Spratt on Unsplash

 

윈도우 함수는 고급 SQL 기능인데요. 이는 분석가뿐만 아니라 보고서를 만드는 사람, 데이터를 가공하는 그 누구에게도 상당히 유용한 기능입니다. 이 글은 많이 알려져 있는 윈도우 함수의 문법을 실제 예시와 함께 살펴보고자 합니다.

 

윈도우 함수가 무엇인가요?

윈도우 함수는 테이블 내부에 '윈도우 프레임'이라고 부르는 범위를 정의하고, 해당 범위 내부에 포함된 값을 자유롭게 사용할 수 있습니다. 윈도우 함수는 GROUP BY와 상당히 유사한데요. 그 이유는 윈도우 함수도 GROUP BY처럼 여러 행의 집계 값을 계산할 수 있기 때문입니다. 하지만, GROUP BY와 달리 윈도우 함수는 여러 행들을 합쳐 1개의 행으로 만들지 않습니다. 기존에 있던 원래 값은 그대로 두고 집계 값만 추가로 구할 수 있습니다.

이 기능은 데이터 분석가, 마케터, 재무 담당자에게 꽤나 유용합니다. 오늘날의 대부분의 관계형 데이터베이스도 해당 함수를 지원합니다. MySQL의 경우 최근까지만 해도 윈도우 함수를 지원하지 않았으나 버전 8.0 이후부터 해당 기능을 추가했습니다.

만약 지금까지 MySQL만 다뤄왔던 분이 계신다면, 아마 윈도우 함수는 상당히 새로운 개념일 것입니다. 그래서 윈도우 함수가 어떻게 작동되냐고요? 아래 sale 테이블을 가지고 이야기해보도록 하겠습니다. 해당 테이블은 제품 아이디 정보를 나타내는 id 열, 가격을 나타내는 value 열, 판매 연도를 나타내는 sale_year 열이 있습니다. 우리가 새로운 열을 추가하고 싶은데, 그 열엔 전년도의 가격을 나타내고 싶습니다. 아래는 윈도우 함수 중 하나인 LAG() 함수가 작동하는 방식을 나타낸 그림입니다.

 

 

위와 같은 결과를 얻을 수 있는 쿼리문은 다음과 같습니다.

SELECT
 id,
 value,
 sale_year,
 LAG(value) OVER(PARTITION BY id ORDER BY year) AS prev_value
FROM sales;

위 쿼리문은 결과값을 id라는 구분막(파티션) 기준으로 나눠서 반환해 주었습니다. 여긴 총 2개의 구분막(파티션)이 있네요. 하나는 ID 1이고 다른 하나는 ID 2입니다. 각 구분막(파티션) 안을 살펴보면 판매 연도 열 옆에 전년도 가격이 함께 적혀있습니다. 2016년의 경우 전년도 가격 데이터(2015년)가 존재하지 않기 때문에 NULL 값이 반환된 것이 보이시나요? 하지만 2017년도의 경우 전년도 가격 데이터(2016년)가 존재하기 때문에 ID 1의 2017년도 전년도 가격 열엔 125라는 숫자가, ID 2의 2017년도 전년도 가격 열엔 80이라는 숫자가 반환되었습니다.

윈도우 함수를 적은 후 OVER() 구문을 적어야 윈도우 함수가 실행됩니다. 위 예시에선 OVER() 구문 내에 결과값 내에 구분막을 설치하고 싶어서 PARTITION BY를 적었고, 각 구분막 내에서 시간 순으로 데이터를 정렬하고 싶어서 ORDER BY도 함께 적었습니다. 다른 윈도우 함수와 거의 이와 비슷한 방식으로 구현됩니다.

대표적인 윈도우 함수에는 순위 함수라고 불리는 RANK() 함수, DENSE_RANK() 함수, ROW_NUMBER() 함수가 있습니다. 이외에도 분석 함수라고 불리는 LAG() 함수와 LEAD() 함수가 있습니다.

순위 함수는 각 구분막(파티션) 안에 있는 각 행에게 순위를 부여하고 분석 함수는 각 구분막(파티션) 안에 있는 특정 행의 앞 또는 뒷 행을 불러옵니다. 만약 구분막(파티션)을 따로 정의하지 않는다면 전체 행의 하나의 큰 구분막으로 작용해 전체 행을 대상으로 윈도우 함수를 실행합니다.

예시를 살펴보도록 하죠.

 

첫 번째 예시 : RANK() 함수

RANK() 함수는 주로 보고서를 생성할 목적으로 많이 사용합니다. 해당 함수는 ORDER BY 문에서 정의한 순서에 따라 각 행에게 순위를 부여합니다. 순위는 숫자 1부터 시작해 순차적인 숫자를 부여합니다. RANK() 함수는 여러 행의 값이 동일한 일종의 동점인 상황이 발생하면 해당 동일한 값을 가진 행들에게 모두 같은 순위를 부여해 줍니다. 그리고 그다음으로 나온 값에겐 직전에 동점이라서 같은 순위를 부여했던 행의 개수만큼을 뛰어넘은 순위를 부여해 줍니다. 이러한 이유로 RANK() 함수가 반환한 순위는 따지고 보면 연속적인 숫자라고는 말하기는 좀 그렇네요.

예시를 보면 더 이해가 잘 될 것입니다. 아래는 product라는 테이블이 존재합니다.

id	name	category	ranking_score
1	Sofa Alan	living room	3422
2	Desk Mirian	office	1777
3	Sofa Frank	living room	1777
4	Armchair Ivo	living room	1201
5	Cabinet AWE	office	4547
6	Armchair Alex	living room	1201

아래의 쿼리는 name, category, ranking_score 열 이외에도 RANK() 함수를 사용해 ranking_score 값으로 정렬하여 순위를 부여한 후 rank_number이라는 이름을 붙여주었습니다.

SELECT
 name,
 category,
 ranking_score,
 RANK() OVER(ORDER BY ranking_score) AS rank_number
FROM product;

해당 쿼리의 결과는 다음과 같습니다.

name	category	ranking_score	rank_number
Armchair Ivo	living room	1201	1
Armchair Alex	living room	1201	1
Desk Mirian	office	1777	3
Sofa Frank	living room	1777	3
Sofa Alan	living room	3422	5
Cabinet AWE	office	4547	6

RANK() 뒤에 OVER() 구문과 더불어 ORDER BY 절이 함께 보이네요. 이런 순위 함수에 ORDER BY 절은 필수입니다. 위 테이블을 살펴보면 ranking_score의 오름차순 기준으로 값이 정렬된 것을 살펴볼 수 있습니다. ORDER BY 행 이름 뒤에 아무것도 적지 않으면 기본은 오름차순으로 정렬하고, 만약 내림차순으로 정렬하길 원한다면 뒤에 ORDER BY 행 이름 뒤에 DESC를 적어주면 됩니다. 

상단의 2개 상품 Armchair Ivo와 Armchair Alex의 ranking_score는 1201로 값이 같습니다. 즉, 동점의 상황이라 두 상품 모두에게 1이라는 순위를 부여했습니다. 1201 다음으로 존재하는 ranking_score은 1777인데, 이 숫자에게 부여된 순위는 2가 아니라 3입니다. 그 이유는 바로 앞 순위인 1을 총 2개 부여해줬기 때문이죠. 1777도 동점의 상황이라 두 상품 모두에게 3이라는 순위를 부여했고, 그다음으로 나온 숫자인 3422 에겐 4를 뛰어넘은 5라는 순위를 부여해 준 것입니다.

 

두 번째 예시 : DENSE_RANK() 함수

DENSE_RANK()는 RANK() 함수와 유사합니다. 하지만 RANK() 함수가 숫자를 뛰어넘었던 것에 반해 해당 함수는 숫자를 뛰어넘지 않고 순위를 부여합니다. 예시를 살펴보도록 하죠.

SELECT
 name,
 category,
 ranking_score,
 DENSE_RANK() OVER(ORDER BY ranking_score DESC) AS dense_rank_number
FROM product;

위 쿼리문은 이전 RANK() 함수의 예시에서 살펴봤던 것과 동일한 행을 반환합니다. 다만 RANK() 함수 대신 DENSE_RANK() 함수를 사용해 dense_rank_number이라는 열을 반환할 것입니다. 앞에 예시와 동일하게 ranking_score를 정렬 기준으로 삼았으나 이번에는 DESC를 붙여서 내림차순으로 정렬되도록 하였습니다.

Desk Mirian과 Sofa Frank의 ranking_score은 1777로 동일해서 dense_rank_number 열에 3이라는 순위를 부여해줬습니다. 1777 다음의 값인 1201에게 순위를 부여할 땐 이전 순위에서 3을 2개나 부여했음에도 불구하고 숫자를 뛰어넘지 않고 3 다음의 숫자인 4라는 순위를 부여해줬습니다. 

위 쿼리문의 결과는 다음과 같습니다.

name	category	ranking_score	dense_rank_number
Cabinet AWE	office	4547	1
Sofa Alan	living room	3422	2
Desk Mirian	office	1777	3
Sofa Frank	living room	1777	3
Armchair Ivo	living room	1201	4
Armchair Alex	living room	1201	4

 

세 번째 예시 : ROW_NUMBER() 함수

또 다른 대표적인 순위 함수는 ROW_NUMBER() 함수입니다. 정말 간단한데 각 행에 연속적인 숫자를 부여합니다. 예시를 보도록 하죠.

SELECT
 name,
 category,
 ranking_score,
 ROW_NUMBER() OVER(ORDER BY ranking_score) AS row_number
FROM product;

위 쿼리는 ranking_score 오름차순을 기준으로 순위를 부여합니다. 비록 ranking_score가 동점인 경우가 있더라도 동점을 무시하고 각 행마다 다른 순위를 부여해 주었습니다.

위 쿼리문의 결과는 다음과 같습니다.

name	category	ranking_score	row_number
Armchair Ivo	living room	1201	1
Armchair Alex	living room	1201	2
Desk Mirian	office	1777	3
Sofa Frank	living room	1777	4
Sofa Alan	living room	3422	5
Cabinet AWE	office	4547	6

 

구분막(파티션) 내의 순위 부여하기

구분막(파티션)을 사용해서 각 구분막(파티션)마다의 순위를 부여할 수 있습니다. PARTITION BY 뒤에 구분막(파티션)으로 삼고 싶은 열 이름을 정의하면 해당 열 내의 고유한 값마다 구분막(파티션)을 설치한 후 그 안에서 각각의 순위를 부여하게 됩니다. 

예시를 살펴보도록 하죠.

SELECT
 name,
 category,
 ranking_score,
 RANK() OVER(PARTITION BY category ORDER BY ranking_score) AS rank_number
FROM product;

위 쿼리는 category를 구분막(파티션) 삼았습니다. category 열에는 living room, office라는 2개의 고유한 값이 있어 총 2개의 구분막(파티션)이 생겼어요. 그다음, ranking_score 오름차순 기준으로 living room 내에서의 순위를 따로 부여하고, office 내에서의 순위를 따로 부여해 줍니다. 그래서 living room의 rank_number은 1에서 시작해 4에서 끝나고, office의 rank_number은 1에서 시작해 2에서 끝납니다.

위 쿼리의 결과는 다음과 같습니다.

name	category	category	rank_number
Armchair Ivo	living room	1201	1
Armchair Alex	living room	1201	1
Sofa Frank	living room	1777	3
Sofa Alan	living room	3422	4
Desk Mirian	office	1777	1
Cabinet AWE	office	4547	2

 

네 번째 예시 : LEAD() 함수

분석 함수는 윈도우 함수의 다른 종류입니다. 해당 함수는 행 내에 있는 값을 반환할 때 사용되고, 그 행들은 주로 선행하는 행들이거나 후행하는 행들입니다. 분석 함수는 특정 열의 선행 혹은 후행하는 행 사이의 차이를 구할 때 상당히 용이합니다. 첫 번째로 살펴볼 분석 함수는 LEAD() 함수입니다. LEAD라는 이름이 말해주듯이 해당 함수는 뒤에 있는 열들의 값을 이끌어 와서 반환해줍니다.

아래 테이블의 이름은 'toy_sale' 입니다. 한 번 살펴보세요.

id	toy_name	month	sale_value
1	robot	3	23455
2	robot	4	12345
3	robot	5	23000
4	kite	3	6890
5	kite	4	7600
6	kite	5	9120
7	ball	3	45123
8	ball	4	42000
9	ball	5	20300
10	puzzle	5	67000

LEAD() 함수를 활용한 쿼리문은 아래와 같이 작성해 보았습니다.

SELECT
 toy_name,
 month,
 sale_value,
 LEAD(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS next_month_value
FROM toy_sale;

LEAD() 함수는 열 sale_value의 기존 행 다음 행 값을 반환했습니다. 위 쿼리문 예시에서는 toy_name으로 구분막(파티션)을 사용했기 때문에 다음 행을 반환해 낼 수 있는 범위도 해당 구분막(파티션) 내에서만 가능합니다. 순위 함수와는 달리 LEAD() 함수는 다음 행을 반환할 열의 이름을 괄호 안에 써 줍니다. LEAD() 함수를 활용한 열의 마지막 값은 항상 NULL일 입니다. 아래 결과 테이블을 보면 5월 이후의 날짜 데이터가 존재하지 않아 더 이상 뒤에서 이끌어 올 데이터가 없으므로 NULL 값이 되는 것입니다.

쿼리 결과는 다음과 같습니다.

toy_name	month	sale_value	next_month_value
ball	3	45123	42000
ball	4	42000	20300
ball	5	20300	NULL
kite	3	6890	7600
kite	4	7600	9120
kite	5	9120	NULL
puzzle	5	67000	NULL
robot	3	23455	12345
robot	4	12345	23000
robot	5	23000	NULL

 

다섯 번째 예시 : LAG() 함수

LAG() 함수는 LEAD() 함수와 비슷합니다. 두 함수 모두 2개의 행 간의 차이를 구할 때 사용되곤 합니다. 두 함수의 차이점이라고 하면 LAG() 함수는 앞 행에서 값을 반환하고 LEAD() 함수는 뒷 행에서 값을 반환합니다. 쿼리문을 살펴보도록 합시다.

SELECT
 toy_name,
 month,
 sale_value,
 LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS prev_month_value,
 LAG(sale_value) OVER(PARTITION BY toy_name ORDER BY month) - sale_value AS difference
FROM toy_sale;

윈도우 함수가 사용된 문장을 보면 OVER() 구문 내에 toy_name 기준으로 구분막(파티션)이 생겼네요. 각 구분막 내에서 LAG() 함수는 열 sale_value의 기존 행 이전 행 값을 반환했습니다. 그래서 5월의 prev_month_value는 이전 행인 4월 달의 값인 42000을, 4월의 prev_month_value는 이전 행인 3월 달의 값인 45123을 반환했습니다. 3월의 경우는 3월 이전의 날짜 데이터가 존재하지 않아 NULL 값이 반환되었네요.

이 쿼리는 기존 sale_value 열과 LAG() 함수를 이용해 전월도 가격을 불러온 prev_month_value 열 사이의 차이를 계산할 수 있습니다. 계산된 값은 difference 라는 이름의 열에 저장되었고, NULL은 누구와 연산을 하는 무조건 NULL을 반환하기 때문에(마치 0처럼) 계산된 값도 NULL 입니다.

toy_name	month	sale_value	prev_month_value	difference
ball	3	45123	NULL	NULL
ball	4	42000	45123	3123
ball	5	20300	42000	21700
kite	3	6890	NULL	NULL
kite	4	7600	6890	-710
kite	5	9120	7600	-1520
puzzle	5	67000	NULL	NULL
robot	3	23455	NULL	NULL
robot	4	12345	23455	11110
robot	5	23000	12345	-10655

 

여섯 번째 예시 : 누적 합계 구하기 

윈도우 함수와 SUM() 함수를 함께 사용해 누적 합계를 구할 수 있습니다. 아래의 쿼리문을 보시죠.

SELECT
 toy_name,
 month,
 sale_value,
 SUM(sale_value) OVER(PARTITION BY toy_name ORDER BY month) AS total_toy_value
FROM toy_sale;

위의 쿼리문 중 5번째 문장은 윈도우 함수를 사용해 장난감 별 누적 합계를 구하고 이를 새로운 열인 'total_toy_value'에 저장합니다. 

toy_name	month	sale_value	total_toy_value
ball	3	45123	45123
ball	4	42000	87123
ball	5	20300	107423
kite	3	6890	6890
kite	4	7600	14490
kite	5	9120	23610
puzzle	5	67000	67000
robot	3	23455	23455
robot	4	12345	35800
robot	5	23000	58800

쿼리를 실행하면 위와 같은 결과를 얻게 됩니다. 'total_toy_value' 열 중 ball의 3월 값은 45123으로 기존 45123과 동일합니다. 3월이 첫 번째 행이기 때문에 앞에 누적할 값이 없기 때문이죠. 하지만 그다음 행인 4월 값은 87123으로 이는 3월(45123)과 4월(42000)을 더한 누적 합계입니다. 5월 값인 107423은 앞의 3월(45123)과 4월(42000) 그리고 5월(20300)의 누적 합계이고요. 누적 합계는 어느 분석에서나 자주 사용되는 개념이고, 특히나 재무나 트렌드 분석에 자주 사용됩니다.    

 

대부분의 관계형 데이터베이스는 윈도우 함수를 지원합니다. 만약 윈도우 함수와 친숙하지 않다면, 이번 기회에 윈도우 함수를 공부하는 것을 추천드립니다. 해당 개념은 데이터 분석가와 마케터들에게 상당히 유용하답니다. 부디 이 글이 윈도우 함수에 관한 여러분의 궁금증을 조금이나마 해소해드렸길 바랍니다!

원문: Six Examples Using MySQL Window Functions
이해를 위해 원문에서 제시한 설명에 부연 설명을 하였습니다.