데이터 분석 SQL - Analytic SQL 개요와 순위

Analytic SQL - Group by 집계 함수 차이

- Group by는 원본 데이터 집합의 레벨을 변경해서 적용	- Analytic SQL은 원본 데이터 집합의 레벨을 그대로 유지하면서 적용 - Window를 이용하여 Row 단위의 집합 연산 수행 가능

 

Analytic SQL 유형 및 사용법

유형	함수
순위/비율 함수	Rank, dense_rank, row_number, Percent_rank, cume_dist, ntile
집계(Aggregate) 함수	sum, max, min, avg, count ( distinct x )
Lead/Lag	Lead, Lag
First_value/Last_value	First_value, Last_value
Inverse Percentile	Percentile_cont, Percentile_disc

<Analytic function>(인자1, ...)
OVER (
    [Partition 절]	-- 그룹화 컬럼명
    [Sorting 절]	-- 정렬 컬럼명(Window 이동 방향 기준 컬럼명)
    [window 절]		-- Window 범위(Row, Range)
)

- 절의 순서가 바뀌어서는 안됨!

- 원본 데이터의 레벨을 그대로 유지하면서, 그룹핑 레벨에서 자유롭게 Window의 이동과 크기를 조절하면서 Analytic을 수행

- 자유로운 window 설정에 따른 analytic 구사가 가능하므로 SQL의 Analytic 함수를 window 함수로도 지칭

 

---

순위 Analytic SQL

- 일반적인 순위 : rank, dense_rank. row_number

- 0~1 사이 정규화 순위 : cume_dist, percent_rank

- 분위 : ntile

 

순위 Analytic - rank, dense_rank, row_numbe

- 전체 데이터/특정 그룹핑 내에서 특정 기순으로 순위를 매기는 함수

- Rank, dense_rank, row_number 는 공동 순위를 정하는 로직이 조금씩 다름

순위 함수 유형	설명
rank	공동 순위가 있을 경우 다음 순위는 공동 순위 개수만큼 밀려서 정함 1,2,2,4 또는 1,2,2,2,5
dense_rank	공동 순위가 있더라도 다음 순위는 바로 이어서 정함 1,2,2,3 또는 1,2,2,2,3
row_number	공동 순위가 있더라도 반드시 unique한 순위를 정함 1,2,3,4,5

select a.empno, ename, job, sal
    , rank() over(order by sal desc) as rank
    , dense_rank() over(order by sal desc) as dense_rank
    , row_number() over(order by sal desc) as row_number from hr.emp a;

 

select a.empno, ename, job, deptno, sal
    , rank() over(partition by deptno order by sal desc) as rank
    , dense_rank() over(partition by deptno order by sal desc) as dense_rank
    , row_number() over(partition by deptno order by sal desc) as row_number
from hr.emp a;

 

순위 함수에서 NULL 처리 로직

rank() OVER (     < Partition 절 >     order by column [nulls first/last]

- Nulls first는 Null을 최우선 순위로, - Nulls last는 Null을 가장 마지막 순위로 결정

- Order by 시 기본적으로 생략되어 있으며 Default는 nulls first임.

- Nulls first 는 Null을 최우선으로, Nulls last는 Null을 가장 마지막으로