分析ファンクション

分析ファンクションは、行のグループに基づいて集計値を計算します。各グループに対して複数の行を戻す点で、集計ファンクションと異なります。行のグループをウィンドウといい、analytic_clauseで定義されます。各行に対して、行のスライディング・ウィンドウが定義されます。このウィンドウによって、カレント行の計算に使用される行の範囲が決定されます。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。

分析ファンクションは、問合せで最後に実行される演算(最後のORDER BY句を除く)の集合です。すべての結合およびすべてのWHERE、GROUP BYおよびHAVING句は、分析ファンクションが処理される前に実行されます。そのため、分析ファンクションは、SELECT構文のリストまたはORDER BY句のみに指定できます。

通常、分析ファンクションは、累積集計、移動集計、センター集計およびレポート集計の実行に使用されます。

analytic_function::=

図「analytic_function.gif」の説明

analytic_clause::=

図「analytic_clause.gif」の説明

query_partition_clause::=

図「query_partition_clause.gif」の説明

order_by_clause::=

図「order_by_clause.gif」の説明

windowing_clause ::=

図「windowing_clause.gif」の説明

次に、この構文のセマンティクスを示します。

analytic_function

分析ファンクションの名前を指定します(セマンティクスの説明の後に示す分析ファンクションのリストを参照)。

引数

分析ファンクションには引数を0から3個指定します。引数には、任意の数値データ型、または暗黙的に数値データ型に変換可能な数値以外のデータ型を指定できます。Oracleは、数値の優先順位が最も高い引数を判断し、残りの引数をそのデータ型に暗黙的に変換します。個々のファンクションに特に指定がないかぎり、戻り型もその引数のデータ型となります。

analytic_clause

OVER analytic_clause句は、ファンクションが問合せ結果セットを操作することを示します。この句は、FROM、WHERE、GROUP BYおよびHAVING句の後に計算されます。SELECT構文のリストのこの句またはORDER BY句に分析ファンクションを指定できます。分析ファンクションに基づいて、問合せの結果をフィルタするには、これらのファンクションを親問合せ内でネストした後、ネストされた副問合せの結果をフィルタします。

analytic_clauseの注意事項: analytic_clauseには、次の注意事項があります。

• analytic_clauseのどの部分にも、分析ファンクションを指定して分析ファンクションをネストできません。ただし、副問合せで分析ファンクションを指定して、別の分析ファンクションを計算することはできます。

• OVER analytic_clauseには、組込み分析ファンクションと同様に、ユーザー定義の分析ファンクションを指定できます。「CREATE FUNCTION」を参照してください。

query_partition_clause

PARTITION BY句を使用すると、1つ以上のvalue_exprに基づいて、問合せ結果セットをグループに分割できます。この句を省略すると、ファンクションは問合せ結果セットのすべての行を単一のグループとして扱います。

分析ファンクションでquery_partition_clauseを使用するには、構文の上位ブランチ(カッコなし)を使用します。この句をモデルの問合せ(model_column_clauses内)またはパーティション化された外部結合(outer_join_clause内)で使用するには、構文の下位ブランチ(カッコ付き)を使用します。

同じまたは異なるPARTITION BYキーで、同じ問合せに複数の分析ファンクションを指定できます。

問い合せているオブジェクトにパラレル属性があり、query_partition_clauseで分析ファンクションを指定する場合は、ファンクションの計算もパラレル化されます。

有効な値のvalue_exprは、定数、列、非分析ファンクション、ファンクション式、またはこれらのいずれかを含む式です。

order_by_clause

order_by_clauseを使用すると、パーティション内でのデータの順序付け方法を指定できます。すべての分析ファンクションに対して、各キーがvalue_exprで定義され、順番付け順序で修飾された複数キーのパーティション内での値を順番付けできます。

各ファンクションには、複数の順序式を指定できます。これは、2番目の式が最初の式にある同一値との間の関連性を変換できるため、値をランク付けするファンクションを使用する場合に特に有効です。

order_by_clauseの結果が複数行に対して同一値になる場合は常に、ファンクションは次のように動作します。

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

• ROW_NUMBERは、order_by_clauseに基づいた同順位がある場合でも、各行に異なる値を割り当てます。この値は行が処理される順序に基づくため、ORDER BYによって全体的な順序が保証されていない場合には非決定的になることがあります。

• 他のすべての分析ファンクションでは、ウィンドウの指定によって結果が異なります。RANGEキーワードを使用して論理ウィンドウを指定する場合、ファンクションは各行に同じ結果を戻します。ROWSキーワードを使用して物理ウィンドウを指定する場合、結果は非決定的になります。

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キーワードで指定したウィンドウ境界には適用されません。

ASC | DESC 順番付け順序(昇順または降順)を指定します。デフォルトはASCです。

NULLS FIRST | NULLS LAST NULL値を含む戻された行が順序の最初にくるか、最後にくるかを指定します。

NULLS LASTは昇順のデフォルトで、NULLS FIRSTは降順のデフォルトです。

分析ファンクションは、常に、ファンクションのorder_by_clauseで指定された順序で行を操作します。ただし、ファンクションのorder_by_clauseは結果の順序を保証しません。最終結果の順序を保証するには、問合せのorder_by_clauseを使用してください。

windowing_clause

一部の分析ファンクションでは、windowing_clauseを使用できます。7-16ページに示す分析ファンクションのリストでは、windowing_clauseを使用できるファンクションにアスタリスク(*)が付いています。

ROWS | RANGE これらのキーワードは、各行に対して、ファンクションの結果の計算に使用されるウィンドウ(行の物理集合または論理集合)を定義します。ファンクションは、ウィンドウのすべての行に適用されます。ウィンドウは、問合せ結果セット内またはパーティションの上から下まで移動します。

• ROWSは、物理単位(行)でウィンドウを指定します。

• RANGEは、論理オフセットとしてウィンドウを指定します。

order_by_clauseを指定しないと、この句を指定できません。RANGE句で定義したウィンドウ境界には、order_by_clauseで指定できる式が1つのみのものもあります。「ORDER BY句の制限事項:」を参照してください。

分析ファンクションが論理オフセットで戻す値は、常に決定的なものです。ただし、分析ファンクションが物理オフセットで戻す値は、順序式の結果が一意の順序にならないかぎり、非決定的な結果を生成することがあります。order_by_clauseに複数の列を指定して、結果の順序を一意にする必要があります。

BETWEEN ... AND BETWEEN ... AND句を使用すると、ウィンドウにスタート・ポイントおよびエンド・ポイントを指定できます。最初の式(ANDの前)はスタート・ポイントを定義し、2番目の式(ANDの後)はエンド・ポイントを定義します。

BETWEENを省略してエンド・ポイントを1つのみ指定すると、Oracleはそれをスタート・ポイントとみなし、デフォルトでカレント行をエンド・ポイントに指定します。

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 RANGEまたはROWに対して、次のことがいえます。

• value_expr FOLLOWINGがスタート・ポイントの場合、エンド・ポイントはvalue_expr FOLLOWINGである必要があります。

• value_expr PRECEDINGがエンド・ポイントの場合、スタート・ポイントはvalue_expr PRECEDINGである必要があります。

数値形式の時間間隔で定義されている論理ウィンドウを定義する場合、変換ファンクションを使用する必要があります。

ROWSを指定した場合、次のことがいえます。

• value_exprは物理オフセットになります。これは定数または式であり、正数値に評価する必要があります。

• value_exprがスタート・ポイントの一部の場合、エンド・ポイントの前にある行に評価する必要があります。

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になります。

分析ファンクションは、通常、データ・ウェアハウス環境で使用されます。次に示す分析ファンクションのリストでは、windowing_clauseを含む完全な構文を使用できるファンクションには、アスタリスク(*)が付いています。

AVG *
CORR *
COUNT *
COVAR_POP *
COVAR_SAMP *
CUME_DIST
DENSE_RANK
FIRST
FIRST_VALUE *
LAG
LAST
LAST_VALUE *
LEAD
LISTAGG
MAX *
MEDIAN
MIN *
NTH_VALUE *
NTILE
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
RANK
RATIO_TO_REPORT
REGR_ (Linear Regression) Functions *
ROW_NUMBER
STDDEV *
STDDEV_POP *
STDDEV_SAMP *
SUM *
VAR_POP *
VAR_SAMP *
VARIANCE *