OVER 절(Transact-SQL)

아티클
12/15/2008

업데이트: 2006년 7월 17일

관련 창 함수를 적용하기 전에 행 집합의 분할과 순서를 결정합니다.

적용 대상

순위 창 함수

집계 창 함수. 자세한 내용은 집계 함수(Transact-SQL)를 참조하십시오.

구문

Ranking Window Functions 
<OVER_CLAUSE> :: =
    OVER ( [ PARTITION BY value_expression , ... [ n ] ]
           <ORDER BY_Clause> )

Aggregate Window Functions 
<OVER_CLAUSE> :: = 
    OVER ( [ PARTITION BY value_expression , ... [ n ] ] )

인수

PARTITION BY
결과 집합을 파티션으로 분할합니다. 창 함수는 각 파티션에 별도로 적용되므로 각 파티션에 대해 계산이 다시 시작됩니다.

value_expression
해당 FROM 절에서 생성되는 행 집합이 분할되는 열을 지정합니다. <value_expression>은 FROM 절에서 사용할 수 있게 만든 열만 참조할 수 있습니다. SELECT 목록에 있는 식 또는 별칭은 참조할 수 없습니다. <value_expression>은 열 식, 스칼라 하위 쿼리, 스칼라 함수 또는 사용자 정의 변수일 수 있습니다.

<ORDER BY 절>
순위 창 함수를 적용할 순서를 지정합니다. 자세한 내용은 ORDER BY 절(Transact-SQL)를 참조하십시오.

중요:
순위 창 함수의 컨텍스트에서 사용할 경우 <ORDER BY 절>은 FROM 절에서 사용할 수 있게 만든 열만 참조할 수 있습니다. SELECT 목록에 있는 열의 이름이나 별칭의 위치를 나타내는 데 정수를 지정할 수 없습니다. <ORDER BY 절>은 집계 창 함수와 함께 사용할 수 없습니다.

주의

창 함수는 ISO SQL 표준에 정의되어 있습니다. SQL Server에서는 순위 및 집계 창 함수를 제공합니다. 창은 사용자 지정 행 집합입니다. 창 함수는 창에서 파생된 결과 집합에 있는 각 행의 값을 계산합니다.

한 쿼리의 한 FROM 절 안에서 순위 또는 집계 창 함수를 둘 이상 사용할 수 있습니다. 그러나 각 함수의 OVER 절은 분할 및 순서에서 달라질 수 있습니다. OVER 절은 CHECKSUM 집계 함수와 함께 사용할 수 없습니다.

예

1. OVER 절에 ROW_NUMBER 함수 사용

각 순위 함수인 ROW_NUMBER, DENSE_RANK, RANK, NTILE는 OVER 절을 사용합니다. 다음 예에서는 OVER 절에 ROW_NUMBER를 사용하는 방법을 보여 줍니다.

USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
    ,ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number'
    ,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s 
    INNER JOIN Person.Contact c 
        ON s.SalesPersonID = c.ContactID
    INNER JOIN Person.Address a 
        ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL 
    AND SalesYTD <> 0;
GO

2. OVER 절에 집계 함수 사용

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

USE AdventureWorks;
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 AdventureWorks;
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.43
43664	773	1	14	7.14
43664	778	1	14	7.14

참고 항목

참조

순위 함수(Transact-SQL)
집계 함수(Transact-SQL)

도움말 및 정보

SQL Server 2005 지원 받기

변경 내역

릴리스	내역
2006년 7월 17일	새로운 내용 예를 추가했습니다.

다음을 통해 공유