SELECT - WINDOW 句 (Transact-SQL)
適用対象: 
SQL Server 2022 (16.x) Azure SQL データベース Azure SQL Managed Instance
WINDOW句の名前付きウィンドウ定義は、OVER句でウィンドウを使用するウィンドウ関数の前の行セットのパーティション分割と順序を決定します。
WINDOW句には、データベース互換性レベル 160 以上が必要です。 データベース互換性レベルが160より低い場合、データベース エンジンは WINDOW 句を使用してクエリを実行できません。
互換性レベルは、 sys.databases ビューまたはデータベース プロパティで確認できます。 次のコマンドを使用して、データベースの互換性レベルを変更できます。
ALTER DATABASE DatabaseName
SET COMPATIBILITY_LEVEL = 160;
構文
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 句では再定義できません。
クエリで複数のウィンドウが使用されている場合、1 つの名前付きウィンドウは、 window_nameを使用して別の名前付きウィンドウを参照できます。 この場合、参照される window_name は、参照元ウィンドウのウィンドウ定義で指定する必要があります。 あるウィンドウで定義されているウィンドウ コンポーネントは、それを参照している別のウィンドウでは再定義できません。
ウィンドウ句でウィンドウが定義されている順序に基づいて、前方および後方のウィンドウ参照が許可されます。 つまり、ウィンドウは、定義されている順序に関係なく、ウィンドウ式の一部として、 reference_window_nameとして定義されている他のウィンドウを使用する可能性があります。 循環参照と、1 つのウィンドウでの複数のウィンドウ参照の使用は許可されません。
ウィンドウ式に含まれる定義済みウィンドウの新しい window_name のスコープは、ウィンドウ式の一部であるウィンドウ定義と、クエリ仕様の SELECT 句、または window 句を含む SELECT ステートメントで構成されます。 基本的なテーブル クエリであるクエリ式の一部であるクエリ仕様にウィンドウ式が含まれている場合、新しい window_name のスコープには、そのクエリ式の ORDER BY 式 (存在する場合) も含まれます。
セマンティクスに基づく集計関数と分析関数を使用した OVER 句でのウィンドウ仕様の使用に関する制限は、 WINDOW 句に適用されます。
例
この記事の Transact-SQL コード サンプルは AdventureWorks2022 または AdventureWorksDW2022 サンプル データベースを使用します。このサンプル データベースは、Microsoft SQL Server サンプルとコミュニティ プロジェクトのホーム ページからダウンロードできます。
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 | カウント | 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 | Qty | 合計 | 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 | Qty | 合計 | 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 |