SELECT - WINDOW - (Transact-SQL)
适用于:SQL Server 2022 (16.x) Azure SQL 数据库Azure SQL 托管实例
在应用 OVER 子句中使用窗口的窗口函数之前,WINDOW 子句中的命名窗口定义确定行集的分区和排序。
注意
WINDOW 子句要求数据库兼容性级别为 160 或更高。 如果数据库兼容性级别低于 160,则 SQL Server 无法使用 WINDOW 子句执行查询。
可在 sys.databases 视图或数据库属性中查看兼容性级别。 可以使用以下命令更改数据库的兼容级别:
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 160
Transact-SQL 语法约定 (Transact-SQL)
语法
WINDOW window_name AS (
[ reference_window_name ]
[ <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_name
定义的窗口规范的名称。 OVER 子句中的窗口函数将使用此名称来引用窗口规范。 窗口名称必须符合标识符的规则。
reference_window_name
当前窗口引用的窗口的名称。 引用的窗口必须位于 WINDOW 子句中定义的窗口之间。
其他参数包括:
PARTITION BY:将查询结果集分为多个分区。
ORDER BY:定义结果集的每个分区中行的逻辑顺序。
ROWS/RANGE:通过指定分区中的起点和终点来限制分区中的行数。
有关参数的更多具体详细信息,请参阅 OVER 子句
一般备注
可在 WINDOW 子句中定义多个命名窗口。
通过使用后跟附加规范的 window_name,可将附加组件添加到 OVER 子句中的命名窗口。 但是,不能在 OVER 子句中重新定义 WINDOW 子句中指定的属性。
当查询使用多个窗口时,一个命名窗口可以使用 window_name 引用另一个命名窗口。 在这种情况下,必须在引用窗口的窗口定义中指定引用 window_name。 一个窗口中定义的窗口组件不能由另一个引用它的窗口重新定义。
根据在 window 子句中定义窗口的顺序,允许向前和向后的窗口引用。 换句话说,一个窗口可使用在它所属的 <window_expression>
中定义的任何其他窗口作为 reference_window_name
,而不管它们的定义顺序如何。 不允许循环引用和在单个窗口中使用多个窗口引用。
<window_expression>
中包含的已定义窗口的新 window_name 的范围由属于 <window_expression>
的任何窗口定义,以及包含该 window 子句的 <query_specification>
或 <SELECT statement>
的 SELECT 子句组成。 如果 <window_expression>
包含在属于简单表查询 <query_expression>
的 <query_specification>
中,则新 window_name 的范围还包括该 <query_expression>
的 <order_by_expression>
(如果有)。
OVER 子句中窗口规范与基于语义的聚合函数和分析函数的使用限制适用于 WINDOW 子句。
示例
A. 指定在 window 子句中定义的窗口
以下示例查询显示在 OVER 子句中使用命名窗口。
ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO
USE AdventureWorks2022;
GO
SELECT ROW_NUMBER() OVER win 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
WINDOW win AS (PARTITION BY PostalCode ORDER BY SalesYTD DESC)
ORDER BY PostalCode;
GO
以下查询与上述查询等效,但不使用 WINDOW 子句。
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 | 姓氏 | 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 子句中多次使用它。
ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO
USE AdventureWorks2022;
GO
SELECT SalesOrderID, ProductID, OrderQty
,SUM(OrderQty) OVER win AS Total
,AVG(OrderQty) OVER win AS "Avg"
,COUNT(OrderQty) OVER win AS "Count"
,MIN(OrderQty) OVER win AS "Min"
,MAX(OrderQty) OVER win AS "Max"
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664)
WINDOW win AS (PARTITION BY SalesOrderID);
GO
以下查询与上述查询等效,但不使用 WINDOW 子句。
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
下面是结果集。
销售订单 ID | 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 |
C. 在 window 子句中定义通用规范
此示例显示在窗口中定义通用规范,并使用它在 OVER 子句中定义其他规范。
ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO
USE AdventureWorks2022;
GO
SELECT SalesOrderID AS OrderNumber, ProductID,
OrderQty AS Qty,
SUM(OrderQty) OVER win AS Total,
AVG(OrderQty) OVER(win PARTITION BY SalesOrderID) AS Avg,
COUNT(OrderQty) OVER(win ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
ProductID LIKE '71%'
WINDOW win AS (ORDER BY SalesOrderID, ProductID);
GO
以下查询与上述查询等效,但不使用 WINDOW 子句。
USE AdventureWorks2022;
GO
SELECT SalesOrderID AS OrderNumber, ProductID,
OrderQty AS Qty,
SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
AVG(OrderQty) OVER(PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg,
COUNT(OrderQty) OVER(ORDER BY SalesOrderID, ProductID ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING) AS Count
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
ProductID LIKE '71%';
GO
下面是结果集。
OrderNumber | ProductID | OrderQty | 总计 | Avg | 计数 |
---|---|---|---|---|---|
43659 | 711 | 4 | 4 | 4 | 2 |
43659 | 712 | 2 | 6 | 3 | 3 |
43659 | 714 | 3 | 9 | 3 | 4 |
43659 | 716 | 1 | 10 | 2 | 5 |
43664 | 714 | 1 | 11 | 1 | 6 |
43664 | 716 | 1 | 12 | 1 | 6 |
D. 向前和向后的窗口引用
此示例显示在 WINDOW 子句中定义新窗口时,将命名窗口用作前向引用和向后引用。
ALTER DATABASE AdventureWorks2022 SET Compatibility_level = 160;
GO
USE AdventureWorks2022;
GO
SELECT SalesOrderID AS OrderNumber, ProductID,
OrderQty AS Qty,
SUM(OrderQty) OVER win2 AS Total,
AVG(OrderQty) OVER win1 AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
ProductID LIKE '71%'
WINDOW win1 AS (win3),
win2 AS (ORDER BY SalesOrderID, ProductID),
win3 AS (win2 PARTITION BY SalesOrderID);
GO
以下查询与上述查询等效,但不使用 WINDOW 子句。
USE AdventureWorks2022;
GO
SELECT SalesOrderID AS OrderNumber, ProductID,
OrderQty AS Qty,
SUM(OrderQty) OVER (ORDER BY SalesOrderID, ProductID) AS Total,
AVG(OrderQty) OVER (PARTITION BY SalesOrderID ORDER BY SalesOrderID, ProductID) AS Avg
FROM Sales.SalesOrderDetail
WHERE SalesOrderID IN(43659,43664) AND
ProductID LIKE '71%';
GO |
下面是结果集。
OrderNumber | ProductID | OrderQty | 总计 | Avg |
---|---|---|---|---|
43659 | 711 | 4 | 4 | 4 |
43659 | 712 | 2 | 6 | 3 |
43659 | 714 | 3 | 9 | 3 |
43659 | 716 | 1 | 10 | 2 |
43664 | 714 | 1 | 11 | 1 |
43664 | 716 | 1 | 12 | 1 |
另请参阅
反馈
https://aka.ms/ContentUserFeedback。
即将发布:在整个 2024 年,我们将逐步淘汰作为内容反馈机制的“GitHub 问题”,并将其取代为新的反馈系统。 有关详细信息,请参阅:提交和查看相关反馈