SELECT - OVER 子句 （Transact-SQL）
适用于：
SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics Platform System (PDW) Microsoft Fabric 中的 SQL 终结点 Microsoft Fabric 中的仓库 Microsoft Fabric SQL 数据库
该 OVER 子句确定在应用关联的窗口函数之前行集的分区和排序。 也就是说，子 OVER 句定义查询结果集中的窗口或用户指定的行集。 然后，开窗函数将计算窗口中每一行的值。 可以将子 OVER 句与函数一起使用，以计算聚合值，例如移动平均值、累积聚合、运行总计或每个组结果的前 N 个。
语法
SQL Server、Azure SQL 数据库 和 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：定义结果集的每个分区中行的逻辑顺序。
通过指定分区中的起始点和终点来限制分区中的行或 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 | 分钟 | max |
|---|---|---|
| 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 | 分钟 | max |
|---|---|---|---|
| 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 | 分钟 | max |
|---|---|---|---|
| 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 排序规则名称 。 有关详细信息，请参阅 排序规则和 Unicode 支持。 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 | 前面 | 中心 | 后面 |
|---|---|---|---|
| 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) 及更高版本。
指定在与当前行一起使用时或与当前值一RANGE起使用ROWS时，窗口在当前行处开始或结束。 CURRENT ROW 可以同时指定为起点和终点。
BETWEEN AND
适用于：SQL Server 2012 (11.x) 及更高版本。
BETWEEN <window frame bound> AND <window frame bound>
与窗口ROWSRANGE的下限（起始）和上（结束）边界点一起使用。 <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 指定，该函数会将查询结果集的所有行视为单个组。
重要
如果ROWS指定<window frame preceding>或RANGE用于<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存在或不ROWSRANGE应用此窗口框架。
限制
子 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 示例和社区项目主页下载它。
A. 将 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子句的 OVER and SUM functions 为表中每个区域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
示例：Analytics Platform System (PDW)
E. 将 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