OVER句は、SQL Server2005でSQLServerの「帰り道」に追加され、SQLで拡張されました。 Server2012。主に「ウィンドウ関数」で使用されます。唯一の例外は、シーケンス関数NEXT VALUEFORです。 OVER句は、クエリのどの行が関数に適用されるか、それらがその関数によって評価される順序、および関数の計算をいつ再開するかを決定するために使用されます。これは他の関数と組み合わせて使用され、この記事では特にOVER句についてのみ説明しているため、これらの関数については、示されている例のOVER句に関連する場合にのみ説明します。

OVER句は次のとおりです。

<function> OVER ( )

構文を見ると、すべてのサブ句はオプションであるように見えます。実際、OVER句を使用できる各関数によって、許可されるサブ句と必須のサブ句が決まります。使用する関数によっては、OVER句自体がオプションの場合があります。この記事の最後に、OVER句のどの部分を許可/要求する関数を示すグラフがあります。

PARTITION BY句は、クエリの結果セットをデータサブセットに分割するために使用されます。パーティション。 PARTITION BY句が使用されていない場合、クエリの結果セット全体が使用されるパーティションになります。使用されているウィンドウ関数は各パーティションに個別に適用され、関数が実行する計算はパーティションごとに再開されます。クエリを分割するパーティションを決定する値のセットを定義します。これらの値は、列、スカラー関数、スカラーサブクエリ、または変数です。

たとえば、次のクエリを調べてみましょう。

SELECT COUNT(*)FROM .sys.indexes;

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

これは、クエリによって返される行数です。この場合は、 msdbデータベース内のインデックスの数。次に、このクエリにOVER句を追加しましょう。

SELECT object_id, index_id, COUNT(*) OVER ()FROM .sys.indexes;

要約された結果は次のとおりです。

このクエリは、各インデックスのobject_idとindex_id、および結果セット内のインデックスの総数を返します。 PARTITION BY句が使用されなかったため、結果セット全体が単一のパーティションとして扱われました。次に、PARTITION BY句を追加して、結果がどのように変化するかを確認します。

SELECT object_id, index_id, COUNT(*) OVER (PARTITION BY object_id)FROM .sys.indexes;

簡略化された結果は次のとおりです。

このクエリは各インデックスの行を返しますが、クエリはobject_id列のPARTITION BY句を指定するため、count関数はインデックスの数を返します。その特定のobject_idに。 ORDER BY句は、行が関数によって評価される順序を制御します。これはまもなく示されます。 ROWSまたはRANGE句は、関数に適用されるパーティション内の行のサブセットを決定します。 ROWSまたはRANGEを使用する場合は、ウィンドウの開始点と終了点を指定します。許可される値は次のとおりです。

ウィンドウを指定するための構文は2つあります。

BETWEEN <beginning frame> AND <ending frame><beginning frame>

開始フレームのみが指定されている場合、デフォルトの終了フレームはCURRENTROWです。

UNBOUNDEDキーワードは、パーティションの開始（PRECEDINGの場合）または終了を指定します。パーティション（フォロー用）。 CURRENT ROWは、使用されているウィンドウフレームの位置に応じて、現在の行がウィンドウの開始または終了のいずれかであることを指定します。「N」は、現在の行の前の行数を指定します（PRECEDINGの場合）。 ）、またはウィンドウフレームに使用する現在の行（FOLLOWINGの場合）の後。

有効なウィンドウ仕様は次のとおりです。

-- specifies the entire result set from the partitionBETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING -- specifies 5 rows, starting 4 rows prior to the current row through the current row from the partitionBETWEEN 4 PRECEDING AND CURRENT ROW-- specifies all of the rows from the current row to the end of the partitionBETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING-- specifies all of the rows from the start of the partition through the current rowUNBOUNDED PRECEDING

ROWSまたはRANGE句を使用するには、ORDER BY句も指定する必要があります。逆に、ORDER BY句を使用し、ROWSまたはRANGE句を指定しない場合、デフォルトのRANGE BETWEEN UNBOUNDED PRECEDING ANDCURRENT ROWが使用されます。

ORDER BY句とROWS句またはRANGE句を示すために、いくつかのテストデータを作成しましょう：2つのアカウント、アカウントごとに4つの日付、および各日付の金額。クエリには、これらの両方が表示されます。さまざまな方法で使用されている句：

このクエリは次の結果セットを返します：

” RowNbr」列はusinですg COUNT関数を使用して、パーティション内の行数を返します。パーティションはTranDateによって順序付けられ、パーティションの先頭から現在の行までのすべての行のウィンドウフレームを指定しています。最初の行では、ウィンドウフレームに1行しかないため、値「1」が返されます。2番目の行では、ウィンドウフレームに2行あるため、値「2」が返されます。このアカウントの残りの行も同様です。

PARTITION BY句はアカウントを指定するため、アカウントが変更されると、関数の計算がリセットされます。これは、結果セットの2番目のアカウントの行を調べることで確認できます。これは「実行中」の集計の例であり、集計は以前の計算に基づいて構築されます。これを使用する場合の例は、各トランザクションの後に銀行口座の残高を計算する場合です（別名、現在の合計）。

「DateCount」列は、日付で分割された行数のカウントを実行しています。この例では、各アカウントには同じ4つの日付のそれぞれにトランザクションがあるため、各日付には2つのトランザクションがあります（アカウントごとに1つ）。これにより、行ごとに値「2」が返されます。これは、日付にGROUP BYを使用してカウントを実行するのと似ていますが、日付ごとに1回だけではなく、行ごとに合計が返される点が異なります。例このメソッドを使用する場合は、「Yの行X」を表示するか、現在の行の合計に対する割合を計算します。

「Last2Count」列は、行のカウントを実行します。パーティション内で、現在の行とその直前の1行について。各アカウントの最初の行については、その前に行がないため、値「1」が返されます。各アカウントの残りの行については、「2」の値が返されます。これは、「移動」または「スライド」集計の例です。この方法を使用する場合の例は、に基づいてボーナスを計算することです。過去2か月の売上。

ここでは、ROWS句のみを示しました。RANGE句も同様に機能しますが、行を位置的に処理するのではなく、その行によって返される値。NPRECEDING/ FOLLOWING句を使用できないのは定位置ではないためです。同じクエリで両方を使用して、ROWSとRANGEの違いを簡単に見てみましょう。人のリスト（DBAと呼びましょう）とその時給。行IDが4 & 5、12 &の行に注意してください。 13のレートは同じです。クエリは2回レートを合計します。1回はROWSを使用し、もう1回はRANGEを使用します。

このクエリは次の結果セットを生成します：

SumByRows列とSumByRange列の両方で、OVER句はROWS / RANGE句を除いて同じです。終了範囲が指定されていないため、デフォルトではCURRENTROWを使用することにも注意してください。結果セットの最初から現在の行までの給与を合計しているため、実際に計算しているのは、[給与]列の現在の合計です。 SumByRows列では、値はROWS句を使用して計算され、現在の行の合計が現在の行の給与と前の行の合計であることがわかります。ただし、RANGE句はSalary列の値に基づいて機能するため、給与が同じかそれより低いすべての行を合計します。これにより、SumByRange値は、同じ給与のすべての行で同じ値になります。

1つの重要な注意：OVER句のORDER BY句は、パーティション内の行が使用される順序のみを制御します。ウィンドウ関数による。最終結果セットの順序は制御されません。クエリ自体にORDERBY句がないと、行の順序は保証されません。クエリが最後に指定されたOVER句の順序で返される場合があります。これは、これが現在SQLServerに実装されている方法によるものです。 MicrosoftのSQLServerチームが動作方法を変更すると、現在観察している方法で結果を並べ替えることができなくなる可能性があります。結果セットに特定の順序が必要な場合は、クエリ自体に対してORDERBY句を指定する必要があります。

最後に、OVER句を使用できるさまざまな関数のグラフと、条項のどの部分が許可/必須/オプションです。

R-必須、O-オプション、X-許可されていません