출처
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을 참고해 주세요.
FIRST_VALUE, LAST_VALUE MSDN
또한 FIRST_VALUE, LAST_VALUE OVER 절과 PARTITON BY 에 대한 글은 아래 참조.
