SELECT - WINDOW - (Transact-SQL)

發行項
05/04/2023

適用於：SQL Server 2022 (16.x)Azure SQL Database 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

SELECT - WINDOW - (Transact-SQL)

語法

引數

window_name

reference_window_name

一般備註

範例

A. 指定在 window 子句中定義的時間範圍

B. 在多個 over 子句中指定單一時間範圍

C. 在 window 子句中定義通用規格

D. 前向和後向時間範圍參考

另請參閱

意見反應

意見反應

其他資源