SQL 문법 | 추출 함수 | FIRST_VALUE LAST_VALUE

SQL에서는 조회된 자료에서 첫 번째 값 혹은 마지막 값을 바로 추출해 낼 수 있는 FIRST_VALUE와 LAST_VALUE 라는 함수가 있다. 본 포스트에서 FIRST_VALUE와 LAST_VALUE에 대해서 자세히 살펴볼 것이다.

FIRST_VALUE & LAST_VALUE

FIST_VALUE와 LAST_VALUE

01 FIRST_VALUE : 첫번째 값을 칼럼에 표현하기

조회된 세트(집합)에서 어떠한 상황에서도 첫 번째 값을 구하는 함수가 FIRST_VALUE이다. 주의해야 할 점은 OVER( )에 ORDER BY 절을 꼭 삽입해야 한다는 것이다. 기본 쿼리 작성은 다음과 같이 한다.

SELECT 	COL1, COL2, FIRST_VALUE(COL3) OVER(ORDER BY COL2)
FROM	TABLE_NAME
;

EXAMPLE

제품 이름과 가격을 조회하고 세 번째 열에 제품 가격 중 가장 비싼 값을 표시해 주세요.

SELECT	PRODUCT_NAME, PRODUCT_PRICE, FIRST_VALUE(PRODUCT_PRICE) OVER(ORDER BY PRODUCT_PRICE DESC) AS BEST_PRICE
FROM	SHOP
;

결과 예시

PRODUCT_NAME	PRODUCT_PRICE	BEST_PRICE
PRODUCT03	10,000	10,000
PRODUCT02	7,000	10,000
PRODUCT01	800	10,000
PRODUCT04	900	10,000

OVER( )안에 PARTITION BY를 넣어 준다면 그룹별 첫 번째 값을 불러올 수 있다.

EXAMPLE

제품카테고리, 이름, 가격을 조회하고 네 번째 열에 카테고리별 가장 비싼 값을 표시해 주세요.

SELECT	PRODUCT_CATEGORY, PRODUCT_NAME, PRODUCT_PRICE, FIRST_VALUE(PRODUCT_PRICE) OVER(PARTITION BY PRODUCT_CATEGORY ORDER BY PRODUCT_PRICE DESC) AS BEST_PRICE
FROM	SHOP
;

결과 예시

PRODUCT_CATEGORY	PRODUCT_NAME	PRODUCT_PRICE	BEST_PRICE
A	PRODUCT01	5,000	5,000
A	PRODUCT04	1,000	5,000
A	PRODUCT06	300	5,000
B	PRODUCT02	210,000	210,000
B	PRODUCT03	40,000	210,000
B	PRODUCT05	30,000	210,000

 

 

 

02 LAST_VALUE : "정해진 범위 내"의 마지막 값을 칼럼에 표현하기

LAST_VALUE함수는 FIRST_VALUE 함수와 비슷하게 조회한 데이터 세트에서 마지막 값을 새로운 열에 표현해 준다. FIRST_VALUE함수와 마찬가지로 LAST_VALUE 함수도 OVER( )에서 ORDER BY 절을 사용해야 한다. 그런데 LAST_VALUE에서는 한 가지 더 주의해야 할 점이 있다. 서브타이틀 이름에서 보면 "정해진 범위 내"가 강조되어 있는데 LAST_VALUE에서는 범위가 중요한 역할을 하기 때문이다. 먼저 FIRST_VALUE와 LAST_VALUE 함수의 작동 방식을 살펴보면 이 두 함수는 ORDER BY로 정렬된 집합에서 첫 행부터 순서대로 열들을 비교를 해 나간다. 그래서 1행을 읽던 4번째 행을 읽던 FIRST_VALUE 함수에서 첫 번째 행은 언제나 첫 번째 행으로 변함이 없다. 이에 반해 LAST_VALUE 함수는 매번 행을 읽을 때마다 읽히고 있는 행이 마지막 행이 된다. 이런 문제를 방지하기 위해 LAST_VALUE 함수는 범위 설정을 해줘야만 정해진 데이터 내의 마지막 값을 구할 수 있다. 기본 쿼리는 다음과 같다.

SELECT	COL1, COL2,
	LAST_VALUE(COL3) OVER(ORDER BY COL2 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
FROM	TABLE_NAME
;

UNBOUNDED PRECEDING은 전체 데이터 셋의 첫번째 행을 뜻하고 UNBOUNDED FOLLOWING은 마지막 행을 뜻한다. 설명에서는 윈도우의 처음과 마지막이라는 말도 사용한다.

EXAMPLE

제품카테고리, 이름, 가격을 오름차순으로 조회하고 네 번째 열에 카테고리별 가장 비싼 값을 표시해 주세요.

SELECT	PRODUCT_CATEGORY, PRODUCT_NAME, PRODUCT_PRICE,
	LAST_VALUE(PRODUCT_PRICE)
    	OVER(ORDERR BY PRODUCT_PRICE ASC BETWEEN UNBOUNDED PRECIDING AND UNBOUNDED FOLLOWING) AS BEST_PRICE
FROM	SHOP
;

결과 예시

PRODUCT_NAME	PRODUCT_PRICE	BEST_PRICE
PRODUCT01	900	10,000
PRODUCT02	800	10,000
PRODUCT03	7,000	10,000
PRODUCT04	10,000	10,000

 

 

 

SUMMARY

• FIRST_VALUE 함수는 조회한 데이터 세트에서 첫 번째 값을 새로운 열에 보여준다.
• FIRST_VALUE(칼럼_이름) OVER(ORDER BY 정렬_칼럼_이름) : 전체가 범위일 경우는 범위를 따로 설정 안 해도 된다.
• LAST_VALUE 함수는 조회한 데이터 세트에서 마지막 값을 새로운 열에 보여준다.
• LAST_VALUE(칼럼_이름) OVER(ORDER BY 정렬_칼럼_이름 BEWEEN 범위) : 범위 설정을 해줘야 한다.
• 조회한 데이터 세트 전체 범위일 경우 : BETWEEEN UNOUNDED PRECEDING AND UNBOUNDED FOLLOWING

---

* 오타 및 잘못된 내용이 있다면 댓글로 알려주세요!