ウィンドウ定義

SQL のウィンドウ拡張を使用して、ウィンドウの境界や、入力ローの分割や順序付けを設定できます。論理的には、GROUP BY 句で定義されたグループが作成された後、最終の SELECT リストの評価とクエリの ORDER BY 句の前に、クエリ仕様の結果を計算するセマンティックの一部として分割が作成されます。SQL 文における句の評価順は次のようになります。

FROM
WHERE
GROUP BY
HAVING
WINDOW
DISTINCT
ORDER BY

クエリを形成する際には、評価順の影響を考慮する必要があります。たとえば、同じ SELECT クエリブロックにある Window 関数を参照する式を述部にすることはできません。ただし、クエリブロックを派生テーブルに入れることで、派生テーブルを使用して述部を指定できます。次のクエリを実行すると、Window 関数が述部に使用されているという旨のメッセージが表示されて、クエリは失敗します。

SELECT DepartmentID, Surname, StartDate, Salary,
       SUM( Salary ) OVER ( PARTITION BY DepartmentID
          ORDER BY StartDate
          RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary" 
   FROM Employees
   WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
      AND DepartmentID IN ( '100', '200' )
   GROUP BY DepartmentID, Surname, StartDate, Salary
   HAVING Salary > 0 AND "Sum_Salary" > 200
   ORDER BY DepartmentID, StartDate;

目的の結果を得るためには、派生テーブル (DT) を使用して述部を指定します。

SELECT * FROM ( SELECT DepartmentID, Surname, StartDate, Salary,
                       SUM( Salary ) OVER ( PARTITION BY DepartmentID
                         ORDER BY StartDate
                         RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS "Sum_Salary"
                  FROM Employees
                  WHERE State IN ( 'CA', 'UT', 'NY', 'AZ' ) 
                     AND DepartmentID IN ( '100', '200' )
                  GROUP BY DepartmentID, Surname, StartDate, Salary
                  HAVING Salary > 0
                  ORDER BY DepartmentID, StartDate ) AS DT
   WHERE DT.Sum_Salary > 200;

ウィンドウ分割は GROUP BY 演算子に続くので、分割を実行する計算で、任意の集合関数 (SUM、AVG、VARIANCE など) の結果を利用できます。そのため、クエリの GROUP BY 句や ORDER BY 句だけでなく、ウィンドウを使用することでもグループ化と順序付けの操作を実行できます。

ウィンドウ指定の定義

Window 関数で操作するウィンドウを定義するときは、次の項目を 1 つまたは複数指定します。

分割 (PARTITION BY 句) PARTITION BY 句により、入力ローのグループ化方法を定義します。省略すると、入力全体が単一の分割として扱われます。指定した内容に応じて、1 つ、複数、またはすべての入力ローから分割を作成できます。2 つの分割からのデータが混合することはありません。つまり、ウィンドウが 2 つの分割の境界に達すると、分割内のデータの処理が終了してから、次の分割内のデータの処理が開始されます。ウィンドウの境界の定義方法に応じて、ウィンドウのサイズが分割の先頭と末尾で変化する可能性があります。
順序付け (ORDER BY 句) ORDER BY 句により、Window 関数による処理の前に、入力ローの順序を決める方法を定義します。RANGE 句を使用して境界を指定する場合、またはランキング関数がウィンドウを参照する場合にかぎり、ORDER BY 句が必要です。それ以外の場合、ORDER BY 句はオプションです。省略すると、データベースサーバが最も効率的な順序で入力ローを処理します。

境界 (RANGE 句および ROWS 句) 現在のローは、ウィンドウの開始ローと終了ローを判断するための参照ポイントになります。ウィンドウ定義の RANGE 句と ROWS 句を使用して、境界を設定できます。RANGE は、現在のローの値からのオフセットとしてデータ値の範囲を指定することでウィンドウを定義します。範囲を計算するためにはデータが順序付けられている必要があるため、RANGE を指定する場合は ORDER BY 句も指定する必要があります。

ROWS は、現在のローからのオフセットとしてロー数を指定することで、ウィンドウを定義します。

RANGE は、データ値の範囲としてローのセットを定義するため、RANGE ウィンドウに含まれるローは現在のローを越える場合があります。この点は ROWS とは異なります。次の図は、ROWS 句と RANGE 句の違いを示したものです。

同様の ROWS 指定と RANGE 指定のウィンドウサイズの比較。後者のほうがローが多い。

ROWS 句と RANGE 句内では、現在のローを基準にしてウィンドウの開始ローと終了ローを (オプションで) 指定できます。これには、PRECEDING 句、BETWEEN 句、および FOLLOWING 句を使用します。これらの句には、式の他に、UNBOUNDED と CURRENT ROW の各キーワードも指定できます。ウィンドウに境界が定義されていない場合、ウィンドウ境界はデフォルトで次のようになります。

ウィンドウ指定に ORDER BY 句が含まれている場合は、RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW を指定したことと同義になります。
ウィンドウ指定に ORDER BY 句が含まれていない場合は、ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING を指定したことと同義になります。

次の表は、ウィンドウ境界の例とウィンドウに含まれるローの説明を示したものです。

指定	意味
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW	分割の先頭から開始し、現在のローで終了します。累積の結果 (累積合計など) を計算する場合に使用してください。
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING	分割内のすべてのローを使用します。分割の各ローに対して集合関数の値を同一にする場合に使用してください。
ROWS BETWEEN x PRECEDING AND y FOLLOWING	現在のローから x の距離にある開始ローと y の距離にある終了ロー (境界値を含む) からなる固定サイズの移動するウィンドウを作成します。移動平均を計算する場合や隣接するロー間の値の差分を計算する場合には、この例を使用してください。複数のローからなる移動するウィンドウでは、分割の最初のローや最後のローを計算するときに NULL になります。これは、現在のローが分割のまさに最初または最後のローである場合に、計算で使用できる直前または直後のローが存在しないためです。そのため、代わりに NULL 値が使用されます。
ROWS BETWEEN CURRENT ROW AND CURRENT ROW	1 つのロー (現在のロー) からなるウィンドウ。
RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING	ロー内の値に基づいてウィンドウを作成します。たとえば、現在のローに対して、ORDER BY 句に指定されたカラムに値 10 が含まれているとします。ウィンドウのサイズを `RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING` と指定した場合、最初のローにはそのカラムに 5、最後のローにはそのカラムに 15 がそれぞれ含まれるのに必要な大きさとして、ウィンドウのサイズを指定します。ウィンドウが分割を移動すると、範囲仕様を満たすのに必要なサイズに応じて、ウィンドウのサイズが変化します。

ウィンドウ指定はできるかぎり明示的にしてください。明示的に指定しないと、デフォルトが予期した結果を返さない場合があります。

連続でない値の場合は、RANGE 句を使用して、Window 関数の入力の差に起因する問題を回避します。RANGE 句を使用してウィンドウ境界が設定された場合、データベースサーバは隣接するローや重複する値を含むローを自動的に処理します。

RANGE では、符号なし整数値を使用します。ORDER BY 式のドメインと RANGE 句で指定した値のドメインに応じて、範囲式のトランケーションが発生することがあります。

ランキング関数やロー番号付け関数を使用するときは、ウィンドウの境界を指定しないでください。

DocCommentXchange で意見交換できます