 Copyright ©2016, 2018,Oracle and/or its affiliates. All rights reserved. |
|
| Oracle® Fusion Middleware Oracle Business Intelligence Enterprise Edition論理SQLリファレンス・ガイド リリース12c (12.2.1.4.0) E96102-01 |
Oracle Business Intelligence Enterprise Edition論理SQLリファレンス・ガイド
リリース12c (12.2.1.4.0)
E96102-01(原本部品番号:E91519-01)
2018年4月
AGO、TODATE、EVALUATEなどの特殊関数(SQL拡張)が含まれています。論理SQL問合せはプレゼンテーション・レイヤー・オブジェクトに解決されます。
このガイドのトピックは、次のとおりです。
Oracleのアクセシビリティについての詳細情報は、Oracle Accessibility ProgramのWebサイト(http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc)を参照してください。
Oracleサポートへのアクセス
サポートを購入したオラクル社のお客様は、My Oracle Supportを介して電子的なサポートにアクセスできます。詳細情報はhttp://www.oracle.com/pls/topic/lookup?ctx=acc&id=infoか、聴覚に障害のあるお客様はhttp://www.oracle.com/pls/topic/lookup?ctx=acc&id=trsを参照してください。
派生列に式を作成するSQL Select文定義の概要を説明します。
Oracle BIサーバーは、クライアント・ツールからSQLのSELECT文を受け入れます。また、Oracle BI管理ツールを使用すると、複雑な式を使用した論理列を定義できます。このガイドでは、派生列を作成するために管理ツールで使用可能なSELECT文および式の構文とセマンティクスについて説明します。
プレゼンテーション・レイヤーおよびビジネス・モデルとマッピング・レイヤーがもたらす抽象化により、クライアントは論理SQLのみを指定してデータを問合せできるため、実際の物理ソースとの相互作用はOracle BIサーバーによって処理されます。各データ・ソース・タイプと通信する必要がある複数のソース言語の複雑さが、ユーザーおよびクライアントから隠されます。
管理ツールでは、「分析」エディタの「詳細設定」タブの「発行されたSQL」セクションを表示すると、特定の分析に対してOracle BIサーバーによって発行される論理SQL問合せを表示できます。適切な権限がある場合は、「管理」タブの「セッションの管理」ページを表示すれば、SQLを表示することもできます。詳細を参照するには、「セッションの管理」ページの「ログの表示」をクリックします。
また、「アンサー」には、論理SQLを発行できる場所もあります。適切な権限がある場合は、「管理」タブの「SQLの発行」ページを使用して、SQLコードを入力してOracle BIサーバーに送信できます。分析に階層列、メンバー選択またはグループが含まれていない場合、「分析」エディタの「詳細設定」タブの「高度なSQL句」フィールドを使用できます。「新規フィルタ」ダイアログでSQLを入力することもできます。
管理ツールでは、論理SQLは、多くの場合、ビジネス・モデルとマッピング・レイヤーのオブジェクトに関連する式の形式で表示されます。式ビルダーでSQL関数を作成します。
Oracle BI Publisher、Oracle Hyperion Interactive Reporting、Oracle BI Add-in for Microsoft Office、Essbaseなど他のクライアントは、論理SQLを表示してOracle BIサーバーに対して発行するための独自のインタフェースも提供しています。
これらの項では、SQL構文とセマンティクスについて説明します。
この項の内容は、次のとおりです。
SELECT文(問合せ指定とも呼ばれる)は、Oracle BIサーバーを介して意思決定支援システムを問い合せる方法です。
SELECT文では、問合せに一致する表がクライアントに返されます。結果が行と列の形式で格納されるため、表と言えます。
SELECT文はSQL(Structured Query Language)データベースへの問合せの基礎となります。Oracle BIサーバーは論理リクエストを受け入れてリポジトリ内のオブジェクトに問い合せます。ユーザー(または問合せツール)は標準のSQL SELECT文を使用して論理リクエストを作成します。サーバーは、論理リクエストを1つ以上のデータ・ソースに対する物理問合せに変換し、論理リクエストに一致するよう結果を組み合せて、エンド・ユーザーに応答を返します。
論理SQLのSELECT文は、結果を返すために表を結合する必要がないという点で標準SQLとは異なります。問合せに指定される結合条件はすべて無視されます。結合条件はOracle BIリポジトリ内に事前定義されています。
この項では、SELECT文と個々の句の定義の基本構文について説明します。構文の説明は、Oracle BIサーバー固有の基本構文と機能のみを対象としています。SQL構文の詳細な説明は、サード・パーティのSQLに関する参考文献または使用しているデータベースのベンダーが提供しているSQLに関するリファレンス・マニュアルを参照してください。『Oracle Database SQL言語リファレンス』を参照してください。
この項では、次の項目について説明します。
この構文を使用して基本のSelect文を作成します。
次に、SELECT文の構文を示します。
SELECT [DISTINCT] select_list FROM from_clause [WHERE search_condition] [GROUP BY column {, column} [HAVING search_condition]] [ORDER BY column {, column}]
説明:
select_listはリクエストに指定される列のリストです。「SELECTリスト構文」を参照してください。
FROM from_clause はリクエストの表のリストです。リクエストの特定の結合情報をオプションで含みます。「FROM句の構文」を参照してください。
WHERE search_condition では条件テストを生成するための条件の任意の組合せを指定します。WHERE句は、特定の質問に回答する結果を取得するためにリクエストを制約するフィルタとして機能します。選択する列とともに、フィルタにより結果に含まれる内容が決定されます。「WHERE句の構文」を参照してください。
GROUP BY column {,column}ではデータ・ソースに定義された表に属する列(または別名)を指定します。「GROUP BY句の構文」を参照してください。
HAVING search_conditionでは条件テストを生成するための条件の組合せを指定します。構文はWHERE句と同じです。
ORDER BY column{,column}では結果を並べ替える列を指定します。「ORDER BY句の構文」を参照してください。
Oracle BIサーバーはSELECT文を論理リクエストとして処理します。
SELECT文で集計データをリクエストする場合、サーバーによってGROUP BY句が自動的に仮定されます。問合せに指定される結合条件はすべて無視されます。結合条件はすべてOracle BIリポジトリ内で事前定義されています。
Oracle BIサーバーは、コメントについて次のSQL構文を受け入れます。
Cスタイル・コメント(/* */)
1行コメントに対する二重スラッシュ(//)
1行コメントに対する番号記号(#)
Oracle BIサーバーはサブ問合せをサポートしています。
Oracle BIサーバーは、論理リクエスト内の特定のサブ問合せと、UNION、UNION ALL、INTERSECTおよびEXCEPT操作をサポートします。サブ問合せのサポートにより、回答可能なビジネス上の質問範囲が拡大し、問合せの生成が容易になり、複数のビジネス・モデル間で問合せを実行できるようになります。
Oracle BIサーバーは、任意の条件式、たとえばWHERE、HAVINGまたはCASE文などにおいて次のサブ問合せ述語をサポートします。
IN, NOT IN Any, >=Any, =Any, <Any, <=Any, <>Any All, >=All, =All, <All,<=All, <>All EXISTS, NOT EXISTS
「アンサー」で、上級ユーザーおよび開発者は「分析」エディタの「詳細設定」タブの「拡張SQL句」フィールドを使用して、様々なSQL句(GROUP BY、HAVING、DISTINCTなど)を指定し、Oracle BIサーバーに送信されるSQL問合せに含めることができます。分析に階層列、選択またはグループが含まれている場合、特定の「高度なSQL句」フィールドが使用できません。
select_list関数はリクエストの列をリストします。
すべての列は単一のビジネス・モデルから選択する必要があります。表名はTable.Columnのように含めることができます。表名は、列名がビジネス・モデル内で一意でない場合はオプションです。列名に空白が含まれる場合は、列名を二重引用符で囲みます。Oracle BIサーバーでは常に個別の問合せが実行されるため、DISTINCTキーワードを含める必要はありません。集計ルールはOracle BIサーバーで認識され、集計は自動的に実行されるため、集計が行われる列に集計関数(SUMなど)を含める必要はありません。
構文
... * | (column | expr) [[AS] alias] {, (column | expr) [[AS] alias] } ...
説明:
*はFROM句内の結果表のすべての列を示します。
columnはデータ・ソースに定義される表に属する列(または別名)です。
exprは任意の有効なSQL式です。
注意:
「アンサー」では、*を使用して「分析」エディタの「詳細設定」タブからすべての列を選択することはできません。かわりに、特定の列を指定する必要があります。
Oracle BIサーバーは、任意の有効なSQL FROM句の構文を受け入れます。
FROM句の作成を容易にするため、テーブル・リストのかわりにサブジェクト・エリアの名前を指定できます。リクエストで検索する列とOracle BIリポジトリの構成に基づいて、適切な表と適切な結合指定がOracle BIサーバーで決定されます。
Oracle BIサーバーは、任意の有効なSQL WHERE句の構文を受け入れます。
結合はすべてOracle Business Intelligenceリポジトリ内で構成されるため、WHERE句に結合条件を指定する必要はありません。WHERE句で指定された結合条件はすべて無視されます。
Oracle BI EEは、任意の条件式(WHERE、HAVINGまたはCASE文など)において次のサブ問合せ述語をサポートします。
IN, NOT IN Any, >=Any, =Any, <Any, <=Any, <>Any All, >=All, =All, <All,<=All, <>All EXISTS, NOT EXISTS
Oracle BIサーバーの自動集計機能により、GROUP BY句を発行する必要はありません。
GROUP BY句が指定されていない場合は、GROUP BY指定によって、SELECTリスト内のすべての非集計列がデフォルトで使用されます。SELECTリスト内で集計関数を明示的に使用する場合は、異なる列にGROUP BY句を指定できます。Oracle BIサーバーにより、GROUP BY句で指定されたレベルに基づいて結果が計算されます。
Oracle BIサーバーに対するリクエストでGROUP BY句を使用する方法の詳細な説明と例については、「集計関数を含む問合せのルール」を参照してください。
Oracle BIサーバーは、SELECTリスト内の順序による列の参照(ORDER BY 3, 1, 5など)を含め、任意の有効なSQL ORDER BY句の構文を受け入れます。
ORDER BYで、次の構文を使用して問合せのNULLのソート順を変更できます。
ORDER BY col1 NULLS LAST, ORDER BY col2 NULLS FIRST
ソート順序列が割り当てられた論理列の場合、ORDER BY句を使用してソート順序列を無視し、かわりに列の値でソートします。次の構文に注意してください。
ORDER BY { { <column_index> | <expr> } [ DISPLAY | SORTKEY ] [ ASC | DESC ] [NULLS { FIRST | LAST } ] }
説明:
DISPLAYは、ソート列が論理列に割り当てられているかどうかに関係なく、式の表示値の順序に基づいてソートします。デフォルトでは、ソート列が論理列に設定されていない場合、Oracle BIサーバーはDISPLAYを想定します。
SORTKEYは、論理列に割り当てられたソート列に基づいてソートします。デフォルトでは、ソート列が論理列に設定されている場合、Oracle BIサーバーはSORTKEYを想定します。
Oracle BIサーバーは、SELECT_PHYSICAL問合せに関する解析、解釈および問合せ生成を行い、生成された問合せをデータベースに渡します。
SELECT_PHYSICAL問合せはプレゼンテーション・レイヤーおよびビジネス・モデルとマッピング・レイヤーを回避しますが、Oracle BIサーバーは、データベースに渡す前に、SELECT_PHYSICAL問合せに対して分析、解釈および問合せの生成を行います。
SELECT_PHYSICALコマンドには、次の制約があるOracle BIサーバーの標準SQLで許可されている任意の要素を含めることができます。
SELECT_PHYSICALコマンドは、リポジトリのビジネス・モデルとマッピング・レイヤーまたはプレセンテーション・レイヤーの構造を明示的に参照しません。
SELECT_PHYSICALコマンドは、 潜在論理変換をリクエストしません。
SELECT_PHYSICALコマンドには、特定の集計関数を含めることはできません。「SELECT_PHYSICAL問合せでサポートされていない集計関数」を参照してください。
SELECT_PHYSICAL文はキャッシュされません。
すべてのSELECT問合せがSELECT_PHYSICAL問合せとして処理される専用の物理接続として、Oracle BIサーバーへのODBC接続を設定できます。これを行うには、Oracle BIサーバーのODBCデータ・ソースで「リクエストを物理レイヤーに送信」を選択します。『Oracle Business Intelligence Enterprise Editionインテグレーターズ・ガイド』のOracle Business Intelligenceと他のクライアントの統合に関する項を参照してください。
SELECT_PHYSICAL文は物理リクエスト・エンティティとして記録されます。
この項のトピックは次のとおりです。
SELECT_PHYSICAL関数の構文はSELECT文で使用される構文に類似しています。
構文
SELECT_PHYSICAL [DISTINCT] select_list FROM from_clause [WHERE search_condition] [GROUP BY column {, column} [HAVING search_condition]] [ORDER BY column {, column}]
SELECT_PHYSICAL集計問合せでは、GROUP BY句も、HAVING句も省略できません。
SELECT_PHYSICAL問合せで、FROMリスト内の表名は完全修飾されている必要があります。完全修飾された各表名は、リポジトリの物理レイヤー内の表名と一致している必要があります。
完全修飾された表名は、4つまでのコンポーネント(データベース名、カタログ名、スキーマ名および表名)で構成されます。各コンポーネントを二重引用符(")で囲み、コンポーネント間セパレータとしてピリオド(.)を使用します。たとえば、SQL Server表の"SQL_DB"."My_Catalog"."My_Schema"."Customers"、キューブ表の"FoodMart"..."Sales"などです。
次のリストにある集計関数は、SELECT_PHYSICAL問合せでサポートされていません。
AGO
BOTTOMN
FILTER
FIRST
LAST
RCOUNT
RMAX
RMIN
RSUM
TODATE
TOPN
Oracle BIサーバーはいくつかのタイプの論理問合せについてSELECT_PHYSICALの使用をサポートしています。
次の論理問合せタイプがサポートされています。
標準の非集計問合せ
標準の非集計SELECT_PHYSICALコマンドは、標準の非集計SELECTコマンドと同じルールに従います。また、文字列関数、数学関数、カレンダ日時関数などのスカラー関数も含めることができます。次に例を示します。
SELECT_PHYSICAL productid, categoryid FROM "My_DB"."My_Schema"."products" WHERE categoryid > 5;
SELECT_PHYSICAL LEFT(productname,10) FROM "My_DB"."My_Schema"."products" WHERE productname is not null;
集計関数を含む問合せ
一般に、SELECT問合せでサポートされているすべての集計関数は、SELECT_PHYSICAL問合せでもサポートされています。このルールに対する例外のリストについては、「SELECT_PHYSICAL問合せでサポートされていない集計関数」を参照してください。
SELECT_PHYSICALコマンドでサポートされている集計の場合、各集計には、GROUP BY句またはBY句を使用して明示的に指定された集計レベルが必要です。次に例を示します。
SELECT_PHYSICAL employeeid, SUM(quantity by) FROM "My_DB"."My_Schema"."employees"; SELECT_PHYSICAL employeeid, SUM(quantity) FROM "My_DB"."My_Schema"."employees" GROUP BY employeeid HAVING SUM(quantity) > 100;
サブ問合せ
Oracle BIサーバーは、次のタイプの問合せをサポートしています。
親問合せとサブ問合せの両方がSELECT_PHYSICALを使用する問合せ。
親問合せがSELECTを使用し、サブ問合せがSELECT_PHYSICALを使用する問合せ。
サブ問合せは、Case文に組み込まれたフィルタとプロジェクションの両方でサポートされています。
次に例を示します。
SELECT_PHYSICAL * FROM "My_DB"."My_Schema"."products" WHERE supplierid IN (SELECT_PHYSICAL supplierid FROM "My_DB"."My_Schema"."suppliers"); SELECT productid FROM snowflakesales.product WHERE categoryid IN (SELECT_PHYSICAL categoryid FROM "My_DB"."My_Schema"."categories");
SELECTCASE WHENb.categoryid IN (SELECT_PHYSICALa.categoryid FROM "My_DB"."My_Schema"."products" a) THEN b.categoryid END FROM categories b;
派生表を含む問合せ
SELECT問合せとSELECT_PHYSICAL問合せの両方で、FROM句に派生表を含めることができます。SELECTまたはSELECT_PHYSICALを使用して、派生表を作成できます。次に例を示します。
SELECT_PHYSICAL COUNT(DISTINCT t.rto) FROM (SELECT_PHYSICAL employeeid AS id, reportsto AS rto FROM "My_DB"."My_Schema"."employees") t;
SELECT productid, categoryid FROM (SELECT_PHYSICAL productid, categoryid FROM "My_DB"."My_Schema"."products" a LEFT OUTER JOIN "My_DB"."My_Schema"."categories" b ON a.categoryid = b.categoryid);
SELECT y.cid, sum(x.qty) FROM (SELECT productid pid, categoryid cid, qtysold qty FROM sales.product) x RIGHT OUTER JOIN (SELECT_PHYSICAL CASE categoryid WHEN 1 THEN null ELSE categoryid END cid FROM "My_DB"."My_Schema"."categories") y ON x.cid = y.cid GROUP BY y.cid;
クロスデータベース問合せ
SELECT_PHYSICALを使用して、様々なデータベース内の表を結合できます。次に例を示します。
SELECT_PHYSICAL a.productid, b.categoryid FROM "My_DB"."My_Schema"."products" a FULL OUTER JOIN "My_DB2"."My_Schema"."categories" b ON a.categoryid = b.categoryid
SELECT_PHYSICAL問合せはNATURAL JOIN構文をサポートします。これにより、事前定義した結合式を使用できます。
ADFデータ・ソースの場合、ADFのViewLinkがアクティブになります。NATURAL JOINタイプは論理表ソース用に公開されません(LEFT OUTER JOINなど)。
SELECT_PHYSICAL問合せ内のNATURAL JOINキーワードのみ使用できます。Oracle Business IntelligenceでのNATURAL JOINの動作はANSI NATURAL JOINとは異なります。次に、結合がNATURAL JOIN構文の有無によりどのように実行されるかを示します。
SELECT PHYSICAL * FROM A, B;
この例では、(結合がメタデータに定義されている場合でも) AB間の結合は実行されません。
SELECT_PHYSICAL * FROM A NATURAL JOIN B;
この例では、AB間の物理結合は実行されます。ADFデータ・ソースの場合、基礎となるViewLinkで定義された結合式が使用されます。
SELECT_PHYSICAL * FROM C, A NATURAL JOIN B;
この例では、CがメタデータでAに結合されている場合でも、AB間の結合のみアクティブになります。CとAの結合は使用されません。
セッション変数およびINDEXCOL関数をSELECT_PHYSICALコマンドで使用できます。
次の例はSELECT_PHYSICALコマンドでのINDEXCOL関数を示します。
SELECT_PHYSICAL VALUEOF(NQ_SESSION.REGION) FROM "My_DB"."My_Schema"."products"; SELECT_PHYSICAL INDEXCOL(VALUEOF(NQ_SESSION.INDEXCOLINDEX), productid, categoryid) FROM "My_DB"."My_Schema"."products";
FETCH句を使用すると、SELECT文によって返される行数を制約でき、OFFSET句を使用すると、指定した行数を結果セットの先頭からスキップできます。
FETCH句およびOFFSET句はオプションです。この2つの句は一緒に使用することも、どちらか片方のみ使用することもできます。FETCH句とOFFSET句はSELECT文の要素で、最後に配置されます。
これらの句は、大規模な結果セット(大規模なディメンションの場合など)がある状況で、たとえば最初の100行をユーザーに表示する場合に役立ちます。制限に達するとOracle BIサーバーで処理が停止されるため、全体的なパフォーマンスが向上し、リソースが維持されます。さらに、多くの場合、バックエンド・データベースに制限がプッシュされるため、データベースで問合せが最適化されます。
FETCH句とOFFSET句はどちらもORDER BY句なしで使用できますが、結果が非確定的になります。FETCH句とOFFSET句を使用する場合は、常にORDER BY句も使用するようにしてください。
OFFSETが未指定の場合のデフォルト値は0(ゼロ)で、結果が最初の行から返されることを意味します。FETCHが未指定の場合は、返される行数に制限がないことを意味します。
これらの句は、WHERE句、集計、HAVING句、ウィンドウ分析関数およびORDER BY句の後に評価されます。これらの句は、SELECTに加えてSELECT_PHYSICALでも使用できます。
OFFSET句の構文
OFFSET n ROW[S]
説明:
nは、結果セットの先頭からスキップする行数です。0 (ゼロ)よりも大きな値をnに使用する必要があります。
FETCH句の構文
FETCH FIRST | NEXT n ROW[S] ONLY
説明:
nは取得する行数です。0 (ゼロ)よりも大きな値をnに使用する必要があります。
FIRST文は、LIMIT句がOFFSET句に関係なく使用されている場合に使用します。NEXTは、LIMIT句がOFFSET句との組合せで使用されている場合に使用します。
例
SELECT employeeid, firstname, revenue FROM sales.employee ORDER BY revenue desc OFFSET 2 ROWS FETCH NEXT 4 ROWS ONLY
次の表に、OFFSET句とFETCH句を指定しない場合の結果セット全体を示します。OFFSET句とFETCH句を指定した場合は、太字で示した行のみが返されます。
| Employeeid | FirstName | Revenue |
|---|---|---|
| 4 |
Margaret |
250187.45 |
| 3 |
Janet |
213051.30 |
| 1 |
Nancy |
202143.71 |
| 2 |
Andrew |
202143.71 |
| 7 |
Robert |
177749.26 |
| 8 |
Laura |
141295.99 |
| 9 |
Anne |
133301.03 |
| 6 |
Michael |
82964.00 |
| 5 |
Steven |
78198.10 |
Oracle BIサーバーでは、集計の問合せの作成に必要なSQL文を単純化しています。
この項では、問合せにGROUP BY句を含めるか、GROUP BY句を指定するか、問合せから想定される結果など、Oracle BIサーバーで遵守するルールについて説明します。この項で説明するルールは、SQL文で使用されるすべての集計(SUM、AVG、MIN、MAX、COUNT(*)およびCOUNT)に適用されます。
ベースライン列は、それが属する論理表の粒度のレベルで非集計データにマップします。
ベースライン列には、リポジトリの「論理列」ダイアログの「集計」タブに定義されている集計ルールがありません。SQLリクエストを介してベースライン列で集計(SUM、AVG、MIN、MAXまたはCOUNT)を実行すると、Oracle BIサーバーでは次のルールに基づくレベルで集計を計算します。
GROUP BY句が指定されていない場合、集計のレベルはSELECTリストのすべての非集計列でグループ化されます。
GROUP BY句が指定されている場合、集計のレベルはGROUP BY句で指定された列に基づきます。
たとえば、次の問合せについて考えます。ここでは列revenueがベースライン列としてリポジトリに定義されており、「論理列」→「集計」タブで指定されたルールはありません。
SELECT year, product, SUM(revenue) FROM time, products, facts
結果は、年、製品および売上の合計が、次のリストのように表示されます。
| YEAR | PRODUCT | SUM(REVENUE) |
|---|---|---|
|
1998 |
Coke |
500 |
|
1998 |
Pepsi |
600 |
|
1999 |
Coke |
600 |
|
1999 |
Pepsi |
550 |
|
2000 |
Coke |
800 |
|
2000 |
Pepsi |
600 |
この問合せは、年および製品でグループ化された結果を返します。つまり、製品と年の組合せごとに1行を返します。各行に対して計算された合計は、その年におけるその製品に対するすべての売上の合計です。これは、論理的に次のものと同じ問合せです。
SELECT year, product, SUM(revenue) FROM time, products, facts GROUP BY year, product
年でのみグループ化するようにGROUP BY句を変更すると、計算される合計は、次のようにその年のすべての製品の合計になります。
SELECT year, product, SUM(revenue) FROM time, products, facts GROUP BY year
結果は、年、製品および売上の合計が、次のリストのように表示されます。
| YEAR | PRODUCT | SUM(REVENUE) |
|---|---|---|
|
1998 |
Coke |
1100 |
|
1998 |
Pepsi |
1100 |
|
1999 |
Coke |
1150 |
|
1999 |
Pepsi |
1150 |
|
2000 |
Coke |
1400 |
|
2000 |
Pepsi |
1400 |
収益のCOUNTをリクエストする問合せに列を追加すると、Oracle BIサーバーでは、各グループの結果の計算に使用されるレコード数を計算します。次に示す例では、年がグループの単位です。
SELECT year, product, SUM(revenue), COUNT(revenue) FROM time, products, facts GROUP BY year
結果は、年、製品、売上の合計、および売上のカウントが、次のリストのように表示されます。
| YEAR | PRODUCT | SUM(REVENUE) | COUNT(REVENUE) |
|---|---|---|---|
|
1998 |
Coke |
1100 |
6000 |
|
1998 |
Pepsi |
1100 |
6000 |
|
1999 |
Coke |
1150 |
6500 |
|
1999 |
Pepsi |
1150 |
6500 |
|
2000 |
Coke |
1400 |
8000 |
|
2000 |
Pepsi |
1400 |
8000 |
メジャー列では、常に定義されている集計を計算します。
メジャー列は、リポジトリの「論理列」ダイアログの「集計」タブに定義されているデフォルトの集計ルールを持ちます。SQLリクエストを介してメジャー列で明示的な集計(SUM、AVG、MIN、MAXまたはCOUNT)を実行すると、実際は集計の集計を求めることになります。このようなネストされた集計の場合、Oracle BIサーバーでは次のルールに基づく集計を計算します。
SQL文で定義した集計関数のないメジャー列のリクエストは、問合せでGROUP BY句が指定されているかどうかにかかわらず、SELECTリストの非集計列のレベルで常にグループ化されます。
GROUP BY句が指定されていない場合、ネストされた集計は、SELECTリストのすべての非集計列で確定される各グループの総計になります。
GROUP BY句が指定された場合、ネストされた集計はGROUP BY句で指定された各グループの総計が計算されます。
たとえば、次の問合せを考えます。ここでは列SumOfRevenueがメジャー列としてリポジトリに定義されています。この列のデフォルト集計ルールは、SUMです(「論理列」ダイアログの「集計」タブで指定されたSUM集計ルール)。
SELECT year, product, SumOfRevenue, SUM(SumOfRevenue) FROM time, products, facts
次の問合せ結果は、年および製品でグループ化されます。つまり、製品と年の組合せごとに1行を返します。メジャー列は常に問合せの非集計列で定義されたレベルになるので、SumOfRevenue列の各行で計算される合計は、その年のその製品のすべての売上の合計になります。
| YEAR | PRODUCT | SUMofREVENUE | SUM(SUMofREVENUE) |
|---|---|---|---|
|
1998 |
Coke |
500 |
3650 |
|
1998 |
Pepsi |
600 |
3650 |
|
1999 |
Coke |
600 |
3650 |
|
1999 |
Pepsi |
550 |
3650 |
|
2000 |
Coke |
800 |
3650 |
|
2000 |
Pepsi |
600 |
3650 |
年でグループ化するようにGROUP BY句を設定すると、SumOfRevenue列で計算される合計は、その年の製品ごとの合計になります。SUM(SumOfRevenue)列で計算される合計は、特定の年の全製品の全売上になります。問合せは次のとおりです。
SELECT year, product, SumOfRevenue, SUM(SumOfRevenue) FROM time, products, facts GROUP BY year
次の結果セットでは、SumOfRevenue列の各行で計算された合計は、その年のその製品の全売上の合計になります。これは、メジャー列が常に問合せの非集計列で定義されるレベルになるためです。SUM(SumOfRevenue)は特定の年に対応する各行は同じになり、その合計はその年の売上合計を表します。ここでは、CokeとPepsiの売上の合計になります。
| YEAR | PRODUCT | SUMofREVENUE | SUM(SUMofREVENUE) |
|---|---|---|---|
|
1998 |
Coke |
500 |
1100 |
|
1998 |
Pepsi |
600 |
1100 |
|
1999 |
Coke |
600 |
1150 |
|
1999 |
Pepsi |
550 |
1150 |
|
2000 |
Coke |
800 |
1400 |
|
2000 |
Pepsi |
600 |
1400 |
表示関数は、問合せの結果セットに対して動作します。
Oracle BIサーバーでサポートされる表示関数(RANK、TOPN、BOTTOMN、PERCENTILE、NTILE、MAVG、MEDIANおよび様々な標準偏差)は、SQL問合せのSELECTリストで指定します。表示関数を使用する問合せは、次のルールに従います。
BY句が指定されていない場合、表示関数は結果セット全体に対して動作します。
BY句が指定されている場合、表示関数はBY句で指定された値セット(パーティション)ごとにその値をリセットします。
たとえば、次の問合せでは、デフォルトの集計ルールがSUMのメジャー列としてSumOfRevenueが定義されます。
SELECT year, product, SumOfRevenue, RANK(SumOfRevenue) FROM time, products, facts
次の問合せ結果セットでは、BY句が指定されていません。そのため、ランクは結果セット全体で計算されます。
| YEAR | PRODUCT | SUMofREVENUE | RANK(SUMofREVENUE) |
|---|---|---|---|
| 1998 |
Coke |
500 |
6 |
| 1998 |
Pepsi |
600 |
2 |
| 1999 |
Coke |
600 |
2 |
| 1999 |
Pepsi |
550 |
5 |
| 2000 |
Coke |
800 |
1 |
| 2000 |
Pepsi |
600 |
2 |
年と製品で分割するようにBY句を変更すると、次のようにランクが年ごとにリセットされます。
SELECT year, product, SUM(revenue), RANK(sum(revenue) by year) FROM time, products, facts GROUP BY year, product
次の結果セットでは、年が変わるごとにランクがリセットされます。年ごとに2行あるので、ランクの値は常に1か2になります。
| YEAR | PRODUCT | SUMofREVENUE | RANK(SUM(REVENUE) by year) |
|---|---|---|---|
| 1998 |
Coke |
500 |
2 |
| 1998 |
Pepsi |
600 |
1 |
| 1999 |
Coke |
600 |
1 |
| 1999 |
Pepsi |
550 |
2 |
| 2000 |
Coke |
800 |
1 |
| 2000 |
Pepsi |
600 |
2 |
集計関数内でBYを使用すると、特定レベルの集計を計算できます。
集計関数を使用する場合、集計関数内でBYを使用して集計の指定されたレベルを計算できます。BYを使用すると、GROUP BY句が不要になります。
たとえば、次の問合せは、年ごとに集計された収益を示すyear_revenue列を返します。
SELECT year, product, revenue, SUM(revenue BY year) as year_revenue FROM softdrinks
表示関数と同じ構文を使用できます。次の問合せは、年ごとの各製品の売上の全体ランク(結果セット全体における各行のランク)、およびそれぞれの年内の各製品の売上のランクを計算しています。
SELECT year, product, revenue, rank(revenue), RANK(revenue by year) FROM softdrinks ORDER BY 1, 5
FILTERは、USING条件を満たす列の引数の集計の計算に使用される、行セットを制限する演算子です。
SQL問合せ言語では、SUM、COUNT、MIN、MAXなどの従来の集計は、タプル(指定された各タイプのオブジェクトの順序付リスト)のグループで評価され、GROUP BY句で決定されます。問合せのSELECT句で指定されたすべての集計は、同じタプルのサブセット上で評価されます。条件集計は、述語を使用して入力を制限することで、SQLを展開します。
FILTER演算子は、論理SQL構造です。FILTER演算子は、メタデータを参照する論理問合せ、またはソースとして既存の論理列を使用する論理列で使用できます。
構文
条件集計は表記上の概念にすぎません。これらは実行可能な演算子を表すものではありません。条件集計は次の文に示すような関数の形式で示されます。
FILTER(measure_expr USING boolean_expr)
説明:
measure_exprは、1つ以上のメジャーを含む式です。次に例を示します。
Salesがメジャーであれば、式「Sales + 1」は使用可能です。
式productidは、productidがスカラー属性であれば許可されません。
boolean_exprは、TRUEまたはFALSEと判断されるブール式であり、メジャーを含みません。この式にはネストされた問合せを含むことはできません。
例
次に、FILTER関数の簡単な例を示します。
SELECT year, FILTER(sales USING product = 'coke'), FILTER(sales USING product = 'pepsi') FROM logBeverages
ナビゲーション後、この問合せは次のように実行されます。
SELECT year, SUM(CASE WHEN product = 'coke' THEN sales), SUM(CASE WHEN product = 'pepsi' THEN sales) FROM physBeverages WHERE product = 'coke' OR product = 'pepsi' GROUP BY year
エラー処理
たとえば、FILTER(x USING y)では、次のような場合にエラー・メッセージが返されます。
y式はブール式ではありません。
y式にメジャーが含まれます。
外部問合せブロックでFILTERが使用されています。
x (メジャー)式で明示的な集計が使用されています。例: FILTER(COUNT(product), C)。
Oracle BI EEは2つのタイプの演算子をサポートしています。
SQL論理演算子
数学演算子
SQL論理演算子は式間の比較を指定するために使用されます。
次のSQL論理演算子を使用できます。
Betweenは、条件の境界を決定するために使用されます。各境界は式です。境界が「より小さい」や「より大きい」で表される場合は、(「以下」や「以上」とは異なり)範囲には境界の制限値は含まれません。NOTをBETWEENの前に使用すると、2つの指定された境界の間に含まれない結果を検索できます。
In: 列値と一連の値との比較を指定します。
Is Null: 列値とNULL値との比較を指定します。
Like: リテラル値との比較を指定します。Likeをワイルドカード文字とともに使用して、ゼロ文字以上の任意の文字列(%)に一致するか、任意の単一の文字(_)と一致することを示します。
数学演算子は、式要素を組み合せて式内の特定のタイプの比較を作成するために使用されます。
この表に使用可能な演算子をリストし、式での使用方法を説明します。
| 演算子 | 説明 |
|---|---|
|
+ |
加算用のプラス記号。 |
|
- |
減算用のマイナス記号。 |
|
* |
乗算用の乗算記号。 |
|
/ |
除算用の除算記号。 |
|
|| |
文字列の連結。 |
|
( |
開きカッコ。 |
|
) |
閉じカッコ。 |
|
> |
大なり記号。値が比較対象より大きいことを示します。 |
|
< |
小なり記号。値が比較対象より小さいことを示します。 |
|
= |
等号。同じ値であることを示します。 |
|
<= |
以下記号。値が比較対象と同じか、比較対象より小さいことを示します。 |
|
>= |
以上記号。値が比較対象と同じか、比較対象より大きいことを示します。 |
|
<> |
等しくありません。値が比較対象より大きいか小さいが、異なることを示します。 |
|
|
|
|
|
|
|
|
|
|
, |
カンマ。リスト内の要素を区切るために使用されます。 |
このCASE文はCASE(Lookup)形式です。
expr1の値が検証され、次にWHEN式の値が検証されます。expr1の値がいずれかのWHEN式の値と一致する場合は、対応するTHEN式にその値が割り当てられます。
どのWHEN式とも値が一致しない場合は、ELSE式に指定されているデフォルト値が割り当てられます。ELSE式が指定されていない場合は、ELSE NULLが自動的に追加されます。
expr1が複数のWHEN句内の式と一致する場合は、最初に一致した式のみが割り当てられます。
注意:
CASE文では、ANDはORよりも優先されます。
構文
CASE expr1 WHEN expr2 THEN expr3 {WHEN expr... THEN expr...} ELSE expr END
説明:
CASEで、CASE文を開始します。CASE文の後には、1つの式、1つ以上のWHEN文とTHEN文、ELSE文(オプション)、およびENDキーワードを指定する必要があります。
WHENで、満たす条件を指定します。
THENで、対応するWHEN式が満たされる場合に割り当てる値を指定します。
ELSEでは、満たされるWHEN条件がない場合に割り当てる値を指定します。省略すると、ELSE NULLが指定されたものとみなされます。
ENDで、CASE文を終了します。
例
CASE Score-par WHEN -5 THEN 'Birdie on Par 6' WHEN -4 THEN 'Must be Tiger' WHEN -3 THEN 'Three under par' WHEN -2 THEN 'Two under par' WHEN -1 THEN 'Birdie' WHEN 0 THEN 'Par' WHEN 1 THEN 'Bogey' WHEN 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END
この例では、WHEN文は厳密な等式を反映する必要があります。たとえば、比較演算子は許可されないため、WHEN < 0 THEN 'Under Par'というWHEN条件は使用できません。
CASE文は各WHEN条件を評価し、条件を満たす場合は対応するTHEN式の値が割り当てられます。
一致するWHEN条件がない場合は、ELSE式に指定されているデフォルト値が割り当てられます。ELSE式が指定されていない場合は、ELSE NULLが自動的に追加されます。
注意:
CASE文では、ANDはORよりも優先されます。
構文
CASE WHEN request_condition1 THEN expr1 {WHEN request_condition2 THEN expr2} {WHEN request_condition... THEN expr...} ELSE expr END
説明:
CASEで、CASE文を開始します。CASE文の後には、1つ以上のWHEN文とTHEN文、ELSE文(オプション)、およびENDキーワードを指定する必要があります。
WHENで、満たす条件を指定します。
THENで、対応するWHEN式が満たされる場合に割り当てる値を指定します。
ELSEでは、満たされるWHEN条件がない場合に割り当てる値を指定します。省略すると、ELSE NULLが指定されたものとみなされます。
ENDで、CASE文を終了します。
例
CASE WHEN score-par < 0 THEN 'Under Par' WHEN score-par = 0 THEN 'Par' WHEN score-par = 1 THEN 'Bogie' WHEN score-par = 2 THEN 'Double Bogey' ELSE 'Triple Bogey or Worse' END
Switch形式のCASE文とは異なり、If形式のWHEN文では比較演算子が許可されます。たとえば、WHEN < 0 THEN 'Under Par'というWHEN条件を使用できます。
SQLの各タイプのリテラルの表現方法を説明します。
リテラルとは、任意のデータ型に対応するNULLでない値です。リテラルは通常定数値です。つまり、何も変更せずに、文字どおりそのまま使用される値です。リテラル値は、それが表すデータ型に準拠する必要があります。
SQLでは、SQL文でリテラルを表現するメカニズムが用意されています。このトピックでは、SQLで各種リテラルを表現する方法について説明します。
文字リテラルは、CHARACTERまたはVARCHARのデータ型の値を表します。
文字リテラルを表現するには、文字列を一重引用符(')で囲みます。リテラルの長さは、一重引用符の間の文字数で決定されます。
例
'Oracle BI Server'
'abc123'
3つの型指定された日時リテラルについて説明します。
SQL 92標準では、次の形式で3種類の型指定された日時リテラルが定義されています。
DATE 'yyyy-mm-dd' TIME 'hh:mm:ss' TIMESTAMP 'yyyy-mm-dd hh:mm:ss'
型指定された日時リテラルを表現するには、前述の例に示したように、キーワードのDATE、TIMEまたはTIMESTAMPを使用して、その後に一重引用符で囲んだ日時文字列を指定します。値が1桁の場合でも、年以外のすべてのコンポーネントには2桁が必要です。
これらの形式は固定で、NQSConfig.INIファイルのDATE_DISPLAY_FORMAT、TIME_DISPLAY_FORMATまたはDATE_TIME_DISPLAY_FORMATパラメータで指定される形式の影響を受けません。
例
DATE '2000-08-15' TIME '11:55:25' TIMESTAMP '1999-03-15 11:55:25'
数値リテラルは、数値データ型(INTEGER、DECIMAL、FLOATなど)の値を表します。
数値リテラルを表現するには、数値をSQL文の一部として入力します。
数値リテラルは一重引用符で囲まないでください。一重引用符で囲むと、リテラルを文字リテラルとして表現することになります。
注意:
NQSConfig.INIでENABLE_NUMERIC_DATA_TYPEがYESに設定されていると、すべての小数リテラル(またはINTデータ型に収まるには大きすぎる整数リテラル)は、Oracle BIサーバー内でNUMERICとして解析されます。
リテラルをNUMERICとして扱う場合、次を含むOracle標準DOUBLEプロモーション・ルールに注意してください。
DOUBLE/NUMBER = DOUBLE , DOUBLE * NUMBER = DOUBLE
数値リテラルの解析は、実際のデータ・ソースがわかる前に問合せ処理の非常に早い段階で発生するため、内部的には、Oracle BIサーバーはENABLE_NUMERIC_DATA_TYPEがYESに設定されている場合、データ・ソース・タイプに関係なく小数をNUMERICとして処理します。
NUMERICが有効でOracle BIサーバーが内部的に小数リテラルを含む式を実行する場合、バックエンド・データ・ソースがNUMERICデータ型をサポートしていない場合でも、サーバーはリテラルをNUMERICとして扱います。ただし、タイプ・プロモーション・ルールは引き続き適用されます。たとえば、Oracle BIサーバーがデータ・ソースからデータをDOUBLEとして取得して内部実行中にNUMERICリテラルと組み合せる場合、最終結果はDOUBLEに変換されます。
次の数値リテラルがあります。
整数定数をリテラルとして表現するには、整数をSQL文の一部として、たとえばSELECTリスト内で入力します。
整数の前にプラス記号(+)かマイナス記号(-)を付けると、その整数がそれぞれ正の数であるか負の数であるかを示すことができます。記号なしの整数は正の数とみなされます。
例
234 +2 567934
小数リテラルを表現するには、小数を入力します。
小数の前にプラス記号(+)かマイナス記号(-)を付けると、その小数がそれぞれ正の数であるか負の数であるかを示すことができます。記号なしの整数は正の数とみなされます。
例
1.223 -22.456 +33.456789
計算済メンバーは、メジャー値が実行時に計算されるユーザー定義のディメンション・メンバーです。
計算済メンバーは、同じディメンションの他のメンバーを参照する式を使用してディメンション内に定義します。ディメンションに複数の階層がある場合、式で参照されるすべてのメンバーは1つの階層に属する必要があります。
計算済メンバーの中では、メンバーは階層内の異なるレベルに存在できます。たとえば、Geography階層では、計算済メンバーを作成して、CountryメンバーのFranceとCityメンバーのBudapestのメジャー値をともに追加することができます。
計算済メンバーの3つの標準コンポーネントは次のとおりです。
計算済メンバーのベースとなるプレゼンテーション階層。例: Geography
計算済メンバーを識別し、ディメンション内の他のメンバーと区別するための名前。例: My Locations
計算済メンバーの計算に使用される式。1つ以上のMember句が含まれます。例: Member ("Geography"."Country".'France') + Member ("Geography"."City".'Budapest')
この項では、次の項目について説明します。
CALCULATEDMEMBER関数構文には、プレゼンテーション階層とプレゼンテーション階層レベルで使用するためのルールが含まれます。
CALCULATEDMEMBER(presentation_hierarchy, member_identifier, calculated_member_formula [, solve_order])
説明:
presentation_hierarchyは、次のように計算済メンバーに基づくプレゼンテーション・レイヤーの完全修飾されたプレゼンテーション階層を識別します。
"subject_area"."presentation_table"."presentation_hierarchy"
注意:
CALCULATEDMEMBER式とcalculated_member_formula パラメータ内のMember句の両方でプレゼンテーション階層およびプレゼンテーション階層レベルを修飾する場合、次のルールが適用されます。
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がある場合、修飾語("subject_area")を指定する必要があります。ない場合は省略できます。
member_identifierは、計算済メンバーを識別する文字列または数値リテラルです。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
calculated_member_formulaは、標準の数値演算子である「+」、「-」、「*」、「/」、「(」、「)」で結び付けられた1つ以上のmember句の例で構成されます。member句の構文は、プレゼンテーション階層がレベルベースかまたは親子かどうかに依存します。「レベルベース階層のMember句の構文」および「親子階層のMember句の構文」を参照してください。
solve_order (オプション)は、正の整数で、同じ問合せ内の異なるディメンションから計算されたメンバーがある場合に、評価の順序を決定する際に使用されます。「解決順序を使用した式評価順序の制御」を参照してください。
レベルベース階層のMember句の構文
MEMBER(presentation_hierarchy_level, member_value)
説明:
presentation_hierarchy_level は、次のようにpresentation_hierarchyで完全修飾された階層レベルを識別します。
"subject_area"."presentation_table"."presentation_hierarchy"."presentation_level"
member_value は、presentation_hierarchy_level でメンバーを識別する文字列または数値リテラルです。
親子階層のMember句の構文
MEMBER(presentation_hierarchy, member_value)
説明:
presentation_hierarchyは、次のように計算済メンバーに基づくプレゼンテーション・レイヤーの完全修飾されたプレゼンテーション階層を識別します。
"subject_area"."presentation_table"."presentation_hierarchy"
member_value は、presentation_hierarchyでメンバーを識別する文字列または数値リテラルです。
計算済メンバーのルールは、CALCULATEDMEMBER式、および問合せで使用されるCALCULATEDMEMBER式に関連します。
特定のCALCULATEDMEMBER式のすべてのレベル参照は、同じディメンション階層に属する必要があります。
CALCULATEDMEMBER式は、問合せのSELECTリスト内でのみ使用できます。
CALCULATEDMEMBER式が含まれる問合せでは、SELECTリストにメジャー列を1つ以上挿入する必要があります。
問合せブロックの各SELECT式の各ディメンションに1つのCALCULATEDMEMBER式を使用できます。ただし、他のディメンションに基づくCALCULATEDMEMBER式を同じ問合せに入れることもできます。
計算済メンバーが次の問合せブロックのコンポーネントに基づくディメンションからの他の列を含めることはできません。
SELECTリスト
WHERE句
HAVING句
ただし、サブ問合せ内の計算済メンバーのディメンションからの列を参照することはできます。
同じ問合せブロック内の他のディメンションにCALCULATEDMEMBER式がなければ、そのディメンションからの列を参照できます。
デフォルトでは、CALCULATEDMEMBER式に解決順序が含まれていない場合、計算済メンバーはSELECTリスト内での並び順に評価されます。
同じ問合せブロック内に異なるディメンションからの計算済メンバーがある場合、Oracle BIサーバーで計算済メンバーを評価する順序が重要になります。
誤った解決順序により不正な結果がどのように導き出されるかを次に示します。
解決順序の使用
次のようなアカウントと時間データがあるとします。
| Kwik Grains | 利益 | 売上 |
|---|---|---|
2007 Q3 |
300 |
1000 |
2007 Q4 |
600 |
1500 |
各期間および2つの四半期の合計で利益率(Profit / Sales * 100)を計算できます。
次のような計算の解決順序を考えます。
'Profit%' = 'Profit'/'Sales' * 100
'2007 Second Half' = '2007 Q3' + '2007 Q4'
ここで、「2007 Second Half」の利益率は、誤って2007 Q3の利益と2007 Q4の利益を加算して計算しています。
(300/1000) + (600/1500) = 30% + 40% = 70%
その結果、次のようになります。
| Kwik Grains | 利益 | 売上 | 利益率 |
|---|---|---|---|
2007 Q3 |
300 |
1000 |
30 |
2007 Q4 |
600 |
1500 |
40 |
2007 Second Half |
900 |
2500 |
70 (不正な結果) |
次のような計算の解決順序を考えます。
'2007 Second Half' = '2007 Q3' + '2007 Q4'
'Profit%' = 'Profit'/'Sales' * 100
「2007 Second Half」の利益率を正しく計算しなおします。まず、2007 Q3と2007 Q4の利益および売上を加算してから利益合計を売上合計で除算します。
(300+600) / (1000+1500) = 900/2500 = 36%
その結果、次のようになります。
| Kwik Grains | 利益 | 売上 | 利益率 |
|---|---|---|---|
2007 Q3 |
300 |
1000 |
30 |
2007 Q4 |
600 |
1500 |
40 |
2007 Second Half |
900 |
2500 |
36 (正しい結果) |
解決順序を明示的に指定する問合せの例は、「問合せの計算済メンバーの例」を参照してください。
これらの例では、問合せでの計算済メンバーおよび計算を実行するベース・データの使用方法を示します。
単一の計算済メンバーの問合せ
この例では、2つの問合せとそれぞれに対応する結果を示します。
最初の問合せには計算済メンバーが含まれます。
SELECT CALCULATEDMEMBER(product."Product - Region",'USA - LA - Tokyo', MEMBER(product."Product - Region"."Country", 'USA') - MEMBER(product."Product - Region"."Region", 'LA') - MEMBER(product."Product - Region"."City", 'Tokyo') ) MyRegion, sales.Revenue Revenue, sales.QtySold QtySold FROM product, sales;
結果:
MYREGION REVENUE QTYSOLD
USA - LA - Tokyo 61959.00 3959
2番目の問合せでは、最初の問合せの結果を検証します。ここでは、最初の問合せの計算が実行されたベース・データを示します。
SELECT * from SupplierCity where Country in ('USA', 'Japan');
結果:
CITY REGION COUNTRY REVENUE QTYSOLD
Boston MA USA 28146.40 2084
Osaka Japan 15678.30 1417
New Orleans LA USA 33351.95 1735
Ann Arbor MI USA 43569.00 1436
Tokyo Japan 33533.20 1134
Bend OR USA 23776.80 1573
異なるディメンションからの計算済メンバーの使用
この例の要件は、USとCanadaのRevenueとQuantity Soldの増加率の経緯を確認することです。
正しい結果を得るためには、解決順序が重要になります。まずこの全期間の2つの国のRevenueとQuantity Soldを加算してから、割合の計算を実行します。「解決順序を使用した式評価順序の制御」を参照してください。
この例では、2つの問合せとそれぞれに対応する結果を示します。
最初の問合せには、「Product - Region」および「Time」の2つのディメンションからの計算済メンバーが含まれ、最初に加算式が計算されてから、割合式が計算されます。
SELECT CALCULATEDMEMBER(product."Product - Region", 'North America', MEMBER(product."Product - Region"."Country", 'USA') + MEMBER(product."Product - Region"."Country", 'Canada'), 1 ) MyRegion, CALCULATEDMEMBER(day."Time", 'Percentage Increase', ( MEMBER(day."Time"."Year", 1996) - MEMBER(day."Time"."Year", 1995) ) * 100 / MEMBER(day."Time"."Year", 1995), 2 ) MyTime, sales.Revenue RevenuePC, sales.QtySold QtySoldPC FROM product, sales, day;
結果:
MYREGION MYTIME REVENUEPC QTYSOLDPC
North America Percentage Increase 16 35
前述の問合せでは、明示的な解決順序はありませんでしたが、SELECTリストの計算済メンバーの順序で、十分に正しい結果を得られました。完成には開発順序も含まれます。
2番目の問合せでは、最初の問合せの結果を検証します。ここでは、最初の問合せの計算が実行されたベース・データを示します。
SELECT CALCULATEDMEMBER(product."Product - Region", 'North America', MEMBER(product."Product - Region"."Country", 'USA') + MEMBER(product."Product - Region"."Country", 'Canada') ) MyRegion, year as Year, sales.Revenue Revenue, sales.QtySold QtySold FROM product, sales, day;
結果:
MYREGION YEAR REVENUE QTYSOLD
North America 1996 101702.75 4918
North America 1995 87265.10 3638
North America 1994 30776.00 1616
SQL文に変数を使用および設定できます。
これを行うには、SQL文の先頭に変数を指定します。
構文
SET VARIABLE variable_name = variable_value; SELECT_statement
nqcmdユーティリティの問合せを実行する場合、デリミタとしてコロンを使用します。その他の場合には、セミコロンまたはコロンを使用できます。
例
SET VARIABLE LOGLEVEL = 3; SELECT Products.Brand, Measures.Dollars FROM "Products" SET VARIABLE DISABLE_CACHE_HIT=1, LOGLEVEL = 3, WEBLANGUAGE='en': SELECT Products.Brand, Measures.Dollars FROM "Products"
集計関数は、複数の値に対して演算を実行し、サマリー結果を作成します。
集計関数は、「論理列」ダイアログの「集計」タブで定義されたデフォルトの集計ルールを持つ論理列の式で、ネストされた集計を作成するために使用することできません。ネストされた集計を指定するには、デフォルトの集計ルールを持つ列を定義してから、SQL文で列の集計をリクエストする必要があります。
次の集計関数があります。
Aggregate At関数は、キーワードATで指定したレベルの集約を行います。
構文
指定するレベルに基づいて列を集計します。AGGREGATE ATを使用すると、メジャーの集計がWHERE句に関係なく、常にキーワード ATの後に指定したレベルで確実に実行されます。
AGGREGATE(expr AT level [, level1, levelN])
説明:
exprは、1つ以上のメジャー列を参照する任意の式です
levelは、集計するレベルです。オプションで、複数のレベルを指定できます。
1番目の引数で指定したメジャーのメジャー・レベルとして使用されるレベルを含むディメンションのレベルを指定できません。たとえば、monthはyearly_salesのメジャー・レベルとして使用される同じ時間ディメンションからのものであるため、この関数でAGGREGATE(yearly_sales AT month)と指定することはできません。
例
次に、AGGREGATE AT関数と実行結果の例を示します。
SELECT month, year, AGGREGATE(sales AT Year) FROM timeseriestesting WHERE year = 1994 AND month = 12
結果:
Month Year AGGREGATE AT year 12 1994 7396 Row count: 1
AGGREGATE AT演算子は常に述語の前に実行されるため、キーワード ATの後に指定された時間レベルの正確な合計を常に返します。
AVG関数は、結果セット内で式の平均値を計算します。
AVG関数は引数として数値式を指定する必要があります。
AVGの分母は集計された列数であることに注意してください。このため、通常、Oracle Business Intelligenceの計算でAVG( x)を使用することは誤りです。かわりに、分子と分母の両方(x/y)を制御できるように式を手動で指定してください。
構文
AVG(numExpr)
説明:
numExprは、数値に評価される任意の式です。
AVGDISTINCT関数は、式のすべての個別の値の平均を計算します。
AVGDISTINCT関数は引数として数値式を取ります。
構文
AVG(DISTINCT numExpr)
説明:
numExprは、数値に評価される任意の式です。
BOTTOMN関数は、式引数の最下位のnの値を1からn番目までランク付けします。1が最下位の数値です。
BOTTOMN関数は、結果セットに返される値を操作します。リクエストには、BOTTOMN式を1つのみ含めることができます。
構文
BOTTOMN(numExpr, integer)
説明:
numExprは、数値に評価される任意の式です。
integerは任意の正の整数です。結果セットに表示されるランキングの最下位数を表し、1が最低ランクです。
COUNT関数は、式のNULL以外の値を含む行数を計算します。
通常、式は列名です。その場合、その列のNULL以外の値を含む行数が返されます。
構文:
COUNT(expr)
説明:
exprは任意の式です。
COUNT(*)関数は行数をカウントします。
構文
COUNT(*)
例
たとえば、Factsという表に200,000,000行あった場合、サンプル・リクエストでは次のような結果が返されます。
SELECT COUNT(*) FROM Facts
結果:
200000000
FIRST関数は、式引数の最初の非NULL値を選択します。
プライマリ・レベル・キーではなく時系列キーに基づいて最初の値を計算する必要がある場合、または最初の値がNULLであるかどうかに関係なくそれを返す必要がある場合は、FIRST関数を使用しないでください。かわりにFIRST_PERIOD関数を使用します。「FIRST_PERIOD」を参照してください。
FIRST関数の使用は、リポジトリ内のディメンション固有の集計ルールの定義に限定されます。これを、SQL文に使用することはできません。
FIRST関数は、明示的に定義されたディメンション内で指定された最も詳細なレベルで操作されます。たとえば、時間ディメンションを階層レベルの日、月、年で定義した場合、FIRST関数は各レベルの最初の日を返します。
FIRST関数は、最初のディメンション固有の集計ルールとして使用しないでください。そうすることで、問合せによりOracle BIサーバーの処理で大量の行が返されることになり、パフォーマンスが低下する場合があります。
メジャーがディメンションに基づいており、データの密度が高い場合、Oracle BIサーバーはデータベースに送信されるSQL文を最適化し、パフォーマンスを向上させます。『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』のメジャー列集計のデフォルト・レベルの設定に関する項を参照してください。
注意:
PERIODROLLING、FIRST、FIRST_PERIOD、LASTおよびLAST_PERIOD関数はネストできません。
構文
FIRST(expr)
説明:
exprは、1つ以上のメジャー列を参照する任意の式です。
例
FIRST(sales)
FIRST_Period関数は、式引数の最初に返された値を選択します。
FIRST_PERIOD関数は年の最初の日の値を計算できます。
FIRSTおよびFIRST_PERIOD関数は異なるセマンティクスを使用します。FIRSTは、特定の期間の引数の最初に存在する、記録されている値を返します。FIRST_PERIODは、特定の期間の最初の時系列キーに対応する値を返すか、その値が見つからなかった場合は何も返しません。
たとえば、次の日付および売上表について考えます。
| Date | Inventory |
|---|---|
| Jan 5 |
10 |
| Jan 10 |
20 |
Inventoryが集計ルールFIRSTを使用したメジャーである場合、問合せSELECT Month, Inventoryの値は10 (つまり、最初に記録された値)です。
Inventoryが集計ルールFIRST_PERIODを使用したメジャーである場合、標準のカレンダ表を使用していると想定して1月1日に記録されたInventoryがないため、同じ問合せの値は空セットになります。
注意:
FIRST_PERIOD関数には次の制限事項があります。
FIRST_PERIOD関数の使用は、リポジトリ内のディメンション固有の集計ルールの定義に限定されます。
FIRST_PERIOD関数を使用する場合、管理ツールの論理列集計タブで「データが密である」フィールドを選択する必要があります。このフィールドを選択しないと、集計ルールはFIRST関数に戻ります。
FIRST_PERIOD関数はSQL文で使用できません。
FIRST_PERIOD関数は、時間ディメンションとマークされたディメンションにのみ適用可能です。他のディメンションには、FIRST関数を使用します。
複数の時間ディメンションにディメンション依存のメジャーを定義している場合、FIRST_PERIOD関数を使用しないでください。ディメンション固有の集計ルールを定義する場合は、かわりに、FIRST関数を使用します。
FIRST_PERIOD関数は、最初のディメンション固有の集計ルールとして使用しないでください。そうすることで、問合せによりOracle BIサーバーの処理で大量の行が返されることになり、パフォーマンスが低下する場合があります。
PERIODROLLING、FIRST、FIRST_PERIOD、LASTおよびLAST_PERIOD関数はネストできません。
FIRST_PERIOD関数は、明示的に定義されたディメンション内で指定された最も詳細なレベルで操作されます。たとえば、時間ディメンションを階層レベルの日、月、年で定義した場合、FIRST_PERIOD関数は各レベルの最初の日を返します。
メジャーがディメンションに基づいており、データの密度が高い場合、Oracle BIサーバーはデータベースに送信されるSQL文を最適化し、パフォーマンスを向上させます。『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』のメジャー列集計のデフォルト・レベルの設定に関する項を参照してください。
構文
FIRST_PERIOD(expr)
説明:
exprは、1つ以上のメジャー列を参照する任意の式です。
FIRST_PERIOD(sales)
GROUPBYCOLUMNは、集計ナビゲーションの設定に使用されます。
これにより、物理集計表に存在する集計データのレベルを定義する論理列を指定します。
たとえば、集計表に店舗および月でグループ化されるデータが含まれる場合、コンテンツ・フィルタで次の構文を指定します(論理ソース・ダイアログの「一般」タブ)。
GROUPBYCOLUMN(STORE, MONTH)
GROUPBYCOLUMN関数は、リポジトリの構成にのみ使用されます。これをSQL文から使用することはできません。
GROUPBYLEVELは、集計ナビゲーションの設定に使用されます。
これにより、物理集計表に存在する集計データのレベルを定義するディメンション・レベルを指定します。
たとえば、集計表に店舗および月レベルでデータが保存されており、これらのレベルを含むディメンション(GeographyおよびCustomers)を定義している場合、コンテンツ・フィルタで次の構文を指定します(論理ソース・ダイアログの「一般」タブ)。
GROUPBYLEVEL(GEOGRAPHY.STORE, CUSTOMERS.MONTH)
GROUPBYLEVEL関数は、リポジトリの構成にのみ使用されます。これをSQL文から使用することはできません。
LAST関数は、式の最後の非NULL値を選択します。
プライマリ・レベル・キーではなく時系列キーに基づいて最後の値を計算する必要がある場合、または最後の値がNULLであるかどうかに関係なくそれを返す必要がある場合は、LAST関数を使用しないでください。かわりにLAST_PERIOD関数を使用します。「LAST_PERIOD」を参照してください。
LAST関数の使用は、リポジトリ内のディメンション固有の集計ルールの定義に限定されます。これを、SQL文に使用することはできません。
LAST関数は、明示的に定義されたディメンション内で指定された最も詳細なレベルで操作されます。たとえば、時間ディメンションを階層レベルの日、月、年で定義した場合、LAST関数は各レベルの最後の日を返します。
LAST関数は、最初のディメンション固有の集計ルールとして使用しないでください。そうすることで、問合せによりOracle BIサーバーの処理で大量の行が返されることになり、パフォーマンスが低下する場合があります。
メジャーがディメンションに基づいており、データの密度が高い場合、Oracle BIサーバーはデータベースに送信されるSQL文を最適化し、パフォーマンスを向上させます。『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』のメジャー列集計のデフォルト・レベルの設定に関する項を参照してください。
PERIODROLLING、FIRST、FIRST_PERIOD、LASTおよびLAST_PERIOD関数はネストしないでください。
構文
LAST(expr)
説明:
exprは、1つ以上のメジャー列を参照する任意の式です。
例
LAST(sales)
LAST_PERIOD関数は、式の最後に返された値を選択します。
たとえば、LAST_PERIOD関数は年の最後の日の値を計算できます。
LAST_PERIOD関数は、明示的に定義されたディメンション内で指定された最も詳細なレベルで操作されます。たとえば、時間ディメンションを階層レベルの日、月、年で定義した場合、LAST_PERIOD関数は各レベルの最後の日を返します。
LASTおよびLAST_PERIOD関数はセマンティクスが異なることに注意してください。LASTは、特定の期間の引数の最後に存在する、記録されている値を返します。一方、LAST_PERIODは、特定の期間の最後の時系列キーに対応する値を返すか、その値が見つからなかった場合に何も返しません。
たとえば、次の日付および売上表について考えます。
| Date | Inventory |
|---|---|
| Jan 1 |
10 |
| Jan 5 |
20 |
Inventoryが集計ルールLASTを使用したメジャーである場合、問合せSELECT Month, Inventoryの値は20 (つまり、最後に記録された値)です。
Inventoryが集計ルールLAST_PERIODを使用したメジャーである場合、標準のカレンダ表を使用していると想定して1月31日に記録されたInventoryがないため、同じ問合せの値は空セットになります。
次のLAST_PERIOD関数の制限事項に注意してください。
LAST_PERIOD関数の使用は、リポジトリ内のディメンション固有の集計ルールの定義に限定されます。
LAST_PERIOD関数を使用する場合、Oracle BI管理ツールの論理列集計タブで「データが密である」フィールドを選択する必要があります。このフィールドを選択しないと、集計ルールはLAST関数に戻ります。
LAST_PERIOD関数はSQL文で使用できません。
LAST_PERIOD関数は、時間ディメンションにのみ適用可能です。他のディメンションには、LAST関数を使用します。
複数の時間ディメンションにディメンション依存のメジャーを定義している場合、LAST_PERIOD関数を使用しないでください。ディメンション固有の集計ルールを定義する場合は、かわりに、LAST関数を使用します。
LAST_PERIOD関数は、最初のディメンション固有の集計ルールとして使用しないでください。そうすることで、問合せによりOracle BIサーバーの処理で大量の行が返されることになり、パフォーマンスが低下する場合があります。
PERIODROLLING、FIRST、FIRST_PERIOD、LASTおよびLAST_PERIOD関数はネストできません。
メジャーがディメンションに基づいており、データの密度が高い場合、Oracle BIサーバーはデータベースに送信されるSQL文を最適化し、パフォーマンスを向上させます。『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』のメジャー列集計のデフォルト・レベルの設定に関する項を参照してください。
構文
LAST_PERIOD(expr)
説明:
exprは、1つ以上のメジャー列を参照する任意の式です。
例
LAST_PERIOD(sales)
MAX関数は、数値式引数に一致する行の最大値(最も高い数値)を計算します。
構文
MAX(numExpr)
説明:
numExprは、数値に評価される任意の式です。
MAX関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
MEDIAN関数は、数値式引数に一致する行のメジアン(中央)値を計算します。
偶数の行がある場合、中央値は2つの中間行の平均です。この関数は常にdoubleを返します。
構文
MEDIAN(numExpr)
説明:
numExprは、数値に評価される任意の式です。
MEDIAN関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
MIN関数は、数値式引数に一致する行の最小値(最も低い数値)を計算します。
構文
MIN(numExpr)
説明:
numExprは、数値に評価される任意の式です。
MIN関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
NTILE関数は、ユーザー指定範囲内での値のランクを決定します。
範囲のランクを示す整数を返します。つまり、結果のソートされたデータ・セットは、各タイルにほぼ同数の値がある複数のタイルに分割されます。
numTiles= 100を指定したNTileは、一般にパーセンタイル(1から100までの範囲の数値で、100がこの分割の最高値)と呼ばれる値を返します。この値はOracle BI EE PERCENTILE関数の結果とは異なります。この関数は、SQL 92ではパーセント・ランクと呼ばれ、0から1の値を返します。
構文
NTILE(numExpr, numTiles)
説明:
numExprは、数値に評価される任意の式です。
numTilesは、タイルの数を表すNULL以外の正の整数です。
numExpr引数がNULLではない場合、この関数はリクエストした範囲内のランクを表す整数を返します。
PERCENTILE関数は、数値式引数に一致する各値のパーセント・ランクを計算します。
パーセンタイル・ランクの範囲は、0 (1番目のパーセンタイル)から1 (100番目のパーセンタイル)です。
パーセンタイルは結果セットの値に基づいて計算されます。
構文
PERCENTILE(numExpr)
説明:
numExprは、数値に評価される任意の式です。
PERCENTILE関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
RANK関数は、数値式引数に一致する各値のランクを計算します。
RANK関数は、数値式引数に一致する各値のレベルを計算します。最も高い数値にはランク1が割り当てられ、次に続くランクには2、3、4などの連続した整数が割り当てられます。特定の値が等しい場合、同じランクが割り当てられます(例: 1、1、1、4、5、5、7...)。
ランクは結果セットの値に基づいて計算されます。
構文
RANK(numExpr)
説明:
numExpr は、数値に評価される任意の式です。
RANK関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
STDDEV関数は、値のセットの標準偏差を返します。
戻り型は常にdoubleです。STDEV_SAMPはSTDDEVの類義語です。
構文
STDDEV([ALL | DISTINCT] numExpr)
説明:
numExprは、数値に評価される任意の式です。
ALLが指定されると、セット内のすべてのデータに対して標準偏差が計算されます。
DISTINCTが指定されると、計算のすべての偏差は無視されます。
何も指定しない(デフォルト)場合、すべてのデータが考慮されます。
STDDEV関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
STDDEV_POP関数は、母分散と標準偏差の計算式を使用して、値のセットの標準偏差を返します。
構文
STDDEV_POP([ALL | DISTINCT] numExpr)
説明:
numExprは、数値に評価される任意の式です。
ALLが指定されると、セット内のすべてのデータに対して標準偏差が計算されます。
DISTINCTが指定されると、計算のすべての偏差は無視されます。
何も指定しない(デフォルト)場合、すべてのデータが考慮されます。
SUM関数は、数値式引数に一致するすべての値を加算して得られる合計を計算します。
構文
SUM(numExpr)
説明:
numExprは、数値に評価される任意の式です。
SUM関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
SUMDISTINCT関数は、数値式引数に一致するすべての値を個別に加算して得られる合計を計算します。
構文
SUM(DISTINCT numExpr)
説明:
numExprは、数値に評価される任意の式です。
TOPN関数は、式引数の最上位のnの値を1からn番目までランク付けします。1が最上位の数値です。
TOPN関数は、結果セットに返される値を操作します。リクエストには、TOPN式を1つのみ含めることができます。
構文
TOPN(numExpr, integer)
説明:
numExprは、数値に評価される任意の式です。
integerは任意の正の整数です。結果セットに表示されるランキングの最上位数を表し、1が最高ランクです。
TOPN関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
集計実行関数は、入力として一連のレコードを取得するという点で集計関数と似ていますが、一連のレコード全体に対して単一の集計を出力するかわりに、そこまでに処理されたレコードに基づく集計を出力します。
この項では、Oracle BIサーバーでサポートされている集計実行関数について説明します。次の関数があります。
MAVG関数は、現在の行を含めて、結果セットのデータの最後のn行の移動平均を計算します。
最初の行の平均は、最初の行の数値式と同じです。2番目の行の平均は、最初の2つの行のデータ平均を取得することで計算されます。3番目の行の平均は、最初の3つの行のデータ平均を取得することで計算されます。このようにn番目の行に到達するまで同様に実行されます。平均は最後のn行のデータに基づいて計算されます。
構文
MAVG(numExpr, integer)
説明:
numExprは、数値に評価される任意の式です。
integerは任意の正の整数です。最後のn行のデータ平均を表します。
MAVG関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
MSUM関数は、現在の行を含めて、データの最後のn行の移動合計を計算します。
最初の行の合計は、最初の行の数値式と同じです。2番目の行の合計は、最初の2つの行のデータ合計を取得することで計算されます。3番目の行の合計は、最初の3つの行のデータ合計を取得することで計算され、同様に続いて実行されます。n番目の行に到達すると、合計は最後のn行のデータに基づいて計算されます。
構文
MSUM(numExpr, integer)
説明:
numExprは、数値に評価される任意の式です。
integerは任意の正の整数です。最後のn行のデータ平均を表します。
MSUM関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
例
この例では、MSUM関数を使用する問合せと問合せ結果を示します。
select month, revenue, MSUM(revenue, 3) as 3_MO_SUM from sales_subject_area
結果:
MONTH REVENUE 3_MO_SUM JAN 100.00 100.00 FEB 200.00 300.00 MAR 100.00 400.00 APRIL 100.00 400.00 MAY 300.00 500.00 JUNE 400.00 800.00 JULY 500.00 1200.00 AUG 500.00 1400.00 SEPT 500.00 1500.00 OCT 300.00 1300.00 NOV 200.00 1000.00 DEC 100.00 600.00
RSUM関数は、そこまでに処理されたレコードに基づいて累計を計算します。
最初の行の合計は、最初の行の数値式と同じです。2番目の行の合計は、最初の2つの行のデータ合計を取得することで計算されます。3番目の行の合計は、最初の3つの行のデータ合計を取得することで計算され、同様に続いて実行されます。
構文
RSUM(numExpr)
説明:
numExprは、数値に評価される任意の式です。
RSUM関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
Oracle BIアンサーでは、次の代替構文も使用できます。
RSUM(expression1 [BY expression2[, expression3[, ...]]])
説明:
「アンサー」では、expression1, expression2, expression3 ...は、任意の列参照または列参照での算術式を表すことができます。
BY句により、RSUMの計算はBY列のいずれかの値が前の行と異なる行で再度開始します。
例
この例では、RSUM関数を使用する問合せと問合せ結果を示します。
SELECT month, revenue, RSUM(revenue) as RUNNING_SUM from sales_subject_area
結果:
MONTH REVENUE RUNNING_SUM JAN 100.00 100.00 FEB 200.00 300.00 MAR 100.00 400.00 APRIL 100.00 500.00 MAY 300.00 800.00 JUNE 400.00 1200.00 JULY 500.00 1700.00 AUG 500.00 2200.00 SEPT 500.00 2700.00 OCT 300.00 3000.00 NOV 200.00 3200.00 DEC 100.00 3300.00
RCOUNT関数は、入力として一連のレコードを取得して、そこまでに処理されたレコード数をカウントします。
構文
RCOUNT(expr)
Oracle Business Intelligenceでは、次のようになります。
exprは任意のデータ型の式です。
RCOUNT関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
Oracle BIアンサーでは、次の代替構文を使用できます。
RCOUNT(expression1 [BY expression2[, expression3[, ...]]])
「アンサー」では、次のようになります。
expression1, expression2, expression3 ...では、任意の列参照または列参照の算術式を使用できます。
BY句により、RCOUNTの計算はBY列のいずれかの値が前の行と異なる行で再度開始します。
例
この例では、RCOUNT関数を使用する問合せと問合せ結果を示します。
select month, profit, RCOUNT(profit) from sales_subject_area where profit > 200
結果:
MONTH PROFIT RCOUNT(profit) MAY 300.00 2 JUNE 400.00 3 JULY 500.00 4 AUG 500.00 5 SEPT 500.00 6 OCT 300.00 7
RMAX関数は、入力として一連のレコードを取得して、指定したポイントまでに処理されたレコードを対象とし、その最大値を表示します。
関連付けられたソート順を持つデータ型を使用する必要があります。
構文
RMAX(expr)
説明:
exprは任意のデータ型です。
RMAX関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
「アンサー」では、Oracle BI Answersの次の代替構文も使用できます。
RMAX(expression1 [BY expression2[, expression3[, ...]]])
説明:
expression1, expression2, expression3 ...文字列は、任意の列参照または列参照の算術式に使用します。
BY句により、RMAXの計算はBY列のいずれかの値が前の行と異なる行で再度開始します。
例
この例では、RMAX関数を使用する問合せと問合せ結果を示します。
SELECT month, profit, RMAX(profit) from sales_subject_area
結果:
MONTH PROFIT RMAX(profit) JAN 100.00 100.00 FEB 200.00 200.00 MAR 100.00 200.00 APRIL 100.00 200.00 MAY 300.00 300.00 JUNE 400.00 400.00 JULY 500.00 500.00 AUG 500.00 500.00 SEPT 500.00 500.00 OCT 300.00 500.00 NOV 200.00 500.00 DEC 100.00 500.00
RMIN関数は、入力として一連のレコードを取得して、そこまでに処理されたレコードに基づいて最小値を表示します。
関連付けられたソート順を持つデータ型を使用する必要があります。
構文
RMIN(expr)
説明:
exprは任意のデータ型の式です。データ型は関連付けられたソート順を持つ必要があります。
RMIN関数は、固有のルールに従って問合せ内の各グループでその値をリセットします。「表示関数のリセットの動作」を参照してください。
Oracle BIアンサーでは、次の代替構文も使用できます。
RMIN(expression1 [BY expression2[, expression3[, ...]]])
説明:
任意のexpression1, expression2, expression3 ...文字列を、列参照または列参照の算術式に使用します。
BY句により、RMINの計算はBY列のいずれかの値が前の行と異なる行で再度開始します。
例
この例では、RMIN関数を使用する問合せと問合せ結果を示します。
select month, profit, RMIN(profit) from sales_subject_area
結果:
MONTH PROFIT RMIN(profit) JAN 400.00 400.00 FEB 200.00 200.00 MAR 100.00 100.00 APRIL 100.00 100.00 MAY 300.00 100.00 JUNE 400.00 100.00 JULY 500.00 100.00 AUG 500.00 100.00 SEPT 500.00 100.00 OCT 300.00 100.00 NOV 200.00 100.00 DEC 100.00 100.00
時系列関数は、時系列的なディメンションを操作します。
時系列関数は、標準のSQLデータ操作関数ではなく、ユーザー指定のカレンダ表に基づいてAGO関数、TODATE関数およびPERIODROLLING関数を計算します。
Oracle BI Answersでは、特定のディメンションで時系列関数を使用します。時間ディメンションとしてこのディメンションを指定し、1つ以上のキーを1つ以上のレベルで時系列キーとして設定する必要があります。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のOracle BIサーバーの問合せキャッシュの概要に関する項を参照してください。
物理表に別名を付けて論理的にモデル化することなく、式ビルダーを使用して時系列計算を行う論理関数を呼び出すことができます。
特定のディメンションで時系列関数を使用するには、ディメンションを時間ディメンションとして指定し、1つ以上のレベルで1つ以上のキーを時系列キーとして設定する必要があります。『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』の時系列データのモデリングに関する項を参照してください。
次の関数があります。
AGO関数は、現在の時間から指定された時点までさかのぼって集計値を算出します。
AGO関数は時系列集計関数です。AGOは、メジャー・グレイン(粒度の単位)および述語によって決定される現在の時刻グレインから指定された時点までの集計値を計算します。たとえば、AGO関数を使用すると、現四半期の各月の売上および対応する1つ前の四半期の売上を生成できます。
注意:
AGO関数はスノーフレーク・モデルではサポートされていません。
時間的推移が発生する粒度は、メジャーの粒度および述語によって決定されます。たとえば、次の問合せでは、時間的推移が四半期レベルで発生し、1年遡ります。
Select year, quarter, Ago(sales, year_level, 1)
ただし、次の問合せでは、時間的推移がday_of_month属性の関連付けられたレベルで決定される日レベルで発生します。
Select year, quarter, Ago(sales, year_level, 1) Where day_of_month IN (1, 2)
時間的推移を計算する場合、Oracle BIサーバーは時間階層を通過します。たとえば、時間階層が年、月、日の順序である場合、Oracle BIサーバーは次のように時間的推移を計算します。
年は1単位で推移します。
年の月は同じままです。
月の日は同じままです。
「AGO関数レベルについて」を参照してください。
未サポートのメトリックがリクエストされた場合、NULL値が返され、ログ・レベルが3以上の場合はnqquery.logファイルに警告エントリが書き込まれます。
すべてのAGO関数で同じレベルの引数を使用する場合、複数のAGO関数をネストできます。TODAT関数とAGO関数がそれぞれ同じレベルの引数を使用する場合は、1つのTODATE関数と複数のAGO関数を正しくネストできます。
構文
AGO(expr, [time_level,] offset)
説明:
exprは、1つ以上のメジャー列を参照する式です。
time_levelはオプションの引数で、四半期、月、年などの期間のタイプを指定します。
「アンサー」には、time_levelのプレゼンテーション階層からプレゼンテーション・レベルを指定します。
管理ツールで、time_levelの論理レベルを指定します。
offsetは、時間的推移を表す整数リテラルです。
例
次の例では、昨年の売上が返されます。
SELECT Year_ID, AGO(sales, year, 1)
AGO関数のレベルは、[time_level]引数を使用して明示的に指定することをお薦めします。
[time_level]引数を明示的に指定しない場合、デフォルトのレベルは次のように決定されます。
式で使用されるメジャーが、管理ツールで設定したように、時間ディメンションのレベルベースのメジャーである場合は、同じレベルがデフォルトのAGOレベルとみなされます。
それ以外の場合、式で使用されるメジャーの粒度は、論理リクエストに表示されているメジャーのBY句の判断に従って、デフォルトのAgoレベルになります。
たとえば、次の問合せの結果:
SELECT year, AGO(sales, 1) WHERE quarter=1
これは、次のように解釈されます。
SELECT year, AGO(sales, year_level, 1) WHERE quarter=1
指定の問合せに対するデフォルトのAGOレベルは、問合せログの論理リクエスト・セクションで確認できます。
PERIODROLLINGは、x単位の時間から始まり、現在の時刻からy単位の時間で終わる期間のメジャーの合計を計算します。
たとえば、PERIODROLLINGを使用して、現在の四半期前の特定の四半期で始まり、現在の四半期後の異なる四半期で終わる期間の売上を計算できます。
時系列関数はその関数以下のレベルの時間ディメンションのメンバーに対して作用するため、特定のレベル以下のメンバーを一意に識別する1つ以上の列を問合せに投影する必要があります。指定されたレベル以下の単一のメンバーを指定する問合せにフィルタを適用できます。「PERIODROLLING関数で使用されるレベルの決定」を参照してください。
PERIODROLLING関数内に、AGO関数とTODATE関数をネストできません。また、PERIODROLLING、FIRST、FIRST_PERIOD、LASTおよびLAST_PERIOD関数はネストできません。
PERIODROLLING関数内に他の集計関数(RANK、TOPN、PERCENTILE、FILTER、RSUMなど)を含めると、PERIODROLLING関数は内側にプッシュされます。たとえば、PERIODROLLING(TOPN(measure))は、TOPN(PERIODROLLING(measure))として実行されます。
構文
PERIODROLLING(measure,x,y[,hierarchy])
説明:
measureは、メジャー列の名前です。
xは、現在の時刻からのオフセットを指定する整数です。過去に遡るオフセットを示すには、整数の前にマイナス記号(-)を付けます。
yは、この関数が計算する時間単位の数を指定します。現在の時刻を指定するには、0を入力します。
hierarchyは、yr、mon、dayなど、時間ウィンドウの計算に使用する時間ディメンション内の階層の名前を指定するオプションの引数です。このオプションは、時間ディメンション内に複数の階層がある場合、または複数の時間ディメンションを区別する場合に便利です。
最大限にロール・バックまたはロール・フォワードする場合は、キーワードUNBOUNDを使用します。たとえば、関数PERIODROLLING (measure , -UNBOUND, 0) では、時間カウントの開始から現在に至るまでの期間が合計されます。
PERIODROLLING関数とAGGREGATE AT関数を組み合せて、PERIODROLLING関数のレベルを明示的に指定できます。たとえば、問合せレベルは日ですが、前月と当月の合計を検索する場合は、次のように指定します。
SELECT year, month, day, PERIODROLLING(AGGREGATE(sales AT month), -1)
例
SELECT Month_ID, PERIODROLLING(monthly_sales, -1, 1)
SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, 2)
SELECT Month_ID, PERIODROLLING(monthly_sales, -UNBOUND, UNBOUND)
PERIODROLLING関数に使用される時間単位、オフセットは関数のレベルと呼ばれます。
この値は、最初の引数内のメジャーのメジャー・レベル、および関数が属する問合せの問合せレベルによって決定されます。管理ツールでメジャーのメジャー・レベルを設定できます。関数に使用されるメジャーのメジャー・レベルが設定されている場合、そのメジャー・レベルが関数のレベルとして使用されます。メジャー・レベルは、関数のストレージ単位とも呼ばれます。
管理ツールにメジャー・レベルが設定されていない場合は、問合せレベルが使用されます。問合せレベルは、関数の問合せ単位とも呼ばれます。次の例では、問合せレベルは月ですが、PERIODROLLING関数では、3月から4月までの各市の先月、当月、次月の合計が計算されます。
SELECT year, month, country, city, PERIODROLLING(sales, -1, 1)
WHERE month in ('Mar', 'Apr') AND city = 'New York'
時間ディメンションに複数の階層がある場合は、PERIODROLLING関数内でhierarchy引数を指定する必要があります。次に例を示します。
SELECT year, fiscal_year, month, PERIODROLLING(sales, -1, 1, "fiscal_time_hierarchy")
この例では、PERIODROLLING関数のレベルはfiscal_yearです。
TODATE関数は、指定された期間の最初から現在の表示時刻までのメジャーを集計する時系列集計関数です。TODATE関数を使用すると、年初から当日までの売上を計算できます。
時系列関数は、その関数で指定されたレベル以下の時間ディメンションのメンバーで操作します。特定のレベル以下のメンバーを一意に識別する1つ以上の列を問合せに設定する必要があります。指定されたレベル以下の単一のメンバーを指定する問合せにフィルタを適用できます。
未サポートのメトリックがリクエストされた場合、NULL値が返され、ログ・レベルが3以上の場合はnqquery.logファイルに警告エントリが書き込まれます。
別のTODATE関数内に、TODATE関数をネストできません。TODAT関数とAGO関数がそれぞれ同じレベルの引数を使用する場合は、1つのTODATE関数と複数のAGO関数を正しくネストできます。
TODATEは、一部のデータベースでサポートされているTO_DATE SQL関数とは異なります。TO_DATEを使用して、DATEデータ型に変更しないでください。かわりに、CAST関数を使用します。「CAST」を参照してください。
構文
TODATE(expr,time_level)
説明:
exprは、1つ以上のメジャー列を参照する式です。
time_levelは、四半期、月、年などの期間のタイプです。
例
次の例では、今年の今月時点までの売上を返します。
SELECT Year_ID, Month_ID, TODATE(sales, year)
レポート関数はすべての述語の評価後に計算され、レポートに表示される値の小計を計算する際に役立ちます。
レポート関数は、プロジェクト・リスト(SELECT句)の中だけで許可されます。
これらの関数は論理SQL内のみで使用でき、Oracle BIリポジトリの中にある式では使用できません。
次の関数があります。
REPORT_AGGREGATE関数は、REPORT_SUM、REPORT_MINなどの他のレポート関数を一般化したものです。
これは、対応する集計ルールでメジャーに対して適用された場合、これらの関数とセマンティック上、同等です。
たとえば、メジャーMが集計ルールSUMで定義された場合は、次のようになります。
REPORT_AGGREGATE(M) := REPORT_SUM(M)
しかし、集計ルールがMAXである場合は、次のようになります。
REPORT_AGGREGATE(M) := REPORT_MAX(M)
REPORT_AGGREGATEはプロジェクト・リスト(SELECT句)の中だけで許可され、WHERE句にあるすべての式が評価された後で計算されます。ただし、例外として、COUNTDISTINCTなどの非線形メジャーに適用された場合は、集計述語の前に計算されます。
注意:
非線形メジャーで集計述語の前にREPORT_AGGREGATEが計算されないようにするには、WHERE句の中にサマリーが含まれないように問合せを書きなおします。たとえば、次の問合せを
SELECT Year, DistinctUnitsSold, REPORT_AGGREGATE(DistinctUnitsSold BY ) WHERE Sales > 1000
次のように書きなおすことができます。
SELECT Year, DistinctProductsSold, REPORT_AGGREGATE(DistinctUnitsSold BY ) WHERE Year IN ( SELECT Year WHERE sales > 1000 )
非線形メジャーを、メジャーではなく派生列として宣言できます。次に例を示します。
COUNT(DISTINCT UnitId)
REPORT_AGGREGATEはすべての述語の評価後に計算されるため、レポートに表示される値の小計を計算する際に役立ちます。
REPORT_AGGREGATEがメジャー(M1、M2など)の式Eとスカラー属性(S1、S2など)に適用されている場合、これは次のようにコンポーネント・メジャーに配置されます。
REPORT_AGGREGATE(E(M1, M2, ..., S1, S2, ...)) := E(REPORT_AGGREGATE(M1), REPORT_AGGREGATE(M2), ..., S1, S2, ...)
次に例を示します。
REPORT_AGGREGATE((sales + 10) / number_of_employees) := (REPORT_AGGREGATE(sales) + 10) / REPORT_AGGREGATE(number_of_employees)
REPORT_AVG関数は、BY列の式のセットによってパーティション化されたargument_expressionの平均値を計算します。
空のBY句は、1つの(総計)パーティションを指定します。
BY句の列は、argument_expressionの(暗黙的) GROUP BY列のサブセットとして定義します。
REPORT_AVGは、プロジェクト・リスト、SELECT句の中のみで許可され、WHERE句のすべての式の後で計算されます。
REPORT_AVGは他のすべての述語の後に計算されるため、レポートの小計値の計算に適しています。
REPORT_AVGは集計ルールとして常にAVGを使用するのに対し、REPORT_AGGREGATEは、その引数に基づいて、集計ルールを自動的に決定しようとします。
デフォルトではREPORT_AGGREGATEを使用することをお薦めします。ただし、REPORT_AGGREGATEのデフォルトの動作で期待される結果が得られない場合は、REPORT_AVGのような明示的な集計ルールを使用できます。
構文
REPORT_AVG(argument_expression BY column_expression, column_expression, ...)
例
SELECT month, year, sales, REPORT_AVG(sales BY year) AS yearly_total WHERE month LIKE 'J%' AND sales > 10
結果:
この表はREPORT_SUMの結果の例を示します。
| Month | Year | 売上 | Yearly_Total |
|---|---|---|---|
| 6月 |
2011 |
20 |
25 |
| 7月 |
2011 |
30 |
25 |
| 1月 |
2012 |
40 |
45 |
| 6月 |
2012 |
50 |
45 |
REPORT_COUNT関数は、BY列の式のセットによってパーティション化されたargument_expressionの非NULL値を持つ行数を計算します。
空のBY句は、1つの(総計)パーティションを指定します。
BY句の列は、argument_expressionの(暗黙的) GROUP BY列のサブセットである必要があります。
REPORT_COUNTは、プロジェクト・リスト(SELECT句)の中だけで許可され、WHERE句のすべての式の後で計算されます。
REPORT_COUNTは他のすべての述語の後に計算されるため、レポートの小計値の計算に適しています。
REPORT_COUNTは集計ルールとして常にCOUNTを使用するのに対し、REPORT_AGGREGATEは、その引数に基づいて、集計ルールを自動的に決定しようとします。
デフォルトでは、REPORT_AGGREGATEを使用することをお薦めします。ただし、REPORT_AGGREGATEのデフォルトの動作で期待される結果が得られない場合は、REPORT_COUNTのような明示的な集計ルールを使用できます。
構文
REPORT_COUNT(argument_expression BY column_expression, column_expression, ...)
例
SELECT month, year, sales, REPORT_COUNT(sales BY year) AS yearly_total WHERE month LIKE 'J%' AND sales > 10
結果:
| Month | Year | 売上 | Yearly_Total |
|---|---|---|---|
| 6月 |
2011 |
20 |
2 |
| 7月 |
2011 |
30 |
2 |
| 1月 |
2012 |
40 |
2 |
| 6月 |
2012 |
50 |
2 |
REPORT_MAX関数は、BY列の式のセットによってパーティション化された数値argument_expressionを満たす行の最大値(最も高い数値)を計算します。
空のBY句は、1つの(総計)パーティションを指定します。
BY句の列は、argument_expressionの(暗黙的) GROUP BY列のサブセットである必要があります。
REPORT_MAXは、プロジェクト・リスト(SELECT句)の中だけで許可され、WHERE句のすべての式の後で計算されます。
REPORT_MAXは他のすべての述語の後に計算されるため、レポートの小計値の計算に適しています。
REPORT_MAXは集計ルールとして常にMAXを使用するのに対し、REPORT_AGGREGATEは、その引数に基づいて、集計ルールを自動的に決定しようとします。
デフォルトでは、REPORT_AGGREGATEを使用することをお薦めします。ただし、REPORT_AGGREGATEのデフォルトの動作で期待される結果が得られない場合は、REPORT_MAXのような明示的な集計ルールを使用できます。
構文
REPORT_MAX(argument_expression BY column_expression, column_expression, ...)
例
SELECT month, year, sales, REPORT_MAX(sales BY year) AS yearly_total WHERE month LIKE 'J%' AND sales > 10
結果:
| Month | Year | 売上 | Yearly_Total |
|---|---|---|---|
| 6月 |
2011 |
20 |
30 |
| 7月 |
2011 |
30 |
30 |
| 1月 |
2012 |
40 |
50 |
| 6月 |
2012 |
50 |
50 |
REPORT_MIN関数は、BY列の式のセットによってパーティション化されたargument_expressionを満たす行の最小値(最も低い数値)を計算します。
空のBY句は、1つの(総計)パーティションを指定します。
BY句の列は、argument_expressionの(暗黙的) GROUP BY列のサブセットである必要があります。
REPORT_MINは、プロジェクト・リスト(SELECT句)の中だけで許可され、WHERE句のすべての式の後で計算されます。
REPORT_MINは他のすべての述語の後に計算されるため、レポートの小計値の計算に適しています。
REPORT_MINは集計ルールとして常にMINを使用するのに対し、REPORT_AGGREGATEは、その引数に基づいて、集計ルールを自動的に決定しようとします。
デフォルトでは、REPORT_AGGREGATEを使用することをお薦めします。ただし、REPORT_AGGREGATEのデフォルトの動作で期待される結果が得られない場合は、REPORT_MINのような明示的な集計ルールを使用できます。
構文
REPORT_MIN(argument_expression BY column_expression, column_expression, ...)
例
SELECT month, year, sales, REPORT_MIN(sales BY year) AS yearly_total WHERE month LIKE 'J%' AND sales > 10
結果:
| Month | Year | 売上 | Yearly_Total |
|---|---|---|---|
| 6月 |
2011 |
20 |
20 |
| 7月 |
2011 |
30 |
20 |
| 1月 |
2012 |
40 |
40 |
| 6月 |
2012 |
50 |
40 |
REPORT_SUM関数は、BY列の式のセットによってパーティション化されたargument_expressionの合計を計算します。
BY句は、argument_expressionの(暗黙的) GROUP BY列のサブセットである必要があります。
REPORT_SUMは、プロジェクト・リスト(SELECT句)の中だけで許可され、WHERE句のすべての式の後で計算されます。
REPORT_SUMは他のすべての述語の後に計算されるため、レポートの小計値の計算に適しています。
REPORT_SUMは集計ルールとして常にSUMを使用するのに対し、REPORT_AGGREGATEは、その引数に基づいて、集計ルールを自動的に決定しようとします。
デフォルトでは、REPORT_AGGREGATEを使用することをお薦めします。ただし、REPORT_AGGREGATEのデフォルトの動作で期待される結果が得られない場合は、REPORT_SUMのような明示的な集計ルールを使用できます。たとえば、REPORT_SUMを使用して、同一の集計ルールをベース・メジャーとして使用するのではなく、レポート内に表示される値の単一の可視的な合計を求めて、合計または小計を計算します。
構文
REPORT_SUM(argument_expression BY column_expression, column_expression, ...)
例
SELECT month, year, sales, REPORT_SUM(sales BY year) AS yearly_total WHERE month LIKE 'J%' AND sales > 10
結果:
| Month | Year | 売上 | Yearly_Total |
|---|---|---|---|
| 6月 |
2011 |
20 |
50 |
| 7月 |
2011 |
30 |
50 |
| 1月 |
2012 |
40 |
90 |
| 6月 |
2012 |
50 |
90 |
文字列関数は様々な文字操作を行い、文字列に対して動作します。
次の関数があります。
ASCII関数は、単一文字列を、対応するASCIIコードに変換します。
ASCII関数は、単一文字列を、0から255の対応するASCIIコードに変換します。文字式が複数の文字に評価される場合は、式の最初の文字に対応するASCIIコードが返されます。
構文
ASCII(strExpr)
説明:
strExprは、文字列に評価される任意の式です。
BIT_LENGTH関数は、指定された文字列の長さをビット単位で返します。
各Unicode文字の長さは2バイトです(16ビットに相当)。
構文
BIT_LENGTH(strExpr)
説明:
strExprは、文字列に評価される任意の式です。
CHAR関数は、0から255の数値を、ASCIIコードに対応する文字値に変換します。
構文
CHAR(numExpr)
ここで、numExprは、0から255の数値に評価される任意の式です。
CHAR_LENGTH関数は、指定された文字列の長さを文字数で返します。先頭と末尾の空白は、文字列の長さにカウントされません。
構文
CHAR_LENGTH(strExpr)
説明:
strExprは、文字列に評価される任意の式です。
CONCAT関数には2つの形式があります。
最初の形式は、2つの文字列を連結します。2番目の形式は、文字列の連結文字を使用して、3つ以上の文字列を連結します。
形式1 (2つの文字列を連結する場合)の構文
CONCAT(strExpr1, strExpr2)
説明:
strExprs は、カンマで区切られた文字列に評価される任意の式です。
例
この例では、リクエストに対して返される結果が示されています。
SELECT DISTINCT CONCAT('abc', 'def') FROM employee
CONCAT('abc', 'def')
結果:
abcdef
形式2 (3つ以上の文字列を連結する場合)の構文
CONCAT(strExpr1, strExpr2 || strExpr3)
説明:
strExprs は文字列に評価される式で、カンマおよび文字列連結演算子||(2本の縦棒)で区切って指定します。最初に、 strExpr2 とstrExpr3が連結されて中間の文字列が生成されます。次に、CONCAT文字列によって strExpr1 とこの中間の文字列が連結されて最終の文字列が生成されます。
例
この例では、リクエストに対して返される結果が示されています。
SELECT DISTINCT CONCAT('abc','def' || 'ghi') FROM employee
結果:
abcdefghi
INSERT関数は、指定された文字列を、別の文字列の指定された場所に挿入します。
構文
INSERT(strExpr1,integer1, integer2, strExpr2)
説明:
strExpr1およびstrExpr2は、文字列に評価される任意の式です。ターゲットの文字列を示します。
integer1およびinteger2は、2番目の文字列が挿入される、ターゲットの文字列の先頭からの文字数を表す任意の正の整数です。
例
最初の文字列では、数値2で示されている2番目の位置から、3つの文字(数値2、3および4)が文字列abcdに置換されます。
SELECT INSERT('123456', 2, 3, 'abcd') FROM table
結果:
1abcd56
1abcd56
...
LEFT関数は、文字列の左側から、指定された文字数を返します。
構文
LEFT(strExpr, integer)
説明:
strExprは、文字列に評価される任意の式です。
integerは、文字列の左側から返す文字数を表す任意の正の整数です。
例
この例では、文字列123456の左端から3文字が返されます。
SELECT LEFT('123456', 3) FROM table
結果:
123 123 ...
LENGTH関数は、指定された文字列の長さを文字数で返します。
末尾の空白文字を除いた長さを返します。
構文
LENGTH(strExpr)
説明:
strExprは、文字列に評価される任意の式です。
LOCATE関数は、別の文字列での文字列の数値位置を返します。
文字列が検索される文字列で見つからない場合、関数は値0を返します。
検索の開始位置を指定する場合は、整数の引数を含めます。返される数値位置は、整数の引数の値に関係なく、文字列内の最初の文字位置を1として数えることで決定されます。
構文
LOCATE(strExpr1, strExpr2 [, integer])
説明:
strExpr1は、文字列に評価される任意の式です。検索する文字列を示します。
strExpr2は、文字列に評価される任意の式です。検索される文字列を示します。
integerは、文字列の検索を開始する位置を表す任意の正(ゼロ以外)の整数です。整数の引数はオプションです。
例
この例では、文字列abcdefで文字dの数値位置として4が返されます。
LOCATE('d', 'abcdef')
この例では、検索される文字列内で文字gが見つからないため、0が返されます。
LOCATE('g', 'abcdef')
この例では、文字列abcdefで文字dの数値位置として4が返されます。検索は、文字列の3番目の文字であるcから開始されます。返される数値位置は、文字aの位置を1として数えることで決定されます。
LOCATE('d' 'abcdef', 3)
この例では、文字列内で、文字bが検索の開始位置の前にあるため、0が返されます。
LOCATE('b' 'abcdef', 3)
OCTET_LENGTH関数は、指定された文字列のビット数を、基数8の単位(バイト数)で返します。
構文
OCTET_LENGTH(strExpr)
説明:
strExprは、文字列に評価される任意の式です。
POSITION関数は、文字式でのstrExpr1の数値位置を返します。
strExpr1が見つからない場合、関数は0を返します。「LOCATE」を参照してください。
構文
POSITION(strExpr1 IN strExpr2)
説明:
strExpr1は、文字列に評価される任意の式です。ターゲット文字列で検索する文字列を示します。
strExpr2は、文字列に評価される任意の式です。検索対象のターゲット文字列を示します。
例
この例では、文字列abcdefの文字dの位置として4が返されます。
POSITION('d', 'abcdef')
この例では、文字列123456で数値9が見つからないため、数値9の位置として0が返されます。
POSITION('9', '123456')
REPEAT関数は指定した式をn回繰り返します。
構文
REPEAT(strExpr, integer)
説明:
strExprは、文字列に評価される任意の式です。
integerは、文字列を繰り返す回数を表す任意の正の整数です。
例
この例では、abcを4回繰り返します。
REPEAT('abc', 4)
REPLACE関数は、指定された文字式の1つ以上の文字を、別の1つ以上の文字で置換します。
構文
REPLACE(strExpr1, strExpr2, strExpr3)
説明:
strExpr1は、文字列に評価される任意の式です。これは、文字が置換される文字列です。
strExpr2は、文字列に評価される任意の式です。この2番目の文字列は、1番目の文字列で置換される文字を示します。
strExpr3は、文字列に評価される任意の式です。この3番目の文字列は、1番目の文字列内に代入する文字を指定します。
例
文字列abcd1234の文字123が文字列zzで置換されます。
Replace('abcd1234', '123', 'zz')
結果:
abcdzz4
RIGHT関数は、文字列の右側から、指定された文字数を返します。
構文
RIGHT(strExpr, integer)
説明:
strExprは、文字列に評価される任意の式です。
integerは、文字列の右側から返す文字数を表す任意の正の整数です。
例
この例では、文字列123456の右端から3文字が返されます。
SELECT right('123456', 3) FROM table
結果:
456
SUBSTRING関数は、元の文字列内に、固定された文字数から始まる新しい文字列を作成します。
構文
SUBSTRING(strExpr FROM starting_position)
説明:
strExprは、文字列に評価される任意の式です。
starting_positionは、結果の開始位置を示す文字列の左端の開始からの文字数を表す任意の正の整数です。
TRIMBOTH関数は、指定された文字を文字列の冒頭と末尾から削除します。
構文
TRIM(BOTH character FROM strExpr)
説明:
characterは任意の単一文字です。文字の指定(および必須の一重引用符)を省略すると、空白文字がデフォルトで使用されます。
strExprは、文字列に評価される任意の式です。
TRIMLEADING関数は、指定された文字を文字列の冒頭から削除します。
構文
TRIM(LEADING character FROM strExpr)
説明:
characterは任意の単一文字です。文字の指定および必須の一重引用符を省略すると、空白文字がデフォルトで使用されます。
strExprは、文字列に評価される任意の式です。
TRIMTRAILING関数は、指定された文字を文字列の末尾から削除します。
構文
TRIM(TRAILING character FROM strExpr)
説明:
characterは任意の単一文字です。文字の指定および必須の一重引用符を省略すると、空白文字がデフォルトで使用されます。
strExprは、文字列に評価される任意の式です。
算術関数は、数学的な操作を実行します。
次の関数があります。
ATAN2関数は、y/xの逆正接を計算します。
y/xの逆正接を計算します。ここでyは最初の数式、xは2番目の数式です。
構文
ATAN2(numExpr1,numExpr2)
説明:
numExprは、数値に評価される任意の式です。
CEILING関数は、整数でない数式を、次に高い整数に丸めます。
数式が整数に評価される場合、CEILING関数はその整数を返します。
構文
CEILING(numExpr)
説明:
numExprは、数値に評価される任意の式です。
EXTRACTBIT関数は、整数内の特定の位置にあるビットを取得します。
そのビットの位置に対応する0または1の整数を返します。この機能は、主にHyperion Financial Managementのキューブ・ソースで「セル・ステータス」を抽出する際に使用されます。EXTRACTBIT関数をいかなるデータベースにもプッシュすることはできません。この関数は、常に、Oracle BIサーバーで内部的に実行されます。
構文
Int ExtractBit(Arg1, Arg2)
説明:
Arg1は、INT型、SMALLINT型、UNIT型、SMALLUNIT型、TINYINT型、またはTINYUNIT型の式です。Arg1がdouble型である場合、最初に列をINTにキャストする必要があります。
Arg2はINT型の式です。値は1からlength_of_Arg1の範囲である必要があります。1を指定すると最下位ビットを取得します。Arg2が整数の長さを超える場合は、0が返されます。Arg2が1より小さいときは、エラー・メッセージがトリガーされます。
FLOOR関数は、整数でない数式を、次に低い整数値に丸めます。
数式が整数に評価される場合、FLOOR関数はその整数を返します。
構文
FLOOR(numExpr)
説明:
numExprは、数値に評価される任意の式です。
MOD関数は、最初の数式を2番目の数式で除算し、商の剰余を返します。
構文
MOD(numExpr1, numExpr2)
説明:
numExprは、数値に評価される任意の式です。
例
この例では、リクエストに対して値0が返されます。
MOD(9, 3)
この例では、リクエストに対して値1が返されます。
MOD(10, 3)
POWER関数は、最初の数式を、2番目の数式で指定された値でべき乗します。
構文
POWER(numExpr1, numExpr2)
説明:
numExpr1は、数値に評価される任意の式です。
RANDFROMSEED関数は、シード値を基準にした疑似乱数を返します。
特定のシード値に対して、乱数の同一セットが生成されます。
構文
RAND(numExpr)
説明:
numExprは、数値に評価される任意の式です。
ROUND関数は、数式をn桁の精度まで丸めます。
構文
ROUND(numExpr, integer)
説明:
numExprは、数値に評価される任意の式です。
integerは、丸める精度の桁数を表す任意の正の整数です。
例
この例では、結果として2.17が返されます。
ROUND(2.166000, 2)
SIGN関数は、評価する数値式の引数に応じた値を返します。
SIGN関数は次の値を返します。
引数に指定した数式が正数に評価される場合は値1を返します。
引数に指定した数式が負数に評価される場合は値-1を返します。
0(ゼロ)に評価される場合は値0を返します。
構文
SIGN(numExpr)
説明:
numExprは、数値に評価される任意の式です。
SQRT関数は、引数に指定した数式の平方根を計算します。
数値式は、負数ではない数値に評価される必要があります。
構文
SQRT(numExpr)
説明:
numExprは、負数でない数値に評価される任意の式です。
カレンダ日付/時刻関数は、暦年に基づいてデータ型DATEおよびDATETIMEのデータを操作します。
これらの関数を他の列とともに選択する必要があります。関数のみを選択することはできません。
デフォルトでは、月および日の名前は大文字小文字混合で返されます。月や日の名前が大文字で返されることを想定している既存の式がある場合は、式を調整するか、NQSConfig.INIのパラメータUSE_UPPERCASE_MONTH_NAMESおよびUSE_UPPERCASE_DAY_NAMESをYESに設定する必要があります。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のNQSConfig.INIファイルの構成設定に関する項を参照してください。
MONTHNAMEなどの関数は日付列を書式設定するのに使用できますが、ロケールはデータ・ソースまたはOracle BIサーバーのNQSConfig.INIファイルで固定されています。論理SQL日付書式設定関数のロケールはODBC標準に基づいており、ロケール引数は用意されていないため、これらの関数にロケールを指定することはできません。
ユーザーが選択したロケールに基づいて日付列を書式設定するには、ロケールを固定するときに、コンテンツ設計者が標準の日付書式またはカスタムの日付書式を分析の日付列に指定することをお薦めします。『Oracle Business Intelligence Enterprise Editionユーザーズ・ガイド』の日付および時間フィールドのカスタム書式設定文字列に関する項を参照してください。
次の関数があります。
CURRENT_TIME関数は、現在の時刻を返します。
時刻はOracle BIサーバーを実行しているシステムによって決定されます。
構文
CURRENT_TIME(integer)
説明:
integerは、小数秒を表示する精度の桁数を表す任意の整数です。この引数の指定はオプションです。引数を指定しない場合は、デフォルトの精度で時刻が返されます。
CURRENT_TIMESTAMP関数は、現在の日付/タイムスタンプを返します。
タイムスタンプはOracle BIサーバーを実行しているシステムによって決定されます。
構文
CURRENT_TIMESTAMP(integer)
説明:
integerは、小数秒を表示する精度の桁数を表す任意の整数です。この引数の指定はオプションです。引数を指定しない場合は、デフォルトの精度で時刻が返されます。
DAY_OF_QUARTER関数は、指定された日付について、四半期の通算日に対応する数字(1から92)を返します。
構文
DAY_OF_QUARTER(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
DAYOFMONTH関数は、指定された日付について、月の通算日に対応する数字を返します。
構文
DAYOFMONTH(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
DAYOFWEEK関数は、指定された日付について、曜日(日曜日から土曜日)に対応する1から7の数字を返します。
たとえば、DAYOFWEEK関数が返す数字1は日曜日に対応し、数字7は土曜日に対応します。
構文
DAYOFWEEK(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
DAYOFYEAR関数は、指定された日付について、年の通算日に対応する数字(1から366)を返します。
構文
DAYOFYEAR(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
HOUR関数は、指定された時刻について、時間に対応する数字(0から23)を返します。
たとえば、0は午前12時に対応し、23は午後11時に対応します。
構文
HOUR(timeExpr)
説明:
timeExprは、時刻に評価される任意の式です。
MINUTE関数は、指定された時刻について、分に対応する数字(0から59)を返します。
構文
MINUTE(timeExpr)
説明:
timeExprは、時刻に評価される任意の式です。
MONTH_OF_QUARTER関数は、指定された日付について、四半期の通算月に対応する数字(1から3)を返します。
構文
MONTH_OF_QUARTER(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
QUARTER_OF_YEAR関数は、指定された日付について、年の四半期に対応する数字(1から4)を返します。
構文
QUARTER_OF_YEAR(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
SECOND関数は、指定された時刻について、秒に対応する数字(0から59)を返します。
構文
SECOND(timeExpr)
説明:
timeExprは、時刻に評価される任意の式です。
TIMESTAMPADD関数は、指定されたタイムスタンプに、指定された間隔の数を加算します。単一のタイムスタンプを返します。
この関数の最も簡単なシナリオでは、指定された整数値が、間隔に基づいて適切なタイムスタンプ・コンポーネントに加算されます。1週は7日として加算され、1四半期は3か月として加算されます。負の整数値を追加すると減算されます(時間を遡ります)。
特定のコンポーネントでオーバーフローが発生する場合(60秒、24時間、12か月を超える場合など)は、次のコンポーネントに適切に値を加算する必要があります。たとえば、タイムスタンプの日のコンポーネントを加算する場合、この関数ではオーバーフローを考慮して、特定月の日数(2月が29日ある、うるう年を含む)が計算時に確認されます。
タイムスタンプの月のコンポーネントを加算する場合は、結果として生じるタイムスタンプについて、日のコンポーネントの日数が適切であるかどうか検証されます。たとえば、2000-05-31に1か月を加算しても2000-06-31にはなりません。これは、6月には31日がないためです。この例では、日のコンポーネントが月の最後の日に減算され、2000-06-30となります。
同様の問題は、月のコンポーネントが2月、日のコンポーネントが29であるタイムスタンプ(つまり、うるう年の2月の最終日)において、タイムスタンプの年のコンポーネントを加算する場合にも発生します。結果として生じるタイムスタンプがうるう年に該当しない場合は、日のコンポーネントが28に減算されます。
この処理は、Microsoft社のSQL ServerおよびOracle DatabaseのネイティブOCIインタフェースに準拠します。
構文
TIMESTAMPADD(interval, intExpr, timestamp)
説明:
interval は指定された間隔です。有効な値は次のとおりです。
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
intExpr は、整数値に評価される任意の式です。
timestampは任意の有効なタイムスタンプです。この値は計算のベースに使用されます。
NULLの整数式またはNULLのタイムスタンプがこの関数に渡されると、NULL値が返されます。
例
次の問合せは、2000-02-27 14:30:00に3日を追加したタイムスタンプを求めています。2000年はうるう年であるため、2000-03-01 14:30:00が単一のタイムスタンプとして返されます。
SELECT TIMESTAMPADD(SQL_TSI_DAY, 3, TIMESTAMP'2000-02-27 14:30:00') FROM Employee WHERE employeeid = 2;
次の問合せは、1999-07-31 0:0:0に7か月を追加したタイムスタンプを求めています。2000-02-29 00:00:00が単一のタイムスタンプとして返されます。2月は短い月であるため、日のコンポーネントが29に減算されていることに注意してください。
SELECT TIMESTAMPADD(SQL_TSI_MONTH, 7, TIMESTAMP'1999-07-31 00:00:00') FROM Employee WHERE employeeid = 2;
次の問合せは、2000-07-31 23:35:00に25分を追加したタイムスタンプを求めています。2000-08-01 00:00:00が単一のタイムスタンプとして返されます。月のコンポーネントにオーバーフローが伝播されていることに注意してください。
SELECT TIMESTAMPADD(SQL_TSI_MINUTE, 25, TIMESTAMP'2000-07-31 23:35:00') FROM Employee WHERE employeeid = 2;
注意:
TIMESTAMPADD関数は、デフォルトでは、Microsoft SQL Server、ODBC、IBM DB2およびOracleデータベースで使用できます。DB2およびOracleセマンティクスでは、この関数が完全にはサポートされないため、この関数からの回答はOracle BIサーバーの結果と一致しない場合があります。
TIMESTAMPDIFF関数は、2つのタイムスタンプ間の指定された間隔の合計を返します。
この関数では、最初に、指定された間隔パラメータに対応するタイムスタンプ・コンポーネントが判別されます。次に、両方のタイムスタンプで次に高い順位のコンポーネントが確認され、各タイムスタンプについて間隔の合計数が計算されます。たとえば、指定された間隔が月のコンポーネントに対応する場合は、月のコンポーネントに年のコンポーネントを12回加算することで各タイムスタンプの月の合計数を計算します。次に、最初のタイムスタンプの間隔の合計数を、2番目のタイムスタンプの間隔の合計数から減算します。
注意:
この項では、関数がOracle BIサーバーで計算される際のTIMESTAMPDIFFの動作について説明しています。この関数がデータ・ソースで計算される場合は、この項で説明する動作と異なる場合があります。TIMESTAMPDIFF関数の結果が望ましい結果でない場合は、管理ツールを使用して、データベース・オブジェクトの「機能」タブでTIMESTAMP_DIFF_SUPPORTEDを無効にし、関数が確実にOracle BIサーバーで計算されるようにします。ただし、この変更によってパフォーマンスが低下する可能性があります。
TIMESTAMPDIFF関数では、間隔の小数部が間隔の境界を越える場合は、次の整数に丸められます。たとえば、1999-12-31と2000-01-01の間の年の差は、年の小数部が翌年にまたがるため(1999年から2000年)、1年となります。対照的に、1999-01-01と1999-12-31の間の年の差は、年の小数部が同じ年内に存在するため(1999年)、0年となります。Microsoft SQL Serverでも同様の丸めが発生します。IBM DB2では常に切り捨てられます。
週の差を計算する場合は、差を日数で計算してから、丸める前に7で除算します。また、この関数では、NQSConfig.INIファイルでFIRST_DAY_OF_THE_WEEKパラメータを使用して構成される方法も考慮されます。たとえば、週の開始を日曜日とすると、2000-07-06(木曜日)と2000-07-10(次の月曜日)の週の差は1週間となります。しかし、週の開始を火曜日とすると、間隔の小数部が同じ週内に収まるため、週の差は0週間となります。四半期の差を計算する場合は、差を月数で計算してから、丸める前に3で除算します。
Oracle BIサーバーにより、Microsoft SQL Server、Oracle Database、IBM DB2およびODBCデータベースでは、TIMESTAMPADD関数とTIMESTAMPDIFF関数がデフォルトで有効になっています。
構文
TIMESTAMPDIFF(interval, timestamp1, timestamp2)
説明:
interval は指定された間隔です。有効な値は次のとおりです。
SQL_TSI_SECOND
SQL_TSI_MINUTE
SQL_TSI_HOUR
SQL_TSI_DAY
SQL_TSI_WEEK
SQL_TSI_MONTH
SQL_TSI_QUARTER
SQL_TSI_YEAR
timestamp1およびtimestamp2は任意の有効なタイムスタンプです。
NULLのタイムスタンプのパラメータがこの関数に渡されると、NULL値が返されます。
例
次の問合せは、タイムスタンプ1998-07-31 23:35:00と2000-04-01 14:24:00の日数差を求めています。値610が返されます。2000年がうるう年であるため、1日追加されていることに注意してください。
SELECT TIMESTAMPDIFF (SQL_TSI_DAY, TIMESTAMP'1998-07-31 23:35:00',TIMESTAMP'2000-04-01 14:24:00') FROM Employee WHERE employeeid = 2;
注意:
TIMESTAMPDIFF関数は、デフォルトでは、Microsoft SQL Server、ODBC、IBM DB2およびOracleデータベースで使用できます。DB2およびOracleセマンティクスでは、この関数が完全にはサポートされないため、この関数からの回答はOracle BIサーバーの計算結果と一致しない場合があります。
WEEK_OF_QUARTER関数は、指定された日付について、四半期の通算週に対応する数字(1から13)を返します。
構文
WEEK_OF_QUARTER(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
WEEK_OF_YEAR関数は、指定された日付について、年の通算週に対応する数字(1から53)を返します。
構文
WEEK_OF_YEAR(dateExpr)
説明:
dateExprは、日付に評価される任意の式です。
変換関数は、ある形式から別の形式に値を変換します。
フィルタでVALUEOF関数を使用して、Oracle BI EEシステム変数の値を参照できます。次の関数があります。
CAST関数は、式またはNULLリテラルのデータ型を別のデータ型に変更します。
たとえば、customer_name変数をCharまたはVarcharとしてキャストできます。birthdateには日付/時刻リテラルを使用できます。次のサポートされているデータ型の値を変更できます。
CHARACTER
VARCHAR
INTEGER
FLOAT
SMALLINT
DOUBLE PRECISION
DATE
TIME
TIMESTAMP
BIT
BIT VARYING
変更元のデータ型によっては、変更後のデータ型でサポートされないものがあります。たとえば、変更元のデータ型がBIT文字列である場合、変更後のデータ型は文字列または別のBIT文字列である必要があります。
DATEデータ型に変更する場合は、CASTを使用します。
次に、CHARデータ型およびVARCHARデータ型の固有の特性を説明します。
次の構文をお薦めします。
CAST(expr | NULL AS CHAR(n))
次に例を示します。
CAST(companyname AS CHAR(35))
次の構文も使用できます。
CAST(expr|NULL AS data_type)
次に例を示します。
CAST(companyname AS CHAR)
注意:
この構文を使用する場合、Oracle BIサーバーではCAST( expr) |NULL AS CHAR(30))として明示的に変換され、格納されます
VARCHARデータ型へのキャスト。サイズ・パラメータを使用する必要があります。サイズ・パラメータを省略すると、変更を保存できません。
例
CAST(hiredate AS CHAR(40)) FROM employee
SELECT CAST(hiredate AS VARCHAR(40)), CAST(age AS double precision),
CAST(hiredate AS timestamp), CAST(age AS integer) FROM employee
CAST("db"."."table"."col" AS date)
CHOOSE関数は、任意数のパラメータを受け取り、ユーザーに参照する権限があるリストの最初の項目を返します。
ただし、管理者はOracle BI管理ツールで列の権限をモデリングしてこの動作を有効にする必要があります。別の方法については、「INDEXCOL」を参照してください。
構文
CHOOSE(expr1, expr2, ..., exprN)
たとえば、組織全体のセキュリティベースの収益を返すように単一の問合せを記述することができます。関数は次のように指定します。
CHOOSE(L1-Revenue, L2-Revenue, L3-Revenue, L4-Revenue)
この関数を発行したユーザーに列L1-Revenueへのアクセス権がある場合、列値が返されます。ユーザーが、列L1-Revenueは参照不可で、L2-Revenueは参照可能な場合、L2-Revenueが返されます。
IFNULL関数は、ある式がNULL値と評価されるかどうかをテストし、評価された場合は、指定された値をその式に割り当てます。
構文
IFNULL(expr, value)
説明:
exprは、評価される式です。
valueは、式がNULL値と評価された場合に割り当てられる値です。
INDEXCOL関数は、外部情報を使用して、ログインしているユーザーが参照できるように適切な列を返すことができます。
Oracle BIサーバーでは、この関数を次のように処理します。
ODBCプロシージャ。NQSGetLevelDrillabilityおよびNQSGenerateDrillDownQueryは、INDEXCOLから変換された式に基づいて、コンテキスト固有のドリルダウン情報を返します。これは、論理SQL問合せで指定されたINDEXCOL式および導出された論理列で指定されたINDEXCOL式の両方に適用されます。
問合せログおよびキャッシュ。 INDEXCOL関数を含む論理SQL問合せは、問合せログのSQL文字列に出力されます。ただし、Oracle BIサーバーではINDEXCOLを論理リクエスト・ジェネレータの式リスト内の式の1つに変換するので、論理リクエストではINDEXCOL関数が示されません。
問合せキャッシュでは、キャッシュ・ヒット検出の結果の変換された式を使用します。
使用状況トラッキング。使用状況トラッキングでは、INDEXCOL関数で論理SQL問合せ文字列を挿入します。
セキュリティ。INDEXCOLから変換された式の列にユーザーがアクセスする権限があれば、問合せは実行されます。
INDEXCOLの最初の引数がセッション変数で、初期化ブロックで失敗してもデフォルトの式を返すように想定されている場合、セッション変数のデフォルト値を設定する必要があります。そうしないと、セッション変数には値の定義がなくなるため、問合せは失敗します。
構文
INDEXCOL(integer_literal, expr_list)
説明:
expr_list は、次と同じです。
expr1 [, expr_list ]
INDEXCOL関数は最初の引数に整数リテラル値をとり、次に変数の長さの式リストが続きます。この関数は式リストから単一の式に変換します。リテラル値は、変換される式リスト内の式の0ベースの索引です。次の式について考えます。
INDEXCOL(integer_literal, expr1, expr2, …)
リテラル値が0である場合、前述の式はexpr1と同等です。リテラル値が1の場合、その値がexpr2と同等になります。以降も同様です。
INDEXCOLの主な使用例は、セッション変数が含まれる最初の引数を求めることです。定数リテラルを指定すると、INDEXCOLは常に同じ式を選択することになります。
架空の会社であるABCの階層レベルの例を示します。
会社ABCは、Country、State、Cityという階層を持つ地理ディメンションがあります。CEOはCountryレベルからCityレベルまでアクセス可能です。販売マネージャはStateおよびCityレベルにアクセス可能です。販売員はCityレベルにのみアクセス可能です。次の表に会社ABCのバックエンド・データベースを示します。
| User_Name | Title | Geo_level | Currency | Currency_col |
|---|---|---|---|---|
| Bob |
CEO |
0 |
US Dollars |
0 |
| Harriet |
Sales Manager |
1 |
Japanese Yen |
1 |
| Jackson |
Sales Manager |
1 |
Japanese Yen |
1 |
| Mike |
Sales Person |
2 |
Japanese Yen |
1 |
| Jim |
Sales Person |
2 |
US Dollars |
0 |
次の手順は、各ユーザーがそれぞれにアクセスできる最高レベルを参照する単一の問合せを作成する方法を示します。
管理者はGEOOGRAPHY_LEVELという新しいセッション変数を作成し、それに初期化ブロックSELECT GEO_LEVEL from T where USER_NAME = ':USER'を設定します。
これは、Oracle BIサーバーのインスタンスが同じユーザー名を持つことが想定されています。
SELECT INDEXCOL(VALUEOF(NQ_SESSION.GEOGRAPHY_LEVEL), Country, State, City), Revenue FROM Salesを使用すると、次のように処理されます。
Bobがログインすると、GEOGRAPHY_LEVELセッション変数が0であるため、INDEXCOLはCountry列に変換します。彼は、SELECT Country, Revenue FROM Salesを使用している場合と同じ結果が得られ、CountryからStateまでドリルダウンできます。
Jacksonがログインすると、GEOGRAPHY_LEVELセッション変数が1であるため、INDEXCOLはState列に変換します。彼は、SELECT State, Revenue FROM Salesを使用している場合と同じ結果が得られ、StateからCityまでドリルダウンできます。
Mikeがログインすると、GEOGRAPHY_LEVELセッション変数が2であるため、INDEXCOLはCity列に変換します。彼は、SELECT City, Revenue FROM Salesを使用している場合と同じ結果が得られ、Cityはドリルダウンできません。
TO_DATETIME関数は、日時書式の文字列リテラルをDateTimeデータ型に変換します。
構文
TO_DATETIME('string1', 'DateTime_formatting_string')
説明:
string1は変換する文字列リテラルです
DateTime_formatting_stringは使用する日時書式です(yyyy.mm.dd hh:mi:ssなど)。この引数では、yyyyは年、mmは月、ddは日、hhは時間、miは分、ssは秒を表しています。
例
SELECT TO_DATETIME('2009-03-03 01:01:00', 'yyyy-mm-dd hh:mi:ss') FROM snowflakesales
SELECT TO_DATETIME('2009.03.03 01:01:00', 'yyyy.mm.dd hh:mi:ss') FROM snowflakesales
VALUEOF関数を使用すると、リポジトリ変数の値を参照できます。
リポジトリ変数はOracle BI管理ツールを使用して定義されます。管理ツールの式ビルダーでVALUEOF関数を使用できます。管理ツールで「分析」エディタの「詳細設定」タブから分析用のSQL文を編集する場合にも、この関数を使用できます。
構文
変数をVALUEOF関数の引数として使用します。静的リポジトリ変数は名前で参照します。変数名では大文字と小文字が区別されます。たとえば、prime_beginおよびprime_endという静的リポジトリ変数の値を使用するには、次のように指定します。
CASE WHEN "Hour" >= VALUEOF("prime_begin")AND "Hour" < VALUEOF("prime_end") THEN 'Prime Time' WHEN ... ELSE...END
動的リポジトリ変数は完全修飾名で参照する必要があります。動的リポジトリ変数を使用する場合、初期化ブロックの名前とリポジトリ変数を二重引用符で囲み、ピリオドで区切って丸カッコで囲む必要があります。たとえば、Region Securityという初期化ブロックに含まれているREGIONという動的リポジトリ変数の値を使用するには、次のような構文を使用します。
SalesSubjectArea.Customer.Region = VALUEOF("Region Security"."REGION")
セッション変数名は、前にNQ_SESSIONを付けて、ピリオドで区切り、カッコで囲む(NQ_SESSION部分を含む)必要があります。たとえば、REGIONというセッション変数の値を使用するには、式ビルダーまたはフィルタで次のような構文を使用します。
"SalesSubjectArea"."Customer"."Region" = VALUEOF(NQ_SESSION.REGION)
変数名の中または先頭にASCII英数字([A-Z] [a-z] [0-9])やアンダースコア(_)以外の文字が含まれる場合、名前を二重引用符(")で囲みます。たとえば空白や一重引用符、二重引用符などがこれに該当します。次の例では一重引用符と空白が含まれています。
"SalesSubjectArea"."Customer"."Region" = VALUEOF("NQ_SESSION"."Steven's Regions")
指定した変数名に二重引用符(")が含まれている場合、二重引用符のセットでエスケープします。次に例を示します。
"SalesSubjectArea"."Customer"."Region" = VALUEOF("NQ_SESSION"."""Top Sales"" Region")
セッション変数を含む初期化ブロック名を使用しても機能しますが、NQ_SESSIONを使用することをお薦めします。NQ_SESSIONはすべての初期化ブロック名と一致するワイルドカードのように機能します。これにより、ローカライズされたようにリクエストに影響せずに初期化ブロックの構造を変更できます。
VARIABLE_REPLACE関数は、セッション変数の指定された値のメジャーを問い合せます。
VARIABLE_REPLACE関数は、セッション変数の指定された値のメジャーを問い合せます。この関数を使用してメジャーを問い合せる場合、1つ以上のセッション変数値を使用してください。複数の名前/値ペアを指定できます。ユーザーにセッション変数を設定する権限があれば、問合せは実行されます。
nqcmdユーティリティを使用してSQLをコマンドラインに入力した場合のみ、この関数を使用できます。
構文
VARIABLE_REPLACE(measure, variable_name, variable_value [, variable_name1, variable_value1, ...])
説明:
measureはメジャー・ファクト列で、ここにセッション変数を含む論理表ソース・マッピングがあります。
variable_nameは接頭辞NQ_SESSIONの付いたセッション変数名です。
variable_valueはセッション変数の置換された値です。文字列および引数の値はサポートされています。変数名に空白が含まれている場合は、それを二重引用符で囲みます。
例
次の例では、セッション変数が文字列リテラル'A'で変換されます。
VARIABLE_REPLACE(Revenue, 'NQ_SESSION.VAR', 'A')
次の例では、セッション変数が整数1で変換されます。
VARIABLE_REPLACE(Revenue, 'NQ_SESSION.VAR', 1)
LOOKUP関数は通常、「ビジネス・モデルとマッピング」レイヤーで、変換済の論理表列の式として使用されます。
多言語スキーマでは、通常、翻訳済フィールドが参照表と呼ばれる個別の表に格納されます。参照表には記述子列の翻訳が複数言語で格納され、ベース表にそのデータが元の言語で格納されます。参照とは、問合せによってベース表と参照表を結合し、ベース表の各行に対して変換済の値を取得することを指します。
『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』の次に示す項を参照してください。
多言語データのサポート
LOOKUP関数の構文について
データベース関数を直接呼び出すか、メタデータ・リポジトリ内の(論理表ソースの)論理列を使用することで、リクエストを作成できます。
ユーザーおよび管理者は、Oracle BIアンサーからデータベース関数を直接呼び出すか、メタデータ・リポジトリ内の論理表ソースの論理列を使用することで、リクエストを作成できます。これらの関数の主な使用方法としては、高度な計算を取得するための式の受渡し機能、基礎となるデータベース上のカスタム書込み関数へのアクセス機能などがあります。
注意:
これらの関数は、XMLデータ・ソースと一緒に使用できません。NQSConfig.INIにあるEVALUATE_SUPPORT_LEVELパラメータは、Oracle BIアンサー内でのデータベース関数のEVALUATEファミリの使用を制御します。Oracle BIアンサー内でこれらの関数が使用されないようにするために、EVALUATE_SUPPORT_LEVELをデフォルト値である0に設定しておくことをお薦めします。EVALUATE_SUPPORT_LEVELを1または2の値に設定すると、ユーザーがOracle BIアンサーを使用して任意のSQL式を分析に挿入できるようになり、それによってデータ・アクセスのセキュリティが低下する可能性があります。
NQSConfig.INIのEVALUATE_SUPPORT_LEVELパラメータは、メタデータ・リポジトリ内でデータベース関数のEVALUATEファミリの使用を制御しません。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のNQSConfig.INIファイルの構成設定に関する項を参照してください。
次の関数があります。
EVALUATE関数は入力値を取得して単一行の出力値を返すスカラー関数で使用されます。パラメータとして(オプションの)参照列を使用する、指定されたデータベース関数をバックエンド・データ・ソースに渡して評価します。
この関数は、スカラー計算を対象としています。Oracle BIサーバーでサポートされていないが基礎となるデータ・ソースで認識される、専用のデータベース関数を使用する場合に便利です。
この埋込みデータベース関数には、1つ以上の列が必要になる場合があります。これらの列は、関数内で%1 ... %Nによって参照されます。関数の後に実際の列をリストする必要があります。
デフォルトで、EVALUATEは無効になっており使用できません。この関数のサポートを有効にするには、NQSConfig.INIでEVALUATE_SUPPORT_LEVELパラメータを変更します。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のNQSConfig.INIファイルの構成設定に関する項を参照してください。
構文
EVALUATE('db_function(%1...%N)' [AS data_type] [, column1, columnN])
説明:
db_functionは、基礎となるデータ・ソースで認識される任意の有効なデータベース関数です。
data_typeは、戻り結果のデータ型を指定するオプション・パラメータです。戻りデータ型を入力引数から確実に予測できない場合は常に使用してください。ただし、型のキャストにこのパラメータを使用しないでください。関数が特定のデータ型を返す必要がある場合は、明示的にキャストを指定します。データベース固有の関数はOracle BIサーバーでサポートされていない戻り型を使用しますが、Oracle BIサーバーに返される必要がない中間の結果を生成するために使用されている場合、通常、このパラメータを省略できます。
column1からcolumnNは、列名をカンマで区切ったオプションのリストです。
例
この例では、埋込みデータベース関数を示します。
SELECT EVALUATE('instr(%1, %2)', address, 'Foster City') FROM employees
固有のEssbase関数を活用するEVALUATE_AGGREGATEとEVALUATEの使用例
次の例では、EVALUATE_AGGREGATE関数とEVALUATE関数を使用します。式は物理キューブを参照する論理表ソースの列に適用される点に注意してください。EVALUATE_AGGREGATEを使用して、カスタム集計を実装します。たとえば、地域収益全体と地域で上位3製品の収益を比較するとします。上位3製品の収益を表す新規メジャーを定義して論理SQL文を作成できます。
SELECT Region, Profit, EVALUATE_AGGREGATE('SUM(TopCount(%1.members, 3, %2), %3)',
Products, Profit, Profit) Top_3_prod_Profit FROM SampleBasic
Oracle BIサーバーで次の式が生成され、カスタム集計が行われます。
member [Measures].[MS1] AS 'SUM(Topcount([Product].Generations(6).members,3,[Measures].[Profit]),[Measures].[Profit])'
計画されたディメンションでEVALUATE関数を使用して、集計後に計算されるスカラー計算を実行します。問合せに含まれないディメンションまたは属性への参照が明示的に定義されている場合、EVALUATEにより問合せのグレインを変更できます。
たとえば、ある地域で販売された売上別ランキングで上位5製品の収益を参照する場合、適用可能なメジャーを作成した後に、作成される論理SQL文は次のとおりです。
SELECT Region, EVALUATE('TopCount(%1.members, 5, %2)' as VARCHAR(20), Products, Sales), Profits FROM SampleBasic
Oracle BIサーバーで次の式が生成され、上位5製品を取得します。
set [Evaluate0] as '{Topcount([Product].Generations(6).members,5,[Measures].[Sales]) }'
EVALUATE_ANALYTIC関数は、1つ以上の行で構成される行セットを受け取り、セットの各行に対して結果を返します。
EVALUATE_ANALYTIC関数は、パラメータとして、参照列を使用する、指定されたデータベース分析関数をオプションでバックエンド・データ・ソースに渡して評価します。この関数を使用してウィンドウ関数とも呼ばれるSQL分析関数をモデル化します。
この埋込みデータベース関数には、1つ以上の列が必要になる場合があります。これらの列は、関数内で%1 ... %Nによって参照されます。関数の後に実際の列をリストする必要があります。
デフォルトで、EVALUATE_ANALYTICは無効になっており使用できません。この関数のサポートを有効にするには、NQSConfig.INIでEVALUATE_SUPPORT_LEVELパラメータを変更します。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』 のNQSConfig.INIファイルの構成設定に関する項を参照してください。
構文
EVALUATE_ANALYTIC('db_function(%1...%N)' [AS data_type] [, column1, columnN])
説明:
db_functionは、基礎となるデータ・ソースで認識される任意の有効なデータベース分析関数です。
data_typeは、戻り結果のデータ型を指定するオプション・パラメータです。戻りデータ型を入力引数から確実に予測できない場合はこのパラメータを使用してください。ただし、型のキャストにこのパラメータを使用しないでください。関数が特定のデータ型を返す必要がある場合は、明示的にキャストを指定します。データベース固有の分析関数はOracle BIサーバーでサポートされていない戻り型を使用しますが、Oracle BIサーバーに返される必要がない中間の結果を生成するために使用されている場合は、このパラメータを省略できます。
column1からcolumnNは、列をカンマで区切ったオプションのリストです。
例
この例では、埋込みデータベース分析関数を示します。
EVALUATE_ANALYTIC('dense_rank() over(order by %1 )' AS INT,sales.revenue)
前述の例でdouble値を返す必要がある場合は、次のようにキャストを明示的に追加する必要があります。
CAST(EVALUATE_ANALYTIC('Rank(%1.dimension.currentmember, %2.members)',
"Foodmart93"."Time"."Month" as Double)
EVALUATE_AGGR関数は、パラメータとしてオプションの参照列を使用する、指定されたデータベース関数をバックエンド・データ・ソースに渡して評価します。
この関数は、GROUP BY句を使用する集計関数を対象としています。
この埋込みデータベース関数には、1つ以上の列が必要になる場合があります。これらの列は、関数内で%1 ... %Nによって参照されます。関数の後に実際の列をリストする必要があります。
デフォルトで、EVALUATE_AGGRは無効になっており使用できません。この関数のサポートを有効にするには、NQSConfig.INIでEVALUATE_SUPPORT_LEVELパラメータを変更します。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のNQSConfig.INIファイルの構成設定に関する項を参照してください。
構文
EVALUATE_AGGR('db_agg_function(%1...%N)' [AS data_type] [, column1,columnN)
説明:
db_agg_functionは、基礎となるデータ・ソースで認識される任意の有効な集計データベース関数です。
data_typeは、戻り結果のデータ型を指定するオプション・パラメータです。戻りデータ型を入力引数から確実に予測できない場合は常にこのパラメータを使用してください。ただし、型のキャストにこのパラメータを使用しないでください。関数が特定のデータ型を返す必要がある場合は、明示的にキャストを指定します。データベース固有の関数はOracle BIサーバーでサポートされていない戻り型を使用しますが、Oracle BIサーバーに返される必要がない中間の結果を生成するために使用されている場合は、このパラメータを省略できます。
column1からcolumnNは、列をカンマで区切ったオプションのリストです。
例
EVALUATE_AGGR('REGR_SLOPE(%1, %2)', sales.quantity, market.marketkey)
EVALUATE_PREDICATE関数は、パラメータとしてオプションの参照列を使用する、指定されたデータベース関数をバックエンド・データ・ソースに渡して評価します。
EVALUATE_PREDICATEは、Boolean戻り型を使用する関数を対象としています。
この埋込みデータベース関数には、1つ以上の列が必要になる場合があります。これらの列は、関数内で%1 ... %Nによって参照されます。実際の列は、関数の後にリストする必要があります。
注意:
EVALUATE_PREDICATEは、Essbaseデータ・ソースとの併用はサポートされません。
デフォルトで、EVALUATE_PREDICATEは無効になっており使用できません。この関数のサポートを有効にするには、NQSConfig.INIでEVALUATE_SUPPORT_LEVELパラメータを変更します。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』のNQSConfig.INIファイルの構成設定に関する項を参照してください。
構文
EVALUATE_PREDICATE('db_function(%1...%N)', [, column1, columnN)
説明:
db_functionは、基礎となるデータ・ソースで認識される、Booleanの戻り型を使用する任意の有効なデータベース関数です。
column1からcolumnNは、列をカンマで区切ったオプションのリストです。
比較目的でデータベース関数をモデル化する場合は、EVALUATE_PREDICATEを使用しないでください。EVALUATEを使用して、関数外で比較を行います。たとえば、次のようにEVALUATE_PREDICATEを使用しないでください。
EVALUATE_PREDICATE('dense_rank() over (order by 1% ) < 5', sales.revenue)
EVALUATEを次のように使用します。
EVALUATE('dense_rank() over (order by 1% ) ', sales.revenue) < 5
例
SELECT year, Sales AS DOUBLE,CAST(EVALUATE('OLAP_EXPRESSION(%1,''LAG(units_cube_
sales, 1, time, time LEVELREL time_levelrel)'')', OLAP_CALC) AS DOUBLE) FROM
"Global".Time, "Global"."Facts - sales" WHERE EVALUATE_PREDICATE('OLAP_
CONDITION(%1, ''LIMIT time KEEP ''''1'''', ''''2'''', ''''3'''', ''''4'''' '')
=1', OLAP_CALC) ORDER BY year;
階層ナビゲーション関数を使用して、階層のメンバー間の関係を識別できます。
次の階層ナビゲーション関数があります。
IDOFおよびISLEAF関数は、レベルベースおよび親子階層の両方に適用されます。一方、他の関数は親子階層にのみ適用されます。
関係ソースの親子関係表(クローズ表)の作成に関する情報を含む、レベルベースおよび親子階層の詳細は、『Oracle Business Intelligence Enterprise Editionメタデータ・リポジトリ作成者ガイド』の論理ディメンションの操作に関する項を参照してください。
DEPTH関数は、ルート・メンバーからのメンバーの深さを示す、0より大きい整数を返します。
DEPTH関数は、ルート・メンバーからのメンバーの深さを示す、0より大きい整数を返します。ルート・メンバーの深さは1です。
構文
DEPTH(pc_presentation_hierarchy)
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
例
DEPTH("employees"."emp_hierarchy")
IDOF関数は、親子階層の指定されたメンバーのメンバー・キー列値の表現、またはレベルベース階層の指定されたレベルのレベル・キー列値の表現を返します。
指定された階層またはレベルに、1つの論理列のみを含むキーがある場合、この関数はその論理列の値を返します。指定されたレベルに複数の論理列を含むキーがある場合、この関数は連結文字列を返します。次に例を示します。
"keycolumn1value"."keycolumn2value"."keycolumn3value"…
注意:
親子階層は、複数の論理列を含むキーを持つことはありません。
指定された階層またはレベルにキーがない場合、エラーが表示されます。
構文
親子階層の場合:
IDOF(pc_presentation_hierarchy)
pc_presentation_hierarchy は、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
レベルベースの階層の場合:
IDOF(level)
ここで、levelは、次のようにレベル・ベースのディメンションのレベルに基づく完全修飾プレゼンテーション・レベルです。
"subject_area"."presentation_table"."presentation_hierarchy"."presentation_level"
例
IDOF("hr"."employees"."emp_hierarchy")
IDOF("market_data"."products"."product"."product")
ISANCESTOR関数を使用して、親子階層の1つ以上のメンバーの祖先(メンバーのすべての祖先またはそれらのメンバーからの指定された階層距離の祖先)を検索できます。
親子階層の各メンバーは、指定されたメンバーと比較され、それが祖先かどうか判断されます。ISANCESTOR関数は、指定されたメンバーの各祖先に対してブール値Trueを返します。それ以外は、Falseを返します。
この関数を使用して複数のメンバーの祖先を検索する場合、OR演算が実行されます。つまり、JoeまたはJuanaの祖先が検索されます。AND演算が必要な場合は、ISANCESTOR関数を複数回(メンバーごとに1回)コールします。
ISANCESTOR関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISANCESTOR関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISANCESTOR(pc_presentation_hierarchy, member_identifiers [, distance])
説明:
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語"subject_area"はオプションです。
member_identifiersは、pc_presentation_hierarchy内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
distance (オプション)は、指定されたメンバーから祖先を検索する親子階層レベルへの距離を特定する正の整数です。
デフォルトでは、distanceが指定されていないと、ISANCESTOR関数はmember_identifiersとすべての上位レベルを含む現在の親子レベルを検索します。
ビジネス・モデルとマッピング・レイヤーの構文
ISANCESTOR(logical_dimension, member_identifiers [, distance])
説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語"business_model"はオプションです。
member_identifiersは、logical_dimension内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
distance (オプション)は、指定されたメンバーから祖先を検索する親子階層レベルへの距離を特定する正の整数です。
デフォルトでは、distanceが指定されていないと、ISANCESTOR関数はmember_identifiersとすべての上位レベルを含む現在の親子レベルを検索します。
例
次の例は、親子階層で従業員Joeのすべての祖先の従業員を選択します。返されたリストには従業員Joeが含まれます。
SELECT emp_name
FROM "employees"
WHERE ISANCESTOR("employees"."emp_hierarchy", 'Joe')
ISCHILD関数を使用して、親子階層の1つ以上のメンバーの子(指定されたメンバーの1つ下の階層レベルのすべてのメンバー)を検索できます。
ISCHILD関数を使用して、親子階層の1つ以上のメンバーの子(指定されたメンバーの1つ下の階層レベルのすべてのメンバー)を検索できます。
注意:
ISCHILD関数は、ISDESCENDANT関数でdistanceパラメータに1を指定したものと同じです。
ISCHILD関数は、指定されたメンバーの各子に対してブール値Trueを返します。それ以外はFalseを返します。
この関数を使用して複数のメンバーの子を検索する場合、OR演算が実行されます。つまり、JoeまたはJuanaの子が検索されます。AND演算が必要な場合は、ISCHILD関数を複数回(メンバーごとに1回)コールします。
ISCHILD関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISCHILD関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。
注意:
関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISCHILD( pc_presentation_hierarchy, member_identifiers)
説明:
pc_presentation_hierarchy は、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
member_identifiersは、pc_presentation_hierarchy 内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
ビジネス・モデルとマッピング・レイヤーの構文
ISCHILD(logical_dimension, member_identifiers)
説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語"business_model"はオプションです。
例
次の例は、親子階層で従業員Joeのすべての子を選択します。
SELECT emp_name
FROM "employees"
WHERE ISCHILD("employees"."emp_hierarchy", 'Joe')
ISDESCENDANT関数を使用して、親子階層の1つ以上のメンバーの子孫(メンバーのすべての子孫またはそれらのメンバーからの指定された階層距離の子孫)を検索できます。
親子階層の各メンバーは、指定されたメンバーと比較され、それが子孫かどうか判断されます。ISDESCENDANT関数は、指定されたメンバーの各子孫に対してブール値Trueを返します。それ以外は、Falseを返します。
この関数を使用して複数のメンバーの子孫を検索する場合、OR演算が実行されます。つまり、JoeまたはJuanaの子孫が検索されます。AND演算が必要な場合は、ISDESCENDANT関数を複数回(メンバーごとに1回)コールします。
ISDESCENDANT関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISDESCENDANT関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISDESCENDANT(pc_presentation_hierarchy, member_identifiers [, distance])
説明:
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
member_identifiersは、pc_presentation_hierarchy 内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
distance (オプション)は、指定されたメンバーから子孫を検索する親子階層レベルへの距離を特定する正の整数です。
デフォルトでは、distanceが指定されていないと、ISDESCENDANT関数はmember_identifiersとすべての下位レベルを含む現在の親子レベルを検索します。
ビジネス・モデルとマッピング・レイヤーの構文
ISDESCENDANT(logical_dimension, member_identifiers [, distance])
説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語("business_model".)はオプションです。
member_identifiersは、logical_dimension 内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
distance (オプション)は、指定されたメンバーから子孫を検索する親子階層レベルへの距離を特定する正の整数です。
デフォルトでは、distanceが指定されていないと、ISDESCENDANT関数はmember_identifiersとすべての下位レベルを含む現在の親子レベルを検索します。
例
次の例は、親子階層で従業員Joeのすべての子孫の従業員を選択します。返されたリストには従業員Joeが含まれます。
SELECT emp_name
FROM "employees"
WHERE ISDESCENDANT("employees"."emp_hierarchy", 'Joe')
ISLEAF関数は、レベルベースおよび親子階層の両方に適用されます。
階層の両方のタイプとも、リーフ・メンバーとは子メンバーを持たないメンバーと定義されています。
階層の各メンバーは検証され、それがリーフ・メンバーかどうか判断されます。ISLEAF関数は、各リーフ・メンバーに対してブール値Trueを返します。それ以外はFalseを返します。
ISLEAF関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISLEAF関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。
注意:
関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISLEAF(presentation_hierarchy)
説明:
presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層(レベルベースまたは親子)を特定します。
"subject_area"."presentation_table"."presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
ビジネス・モデルとマッピング・レイヤーの構文
ISLEAF(logical_dimension)
説明:
logical_dimensionは、次のようにナビゲートする階層(レベルベースまたは親子)が含まれる完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語("business_model".)はオプションです。
例
次の例は、階層内のリーフ・メンバー(階層内でその下にメンバーがいない従業員)の従業員をすべて選択します。
SELECT emp_name
FROM "employees"
WHERE ISLEAF("employees"."emp_hierarchy")
ISPARENT関数を使用して、親子階層の1つ以上のメンバーの親(指定されたメンバーの1つ上の階層レベルのすべてのメンバー)を検索できます。
注意:
ISPARENT関数は、ISANCESTOR関数でdistanceパラメータに1を指定したものと同じです。
ISPARENT関数は、指定されたメンバーの各親に対してブール値Trueを返します。それ以外はFalseを返します。
この関数を使用して複数のメンバーの親を検索する場合、OR演算が実行されます。つまり、JoeまたはJuanaの親が検索されます。AND演算が必要な場合は、ISPARENT関数を複数回(メンバーごとに1回)コールします。
ISPARENT関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISPARENT関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。
注意:
関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISPARENT(pc_presentation_hierarchy, member_identifiers)
説明:
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
member_identifiersは、pc_presentation_hierarchy内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
ビジネス・モデルとマッピング・レイヤーの構文
ISPARENT(logical_dimension, member_identifiers)
説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語("business_model".)はオプションです。
member_identifiersは、logical_dimension内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
例
次の例は、親子階層で従業員Joeのすべての親を選択します。
SELECT emp_name
FROM "employees"
WHERE ISPARENT("employees"."emp_hierarchy", 'Joe')
プレゼンテーション階層メンバーは、親子プレゼンテーション階層でその上に祖先がない場合、ルート・メンバーとされます。
親子階層の各メンバーは検証され、それがルート・メンバーかどうか判断されます。ISROOT関数は、各ルート・メンバーに対してブール値Trueを返します。それ以外はFalseを返します。
ISROOT関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISROOT関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。
注意:
関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISROOT(pc_presentation_hierarchy)
説明:
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area")はオプションです。
ビジネス・モデルとマッピング・レイヤーの構文
ISROOT(logical_dimension)説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語("business_model")はオプションです。
例
次の例は、階層内のルート・メンバー(階層内でその上に祖先がいない従業員)の従業員をすべて選択します。
SELECT emp_name
FROM "employees"
WHERE ISROOT("employees"."emp_hierarchy")
ISSIBLING関数を使用して、親子階層の1つ以上のメンバーの兄弟(指定されたメンバーと同じ親を持つすべてのメンバー)を検索できます。
兄弟メンバーも他のメンバーの親になることができます。NULL親を持つルート・メンバーは、互いに兄弟になります。
ISSIBLING関数は、指定されたメンバーの各兄弟に対してブール値Trueを返します。それ以外は、Falseを返します。
この関数を使用して複数のメンバーの兄弟を検索する場合、OR演算が実行され、JoeまたはJuanaの兄弟が検索されます。AND演算が必要な場合は、ISSIBLING関数を複数回(メンバーごとに1回)コールします。
ISSIBLING関数は、問合せのCASE文およびWHERE句条件で使用できます。
ISSIBLING関数は、プレゼンテーション・レイヤーの問合せ、およびビジネス・モデルとマッピング・レイヤーの両方で使用できます(導出された列を作成する場合など)。
注意:
関数の構文は、それを使用している場所に依存します。
プレゼンテーション・レイヤーの構文
ISSIBLING(pc_presentation_hierarchy, member_identifiers)
説明:
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area")はオプションです。
member_identifiersは、pc_presentation_hierarchy内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
ビジネス・モデルとマッピング・レイヤーの構文
ISSIBLING(logical_dimension, member_identifiers)
説明:
logical_dimensionは、次のように親子関係を含む完全修飾されたディメンションを特定します。
"business_model"."dimension_name"
異なるビジネス・モデルに同じ名前を持つ複数のディメンションがなければ、修飾語("business_model")はオプションです。
member_identifiersは、logical_dimension 内の1つ以上のメンバーを識別する文字列または数値リテラルです。複数のリテラルはカンマで区切り、グループごとにかっこで囲んでください(例: (2, 3))。リテラルのタイプは、ディメンション・レベルキーのデータ型に依存します。
例
次の例は、親子階層で従業員Joeのすべての兄弟を選択します。
SELECT emp_name
FROM "employees"
WHERE ISSIBLING("employees"."emp_hierarchy", 'Joe')
PARENT関数は、親子階層の指定されたメンバーの親のメンバー・キー列値を返します。
PARENT関数は、親子階層の指定されたメンバーの親のメンバー・キー列値を返します。
指定された階層にキーがない場合、エラーが表示されます。
構文
PARENT(pc_presentation_hierarchy)
pc_presentation_hierarchyは、次のような完全修飾された親子プレゼンテーション階層を特定します。
"subject_area"."presentation_table"."pc_presentation_hierarchy"
異なるサブジェクト・エリアに同じ名前を持つ複数のプレゼンテーション表またはプレゼンテーション階層がなければ、修飾語("subject_area".)はオプションです。
例
PARENT("employees"."emp_hierarchy")
論理SQL文を使用してOracle BIサーバーをテストできます。SQL文はデータ・ソースに対して直接実行されます。
Oracle BIサーバーのODBC関数(NQSGetLevelDrillabilityなど)を使用して、サーバーに関する診断情報を取得できます。「SQLの発行」ページを使用すると、即時に応答が返されます。応答では、問合せの結果がSQL文で指定した形式で返されるか、エラー・メッセージと詳細が返されます。SQLの発行コマンドに添付されたログ・ファイルを照会するには、メッセージ・セッション権限が必要です。 「ログ情報、エラー・メッセージおよびアラートを表示するためのFusion Middleware Controlの使用」を参照してください。
「SQLの発行」ページでは、一部のSQL関数およびプロシージャ(NQSSetSessionVariables()プロシージャなど)をサポートしていません。
分析として発行したSQL文は保存できません。
Oracle BIサーバーは、Visual Analyzerユーザーがビン化およびトレンド線などのモデルを使用してデータを検索できる論理SQL関数をサポートしています。
これらの論理SQL関数は、プレゼンテーション・サービス・ユーザーも使用できます。この項では、次の項目について説明します。
Oracle BIサーバーは、BINおよびWIDTH_BUCKET論理SQL関数をサポートしています。これらの関数は同じパラメータおよび構文の一部を共有しますが、各関数の使用状況は異なります。
BINおよびWIDTH_BUCKET関数には次の違いがあります。
WIDTH_BUCKET関数
WIDTH_BUCKET関数は集計目的の新しいディメンション属性として処理されません。かわりに、RANK、TOPN、BOTTOMN、NTILE、PERCENTILE、MAVGおよびMEDIANなどの他の表示関数と似ている問合せ結果にWIDTH_BUCKET関数が適用されます。
すでに集計された問合せ結果セットのバケットの個別のセットを計算する場合、WIDTH_BUCKET関数を使用します。
BY句は常にWIDTH_BUCKET関数のオプションです。BY句がWIDTH_BUCKET関数から省略されると、関数は結果セット全体を操作します。
WIDTH_BUCKET関数のBY句は、WIDTH_BUCKET計算を適用する問合せ結果セットのグループを定義します。異なるグループ内のバケットが個別に計算されます。
BIN関数は集計目的の新しいディメンション属性として処理されます。
BIN関数のBY句は、ビン化された式をビン化の前に評価する粒度を定義します。ビン化された式がメジャーである場合、ビン化の前にBY句で指定された粒度でメジャーがグループ化されます。
ビン化された式がメジャーである場合、BIN関数のBY句は必須です。そうでない場合、メジャー以外の式のBY句はオプションです。
連続した値の属性またはメジャーの個別のバケットのセットを計算し、問合せの他のベース・メジャーのGROUP BY句に含まれる新しいディメンション属性のようにその新しいセットの個別のバケットを処理する場合、BIN関数を使用します。
BIN関数を使用して、データ値を含むディメンション、ファクト表またはメジャーの数値属性(INT、FLOAT、DOUBLE、NUMERIC)を選択し、個別のビンに格納できます。
BIN関数は、指定された数値式を、指定された数の等幅バケットに分類します。この関数は、ビン番号、またはビン間隔の2つのエンドポイントのいずれかを返すことができます。BIN関数の出力は、問合せに含まれる他のメジャーのGROUP BY式として使用されます。
たとえば、ユーザーが小売店の売上高を分析するとします。売上の見方として、ヒストグラム形式があります。これにより、$500より少ない、$500と$1000の間のように売上を表示できます。ヒストグラムを最初に作成するには、売上の合計値をビンに分類します。
集計、フィルタ処理およびドリルなどのために、BIN関数は新しいディメンション属性のように処理されます。これらのすべての操作は、BIN式でサポートされます。
WIDTH_BUCKET関数は、表示関数として最終の問合せ結果セットにバケット処理が必要なユーザーに対してBIN関数のかわりに提供されています。
構文
BIN(numeric_expr [BY grain_expr1, ..., grain_exprN] [WHERE condition]
INTO number_of_bins BINS [BETWEEN min_value AND max_value]
[RETURNING { NUMBER | RANGE_LOW | RANGE_HIGH }])
説明:
numeric_exprは、ビンに対するメジャーまたは数値属性を示します。
BY grain_expr1, ..., grain_exprNは、数値がビンに割り当てられる前にnumeric_exprの計算で使用される粒度を定義する式のリストを示します。この句はメジャー式の場合は必須で、属性式の場合はオプションです。
WHERE conditionは、数値がビンに割り当てられる前にnumeric_exprに適用するフィルタ条件を示します。
INTO number_of_binsは、返すビンの数を示します。デフォルト値は10です。
BETWEEN min_value AND max_valueは、最も外側のビンのエンドポイントに使用する最小値と最大値を示します。
RETURNINGは、数値がビンに割り当てられる前にnumeric_exprに適用するフィルタ条件を示します。次のオプションを使用できます。
RETURNING NUMBERは、戻り値がビン番号(1、2、3、4など)であることを示します。これはデフォルトの条件です。
RETURNING RANGE_LOWは、ビン間隔の下限値を示します。
RETURNING RANGE_HIGHは、ビン間隔の上限値を示します。
複数年の例
この例では、フィルタYear IN (2013, 2014, 2015)がRevenueの計算に適用されますが、製品ごとにビンを計算するために使用されるUnitPrice副問合せには影響しません。
SELECT Year, BIN(UnitPrice BY ProductName), Revenue FROM SampleSalesSubjectArea WHERE Year IN (2013, 2014, 2015);
Year Inフィルタの例
この例では、フィルタYear = 2014がビン計算に明示的に適用され、ビンを定義するために使用するQtySold式に影響します。Year IN (2013, 2014, 2015)フィルタがRevenueの計算に適用されますが、ビン定義には影響しません。
この問合せでは、ビン間隔の上下のエンドポイントが返されます。
SELECT Year, BIN(QtySold BY ProductName WHERE Year = 2014 INTO 20 BINS RETURNING RANGE_LOW), BIN(QtySold BY ProductName WHERE Year = 2014 INTO 20 BINS RETURNING RANGE_HIGH), Revenue FROM SampleSalesSubjectArea WHERE Year IN (2013, 2014, 2015);
QtySoldの例
この例では、メジャーQtySoldがCityの粒度で計算されます。地域に関係なく各都市にビン1、2、3、4または5が割り当てられます。
ビン番号(1から5)を含むビン問合せおよびCityの主キーがCityキーのメイン問合せに結合されます。メイン問合せがRegionおよびCityビン(1から5)でグループ化されます。
一部の地域は、各都市のビン番号が最終の結果セットに表されない可能性があります。たとえば、地域Eastの行にビン1、3、4のみが表され、地域Westにビン1、2、4、5が表される可能性があります。
SELECT Region, BIN(QtySold BY City INTO 5 BINS), Revenue FROM SampleSalesSubjectArea;
Age属性の例
この例では、Ageが属性であるため、BY句が省略されます。ビンを計算するために使用する粒度は、メタデータで定義されている属性の関連付けられたレベルによって決定されます。
SELECT BIN(Age), Revenue FROM SampleSalesSubjectArea;
AgeおよびCustomerの例
この例では、BY句はAge属性を計算する粒度を明示的に示します。この粒度で属性に複数の値が含まれる場合、Oracle BIサーバーの粒度チェックがエラー条件を検出し、複数の値を含むタプルを示すエラー・メッセージを表示します。
SELECT BIN(Age BY Customer), Revenue FROM SampleSalesSubjectArea;
WIDTH_BUCKET関数は、指定された数値式を、指定された数の等幅バケットに割り当てます。
WIDTH_BUCKET関数は、表示関数としてベース問合せ結果セットで動作します。この関数は、ビン番号、またはビン間隔の2つのエンドポイントのいずれかを返すことができます。
構文
WIDTH_BUCKET関数の構文では、単純なカンマ句切り引数を使用します。WIDTH_BUCKET関数でサポートされているネスト句はBY句のみです。
WIDTH_BUCKET(numeric_expr, { NUMBER | RANGE_LOW | RANGE_HIGH },
number_of_bins, [min_value, max_value] [BY expr1, ..., exprN])
説明:
numeric_exprは、ビンに対するメジャーまたは数値属性を示します。
NUMBERは戻り値がビン番号(たとえば、1、2、3、4)であることを示しています。Numberはデフォルトの戻り値です。
RANGE_LOWは、ビン間隔の下限値を示します。
RANGE_HIGHは、ビン間隔の上限値を示します。
number_of_binsは、返すビンの数を示します。デフォルト値は10です。
min_valueおよびmax_valueは、最も外側のビンのエンドポイントに使用する最小値と最大値を示します。min_valueおよびmax_value条件が省略されると、関数は自動的にエンドポイントを決定します。
BY expr1, ..., exprNは、WIDTH_BUCKET計算を適用する問合せ結果セットのグループを定義する式のオプションの式のリストを示します。異なるグループ内のバケット間隔が個別に計算されます。
Year INフィルタの例
この例では、フィルタYear IN (2013, 2014, 2015)が結果セット全体に適用されます。RevenueメジャーがYear、Categoryの粒度で計算されます。Year、Categoryの粒度ですでにフィルタ処理および集計されているRevenueメジャーを使用して、WIDTH_BUCKETが計算されます。
SELECT Year, Category, Revenue, WIDTH_BUCKET(Revenue, RANGE_HIGH, 5)
FROM SampleSalesSubjectArea
WHERE Year IN (2013, 2014, 2015);
Revenueメジャーの例
この例では、Year、Categoryの粒度でRevenueメジャーが計算され、集計されたRevenueメジャーにWIDTH_BUCKETが計算されます。
WIDTH_BUCKET関数は、値0と1,000,000の間の10個のバケットを指定します。結果として、それぞれ100,000の間隔で10個のバケットが作成されます。範囲(0、1,000,000)外のRevenueの値はオーバーフロー・バケットに格納されます。Revenueの値が0を下回る、または1,000,000を超える場合、結果にはバケット11または12が下限オーバーフロー値または上限オーバーフロー値として含まれる可能性があります。
SELECT Year, Category, Revenue, WIDTH_BUCKET(Revenue, NUMBER, 10, 0, 1000000) FROM SampleSalesSubjectArea;
独立バケット間隔の例
この例では、バケット間隔をYearごとに個別に計算するため、WIDTH_BUCKETがYearごとにパーティション化されます。結果がYear単位で分割して複数表示されない場合、問合せが無意味になることがあります。たとえば、トレリス・チャートを使用している場合、複数年にわたるバケット間隔のエンドポイントを一致させるような調整は必ずしも行われません。
SELECT Year, Category, Revenue, QtySold WIDTH_BUCKET(Revenue, RANGE_LOW, 5 BY Year) as Low, WIDTH_BUCKET(Revenue, RANGE_HIGH, 5 BY Year) as High FROM SampleSalesSubjectArea;
TRENDLINE関数は、時間のデータを測定し、順序付けられたレコード線グラフでメトリックを表示します。
TRENDLINE関数は、線形回帰および指数回帰としてデータをモデル化できます。
構文
TRENDLINE( <numeric_expr>, ( [<series>] ) BY ( [<partitionBy>] ), <model_type>, <result_type>, [number_of_degrees] )
説明:
numeric_exprはトレンド化するデータを示します。numeric_exprは、通常はメジャー列およびY軸です。
seriesはX軸を示します。シリーズ変数は、<valueExp> <orderByDirection>リストです。ここで、<valueExp>はディメンション列であり、<orderByDirection>の値はASC (昇順)またはDES (降順)です。デフォルトはASCです。任意の数値列の組合せを使用することはできません。
partitionByはトレンド線のコントロール・ブレークを示します。
model_typeは使用するモデルのタイプを示します。指定できるのはLINEARのみです。
result_typeは出力のタイプを示します。VALUE、MODELまたはSEQUENCEを指定できます。VALUEは、適合においてXを与えたときの回帰Yの値を返します。MODELは、JSON形式文字列のパラメータを返します。SEQUENCEは、Xに対して生成される連番を返します。
number_of_degreesは多項式モデルでのみ使用されます。number_of_degreesパラメータはオプションです。
Sales.Revenueおよびday.cal_yearの例
この例は、Y軸にSales.Revenue、X軸にday.cal_yearを使用したトレンド線を示します。回帰法は線形回帰で、返される型はvalueです。day.cal_yearは時間ディメンションのプライマリ・レベル・キーです。
SELECT day.cal_year, Sales.Revenue, TRENDLINE(Sales.Revenue, (day.cal_year) BY(), ('LINEAR', 'VALUE')FROM TimeSeriesTesting;
Product.CategoryIDの例
この例は、Product.CategoryIDごとにY軸にSales.Revenue、X軸にday.cal_yearを使用した個別のトレンド線を示します。
SELECT Product.CategoryID, day.cal_year, Sales.Revenue, TRENDLINE(Sales.Revenue, (day.cal_year), BY(Product.CategoryID), 'LINEAR', 'VALUE') FROM TimeSeriesTesting; //single trendline with control_break
複数TRENDLINE関数の例
この例は、同じ問合せの複数のTRENDLINE関数を示します。この例は、Y軸にSales.Revenue、X軸に(day.cal_year, day.cal_qtr)を使用します。
SELECT day.cal_year, day.cal_qtr, Sales.Revenue, TRENDLINE(Sales.Revenue,(day.cal_year,day.cal_qtr) BY(),'LINEAR', 'VALUE'), TRENDLINE(Sales.Revenue,(day.cal_year, day.cal_qtr), BY(),'LINEAR', 'MODEL') FROM TimeSeriesTesting order by 1; //multiple trendline functions
個別TRENDLINEの例
この例は、Product.CategoryIDごとにY軸にSales.Revenue、X軸にday.cal_yearを使用した個別のトレンド線を示します。Shipper.ShipperIDが問合せプロジェクション・リストに含まれますが、TrendlineではShipper.ShipperIDを計算には使用しません。
SELECT Product.CategoryID, Shipper.ShipperID, day.cal_year, Sales.Revenue, TRENDLINE(aggregate(Sales.Revenue by day.cal_year, Product.CategoryID), (day.cal_year), BY (Product.CategoryID), ('LINEAR', 'Value') FROM TimeSeriesTesting order by 1, 3; //Query grain can be lower than Trendline Y-axis grain.
単一数値列の例
この例は、Trendline X軸として1つの数値列を使用します。
SELECT employee.country, category.categoryid, sales.qtysold, TRENDLINE(sales.qtysold, (category.categoryid), BY (employee.country),'LINEAR', 'VALUE') FROM TimeSeriesTesting order by 1, 2;
TrendlineのX軸として単一の数値列を使用します。
Oracle BIサーバーは、Visual Analyzerユーザーが予測、クラスタおよび外れ値などのモデルを使用してデータを検索できる外部的な論理SQL関数をサポートしています。
この項で説明されている外部的な論理SQL関数は、プレゼンテーション・サービス・ユーザーも使用できます。
外部的な論理SQL関数は、入力データをスタンドアロンのRプロセスまたはORE (Oracle R Enterprise Edition)に提供します。環境および問合せのニーズに応じて、これらの関数を正常に使用する前に環境にRまたはORE外部エンジンを含める必要があります。『Oracle Business Intelligence Enterprise Editionシステム管理者ガイド』の外部論理SQL向けRおよびOracle R Enterpriseのインストールに関する項を参照してください。
この項では、次の項目について説明します。
EVALUATE_SCRIPT関数は、script_file_pathで指定されているようにRスクリプトを実行し、入力として1つ以上の列またはリテラル式を渡します。関数の出力はcolumn_nameによって決定されます。
構文
EVALUATE_SCRIPT(script_file_path,column_name,options,[runtime_binded_column_options])
説明:
script_file_pathはスクリプトのXMLファイル・パスを示します。たとえば、filerepo//obiee.TimeSeriesForecast.xmlです。
column_nameは予測する列名を示します。
optionsは、セミコロン(;)で区切られた名前または値のペアの文字列リストです。次に例を示します。
'algorithm=GLM;CustomerID=%1;ActualRevenue=%2;YearsAsCustomer=%3’
runtime_binded_column_optionsは、列式のオプションの変数リストを示します。1つ以上の列を指定できます。
EVALUATE_SCRIPTの例
この例は、EVALUATE_SCRIPTコマンドの一般的な使用方法を示します。
EVALUATE_SCRIPT( 'filerepo://PAFScore_bounded.xml', 'predictionValue', 'algorithm=GLM;CustomerID=%1;ActualRevenue=%2;YearsAsCustomer=%3', "A - Sample Sales"."Customers"."C0 Customer Number", "A - Sample Sales"."Datamining"."M01- Actual Revenue", "A - Sample Sales"."Customers"."C20 Years as Customer" ) PredictedRevenue
Clustering.xmlスクリプト・コールの例
この例は、EVALUATE_SCRIPT関数を使用してClustering.xmlスクリプトをコールする方法を示します。
SELECT "A - Sample Sales"."Customers"."C0 Customer Number" CustomerID, "A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue, EVALUATE_SCRIPT( 'filerepo://obiee.Clustering.xml', 'clusterId', 'algorithm=kmeans;numClusters=3;id=%1;arg1=%2;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev") ClusterId, EVALUATE_SCRIPT( 'filerepo://obiee.Clustering.xml', 'clusterName', 'algorithm=kmeans;numClusters=3;id=%1;arg1=%2;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev") ClusterName, EVALUATE_SCRIPT( 'filerepo://obiee.Clustering.xml', 'clusterSize', 'algorithm=kmeans;numClusters=3;id=%1;arg1=%2;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev") ClusterSize, EVALUATE_SCRIPT( 'filerepo://obiee.Clustering.xml', 'distanceFromCenter', 'algorithm=kmeans;numClusters=3;id=%1;arg1=%2;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev") DistanceFromCenter FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers". "C0 Customer Number" < 1000;
TimeSeriesForecast.xmlスクリプトの例
この例は、EVALUATE_SCRIPT関数を使用してTimeSeriesForecast.xmlスクリプトをコールする方法を示します。
SELECT "A - Sample Sales"."Time"."T00 Calendar Date" Day1, "A - Sample Sales"."Base Facts"."1- Revenue" Target, EVALUATE_SCRIPT( 'filerepo://obiee.TimeSeriesForecast.xml', 'forecast', 'numPeriods=7;predictionInterval=90;timeDay=%1;target=%2;', "A - Sample Sales"."Time"."T00 Calendar Date", "A - Sample Sales"."Base Facts"."1- Revenue") ForecastedRevenue, EVALUATE_SCRIPT( 'filerepo://obiee.TimeSeriesForecast.xml', 'high', 'numPeriods=7;predictionInterval=90;timeDay=%1;target=%2;', "A - Sample Sales"."Time"."T00 Calendar Date", "A - Sample Sales"."Base Facts"."1- Revenue") High, EVALUATE_SCRIPT( 'filerepo://obiee.TimeSeriesForecast.xml', 'low', 'numPeriods=7;predictionInterval=90;timeDay=%1;target=%2;', "A - Sample Sales"."Time"."T00 Calendar Date", "A - Sample Sales"."Base Facts"."1- Revenue") Low FROM "A - Sample Sales"order by 1;
Outliers.xmlの例
この例は、EVALUATE_SCRIPT関数を使用してOutliers.xmlをコールする方法を示します。
SELECT "A - Sample Sales"."Customers"."C0 Customer Number" CustomerID, "A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue, EVALUATE_SCRIPT( 'filerepo://obiee.Outliers.xml', 'isOutlier', 'algorithm=mvoutlier;id=%1;arg1=%2;arg2=%3;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample Sales"."Customers"."C21 Customer Age") IsOutlier, EVALUATE_SCRIPT( 'filerepo://obiee.Outliers.xml', 'distance', 'algorithm=mvoutlier;id=%1;=arg1=%2;arg2=%3;', "A - Sample Sales"."Customers"."C0 Customer Number" , "A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample Sales"."Customers"."C21 Customer Age") Distance FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers". "C0 Customer Number" < 1000
FORECAST関数は、指数平滑法またはARMIAを使用して、seriesに対して指定されたメジャーの時系列モデルを作成します。
FORECAST関数は、numPeriodsで指定された期間セットの予測を出力します。
構文
FORECAST(numeric_expr, ([series]), output_column_name, options,[runtime_binded_options])])
説明:
numeric_exprは、予測するメジャー、たとえば予測する収益データを示します。
seriesは予測モデルが作成される時間の粒度を示します。これは1つ以上の時間ディメンション列のリストです。seriesを省略すると、問合せから時間の粒度が決定されます。
output_column_nameは出力列を示します。有効な値は、forecast、low、highおよびpredictionIntervalです。
optionsは、セミコロン(;)で区切られた名前/値のペアの文字列リストを示します。値にはruntime_binded_optionsで指定される%1 ... %Nを含むことができます。
runtime_binded_optionsは、ランタイム・バインド列およびオプションのカンマ区切りリストを示します。
FORECAST関数のオプション
次の表に、FORECAST関数で使用可能なオプションを示します。
| オプション名 | 値 | 説明 |
|---|---|---|
| numPeriods | Integer | 予測する時間間隔の数 |
| predictionInterval | 0から100(値が大きいほど信頼度が高くなります) | 予測の信頼度レベル。 |
| modelType | ARIMA ETS |
予測に使用するモデル。 |
| useBoxCox | TRUE FALSE |
TRUEの場合、Box-Cox変換を使用します。 |
| lambdaValue | 該当なし | Box-Cox変換パラメータ。 NULLまたは それ以外の場合、モデルを予測する前にデータが変換されます。 |
| trendDamp | 該当なし | これはETSモデルのパラメータです。 TRUEの場合、減衰した傾向を使用します。NULLの場合、減衰した傾向および減衰していない傾向の両方を試行し、最適な方を選択します。 |
| errorType | 加法("A") 乗法("M") 自動選択("Z") |
これはETSモデルのパラメータです。 |
| trendType | なし("N") 加法("A") 乗法("M") 自動選択("Z") |
これはETSモデルのパラメータです。 |
| seasonType | なし("N") 加法("A") 乗法("M") 自動選択("Z") |
これはETSモデルのパラメータです。 |
| modelParamIC | ic_auto ic_aicc ic_bic ic_auto (これがデフォルトです) |
モデル選択に使用する情報基準(IC)。 |
日単位の収益予測の例
この例は日単位の収益予測を選択します。
FORECAST("A - Sample Sales"."Base Facts"."1- Revenue" Target,
("A - Sample Sales"."Time"."T00 Calendar Date"),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue
年および四半期単位の収益予測の例
この例は年単位および四半期単位の収益予測を選択します。
FORECAST("A - Sample Sales"."Base Facts"."1- Revenue",
("A - Sample Sales"."Time"."T01 Year" timeYear, "A - Sample Sales"."Time"."T02 Quarter" TimeQuarter),'forecast', 'numPeriods=30;predictionInterval=70;') ForecastedRevenue
カスタム予測スクリプトXMLファイルの例
この例は、カスタム予測スクリプトXMLファイルを使用して、年単位および四半期単位で収益予測を選択します。
FORECAST("A - Sample Sales"."Base Facts"."1- Revenue",
("A - Sample Sales"."Time"."T01 Year" timeYear, "A - Sample Sales"."Time"."T02 Quarter" TimeQuarter),'forecast',
'numPeriods=30;predictionInterval=70;Script=filerepo://PAFTimeSeriesForecast.xml'
) ForecastedRevenue
ランタイム・バインド・オプションの例
この例には、numPeriods=%1のランタイム・バインド・オプションが含まれます。
SELECT
"X - Airlines Traffic"."Month"."Dep Month" s_1,
"X - Airlines Traffic"."Passengers"."Passengers" s_2,
IFNULL(FORECAST(0.9*"X - Airlines Traffic"."Passengers"."Passengers" Target,
("X - Airlines Traffic"."Month"."Dep Month" Time_Month),'forecast',
'numPeriods=%1;predictionInterval=90',6),"X - Airlines
Traffic"."Passengers"."Passengers") s_3
FROM "X - Airlines Traffic"
浮動小数点時間粒度の例
この例は、浮動小数点の時間の粒度を使用した年単位および月単位の収益予測を選択します。
SELECT "A - Sample Sales"."Time"."T05 Per Name Year" TimeYear1, "A - Sample Sales"."Time"."T02 Per Name Month" Month1, "A - Sample Sales"."Base Facts"."1- Revenue" Target1,FORECAST( "A - Sample Sales"."Base Facts"."1- Revenue" , (), 'Forecast', 'numPeriods=%1;predictionInterval=70;',4) ForecastedRevenue FROM "A - Sample Sales" order by Month1;
個別の地域および国を使用する収益予測の例
この例は、RegionおよびCountry Nameごとに浮動小数点の時間の粒度を使用した年単位および月単位の収益予測を選択します。
SELECT
"A - Sample Sales"."Time"."T05 Per Name Year" TimeYear1,
"A - Sample Sales"."Time"."T02 Per Name Month" Month1,
"A - Sample Sales"."Base Facts"."1- Revenue" Target1,
"A - Sample Sales"."Office Regions"."D50 Region" Region,
"A - Sample Sales"."Office Regions"."D52 Country Name" Con,
FORECAST("A - Sample Sales"."Base Facts"."1- Revenue" ,
(),'Forecast','numPeriods=%1;predictionInterval=70;',4) ForecastedRevenue
FROM "A - Sample Sales"
order by region, con, Month1;
CLUSTER関数は、K平均法または階層的クラスタリングを使用する1つ以上の入力式に基づいて、レコード・セットを配置します。
オプション
この項では、CLUSTER関数で使用できるオプションおよびオプション値の情報について説明します。
| オプション名 | 値 | 説明 |
|---|---|---|
| algorithm | k-mean h-clustering |
クラスタリングに使用するアルゴリズム。 |
| method | K平均法アルゴリズムの場合のメソッド: Hartigan-Wong、Lloyd、Forgy、MacQueen。 階層的クラスタリングの場合のメソッド: ward.D、ward.D2、single、complete、average、mcquitty、medianまたはcentroid。 |
アルゴリズム内のメソッド。 |
| numClusters | Integer | クラスタの数。各レコードがクラスタのいずれかに割り当てられます。 |
| attributeNames | arg1、arg2、arg3、arg4、arg5、arg6、arg7、arg8、arg9、arg10 | クラスタリングを考慮する属性。 |
| maxIter | Integer | 最大反復回数。 |
| normalizedDist | True False |
TRUEに設定されている場合、0から100の間の距離を正規化します。 |
| useRandomSeed | True False |
デフォルトでTRUEに設定されます。TRUEに設定されている場合、値は本番環境で使用されます。 FALSEに設定されている場合、QA/デバッグ環境で使用するために |
| initialSeed | Integer | デフォルトは250です。
|
| clusterNamePrefix | Varchar | デフォルトは空です。 設定されている場合、これはクラスタ名の接頭辞です。 |
| clusterNameSuffix | Varchar | デフォルトは空です。 設定されている場合、これはクラスタ名の接尾辞です。 |
出力列
CLUSTER関数の出力には、次の列が含まれます。
この列はクラスタ番号またはIDです。
この列はクラスタの名前です。clusterIDと同じです。
エンド・ユーザーは、クラスタ・データ・セットがDSSに保持された後で説明を追加できます。
この列は現在のクラスタの要素の数です。
この列は、現在のクラスタ要素が現在のクラスタの中心からどれだけ離れているかを示します。
この列は、現在のクラスタの中心を示します。
構文
CLUSTER((dimension_expr1 , ... dimension_exprN), (expr1, .. exprN), output_column_name, options, [runtime_binded_options])
説明:
dimension_exprは、クラスタ化されるディメンションのリストを示します。
exprは、dimension_exprをクラスタ化するために使用するディメンション属性またはメジャーのリストを示します。
output_column_nameは、クラスタの出力列名を示します。有効な値は、clusterId、clusterName、clusterDescription、clusterSize、distanceFromCenter、およびcentersです。
optionsは、セミコロン(;)で区切られた名前/値のペアの文字列リストを示します。runtime_binded_optionsを使用して、指定できる%1 ... %Nを含むことができます。
runtime_binded_optionsは、ランタイム・バインド列またはリテラル式のカンマ区切りリスト(,)を示します。
例1
(オプション)ここには、リファレンスを説明する例を入力します。
SELECT "A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev"),
'clusterId', 'algorithm=k-means;numClusters=5;maxIter=10') ClusterId
FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers"."C0 Customer Number"
< 1000;
例2
この例は、Expected RevenueおよびCustomer AgeごとにCustomer Numberをクラスタ化し、1000未満の各Customer NumberのclusterIDを選択します。
SELECT
"A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),
'clusterId',
'algorithm=k-means;numClusters=5;maxIter=10'
) ClusterId
FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers"."C0 Customer Number" < 1000;
例3
この例は、カスタム・クラスタ・スクリプトXMLを使用します。カスタム・クラスタ・スクリプトXMLが他のAdvanced Analytics XMLファイルと同じディレクトリにあることが必要なので注意してください。
SELECT
"A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),
'clusterId',
'algorithm=k-means;numClusters=5;maxIter=10;Script=filerepo://CustomClusterScript.xml'
) ClusterId
FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers"."C0 Customer Number" < 1000;
例4
この例はランタイム・バインド・パラメータを示します。
SELECT
"A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),
'clusterId',
'algorithm=%2;numClusters=%1;maxIter=%3', 5, 'k-means', 10
) ClusterId
FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers"."C0 Customer Number" < 1000;
例5
この例は、Expected RevenueおよびCustomer AgeごとにCustomer Numberをクラスタ化し、1000未満の各Customer NumberのclusterIDおよびdistanceFromCenterを選択します。
SELECT
"A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),'clusterId',
'algorithm=%2;numClusters=%1;maxIter=%3', 5,'k-means', 10
) ClusterId,
CLUSTER(
( "A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),'distanceFromCenter',
'algorithm=%3;numClusters=%1;maxIter=%2', 5, 10, 'k-means'
) distanceFromCenter
FROM "A - Sample Sales" WHERE "A - Sample Sales"."Customers"."C0 Customer Number" < 1000;
例6
この例は、BrandおよびProduct Typeは個別にExpected RevenueおよびCustomer AgeごとにCustomer Numberをクラスタ化し、1000未満の各Customer NumberのclusterIDおよびdistanceFromCenterを選択します。
SELECT
"A - Sample Sales"."Customers"."C0 Customer Number" CustomerID,
"A - Sample Sales"."Datamining"."M02- Expected Rev" ActualRevenue,
"A - Sample Sales"."Products"."P4 Brand" Brand,
"A - Sample Sales"."Products"."P2 Product Type" ProductType,
CLUSTER(
("A - Sample Sales"."Customers"."C0 Customer Number"),
("A - Sample Sales"."Datamining"."M02- Expected Rev", "A - Sample
Sales"."Customers"."C21 Customer Age"),
'clusterName',
'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;', 2, 10
) clusterName
FROM "A - Sample Sales"
where Brand = 'FunPod' order by Brand, ProductType;
例7
この例は、IDリストの複数の列(ProductおよびCompany)をクラスタ化します。両方のIDリスト列がプロジェクション・リストにあるため、パーティション・ディメンションはありません。
SELECT
"A - Sample Sales"."Products"."P1 Product" s_1,
"A - Sample Sales"."Offices"."D4 Company" s_2,
CLUSTER(
("A - Sample Sales"."Products"."P1 Product", "A - Sample Sales"."Offices"."D4 Company"),
("A - Sample Sales"."Base Facts"."2- Billed Quantity","A - Sample Sales"."Base Facts"."1- Revenue"),
'clusterName',
'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;', 5, 10) s_3,
"A - Sample Sales"."Base Facts"."2- Billed Quantity" s_4,
"A - Sample Sales"."Base Facts"."1- Revenue" s_5
FROM "A - Sample Sales";
例8
この例は、IDリストの複数の列(ProductおよびCompany)をクラスタ化します。1つのパーティション・ディメンション(City)があります。
SELECT
"A - Sample Sales"."Products"."P1 Product" s_1,
"A - Sample Sales"."Offices"."D4 Company" s_2,
"A - Sample Sales"."Office Regions"."D55 City" s_3,
CLUSTER(
("A - Sample Sales"."Products"."P1 Product", "A - Sample Sales"."Offices"."D4 Company"),
("A - Sample Sales"."Base Facts"."2- Billed Quantity","A - Sample Sales"."Base Facts"."1- Revenue"),
'clusterName',
'algorithm=k-means;numClusters=%1;maxIter=%2;useRandomSeed=FALSE;', 5, 10) s_4,
"A - Sample Sales"."Base Facts"."2- Billed Quantity" s_5,
"A - Sample Sales"."Base Facts"."1- Revenue" s_6
FROM "A - Sample Sales" order by s_3
REGR関数は、線形モデルに適合させて、適合した値またはモデルを返します。
この関数は、2つのメジャーに線形曲線を適合するために使用できます。
オプション
次のオプションをREGR関数で使用できます。
| オプション名 | 説明 | 値 |
|---|---|---|
| algorithm | 回帰に使用するアルゴリズム。 | LM |
| attributeNames | 回帰に考慮する属性。 | arg1、arg2、arg3、arg4、arg5、arg6、arg7、arg8、arg9、arg10 |
| targetNames | 反復数。 | target |
| showModelDescription | これは、デフォルトではTRUEに設定されます。 TRUEに設定されている場合、モデルの説明がJSON形式で書き込まれます。 |
TRUE FALSE |
構文
REGR(y_axis_measure_expr, (x_axis_expr), (category_expr1, ..., category_exprN), output_column_name, options, [runtime_binded_options])
説明:
y_axis_measure_exprは、回帰モデルの計算対象のメジャーを示します。
x_axis_measure_exprは、y_axis_measure_exprの回帰モデルを決定するために使用するメジャーを示します。
category_expr1, ..., category_exprNは、y_axis_measure_exprの回帰モデルの計算対象のカテゴリを決定するために使用するディメンション/ディメンション属性を示します。1から5個までのディメンション/ディメンション属性をカテゴリ列として追加できます。
output_column_nameは、回帰の出力列名を示します。有効な値は、fitted、intercept、またはmodelDescriptionです。
optionsは、セミコロン(;)で区切られた名前/値のペアの文字列リストを示します。値には%1 ... %Nを含めることができ、runtime_binded_optionsを使用してこれを指定します。
runtime_binded_optionsは、ランタイム・バインド列またはリテラル式を含む、オプションのカンマ(,)区切りリストです。
例1-1 Discount Amountに基づく回帰モデルの例
この例は、Product TypeおよびBrandごとのDiscountに基づく回帰モデルを使用して決定されRegionおよびCountry Nameでパーティション化されるRevenueの適合した値を選択します。
SELECT
"A - Sample Sales"."Office Regions"."D50 Region" Region,
"A - Sample Sales"."Office Regions"."D52 Country Name" Con,
"A - Sample Sales"."Products"."P4 Brand" Brand,
"A - Sample Sales"."Products"."P2 Product Type" ProductType,
"A - Sample Sales"."Base Facts"."3- Discount Amount" Discount,
"A - Sample Sales"."Base Facts"."1- Revenue" ActualRevenue,
REGR(
"A - Sample Sales"."Base Facts"."1- Revenue" ,
("A - Sample Sales"."Base Facts"."3- Discount Amount"),
("A - Sample Sales"."Products"."P2 Product Type", "A - Sample Sales".
"Products"."P4 Brand"), 'FITTED', 'algorithm=%1', 'LM')
FROM "A - Sample Sales";
例1-2 Revenue回帰モデルの例
この例は、Product Type、Brand、Region、Country NameごとのDiscountに基づく回帰モデルを使用して決定されるRevenueの適合した値を選択します。
SELECT
"A - Sample Sales"."Office Regions"."D50 Region" Region,
"A - Sample Sales"."Office Regions"."D52 Country Name" Con,
"A - Sample Sales"."Products"."P4 Brand" Brand,
"A - Sample Sales"."Products"."P2 Product Type" ProductType,
"A - Sample Sales"."Base Facts"."3- Discount Amount" Discount,
"A - Sample Sales"."Base Facts"."1- Revenue" ActualRevenue,
REGR(
"A - Sample Sales"."Base Facts"."1- Revenue" ,
("A - Sample Sales"."Base Facts"."3- Discount Amount"), (),
'FITTED', 'algorithm=%1', 'LM')
FROM "A - Sample Sales";
例1-3 Discount Amountに基づく回帰モデルの例
この例は、Product TypeおよびBrandごとのDiscountに基づく回帰モデルを使用して決定されるRevenueの適合した値を選択します。
SELECT
"A - Sample Sales"."Products"."P4 Brand" Brand,
"A - Sample Sales"."Products"."P2 Product Type" ProductType,
"A - Sample Sales"."Base Facts"."3- Discount Amount" Discount,
"A - Sample Sales"."Base Facts"."1- Revenue" ActualRevenue,
REGR(
"A - Sample Sales"."Base Facts"."1- Revenue" ,
("A - Sample Sales"."Base Facts"."3- Discount Amount"),
("A - Sample Sales"."Products"."P2 Product Type", "A - Sample Sales".
"Products"."P4 Brand"), 'FITTED', 'algorithm=%1', 'LM')
FROM "A - Sample Sales";
例1-4 Brandに基づく回帰モデルの例
この例は、Product TypeおよびBrandごとのDiscountに基づく回帰モデルを使用して決定されるRevenueの適合した値を選択します。
SELECT
"A - Sample Sales"."Products"."P4 Brand" Brand,
"A - Sample Sales"."Products"."P2 Product Type" ProductType,
"A - Sample Sales"."Base Facts"."3- Discount Amount" Discount,
"A - Sample Sales"."Base Facts"."1- Revenue" ActualRevenue,
REGR(
"A - Sample Sales"."Base Facts"."1- Revenue" ,
("A - Sample Sales"."Base Facts"."3- Discount Amount"),
(), 'FITTED', 'algorithm=%1', 'LM')
FROM "A - Sample Sales";
Oracle Fusion Middleware Oracle Business Intelligence Enterprise Edition論理SQLリファレンス・ガイド, リリース12c (12.2.1.4.0)
E96102-01
Copyright © 2016, 2018, Oracle and/or its affiliates. All rights reserved.
このソフトウェアおよび関連ドキュメントの使用と開示は、ライセンス契約の制約条件に従うものとし、知的財産に関する法律により保護されています。ライセンス契約で明示的に許諾されている場合もしくは法律によって認められている場合を除き、形式、手段に関係なく、いかなる部分も使用、複写、複製、翻訳、放送、修正、ライセンス供与、送信、配布、発表、実行、公開または表示することはできません。このソフトウェアのリバース・エンジニアリング、逆アセンブル、逆コンパイルは互換性のために法律によって規定されている場合を除き、禁止されています。
ここに記載された情報は予告なしに変更される場合があります。また、誤りが無いことの保証はいたしかねます。誤りを見つけた場合は、オラクル社までご連絡ください。
このソフトウェアまたは関連ドキュメントを、米国政府機関もしくは米国政府機関に代わってこのソフトウェアまたは関連ドキュメントをライセンスされた者に提供する場合は、次の通知が適用されます。
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
このソフトウェアまたはハードウェアは様々な情報管理アプリケーションでの一般的な使用のために開発されたものです。このソフトウェアまたはハードウェアは、危険が伴うアプリケーション(人的傷害を発生させる可能性があるアプリケーションを含む)への用途を目的として開発されていません。このソフトウェアまたはハードウェアを危険が伴うアプリケーションで使用する際、このソフトウェアまたはハードウェアを安全に使用するために、適切な安全装置、バックアップ、冗長性(redundancy)、その他の対策を講じることは使用者の責任となります。このソフトウェアまたはハードウェアを危険が伴うアプリケーションで使用したことに起因して損害が発生しても、オラクル社およびその関連会社は一切の責任を負いかねます。
OracleおよびJavaはOracle Corporationおよびその関連企業の登録商標です。その他の名称は、それぞれの所有者の商標または登録商標です。
Intel、Intel Xeonは、Intel Corporationの商標または登録商標です。すべてのSPARCの商標はライセンスをもとに使用し、SPARC International, Inc.の商標または登録商標です。AMD、Opteron、AMDロゴ、AMD Opteronロゴは、Advanced Micro Devices, Inc.の商標または登録商標です。UNIXは、The Open Groupの登録商標です。
このソフトウェアまたはハードウェア、そしてドキュメントは、第三者のコンテンツ、製品、サービスへのアクセス、あるいはそれらに関する情報を提供することがあります。適用されるお客様とOracle Corporationとの間の契約に別段の定めがある場合を除いて、Oracle Corporationおよびその関連会社は、第三者のコンテンツ、製品、サービスに関して一切の責任を負わず、いかなる保証もいたしません。適用されるお客様とOracle Corporationとの間の契約に定めがある場合を除いて、Oracle Corporationおよびその関連会社は、第三者のコンテンツ、製品、サービスへのアクセスまたは使用によって損失、費用、あるいは損害が発生しても一切の責任を負いかねます。
このドキュメントはリリース前のものであり、デモおよび暫定使用のみを目的としています。このソフトウェアを使用するハードウェアに限定するものではありません。Oracle社およびその関連会社は、このドキュメントに関して一切の責任を負わず、いかなる保証もいたしません。また、このドキュメントを使用したことによって損失、費用、あるいは損害が発生しても、一切の責任を負いかねます。
このドキュメントの情報は、情報共有の目的のみに使用され、顧客諮問委員会の会員の立場で考慮されるか、ベータ・トライアル版契約のみに準じる必要があります。資料、規約または機能性を配信する義務はなく、購買決定に依存すべきではないものとします。このドキュメントに記載されている開発、リリース、およびすべての機能または機能性の時期は、オラクル社の独自の判断に従うものとします。
このドキュメントは、ソフトウェアまたは印刷物などの形式にかかわらず、オラクル社の独占的な財産である機密情報を含みます。この機密資料へのアクセスと使用は、オラクル社との間で締結され遵守に同意したOracle Master Agreement、Oracle License and Services Agreement、Oracle PartnerNetwork Agreement、オラクル販売契約、あるいはその他のライセンス契約の条件にしたがうものとします。オラクル社の文書による事前承諾なしに、このドキュメントおよびここに含まれる情報を開示、コピー、再生、またはオラクル社外部の他者に配布することはできません。このドキュメントは使用許諾契約の一部ではなく、オラクル社の子会社またはアフィリエイトとの契約上の合意に組み込まれません。