SELECT-OVER 절 (Transact-SQL)

• 2017 년 8 월 11 일
• 읽는 데 17 분
• • V
  • L
  • c
  • j
  • M
  • +11

적용 대상 : SQL Server (모두 지원되는 버전) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics 병렬 데이터웨어 하우스

관련 창 함수가 적용되기 전에 행 집합의 분할 및 순서를 결정합니다. 즉, OVER 절은 쿼리 결과 집합 내에서 창 또는 사용자 지정 행 집합을 정의합니다. 그런 다음 창 함수는 창의 각 행에 대한 값을 계산합니다. OVER 절을 함수와 함께 사용하여 이동 평균, 누적 집계, 누적 합계 또는 그룹당 상위 N 개 결과와 같은 집계 값을 계산할 수 있습니다.

• 순위 함수

• 집계 함수

• 분석 함수

• 함수에 대한 다음 값

Transact-SQL 구문 규칙

구문

인수

창 함수는 OVER 절에 다음 인수를 가질 수 있습니다.

• 쿼리 결과 집합을 파티션으로 나누는 PARTITION BY.
• 결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의하는 ORDER BY.
• 파티션 내에서 시작 및 끝 지점을 지정하여 파티션 내 행을 제한하는 ROWS / RANGE. ORDER BY 인수가 필요하며 ORDER BY 인수가 지정된 경우 기본값은 파티션의 시작부터 현재 요소까지입니다.

인수를 지정하지 않으면 창 함수가 전체 결과 집합에 적용됩니다.

object_id	min	최대
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

쿼리 결과 집합을 파티션으로 나눕니다. 창 함수는 각 파티션에 개별적으로 적용되고 각 파티션에 대해 계산이 다시 시작됩니다. 이온.

PARTITION BY가 지정되지 않은 경우 함수는 쿼리 결과 집합의 모든 행을 단일 파티션으로 처리합니다. 함수는 모든 행에 적용됩니다. ORDER BY 절을 지정하지 않은 경우 파티션에서.

PARTITION BY value_expression

행 집합이 분할되는 열을 지정합니다. value_expression은 FROM 절에서 사용할 수있는 열만 참조 할 수 있습니다. value_expression은 선택 목록의 표현식 또는 별칭을 참조 할 수 없습니다. value_expression은 열 표현식, 스칼라 하위 쿼리, 스칼라 함수 또는 사용자 정의 변수 일 수 있습니다.

ORDER BY

결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의합니다. 즉, 윈도우 함수 계산이 수행되는 논리적 순서를 지정합니다.

• 지정되지 않은 경우 기본 순서는 ASC이며 window 함수는 파티션의 모든 행을 사용합니다.
• 지정되고 ROWS / RANGE가 지정되지 않은 경우 기본 RANGE UNBOUNDED PRECEDING AND CURRENT ROW가 창에 대한 기본값으로 사용됩니다. 선택적 ROWS / RANGE 사양 (예 : min 또는 max)을 허용 할 수있는 함수 별 프레임.

order_by_expression
정렬 할 열 또는 표현식을 지정합니다. order_by_expression은 FROM 절에서 사용 가능한 열만 참조 할 수 있습니다. 열 이름이나 별칭을 나타 내기 위해 정수를 지정할 수 없습니다.

COLLATE collation_name
collation_name에 지정된 데이터 정렬에 따라 ORDER BY 작업을 수행하도록 지정합니다. collation_name은 Windows 데이터 정렬 이름 또는 SQL 데이터 정렬 이름 일 수 있습니다. 자세한 내용은 데이터 정렬 및 유니 코드 지원을 참조하십시오. COLLATE는 char, varchar, nchar 및 nvarchar 유형의 열에 만 적용됩니다.

ASC | DESC
지정된 열의 값이 오름차순 또는 내림차순으로 정렬되도록 지정합니다. ASC는 기본 정렬 순서입니다.Null 값은 가능한 가장 낮은 값으로 처리됩니다.

ROWS 또는 RANGE

적용 대상 : SQL Server 2012 (11.x) 이상

추가 제한 파티션 내에서 시작 및 종료 지점을 지정하여 파티션 내 행. 이는 논리적 연관 또는 물리적 연관에 의해 현재 행에 대한 행 범위를 지정하여 수행됩니다. 물리적 연결은 ROWS 절을 사용하여 이루어집니다.

ROWS 절은 현재 행 앞 또는 뒤에 고정 된 행 수를 지정하여 파티션 내의 행을 제한합니다. 또는 RANGE 절은 현재 행의 값과 관련하여 값 범위를 지정하여 파티션 내의 행을 논리적으로 제한합니다. 선행 및 다음 행은 ORDER BY 절의 순서에 따라 정의됩니다. 창 프레임 “RANGE … CURRENT ROW …”에는 ORDER BY 표현식에서 현재 행과 동일한 값을 가진 모든 행이 포함됩니다. 예를 들어 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW는 함수가 작동하는 행 창 크기가 현재 행을 포함하여 앞선 2 행부터 시작하여 3 행임을 의미합니다.

UNBOUNDED PRECEDING

적용 대상 : SQL Server 2012 (11.x) 이상.

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

< 부호없는 값 사양 > PRECEDING
지정됨 < 부호없는 값 사양 >을 사용하여 현재 행 이전의 행 또는 값 수를 나타냅니다. 이 사양은 RANGE에 허용되지 않습니다.

CURRENT ROW

적용 대상 : SQL Server 2012 (11.x) 이상

창이 시작되도록 지정합니다. 또는 ROWS와 함께 사용하면 현재 행에서 끝나고 RANGE와 함께 사용하면 현재 값에서 끝납니다. CURRENT ROW는 시작점과 끝점 모두로 지정할 수 있습니다.

BETWEEN AND

적용 대상 : SQL Server 2012 (11.x) 이상

ROWS 또는 RANGE와 함께 사용하여 창의 하한 (시작) 및 상한 (끝) 경계 지점을 지정합니다. < 창 프레임 경계 >는 경계 시작점을 정의하고 < 창 프레임 경계 >는 경계 끝점을 정의합니다. 상한은 하한보다 작을 수 없습니다.

UNBOUNDED FOLLOWING

적용 대상 : SQL Server 2012 (11.x) 이상

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

< 부호없는 값 사양 > FOLLOWING
< 부호없는 값 사양 >로 지정되어 현재 행 다음에 오는 행 또는 값. < 부호없는 값 사양 > FOLLOWING이 창 시작 지점으로 지정된 경우 종료 지점은 < 부호없는 값 사양 > FOLLOWING. 예를 들어 ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING은 현재 행 다음에 오는 두 번째 행에서 시작하고 현재 행 뒤에 오는 열 번째 행으로 끝나는 창을 정의합니다. 이 사양은 RANGE에 허용되지 않습니다.

부호없는 정수 리터럴
적용 대상 : SQL Server 2012 (11.x) 이상

양의 정수 리터럴 (0 포함) )는 현재 행 또는 값의 앞이나 뒤에 올 행 또는 값의 수를 지정합니다. 이 사양은 ROWS에 대해서만 유효합니다.

일반 사항

하나의 FROM 절이있는 단일 쿼리에서 둘 이상의 창 함수를 사용할 수 있습니다. 각 함수의 OVER 절은 분할 및 순서가 다를 수 있습니다.

PARTITION BY가 지정되지 않은 경우 함수는 쿼리 결과 집합의 모든 행을 단일 그룹으로 취급합니다.

중요 !

ROWS / RANGE가 지정되고 < 앞의 창 프레임 >이 창 프레임 범위 > (짧은 구문) 그러면이 사양이 창 프레임 경계 시작점으로 사용되고 CURRENT ROW가 경계 끝점으로 사용됩니다. 예를 들어 “ROWS 5 PRECEDING”은 “ROWS BETWEEN 5 PRECEDING AND CURRENT ROW”와 같습니다.

제한 및 제한 사항

OVER 절은 CHECKSUM 집계 함수와 함께 사용할 수 없습니다.

RANGE는 < 부호없는 값 사양 > PRECEDING 또는 < 부호없는 값 사양 > FOLLOWING.

순위, 집계 또는 분석에 따라 다름 OVER 절, < ORDER BY 절 > 및 / 또는 <와 함께 사용되는 함수 ROWS 및 RANGE 절 >은 지원되지 않을 수 있습니다.

예

A. ROW_NUMBER 함수와 함께 OVER 절 사용

다음 예제는 파티션 내의 각 행에 대한 행 번호를 표시하기 위해 ROW_NUMBER 함수와 함께 OVER 절을 사용하는 방법을 보여줍니다. OVER 절에 지정된 ORDER BY 절은 SalesYTD 열을 기준으로 각 파티션의 행을 정렬합니다. SELECT 문의 ORDER BY 절은 전체 쿼리 결과 집합이 반환되는 순서를 결정합니다.

결과 집합은 다음과 같습니다.

B. 집계 함수와 함께 OVER 절 사용

다음 예에서는 쿼리에서 반환 된 모든 행에 대해 집계 함수와 함께 OVER 절을 사용합니다. 이 예에서는 OVER 절을 사용하는 것이 하위 쿼리를 사용하여 집계 값을 파생하는 것보다 더 효율적입니다.

결과 집합은 다음과 같습니다.

다음 예제는 계산 된 값에서 집계 함수와 함께 OVER 절을 사용하는 것을 보여줍니다.

다음은 결과 집합입니다. 집계는 SalesOrderID에 의해 계산되고 Percent by ProductID는 각 SalesOrderID.

C. 이동 평균 및 누적 합계 생성

다음 예제에서는 OVER 절과 함께 AVG 및 SUM 함수를 사용하여 테이블. 데이터는 TerritoryID로 분할되고 논리적으로 SalesYTD로 정렬됩니다. 즉, 판매 연도를 기준으로 각 지역에 대해 AVG 함수가 계산됩니다. TerritoryID 1의 경우 2005 년 매출이 발생한 두 명의 영업 사원을 나타내는 두 개의 행이 2005 년에 있습니다. 이 두 행의 평균 매출이 계산 된 다음 2006 년 매출을 나타내는 세 번째 행이 계산에 포함됩니다.

다음은 결과 집합입니다.

이 예에서 OVER 절에는 PARTITION BY가 포함되지 않습니다. 이는 함수가 쿼리에서 반환 된 모든 행에 적용됨을 의미합니다. OVER 절에 지정된 ORDER BY 절은 AVG 함수가 적용되는 논리적 순서를 결정합니다. 쿼리는 WHERE 절에 지정된 모든 판매 지역에 대한 연도 별 판매 이동 평균을 반환합니다. SELECT 문에 지정된 ORDER BY 절은 쿼리 행이 표시되는 순서를 결정합니다.

결과 집합은 다음과 같습니다.

D. ROWS 절 지정

적용 대상 : SQL Server 2012 (11.x) 이상

다음 예제에서는 ROWS 절을 사용하여 행이 다음과 같이 계산되는 창을 정의합니다. 현재 행과 그 뒤에 오는 N 개의 행 (이 예에서는 1 개 행)

결과 집합은 다음과 같습니다.

다음 예에서 ROWS 절은 UNBOUNDED PRECEDING으로 지정됩니다. 그 결과 창이 파티션의 첫 번째 행에서 시작됩니다.

결과 집합은 다음과 같습니다.

예 : 병렬 데이터웨어 하우스

E. ROW_NUMBER 함수와 함께 OVER 절 사용

다음 예제는 할당 된 판매 할당량을 기준으로 영업 담당자에 대한 ROW_NUMBER를 반환합니다.

다음은 일부 결과 집합입니다.

F. 집계 함수와 함께 OVER 절 사용

다음 예에서는 집계 함수와 함께 OVER 절을 사용하는 방법을 보여줍니다. 이 예에서는 OVER 절을 사용하는 것이 하위 쿼리를 사용하는 것보다 더 효율적입니다.

다음은 결과 집합입니다.

다음 예 계산 된 값에서 집계 함수와 함께 OVER 절을 사용하는 방법을 보여줍니다. 집계는 SalesOrderNumber에 의해 계산되고 총 판매 주문의 백분율은 각 SalesOrderNumber의 각 줄에 대해 계산됩니다.

이 결과 집합의 첫 번째 시작은 다음과 같습니다.

참고 항목

집계 함수 (Transact-SQL)
분석 함수 (Transact-SQL)
Itzik Ben-Gan의 sqlmag.com에서 창 기능 및 OVER에 대한 우수한 블로그 게시물