ПРЕДЛОЖЕНИЕ SELECT — OVER (Transact-SQL)
Область применения:
SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure конечной точке аналитики платформы Аналитики Azure Synapse Analytics (PDW) в Microsoft Fabric Хранилище в базе данных Microsoft Fabric SQL в 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функция окна использует все строки в секции.Если задано или
ROWSRANGEне указано, то значение по умолчанию используется в качестве значения по умолчаниюRANGE BETWEEN 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
В. Создание скользящей средней и совокупной общей суммы
В следующем примере используется 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