その他の関数¶
その他の関数:
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
には以下の いずれか を指定できます (この関数によって返される値は、このパラメータに応じて異なります)。user
: 現在のユーザーのユーザー名を返します。useragent
: 現在のコネクションに関連付けられたユーザーエージェントを返します。database
: クライアントの接続先 Virtual DataPort データベースの名前を返します。i18n
: クライアントの接続先データベースのロケール構成の名前を返します。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_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;
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 |