分析関数の説明

分析関数と集約関数はどちらも、行セット（一連の行の集合）に対して集計計算を行います。違いは、集約関数が各グループごとに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 は各行に対して同じ結果を返します。

• ROW_NUMBER は、order_by_clause に基づく値があっても、各行に対して一意の値を割り当てます。この値は行の処理順序に基づいており、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 です。