計算済メジャー式

計算済メジャー式では、分析ビューの計算済メジャーを定義します。計算済メジャー式は、CREATE ANALYTIC VIEW文のcalc_measure_clauseのcalc_meas_expressionパラメータとして使用します。

ヒント:

計算済メジャーを使用する分析ビューを作成するSQLスクリプトは、Oracle Live SQLのWebサイト(https://livesql.oracle.com/apex/livesql/file/index.html)で表示および実行できます。このWebサイトには、分析ビューの作成方法および使用方法を示すスクリプトおよびチュートリアルが用意されています。

構文

calc_meas_expression::=

図calc_meas_expression.epsの説明

セマンティクス

calc_meas_expression

次の各トピックでは、分析ビューに固有の構文がある計算済メジャー式について説明します。

• 分析ビューのメジャー式

• 分析ビューの単純式

• 単一行ファンクション式

計算済メジャーで使用可能な他のタイプの式は、次のトピックを参照してください。

• CASE式

• 複合式

• 日時式

• 期間式

関連項目:

CREATE ANALYTIC VIEW

分析ビューのメジャー式

分析ビューのメジャー式は、分析ビューのメジャーに基づいています。

ヒント:

計算済メジャーを使用する分析ビューを作成するSQLスクリプトは、Oracle Live SQLのWebサイト(https://livesql.oracle.com/apex/livesql/file/index.html)で表示および実行できます。このWebサイトには、分析ビューの作成方法および使用方法を示すスクリプトおよびチュートリアルが用意されています。

構文

av_meas_expression::=

図av_meas_expression.epsの説明

lead_lag_expression::=

図lead_lag_expression.epsの説明

lead_lag_function_name::=

図lead_lag_function_name.epsの説明

lead_lag_clause::=

図lead_lag_clause.epsの説明

hierarchy_ref::=

図hierarchy_ref.epsの説明

window_expression::=

図window_expression.epsの説明

window_clause::=

図window_clause.epsの説明

preceding_boundary ::=

図preceding_boundary.epsの説明

following_boundary::=

図following_boundary.epsの説明

calc_meas_order_by_clause::=

図calc_meas_order_by_clause.epsの説明

share_of_expression::=

図share_of_expression.epsの説明

share_clause::=

図share_clause.epsの説明

level_member_literal::=

図level_member_literal.epsの説明

pos_member_keys::=

図pos_member_keys.epsの説明

named_member_keys::=

図named_member_keys.epsの説明

hier_navigation_expression::=

図hier_navigation_expression.epsの説明

hier_ancestor_expression::=

図hier_ancestor_expression.epsの説明

member_expression::=

図member_expression.epsの説明

hier_parent_expression::=

図hier_parent_expression.epsの説明

hier_lead_lag_expression::=

図hier_lead_lag_expression.epsの説明

hier_lead_lag_clause::=

図hier_lead_lag_clause.epsの説明

qdr_expression::=

図qdr_expression.epsの説明

qualifier::=

図qualifier.epsの説明

セマンティクス

av_meas_expression

関連するメジャー値を検索するために、階層ナビゲーションを実行する式。

lead_lag_expression

階層内で、LEAD操作またはLAG操作を指定して、ある数のメンバー分だけ前後にナビゲートして、関連するメジャー値を検索する式。

calc_meas_expressionパラメータは、lead_lag_expressionによって作成される新しいコンテキストで評価されます。このコンテキストには、指定される階層のメンバーが、LEAD操作またはLAG操作によって指定された関連するメンバーに変更される以外は、外部コンテキストと同じメンバーが含まれます。LEADファンクションまたはLAGファンクションは、lead_lag_clauseパラメータで指定された階層メンバーに対して実行されます。

lead_lag_function_name

LEADファンクションまたはLAGファンクションは、次のいずれかになります。

• LAGでは、先行するメンバーのメジャー値を戻します。

• LAG_DIFFでは、現在のメンバーのメジャー値と先行するメンバーのメジャー値との差を戻します。

• LAG_DIFF_PERCENTでは、現在のメンバーのメジャー値と先行するメンバーのメジャー値との相違率を戻します。

• LEADでは、後続のメンバーのメジャー値を戻します。

• LEAD_DIFFでは、現在のメンバーのメジャー値と後続のメンバーのメジャー値との差を戻します。

• LEAD_DIFF_PERCENTでは、現在のメンバーのメジャー値と後続のメンバーのメジャー値との相違率を戻します。

lead_lag_clause

評価する階層およびオフセット値を指定します。lead_lag_clauseのパラメータは次のとおりです。

• HIERARCHY hierarchy_refでは、分析ビューの階層の名前を指定します。

• OFFSET offset_exprでは、数値に変換するcalc_meas_expressionを指定します。数値は、現在のメンバーから前後に何メンバー分移動するかを指定します。レベル内のメンバーの順序は、階層で使用する属性ディメンションの定義によって決まります。

• WITHIN LEVELでは、現在のメンバーと深さのレベルが同じメンバー内で、メンバーのオフセット数に従って前後に移動して、関連するメンバーを検索することを指定します。レベル内のメンバーの順序は、階層で使用する属性ディメンションの定義によって決まります。

  WITHIN LEVELキーワードとACROSS ANCESTOR AT LEVELキーワードのどちらも指定されていない場合は、WITHIN LEVEL操作がデフォルトとなります。

• WITHIN PARENTでは、現在のメンバーと親が同じメンバー内で、メンバーのオフセット数に従って前後に移動して、関連するメンバーを検索することを指定します。

• ACROSS ANCESTOR AT LEVEL level_refでは、level_refで指定されたレベルの現在のメンバーの祖先(または祖先が存在しない場合はメンバー自体)までナビゲートして関連するメンバーを検索することを指定し、かつ、その親内の各祖先メンバー(メンバー自体を含む)の位置を示します。level_refパラメータは、指定された階層のレベルの名前です。

  祖先メンバーが見つかると、ナビゲーションは、祖先メンバーと深さが同じメンバー内で、メンバーのオフセット数だけ前後に移動します。関連する祖先が見つかった後、ナビゲーションはそのメンバーから階層を逆方向に進み、上に向かって記録された(逆順)親内の位置を照合します。親内での位置は、POSITION FROM BEGINNINGとPOSITION FROM ENDのいずれが指定されているかに従い、最初の子または最後の子からオフセットされます。デフォルト値はPOSITION FROM BEGINNINGです。レベル内のメンバーの順序は、階層で使用する属性ディメンションの定義によって決まります。

window_expression

window_expressionでは、現在のメンバーから始まる指定された範囲内にあり、かつ、現在のメンバーと同じ深さにある一連のメンバーを選択します。WITHIN句を使用して階層関係を指定することによって、このメンバーの選択をさらに制限できます。その後、選択したメジャー値で集計が実行され、式に対する1つの結果が生成されます。

window_expressionのパラメータは次のとおりです。

• aggregate_functionは、COLLECT、GROUP_ID、GROUPING、GROUPING_ID、SYS_XMLAGG、XMLAGGおよびすべての複数引数ファンクションを除く、任意の既存のSQL集計ファンクションです。ユーザー定義集計ファンクションも使用できます。集計ファンクションの引数はcalc_meas_expression式です。これらの式は、外部コンテキストを使用して評価され、指定された階層のメンバーは、関連する範囲の各メンバーに変更されます。このため、それぞれの式引数は関連するメンバーごとに1回評価されます。その後、結果が、aggregate_functionを使用して集計されます。

• OVER (window_clause)では、使用する階層および考慮するウィンドウの境界を指定します。

関連項目:

集計ファンクション

window_clause

window_clauseパラメータでは、現在のメンバーに関連するメンバーの範囲を選択します。この範囲は、preceding_boundaryパラメータまたはfollowing_boundaryパラメータで指定されたメンバーの間になります。範囲は、常に、現在のメンバーと同じレベルのメンバーに対して計算されます。

window_clauseのパラメータは次のとおりです。

• HIERARCHY hierarchy_refでは、分析ビューの階層の名前を指定します。

• BETWEEN preceding_boundaryまたはfollowing_boundaryでは、現在のメンバーに関連する一連のメンバーを定義します。

• WITHIN LEVELでは、現在のレベルのすべてのメンバーにboundary句を適用することによって、関連するメンバーを選択します。これは、WITHINキーワードが指定されていない場合にデフォルトとなります。

• WITHIN PARENTでは、現在のメンバーと親を共有するすべてのメンバーにboundary句を適用することによって、関連するメンバーを選択します。

• WITHIN ANCESTOR AT LEVELでは、現在のメンバーと指定されたレベルの祖先を共有する(またはメンバー自体である)現在の深さのすべてのメンバーにboundary句を適用することによって、関連するメンバーを選択します。現在のメンバーが指定されたレベルよりも上にある場合、ウィンドウ式の値はNULLとなります。指定された階層に、このレベルがない場合は、エラーが発生します。

preceding_boundary

preceding_boundaryパラメータではメンバーの範囲を定義し、その先頭は、現在のメンバーのレベルで、指定された数だけ戻ったメンバーとなり、末尾は指定された境界の端となります。次のパラメータでは、範囲を指定します。

• UNBOUNDED PRECEDINGを指定すると、レベルの最初のメンバーが範囲の先頭になります。

• offset_expr PRECEDINGを指定すると、現在のメンバーからoffset_exprの数だけ戻ったメンバーが範囲の先頭になります。offset_expr式は、数値に変換するcalc_meas_expressionです。オフセット数が、そのレベルの現在のメンバーから最初のメンバーまでのメンバーの数より大きい場合、最初のメンバーが範囲の開始点として使用されます。

• CURRENT MEMBERを指定すると、現在のメンバーが範囲の末尾となります。

• offset_expr PRECEDINGを指定すると、現在のメンバーからoffset_exprだけ戻ったメンバーが範囲の末尾になります。

• offset_expr FOLLOWINGを指定すると、現在のメンバーからoffset_expr分だけ進んだメンバーが範囲の末尾になります。

• UNBOUNDED FOLLOWINGを指定すると、そのレベルの最後のメンバーが範囲の末尾になります。

following_boundary

following_boundaryパラメータではメンバーの範囲を定義し、その先頭は、現在のメンバーから指定された数だけ進んだメンバーになり、末尾は、指定した範囲の終わりになります。次のパラメータでは、範囲を指定します。

• CURRENT MEMBERを指定すると、現在のメンバーが範囲の先頭になります。

• offset_expr FOLLOWINGを指定すると、現在のメンバーからoffset_exprだけ進んだメンバーが範囲の先頭になります。

• offset_expr FOLLOWINGを指定すると、現在のメンバーからoffset_expr分だけ進んだメンバーが範囲の末尾になります。

• UNBOUNDED FOLLOWINGを指定すると、そのレベルの最後のメンバーが範囲の末尾になります。

hierarchy_ref

分析ビューの階層への参照。hier_aliasパラメータは、分析ビューの定義で階層の名前を指定します。特殊文字のエスケープまたはケースの保持、あるいはその両方を行うには、二重引用符を使用します。

オプションのattr_dim_aliasパラメータは、分析ビューの定義で指定された別名の名前です。指定された階層の別名が分析ビューの別の階層の別名と競合する場合、または分析ビューの定義で属性ディメンションが複数回使用されている場合は、attr_dim_aliasパラメータを使用して、あいまいさを解決できます。attr_dim_aliasパラメータは、名前の競合が存在しない場合も使用できます。

share_of_expression

share_of_expression式では、現在のコンテキストの式の値と、関連するコンテキストの式の値の比率を計算します。この式は、現在のコンテキストおよび関連するコンテキストで評価されるcalc_meas_expressionです。share_clauseの指定により、使用する関連するコンテキストが決まります。

share_clause

share_clauseでは、指定された階層のメンバーを関連するメンバーに設定することによって、外部コンテキストを変更します。

share句のパラメータは次のとおりです。

• HIERARCHY hierarchy_refでは、share_of_expression計算の外部コンテキストである階層の名前を指定します。

• PARENTでは、関連するメンバーが現在のメンバーの親であることを指定します。

• LEVEL level_refでは、関連するメンバーが、階層の指定されたレベルの現在のメンバーの祖先(またはメンバー自体)であることを指定します。現在のメンバーが、指定されたレベルよりも上にある場合は、share式に対してNULLが戻されます。階層に、このレベルがない場合は、エラーが発生します。

• MEMBER member_expressionでは、関連するメンバーが、現在のコンテキストでmember_expressionを評価した後に戻されるメンバーであることを指定します。指定されたメンバーの値がNULLの場合、share式に対してNULLが戻されます。

level_member_literal

level_member_literalは、階層の単一メンバーに変換する式です。この式には、レベルの名前および1つ以上のメンバー・キーが含まれます。メンバー・キーは位置または名前で識別できます。コンテキストの階層に、指定されたレベルがない場合は、エラーが発生します。

pos_member_keys

member_key_expr式は、メンバーのキー値に解決されます。位置で指定する場合、キーのすべてのコンポーネントは、ALL_HIER_LEVEL_ID_ATTRSディクショナリ・ビュー内の順序で指定する必要があります。指定されたレベルが子レベルによって決定されない階層では、そのようなすべての子レベルのすべてのメンバー・キー値を、現在のレベルのメンバー・キーより前に指定する必要があります。重複するキー・コンポーネントは、初めて出現したときにのみ指定されます。

主キーは、level_member_literalがpos_member_keys句で指定された場合に使用されます。代替キーを参照するには、named_member_keys句を使用します。

named_member_keys

member_key_expr式は、メンバーのキー値に解決されます。attr_nameパラメータは、属性名の識別子です。すべての属性名が、指定されたレベルのキーまたは代替キーを構成していない場合は、エラーが発生します。

名前で指定する場合、キーのすべてのコンポーネントを指定し、すべてで属性の名前 = 値書式を任意の順序で使用する必要があります。指定されたレベルが子レベルによって決定されない階層では、そのようなすべての子レベルのすべてのメンバー・キー値を指定し、指定された書式も使用する必要があります。重複するキー・コンポーネントは、1回のみ指定されます。

hier_navigation_expression

hier_navigation_expression式は、指定されたメンバーから階層の別のメンバーにナビゲートします。

hier_ancestor_expression

指定されたメンバーから、指定されたレベルまたは深さの祖先メンバー(またはメンバー自体)にナビゲートします。深さは、数値に変換する必要がある式として指定されます。メンバーが、指定されたメンバーより上のレベルまたは深さの場合、またはメンバーがNULLの場合、式の値に対してNULLが戻されます。コンテキストの階層に、指定されたレベルがない場合は、エラーが発生します。

member_expression

member_expressionは、指定された階層のメンバーと評価されます。階層は、常に、外部式(構文によって適用)から決定できます。member_expressionは、次のいずれかになります。

• level_member_literalは、階層メンバーと評価される式です。

• hier_navigation_exprは、階層のあるメンバーを別のメンバーに関連付ける式です。

• CURRENT MEMBERでは、外部コンテキストで決定された階層のメンバーを指定します。

• NULLは、存在しないメンバーを指定する方法です。

• ALLでは、すべての階層の最上位の単一メンバーを指定します。

hier_parent_expression

指定されたメンバーから親メンバーにナビゲートします。

hier_lead_lag_expression

コンテキスト階層内で、一定のメンバーの数だけ前後に移動して、指定されたメンバーから関連するメンバーにナビゲートします。HIER_LEADキーワードを指定すると、後続のメンバーが戻されます。HIER_LAGキーワードを指定すると、先行するメンバーが戻されます。

hier_lead_lag_clause

指定されたメンバーからoffset_exprの数だけ前後にあるメンバーを対象にナビゲートします。レベル内のメンバーの順序は、属性ディメンションの定義で指定されます。

hier_lead_lag_clauseのオプションのパラメータは次のとおりです。

• WITHIN LEVELでは、現在のメンバーと深さが同じメンバー内で、offset_exprメンバー分だけ前後に移動して、関連するメンバーが検索されます。レベル内のメンバーの順序は、属性ディメンションの定義によって決まります。WITHINキーワードとACROSSキーワードのどちらも使用されていない場合は、WITHIN LEVEL操作がデフォルトとなります。

• WITHIN PARENTでは、現在のメンバーと深さが同じメンバー内で、offset_exprメンバー分だけ前後に移動して、関連するメンバーが検索されますが、現在のメンバーと親を共有するメンバーのみが考慮されます。レベル内のメンバーの順序は、属性ディメンションの定義によって決まります。

• WITHIN ACROSS ANCESTOR AT LEVELは、指定されたレベルの現在のメンバーの祖先(またはメンバー自体)までナビゲートして関連するメンバーを検索し、その親内の各祖先メンバー(メンバー自体を含む)の位置を示します。祖先メンバーが見つかると、祖先メンバーと深さが同じメンバー内で、offset_exprメンバー分前後にナビゲーションが移動します。

  関連する祖先が見つかった後、ナビゲーションはそのメンバーから階層を逆方向に移動し、上に向かって記録された(逆順)親内の位置を照合します。親内での位置は、POSITION FROM BEGINNINGとPOSITION FROM ENDのいずれが指定されているかに従い、最初の子または最後の子からオフセットされます。デフォルトは、POSITION FROM BEGINNINGです。レベル内のメンバーの順序は、属性ディメンションの定義によって決まります。

qdr_expression

qdr_expressionは、新しいコンテキストで指定されたcalc_meas_expressionを評価し、階層メンバーを新しい値に設定する修飾データ参照です。

qualifier

修飾子は、指定された階層のメンバーを、member_expressionの評価の結果のメンバーに設定して、外部コンテキストを変更します。member_expressionがNULLの場合、qdr_expressionの選択の結果はNULLです。

分析ビューの単純式

計算済メジャー式は、分析ビューの単純式にすることができます。分析ビューの単純式は、SQLリテラルまたは分析ビューのメジャーへの参照にすることができます。

av_simple_expression::=

図av_simple_expression.epsの説明

measure_ref::=

図measure_ref.epsの説明

measure_ref

分析ビューのメジャーへの参照。meas_nameパラメータは、分析ビューの定義のメジャーの名前にする必要があります。特殊文字のエスケープまたはケースの保持、あるいはその両方を行うには、二重引用符を使用します。メジャー名が分析ビューで使用される属性ディメンションの名前と競合する場合は、オプションのMEASURESキーワードを使用して、あいまいさを解決できます。名前の競合が存在しない場合でも、MEASURESキーワードは使用できます。

単一行ファンクション式

計算済メジャー式は、SQLの単一行ファンクション式にすることができます。

関連項目:

• ファンクション式

• 単一行ファンクション

計算済メジャー式の例

LAG式の例

このトピックでは、分析ビューのMEASURES句に定義された計算済メジャーを表示する例を示します。その他の例は、SQL LiveのWebサイト(https://livesql.oracle.com/apex/livesql/file/index.html)にある分析ビューのチュートリアルを参照してください。

これらの計算済メジャーは、LAG操作とは異なります。

-- These calculated measures are from the measures_clause of the
-- sales_av analytic view.
MEASURES
 (sales FACT sales,                      -- A base measure
  units FACT units,                      -- A base measure
  sales_prior_period AS                  -- Calculated measures
    (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1)),
  sales_year_ago AS
    (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL year)),
  chg_sales_year_ago AS
    (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL year)),
  pct_chg_sales_year_ago AS
    (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL year)),
  sales_qtr_ago AS
    (LAG(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL quarter)),
  chg_sales_qtr_ago AS
    (LAG_DIFF(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL quarter)),
  pct_chg_sales_qtr_ago AS
    (LAG_DIFF_PERCENT(sales) OVER (HIERARCHY time_hier OFFSET 1
     ACROSS ANCESTOR AT LEVEL quarter))
 )

ウィンドウ式の例

この計算済メジャーは、ウィンドウ操作を使用します。

MEASURES
 (sales FACT sales,
  units FACT units,
  sales_qtd AS
    (SUM(sales) OVER (HIERARCHY time_hier
     BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER
     WITHIN ANCESTOR AT LEVEL QUARTER)),
  sales_ytd AS
    (SUM(sales) OVER (HIERARCHY time_hier
     BETWEEN UNBOUNDED PRECEDING AND CURRENT MEMBER
     WITHIN ANCESTOR AT LEVEL YEAR))      
 )

SHARE OF式の例

これらの計算済メジャーは、SHARE OF式を使用します。

MEASURES
 (sales FACT sales,
  units FACT units,
 sales_shr_parent_prod AS
   (SHARE_OF(sales HIERARCHY product_hier PARENT)),
 sales_shr_parent_geog AS
   (SHARE_OF(sales HIERARCHY geography_hier PARENT)),
 sales_shr_region AS
   (SHARE_OF(sales HIERARCHY geography_hier LEVEL REGION)) 
 )

QDR式の例

これらの計算済メジャーは、QUALIFYキーワードを使用して、修飾データ参照式を指定します。

MEASURES
 (sales FACT sales,
  units FACT units,
  sales_2011 AS
    (QUALIFY (sales, time_hier = year['11'])),
  sales_pct_chg_2011 AS
    ((sales - (QUALIFY (sales, time_hier = year['11']))) /
    (QUALIFY (sales, time_hier = year['11'])))
 )