SELECT - WINDOW - (Transact-SQL)

[アーティクル]
05/23/2023

適用対象: SQL Server 2022 (16.x) Azure SQL Database Azure SQL Managed Instance

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 句では、複数の名前付きウィンドウを定義できます。

OVER 句の名前付きウィンドウに追加コンポーネントを追加するには、後ろに追加の仕様が続く window_name を使用します。しかし、WINDOW 句で指定されたプロパティを OVER 句で再定義することはできません。

クエリで複数のウィンドウが使用されている場合、ある名前付きウィンドウで、window_name を使用して別の名前付きウィンドウを参照できます。この場合、参照される window_name は、参照元ウィンドウのウィンドウ定義で指定する必要があります。あるウィンドウで定義されているウィンドウコンポーネントは、それを参照する別のウィンドウでは再定義できません。

ウィンドウ句でウィンドウが定義されている順序に基づいて、前方および後方のウィンドウ参照が許可されます。つまり、ウィンドウでは、定義されている順序に関係なく、reference_window_name として、その一部である <window_expression> で定義されている他のウィンドウを使用できます。循環参照、および単一ウィンドウでの複数のウィンドウ参照の使用は許可されません。

<window_expression> に含まれる定義済みウィンドウの新しい window_name のスコープは、ウィンドウ定義で構成されます。これらは、<query_specification> の SELECT 句、またはウィンドウ句を含む <SELECT statement> と一緒に、<window_expression> の一部となります。単純なテーブルクエリである <query_expression> の一部である <query_specification> に <window_expression> が含まれる場合は、新しい window_name のスコープに <order_by_expression> (存在する場合は、その <query_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	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 句で 1 つのウィンドウを指定する

次の例は、ウィンドウ指定を定義し、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 句で 1 つのウィンドウを指定する

C. window 句で共通仕様を定義する

D. 前方および後方のウィンドウ参照

関連項目

フィードバック

フィードバック

その他のリソース