윈도우 함수(2/2)

2. 집계함수에서  WINDOW 함수 사용

 이전에 일반적으로 SELECT 절에서 집계를 할 때나 GROUP 의 조건을 정의하던 집계함수를 통해 파티션별로 윈도우의 값을 구할 수 있습니다 이전 포스팅에서도 진행했던 EMPLOYEES  테이블에서 직업별로 만약 WINDOW FUNCTION을 사용한다고 하면 

2-1. SUM 함수

SUM과 같은 집계함수를 통해 직업별로 파티션을 나누고 직업별 합계 컬럼을 구할 수 있습니다

 

2-2 AVG 함수

문법은 동일합니다 이런식으로 작성을 해주면

이러한 결과를 반환해 줍니다 

2-3 COUNT

 한가지 예제만 더 해보겠습니다 만약 특정직원의 성과에 -50 ~+150 인 사람의 수를 구하는  통계값을 구하고자 한다면 아래와 같이 작성해 주면 됩니다

 

3. 그룹 내 행 순서 함수

WINDOW FUNCTION을 사용하면 그룹 내 행 순서를 보여주는 구문을 작성할 수도 있습니다 하지만  이 기능은 SQL SERVER에서는 지원을 하지 않는 기능입니다. 위에 집계함수를 사용한 WINDOW FUNCTION도 간단했지만 그룹 내 순서행 함수 또한 구문자체가 간단하기 때문에 어떠한 함수가 있는지 개념만 익히고 가겠습니다.

함수	설명
FIRST_VALUE	윈도우 파티셔닝을 거친 값 중 첫번째를 반환
LAST_VALUE	윈도우 파티셔닝을 거친 값 중 마지막 값을  반환
LAG	파티션별 윈도우에서 이전 몇 번쨰 행의 값을 가져올 수 있다
LEAD	파티션 윈도우에서 이후 몇 번째 행을 가져올 수 있다

 

4. 그룹 내 비율함수

 4-1. RATIO_TO_PERCENT

 RATIO_TO_PERCENT 함수를 사용하면 파티션 내 전체(SUM)값에 대한 행별 칼럼 값을 밴분율을 소수점 단위로 구할 수가 있습니다 예제 코드를 통해 알아보겠습니다 예를 들어 특정 직업 을 가진 사람들의 총 합겨량 에서 각자 차지하는 비율에 대해서 알고 싶을 경우가 있습니다

이런 쿼리를 데이터베이스 날린다면

위와 같은 결과를 얻을 수 있습니다 AD_VP와 같은 경우 두 사원의 판매량이 같기 때문에 다른 경우를 한번 더 가져와 보겠습니다

위와 같은 쿼리를 작성했을 경우에는 아래와 같은 결과를 반환합니다

 

여기서 판매량 비율의 합이 round로 반올림이 되었기 때문에 합계가 1.01이 나오지만 round를 하지 않았을 순수한 RATIO_TO_PERCENT 의 합계는 1이 됩니다

 4-2.PERCENT_RANK 함수

만약 특정 파티션에서 각각의 요소들의 자신의 위치가 어느정도 되는지 확인하고 싶다면 기준이 필요할 것이다 RPRCENT_RANK 함수는 이런 기준을 0~1 로 두고 이 지표를 통해 자신이 어느 위치에 해당하는지 조회 가능하도록 한다

 

위에 쿼리는 이러한 결과를 반환합니다 해당 코드를 통해 특정 사원이 자신의 그룹 내 위치를 알 수 있습니다.

4-3.CUME_DIST 함수

 CUME_DIST는 자주 사용하는 함수는 아니지만 파티션별로 전체건수에서 현재 행보다 작거나 같은 건수에 대한 누적 백분율을 구할때 사용합니다

위와 같은 쿼리를 사용하게 되면 JOB_ID를 기준으로 파티션을 나누고 판매량을 기준으로 누적 백분율을 계산합니다

4-4,NTILE 함수 

특정기준으로 파티션 개수를 지정해서 나누고 싶은 경우가 있을 수 있습니다 이럴 겨우 NTILE 함수를통해파티션의 개수를 지정해 주면 NTILE에 선언된 개수 만큼 파티션을 분할 해 줍니다