Предложение OVER (Transact-SQL)

Изменения: 17 июля 2006 г.

Определяет секционирование и упорядочение набора строк до применения функции ассоциированного окна.

Применимо к:

Ранжирующие функции окна

Статистические функции окна. Дополнительные сведения см. в разделе Статистические функции (Transact-SQL).

Синтаксические обозначения в 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> может быть выражением столбца, скалярным вложенным запросом или определяемой пользователем переменной.

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

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

Замечания

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

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

Примеры

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

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

Ниже приводится результирующий набор.

Следующий пример демонстрирует использование предложения 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, а столбец Percent by ProductID вычисляется для каждой строки каждого SalesOrderID.

См. также

Справочник

Ранжирующие функции (Transact-SQL)
Статистические функции (Transact-SQL)

Справка и поддержка

Получение помощи по SQL Server 2005

Журнал изменений