選択ビューの作成

選択ビューは、入力ビューに対して選択 (フィルタリング) と投影の関係代数演算を実行します。演算の対象とする入力ビューは、異なるデータベースに属していてもかまいません。

選択ビューを作成するには、[File] > [New] メニューで [Selection] をクリックするか、[Server Explorer] で右クリックして [New] メニューで [Selection] をクリックします。

[Selection view] ダイアログが開き、[Server Explorer] で現在選択しているビューが追加されます。入力ビューを変更するには、 image0 ボタンをクリックして [Model] タブから現在のビューを削除し、[Server Explorer] から別のビューをこのタブにドラッグします。

このマニュアルの例では、「 結合ビューの作成 」で作成した incidents_sales ビューをドラッグします。

[Selection view] ダイアログには、以下の 5 つのタブがあります。

  1. Model: 新しいビューのソースにするビューを、このタブにドラッグします。現在とは別のデータベースにあるビューをドラッグしてもかまいません。

    このタブで ビューパラメーター を追加できます。

  2. Where Conditions: ビューの定義に WHERE 条件を追加できます。

    WHERE 条件を追加する場合、[WITH CHECK OPTION ** clause**] オプションのいずれかを選択して、挿入または更新するすべての行が、このビューの定義に従うようにするかどうかを指定する必要があります (『VQL ガイド』の「 WITH CHECK OPTION の使用 」を参照)。選択可能なオプションは次のとおりです。

    1. None: クライアントがこのビューに対して INSERT 操作を実行する際に、何も確認されません。

    2. Local: 行を挿入または更新する際に、このビューの選択条件との適合が確認されます。下位レベルのビュー (このビューの定義に記述されているビュー) の条件との適合は確認されません。

    3. Cascade: 行を挿入または更新する際に、このビューの投影条件およびその下位レベルのビューの投影条件との適合が確認されます。

    [Automatic simplification of queries] が有効な場合、派生ビューに対して INSERTUPDATE 、または DELETE のクエリを実行すると、このビューが [WITH CHECK OPTION] を指定して作成されていると見なされます。これにより、挿入、更新、または削除するデータが、ビューの定義の WHERE 条件を満たすかどうかが確認されます。

    このオプションが有効かどうかを確認するには、[Administration] > [Server configuration] メニューで [Queries optimization] をクリックします。

  3. Group By: ビューに GROUP BY フィールドを追加できます。

  1. Output: ビューの出力を構成できます。具体的には、ビューとそのフィールドの名前の変更、派生属性の追加、ビューのプライマリキーの定義などができます。

  2. Metadata: 新しいビューの格納先フォルダーと説明を定義できます。

[Where Conditions]、[Group By]、[Output]、および [Metadata] の各タブの動作は [Union view] ダイアログの場合と同じです。使用方法の詳細については、「 和結合ビューの作成 」を参照してください。

このマニュアルの例では、ここで以下の 2 つのビューを作成します。

  • [Server Explorer] で incidents_sales ビューを右クリックし、[File] > [New] メニューで [Selection] をクリックします。このビューが [Model] タブに追加されます。

  • [Where Conditions] タブで、平均月間売上高が 600 ユーロを超える顧客のデータのみを取得するために、条件 revenue>600 を追加します。

  • [Output] タブで、ビューの名前を pref_clients_inc_sales に変更します。

つづいて、[Save] をクリックしてビューを作成すると、新しいビューのスキーマが表示されます。

ここで、GROUP BY フィールドを持つ 2 番目のビューを、次のように作成する必要があります。

  • [Server Explorer] で pref_clients_inc_sales ビューを右クリックし、[File] > [New] メニューで [Select] をクリックします。

  • [Group By] タブで以下の手順に従います。

    • [Use group by] チェックボックスをチェックします。

    • taxId フィールドと revenue フィールドを選択して [Add >>] をクリックします。

  • [Output] タブで以下の手順に従います。

    • ビューの名前を inc_grouped_by_pref_clients に変更します。

    • [New Aggr. expression] をクリックします。新しいフィールドの名前は num_incidents 、式は COUNT(*) です。

  • [Save] をクリックします。

Creating the selection view inc_grouped_by_pref_clients ("Group By" tab)

選択ビュー inc_grouped_by_pref_clients の作成 ([Group By] タブ)

Compound Values Editor での条件の作成

注釈

現在、Design Studio では Compound Values Editor を使用できないので、VQL 構文を使用して値を直接記述する必要があります。今後の更新で、現在のオプションを拡張して Compound Values Editor を追加する予定です。

派生ビュー (結合、和結合、差結合、積結合、フラット化、および選択) を作成および編集するすべてのダイアログには、ビューの定義に WHERE 条件を追加するための [Where Conditions] タブがあります。このタブの [Simple condition] モードを使用する場合は、複合型 (register または array) の定数を作成するオプションとして以下の 2 つがあります。

  1. VQL 構文で値を直接記述します。たとえば、 { ROW( 'B78596011' ), ROW( 'B78596012' ) } とします。

    この構文の詳細については、『VQL ガイド』の「 複合値を使用した条件 」を参照してください。

  2. Compound Values Editor を使用します。このエディターを開くには、右側のオペランドの横にある image1 をクリックします (register 型フィールドまたは array 型フィールドでのみ使用可能)。

例として、複合値を使用した WHERE 条件を持つ新しい選択ビューを作成する手順について説明します。その前に、新しい基本ビューを作成する必要があります。

  1. Web サービスデータソースの sales (「 SOAP Web サービスソースのインポート 」で作成したソース) を開き、[Create base view] をクリックします。

  2. getSumRevenueByTaxIds 操作の横にある [Create base view] をクリックします。

  3. 新しい基本ビューの名前を RevenueSum に変更します。

  4. 入力属性の名前を clients 、返す属性の名前を totalrevenue にそれぞれ変更します。

  5. clients 属性は、 getsumrevenuebyids_in0 型のレジスターの配列です。Virtual DataPort では、ビューを作成する際にこの型が自動的に生成されます。

  6. この配列の各エレメントは getsumrevenuebyids_in0_string 型の register であり、これも Virtual DataPort によって自動的に生成されます。

  7. clients 配列のエレメントの名前を taxId に変更します。

  8. [Save] (image2) をクリックして、基本ビューを作成します。

ここで、基本ビュー RevenueSum から選択ビューを作成して、taxid が B78596011 および B78596012 の顧客の収益合計を取得します。選択条件の定数オペランドは、Compound Values Editor を使用して作成します。

以下の手順に従います。

  1. [Server Explorer] で RevenueSum ビューを右クリックし、[New] メニューで [Selection] をクリックします。 RevenueSum ビューが [Model] タブに追加されます。

  2. [Where Conditions] タブをクリックします。

  3. image4 をクリックして、条件を追加します。

  4. CLIENTS フィールドと = 演算子を選択します。

  5. image1 をクリックして Compound Values Editor を開きます。

  6. [Value] の横にある image5 を 2 回クリックして、 getSumRevenuebyTaxIds_in0_string 型のエレメントを 2 つ作成します。

  7. これらの新しいエレメントをクリックして展開し、配列の各エレメントの値を編集します。

  8. 1 番目のレジスターの taxId フィールドに値 B78596011 を入力し、2 番目のレジスターの同じフィールドに値 B78596012 を入力します (以下 の図を参照)。

  9. [Ok] をクリックしてエディターを閉じます。

  10. [Save] (image2) をクリックして選択ビューを作成します。

Creating a value of type getSumRevenuebyTaxIds_IN0

getSumRevenuebyTaxIds_IN0 型の値の作成

派生ビューのパラメーター

ビューには、通常の列だけではなく、 ビューパラメーター も設定できます。WHERE 条件を使用するビューを構築する際に、固定のフィルター値をビュー定義で指定するのではなく、クエリに応じてフィルター値を変更できるようにする場合に ビューパラメーター が効果的です。

たとえば、以下の 2 つのビューがあるとします。

  1. client ビュー。 nameincome 、 および state の 3 つのフィールドがあります。

  2. wealthy_client_by_state ビュー。次のように定義されています。

    CREATE VIEW wealthy_client_by_state AS
    SELECT state, COUNT(*)
    FROM client
    WHERE income > 1000000
    GROUP BY state
    

2 番目のビューには制限があります。つまり、顧客を裕福であると見なす所得制限値が静的になっています。したがって、ビューを作成する前にこの制限のことを知っている必要があります。実行時にこの制限を変更する場合、 WHERE 条件を削除して income フィールドを GROUP BY フィールドに追加する方法があります。しかし、その場合は、income フィールドを GROUP BY 句に追加する必要がありますが、それは得策ではありません。

この問題を回避するために、「ビューパラメーター」をビューに追加する方法があります。ビューにビューパラメーターを追加すると、そのパラメーターは出力スキーマの一部となり、通常のフィールドを使用できる場所で使用できるようになります。

ビューパラメーターを使用すると、実行時に所得制限値を指定できるように、前述の例を変更できます。そのためには、ビューを編集して以下の手順に従います。

  1. [Model] タブをクリックして、[View parameters] の横にある image1 をクリックします。表示されたダイアログで [Add new parameter] をクリックして、新しいビューパラメーターを追加します。

    パラメーターの名前を wealthy_client_income_limit に設定し、型として long を選択して、デフォルト値を 1000000 に設定します。

  2. [Where Conditions] をクリックして、条件 income > wealthy_client_income_limit を設定します。

Adding a view parameter

ビューパラメーターの追加

ビューに新しい出力フィールド wealthy_client_income_limit が追加されます。このフィールドを使用して、実行時に条件を変更できます。以下に例を示します。

SELECT *
FROM wealthy_client_by_state
WHERE wealthy_client_income_limit = 250000

この場合、所得が 250,000 を超える顧客が選択されます。

このパラメーターにはデフォルト値があります。たとえば以下のステートメントを実行したとします。

SELECT *
FROM wealthy_client_by_state

この場合、所得が 1,000,000 を超える顧客が選択されます。

ビューパラメーターに関する考慮事項

ビューパラメーターは、特別なフィールド型であり、通常とは異なる動作をするため、以下の点を考慮してください。

  • ビューパラメーターに値を割り当てるには、以下の 2 つの方法があります。

    1. クエリの WHERE 条件で、 = 演算子を使用します。

      ...
      WHERE <view parameter> = <value>
      ...
      

      <value> は以下のいずれかです。

      • リテラル。

      • リテラルのみを使用する式。その他の列は使用しません。以下に例を示します。

        ...
        WHERE <view parameter> = ADDYEAR( LOCALTIMESTAMP, -10)
        
      • 別のビューパラメーター。

    2. クエリの JOIN 条件でも、 = 演算子を使用します。その他の演算子は使用しません。この場合、他のビューの列の名前を使用できます。以下に例を示します。

      ...
      <view A> INNER JOIN <view B> ON <view A>.<column 1> = <view B>.<view parameter>
      ...
      
  • ビューパラメーターにデフォルト値を指定していない場合、クエリでパラメーターに値を割り当てる必要があります。そうでない場合、実行エンジンがクエリを拒否します。

  • = 以外の演算子を使用して、ビューパラメーターと別の値を比較する条件がクエリに設定されている場合、このクエリはこの条件を使用してパラメーターに値を割り当てるのではなく、結果をフィルターします。たとえば、クエリに次の条件が設定されている場合です。

    ...
    WHERE wealthy_client_income_limit > 100000
    

    この条件には > 演算子が使用されているため、ビューパラメーター「wealthy_client_income_limit」に値を割り当てていません。この場合、このビューパラメーターにデフォルト値が設定されていなければ、クエリは失敗します。デフォルト値が設定されていれば、クエリはこのパラメーターのデフォルト値を使用して、条件「wealthy_client_income_limit > 100000」を満たす行を返します。

  • クエリが NOT 演算子を使用する場合、このクエリが = 演算子を使用してもパラメーターには値が割り当てられません。以下に例を示します。

    WHERE NOT (wealthy_client_income_limit = 100000)
    

    この条件ではパラメーターに値が割り当てられていません。

  • クエリが IN 演算子を使用してパラメーターに値を割り当てる場合、その結果は UNION になります。以下に例を示します。

    FROM wealthy_client_by_state
    WHERE wealthy_client_income_limit IN (100000, 2000000)
    

    このクエリの結果は、次の結果と同じです。

    SELECT ...
    FROM wealthy_client_by_state
    WHERE wealthy_client_income_limit = 100000
    
    UNION
    
    SELECT ...
    FROM wealthy_client_by_state
    WHERE wealthy_client_income_limit = 2000000
    
  • サブクエリの結果をビューパラメーターに割り当てないでください。この割り当ては、値の割り当てとはみなされないからです。たとえば、次のクエリを実行したとします。

    SELECT *
    FROM wealthy_client_by_state
    WHERE wealthy_client_income_limit = (
        SELECT MAX(income_limit) FROM state_limit WHERE state = 'CA'
    )
    

    このサブクエリの結果は、「=」演算子を使用しても、パラメーター wealthy_client_income_limit には割り当てられません。この条件は結果のフィルターに使用されますが、パラメーターにデフォルト値が設定されていない場合、実行エンジンはクエリを拒否します。

ビューパラメーターを使用する必要がある状況

主に以下の 4 つの状況でビューパラメーターが効果的です。

  • フィルター条件の強制適用

  • Group By の迂回

  • Outer Join の迂回

  • 分割和結合

フィルター条件の強制適用

何らかのフィルターを指定して必ずビューにクエリが実行されるようにするパラメーターを追加できます。たとえば、ファクトテーブルに大量のデータが存在する場合、ビューパラメーターを使用して、必ず一定の日付範囲 (start_date, end_date) を指定したクエリが実行されるようにすることができます。

Group By の迂回

集約ビューを定義する場合、Group By の対象になっていないフィールドに対するフィルター条件を指定して、ビューにクエリを実行する状況が考えられます。前述の例は、Group By を迂回するシナリオです。

Outer Join の迂回

Group By の迂回と同様に、外部側から取得した行にはフィルターを適用せずに、内部側にフィルターを適用する外部結合を定義する状況が考えられます。

たとえば、小売企業がその取り扱い製品すべてのリストを取得し、そのうち顧客から返品された製品については返品理由が得られるようにするとします。この場合、以下のような items_return_details ビューを作成できます。

CREATE VIEW items_return_details AS
SELECT p.prod_name, p.prod_category, r.reason_cat, r.comments
FROM item p LEFT JOIN returns r ON(p.product_id = r.product_id)

今度は、すべての製品について同じ情報を取得しますが、過去 12 か月以内に返品された製品についてのみ返品情報が得られるようにする場合を考えます。以下のクエリを使用するとします。

SELECT prod_name, prod_category, reason_cat, comments
FROM items_return_details
WHERE return_date >= addmonth(CURRENT_TIMESTAMP, -12)

この場合に得られるのは、すべての製品のリストではなく、過去 12 か月以内に返品された製品のみのリストです。その理由は、SQL では JOIN 句の後で WHERE 句が適用されることにあります。したがって、返品情報が存在する製品をすべて取得した後、過去 12 か月以内に返品されていない製品がすべて除外されます。しかも、その除外対象にはまったく返品されていない製品もすべて含まれています。

items_return_details ビューを編集して [WHERE conditions] タブで条件を追加した場合も同じ問題が発生します。そこで指定した条件は、結合する前ではなく、結合した後で適用されるからです。

想定どおりのクエリを実行するには、SQL を以下のようにする必要があります。

SELECT p.prod_name, p.prod_category, r.reason_cat, r.comments
FROM PRODUCT p LEFT JOIN
(SELECT * from returns WHERE return_date >= addmonth(CURRENT_TIMESTAMP, -12)) r
ON(p.product_id = r.product_id)

ただし、items_return_details のような事前定義のビューを使用しながら、外部結合の内部側にフィルターを適用できるようにする場合は以下の手順に従います。

  • フィルターを適用するビューから補助ビューを作成します。この例では、returns から選択ビューとして recent_returns を作成します。

  • この補助ビューで、値をフィルター条件に渡す新しいビューパラメーターを定義します。この例では、ビューパラメーターとして num_months を作成します。

  • 新しいビューパラメーターを使用した WHERE 条件を追加します。この例では、 return_date >= addmonth(now(), -1 * num_months) を追加します。

  • 外部結合を使用したビューを編集して、代わりに補助ビューを使用するように変更します。

num_months にはデフォルト値を指定していないので、このパラメーター値の指定は必須になります。したがって、すべてのクエリでは、データの取得期間とする月数を指定する必要があります。以下に例を示します。

SELECT prod_name, prod_category, reason_cat, comments
FROM items_return_details
WHERE num_months = 12

ここでは、結合の後に WHERE 条件を設定していますが、num_months は通常のフィールドではなく、ビューパラメーターなので、 num_months = 12 はパラメーターの代入と見なされ、左結合から得られた行に対してフィルターとして機能しません。データ取得期間の月数指定を必須とせず、オプションにする場合は、recent_returns ビューを以下のように編集します。

  • ビューパラメーターを編集して、新しいデフォルト値 0 を追加します

  • 条件を return_date >= addmonth(CURRENT_TIMESTAMP, -1 * num_months) OR num_months = 0 に変更します

分割和結合

分割和結合 を構築する場合、和結合の各分岐に分割基準を指定する条件が必要です。ただし、この基準の定義に使用できる列がテーブルに存在しないことがあり、その場合は代わりにビューパラメーターを使用します。たとえば、世界中で製品を販売している企業の売上情報が、以下の 3 つのシステムに分割されているとします。

  • EMEA (欧州、中東、アフリカ) の情報を保存するシステム

  • アメリカの情報を保存するシステム

  • APAC (アジア太平洋) の情報を保存するシステム

この場合の分割は地域別ですが、sales には地域を指定する列が存在しません。分割和結合を実現するために、ビューパラメーターを使用して中間選択を作成する方法があります。たとえば、APAC 地域について以下の操作が可能です。

  1. APAC 分割の sales テーブルから選択ビューを作成する

  2. この選択ビューに新しいビューパラメーター region を作成して、そのデフォルト値を APAC にする

  3. この新しいビューに WHERE 条件として region = ‘APAC’ を追加する

他の分割から作成するビューでも同様の処理が必要です。その場合、値「APAC」を、各分割に該当する地域を表す値に置き換えます。