SQL 윈도우 함수 예제 - 순위함수(RANK), 누적 집계함수

오늘은 SQL에서 자주 사용하는 윈도우 함수(Window function)에 대해 포스팅합니다.

 

윈도우 함수란 행과 행 간의 관계를 정의하며,

윈도우 함수에는 순위함수와 누적 집계함수 2가지가 있습니다.

그래서 윈도우 함수를 활용해 순위(순서)를 매기거나, 그냥 집계함수가 아닌 "누적" 집계값을 뽑을 수 있습니다. 바로 알아봅시다.

 

✔Table of Contents

 

윈도우 함수 기본 문법

윈도우 함수만의 특이한 문법이 있는데, 윈도우 함수를 쓸 때는 다음의 문법을 사용합니다.

윈도우 함수 OVER (ORDER BY 열 ASC or DESC)

 

예를 들어 윈도우 순위함수를 사용하는 경우, 특정 열을 기준으로 정렬하여 순위를 부여합니다.

만일 또다른 특정 열을 기준으로 '그룹화'를 하고 싶다면 어떻게 하면 될까요?

 

윈도우 함수 OVER (PARTITION BY 열1 ORDER BY 열2 ASC or DESC)

 

이렇게, PARTITION BY를 추가해주면 열1을 기준으로 그룹화를 하고, 열2를 기준으로는 정렬을 합니다. 

(PARTITON BY는 GROUP BY와 같은 기능이라고 생각하시면 쉽습니다.)

또한 열1과 열2는 같은 열을 써도 됩니다.

이제 예제로 알아볼까요?

 

USE EDU 
SELECT * FROM [ORDER]

---

1. 순위함수 (ROW_NUMBER, RANK, DENSE_RANK)

윈도우 순위함수에는 3가지가 있으며, 동점자 처리를 하는 방식에 따라 나뉩니다.

예를 들어, 점수가 9, 8, 8, 2점일 때 순위를 매긴다면 어떻게 될까요?

 

• ROW_NUMBER() : 동일한 값에 고유한 순위 반환 / 순위는 1, 2, 3, 4로 표시(동점자 무시)
• RANK() : 동일한 값에 대해 고유한 순위를 반환하되, 동점자 처리 O / 순위는 1, 2, 2, 4로 표시
• DENSE_RANK() : 동점자 처리 O지만 동일한 순위는 하나의 등수로 간주 / 순위는 1, 2, 2, 3으로 표시

우리가 보통 사용하는 순위 방식은 바로 RANK() 입니다.

바로 예제로 알아봅시다.

 

Q. order_date(주문일자)에 대해, 최초주문일자로부터 순위를 부여하라. (위의 3개 함수를 모두 사용)

SELECT order_date,
	ROW_NUMBER() OVER (ORDER BY order_date ASC) AS rownumber,
	RANK() OVER (ORDER BY order_date ASC) AS rnk,
	DENSE_RANK() OVER (ORDER BY order_date ASC) AS densernk
FROM [ORDER]

 

ROW_NUMBER() 함수는 rownumber / RANK() 함수는 rnk / DENSE_RANK() 함수는 densernk라는 열로 뽑혔습니다.

2020-01-10 00:00:00 라는 똑같은 시간이 3개가 등장하는데요, 3개의 순위함수가 순위를 내는 방식이 좀 다르죠?

ROW_NUMBER는 모두 동점이지만 1,2,3으로,

RANK는 모두 동점이니까 1,1,1로, (대신 4번째 행부턴 4)

DENSE_RANK는 모두 동점이니 1,1,1로 나옵니다. (대신 4번째 행부턴 2)

 

Q. mem_no(회원번호)에 따라 구분지어 order_date에 순위를 부여하라.

SELECT mem_no, order_date,
	ROW_NUMBER() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS rownumber,
	RANK() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS rnk,
	DENSE_RANK() OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS densernk
FROM [ORDER]

 

결과를 보면 각 mem_no별 order_date의 순위가 매겨져 있는 것을 확인할 수 있네요!

빨간선으로 표시해놓듯, 회원번호가 1000005인 것의 순위와 1000006인 것의 순위가 따로 집계된 것을 확인할 수 있습니다.

 

이러한 순위함수는 어떨 때 사용하면 좋을까요?

대표적으로 유저들의 순위를 구할 수 있겠죠. 예를 들면 커머스 업종에서, 유저별로 몇 회째 구매한 것인지 순위를 매기는데 2번째 구매한 데이터만 뽑고 싶다! 라고 할 때 윈도우 순위 함수를 응용할 수 있습니다.

---

2. 누적 집계함수(COUNT, SUM, AVG, MAX, MIN)

이전에 집계함수 포스팅을 보시면 아시겠지만, GROUP BY와 함께 쓰여 통계량을 구하는 데 사용된다고 했었죠.

윈도우 집계함수는 "누적"값을 구하는 데 사용됩니다. 바로 예제로 알아봅시다.

 

Q. order_date에 따라, sales_amt의 누적 구매횟수/누적 구매금액/누적 평균구매금액/누적 최고구매금액/누적 최저구매금액을 구하여라.

SELECT order_date, sales_amt,
		COUNT(sales_amt) OVER (ORDER BY order_date ASC) AS 누적구매횟수,
		SUM(sales_amt) OVER (ORDER BY order_date ASC) AS 누적구매금액,
		AVG(sales_amt) OVER (ORDER BY order_date ASC) AS 누적평균구매금액,
		MAX(sales_amt) OVER (ORDER BY order_date ASC) AS 누적최고구매금액,
		MIN(sales_amt) OVER (ORDER BY order_date ASC) AS 누적최저구매금액
FROM [ORDER]

 

order_date별로 누적 집계값이 다르게 뽑히는 것을 볼 수 있습니다.

같은 값인 2020-01-10 00:00:00 에서 구매건이 3개가 나오는데, 이것들의 누적 구매횟수가 3회로 뽑히게 됩니다.

그 외에 누적 구매금액(sales_amt의 합계) / 누적 평균구매금액(sales_amt의 평균) / 누적 최고구매금액(3건 중에서 가장 큰 sales_amt) / 누적 최저구매금액(3건 중에서 가장 작은 slaes_amt) 가 뽑힌 것을 확인할 수 있습니다.

 

Q. 근데 위의 경우엔 order_date별로 그룹화를 해서 봤다고도 볼 수 있지 않나요? 저는 PARTITION BY도 써야 한다고 생각했는데요??

 

-> 만일 PARTITON BY order_date를 쓴다고 하면, order_date에 따라 그룹이 만들어지고, 이에 따라 누적 집계 결과가 달라집니다. 실제로 해봅시다.

 

-- 위와 다른 결과
SELECT order_date, sales_amt,
		COUNT(sales_amt) OVER (PARTITION BY order_date ORDER BY order_date ASC) AS 누적구매횟수,
		SUM(sales_amt) OVER (PARTITION BY order_date ORDER BY order_date ASC) AS 누적구매금액,
		AVG(sales_amt) OVER (PARTITION BY order_date ORDER BY order_date ASC) AS 누적평균구매금액,
		MAX(sales_amt) OVER (PARTITION BY order_date ORDER BY order_date ASC) AS 누적최고구매금액,
		MIN(sales_amt) OVER (PARTITION BY order_date ORDER BY order_date ASC) AS 누적최저구매금액
FROM [ORDER]

결과를 보면, order_date별로 누적 집계값을 계산하게 됩니다. 즉, 2020-01-10 00시의 누적집계값 따로, 2020-01-11 00시의 누적집계값 따로 이런 식이 됩니다. 어떤 차이가 있는지 보이시죠? ㅎㅎ 

 

마지막으로 order_date뿐 아니라 mem_no(회원번호)별로도 나누어 누적 집계값을 구해봅시다.

 

Q. mem_no 및 order_date별(오름차순) 누적 집계함수를 사용하시오.

SELECT mem_no, order_date, sales_amt,
		COUNT(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적구매횟수,
		SUM(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적구매금액,
		AVG(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적평균구매금액,
		MAX(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적최고구매금액,
		MIN(sales_amt) OVER (PARTITION BY mem_no ORDER BY order_date ASC) AS 누적최저구매금액
FROM [ORDER]

 

이번에는 mem_no별로 먼저 그룹화가 되고, 그 이후에 order_date가 날짜 순서대로 집계되었습니다.

회원번호 1000005인 사람의 누적 집계값들이 뽑히고, order_date가 같은 경우엔 누적값도 같게 나오는 것을 확인할 수 있습니다.

 

---

코드는 여기서 확인하세요 :)

GitHub - suy379/SQL-basic: My SQL Study Note