[mssql][SQL2012] 결과집합의 첫 번째 값과 마지막 값을 가져오는 FIRST_VALUE, LAST_VALUE

 

출처 http://www.sqler.com/537316

 

 

 

안녕하세요? 쓸만한게없네 윤선식입니다.  

SQL Server 2012의 신규 분석 함수로 FIRST_VALUE와 LAST_VALUE 가 있습니다.  

 

만약 Oracle 11G의 Window Funcion을 사용하신 분이면 금방 이해가 가실 것입니다.. 이와 동일하기 때문이죠

 

다음 예제 데이터를 통해 기능을 살펴보겠습니다.    

 

CREATE TABLE dbo.T_FIRST_LAST

(

        SEQ INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

        PRICE INT NOT NULL,

        PROD_NAME VARCHAR(100) NOT NULL

)

;

 

INSERT INTO dbo.T_FIRST_LAST (PRICE, PROD_NAME) VALUES

(600, '상품1'), (400, '상품6'), (100, '상품3'), (300, '상품2'),

(300, '상품4'), (200, '상품8'), (500, '상품10'), (700, '상품9');

 

SELECT  SEQ, PRICE, PROD_NAME

FROM    dbo.T_FIRST_LAST

ORDER BY       SEQ

;

 

 

1. FIRST_VALUE

SELECT  SEQ, PRICE, PROD_NAME,

        FIRST_VALUE(PROD_NAME) OVER (ORDER BY PRICE ASC) AS MIN_PRICE_PROD_NAME

FROM    dbo.T_FIRST_LAST

ORDER BY       SEQ

;

 

가장 낮은 값인 100에 대한 상품3에 대한 값이 표시됩니다.

 

2. LAST_VALUE

SELECT  SEQ, PRICE, PROD_NAME,

        LAST_VALUE(PROD_NAME) OVER (ORDER BY PRICE ASC) AS MAX_PRICE_PROD_NAME

FROM    dbo.T_FIRST_LAST

ORDER BY       SEQ

;

 

결과가 나오긴 하는데, FIRST_VALUE처럼 가장 낮은 값만 나오는 것이 아니라 데이터가 다른 형태로 나옵니다.

이는 Windows Function OVER 절의 기본 영역이 "RANGE UNBOUNDED PRECEDING AND CURRENT ROW" 이기 때문입니다.

 

FIRST_VALUE 와 같이 모든 데이터를 지정하려면 다음과 같이 명령어를 기재하시면 됩니다.

SELECT

        SEQ, PRICE, PROD_NAME,

        FIRST_VALUE(PROD_NAME) OVER (ORDER BY PRICE ASC) AS MIN_PRICE_PROD_NAME,

        LAST_VALUE(PROD_NAME) OVER (ORDER BY PRICE ASC

        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS MAX_PRICE_PROD_NAME

FROM

        dbo.T_FIRST_LAST

ORDER BY

        SEQ

;

 

OVER 절에서 영역을 지정하는 주요 단위는 다음과 같습니다.

1) BETWEEN <window frame bound > AND <window frame bound > : ROWS 또는 RANGE와 함께 사용되어 창의 하한(시작) 및 상한(끝) 지점을 지정합니다.<window frame bound>는 경계 시작 지점을 정의하고 <window frame bound>는 경계 끝 지점을 정의합니다.상한은 하한보다 작을 수 없습니다.

2) UNBOUNDED PRECEDING : 창이 파티션의 첫 번째 행에서 시작되도록 지정합니다.UNBOUNDED PRECEDING은 창 시작 지점으로만 지정할 수 있습니다.

3) CURRENT ROW : 창이 현재 행(ROWS와 함께 사용될 경우) 또는 현재 값(RANGE와 함께 사용될 경우)에서 시작되거나 끝나도록 지정합니다.CURRENT ROW는 시작 지점 및 끝 지점 모두로 지정할 수 있습니다.

4) UNBOUNDED FOLLOWING : 창이 파티션의 마지막 행에서 끝나도록 지정합니다.UNBOUNDED FOLLOWING은 창 끝 지점으로만 지정할 수 있습니다.예를 들어 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING은 현재 행에서 시작하고 파티션의 마지막 행에서 끝나는 창을 정의합니다.

이 외에도 PARTITION BY 구문을 이용해서 더 많은 응용이 가능합니다.

 

더 자세한 OVER 부분은 아래 URL을 참고해 주세요.

http://msdn.microsoft.com/ko-kr/library/ms189461.aspx

 

FIRST_VALUE, LAST_VALUE MSDN

http://msdn.microsoft.com/ko-kr/library/hh213018.aspx

http://msdn.microsoft.com/ko-kr/library/hh231517.aspx

 

또한 FIRST_VALUE, LAST_VALUE OVER 절과 PARTITON BY 에 대한 글은 아래 참조.

http://blog.sqlauthority.com/2011/11/09/sql-server-introduction-to-first-_value-and-last_value-analytic-functions-introduced-in-sql-server-2012/

http://www.mssqltips.com/sqlservertip/2640/sql-server-2012-functions--firstvalue-and-lastvalue/