OVER 子句 (Transact-SQL)
更新日期: 2006 年 7 月 17 日
确定在应用关联的开窗函数之前,行集的分区和排序。
适用范围:
排名开窗函数
聚合开窗函数。 有关详细信息,请参阅聚合函数 (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 聚合函数结合使用。
示例
A. 将 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
B. 将 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 | 总计 | Avg | 计数 | 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
计算,并会为每个 SalesOrderID
的每一行计算 Percent by ProductID
(ProductID 的百分比)。
SalesOrderID | ProductID | OrderQty | 总计 | 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)
帮助和信息
更改历史记录
发布日期 | 历史记录 |
---|---|
2006 年 7 月 17 日 |
|