:その他の関数

cast(expr as <type>)

coalesce(expr1, expr2, ...)

decode(expression, search, result[, search, result]...[, default])

• expression: 比較する式。
• search: expression と比較する検索文字列です。
• result: search と expression の値が一致したときに戻す値です。
• default: 任意のパラメーターです。 すべての検索項目が式と一致しない場合は、このデフォルト値が返されます。 指定しない場合は、NULL が返されます。

• 一致した検索レコードが返されます。
• 一致するレコードがない場合は、デフォルト値が返されます。
• デフォルト値が指定されていない場合は、 NULL が返されます。
  注

  • 少なくとも 3 つのパラメーターを指定しなければなりません。
  • 結果のデータ型はすべて同じか NULL でなければなりません。 データ型が一致しない場合は、例外が発生します。 すべての検索と式のデータ型は同じでなければなりません。そうでなければ例外が報告されます。
  • デコード中のオプション検索で、レコードが繰り返し一致した場合は、最初の値が返されます。

Select
decode(customer_id,
1, 'Taobao',
2, 'Alipay',
3, 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;

if customer_id = 1 then
result := 'Taobao';
elsif customer_id = 2 then
result := 'Alipay';
elsif customer_id = 3 then
result := 'Aliyun';
...
else
result := 'Others';
end if;

get_idcard_age(idcardno)

get_idcard_birthday(idcardno)

get_idcard_sex(idcardno)

greatest(var1, var2, ...)

ordinal(bigint nth, var1, var2, ...)

• nth: Bigint型で、値を返す位置を指定します。 NULL の場合は NULL が返されます。
• var1/var2: Bigint 型、Double 型、Decimal 型、Datetime 型、String 型のいずれかです。

• n 番目のビット値です。 暗黙の型変換が必要ない場合、戻り値のデータ型は入力パラメータのデータ型と同じになります。
• 暗黙の型変換が必要な入力パラメータ値がある場合、
  • Double 型、Bigint 型、String 型の場合は、Double 型に変換されます。
  • String 型と Datetime 型の場合は、Datetime 型に変換されます。
  • それ以外の暗黙の変換はできません。
• NULL は最小値です。

ordinal(3, 1, 3, 2, 5, 2, 4, 6) = 2

least(var1, var2, ...)

• 入力パラメータの最小値が返されます。暗黙の型変換が必要ない場合、戻されるデータ型は入力パラメータのデータ型と同じになります。
• 暗黙の型変換が必要な入力パラメータ値がある場合
  • Double 型、Bigint 型、String 型の場合は、Double 型に変換されます。
  • String 型と Datetime 型の場合は、Datetime 型に変換されます。
  • Decimal 型が Double 型、Bigint 型、String 型と比較されるときに、Decimal 型に変換されます。
  • それ以外の暗黙の変換はできません。

• NULL は最小値です。

max_pt(table_full_name)

pt =‘20120901’
pt =‘20120902’

select * from tbl where pt=max_pt('myproject.tbl');

string uuid()

boolean sample(x, y, column_name)

• x、y: Bigint 型。x 部分へのハッシュを示し、y 番目の部分が読み取られます。 y は省略できます。
  • y を省略すると、最初の部分が読み取られます。 パラメーター内で y を省略すると、column_name も同時に無視されます。
  • x と y は Bigint 型の定数で、0 より大きい値です。 それ以外のデータ型の場合、または 0 以下の場合は、例外が発生します。 y&gt, x の場合も、例外が発生します。 x と y のいずれかの入力値が NULL の場合、NULL が返されます。
• column_name: サンプリング対象の列です。
  • column_name は省略することができます。その場合、x と y の値に従って、ランダムにサンプルが取得されます。
  • 任意のデータ型にすることができ、列値は NULL にすることができます。 暗黙的な型変換は必要ありません。
  • column_nameが NULL の場合、例外が報告されます。

select * from tbla where sample (4, 1 , cola) = true;
-- The values are carried out Hash into 4 portions and take the first portion.
select * from tbla where sample (4, 2) = true;
-- The values do random Hash into 4 portions for each row of data and take the second portion.

case
when (_condition1) then result1
when (_condition2) then result2
...
else resultn
end
case
when (_condition1) then result1
when (_condition2) then result2
when (_condition3) then result3
...
else resultn
end

select
case
when shop_name is null then 'default_region'
when shop_name like 'hang%' then 'zj_region'
end as region
From sale_detail;

if(testCondition, valueTrue, valueFalseOrNull)

• testCondition: 真偽を判定するための式です。 Boolean 型です。
• valueTrue: 式 testCondition が真の時に返される値です。
• valueFalseOrNull: 式 testCondition が真ではない場合、および NULL の可能性がある場合に返される値です。

select if(1=2,100,200) from dual; 
--Returned results:
+ ------------ +
| _c0 |
+------------+
| 200 |
+------------+

split(str, pat)

• str: String 型。分割する文字列を指定します。
• pat: String 型。区切り文字を指定します。正規表現を使用できます。

select split("a,b,c",",") from dual;
Results:
+------+
| _c0 |
+------+
| [a, b, c] |
+------+

explode(var)

• var が Array 型の場合、列に格納されている配列が複数行に変換されます。
• var が Map 型の場合、列に格納されているマップの各キー値のペアは､2 つの列から成る行に変換されます。1 列はキー用、もう 1 列は値用です。

explode(array(null, 'a', 'b', 'c')) col

MAP map(K key1, V value1, K key2, V value2, ...)

• 暗黙的な変換後も含め、すべてのキーのデータ型は一致しており、基本のデータ型でなければなりません。
• 暗黙の型変換後の値も含めて、すべての値のデータ型は一致している必要がありますが、どの型でも構いません。

+------------+----+----+------------+------------+
| c1         | c2 | c3 | c4         | c5         |
+------------+----+----+------------+------------+
| 1000       | k11 | k21 | 86         | 15         |
| 1001       | k12 | k22 | 97         | 2          |
| 1002       | k13 | k23 | 99         | 1          |
+------------+----+----+------------+------------+

select  map(c2,c4,c3,c5) from t_table;

ARRAY map_keys(map<K, V>)

ARRAY map_values(map<K, V>)

select map_values(map('a',123,'b',456));
結果:
[123, 456]

ARRAY array(value1,value2, ...)

select array(c2,c4,c3,c5) from t_table;

+------+
| _c0 |
+------+
| [k11, 86, k21, 15] |
| [k12, 97, k22, 2] |
| [k13, 99, k23, 1] |
+ ---- +

INT size(map)
INT size(array)

• size(map<K，V>) は、特定のマップのキー値ペア―の数を返します。
• size(array<T>) は、特定の配列の要素数を返します。

• map<K, V>: Map 型のデータです。
• array<T>: Array 型のデータです。

select size(map('a',123,'b',456)) from dual;--Returns 2
select size(map('a',123,'b',456,'c',789)) from dual;--Returns 3
select size(array('a','b')) from dual;--Returns 2
select size(array(123,456,789)) from dual;--Returns 3

boolean array_contains(ARRAY<T> a,value v)

• a: Array 型のデータです。
• v: 指定された v は、配列内のデータと同じデータ型ータ型でなければなりません。

+ ------------ + --------- +
| c1         | t_array |
+------------+---------+
| 1000       | [k11, 86, k21, 15] |
| 1001       | [k12, 97, k22, 2] |
| 1002       | [k13, 99, k23, 1] |
+------------+---------+

ARRAY sort_array(ARRAY<T>)

select sort_array(array('a','c','f','b')),sort_array(array(4,5,7,2,5,8)),sort_array(array('You','Me','He')) from dual;
結果:
[a, b, c, f] [2, 4, 5, 5, 7, 8] [He, You, Me]

posexplode(ARRAY<T>)

select posexplode(array('a','c','f','b')) from dual;
結果:
+------------+-----+
| pos | val |
+------------+-----+
| 0 | a |
| 1 | c |
| 2 | f |
| 3 | b |
+------------+-----+

STRUCT struct(value1,value2, ...)

select struct('a',123,'ture',56.90) from dual;
結果:
{col1:a, col2:123, col3:ture, col4:56.9}

STRUCT named_struct(string name1, T1 value1, string name2, T2 value2, ...)

• value: 各値は任意のデータ型にできます。
• name: String 型のフィールド名を指定します。

select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50) from dual;
結果:
{user_id:10001, user_name:LiLei, married:F, weight:63.5}

inline(array<struct<f1:T1, f2:T2, ... >>)

+----------+
| T_struct |
+----------+
{user_id:10001, user_name:LiLei, married:F, weight:63.5}
{user_id:10001, user_name:LiLei, married:F, weight:63.5}
+----------+

trans_array (num_keys, separator, key1,key2,…,col1, col2,col3) as (key1,key2,…,col1, col2)

• num_keys: Bigint 型の定数。0 以上でなければなりません。 複数行に変換するときに､キーを転置するための列番号として使用されます。
• Key: 1つの行を複数行に変換するときは、複数行にある列を複製します。
• separator: String 型の定数です。 文字列を複数の要素に分割するために使用される区切り文字です。 NULL の場合は例外が発生します。
• keys: 転置するときのキーの列として、 num_keys で指定します。 num_keys がすべての列をキーとして指定されている場合 (つまり、num_keys がすべての列の数に等しい場合)、1 行のみが返されます。
• cols: 行に変換するための配列です。 キーの後のすべての列は転置される配列と見なされます。 String 型です。 格納されているコンテンツは、「Hangzhou; Beijing; shanghai」といった String 形式の配列になります。配列は、「;」で区切られます。

+----------+----------+------------+
| login_id | login_ip | login_time |
+----------+----------+------------+
wangwangA 192.168.0.1,192.168.0.2 20120101010000,20120102010000
| Wangwangb | 192.168.25.10, 192.168.67.22, 192,168.6. 3 | maid, 20120223080000 |
+----------+----------+------------+

trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time)

+----------+----------+------------+
| Login_id | login_ip | login_time |
+----------+----------+------------+
| wangwangB | 192.168.45.10 | 20120111010000 |
| wangwangB | 192.168.67.22 | 20120112010000 |
| wangwangB | 192.168.6.3 | 20120223080000 |
| wangwangA | 192.168.0.1 | 20120101010000 |
| wangwangA | 192.168.0.2 | 20120102010000 |
+----------+----------+------------+

Login_id LOGIN_IP LOGIN_TIME 
wangwangA 192.168.0.1,192.168.0.2 20120101010000

Login_id Login_ip Login_time 
wangwangA 192.168.0.1 20120101010000
wangwangA 192.168.0.2 NULL