데이터 분석 SQL - Analytic SQL 집계(Aggregate) & Window 상세

집계(Aggregate) Analytic SQL

- sum(), max(), min(), avg(), count() 와 같은 집계 함수를 window를 이용하여 로우 레벨로 자유 자재로 집계할 수 있는 기능 제공

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

- 집계(aggregate) 계열 analytic 함수는 order by 절이 있을 경우 window절은 기본적으로 range unbounded preceding and current row 임

- 만약 order by 절이 없다고 window는 해당 partition의 모든 row를 대상

- 만약 partition 절도 없다면 window는 전체 데이터의 row를 대상

 

Window 절 구문

windowing_caluse =      { ROWS | RANGE }     { BETWEEN         { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING }     }     AND     { UNBOUNDED FOLLOWING | CURRENT ROW | value_expr {PRECEDING | FOLLOWING}     }     | { UNBOUNDED PRECEDING | CURRENT ROW | value_expr PRECEDING }     }

- window 절의 상세 구문설명

 

Window 의 범위설정

- 집계 ( aggregate ) 계열 analytic 함수는 order by 절이 있을 경우 window 절은 기본적은로 range between unbounded preceding and current now

- Windows 절에 between이 주어지지 않고 시작 범위만 주어질 때 종료 범위는 자동으로 current row.

 

이동 평균(Moving Average)

- 평균을 구하는 범위 구간을 이동 시키면서 구하는 평균값

 

range와 rows 적용 시 유의 사항

- range는 논리적인 row 위치를 지정하므로 보통은 숫자값과 interval값으로 window의 크기를 설정함

  over (partition by customer_id order by order_date range between interval '2' day preceding and current row)

- 또한 range는 rows와 동일한 window 크기 syntax도 사용 가능함 ( unbounded preceding, unbounded following )

- 집계(aggregate) 계열 analytic 함수는 order by 절이 있을 경우 window 절은 기본적으로 range between unbounded preceding and current row임, 즉 sum(sal) over (order by hiredate)는 sum(sal) over (order by hiredate range between unbounded preceding and current row)

- 하지만 range를 적용할 경우는 order by에서 동일 값이 있을 경우에 current row를 자신의 row가 아닌 동일 값이 있는 전체 row를 동일 그룹으로 간주하여 집계 analytic을 적용하므로 rows를 명시적으로 사용하는 경우와 값이 달라질 수 있음.