USER MANUALS

その他の関数

その他の関数:

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 というビューについて考えてみます。

a

b

c

10.10

I am some text

21-ene-2005 0h 0m 0s

-80.10

Text is $% needed always

12-mar-2005 12h 30m 0s

20.50

Text for a living

01-feb-2006 16h 45m 0s

40.05

NULL

NULL

例 1

SELECT COALESCE(b, 'hello')
FROM V

coalesce

I am some text

Text is $% needed always

Text for a living

hello

例 2

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

coalesce

hello

hello

hello

hello

例 3

SELECT COALESCE(b, a)
FROM V

coalesce

I am some text

Text is $% needed always

Text for a living

40.05

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 について考えてみます。

text_sample

A web service (also webservice) is defined by the W3C as a software system designed to support interoperable machine-to-machine interaction over a network. It has an interface described in a machine-processable format (specifically Web Services Description Language WSDL). Other systems interact with the web service in a manner

prescribed by its description using SOAP messages, typically conveyed using HTTP with an XML serialization in conjunction with other web-related standards. Web services are frequently just Internet Application Programming Interfaces (API) that can be accessed over a network, such as the Internet, and executed on a remote system hosting the requested services. Other approaches with nearly the same functionality

as web services are Object Management Group’s (OMG) Common Object Request

Broker Architecture (CORBA), Microsoft’s Distributed Component Object Model

(DCOM) or Sun Microsystems’s Java/Remote Method Invocation (RMI).

SELECT contextualsummary(text_sample, 'system')
FROM text_summary_sample

contextualsummary

system

system

例 3

SELECT contextualsummary(text_sample, 'service')
FROM text_summary_sample

contextualsummary

service ... service

例 4

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

contextualsummary

A web/ service (also-- (specifically Web/ Services-- with the web/ service

with other web/-related

as web/ services

DECRYPT

DECRYPT 関数は、encrypt 関数を使用して暗号化されたメッセージをデコードします。

構文

DECRYPT( <password : text>, <input : text> )

DECRYPT( <algorithm : text>, <password : text>, <input : text> )

DECRYPT( <provider : text>, <algorithm : text>, <password : text>, <input : text> )
  • DECRYPT( password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードを使用してテキストを復号化します。使用される暗号化アルゴリズムは PBEWithMD5AndDES です。

  • DECRYPT( algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを復号化します。暗号化アルゴリズムが Denodo Platform JRE のデフォルトの JCE でサポートされている必要があります。

  • DECRYPT( provider, algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを復号化します。この関数は、最初の引数で指定されたプロバイダーが提供する実装を使用します。暗号化アルゴリズムがプロバイダーでサポートされている必要があります。また、追加のプロバイダーを Denodo Platform JRE の一部として登録する必要があります。

SELECT DECRYPT('mypassword', 'OC7ignZQLsLb5/cZ1C2zqiP7c0+n3n/7r1MOn61rXvE=') as input
FROM Dual();

input

to be or not to be

DECRYPT_FIXED

DECRYPT_FIXED 関数は、encrypt_fixed 関数を使用して暗号化されたメッセージをデコードします。

構文

DECRYPT_FIXED( <iv : text>, <salt : text>, <password : text>, <input : text> )

DECRYPT_FIXED( <provider : text>, <algorithm : text>, <password : text>, <input : text> )

DECRYPT_FIXED( <iv : text>, <salt : text>, <algorithm : text>, <password : text>, <input : text> )
  • DECRYPT_FIXED( iv, salt, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードを使用してテキストを復号化します。使用される暗号化アルゴリズムは PBEWithMD5AndDES です。

  • DECRYPT_FIXED( provider, algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを復号化します。この関数は、最初の引数で指定されたプロバイダーが提供する実装を使用します。暗号化アルゴリズムがプロバイダーでサポートされている必要があります。また、追加のプロバイダーを Denodo Platform JRE の一部として登録する必要があります。暗号化と復号化に同じ IV と salt を使用する必要があることに注意してください。

  • DECRYPT_FIXED( iv, salt, algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを復号化します。暗号化アルゴリズムが Denodo Platform JRE のデフォルトの JCE でサポートされている必要があります。暗号化と復号化に同じ IV と salt を使用する必要があることに注意してください。

SELECT DECRYPT_FIXED('!"446rth5yh}d24f','*^)·jhnf24xsa*9w','PBEWithMD5AndDES','mypassword','dMhW5OM6fXXkn22LFwJsSQ==') as input
FROM Dual();

input

TOP SECRET

ENCRYPT

ENCRYPT 関数は、ランダムな salt と初期化ベクトル (IV) を使用します。この理由から、(encypt_fixed ではなく) encrypt 関数を使用することを強くお勧めします。encrypt 関数では、初期状態で一定のランダム性を使用することで暗号化が確定的にならないように保証されるためです。

構文

ENCRYPT( <password : text>, <input : text> )

ENCRYPT( <algorithm : text>, <password : text>, <input : text> )

ENCRYPT( <provider : text>, <algorithm : text>, <password : text>, <input : text> )
  • ENCRYPT( password, input ): この関数は、テキストを入力パラメータとして取り、最初の引数として指定されたパスワードを使用してテキストを暗号化します。使用される暗号化アルゴリズムは PBEWithMD5AndDES です。

  • ENCRYPT( algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、2 つ目の引数として指定されたパスワードと、最初の引数として指定された暗号化アルゴリズムを使用してテキストを暗号化します。暗号化アルゴリズムは PBE 暗号化アルゴリズムを参照する必要があります。また、Denodo Platform JRE のデフォルトの JCE でサポートされている必要があります。

  • ENCRYPT( provider, algorithm, password, input ): This function takes a text as input parameter and encrypts the text using the password provided as third argument, the encryption algorithm provided as second argument, the function will use the implementation provided by the provider specified as first argument. The encryption algorithm has to be supported by the provider and the additional provider has to be registered as part of the Denodo Platform JRE.

SELECT ENCRYPT('mypassword', 'to be or not to be') as encrypt_value
FROM Dual();

encrypt_value

MUEt6d4kgm+iYayzs3VUaCMilEiZMZ7PiYj4L66No/A=

ENCRYPT_FIXED

ENCRYPT_FIXED 関数は、ユーザーが指定した固定 salt と初期化ベクトル (IV) を使用します。これはランダムな salt と IV を使用する encrypt 関数とは異なります。同じ入力から同じ暗号文を得る必要がある場合に、encrypt_fixed 関数を使用できます。

(encypt_fixed ではなく) encrypt 関数を使用することを強くお勧めします。encrypt 関数では、初期状態で一定のランダム性を使用することで暗号化が確定的にならないように保証されるためです。

構文

ENCRYPT_FIXED( <iv : text>, <salt : text>, <password : text>, <input : text> )

ENCRYPT_FIXED( <iv : text>, <salt : text>, <algorithm : text>, <password : text>, <input : text> )

ENCRYPT_FIXED( <iv : text>, <salt : text>, <provider : text>, <algorithm : text>, <password : text>, <input : text> )
  • ENCRYPT_FIXED( iv, salt, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードを使用してテキストを暗号化します。使用される暗号化アルゴリズムは PBEWithMD5AndDES です。

  • ENCRYPT_FIXED( iv, salt, algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを暗号化します。暗号化アルゴリズムは PBE 暗号化アルゴリズムを参照する必要があります。また、Denodo Platform JRE のデフォルトの JCE でサポートされている必要があります。アルゴリズム名は PBEWith<digest>And<encryption> という規則に従います。推奨されるアルゴリズム名は PBEWithHMACSHA512AndAES_256 です。暗号化と復号化に同じ IV と salt を使用する必要があることに注意してください。セキュリティ上の理由から、暗号化ごとに新しい IV と新しい salt を使用することをお勧めします。これらはアルゴリズムのブロックサイズの倍数である必要があります。標準的なブロックサイズは 8 バイトまたは 16 バイトです。

  • ENCRYPT_FIXED( iv, salt, provider, algorithm, password, input ): この関数は、テキストを入力パラメータとして取り、指定されたパスワードと暗号化アルゴリズムを使用してテキストを暗号化します。この関数は、指定されたプロバイダーが提供する実装を使用します。暗号化アルゴリズムがプロバイダーでサポートされている必要があります。また、追加のプロバイダーを Denodo Platform JRE の一部として登録する必要があります。暗号化と復号化に同じ IV と salt を使用する必要があることに注意してください。セキュリティ上の理由から、暗号化ごとに新しい IV と新しい salt を使用することをお勧めします。これらはアルゴリズムのブロックサイズの倍数である必要があります。標準的なブロックサイズは 8 バイトまたは 16 バイトです。

SELECT ENCRYPT_FIXED('!"446rth5yh}d24f', '*^)·jhnf24xsa*9w', 'mypassword', 'TOP SECRET') as encrypt_value
FROM Dual();

encrypt_value

dMhW5OM6fXXkn22LFwJsSQ==

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();

user_name

user1@denodo_samples_db

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()

hash_value

null_value

eNV9lLZhJ1lex1ztvwnajg==

NULL

HASH_FUNCTION

説明

HASH_FUNCTION は、N ビットの hash_algorithm メッセージダイジェストを含む 16 進エンコードされた文字列を返します。Hash_algorithm の有効な値は、md5、sha1、sha256、および sha512 です。

構文

HASH_FUNCTION( <value:text>, hash_algorithm ):text
  • value: 必須。フィールドまたはリテラルの名前。

SELECT hash_function('denodo', 'MD5') as hash_function_value
FROM Dual()

hash_function_value

A5631A7D0AD51A8564EA78F9F46B4339

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: オプション。コンテンツのロケール構成。

注釈

どちらの場合も、 key は大文字と小文字が区別されるパラメータです。

例 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()

breakfast

lunch

dinner

none

milk

meat

lettuce

NULL

例 2

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

country

month

revenue

currency

Mexico

JAN

7536.00

MXN

Spain

JAN

20000.00

EUR

United Kingdom

JAN

26816.00

GBP

Canada

FEB

-25616.00

CAD

Japan

FEB

100024.00

JPY

さらに、各通貨とドルの為替レートを含む 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

month

country

revenue_usd

JAN

Mexico

580.19

JAN

Spain

29942.00

JAN

United Kingdom

44782.72

FEB

Canada

-24517.61

FEB

Japan

1116.87

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 というビューについて考えてみます。

id

summary

ttime

taxid

1

Error in ADSL router

2005-06-29 19:19:41.0

B78596011

2

Incident in ADSL router

2005-06-29 19:19:41.0

B78596012

3

Install additional line

2005-06-29 19:19:41.0

B78596013

4

Bandwidth increase

2005-06-29 19:19:41.0

B78596014

例 1

SELECT NULLIF(id, 1) AS display
FROM internet_inc

display

NULL

2

3

4

例 2

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

id

summary

ttime

taxid

2

Incident in ADSL router

2005-06-29 19:19:41.0

B78596012

3

Install additional line

2005-06-29 19:19:41.0

B78596013

4

Bandwidth increase

2005-06-29 19:19:41.0

B78596014

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

例 3

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

display

Error in ADSL router

2

3

4

注釈

NULLIF は 2 番目のパラメータを自動的に整数に変換し、それを同じく整数である ID 列の値と比較します。

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 について見てみましょう。

name

phone_numbers

Smith

[Array] ...

Willson

[Array] ...

Stan

[Array] ...

Jonhson

[Array] ...

McNamara

[Array] ...

Richardson

[Array] ...

配列の例:

value

666-987-8549

555-125-7841

2 つの phone_numbers 行を 2 つの列``home_phone_number`` および office_phone_number に変換するとします。これを行うには、 PIVOTREGISTER 関数を phone_numbers 列に適用し、プロパティ home_phone_numberoffice_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;

name

pivotregister

Smith

[Register] ...

Willson

[Register] ...

Stan

[Register] ...

Jonhson

[Register] ...

McNamara

[Register] ...

Richardson

[Register] ...

RESULT -> pivotregister

home_phone_number

office_phone_number

666-987-8549

555-125-7841

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

name

home_phone_number

office_phone_number

Smith

666-987-8549

555-125-7841

Willson

666-907-8009

555-105-7001

Stan

666-957-8509

555-155-7501

Jonhson

666-947-8409

555-145-7401

McNamara

666-927-8209

555-125-7302

Richardson

666-937-8309

555-135-7301

この関数を使用して、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() 関数の呼び出しになります。

rownum

Date

C1

C2

C3

C4

C5

1

<null>

<null>

<null>

<null>

<null>

<null>

2

2020-10-09 17:59:-07:00

true

43.58

21:15:45

421

23

3

2020-10-10 17:59:-07:00

true

43.79

21:15:45

<null>

<null>

4

2020-10-11 17:59:-07:00

false

44.74

21:15:45

<null>

<null>

5

2020-10-12 17:59:-07:00

true

45.1

21:15:45

<null>

<null>

6

2020-10-13 17:59:-07:00

false

46.01

21:15:45

486

23

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

rownum <> 1

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

Date

C1

C2

C3

C4

C5

2020-10-09 17:59:-07:00

true

43.58

21:15:45

421

23

2020-10-10 17:59:-07:00

true

43.79

21:15:45

<null>

<null>

2020-10-11 17:59:-07:00

false

44.74

21:15:45

<null>

<null>

2020-10-12 17:59:-07:00

true

45.1

21:15:45

<null>

<null>

2020-10-13 17:59:-07:00

false

46.01

21:15:45

486

23

ROWNUM

説明

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

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

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

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

構文

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

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

id

summary

ttime

taxid

1

Error in ADSL router

2005-06-29 19:19:41.0

B78596011

2

Incident in ADSL router

2005-06-29 19:19:41.0

B78596012

3

Install additional line

2005-06-29 19:19:41.0

B78596013

4

Bandwidth increase

2005-06-29 19:19:41.0

B78596014

例 1

SELECT rownum(10), summary, taxid
FROM internet_inc

rownum

summary

taxid

11

Error in ADSL router

B78596011

12

Incident in ADSL router

B78596012

13

Install additional line

B78596013

14

Bandwidth increase

B78596014

例 2

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

rownum

summary

taxid

4

Bandwidth increase

B78596014

1

Error in ADSL router

B78596011

2

Incident in ADSL router

B78596012

3

Install additional line

B78596013

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

UNPIVOTREGISTER

説明

UNPIVOTREGISTER 関数は、指定されたレコード内で列を行に変換します。

構文

UNPIVOTREGISTER (<value:record>)
  • value: 必須。

actor_table の first_name と last_name をピボット解除するとします。

id

first_name

last_name

1

Patrick

Dempsey

2

Penelope

Cruz

3

Brad

Pitt

4

Matthew

McConaughey

5

Chris

Hemsworth

このためには、レジスターを作成してからカスタム関数 unpivotregister を適用し、キーと、column_name、register_value が含まれる値の配列を返すようにします。

CREATE OR REPLACE VIEW unpivot_actor_aux as select id,
         UNPIVOTREGISTER(register(first_name, last_name))
         FROM actor;

id

unpivotregister

1

[Array] ...

2

[Array] ...

3

[Array] ...

4

[Array] ...

5

[Array] ...

RESULT -> unpivotregister

key

value

first_name

Patrick

last_name

Dempsey

最後にビューをフラット化できます。これにより、次のようなピボット解除結果が得られます。

SELECT id, key, value
      FROM flatten unpivot_actor_aux as a (a.unpivotregister);

id

key

value

1

first_name

Patrick

1

last_name

Dempsey

2

first_name

Penelope

2

last_name

Cruz

3

first_name

Brad

3

last_name

Pitt

4

first_name

Matthew

4

last_name

McConaughey

5

first_name

Chris

5

last_name

Hemsworth

Add feedback