Поделиться через

Предложение 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).

    ms189461.note(ru-ru,SQL.90).gifВажно!
    При использовании в контексте функции ранжирования окна <Предложение 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

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

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, а столбец Percent by ProductID вычисляется для каждой строки каждого SalesOrderID.

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

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

Версия Журнал

17 июля 2006 г.
Добавления
  • Дополнительные примеры.