SELECT - WINDOW - (Transact-SQL)
適用於:SQL Server 2022 (16.x) Azure SQL 資料庫 Azure SQL 受控執行個體
WINDOW 子句中的具名時間範圍定義會決定在套用時間範圍函式以在 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 子句中的具名時間範圍。 不過,WINDOW 子句中指定的屬性無法在 OVER 子句中重新定義。
當查詢使用多個時間範圍時,一個具名時間範圍可以使用 window_name 來參考另一個具名時間範圍。 在此情況下,參考的 window_name 必須指定於參考時間範圍的時間範圍定義中。 一個時間範圍中定義的時間範圍元件無法由加以參考的另一個時間範圍重新定義。
根據時間範圍定義於時間範圍子句中的順序,會允許前向和後向時間範圍參考。 換句話說,時間範圍可能會使用在其所屬的 <window_expression>
中定義的任何其他時間範圍作為 reference_window_name
,無論其定義順序為何。 不允許循環參考以及在單一時間範圍中使用多個時間範圍參考。
<window_expression>
中包含的已定義時間範圍的新 window_name 的範圍,由屬於 <window_expression>
的任何時間範圍定義,以及包含 window 子句的 <query_specification>
或 <SELECT statement>
的 SELECT 子句組成。 如果 <window_expression>
包含在 <query_specification>
中,且其所屬的 <query_expression>
是一個簡單資料表查詢,則新 window_name 的範圍也會包含該 <query_expression>
的 <order_by_expression>
(如果有的話)。
WINDOW 子句適用在 OVER 子句中根據語意搭配彙總和分析函式使用時間範圍規格的限制。
範例
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
以下為結果集。
資料列號碼 | 姓氏 | 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
以下為結果集。
SalesOrderID | ProductID | OrderQty | 總計 | 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 |
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 | Count |
---|---|---|---|---|---|
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 問題作為內容的意見反應機制,並將它取代為新的意見反應系統。 如需詳細資訊,請參閱:提交並檢視相關的意見反應