[Oracle] first_value 함수 사용하기

오라클로 DB개발시 특히 집계 결과를 생성시에 최소값 또는 최대값을 기준으로 편차를 계산하는 등의 작업이 필요할 때가 있다. 오라클의 first_value 함수를 사용하면 특정 집단내의 기준값을 찾고 그 기준으로 결과를 뽑아내기가 쉬워진다.

 

정의는 다음과 같다.

FIRST_VALUE (expr [ IGNORE NULLS ])
   OVER (analytic_clause)

 

다음과 같이 간단한 데이터 테이블이 있다고 가정하자.... 카테고리별로 물품이 있고 각 물품은 가격을 가지고 있다.

 

각 물건의 가격을 전체 집단의 최저가격 대비 얼마만큼 차이가 있는지 쉽게 알수 있는 방법은 뭘까?

 

예제1. 각 카테고리별로 최저값을 구하고 각 카테고리별로 모든 물품의 최저값과의 차이를 구해라

SELECT
    CATEGORY,
    PRODUCT,
    FIRST_VALUE(PRICE) OVER (PARTITION BY CATEGORY ORDER BY PRICE ASC) AS LOWEST_SAL,
    PRICE -  FIRST_VALUE(PRICE) OVER (PARTITION BY CATEGORY ORDER BY PRICE ASC) AS DIFF
FROM FIRST_VALUE_TEST
ORDER BY CATEGORY ASC

 

쿼리 결과는 다음과 같다. first_value함수 내에서 PARTITION BY를 사용하면 파티션 별로 first_value를 구하게 된다. 여기서는 PRICE 기준으로 오름차순 정렬을 했기 때문에 제일 가격이 작은값이 first_value로 선택된다.

 

과일에서 최저값은 포도 120이고 자동차에서 최저값은 카티발 100인다.

 

first_value내의 analytic_function partition by를 안썼을 경우를 살펴보자. 

SELECT
    CATEGORY,
    PRODUCT,
    FIRST_VALUE(PRICE) OVER (ORDER BY PRICE) AS LOWEST_SAL,
    PRICE - FIRST_VALUE(PRICE) OVER (ORDER BY PRICE) AS DIFF
FROM FIRST_VALUE_TEST
ORDER BY CATEGORY ASC

 

쿼리를 실행해보면 first_value는 그룹별로 구해지지 않고 전체 테이블에서 최저값이 정해지게 된다. 따라서 여기서는 카니발의 100이 최저값이 되고 그에 따른 차이값이 구해지게 된다.

 

간단하지만 유용하게 쓰일수 있는 first_value 함수 였습니다.

 

-- The End --