COALESCE¶

説明

COALESCE 関数は、NULL でない最初の引数を返します。 COALESCE は、次の式と同等です。

CASE WHEN arg1 IS NOT NULL THEN arg1
     WHEN arg2 IS NOT NULL THEN arg2
     …
END

構文

COALESCE( <field name:identifier>, <field name:identifier> [, <field name:identifier> ]*)

• field name: NULL の可能性があるテキストまたはフィールド名。

例

V というビューについて考えてみます。

例 1

SELECT COALESCE(b, 'hello')
FROM V

例 2

SELECT coalesce('hello', 'bye')
FROM v

例 3

SELECT COALESCE(b, a)
FROM V

CONTEXTUALSUMMARY¶

説明

CONTEXTUALSUMMARY 関数は、テキストについて、指定された語または文を含む、関連性のあるテキストフラグメントを返します。

構文

CONTEXTUALSUMMARY( <content:text>, <keyword:text>, [ <begin delim:text>,
    <end delim:text>, <fragment separator:text>, <fragment length:int>
    [, <max fragments number:int> [, <analyzer:text> ] ] ] )

• content: 必須。最も関連性の高いフラグメントの抽出元のテキスト。

• keyword: 必須。テキストフラグメントを抽出するために使用されるキーワード。この引数には 1 語または 1 文を内容として含めることができます。

• begin delim: オプション。テキスト内に出現した場合に常にキーワードの接頭辞として追加するテキスト。デフォルト値は「」です。

• end delim: オプション。テキスト内に出現した場合に常にキーワードの接尾辞として追加するテキスト。デフォルト値は「」です。

• fragment separator: オプション。結果の各テキストフラグメントを区切るためのテキスト。デフォルト値は「...」です。

• fragment length: オプション。テキスト内のキーワード出現箇所の前後に現われる文字のおおよその数。デフォルト値は 5 です。

• max fragment number: オプション。取得するフラグメントの最大数。

• analyzer: オプション。キーワードの検索に使用するアナライザー。デフォルトでは、標準アナライザー (std) が使用されます。このアナライザーは、見出し語化やストップワードを考慮しません。Virtual DataPort には、英語用 (en) とスペイン語用 (es) のアナライザーが同梱されています。

例

例 1

SELECT CONTEXTUALSUMMARY(content, 'Denodo', '<b>', '</b>', ' … ', 5, 1)
FROM demo_arn_view;

このクエリは、「Denodo」という語が出現するテキスト content のフラグメントを返します。

例 2

次のビュー text_summary_sample について考えてみます。

SELECT contextualsummary(text_sample, 'system')
FROM text_summary_sample

例 3

SELECT contextualsummary(text_sample, 'service')
FROM text_summary_sample

例 4

SELECT contextualsummary(text_sample, 'web', '\', '/', '--', 25)
FROM text_summary_sample

GETVAR¶

GETVAR 関数は、この関数を使用するクエリのコンテキストから変数の値を取得します。変数が存在しない場合、デフォルト値を返します。

この関数の使用方法については、「 選択条件への変数の追加 (GETVAR および SETVAR) 」を参照してください。

構文

GETVAR('<name of the variable:literal>', '<type of the variable:literal>', '<default value:literal>')

GETSESSION¶

説明

GETSESSION 関数は、Virtual DataPort サーバーとの間で確立されたセッションの情報を提供します。

構文

GETSESSION( <parameter : literal> )

• parameter には以下の いずれか を指定できます (この関数によって返される値は、このパラメータに応じて異なります)。

  1. user: 現在のユーザーのユーザー名を返します。

  2. useragent: 現在のコネクションに関連付けられたユーザーエージェントを返します。

  3. database: クライアントの接続先 Virtual DataPort データベースの名前を返します。

  4. i18n: クライアントの接続先データベースのロケール構成の名前を返します。

  5. roles: 現在のユーザーに割り当てられている有効なロールの名前が含まれる配列を返します。有効なロールとは、ユーザーに直接的および間接的に割り当てられているロールです。たとえば、「user1」にロール「A」が割り当てられていて、ロール「A」がロール「B」に割り当てられている場合、この関数は「A」および「B」を返します。

例

SELECT GETSESSION('user') || '@' || GETSESSION('database') as user_name
FROM Dual();

HASH¶

説明

HASH 関数は、MD5 アルゴリズムを使用して入力値のダイジェストを計算し、それを base64 (base 16 ではありません) でエンコードして返します。

この関数は、同じ入力に対しては常に同じ値を返します。

入力パラメータが NULL の場合は、NULL を返します。

構文

HASH( <value:text> ):text

• value: 必須。フィールドまたはリテラルの名前。

例

SELECT hash('text value') as hash_value, hash(null) as null_value
FROM Dual()

MAP¶

説明

MAP 関数は、キーに関連付けられた値を返します。キー/値のペアはビューまたはマップから取得できます (「 マップの定義 」を参照)。キーが存在しない場合、この関数は NULL を返します。

使用可能なシグネチャは 2 つあります。

構文 1

MAP ( <key:text>, <view name:text>, <key field:text>,
    <value field:text> )

キーに関連付けられた値を取得します。MAP はビューの列でキーの値を検索します。

• key: 必須。ビューで検索する値。

• view name: 必須。キーとその値を含むビューの名前。

• key field: 必須。キーを含むビューの列。

• value field: 必須。値を含むビューの列。

構文 2

MAP ( <key:text>, <map name:text> [, <i18n:text> ] )

マップからキーに関連付けられた値を取得します。

• key: 必須。マップで検索する値。

• map name: 必須。キーとその値を含むマップの名前。

• i18n: オプション。コンテンツのロケール構成。

例

例 1

food というマップについて考えてみます。

CREATE MAP SIMPLE food (
    'breakfast' = 'milk'
    'dinner' = 'lettuce'
    'lunch' = 'meat'
)

SELECT MAP('breakfast', 'food') AS breakfast
    , MAP('lunch', 'food') AS lunch
    , MAP('dinner', 'food') AS dinner
    , MAP('none', 'food') AS none
FROM Dual()

例 2

各国の会社の収益がその国の通貨で含まれる FOREIGN_SALES というビューについて考えてみます。

さらに、各通貨とドルの為替レートを含む CURRENCY_RATES_TO_USD というマップについて考えてみます。

CREATE MAP SIMPLE currency_rates_to_usd (
    'CAD' = '0.957121'
    'EUR' = '1.4971'
    'GBP' = '1.67'
    'JPY' = '0.011166'
    'MXN' = '0.076989'
    'USD' = '1.0'
);

SELECT month
    , country
    , CAST('float', MAP(CURRENCY, 'currency_rates_to_usd')) * revenue AS revenue_usd
FROM foreign_sales

NULLIF¶

説明

NULLIF 関数は、2 つの値または式を比較して、2 つが等しければ NULL を返し、そうでなければ最初の値を返します。

この関数は、次のステートメントと同等です。

CASE WHEN <expression> = <expression>
    THEN NULL
    ELSE <expression>
END

NULLIF は暗黙的な型変換を実行します。つまり、2 つのパラメータが異なる型の場合、比較のために一方の型変換を試みます

。

例: 最初のパラメータが 1 (text)、2 番目のパラメータが 1 (integer) の場合、 text パラメータを整数に変換します。これらは型が異なっていても等しいとみなされます。

構文

NULLIF(<expression>, <expression>)

例

internet_inc というビューについて考えてみます。

例 1

SELECT NULLIF(id, 1) AS display
FROM internet_inc

例 2

SELECT *
FROM internet_inc
WHERE NULLIF(ID, 1) <> NULL

このビューの最初の行は条件に一致しません。

例 3

SELECT COALESCE(NULLIF(ID, '1'), summary) AS display
FROM internet_inc

PIVOTREGISTER¶

説明

PIVOTREGISTER 関数は、指定された配列の値を、指定された名前の列に変換します。

使用可能なシグネチャは 2 つあります。

構文 1

PIVOTREGISTER ( <values:array>, <column names:array>)

これは、指定された配列の値を列に変換します。結果の列は常に text 型になります。

• values: 必須。列に変換する値。

• column names: 必須。新しい列の名前。

構文 2

PIVOTREGISTER ( <values:array>, <column names:text>)

これは、指定された配列の値を列に変換します。

• values: 必須。列に変換する値。

• column names: 必須。新しい列の名前。これは、列名と、カンマ区切りの型 (オプション) を含むテキストです。構文は : field1:type1 [,field2:type2, ...] にしてください。型は string、double、float、integer、long、boolean、date、time、timestamp、および timestamptz のいずれかにします。

例

テーブル phone_array_table について見てみましょう。

配列の例:

2 つの phone_numbers 行を 2 つの列``home_phone_number`` および office_phone_number に変換するとします。これを行うには、 PIVOTREGISTER 関数を phone_numbers 列に適用し、プロパティ home_phone_number と office_phone_number 、および phone_numbers 列の値を含むレコードを返します。

CREATE OR REPLACE VIEW phone_table_aux AS
    SELECT name,
           PIVOTREGISTER(phone_numbers, { ROW ('home_phone_number'), ROW ('office_phone_number') })
    FROM phone_array_table;

または

CREATE OR REPLACE VIEW phone_table_aux AS
    SELECT name,
           pivotregister(phone_numbers, 'home_phone_number:string,office_phone_number:string')
    FROM phone_array_table;

最後に、 phone_table_aux に派生ビューを作成し、ビューの PIVOTREGISTER レコードのフィールドを投影すると、ピボット結果が得られます。

この関数を使用して、Google Sheets や Google Analytics などのソースから作成された JSON データソースをテーブルで変換できます。この場合、JSON の最初のエレメントがテーブルのヘッダーであった可能性があります。列タイプを指定する場合、ヘッダーにはテキスト値があるため、最初のタプルは NULL になることがあります。

例:

この JSON を 6 つの列のテーブルに変換するとします。

• timestamptz 型の日付。

• boolean 型の C1。

• double 型の C2。

• time 型の C3。

• integer 型の C4。

• integer 型の C5。

{
   "range": "'Sheet 1'!A1:E36",
   "majorDimension": "ROWS",
   "values": [
            [
         "Date",
         "c1",
         "c2",
         "c3",
         "c4",
         "c5"
      ],
      [
         "2020-10-10T01:59:59+01:00",
         "true",
         "43.58",
         "21:15:45",
         "421",
         "23"
      ],
      [
         "2020-10-11T01:59:59+01:00",
         "true",
         "43.79",
         "21:15:45"
      ],
      [
         "2020-10-12T01:59:59+01:00",
         "false",
         "44.74",
         "21:15:45"
      ],
      [
         "2020-10-13T01:59:59+01:00",
         "true",
         "45.10",
         "21:15:45"
      ],
      [
         "2020-10-14T01:59:59+01:00",
         "false",
         "46.01",
         "21:15:45",
         "486",
         "23"
      ]
   ]
}

この JSON を使用してデータソースを作成した後、基本ビュー bv_pivot_json を作成します。次に、派生ビュー dv_pivot_json を作成して values 配列をフラット化します。

配列の値を目的の型の列で変換するために、 PIVOTREGISTER 関数を適用します。

CREATE OR REPLACE view pivot_json_aux AS
   SELECT pivotregister(values, 'Date:timestampz, C1:boolean, C2:double, C3:time,
   C4:long, C5:integer')
FROM dv_pivot_json

NULL 行が発生しないように、テーブルの行に番号を付けることができます。これを行うには、レコードのサブフィールドが投影されるビューに、新しい列 rownum を追加する必要があります。この新しい列が rownum() 関数の呼び出しになります。

これで、最初の行を削除する派生ビューを作成するだけです。作成するには、以下の WHERE 条件を追加する必要があります。

rownum <> 1

派生ビューの [Output] タブにある rownum フィールドを忘れずに削除してください。結果ビューは以下のようになります。

ROWNUM¶

説明

ROWNUM 関数は、クエリの結果の各行に対して一意の数値を返します。

この数値は、データが異なるソースから取得されていても、各クエリで一意です。

ORDER BY 句は、 ROWNUM が各行に値を割り当てた後に処理されます。

1 つのクエリを何度実行しても一貫した結果が得られるようにするには、ビューに対して ROWNUM() を ORDER BY 句とともに使用します。

構文

ROWNUM( [ <offset:long > ] ):long

• offset: オプション。指定されている場合、 ROWNUM によって返される最初の値は 1 ではなく、(offset + 1) です。

例

internet_inc というビューについて考えてみます。

例 1

SELECT rownum(10), summary, taxid
FROM internet_inc

例 2

SELECT ROWNUM(), summary, taxid
FROM internet_inc
ORDER BY summary

「例 2」のクエリでは、結果をフィールド summary で並べ替えている点に注意してください。 ORDER BY 句は ROWNUM 関数が各行に値を割り当てた後に処理されるため、 ROWNUM の最初の値は 1 にはなりません。