分析ファンクション
分析ファンクションは、行のグループに基づいて集計値を計算します。各グループに対して複数の行を戻す点で、集計ファンクションと異なります。行のグループをウィンドウといい、analytic_clause
で定義されます。各行に対して、行のスライディング・ウィンドウが定義されます。このウィンドウによって、カレント行の計算に使用される行の範囲が決定されます。ウィンドウの大きさは、行の物理数値または時間などのロジカル・インターバルに基づきます。
分析ファンクションは、問合せで最後に実行される演算(最後のORDER
BY
句を除く)の集合です。すべての結合およびすべてのWHERE
、GROUP
BY
およびHAVING
句は、分析ファンクションが処理される前に実行されます。そのため、分析ファンクションは、SELECT構文のリストまたはORDER
BY
句のみに指定できます。
通常、分析ファンクションは、累積集計、移動集計、センター集計およびレポート集計の実行に使用されます。
analytic_function::=
analytic_clause::=
query_partition_clause::=
order_by_clause::=
windowing_clause::=
次に、この構文のセマンティクスを示します。
analytic_function
分析ファンクションの名前を指定します(セマンティクスの説明の後に示す分析ファンクションのリストを参照)。
arguments
分析ファンクションには引数を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」を参照してください。 -
analytic_clause
でのPARTITION
BY
句およびORDER
BY
句は照合依存です。
関連項目:
-
分析ファンクションの
OVER
(PARTITION
BY
...ORDER
BY
...)
句の照合決定ルールは、『Oracle Databaseグローバリゼーション・サポート・ガイド』の付録Cを参照してください。
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
を使用してください。
関連項目:
この句の詳細は、「SELECT」の「order_by_clause」を参照してください。
windowing_clause
一部の分析ファンクションでは、windowing_clause
を使用できます。7-16ページに示す分析ファンクションのリストでは、windowing_clause
を使用できるファンクションにアスタリスク(*)が付いています。
ROWS | RANGE | GROUPS
キーワードROWS
、RANGE
およびGROUPS
は、ファンクションの結果の計算に使用されるウィンドウ・フレーム・ユニットを定義するオプションです。ファンクションは、ウィンドウのすべての行に適用されます。ウィンドウは、問合せ結果セット内またはパーティションの上から下まで移動します。
-
ROWS
を使用すると、現在の行から行を前後にカウントすることによって、ウィンドウ・フレーム範囲を指定できます。ROWS
では、任意の数のソート・キー、または任意の順序のデータ型を指定できます。 -
RANGE
を使用すると、ウィンドウ・フレーム範囲を論理オブジェクトとして指定できます。RANGE
では1つのソート・キーのみを指定でき、その宣言データ型は加算および減算が可能である必要があります。たとえば、数値、日時または期間のデータ型である必要があります。 -
GROUPS
を使用すると、ROWS
およびRANGE
の両方の特性を持つウィンドウ・フレーム範囲を指定できます。ROWS
と同様に、GROUPS
ウィンドウも任意の数のソート・キーまたは任意の順序の型を持つことができます。RANGE
と同様に、GROUPS
ウィンドウでも、ソート・キーに同じ値を持つ隣接行の間でのカットオフは実行されません。
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
である必要があります。
数値形式の時間間隔で定義されている論理ウィンドウを定義する場合、変換ファンクションを使用する必要があります。
関連項目:
数値時間から間隔への変換の詳細は、「NUMTOYMINTERVAL」および「NUMTODSINTERVAL」を参照してください。
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
になります。
EXCLUDE
EXCLUDE
オプションを指定して、ウィンドウ・フレームから行、グループおよびタイを削除できます。
-
EXCLUDE CURRENT ROW
を指定し、現在の行がウィンドウ・フレームにある場合、現在の行がウィンドウ・フレームから削除されます。 -
EXCLUDE GROUP
を指定した場合、現在の行および現在の行のピアがウィンドウ・フレームから削除されます。 -
EXCLUDE TIES
を指定した場合、現在の行のピアがウィンドウ・フレームから削除されます。現在の行は保持されます。現在の行が以前にウィンドウ・フレームから削除されている場合、削除されたままになります。 -
EXCLUDE NO OTHERS
を指定した場合、ウィンドウ・フレームからその他の行は削除されません。このオプションがデフォルトです。
分析ファンクションは、通常、データ・ウェアハウス環境で使用されます。次に示す分析ファンクションのリストでは、windowing_clause
を含む完全な構文を使用できるファンクションには、アスタリスク(*)が付いています。
- AVG *
- BIT_AND_AGG*
- BIT_OR_AGG*
- BIT_XOR_AGG*
- BOOLEAN_AND_AGG*
- BOOLEAN_OR_AGG*
- CHECKSUM*
- CLUSTER_DETAILS
- CLUSTER_DISTANCE
- CLUSTER_ID
- CLUSTER_PROBABILITY
- CLUSTER_SET
- CORR *
- COUNT *
- COVAR_POP *
- COVAR_SAMP *
- CUME_DIST
- DENSE_RANK
- EVERY*
- FEATURE_DETAILS
- FEATURE_ID
- FEATURE_SET
- FEATURE_VALUE
- FIRST
- FIRST_VALUE *
- KURTOSIS_POP*
- KURTOSIS_SAMP*
- LAG
- LAST
- LAST_VALUE *
- LEAD
- LISTAGG
- MAX *
- MEDIAN
- MIN *
- NTH_VALUE *
- NTILE
- PERCENT_RANK
- PERCENTILE_CONT
- PERCENTILE_DISC
- PREDICTION
- PREDICTION_COST
- PREDICTION_DETAILS
- PREDICTION_PROBABILITY
- PREDICTION_SET
- RANK
- RATIO_TO_REPORT
- REGR_(線形回帰)ファンクション *
- ROW_NUMBER
- STDDEV *
- STDDEV_POP *
- SKEWNESS_POP*
- SKEWNESS_SAMP*
- STDDEV_SAMP *
- SUM *
- VAR_POP *
- VAR_SAMP *
- VARIANCE *
関連項目:
これらのファンクションおよびその使用方法の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。