Analytic SQL - 집계(Aggregate) Analytic과 Window 상세 #
Aggregate Functions vs Window Functions #
- Aggregate Functions
- Group by는 원본 데이터 집합의 레벨을 변경하여 적용
- Window Functions
- Analytic SQL은 원본 데이터 집합의 레벨을 그대로 유지하면서 적용
- Window를 이용하여 Row 단위의 집합 연산 수행 가능
- 원본 데이터의 레벨을 그대로 유지하면서, 그룹핑 레벨에서 자유롭게 Window의 이동과 크기를 조절하면서 Analytic을 수행
Analytic SQL 적용 로직 #
<Analytic function> (인자1, ...) OVER ( [Partition절] [Sorting절] [Window절] )
- 자유로운 window 설정에 따른 analytic 구사가 가능하므로, SQL의 Analytic 함수를 window 함수로도 지칭한다.
순위 Analytic #
- 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
NULL 처리
- Nulls first : NULL을 최우선 순위로 (default)
- Nulls las : NULL을 가장 마지막 순위로
rank() OVER (<Partition절> order by column [nulls first/last])
집계(aggregate) 계열 analytic 함수 #
- sum(), max(), min(), avg(), count() 등
- order by절이 있을 경우 window 절은 기본적으로
range unbounded preceding and current row이다. - order by절이 없다면 window는 해당 partition의 모든 row를 대상으로 한다.
- partition, order by절 모두 없다면 window는 전체 데이터의 row를 대상으로한다.
range와 rows의 차이 #
- range :
range unbounded preceding and current row - rows :
rows between unbounded preceding and current now - 동일한 order by컬럼 값이 있을 경우
- range : 중복되는 값들 모두를 대상으로 한다.
- current row를 자신의 row가 아닌 동일 값이 있는 전체 row를 동일 그룹으로 간주하여 집계에 적용한다.
- rows : 중복이 되더라도 current row를 대상으로 한다.
- range : 중복되는 값들 모두를 대상으로 한다.