Group by 후에 min, max 값을 가져오는 경우도 있지만 집계레벨과 상관 없이 묶인 대상중에 첫번째 대상에 값들을 가지고 와야 하는 경우도 있다.
아래와 같은 데이터가 있다고 한다면
select col1,min(col2) from table group by col
형태로 데이터를 가지고 온 다음 col2 에 해당하는 col3,col4를 가지고 와야 한다면 min, max 함수로는 가져 올 수 없다.
이럴 때 FIRST_VALUE 함수를 사용해서 가져 오는 것이 가능 하다.
select col1, max(col3), max(col4)
from (
select
col1,
FIRST_VALUE(col3) over(partition by col1 order by col2) as col3,
FIRST_VALUE(col4) over(partition by col1 order by col2) as col4
from table
) as a group by col1
USE AdventureWorks2012;
GO
SELECT JobTitle, LastName, VacationHours,
FIRST_VALUE(LastName) OVER (PARTITION BY JobTitle
ORDER BY VacationHours ASC
ROWS UNBOUNDED PRECEDING
) AS FewestVacationHours
FROM HumanResources.Employee AS e
INNER JOIN Person.Person AS p
ON e.BusinessEntityID = p.BusinessEntityID
ORDER BY JobTitle;
1. ROW, RANGE
ROW는 다음에 나올 BETWEEN ~ AND ~에서 현재 row 기준 앞뒤로 몇 개의 row를 참조하여 SUM 함수등의 함수를 적용할지를 나타냅니다.
RANGE는 다음에 나올 BETWEEN ~ AND ~에서 현재 row의 값 기준으로 얼마만큼의 값 범위인 값들을 참조하여 SUM 함수 등의 함수를 적용할지를 나타냅니다.
2. UNBOUNDED PRECEDING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 UNBOUNDED PRECEDING을 명시하면 현재 row 기준으로 현재 row보다 더 위쪽에 있는 모든 row를 참조한다는 의미입니다.
3. UNBOUNDED FOLLOWING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 UNBOUNDED FOLLOWING을 명시하면 현재 row 기준으로 현재 row보다 더 아래쪽에 있는 모든 row를 참조한다는 의미입니다.
4. CURRENT ROW
ROW 옵션을 명시했을 때 BETWEEN a AND b 절의 a 부분에 CURRENT ROW을 명시하면 현재 row를 시작점으로 간주하라는 뜻입니다.
ROW 옵션을 명시했을 때 BETWEEN a AND b 절의 b부분에 CURRENT ROW을 명시하면 현재 row를 끝점으로 간주하라는 뜻입니다.
5. value_expr PRECEDING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 value_expr PRECEDING을 명시하면 현재 row 기준으로 현재 row보다 value_expr만큼 위쪽에 있는 row 까지를 참조한다는 의미입니다.
6. value_expr FOLLOWING
ROW 옵션을 명시했을 때 BETWEEN ~ AND ~ 절에 value_expr FOLLOWING을 명시하면 현재 row 기준으로 현재 row보다 value_expr만큼 아래쪽에 있는 row 까지를 참조한다는 의미입니다.
SELECT product_no, category_no, price,
SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS sum_1,
SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS sum_2,
SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS sum_3,
SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS sum_4,
SUM() OVER(ORDER BY category_no, product_no ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING) AS sum_5
FROM products
