Analytic SQL 3. 집계(Aggregate) SQL

일반적인 순위 함수 사용방법과 더불어 집계 함수를 사용하는 방법에 대해서도 소개하는 시간을 가진다.

 

집계 함수란?

전부터 계속 말하고 있지만, 특정 컬럼의 재가공과 아주 밀접한 관련이 있는 집계 함수.

그 종류에는 sum(), max(), min(), avg(), count() 등이 있으며, 해당 집계 함수를  window를 이용하여 행 레벨로 자유자재로 집계할 수 있는 기능을 Analytic SQL에서는 제공하고 있다.

 

아래의 쿼리 예시는 개요에서도 보여준 바가 있는 Analytic SQL의 사용 방법을 보여준다.

<Analytic 함수> (인자)
over(
-- partition 절
-- sorting 절
-- window 절
)

여기에서 over 구문의 마지막으로 나오는 window의 첫 번째 사용 방법으로 집계 함수를 설명하고자 하는 것이다.

(window 절이 Analytic SQL 중 Syntax의 길이가 제일 길다고 하다.)

 

대표 사용 예시 - sum() 함수

 

아래의 코드는 sum 함수를 이용하여 학년별 학생의 돈의 합을 출력시키는 쿼리문이다.

select student_id, grade, money
, sum(money) over (partition by grade) as total_money
from a_table;

상기의 코드는 학년을 기준으로 돈의 합을 출력하는 코드를 보여주고 있으며, 이는 새로운 컬럼이 작성됨을 의미한다.

 

아마 아래와 같은 테이블이 출력될 것이다.

student_id	grade	money	total_money
101	1	500	1500
102	1	1000	1500
202	2	700	700
301	3	850	850

total_money의 같은 1500 값이 보이는가? 단순히 파티션을 나누고 합을 출력하라 하면 모든 파티션에 대해 같은 값이 출력되는 것을 알 수 있다.

 

만약 이를 누적합으로 바꾸고 싶다면 아래와 같이 코드를 수정하면 될 것이다.

select student_id, grade, money
, sum(money) over (partition by grade order by student_id) as cum_money_by_ord
from a_table;

order by 구문이 추가되어 학번을 기준으로 오름차순 정렬 후 sum 함수가 작동되는 것을 확인할 수 있다.

 

해당 코드를 실행하게 되면 다음과 같은 누적합 테이블을 만들 수 있을 것이다.

student_id	grade	money	total_money
101	1	500	500
102	1	1000	1500
202	2	700	700
301	3	850	850

 

---

window 절의 생략 

 

사실 order by를 추가하게 되면 자동적으로 window가 생략된 채 실행되는 것으로 생각하는 것이 올바르다. 이는 order by 때문에 누적합이 계산된 것이 아닌, order by 절을 추가함으로 암묵적으로 생성되는 window 절에 의해 누적합이 도출되는 것이라 생각해야 하는 것이 맞다는 이야기와 같다.

💡 order by 조건을 넣는 순간, window 절은 암묵적으로 생성된다.

 

order by 구문만 추가하여 누적합을 계산했던 코드를 아래와 같은 쿼리문으로 더 명시적인 표현이 가능하다.

select student_id, grade, money
, sum(money) over (partition by grade order by student_id rows between unbounded preceding and current row) as cum_money_by_ord
from a_table;

해당 코드를 실행하게 되면 누적합 테이블 결과값과 완전히 동일한 결과가 출력될 것이다.

 

그럼 저 길어보이는 rows between unbounded precding and current row 절에 대해서 하나씩 뜯어보자.

 

unbounded preceding : 해당 파티션의 시작 부분을 의미한다.
current row : 해당 파티션에서 현재 참조하고 있는 행을 의미한다.

 

이를 키워드를 나누어 뜯어보면 다음과 같은 의미로 해석할 수 있다.

rows between unbounded precding and current row
파티션의 시작 부분부터(and) 현재 행까지의(between) 모든 열들

 

이는 우리가 평소에 알고 있는 누적합과의 정의와 동일하다! 따라서 암묵적으로 실행되는 상기의 코드는 항상 숙지하고 있는 편이 좋을 것 같다.

 

 

집계 계열 함수의 암묵적인 규칙

1. order by 절까지만 있을 경우 window 절은 기본으로 range undounded preceding and current row를 생성한다.

💡 여기서는 왜 range야? : range에 rows 외에도 colums도 포괄할 수 있기 때문에 더 넓은 범위인 range로 규칙을 만듦. 하지만 머릿속에서는 95% 정도로 rows between으로 생각하는 게 정신건강에 좋다😁

2. 만약 order by 없이 partition 절만 존재한다면 window 절은 해당 partition의 모든 row를 대상으로 진행한다.

3. 만약 partition 절조차 없다면 window 절은 전체 데이터의 row를 대상으로 진행한다.

 

해당 절은 귀에 딱지가 앉도록 듣게 될 것이니, window 절 코드가 생략되었다! 하면 항상 상기의 window 절이 생략되었구나 생각하면서 쿼리문을 작성하는 것이 좋을 것 같다. 

 

---

그 외의 사용 예시 - 최대, 최소, 평균, 누적

 

window 절을 설명하기 위한 대표적인 예시로 sum 함수를 사용하였다. 이를 응용하여 어떠한 방식으로 테이블이 만들어지는지 다른 함수를 통해서 살펴보도록 하자. 아마 window 절의 규칙을 이해했다면 무난하게 이해하고 넘어갈 수 있는 내용이지 않을까 싶다.

 

아래는 max 함수를 이용한 쿼리문이다. 기존 sum 함수에서 함수 이름만 바뀐 형태이다.

select student_id, grade, money
, max(money) over (partition by grade order by student_id) as max_money
from a_table;

 

조금 더 시각적인 확인을 위해 기존 테이블 값도 살짝 변경해보도록 하자.

student_id	grade	money	max_money
101	1	1000	1000
102	1	600	1000
103	1	1300	1300
202	2	700	700
301	3	850	850

파티션별로(학년별로) 돈의 크기를 훑으며 행 레벨에서 가장 큰돈을  출력해내는 것을 알 수 있다.  

 

아래는 min 함수를 이용한 쿼리문과 그 결과 테이블이다. 상기 max 코드와 작동법이 거의 유사하다.

select student_id, grade, money
, min(money) over (partition by grade order by student_id) as min_money
from a_table;

student_id	grade	money	min_money
101	1	1000	1000
102	1	600	600
103	1	1300	600
202	2	700	700
301	3	850	850

 

아래는 avg 함수를 이용한 쿼리문과 그 결과 테이블이다. 누적 평균 테이블이 작성되는 것을 확인할 수 있다.

select student_id, grade, money
, avg(money) over (partition by grade order by student_id) as avg_money
from a_table;

student_id	grade	money	avg_money
101	1	1000	1000
102	1	600	800
103	1	1300	966.6667
202	2	700	700
301	3	850	850

현재는 누적 평균에 대한 코드이지만, 실생활에서는 상기 누적 평균보다는 이동 평균이라는 지표를 더 많이 활용한다고 한다. 해당 내용은 추후 공부를 진행할 때 소개하는 시간을 가지도록 한다.

 

아래는 count 함수를 이용한 쿼리문과 그 결과 테이블이다. 누적 건수라는 해괴한 결과가 나오는 것이 특징

select student_id, grade, money
, count(money) over (partition by grade order by student_id) as count_money
from a_table;

student_id	grade	money	count_money
101	1	1000	1
102	1	600	2
103	1	1300	3
202	2	700	1
301	3	850	1