Analytic SQL 1. 개요

그래서 이게 뭔데?

SQL을 이용한 분석. 예전부터 존재는 했으나(OLAP 등) Analytic SQL이 나오면서 데이터 분석을 더욱 용이하게 함

RDBMS(Realtional DataBase Management System, 관계형 데이터베이스 시스템)에서 Analytic SQL을 제공한 이후 다시 데이터 분석의 중심에 설 수 있는 계기가 되었음

 

기존 집계 함수 : Group by를 사용할 때 원본 데이터 집합의 레벨을 변경하여 적용한다.

💡 Group by만을 이용해서 데이터 분석을 하기에는 너무 귀찮고 어렵고 레벨 변경에 한계가 있어!

 

Analytic SQL : 원본 데이터 집합의 레벨을 그대로 유지하면서 적용한다는 차이를 가짐.

💡 Window function(=Analytic Function)을 이용하여 행 단위(Row Level) 단위의 집합 연산을 수행 가능

출처 : https://towardsdatascience.com/a-guide-to-advanced-sql-window-functions-f63f2642cbf9

 

Window라는 의미가 말 그대로 창(window)이 원본 데이터 중 특정 데이터 집합을 훑어본다는 느낌으로 접근해 보자.

알고리즘 중 Sliding Window 알고리즘과 비슷할지도?

 

아래는 대표적인 Analytic SQL 유형

유형	함수
순위/비율	rank, dense_rank, row_number, percent_rank, cume_dist, ntile 등
집계 함수(Aggregate)	sum, max, min, avg, count(distinct는 불가)
lead/lag	lead, lag
first_value/last_value	first_value, last_value
inverse percentile	percentile_cont, percentile_disc

inverse percentile에 작성된 함수는 PostgreSQL, MySQL에서는 모두 group by절과 같이 사용되어야 함에 유의

 

상기 표에서 소개한 유형과 함수는 다음 장들을 통해서 자세히 살펴보는 시간을 가져본다.

 

Analytic 사용방법과 적용 로직

 

상기 Analytic SQL 함수와 함께 over 절이 함께 사용되면 온전한 사용이 가능

rank(agument)
over(
-- partition 절
-- sorting 절
-- window 절
)

 

partition, sorting, window가 각각 어떤 역할을 하는지는 아래의 이미지를 통해 살펴보도록 하자.

 

출처 : https://www.kaggle.com/code/alexisbcook/analytic-functions

 

우선 하나의 데이터셋이 1. partition 행위를 통해 group by절과 같은 분할(파티션 분할)이 된 것을 확인할 수 있다.

분할된 데이터 별로 2. order by/sorting을 적용시켜 오름차순/내림차순 등과 같은 정렬이 가능하고

마지막으로 3. window절을 이용하여 row 레벨+각 분할 파티션 별로 누적된 평균을 사용하는 것을 알 수 있다.

 

이를 명시적으로 살펴보면 다음과 같은 특징을 가진다 할 수 있다.

 

1. partition 절 : 그룹화를 진행할 컬럼명을 작성하면 해당 컬럼을 기준으로 그룹화(파티셔닝)가 진행됨
2. sorting 절 : 정렬되는 컬럼명을 각 파티션별로 정렬
3. window 절 : window 되는 레벨의 범위를 정의하고 지정. 원본 데이터의 레벨을 그대로 유지하면서 파티션(그룹) 레벨에서 자유롭게 window 이동이 이루어짐