SELECT-OVER句（Transact-SQL）

• 2017年8月11日
• 読むのに17分
• • V
  • L
  • c
  • j
  • M
  • +11

適用対象： SQL Server（すべてサポートされているバージョン） AzureSQLデータベース AzureSQLマネージドインスタンス Azure Synapse Analytics 並列データウェアハウス

関連するウィンドウ関数が適用される前に、行セットのパーティション分割と順序を決定します。つまり、OVER句は、クエリ結果セット内のウィンドウまたはユーザー指定の行セットを定義します。次に、ウィンドウ関数はウィンドウ内の各行の値を計算します。関数でOVER句を使用して、移動平均、累積集計、現在の合計、グループごとの上位Nなどの集計値を計算できます。

• ランキング関数

• 集計関数

• 分析関数

• 関数の次の値

Transact-SQL構文規則

構文

引数

ウィンドウ関数のOVER句には次の引数が含まれる場合があります。

• クエリ結果セットをパーティションに分割するPARTITIONBY。
• ORDER BYは、結果セットの各パーティション内の行の論理的な順序を定義します。
• ROWS / RANGEは、パーティション内の開始点と終了点を指定することにより、パーティション内の行を制限します。 ORDER BY引数が必要であり、ORDER BY引数が指定されている場合、デフォルト値はパーティションの先頭から現在の要素までです。

引数を指定しない場合、ウィンドウ関数は結果セット全体に適用されます。

object_id	min	max
3	3	2139154666
5	3	2139154666
…	…	…
2123154609	3	2139154666
2139154666	3	2139154666

PARTITION BY

クエリ結果セットをパーティションに分割します。ウィンドウ関数は各パーティションに個別に適用され、各パーティションの計算が再開されます。

PARTITION BYが指定されていない場合、関数はクエリ結果セットのすべての行を単一のパーティションとして扱います。関数はすべての行に適用されます。 ORDER BY句を指定しない場合はパーティション内。

PARTITION BY value_expression

行セットをパーティション化する列を指定します。 value_expressionは、FROM句によって使用可能になった列のみを参照できます。 value_expressionは、選択リスト内の式またはエイリアスを参照できません。 value_expressionには、列式、スカラーサブクエリ、スカラー関数、またはユーザー定義変数を指定できます。

ORDER BY

結果セットの各パーティション内の行の論理的な順序を定義します。つまり、ウィンドウ関数の計算が実行される論理的な順序を指定します。

• 指定されていない場合、デフォルトの順序はASCであり、ウィンドウ関数はパーティション内のすべての行を使用します。
• 指定されていて、ROWS / RANGEが指定されていない場合、デフォルトのRANGE UNBOUNDED PRECEDING AND CURRENT ROWがウィンドウのデフォルトとして使用されます。オプションのROWS / RANGE指定を受け入れることができる関数（たとえば、minまたはmax）によるフレーム。

order_by_expression
並べ替える列または式を指定します。 order_by_expressionは、FROM句によって使用可能になった列のみを参照できます。列名またはエイリアスを表す整数を指定することはできません。

COLLATEcollation_name
collation_nameで指定された照合に従ってORDERBY操作を実行する必要があることを指定します。 collation_nameは、Windows照合名またはSQL照合名のいずれかです。詳細については、照合とUnicodeのサポートを参照してください。 COLLATEは、char、varchar、nchar、およびnvarchar型の列にのみ適用できます。

ASC | DESC
指定された列の値を昇順または降順で並べ替える必要があることを指定します。 ASCはデフォルトのソート順です。ヌル値は可能な限り低い値として扱われます。

ROWSまたはRANGE

適用対象：SQL Server 2012（11.x）以降。

その他の制限パーティション内の開始点と終了点を指定することにより、パーティション内の行。これは、論理的関連付けまたは物理的関連付けのいずれかによって、現在の行に対して行の範囲を指定することによって行われます。物理的な関連付けは、ROWS句を使用して実現されます。

ROWS句は、現在の行の前後に固定数の行を指定することにより、パーティション内の行を制限します。または、RANGE句は、現在の行の値に対して値の範囲を指定することにより、パーティション内の行を論理的に制限します。前後の行は、ORDERBY句の順序に基づいて定義されます。ウィンドウフレーム「RANGE … CURRENT ROW …」には、ORDERBY式に現在の行と同じ値を持つすべての行が含まれます。たとえば、ROWS BETWEEN 2 PRECEDING AND CURRENT ROWは、関数が操作する行のウィンドウのサイズが3行であることを意味し、現在の行までの2行から始まります。

UNBOUNDED PRECEDING

適用対象：SQL Server 2012（11.x）以降。

ウィンドウがパーティションの最初の行から始まることを指定します。 UNBOUNDED PRECEDINGは、ウィンドウの開始点としてのみ指定できます。

<符号なしの値の指定> PRECEDING
指定<符号なし値の指定>を使用して、現在の行の前にある行または値の数を示します。この指定はRANGEでは許可されていません。

CURRENT ROW

適用対象：SQL Server 2012（11.x）以降。

ウィンドウが開始することを指定します。または、ROWSで使用する場合は現在の行で終了し、RANGEで使用する場合は現在の値で終了します。 CURRENT ROWは、開始点と終了点の両方として指定できます。

BETWEEN AND

適用対象：SQL Server 2012（11.x）以降。

ROWSまたはRANGEのいずれかとともに使用して、ウィンドウの下限（開始）と上限（終了）の境界点を指定します。 <ウィンドウフレーム境界>は境界の開始点を定義し、<ウィンドウフレーム境界>は境界の終点を定義します。上限を下限より小さくすることはできません。

UNBOUNDED FOLLOWING

適用対象：SQL Server 2012（11.x）以降。

指定します。ウィンドウはパーティションの最後の行で終了します。 UNBOUNDED FOLLOWINGは、ウィンドウの終点としてのみ指定できます。たとえば、RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWINGは、現在の行で始まり、パーティションの最後の行で終わるウィンドウを定義します。

<符号なしの値の指定>以下
<符号なしの値の指定>で指定現在の行に続く行または値。 <符号なし値の指定> FOLLOWINGがウィンドウの開始点として指定されている場合、終了点は<符号なしの値の指定>以下。たとえば、ROWS BETWEEN 2FOLLOWINGと10FOLLOWINGは、現在の行に続く2番目の行で始まり、現在の行に続く10番目の行で終わるウィンドウを定義します。この指定はRANGEでは許可されていません。

符号なし整数リテラル
適用対象：SQL Server 2012（11.x）以降。

正の整数リテラル（0を含む）です。 ）現在の行または値の前後にある行または値の数を指定します。この仕様はROWSにのみ有効です。

一般的な注意事項

1つのFROM句を使用して1つのクエリで複数のウィンドウ関数を使用できます。各関数のOVER句は、パーティション分割と順序が異なる場合があります。

PARTITION BYが指定されていない場合、関数はクエリ結果セットのすべての行を単一のグループとして扱います。

重要！

ROWS / RANGEが指定され、<の前のウィンドウフレームが>に使用されている場合<ウィンドウフレーム範囲>（短い構文）この場合、この指定はウィンドウフレーム境界の開始点に使用され、CURRENTROWは境界の終了点に使用されます。たとえば、「ROWS5PRECEDING」は「ROWSBETWEEN5 PRECEDING ANDCURRENTROW」と同じです。

制限と制限

OVER句はCHECKSUM集計関数では使用できません。

RANGEは<符号なし値の指定> PRECEDINGまたは<符号なし値の指定>フォロー中。

ランキング、集計、または分析によって異なります。 OVER句、< ORDERBY句>および/または<で使用される関数ROWSおよびRANGE句>はサポートされていない可能性があります。

例

A。 ROW_NUMBER関数でのOVER句の使用

次の例は、ROW_NUMBER関数でOVER句を使用して、パーティション内の各行の行番号を表示する方法を示しています。 OVER句で指定されたORDERBY句は、各パーティションの行を列SalesYTDで並べ替えます。 SELECTステートメントのORDERBY句は、クエリ結果セット全体が返される順序を決定します。

これが結果セットです。

B。集計関数でのOVER句の使用

次の例では、クエリによって返されるすべての行で集計関数を使用してOVER句を使用します。この例では、OVER句を使用すると、サブクエリを使用して集計値を導出するよりも効率的です。

結果セットは次のとおりです。

次の例は、計算値の集計関数でOVER句を使用する方法を示しています。

これが結果セットです。集計はSalesOrderIDによって計算され、Percent by ProductIDは各SalesOrderID。

C。移動平均と累積合計の生成

次の例では、OVER句を指定したAVG関数とSUM関数を使用して、テーブル。データはTerritoryIDによって分割され、SalesYTDによって論理的に順序付けられます。これは、AVG関数が、販売年に基づいて地域ごとに計算されることを意味します。 TerritoryID 1の場合、2005年の販売には、その年に販売を行った2人の営業担当者を表す2つの行があることに注意してください。これら2つの行の平均売上が計算され、2006年の売上を表す3番目の行が計算に含まれます。

これが結果セットです。

この例では、 OVER句にはPARTITIONBYは含まれません。これは、クエリによって返されるすべての行に関数が適用されることを意味します。 OVER句で指定されたORDERBY句は、AVG関数が適用される論理的な順序を決定します。クエリは、WHERE句で指定されたすべての販売地域の年間売上の移動平均を返します。 SELECTステートメントで指定されたORDERBY句は、クエリの行が表示される順序を決定します。

結果セットは次のとおりです。

D。 ROWS句の指定

適用対象：SQL Server 2012（11.x）以降

次の例では、ROWS句を使用して、行が次のように計算されるウィンドウを定義します。現在の行とそれに続くN行（この例では1行）。

結果セットは次のとおりです。

次の例では、ROWS句はUNBOUNDEDPRECEDINGで指定されています。その結果、ウィンドウはパーティションの最初の行から始まります。

これが結果セットです。

例：並列データウェアハウス

E。 ROW_NUMBER関数でOVER句を使用する

次の例では、割り当てられた販売割り当てに基づいて営業担当者のROW_NUMBERを返します。

これは部分的な結果セットです。

F。集計関数でのOVER句の使用

次の例は、集計関数でのOVER句の使用を示しています。この例では、OVER句を使用すると、サブクエリを使用するよりも効率的です。

結果セットは次のとおりです。

次の例は、計算値の集計関数でOVER句を使用する方法を示しています。集計はSalesOrderNumberによって計算され、総販売注文の割合は各SalesOrderNumberの各行について計算されることに注意してください。

この結果セットの最初の開始点は次のとおりです。

関連項目

集計関数（Transact-SQL）
分析関数（Transact-SQL）
Itzik Ben-Ganによるsqlmag.comのウィンドウ関数とOVERに関する優れたブログ投稿