[T-SQL] OVER절

1. OVER 절에  ROW_NUMBER함수 사용
각 순위 함수인 ROW_NUMBER, DENSE_RANK, RANK, NTILE는 OVER 절 사용.

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


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


USE AdventureWorks2008R2;
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