Предложение OVER (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. Выражение value_expression не может ссылаться на выражения или псевдонимы в списке выбора. value_expression может быть выражением столбца, скалярным вложенным запросом, скалярной функцией или пользовательской переменной.

• <Предложение ORDER BY>
  Задает порядок для ранжирующей оконной функции. Дополнительные сведения см. в разделе Предложение ORDER BY (Transact-SQL).

  Важно!
  Если <предложение ORDER BY> используется в контексте ранжирующей оконной функции, оно может ссылаться только на столбцы, доступные через предложение FROM. Указывать положение имени или псевдонима столбца в списке выборки с помощью целого числа нельзя. <Предложение ORDER BY> не может использоваться со статистическими оконными функциями.

Замечания

Оконные функции определены в стандарте ISO SQL. В SQL Server предоставляются ранжирующие и статистические оконные функции. Окно — это набор строк, определяемый пользователем. Оконная функция вычисляет значение для каждой строки в результирующем наборе, полученном из окна.

В одном запросе с одним предложением FROM может использоваться несколько статистических или ранжирующих оконных функций. Однако предложение OVER для каждой функции может использовать свое секционирование и упорядочение. Предложение OVER не может использоваться со статистической функцией CHECKSUM.

Примеры

А. Использование предложения OVER с функцией ROW_NUMBER

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

Б. Использование предложения 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

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

См. также

Справочник