分析関数の説明

分析関数と集計関数はどちらも、行セットグループ（一連の行の集合）に対して集計計算を行います。違いとして、集計関数は各グループから1つの値（1行）しか返せませんが、分析関数は各グループから複数の値（複数行）を返すことができます。この行のグループはウィンドウ（Window）と呼ばれ、analytic_clauseによって定義されます。ウィンドウのサイズは実際の行数または論理的な間隔（例えば時間）によって決まり、グループ内の各行はウィンドウに基づく論理的な計算結果です。

分析関数をトリガーするには、ウィンドウを指定するための特別なキーワードOVERが必要です。ウィンドウには以下の3つの構成要素が含まれます：

• パーティション規範：入力行を異なるパーティションに分割するために使用されます。このプロセスはGROUP BY句による分割プロセスと似ています。

• ソート規範：分析関数内での入力データ行の実行順序を決定するために使用されます。

• ウィンドウ境界：データ計算のウィンドウ境界を指定します。デフォルト値はRANGE UNBOUNDED PRECEDINGです。この境界には、現在のパーティションの開始から現在の行までのすべてのデータが含まれます。

分析関数は、クエリ内で最後に実行される操作グループであり、最後のORDER BY句を除きます。分析関数を処理する前に、すべてのJOINおよびWHERE、GROUP BY、HAVING句を完了する必要があります。そのため、分析関数は選択リストまたはORDER BY句にのみ記述できます。

構文

analytic_function

analytic_functionは分析関数の名前を指定します。構文は以下のとおりです：

analytic_function([ arguments ]) OVER (analytic_clause)

分析関数は0～3個のargumentsパラメータを使用します。パラメータは任意の数値データ型、または暗黙的に数値データ型に変換できる任意の非数値データ型にすることができます。OceanBaseはデータ型の優先順位に基づいて最も高い数値優先順位を持つパラメータを決定し、残りのパラメータをこの最も高い数値優先順位を持つパラメータのデータ型に暗黙的に変換します。返り値の型も、個々の関数で特に指定されていない限り、最も高い数値優先順位を持つパラメータのデータ型になります。

analytic_clause

分析句（analytic_clause）は、OVER analytic_clauseを使用して関数がクエリ結果セット上で操作するよう指示します。この句はFROM、WHERE、GROUP BY、およびHAVING句の後に計算されます。この句を使用して、選択リストまたはORDER BY句で分析関数を指定できます。分析関数に基づくクエリ結果をフィルタリングする場合は、これらの関数を親クエリ内にネストしてから、ネストされたサブクエリの結果をフィルタリングします。構文は以下のとおりです：

[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]

query_partition_clause

パーティション句（query_partition_clause）は、PARTITION BY句を使用して、1つまたは複数のvalue_exprに基づくグループにクエリ結果セットを分割します。この句を省略した場合、関数はクエリ結果セットのすべての行を単一のグループと見なします。

同一クエリ内で複数の分析関数を指定でき、各関数はキーによって同じまたは異なるパーティションを持ちます。query_partition_clauseで分析関数を指定し、かつクエリ対象が並列属性を持つ場合、関数の計算も並列化されます。

value_exprの有効な値は、定数、列、非分析関数、関数式、またはそれらのいずれかに関連する式です。

構文は以下のとおりです：

PARTITION BY { expr[, expr ]... | ( expr[, expr ]... ) }

order_by_clause

ソート句 order_by_clause を使用して、パーティション内でデータをソートする方法を指定します。すべての分析関数について、複数のキーに基づくパーティションで値をソートできます。各キーは value_expr で定義され、ソート順序で制限されます。

各関数では、複数のソート式を指定できます。これは、値をソートする関数を使用する場合に特に役立ちます。

order_by_clause が複数の行に対して同じ値を生成する場合、関数の動作は次のとおりです：

• CUME_DIST、DENSE_RANK、NTILE、PERCENT_RANK、および RANK は各行に対して同じ結果を返します。

• ORDER BY に基づく値が1つある場合でも、ROW_NUMBER は各行に異なる値を割り当てます。この値は行の処理順序に基づいており、ORDER BY で全体的なソートが実現できない場合、その順序は不確定になる可能性があります。

• 他の分析関数の場合、その結果はウィンドウルールに依存します。RANGE キーワードを使用した論理ウィンドウを指定した場合、関数は各行に対して同じ結果を返します。ROWS キーワードを使用して物理ウィンドウを指定した場合、結果は不確定です。

構文は次のとおりです：

ORDER [ SIBLINGS ] BY{ expr | position | c_alias } [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, { expr | position | c_alias } [ ASC | DESC ][ NULLS FIRST | NULLS LAST ]]...

ASCまたはDESCキーワード

ソート順序を指定します。ASC は昇順を、DESC は降順を意味します。デフォルトは昇順（ASC）です。

NULLS FIRSTまたはNULLS LASTキーワード

order_by_clause の nulls first と nulls last について、nulls first はソート時に NULL 値を最小値として扱うことを意味し、nulls last はソート時に NULL 値を最大値として扱うことを意味します。

ORDER BY句の制限

ORDER BY 句の使用には、以下の制限があります：

• 分析関数では、order_by_clause は式（expr）を使用しなければなりません。SIBLINGS キーワードは無効です（階層クエリにのみ関連）。位置（position）と列エイリアス（c_alias）も無効です。それ以外の場合、この order_by_clause はクエリ全体またはサブクエリのソートコマンドと同じです。

• RANGE キーワードを使用した分析関数は、その関数の ORDER BY 句で複数のソートキーを使用できます。次のウィンドウを指定する必要があります：

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW（略称 RANGE UNBOUNDED PRECEDING)
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
  • RANGE BETWEEN CURRENT ROW AND CURRENT ROW
  • RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

上記の4つのウィンドウに加えて、ウィンドウ境界は分析関数の ORDER BY 句で1つのソートキーしか持てません。この制限は、ROW キーワードで指定されたウィンドウ境界には適用されません。

windowing_clause

一部の分析関数では、windowing_clause を使用できます。構文は次のとおりです：

{ ROWS | RANGE } { BETWEEN { UNBOUNDED PRECEDING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } AND{ UNBOUNDED FOLLOWING | CURRENT ROW | value_expr { PRECEDING | FOLLOWING } } | { UNBOUNDED PRECEDING | CURRENT ROW| value_expr PRECEDING}}

ROWSまたはRANGEキーワード

これらのキーワードは、各行に関数結果を計算するためのウィンドウを定義し、その後その関数をウィンドウ内のすべての行に適用します。ウィンドウは、クエリ結果セットまたはパーティションを上から下へと移動します。ウィンドウは FRAME とも呼ばれ、OceanBaseデータベースは以下のウィンドウステートメントをサポートしています：

• ROWS：物理単位（行）でウィンドウを指定します。

• RANGE：ウィンドウを論理オフセットとして指定します。デフォルトの方法は RANGE UNBOUNDED PRECEDING です。

分析関数で windowing_clause を使用する場合は、order_by_clause も追加する必要があります。windowing_clause が RANGE 句でウィンドウ境界を定義する場合、order_by_clause では1つの式しか指定できません。詳細については、ORDER BY句の制限 を参照してください。

論理オフセットを持つ分析関数が返す値は常に決定的です。しかし、物理オフセットを持つ分析関数が返す値は不確定な結果を生じる可能性があります。ソート式が一意のソートを返すことで、物理オフセットを持つ分析関数が決定的な値を返すようになるため、order_by_clause では複数の列を指定して一意のソートを実現する必要があります。

BETWEEN ... ANDキーワード

BETWEEN ... AND 句を使用して、ウィンドウの開始点と終了点を指定します。最初の式（AND の前）が開始点を定義し、2 番目の式（AND の後）が終了点を定義します。BETWEEN を省略して終了点のみを指定した場合、OceanBaseデータベースはそれを開始点と見なし、終了点はデフォルトで現在の行になります。

UNBOUNDED PRECEDINGキーワード

UNBOUNDED PRECEDING は、ウィンドウがパーティションの最初の行から始まることを示します。これは開始点に使用され、終了点には使用できません。

UNBOUNDED FOLLOWINGキーワード

UNBOUNDED FOLLOWING は、ウィンドウがパーティションの最後の行で終了することを示します。これは終了点に使用され、開始点には使用できません。

CURRENT ROWキーワード

開始点として、CURRENT ROW はウィンドウが現在の行または現在の値から始まることを指定します（それぞれ ROW または RANGE が指定されているかどうかによって異なります）。この場合、終了点は value_expr PRECEDING にすることはできません。終了点として、CURRENT ROW はウィンドウが現在の行または値で終了することを指定します（それぞれ ROW または RANGE が指定されているかどうかによって異なります）。この場合、開始点は value_expr FOLLOWING にすることはできません。

value_expr PRECEDINGまたはvalue_expr FOLLOWINGキーワード

• value_expr FOLLOWING が開始点の場合、終了点は value_expr FOLLOWING でなければなりません。

• value_expr PRECEDING が終了点の場合、開始点は value_expr PRECEDING でなければなりません。

時間間隔によって定義される数値形式の論理ウィンドウを定義する場合は、変換関数を使用する必要がある場合があります。

ROWS を指定した場合：

• value_expr が物理オフセットである場合、それは定数または式でなければならず、正の数として計算されなければなりません。

• value_expr が開始点の一部である場合、それは開始点と終了点の前の部分を1行として計算しなければなりません。

RANGE を指定した場合：

• value_expr が論理オフセットである場合、それは定数または式でなければならず、その結果は正の数値または間隔リテラルでなければなりません。

• order_by_clause では1つの式しか指定できません。

• value_expr が数値の場合、ORDER BY expr は数値または DATE データ型でなければなりません。

• value_expr が間隔値の場合、ORDER BY expr は DATE データ型でなければなりません。windowing_clause を完全に省略した場合、デフォルト値は RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW になります。