OVER 절은 SQL Server 2005의 SQL Server “뒤로”에 추가되었으며 SQL에서 확장되었습니다. Server 2012. 주로 “창 기능”과 함께 사용됩니다. 유일한 예외는 시퀀스 함수 NEXT VALUE FOR입니다. OVER 절은 쿼리의 어떤 행이 함수에 적용되는지, 해당 함수에서 평가되는 순서 및 함수의 계산을 다시 시작해야하는시기를 결정하는 데 사용됩니다. 다른 함수와 함께 사용되며이 기사는 구체적으로 OVER 절에 관한 것이므로이 함수는 주어진 예에서 OVER 절과 관련된 경우에만 설명됩니다.

OVER 절은 다음과 같습니다.

<function> OVER ( )

구문을 보면 모든 하위 절이 선택 사항 인 것으로 보입니다. 실제로 OVER 절을 사용할 수있는 각 함수는 허용되는 하위 절과 필수 절을 결정합니다. 사용중인 함수에 따라 OVER 절 자체는 선택 사항 일 수 있습니다. 이 기사의 끝에는 어떤 함수가 OVER 절의 어느 부분을 허용 / 요구하는지 보여주는 차트가 있습니다.

PARTITION BY 절은 쿼리의 결과 집합을 데이터 하위 집합으로 나누는 데 사용됩니다. 파티션. PARTITION BY 절이 사용되지 않으면 쿼리의 전체 결과 집합이 사용될 파티션입니다. 사용중인 윈도우 함수는 각 파티션에 개별적으로 적용되며 함수가 수행하는 계산은 각 파티션에 대해 다시 시작됩니다. 쿼리를 나눌 파티션을 결정하는 값 집합을 정의합니다. 이러한 값은 열, 스칼라 함수, 스칼라 하위 쿼리 또는 변수 일 수 있습니다.

예를 들어 다음 쿼리를 살펴 보겠습니다.

SELECT COUNT(*)FROM .sys.indexes;

이 쿼리는 다음 결과 집합을 반환합니다.

이것은 단순히 쿼리에서 반환 한 행 수입니다.이 경우에는 msdb 데이터베이스의 인덱스 수 이제이 쿼리에 OVER 절을 추가해 보겠습니다.

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

요약 된 결과는 다음과 같습니다.

이 쿼리는 각 인덱스에 대한 object_id 및 index_id와 결과 집합의 총 인덱스 수를 반환합니다. PARTITION BY 절이 사용되지 않았으므로 전체 결과 집합이 단일 파티션으로 처리되었습니다. 이제 PARTITION BY 절을 추가하고 이로 인해 결과가 어떻게 변경되는지 확인할 차례입니다.

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

요약 된 결과는 다음과 같습니다.

이 쿼리는 각 인덱스에 대한 행을 반환하지만 이제 쿼리는 object_id 열의 PARTITION BY 절을 지정하므로 count 함수는 인덱스 수를 반환합니다. 특정 object_id에. ORDER BY 절은 함수가 행을 평가하는 순서를 제어합니다. 이것은 곧 시연 될 것입니다. ROWS 또는 RANGE 절은 함수에 적용 할 파티션 내 행의 하위 집합을 결정합니다. ROWS 또는 RANGE를 사용할 때 창의 시작 지점과 끝 지점을 지정합니다. 허용되는 값은 다음과 같습니다.

창을 지정하는 두 가지 구문이 있습니다.

BETWEEN <beginning frame> AND <ending frame><beginning frame>

시작 프레임 만 지정된 경우 기본 종료 프레임은 CURRENT ROW입니다.

UNBOUNDED 키워드는 파티션의 시작 (PRECEDING의 경우) 또는 끝을 지정합니다. 파티션 (FOLLOWING 용). CURRENT ROW는 사용되는 창 프레임 위치에 따라 현재 행이 창의 시작 또는 창의 끝임을 지정합니다. “N”은 현재 행 이전의 행 수를 지정합니다 (PRECEDING의 경우). ) 또는 창 프레임에 사용할 현재 행 (FOLLOWING의 경우) 뒤에 있습니다.

다음은 유효한 창 사양입니다.

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

ROWS 또는 RANGE 절을 사용하려면 ORDER BY 절도 지정해야합니다. 반대로 ORDER BY 절을 사용하고 ROWS 또는 RANGE 절을 지정하지 않은 경우 기본 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW가 사용됩니다.

ORDER BY 및 ROWS 또는 RANGE 절을 보여주기 위해 몇 가지 테스트 데이터 (계정 2 개, 계정 당 4 개 날짜 및 각 날짜에 대한 금액)를 만들어 보겠습니다. 쿼리는이 두 가지를 모두 표시합니다. 다른 방식으로 사용되는 절 :

이 쿼리는 다음 결과 집합을 반환합니다.

The ” RowNbr”열이 사용됩니다. g COUNT 함수를 사용하여 파티션에있는 행 수를 반환합니다. 파티션은 TranDate로 정렬되며 파티션의 시작부터 현재 행까지 모든 행의 창 프레임을 지정합니다. 첫 번째 행의 경우 창 프레임에 행이 하나만 있으므로 값 “1”이 반환됩니다. 두 번째 행의 경우 창 프레임에 두 개의 행이 있으므로 값 “2”가 반환됩니다. 이 계정의 나머지 행을 통해 계속됩니다.

PARTITION BY 절이 계정을 지정하기 때문에 계정이 변경되면 함수 계산이 재설정되며 결과 집합에서 두 번째 계정에 대한 행을 검사하여 확인할 수 있습니다. 이것은 집계가 이전 계산을 기반으로 구축되는 “실행중인”집계의 예입니다.이를 사용하는 경우의 예는 각 거래 후 은행 계좌 잔액을 계산할 때 (또는 누계라고도 함)입니다.

“DateCount”열은 날짜별로 분할 된 행 수를 계산합니다. 이 예에서 각 계정에는 동일한 4 개의 날짜에 대한 트랜잭션이 있으므로 각 날짜에는 2 개의 트랜잭션 (각 계정에 대해 하나씩)이 있습니다. 결과적으로 각 행에 대해 값 “2”가 반환됩니다. 이는 날짜에 대해 GROUP BY를 사용하는 계산을 수행하는 것과 유사합니다. 차이점은 각 날짜에 대해 한 번만 반환되는 것이 아니라 각 행에 대해 합계가 반환된다는 점입니다. 예 이 방법을 사용하면 “Y의 행 X”를 표시하거나 합계에 대한 현재 행의 백분율을 계산할 수 있습니다.

“Last2Count”열은 행 수를 계산합니다. 파티션 내에서 현재 행과 바로 앞의 행에 대해. 각 계정의 첫 번째 행에 대해 앞에 행이 없으므로 “1”값이 반환됩니다. 각 계정의 나머지 행에 대해 “2”값이 반환됩니다. 이것은 “이동”또는 “슬라이딩”집계의 예입니다.이 방법을 사용하는 경우의 예는 다음에 따라 보너스를 계산하는 것입니다. 지난 2 개월 동안의 판매.

이 시점에서는 ROWS 절만 표시했습니다. RANGE 절은 비슷한 방식으로 작동하지만 위치 방식으로 행을 처리하는 대신 N 개의 PRECEDING / FOLLOWING 절을 사용할 수없는 것은 위치가 아니기 때문입니다. ROWS와 RANGE의 차이점을 동일한 쿼리에서 모두 사용하여 간단히 살펴 보겠습니다. 다음은 사람 목록 (DBA라고합시다) 및 시간당 요금. RowID가 4 & 5 및 12 인 행 & 13의 비율이 동일합니다. 쿼리는 ROWS를 사용하고 다른 하나는 RANGE를 사용하여 비율을 두 번 합산합니다.

이 쿼리는 다음 결과 집합을 생성합니다.

SumByRows 및 SumByRange 열에서 OVER 절은 ROWS / RANGE 절을 제외하고는 동일합니다. 끝 범위가 지정되지 않았으므로 기본값은 CURRENT ROW를 사용하는 것입니다. 현재 행을 통해 결과 집합의 시작부터 급여를 합산하므로 실제로 계산하는 것은 급여 열의 누계입니다. SumByRows 열에서 값은 ROWS 절을 사용하여 계산되며 현재 행의 합계가 현재 행의 Salary에 이전 행의 합계를 더한 것을 볼 수 있습니다. 그러나 RANGE 절은 Salary 열의 값에서 작동하므로 급여가 같거나 낮은 모든 행을 합산합니다. 결과적으로 SumByRange 값은 Salary가 동일한 모든 행에 대해 동일한 값이됩니다.

한 가지 중요한 참고 사항 : OVER 절의 ORDER BY 절은 파티션의 행이 활용되는 순서 만 제어합니다. 창 기능으로. 최종 결과 집합의 순서는 제어하지 않습니다. 쿼리 자체에 ORDER BY 절이 없으면 행의 순서가 보장되지 않습니다. 마지막으로 지정된 OVER 절의 순서로 쿼리가 반환 될 수 있습니다. 이는 현재 SQL Server에서 구현 된 방식 때문입니다. Microsoft의 SQL Server 팀이 작동 방식을 변경하면 더 이상 현재 관찰중인 방식으로 결과를 정렬하지 않을 수 있습니다. 결과 집합에 대한 특정 순서가 필요한 경우 쿼리 자체에 대해 ORDER BY 절을 제공해야합니다.

마지막으로 다음은 OVER 절을 사용할 수있는 다양한 함수의 차트입니다. 절의 어떤 부분이 허용 / 필수 / 선택 사항인지.

R- 필수, O- 선택, X- 허용되지 않음