ПРЕДЛОЖЕНИЕ SELECT — OVER (Transact-SQL)

  • Статья

Область применения: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в Microsoft Fabric

Предложение OVER определяет секционирование и порядок набора строк перед применением связанной функции окна. То есть OVER предложение определяет окно или заданный пользователем набор строк в результирующем наборе запросов. Затем оконная функция вычисляет значение для каждой строки в окне. Предложение можно использовать OVER с функциями для вычисления статистических значений, таких как скользящее среднее значение, совокупные агрегаты, запущенные итоги или первые N на результаты группы.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure и Azure Synapse Analytics.

OVER (
       [ <PARTITION BY clause> ]
       [ <ORDER BY clause> ]
       [ <ROW or RANGE clause> ]
      )

<PARTITION BY clause> ::=
PARTITION BY value_expression , ... [ n ]

<ORDER BY clause> ::=
ORDER BY order_by_expression
    [ COLLATE collation_name ]
    [ ASC | DESC ]
    [ , ...n ]

<ROW or RANGE clause> ::=
{ ROWS | RANGE } <window frame extent>

<window frame extent> ::=
{   <window frame preceding>
  | <window frame between>
}
<window frame between> ::=
  BETWEEN <window frame bound> AND <window frame bound>

<window frame bound> ::=
{   <window frame preceding>
  | <window frame following>
}

<window frame preceding> ::=
{
    UNBOUNDED PRECEDING
  | <unsigned_value_specification> PRECEDING
  | CURRENT ROW
}

<window frame following> ::=
{
    UNBOUNDED FOLLOWING
  | <unsigned_value_specification> FOLLOWING
  | CURRENT ROW
}

<unsigned value specification> ::=
{  <unsigned integer literal> }

Синтаксис для параллельного хранилища данных.

OVER ( [ PARTITION BY value_expression ] [ order_by_clause ] )

Аргументы

Оконные функции могут иметь следующие аргументы в предложении OVER:

  • PARTITION BY — разделяет результирующий набор запроса на секции.

  • ORDER BY — определяет логический порядок строк в каждой секции результирующего набора.

  • ROWS или RANGE , ограничивающие строки в секции, задав начальные и конечные точки в разделе. Он требует аргумента ORDER BY, а по умолчанию будет охватывать интервал от начала секции до текущего элемента, если указан аргумент ORDER BY.

Если аргумент не указан, функции окна применяются ко всему результирующий набору.

SELECT object_id,
       MIN(object_id) OVER () AS [min],
       MAX(object_id) OVER () AS [max]
FROM sys.objects;
object_id мин макс.
3 3 2139154666
5 3 2139154666
... ... ...
2123154609 3 2139154666
2139154666 3 2139154666

PARTITION BY

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

PARTITION BY <value_expression>

Если PARTITION BY не указано, функция обрабатывает все строки результирующих наборов запросов как одну секцию.

Функция применяется ко всем строкам в секции, если предложение не указано ORDER BY .

PARTITION BY value_expression

Определяет столбец, по которому секционируется набор строк. value_expression может ссылаться только на столбцы, доступные предложениемFROM. value_expression не может ссылаться на выражения или псевдонимы в списке выбора. Выражение value_expression может быть выражением столбца, скалярным вложенным запросом, скалярной функцией или пользовательской переменной.

SELECT object_id,
       type,
       MIN(object_id) OVER (PARTITION BY type) AS [min],
       MAX(object_id) OVER (PARTITION BY type) AS [max]
FROM sys.objects;
object_id type мин макс.
68195293 PK 68195293 711673583
631673298 PK 68195293 711673583
711673583 PK 68195293 711673583
... ... ... ...
3 S 3 98
5 S 3 98
... ... ... ...
98 S 3 98
... ... ... ...

ORDER BY

ORDER BY <order_by_expression> [ COLLATE <collation_name> ] [ ASC | DESC ]

Определяет логический порядок строк в каждой секции результирующего набора. То есть он указывает логический порядок, в котором выполняется вычисление оконной функции.

  • Если это не указано, порядок по умолчанию и ASC функция окна использует все строки в секции.

  • Если задано или ROWS RANGE не указано, то значение по умолчанию используется в качестве значения по умолчанию RANGE UNBOUNDED PRECEDING AND CURRENT ROW для рамки окна, функциями, которые могут принимать необязательные ROWS или RANGE спецификации (например, min или max).

SELECT object_id,
       type,
       MIN(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [min],
       MAX(object_id) OVER (PARTITION BY type ORDER BY object_id) AS [max]
FROM sys.objects;
object_id type мин макс.
68195293 PK 68195293 68195293
631673298 PK 68195293 631673298
711673583 PK 68195293 711673583
... ... ...
3 S 3 3
5 S 3 5
6 S 3 6
... ... ...
97 S 3 97
98 S 3 98
... ... ...

order_by_expression

Указывает столбец или выражение, по которому производится сортировка. order_by_expression может ссылаться только на столбцы, доступные предложениемFROM. Целое число не может быть указано для представления имени столбца или псевдонима.

COLLATE collation_name

Указывает, что ORDER BY операция должна выполняться в соответствии с параметрами сортировки, указанными в collation_name. Аргументом collation_name может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Дополнительные сведения см. в разделе о поддержке сортировки и Юникода. COLLATEприменимо только для столбцов типа char, varchar, nchar и nvarchar.

ASC | DESC

Указывает порядок сортировки значений в указанном столбце — по возрастанию или по убыванию. Порядок сортировки по умолчанию — ASC. Значения NULL рассматриваются как минимально возможные значения.

ROWS или RANGE

Применимо: SQL Server 2012 (11.x) и более поздних версий.

Еще больше ограничивает строки в пределах секции, указывая начальную и конечную точки. Он задает диапазон строк относительно текущей строки по логической ассоциации или физической ассоциации. Физическая связь достигается с помощью ROWS предложения.

Предложение ROWS ограничивает строки в секции путем указания фиксированного количества строк, предшествующих текущей или следующей за текущей строкой. Кроме того, предложение логически ограничивает строки в секции, RANGE указав диапазон значений относительно значения в текущей строке. Предыдущие и следующие строки определяются на основе упорядочения в предложении ORDER BY . Кадр RANGE ... CURRENT ROW ... окна содержит все строки с одинаковыми значениями в ORDER BY выражении, что и текущая строка. Например, это означает, что окно строк, на которые работает функция, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW составляет три строки размером, начиная с 2 строк, предшествующих и включая текущую строку.

SELECT object_id,
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [preceding],
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS [central],
       COUNT(*) OVER (ORDER BY object_id ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS [following]
FROM sys.objects
ORDER BY object_id ASC;
object_id preceding центральный following
3 1 3 156
5 2 4 155
6 3 5 154
7 4 5 153
8 5 5 152
... ... ... ...
2112726579 153 5 4
2119678599 154 5 3
2123154609 155 4 2
2139154666 156 3 1

ROWS или RANGE требуется указать ORDER BY предложение. Если ORDER BY содержит несколько выражений порядка, CURRENT ROW FOR RANGE при определении текущей строки учитывается все столбцы в ORDER BY списке.

UNBOUNDED PRECEDING

Применимо: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно начинается с первой строки секции. UNBOUNDED PRECEDING можно указать только начальную точку окна.

<спецификация неподписанного значения> PRECEDING

Задано для <unsigned value specification> указания количества строк или значений, предшествующих текущей строке. Эта спецификация не разрешена RANGE.

CURRENT ROW

Применимо: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно начинается или заканчивается в текущей строке при использовании с ROWS или текущим значением при использовании с RANGE. CURRENT ROW можно указать как начальную, так и конечную точку.

BETWEEN AND

Применимо: SQL Server 2012 (11.x) и более поздних версий.

BETWEEN <window frame bound> AND <window frame bound>

Используется либо для ROWS RANGE указания нижних (начальных) и верхних (конечных) точек границ окна. <window frame bound> определяет начальную точку границы и <window frame bound> определяет конечную точку границы. Верхняя граница не может быть меньше нижней границы.

UNBOUNDED FOLLOWING

Применимо: SQL Server 2012 (11.x) и более поздних версий.

Указывает, что окно заканчивается на последней строке секции. UNBOUNDED FOLLOWING можно указать только конечную точку окна. Например, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING определяет окно, которое начинается с текущей строки и заканчивается последней строкой секции.

<спецификация неподписанного значения> FOLLOWING

Указывается с <unsigned value specification> для обозначения числа строк или значений после текущей строки. При <unsigned value specification> FOLLOWING указании в качестве начальной точки окна конечная точка должна быть <unsigned value specification> FOLLOWING. Например, ROWS BETWEEN 2 FOLLOWING AND 10 FOLLOWING определяет окно, начинающееся со второй строки, которая следует текущей строке и заканчивается десятой строкой, следующей за текущей строкой. Эта спецификация не разрешена RANGE.

<целочисленный литерал без знака>

Применимо: SQL Server 2012 (11.x) и более поздних версий.

Положительный целочисленный литерал (включая 0), указывающий количество строк или значений, которые следует предшествовать текущей строке или значению. Эта спецификация допустима только для ROWS.

Замечания

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

Если PARTITION BY не указано, функция обрабатывает все строки результирующих наборов запросов как одну группу.

Внимание

RANGE Если ROWS или задано и <window frame preceding> используется для (короткого синтаксиса), эта спецификация используется <window frame extent> для начальной точки границы рамки окна и CURRENT ROW используется для конечной точки границы. Например, ROWS 5 PRECEDING равно ROWS BETWEEN 5 PRECEDING AND CURRENT ROW.

Если ORDER BY это не указано, для окна используется весь раздел. Это относится только к функциям, которые не требуют ORDER BY предложения. Если ROWS или RANGE он не указан, но ORDER BY указан, RANGE UNBOUNDED PRECEDING AND CURRENT ROW используется в качестве значения по умолчанию для фрейма окна. Это относится только к функциям, которые могут принимать необязательные ROWS или RANGE спецификации. Например, функции ранжирования не могут принимать ROWS или RANGE, следовательно, этот кадр окна не применяется, даже если ORDER BY он присутствует и ROWS RANGE не является.

Ограничения

Предложение OVER нельзя использовать с DISTINCT агрегатами.

RANGE нельзя использовать с <unsigned value specification> PRECEDING или <unsigned value specification> FOLLOWING.

В зависимости от ранжирования, статистической или аналитической функции, используемой OVER с предложением, <ORDER BY clause> и (или) <ROWS and RANGE clause> может не поддерживаться.

Примеры

Примеры кода Transact-SQL в этой статье используют AdventureWorks2022 базу данных или AdventureWorksDW2022 пример базы данных, которую можно скачать с домашней страницы примеров и проектов сообщества Microsoft SQL Server.

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

В следующем примере показано использование OVER предложения с ROW_NUMBER функцией для отображения номера строки для каждой строки в разделе. Предложение ORDER BY, указанное в предложении OVER, упорядочивает строки каждой секции по столбцу SalesYTD. Предложение ORDER BY в SELECT инструкции определяет порядок, в котором возвращается весь результирующий набор запросов.

USE AdventureWorks2022;
GO

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

Вот результирующий набор.

Row Number      LastName                SalesYTD              PostalCode
--------------- ----------------------- --------------------- ----------
1               Mitchell                4251368.5497          98027
2               Blythe                  3763178.1787          98027
3               Carson                  3189418.3662          98027
4               Reiter                  2315185.611           98027
5               Vargas                  1453719.4653          98027
6               Ansman-Wolfe            1352577.1325          98027
1               Pak                     4116871.2277          98055
2               Varkey Chudukatil       3121616.3202          98055
3               Saraiva                 2604540.7172          98055
4               Ito                     2458535.6169          98055
5               Valdez                  1827066.7118          98055
6               Mensa-Annan             1576562.1966          98055
7               Campbell                1573012.9383          98055
8               Tsoflias                1421810.9242          98055

B. Использование предложения OVER с агрегатными функциями

В следующем примере предложение OVER используется с агрегатной функцией для всех возвращаемых запросом строк. В данном примере использование предложения OVER является более эффективным, чем использование вложенных запросов для получения статистических значений.

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

C. Создание скользящей средней и совокупной общей суммы

В следующем примере используется AVG предложение и SUM функции с OVER предложением для предоставления скользящей средней и совокупной суммы ежегодных продаж для каждой Sales.SalesPerson территории в таблице. Данные секционируются по TerritoryID и логически сортируются по SalesYTD. Это означает, что AVG функция вычисляется для каждой территории на основе года продаж. Для TerritoryID 1 есть две строки для года 2005 продаж, представляющих двух продавцов с продажами в этом году. Средние продажи для этих двух строк вычисляются, а затем третья строка, представляющая продажи за год 2006 , включается в вычисление.

USE AdventureWorks2022;
GO

SELECT BusinessEntityID,
       TerritoryID,
       DATEPART(yy, ModifiedDate) AS SalesYear,
       CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
       CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
       CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
      OR TerritoryID < 5
ORDER BY TerritoryID, SalesYear;

Вот результирующий набор.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           559,697.56           559,697.56
287              NULL        2006        519,905.93           539,801.75           1,079,603.50
285              NULL        2007        172,524.45           417,375.98           1,252,127.95
283              1           2005        1,573,012.94         1,462,795.04         2,925,590.07
280              1           2005        1,352,577.13         1,462,795.04         2,925,590.07
284              1           2006        1,576,562.20         1,500,717.42         4,502,152.27
275              2           2005        3,763,178.18         3,763,178.18         3,763,178.18
277              3           2005        3,189,418.37         3,189,418.37         3,189,418.37
276              4           2005        4,251,368.55         3,354,952.08         6,709,904.17
281              4           2005        2,458,535.62         3,354,952.08         6,709,904.17

В этом примере OVER предложение не включает PARTITION BY. Это означает, что функция применяется ко всем строкам, возвращаемым запросом. Предложение ORDER BY , указанное OVER в предложении, определяет логический порядок AVG применения функции. Запрос возвращает скользящее среднее значение продаж за год для всех территорий продаж, указанных в предложении WHERE . Предложение ORDER BY , указанное SELECT в инструкции, определяет порядок отображения строк запроса.

SELECT BusinessEntityID,
       TerritoryID,
       DATEPART(yy, ModifiedDate) AS SalesYear,
       CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
       CONVERT (VARCHAR (20), AVG(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS MovingAvg,
       CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (ORDER BY DATEPART(yy, ModifiedDate)), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
      OR TerritoryID < 5
ORDER BY SalesYear;

Вот результирующий набор.

BusinessEntityID TerritoryID SalesYear   SalesYTD             MovingAvg            CumulativeTotal
---------------- ----------- ----------- -------------------- -------------------- --------------------
274              NULL        2005        559,697.56           2,449,684.05         17,147,788.35
275              2           2005        3,763,178.18         2,449,684.05         17,147,788.35
276              4           2005        4,251,368.55         2,449,684.05         17,147,788.35
277              3           2005        3,189,418.37         2,449,684.05         17,147,788.35
280              1           2005        1,352,577.13         2,449,684.05         17,147,788.35
281              4           2005        2,458,535.62         2,449,684.05         17,147,788.35
283              1           2005        1,573,012.94         2,449,684.05         17,147,788.35
284              1           2006        1,576,562.20         2,138,250.72         19,244,256.47
287              NULL        2006        519,905.93           2,138,250.72         19,244,256.47
285              NULL        2007        172,524.45           1,941,678.09         19,416,780.93

D. Указание предложения ROWS

Применимо: SQL Server 2012 (11.x) и более поздних версий.

В следующем примере предложение используется ROWS для определения окна, по которому строки вычисляются в качестве текущей строки и N числа следующих строк (одна строка в этом примере).

SELECT BusinessEntityID,
       TerritoryID,
       CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
       DATEPART(yy, ModifiedDate) AS SalesYear,
       CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
      OR TerritoryID < 5;

Вот результирующий набор.

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274              NULL        559,697.56           2005        1,079,603.50
287              NULL        519,905.93           2006        692,430.38
285              NULL        172,524.45           2007        172,524.45
283              1           1,573,012.94         2005        2,925,590.07
280              1           1,352,577.13         2005        2,929,139.33
284              1           1,576,562.20         2006        1,576,562.20
275              2           3,763,178.18         2005        3,763,178.18
277              3           3,189,418.37         2005        3,189,418.37
276              4           4,251,368.55         2005        6,709,904.17
281              4           2,458,535.62         2005        2,458,535.62

В следующем примере ROWS предложение указывается с UNBOUNDED PRECEDING. В результате окно начинается с первой строки секции.

SELECT BusinessEntityID,
       TerritoryID,
       CONVERT (VARCHAR (20), SalesYTD, 1) AS SalesYTD,
       DATEPART(yy, ModifiedDate) AS SalesYear,
       CONVERT (VARCHAR (20), SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy, ModifiedDate) ROWS UNBOUNDED PRECEDING), 1) AS CumulativeTotal
FROM Sales.SalesPerson
WHERE TerritoryID IS NULL
      OR TerritoryID < 5;

Вот результирующий набор.

BusinessEntityID TerritoryID SalesYTD             SalesYear   CumulativeTotal
---------------- ----------- -------------------- ----------- --------------------
274              NULL        559,697.56           2005        559,697.56
287              NULL        519,905.93           2006        1,079,603.50
285              NULL        172,524.45           2007        1,252,127.95
283              1           1,573,012.94         2005        1,573,012.94
280              1           1,352,577.13         2005        2,925,590.07
284              1           1,576,562.20         2006        4,502,152.27
275              2           3,763,178.18         2005        3,763,178.18
277              3           3,189,418.37         2005        3,189,418.37
276              4           4,251,368.55         2005        4,251,368.55
281              4           2,458,535.62         2005        6,709,904.17

Примеры: система платформы аналитики (PDW)

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

В приведенном ниже примере возвращается ROW_NUMBER для торговых представителей в зависимости от установленной для них квоты продаж.

SELECT ROW_NUMBER() OVER (ORDER BY SUM(SalesAmountQuota) DESC) AS RowNumber,
       FirstName,
       LastName,
       CONVERT (VARCHAR (13), SUM(SalesAmountQuota), 1) AS SalesQuota
FROM dbo.DimEmployee AS e
     INNER JOIN dbo.FactSalesQuota AS sq
         ON e.EmployeeKey = sq.EmployeeKey
WHERE e.SalesPersonFlag = 1
GROUP BY LastName, FirstName;

Далее представлен частичный результирующий набор.

RowNumber  FirstName  LastName            SalesQuota
---------  ---------  ------------------  -------------
1          Jillian    Carson              12,198,000.00
2          Linda      Mitchell            11,786,000.00
3          Michael    Blythe              11,162,000.00
4          Jae        Pak                 10,514,000.00

F. Использование предложения OVER с агрегатными функциями

В следующих примерах показано использование OVER предложения с агрегатными функциями. В этом примере использование предложения является более эффективным, чем использование OVER вложенных запросов.

SELECT SalesOrderNumber AS OrderNumber,
       ProductKey,
       OrderQuantity AS Qty,
       SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
       AVG(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS AVG,
       COUNT(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS COUNT,
       MIN(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MIN,
       MAX(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS MAX
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
      AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;

Вот результирующий набор.

OrderNumber  Product  Qty  Total  Avg  Count  Min  Max
-----------  -------  ---  -----  ---  -----  ---  ---
SO43659      218      6    16     3    5      1    6
SO43659      220      4    16     3    5      1    6
SO43659      223      2    16     3    5      1    6
SO43659      229      3    16     3    5      1    6
SO43659      235      1    16     3    5      1    6
SO43664      229      1     2     1    2      1    1
SO43664      235      1     2     1    2      1    1

Следующий пример демонстрирует использование предложения OVER с агрегатной функцией в вычисляемом значении. Агрегаты вычисляются по SalesOrderNumber и процент общего заказа на продажу вычисляется для каждой строки каждой SalesOrderNumberстроки.

SELECT SalesOrderNumber AS OrderNumber,
       ProductKey AS Product,
       OrderQuantity AS Qty,
       SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) AS Total,
       CAST (1. * OrderQuantity / SUM(OrderQuantity) OVER (PARTITION BY SalesOrderNumber) * 100 AS DECIMAL (5, 2)) AS PctByProduct
FROM dbo.FactResellerSales
WHERE SalesOrderNumber IN (N'SO43659', N'SO43664')
      AND ProductKey LIKE '2%'
ORDER BY SalesOrderNumber, ProductKey;

Первое начало этого результирующий набор выглядит следующим образом:

OrderNumber  Product  Qty  Total  PctByProduct
-----------  -------  ---  -----  ------------
SO43659      218      6    16     37.50
SO43659      220      4    16     25.00
SO43659      223      2    16     12.50
SO43659      229      2    16     18.75

Связанный контент