SELECT - OVER 절(Transact-SQL)
적용 대상: 
SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System(PDW) Microsoft Fabric의 SQL 분석 엔드포인트 Microsoft Fabric의 웨어하우스
이 절은 OVER 연결된 창 함수가 적용되기 전에 행 집합의 분할 및 순서를 결정합니다. 즉, 이 절은 OVER 쿼리 결과 집합 내에서 창 또는 사용자가 지정한 행 집합을 정의합니다. 그런 다음 창 함수가 창의 각 행에 대한 값을 계산합니다. 함수와 함께 절을 OVER 사용하여 이동 평균, 누적 집계, 실행 합계 또는 그룹별 상위 N 과 같은 집계 값을 계산할 수 있습니다.
구문
SQL Server, Azure SQL Database 및 Azure Synapse Analytics에 대한 구문입니다.
OVER (
[ <PARTITION BY clause> ]
[ <ORDER BY clause> ]
[ <ROW or RANGE clause> ]
)
<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]
<ORDER BY clause> ::=
ORDER BY order_by_expression
[ COLLATE collation_name ]
[ ASC | DESC ]
[ , ...n ]
<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>
<window frame extent> ::=
{ <window frame preceding>
| <window frame between>
}
<window frame between> ::=
BETWEEN <window frame bound> AND <window frame bound>
<window frame bound> ::=
{ <window frame preceding>
| <window frame following>
}
<window frame preceding> ::=
{
UNBOUNDED PRECEDING
| <unsigned_value_specification> PRECEDING
| CURRENT ROW
}
<window frame following> ::=
{
UNBOUNDED FOLLOWING
| <unsigned_value_specification> FOLLOWING
| CURRENT ROW
}
<unsigned value specification> ::=
{ <unsigned integer literal> }
병렬 데이터 웨어하우스용 구문
OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )
인수
창 함수는 OVER 절에 다음 인수를 사용할 수 있습니다.
PARTITION BY - 쿼리 결과 집합을 파티션으로 분할합니다.
ORDER BY - 결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의합니다.
파티션 내에서 시작점과 끝점을 지정하여 파티션 내의 행을 제한하는 ROWS 또는 RANGE 입니다.
ORDER BY인수가 필요하며,ORDER BY인수가 지정된 경우 기본값은 파티션 시작부터 현재 요소까지입니다.
인수를 지정하지 않으면 창 함수가 전체 결과 집합에 적용됩니다.
SELECT object_id,
MIN(object_id) OVER () AS [min],
MAX(object_id) OVER () AS [max]
FROM sys.objects;
| object_id | 분 | max |
|---|---|---|
| 3 | 3 | 2139154666 |
| 5 | 3 | 2139154666 |
| ... | ... | ... |
| 2123154609 | 3 | 2139154666 |
| 2139154666 | 3 | 2139154666 |
PARTITION BY
쿼리 결과 집합을 파티션으로 분할합니다. 창 함수는 각 파티션에 별도로 적용되므로 각 파티션에 대해 계산이 다시 시작됩니다.
PARTITION BY <value_expression>
지정하지 않으면 PARTITION BY 함수는 쿼리 결과 집합의 모든 행을 단일 파티션으로 처리합니다.
절을 지정 ORDER BY 하지 않으면 파티션의 모든 행에 함수가 적용됩니다.
PARTITION BY value_expression
행 집합을 분할하는 데 사용하는 열을 지정합니다. value_expression 절에서 사용할 수 있는 열만 참조할 FROM 수 있습니다. value_expression 선택 목록에서 식이나 별칭을 참조할 수 없습니다. value_expression은 열 식, 스칼라 하위 쿼리, 스칼라 함수 또는 사용자 정의 변수일 수 있습니다.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type) AS [min],
MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
| object_id | 형식 | 분 | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 711673583 |
| 631673298 | PK | 68195293 | 711673583 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | ... |
| 3 | S | 3 | 98 |
| 5 | S | 3 | 98 |
| ... | ... | ... | ... |
| 98 | S | 3 | 98 |
| ... | ... | ... | ... |
ORDER BY
ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]
결과 집합의 각 파티션 내에서 행의 논리적 순서를 정의합니다. 즉, 창 함수 계산이 수행되는 논리적 순서를 지정합니다.
지정하지 않으면 기본 순서가 지정되고
ASC창 함수는 파티션의 모든 행을 사용합니다.지정되거나
ROWSRANGE지정되지 않은 경우 기본값RANGE UNBOUNDED PRECEDING AND CURRENT ROW은 선택적ROWS또는RANGE사양(예min: 또는max)을 수락할 수 있는 함수에 의해 창 프레임의 기본값으로 사용됩니다.
SELECT object_id,
type,
MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
| object_id | 형식 | 분 | max |
|---|---|---|---|
| 68195293 | PK | 68195293 | 68195293 |
| 631673298 | PK | 68195293 | 631673298 |
| 711673583 | PK | 68195293 | 711673583 |
| ... | ... | ... | |
| 3 | S | 3 | 3 |
| 5 | S | 3 | 5 |
| 6 | S | 3 | 6 |
| ... | ... | ... | |
| 97 | S | 3 | 97 |
| 98 | S | 3 | 98 |
| ... | ... | ... |
order_by_expression
정렬할 열 또는 식을 지정합니다. order_by_expression 절에서 사용할 수 있는 열만 참조할 FROM 수 있습니다. 열 이름 또는 별칭을 나타내도록 정수는 지정할 수 없습니다.
COLLATE collation_name
ORDER BY collation_name 지정된 데이터 정렬에 따라 작업을 수행하도록 지정합니다. 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개의 행 크기가 있음을 의미합니다.
SELECT object_id,
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
| object_id | preceding | central | following |
|---|---|---|---|
| 3 | 1 | 3 | 156 |
| 5 | 2 | 4 | 155 |
| 6 | 3 | 5 | 154 |
| 7 | 4 | 5 | 153 |
| 8 | 5 | 5 | 152 |
| ... | ... | ... | ... |
| 2112726579 | 153 | 5 | 4 |
| 2119678599 | 154 | 5 | 3 |
| 2123154609 | 155 | 4 | 2 |
| 2139154666 | 156 | 3 | 1 |
ROWS 또는 RANGE 절을 지정해야 합니다 ORDER BY . 여러 순서 식이 CURRENT ROW FOR RANGE 포함된 경우 ORDER BY 현재 행을 결정할 때 목록의 ORDER BY 모든 열을 고려합니다.
UNBOUNDED PRECEDING
적용 대상: SQL Server 2012(11.x) 이상 버전.
창이 파티션의 첫 번째 행에서 시작되도록 지정합니다. UNBOUNDED PRECEDING 는 창 시작 지점으로만 지정할 수 있습니다.
<unsigned value specification> PRECEDING
<unsigned value specification> 현재 행 앞에 올 행 또는 값의 수를 나타내기 위해 지정됩니다. 이 사양은 허용되지 RANGE않습니다.
CURRENT ROW
적용 대상: SQL Server 2012(11.x) 이상 버전.
사용할 경우 창이 현재 행에서 시작되거나 끝나거나 함께 사용될 때 현재 값으로 ROWS RANGE끝나게 지정합니다. CURRENT ROW 를 시작점과 끝점으로 지정할 수 있습니다.
BETWEEN AND
적용 대상: SQL Server 2012(11.x) 이상 버전.
BETWEEN <window frame bound> AND <window frame bound>
창의 아래쪽(시작) 및 위쪽(끝) 경계 지점을 지정하거나 RANGE 함께 ROWS 사용합니다. <window frame bound> 는 경계 시작점을 정의하고 <window frame bound> 경계 엔드포인트를 정의합니다. 상한은 하한보다 작을 수 없습니다.
UNBOUNDED FOLLOWING
적용 대상: SQL Server 2012(11.x) 이상 버전.
창이 파티션의 마지막 행에서 끝나도록 지정합니다. UNBOUNDED FOLLOWING 는 창 엔드포인트로만 지정할 수 있습니다. 예를 들어 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 현재 행으로 시작하고 파티션의 마지막 행으로 끝나는 창을 정의합니다.
<unsigned value specification> FOLLOWING
<unsigned value specification> 현재 행을 따를 행 또는 값의 수를 나타내기 위해 지정됩니다. <unsigned value specification> FOLLOWING 창 시작점으로 지정되면 끝점은 .이어야 <unsigned value specification> FOLLOWING합니다. 예를 들어 현재 ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING 행 뒤에 있는 두 번째 행으로 시작하고 현재 행 뒤에 있는 10번째 행으로 끝나는 창을 정의합니다. 이 사양은 허용되지 RANGE않습니다.
<부호 없는 정수 리터럴>
적용 대상: SQL Server 2012(11.x) 이상 버전.
현재 행 또는 값 앞에 오거나 따를 행 또는 값의 수를 지정하는 양의 정수 리터럴(포함 0)입니다. 이 사양은 .에 대해서만 유효합니다 ROWS.
설명
단일 FROM 절이 있는 단일 쿼리에서 둘 이상의 창 함수를 사용할 수 있습니다. 각 함수에 대한 절은 OVER 분할 및 순서에 따라 다를 수 있습니다.
지정하지 않으면 PARTITION BY 함수는 쿼리 결과 집합의 모든 행을 단일 그룹으로 처리합니다.
Important
지정되거나RANGE(<window frame preceding>짧은 구문)에 <window frame extent> 사용되는 경우 ROWS 이 사양은 창 프레임 경계 시작점에 사용되며 CURRENT ROW 경계 끝점에 사용됩니다. 예를 들어 . ROWS 5 PRECEDING ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
지정하지 않으면 ORDER BY 전체 파티션이 창 프레임에 사용됩니다. 절이 필요하지 ORDER BY 않은 함수에만 적용됩니다. 지정되었거나 RANGE 지정되지 않았지만 ORDER BY 지정된 RANGE UNBOUNDED PRECEDING AND CURRENT ROW 경우 ROWS 창 프레임의 기본값으로 사용됩니다. 선택적 ROWS 또는 RANGE 사양을 수락할 수 있는 함수에만 적용됩니다. 예를 들어 순위 함수는 허용하거나 수락 ROWS 할 수 없으므로 이 창 프레임은 존재하거나 RANGE ROWS 적용되지 않더라도 ORDER BY 적용되지 RANGE않습니다.
제한 사항
이 절은 OVER 집계와 함께 DISTINCT 사용할 수 없습니다.
RANGE는 <unsigned value specification> PRECEDING 또는 <unsigned value specification> FOLLOWING와 함께 사용할 수 없습니다.
절 <ORDER BY clause> 과 함께 사용되는 순위, 집계 또는 분석 함수에 OVER 따라 지원 <ROWS and RANGE clause> 되지 않을 수 있습니다.
예제
이 문서의 Transact-SQL 코드 샘플은 AdventureWorks2022 또는 AdventureWorksDW2022 샘플 데이터베이스를 사용하며, 이는 Microsoft SQL Server 예시 및 커뮤니티 프로젝트(Microsoft SQL Server Samples and Community Projects) 홈 페이지에서 다운로드할 수 있습니다.
A. ROW_NUMBER 함수와 함께 OVER 절 사용
다음 예제에서는 함수와 함께 ROW_NUMBER 절을 OVER 사용하여 파티션 내의 각 행에 대한 행 번호를 표시하는 방법을 보여 있습니다. ORDER BY 절에 지정된 OVER 절은 각 파티션의 행을 SalesYTD 열을 기준으로 정렬합니다. 문의 절은 ORDER BY SELECT 전체 쿼리 결과 집합이 반환되는 순서를 결정합니다.
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER (PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS "Row Number",
p.LastName,
s.SalesYTD,
a.PostalCode
FROM Sales.SalesPerson AS s
INNER JOIN Person.Person AS p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address AS a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY PostalCode;
GO
결과 집합은 다음과 같습니다.
Row Number LastName SalesYTD PostalCode
--------------- ----------------------- --------------------- ----------
1 Mitchell 4251368.5497 98027
2 Blythe 3763178.1787 98027
3 Carson 3189418.3662 98027
4 Reiter 2315185.611 98027
5 Vargas 1453719.4653 98027
6 Ansman-Wolfe 1352577.1325 98027
1 Pak 4116871.2277 98055
2 Varkey Chudukatil 3121616.3202 98055
3 Saraiva 2604540.7172 98055
4 Ito 2458535.6169 98055
5 Valdez 1827066.7118 98055
6 Mensa-Annan 1576562.1966 98055
7 Campbell 1573012.9383 98055
8 Tsoflias 1421810.9242 98055
B. 집계 함수와 함께 OVER 절 사용
다음 예에서는 쿼리에서 반환된 모든 행에 대해 OVER 절에 집계 함수를 사용합니다. 이 예에서는 하위 쿼리를 사용하는 것보다 OVER 절을 사용하는 것이 집계 값을 파생시키는 데 더 효율적입니다.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Avg",
COUNT(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Count",
MIN(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Min",
MAX(OrderQty) OVER (PARTITION BY SalesOrderID) AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
결과 집합은 다음과 같습니다.
SalesOrderID ProductID OrderQty Total Avg Count Min Max
------------ ----------- -------- ----------- ----------- ----------- ------ ------
43659 776 1 26 2 12 1 6
43659 777 3 26 2 12 1 6
43659 778 1 26 2 12 1 6
43659 771 1 26 2 12 1 6
43659 772 1 26 2 12 1 6
43659 773 2 26 2 12 1 6
43659 774 1 26 2 12 1 6
43659 714 3 26 2 12 1 6
43659 716 1 26 2 12 1 6
43659 709 6 26 2 12 1 6
43659 712 2 26 2 12 1 6
43659 711 4 26 2 12 1 6
43664 772 1 14 1 8 1 4
43664 775 4 14 1 8 1 4
43664 714 1 14 1 8 1 4
43664 716 1 14 1 8 1 4
43664 777 2 14 1 8 1 4
43664 771 3 14 1 8 1 4
43664 773 1 14 1 8 1 4
43664 778 1 14 1 8 1 4
다음 예에서는 OVER 절에 계산된 값의 집계 함수를 사용하는 방법을 보여 줍니다.
USE AdventureWorks2022;
GO
SELECT SalesOrderID,
ProductID,
OrderQty,
SUM(OrderQty) OVER (PARTITION BY SalesOrderID) AS Total,
CAST (1. * OrderQty / SUM(OrderQty) OVER (PARTITION BY SalesOrderID) * 100 AS DECIMAL (5, 2)) AS [Percent by ProductID]
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN (43659, 43664);
GO
결과 집합은 다음과 같습니다. 집계는 각 SalesOrderID줄에 대해 SalesOrderID 계산됩니다Percent by ProductID.
SalesOrderID ProductID OrderQty Total Percent by ProductID
------------ ----------- -------- ----------- ---------------------------------------
43659 776 1 26 3.85
43659 777 3 26 11.54
43659 778 1 26 3.85
43659 771 1 26 3.85
43659 772 1 26 3.85
43659 773 2 26 7.69
43659 774 1 26 3.85
43659 714 3 26 11.54
43659 716 1 26 3.85
43659 709 6 26 23.08
43659 712 2 26 7.69
43659 711 4 26 15.38
43664 772 1 14 7.14
43664 775 4 14 28.57
43664 714 1 14 7.14
43664 716 1 14 7.14
43664 777 2 14 14.29
43664 771 3 14 21.4
43664 773 1 14 7.14
43664 778 1 14 7.14
C. 이동 평균 및 누적 합계 생성
다음 예제에서는 절과 함께 OVER and SUM 함수를 사용하여 AVG 테이블의 각 지역에 대한 이동 평균 및 누적 연간 매출 합계를 Sales.SalesPerson 제공합니다. 데이터는 TerritoryID를 기준으로 분할되고 SalesYTD를 기준으로 논리적으로 정렬됩니다. 즉, AVG 함수는 판매 연도에 따라 각 지역에 대해 계산됩니다. 1의 경우 TerritoryID 해당 연도에 판매된 두 명의 영업 사원을 나타내는 판매 연도 2005 에 대해 두 개의 행이 있습니다. 이 두 행의 평균 판매액이 계산된 다음 연도 2006 의 매출을 나타내는 세 번째 행이 계산에 포함됩니다.
USE AdventureWorks2022;
GO
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;
결과 집합은 다음과 같습니다.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 559,697.56 559,697.56
287 NULL 2006 519,905.93 539,801.75 1,079,603.50
285 NULL 2007 172,524.45 417,375.98 1,252,127.95
283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07
280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07
284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27
275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18
277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37
276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17
281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17
이 예제에서는 절에 OVER .가 포함되지 PARTITION BY않습니다. 즉, 함수는 쿼리에서 반환된 모든 행에 적용됩니다. 절에 OVER 지정된 절은 ORDER BY 함수가 적용되는 논리적 순서를 AVG 결정합니다. 쿼리는 절에 지정된 모든 판매 지역에 대해 연간 이동 평균 판매량을 WHERE 반환합니다. 문에 SELECT 지정된 절은 ORDER BY 쿼리 행이 표시되는 순서를 결정합니다.
SELECT BusinessEntityID,
TerritoryID,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5
ORDER BY SalesYear;
결과 집합은 다음과 같습니다.
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35
275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35
276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35
277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35
280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35
281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35
283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35
284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47
287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47
285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93
D. ROWS 절 지정
적용 대상: SQL Server 2012(11.x) 이상 버전.
다음 예제에서는 절을 ROWS 사용하여 행이 현재 행으로 계산되는 창과 다음에 오는 행의 N 개수(이 예제의 한 행)를 정의합니다.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
결과 집합은 다음과 같습니다.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 1,079,603.50
287 NULL 519,905.93 2006 692,430.38
285 NULL 172,524.45 2007 172,524.45
283 1 1,573,012.94 2005 2,925,590.07
280 1 1,352,577.13 2005 2,929,139.33
284 1 1,576,562.20 2006 1,576,562.20
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 6,709,904.17
281 4 2,458,535.62 2005 2,458,535.62
다음 예제에서는 절이 ROWS .로 UNBOUNDED PRECEDING지정됩니다. 그 결과 창이 파티션의 첫 번째 행에서 시작됩니다.
SELECT BusinessEntityID,
TerritoryID,
CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
DATEPART(yy, ModifiedDate) AS SalesYear,
CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
OR TerritoryID < 5;
결과 집합은 다음과 같습니다.
BusinessEntityID TerritoryID SalesYTD SalesYear CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274 NULL 559,697.56 2005 559,697.56
287 NULL 519,905.93 2006 1,079,603.50
285 NULL 172,524.45 2007 1,252,127.95
283 1 1,573,012.94 2005 1,573,012.94
280 1 1,352,577.13 2005 2,925,590.07
284 1 1,576,562.20 2006 4,502,152.27
275 2 3,763,178.18 2005 3,763,178.18
277 3 3,189,418.37 2005 3,189,418.37
276 4 4,251,368.55 2005 4,251,368.55
281 4 2,458,535.62 2005 6,709,904.17
예제: 분석 플랫폼 시스템(PDW)
E. ROW_NUMBER 함수와 함께 OVER 절 사용
다음 예는 담당자의 판매 할당량을 기반으로 영업 담당자의 ROW_NUMBER를 반환합니다.
SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
FirstName,
LastName,
CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
INNER JOIN dbo.FactSalesQuota AS sq
ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;
다음은 결과 집합의 일부입니다.
RowNumber FirstName LastName SalesQuota
--------- --------- ------------------ -------------
1 Jillian Carson 12,198,000.00
2 Linda Mitchell 11,786,000.00
3 Michael Blythe 11,162,000.00
4 Jae Pak 10,514,000.00
F. 집계 함수와 함께 OVER 절 사용
다음 예제에서는 집계 함수와 함께 절을 OVER 사용하는 것을 보여 줍니다. 이 예제에서는 하위 쿼리를 OVER 사용하는 것보다 절을 사용하는 것이 더 효율적입니다.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
결과 집합은 다음과 같습니다.
OrderNumber Product Qty Total Avg Count Min Max
----------- ------- --- ----- --- ----- --- ---
SO43659 218 6 16 3 5 1 6
SO43659 220 4 16 3 5 1 6
SO43659 223 2 16 3 5 1 6
SO43659 229 3 16 3 5 1 6
SO43659 235 1 16 3 5 1 6
SO43664 229 1 2 1 2 1 1
SO43664 235 1 2 1 2 1 1
다음 예에서는 OVER 절에 계산된 값의 집계 함수를 사용하는 방법을 보여 줍니다. 집계는 각 줄SalesOrderNumber에 SalesOrderNumber 대해 계산되고 총 판매 주문의 백분율을 기준으로 계산됩니다.
SELECT SalesOrderNumber AS OrderNumber,
ProductKey AS Product,
OrderQuantity AS Qty,
SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;
이 결과 집합의 첫 번째 시작은 다음과 같습니다.
OrderNumber Product Qty Total PctByProduct
----------- ------- --- ----- ------------
SO43659 218 6 16 37.50
SO43659 220 4 16 25.00
SO43659 223 2 16 12.50
SO43659 229 2 16 18.75