ウィンドウ定義：OVER 句と WINDOW 句を使用したインライン定義

ウィンドウを定義する方法には次の 3 つがあります。

インライン (Window 関数の OVER 句内)
WINDOW 句内
インラインと WINDOW 句内で部分的に指定

ただし、以降の項に示すとおり、方法によっては制限があります。

インライン定義 (Window 関数の OVER 句内)

ウィンドウ定義は、Window 関数の OVER 句に配置できます。このことを、ウィンドウをインラインで定義するといいます。

たとえば、次の文は、2001 年 7 月と 8 月に出荷されたすべての製品と、出荷日ごとの累積出荷数量について、サンプルデータベースに問い合わせます。ウィンドウはインラインで定義されています。

SELECT p.ID, p.Description, s.Quantity, s.ShipDate,
   SUM( s.Quantity ) OVER ( PARTITION BY s.ProductID
       ORDER BY s.ShipDate
       ROWS BETWEEN UNBOUNDED PRECEDING
       AND CURRENT ROW ) AS Cumulative_qty
FROM SalesOrderItems s JOIN Products p 
   ON ( s.ProductID = p.ID )
WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31'
ORDER BY p.ID;

このクエリは、次の結果を返します。

	ID	Description	Quantity	ShipDate	Cumulative_qty
1	301	V-neck	24	2001-07-16	24
2	302	Crew Neck	60	2001-07-02	60
3	302	Crew Neck	36	2001-07-13	96
4	400	Cotton Cap	48	2001-07-05	48
5	400	Cotton Cap	24	2001-07-19	72
6	401	Wool Cap	48	2001-07-09	48
7	500	Cloth Visor	12	2001-07-22	12
8	501	Plastic Visor	60	2001-07-07	60
9	501	Plastic Visor	12	2001-07-12	72
10	501	Plastic Visor	12	2001-07-22	84
11	601	Zipped Sweatshirt	60	2001-07-19	60
12	700	Cotton Shorts	24	2001-07-26	24

この例では、2 つのテーブルのジョインとクエリの WHERE 句の適用後に、SUM Window 関数の計算が発生します。クエリは次のように処理されます。

ProductID の値を基に、入力ローを分割 (グループ化) します。
各分割内で、ShipDate の値を基にローをソートします。
分割内の各ローについて、Quantity の値に対して SUM 関数を評価します。このとき、各分割の最初の (ソートされた) ローからなるスライドウィンドウを使用します。

WINDOW 句の定義

前述のクエリの別の構成として、WINDOW 句を使用して、ウィンドウを使用する関数とは別にウィンドウを指定します。次に、各関数の OVER 句からウィンドウを参照します。

この例で、WINDOW 句はウィンドウ Cumulative を作成し、データを ProductID ごとに分割し、ShipDate で並べ替えます。SUM 関数は、その OVER 句でウィンドウを参照し、ROWS 句を使用してウィンドウのサイズを定義します。

SELECT p.ID, p.Description, s.Quantity, s.ShipDate,
    SUM( s.Quantity ) OVER ( Cumulative
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW ) AS cumulative_qty
FROM SalesOrderItems s 
JOIN Products p ON ( s.ProductID = p.ID )
WHERE s.ShipDate BETWEEN '2001-07-01' AND '2001-08-31'
WINDOW Cumulative AS ( PARTITION BY s.ProductID ORDER BY s.ShipDate )
ORDER BY p.ID;

WINDOW 句構文を使用するときは、次の制限があります。

PARTITION BY 句を指定する場合は、WINDOWS 句内に配置する必要があります。
ROWS 句または RANGE 句を指定する場合は、参照元関数の OVER 句内に配置する必要があります。
ウィンドウに ORDER BY 句を指定する場合は、WINDOW 句内か参照元関数の OVER 句内に配置できますが、両方には配置できません。
WINDOW 句は、SELECT 文の ORDER BY 句に先行する必要があります。

インラインおよび WINDOW 句の定義の組み合わせ

ウィンドウ定義の一部をインラインで指定し、残りを WINDOW 句で定義できます。次に例を示します。

AVG() OVER ( windowA
             ORDER BY expression )...
...
WINDOW windowA AS ( PARTITION BY expression )

この方法でウィンドウ定義を分割すると、次の制限が適用されます。

Window 関数構文では PARTITION BY 句を使用できません。
Window 関数構文または WINDOW 句のいずれかで ORDER BY 句を使用することはできますが、両方では使用できません。
RANGE 句または ROWS 句を WINDOW 句に含めることはできません。

参照

DocCommentXchange で意見交換できます