Go to Qiita Advent Calendar 2024 Top
LoginSignup
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@tyo38

Oracle Analytics Cloud：組み込みのIndexCol関数で条件分岐する

Posted at

はじめに

Oracle Analytics Cloud（OAC）を使用してデータ検索する際のテクニックとして、条件分岐の方法を紹介します。
円、ドル、ユーロで換算した金額列を持つ表を検索します。
その際、どの列を検索するかをセッション変数の内容で条件分岐してみます。

記事執筆時点でのOAC最新版である Jan 2024 Update で動作確認しました。

1. 条件分岐の関数

検索する際に条件分岐をしたいことがあると思います。
例えば、列Aのデータの先頭が0だったら列Bを検索し、1だったら列Cを検索するというケースを想定してみます。

1.1 Case文

Case文は関数ではないですが、条件分岐をするのに適しています。

SQLの例
select case substr(列A, 1, 1) when '0' then 列B when '1' then 列C else 列D end from 表A

OACからもこういったSQLを発行することはできます。
この場合、Case文がそのままSQLとして実行され、表の全行を評価することになります。
大規模な表に対して実行すると時間がかかる可能性があります。

1.2 IndexCol関数

OACでのもう一つの選択肢がIndexCol関数です。
IndexColはOACの論理SQLで使用できるOACの組み込み関数です。
IndexCol関数では、物理SQLに変換される前にどの列を検索するかが決定されます。
つまり、生成された物理SQLにはCase文は含まれず、特定の列を検索する普通のSQLとなります。
IndexCol関数の書式は、次の通りです。

OACの論理SQLの例（IndexCol関数）
select IndexCol(Cast(Substring(列A from 1 for 1) as integer), 列B, 列C, 列D) from 表A

最初の引数は0以上の整数を返す必要があります。
0なら第二引数の列、1なら第三引数の列、・・・という具合に検索する列が決定されます。

2. IndexColを論理列に実装

セマンティック・モデラーを使用してIndexCol関数の実装例を紹介します。
基本的な検索ができるセマンティック・モデルは完成しているものとします。
セマンティック・モデラーの使い方については、こちらを参考にしてください。

Developer Client Toolを使用しても同様の設定は可能です。

2.1 セマンティック・モデラーで初期化ブロックを作成

「変数」に移動し「＋」をクリックして、「初期化ブロックの作成」をクリックします。
image.png

「Currency」という名前の初期化ブロックを作成します。
タイプは「セッション」とします。
この初期化ブロックは、セッション単位で実行されることになります。
image.png

初期化ブロックの設定をします。
問合せの返しは「変数値」とします。
SELECT文は

select 'JPY' from dual

と入力します。
DUAL表とは、Oracle Databaseにおいてセットアップ時から存在する、1列1行しかない特別な表です。
このSQLは「JPY」という値を返します。
定義済みのAutonomous DatabaseやOracle Databaseを検索する接続プールを指定します。

この値をセットするセッション変数を「＋」をクリックして追加します。
image.png

セッション変数の名前を「CURRENCY」として、デフォルト値を「'JPY'」とします。
image.png

2.2 論理レイヤーに新しい論理列を作成

「論理レイヤー」に移動します。
任意の論理フォルダに新規列を追加します。
今回は、ファクト表が元になっている論理フォルダを使いました。
image.png

名前を入力し、ソースを「論理式」とします。
「fx」アイコンをクリックして式エディタで式を入力します。
image.png

変数アイコンをクリックすると、先程定義した「CURRENCY」セッション変数が表示されているのがわかります。
ダブルクリックして、式に含めることができます。
image.png

論理SQLの例です。

IndexCol(case VALUEOF(NQ_SESSION.CURRENCY) when 'JPY' then 0 when 'USD' then 1 when 'EUR' then 2 else 0 end, 販売金額 (JPY), 販売金額 (USD), 販売金額 (EUR) )

image.png
変数CURRENCYの中身をCASE文で判定して、0～2の値を返します。デフォルト値は0です。
IndexCol関数は第一引数であるCASE文の値が0なら第二引数の列を、1なら第三引数の列を、、、というように物理SQLに含める列を判定していきます。

IndexCol関数は物理SQLを生成する前に式を評価するので、case文は物理SQLには含まれません。
image.png

2.3 作成した論理列をサブジェクト領域に追加しデプロイ

「プレゼンテーション・レイヤー」に移動します。
論理レイヤーに作成した列をプレゼンテーション・フォルダに追加します。
image.png

デプロイします。
必要に応じて、デプロイ前にスナップショットを取得しておいてください。
image.png

3. 動作確認

作成（編集）したサブジェクト領域は、ワークブックからでもアンサーからでも使用できます。
今回は、アンサーを使用します。
「クラシック・ホームを開く」をクリックします。
image.png

3.1 普通に検索してみる

「作成」メニューから「分析」をクリックします。
image.png

先ほど編集したサブジェクト領域を選択します。
image.png

属性列である「ブランド」とIndexCol関数を設定した「販売金額」を選択します。
比較しやすいように「販売金額 (USD)」「販売金額 (JPY)」「販売金額 (EUR)」も同時に選択しました。
image.png

「結果」タブに移動して検索結果を確認します。
image.png

セッション変数「CURRENCY」にはデフォルト値である「JPY」という値がセットされており、その場合「販売金額 (JPY)」列を検索することになるので、「販売金額」と「販売金額 (JPY)」が同じ値を返していることがわかります。

この分析に名前を付けて保存しておきます。
共有フォルダの「Demo」サブフォルダに「ブランド別販売金額」と名前を付けて保存しました。
image.png

3.2 変数プロンプトで列を切り替える

ダッシュボード・プロンプトの一種である変数プロンプトを使用して、セッション変数の中身を動的に変更すると検索結果がどう変わるかを確認します。

3.2.1 変数プロンプトを作成

「作成」メニューから「ダッシュボード・プロンプト」をクリックします。
image.png

分析を作成する際に使用したサブジェクト領域を選択します。
image.png

「＋」アイコンをクリックして、「変数プロンプト」を選択します（通貨プロンプトではありません）。
image.png

プロンプト対象を「リクエスト変数」にし、「CURRENCY」と入力します。
これにより、セッション変数「CURRENCY」の内容を変更できます。
ユーザー入力を「ラジオ・ボタン」に変更します。
image.png

ラジオ・ボタンの値が「カスタム値」になっていることを確認し「＋」をクリックします。
image.png

「JPY」と入力します。
image.png

同様の手順で「USD」と「EUR」も追加します。
オプションを開いて、デフォルトの選択を「サーバー変数」にして「CURRENCY」を入力します。
ラジオ・ボタンのレイアウトは「水平方向」にします。
image.png

オプションの「詳細」タブをクリックして、ラジオ・ボタンの幅を「動的」にします。
「OK」します。
image.png

プレビューを確認し、編集アイコンをクリックします。
image.png

タイトルの「ページ1」を削除します。
「適用」ボタンの表示と「リセット」ボタンの表示のチェックを外し、「OK」をクリックします。
image.png

プロンプトを保存します。
image.png

3.2.2 ダッシュボードを作成

「新規」メニューから「ダッシュボード」を選択します。
image.png

任意の名前を入力し、場所を指定します。
「すぐにコンテンツを追加」が選択されていることを確認して「OK」をクリックします。
image.png

先ほど作成した分析とプロンプトをドラッグ＆ドロップします。
image.png

ダッシュボードを上書き保存してから「実行」をクリックします。
image.png

プロンプトで「JPY」を選択した状態
image.png

プロンプトで「USD」を選択した状態
image.png

プロンプトで「EUR」を選択した状態
image.png

セッション変数「CURRENCY」の値が変数プロンプトの値によって動的に変更され、そのセッション変数を参照して検索列を決定しているIndexCol関数は、選択する列を動的に変更しているのがわかります。

プロンプトで変更されるのは、そのセッション中のセッション変数の内容のみです。

4. サンプルデータ

Autonomous DatabaseのSALESというスキーマに、次のサンプルデータを作成しました。
SAMPLE_SALES表には製品の売上金額がドル、円、ユーロにそれぞれ換算した値で格納されます。

create table SALES.PROD
(
PROD_ID	varchar2(1),
PROD_NAME	varchar2(40),
PROD_TYPE	varchar2(24),
PROD_LOB	varchar2(24),
PROD_BRAND	varchar2(12)
);

ALTER TABLE SALES.PROD MODIFY (PROD_ID NOT NULL);
ALTER TABLE SALES.PROD ADD CONSTRAINT PROD_PK PRIMARY KEY (PROD_ID) ENABLE;

create table SALES.SAMPLE_SALES
(
PROD_ID	varchar2(1),
ORDER_DAY_DT	date,
REVENUE_USD	number,
REVENUE_JPY	number,
REVENUE_EUR	number,
UNITS	number
);

ALTER TABLE SALES.SAMPLE_SALES ADD CONSTRAINT SAMPLE_SALES_JA_FK FOREIGN KEY (PROD_ID)
REFERENCES SALES.PROD (PROD_ID) ENABLE;



insert into SALES.PROD values('1', 'MP3スピーカーシステム', 'アクセサリー', 'エレクトロニクス', 'BizTech');
insert into SALES.PROD values('2', 'SoundX Nano 4Gb', 'オーディオ', 'エレクトロニクス', 'BizTech');
insert into SALES.PROD values('3', 'Touch-Screen T5', 'スマートフォン', '通信', 'BizTech');
insert into SALES.PROD values('4', 'LCD 36X Standard', '液晶', 'テレビ', 'HomeView');
insert into SALES.PROD values('5', '設置', '設置', 'サービス', 'HomeView');
insert into SALES.PROD values('6', '保守', '保守', 'サービス', 'HomeView');
insert into SALES.PROD values('7', 'ゲーム・ステーション', '据え置き', 'ゲーム', 'FunPod');
insert into SALES.SAMPLE_SALES values('3', '2020/12/16', 2167.05, 270881.25, 1733.64, 3);
insert into SALES.SAMPLE_SALES values('3', '2021/01/25', 2966.06, 370757.5, 2372.85, 4);
insert into SALES.SAMPLE_SALES values('3', '2021/01/30', 1142.11, 142763.75, 913.69, 3);
insert into SALES.SAMPLE_SALES values('1', '2021/02/05', 2213.71, 276713.75, 1770.97, 4);
insert into SALES.SAMPLE_SALES values('2', '2021/02/28', 4211.31, 526413.75, 3369.05, 1);
insert into SALES.SAMPLE_SALES values('2', '2021/03/06', 2097.49, 262186.25, 1677.99, 1);
insert into SALES.SAMPLE_SALES values('1', '2021/03/08', 1602.11, 200263.75, 1281.69, 2);
insert into SALES.SAMPLE_SALES values('7', '2023/04/03', 1567.62, 221034.42, 1489.24, 2);
insert into SALES.SAMPLE_SALES values('7', '2023/04/08', 936.92, 132105.72, 890.07, 2);
insert into SALES.SAMPLE_SALES values('7', '2023/04/28', 3068.83, 432705.03, 2915.39, 3);
insert into SALES.SAMPLE_SALES values('7', '2023/05/03', 1127.04, 158912.64, 1070.69, 3);
insert into SALES.SAMPLE_SALES values('4', '2023/06/26', 3648.65, 514459.65, 3466.22, 3);
insert into SALES.SAMPLE_SALES values('6', '2023/07/11', 1081.81, 152535.21, 1027.72, 3);
insert into SALES.SAMPLE_SALES values('5', '2023/07/12', 2245.11, 316560.51, 2132.85, 3);
insert into SALES.SAMPLE_SALES values('4', '2023/08/21', 3224.78, 454693.98, 3063.54, 3);
commit;
1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?