Other functions

Function definition:

cast(expr as <type>)

Function definition:

coalesce(expr1, expr2, ...)

Return value type is the same as parameter type.

Function definition:

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

Parameter description:

• expression: expression to be compared.
• search: A search string to be compared with the expression.
• result: the returned value when the values of search and expression match.
• default: it is optional. If all search items do not match the expression, return this default value. If it is not specified, return NULL.

Return Value:

• return matched search.
• If no matching record exists, return default.
• If default is not specified, return NULL.
  Note

  • You must specify at least three parameters.
  • All of the result types must be the same or NULL. Inconsistent data type causes an exception. All of the search and expression types must be consistent, otherwise an exception is reported.
  • If the option search in decode has repeated record and has been matched, return the first value.

Example:

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

The decode function mentioned previously implements the function in following if-then-else sentence:

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;

Function definition:

get_idcard_age(idcardno)

Function definition:

get_idcard_birthday(idcardno)

Function definition:

get_idcard_sex(idcardno)

Function definition:

greatest(var1, var2, ...)

Function definition:

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

Parameter description:

• nth: Bigint type, specify the location to return its value. If it is NULL, return NULL.
• var1/var2: Its type can be Bigint, Double, Datetime or String type.

Return Value:

• The value in nth bit. If the implicit conversion is not needed, return type is the same as input parameter type.
• If implicit conversion is in input parameters,
  • For Double, Bigint and String type, convert them to be Double type.
  • For String and Datetime type, convert them to be Datetime type.
  • Other implicit conversion is not allowed.
• NULL is the least value.

Example:

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

Function definition:

least(var1, var2, ...)

Return Value:

• The least value in input parameter; If the implicit conversion is not needed, return type is the same as input parameter type.
• If implicit conversion is in input parameters,
  • For Double, Bigint and String type, convert them to be Double type.
  • For ‘string’ type and ‘datetime’ type, convert them to be ‘datetime’ type.
  • Converts to Decimal type when Decimal type compares to Double, Bigint or String type.
  • Other implicit conversion is not allowed.

• NULL is the least value.

Function definition:

max_pt(table_full_name)

Example: Suppose that ‘tbl’ is a partitioned table, all partitions of the table are as follows, and there are data files:

pt =‘20120901’
pt =‘20120902’

In the following statement, the return value of max_pt is ‘20120902’, and the MaxCompute SQL statement reads the data in the ‘20120902’ partition.

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

Function definition:

string uuid()

Function definition:

boolean sample(x, y, column_name)

Parameter description:

• x, y: Bigint type, indicates hash to x portions, take yth portions. y can be ignored.
  • If y is ignored, take the first portion. If y in parameter is ignored, then column_name is ignored at the same time.
  • x and y are Bigint constants and greater than 0. If it is other data type or less than or equal to 0, an exception is thrown. If y&gt, x exception is also thrown. If any input of x and y is NULL, return NULL.
• column_name: the destination column to be sampled.
  • column_name can be omitted, in which case, a random sample is taken according to the values of x and y.
  • It can be any data type and the column value can be NULL. Do not need implicit type conversion.
  • If column_name is the constant NULL, an exception is reported.

Suppose that the table tbla is existent and a column cola is in this table:

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.

MaxCompute provides two kinds of case when syntax formats, as follows:

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

The following sentences is used to get the region according to different shop_name:

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

Function definition:

if(testCondition, valueTrue, valueFalseOrNull)

Parameter description:

• testCondition: The expression to be judged. Boolean type.
• valueTrue: It returns when the expression testCondition is true.
• valueFalseOrNull: It returns when the expression testCondition is not true and also can be null.

Example:

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

Function definition:

split(str, pat)

Parameter description:

• str: String type, specifies the string to be separated.
• pat: String type, specifies the delimiter, supports regular expressions.

Example:

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

Function definition:

explode(var)

Converts one row of data into a multi-row UDTF.

• If var is Array type, the array stored in the column is converted to multiple rows.
• If var is Map type, each key-value pair of the map stored in the column is converted to a row with two columns, one column for the key and one for the value.

Rows after conversion are returned.

Example:

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

Function definition:

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

key/value

• All key types are consistent, including those after implicit conversion, and must be basic.
• All value types are consistent, including those after implicit conversion, and can be of any type.

For example, the fields in t_table are(c1 bigint,c2 string,c3 string, c4 bigint ,c5 bigint), with the following data

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

Execute SQL:

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

+ ---- +
| _c0 |
+------+
| {k11:86, k21:15} |
| {k12:97, k22:2} |
| {k13:99, k23:1} |
+------+

Function definition:

ARRAY map_keys(map<K, V>)

+------------+-------+
| C1 | t_map |
+ ------------ + ------- +
| 1000       | {k11:86, k21:15} |
| 1001       | {k12:97, k22:2} |
| 1002       | {k13:99, k23:1} |
+------------+-------+

select  c1,map_keys(t_map) from t_table_map;

+------------+------+
| c1         | _c1  |
+------------+------+
| 1000       | [k11, k21] |
| 1001       | [k12, k22] |
| 1002       | [k13, k23] |
+------------+------+

Function definition:

ARRAY map_values(map<K, V>)

Example:

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

Function definition:

ARRAY array(value1,value2, ...)

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

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

Results:

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

Function definition:

INT size(map)
INT size(array)

Usage:

• size(map<K，V>) returns the number of K/V pairs in the given map.
• size(array<T>) returns the number of elements in the given array.

Parameter description:

• map<K, V>: Map-type data.
• array<T>: Array-type data.

Example:

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

Function definition:

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

Parameter description:

• a: Array-type data.
• v: The given v must be of the same type as the data in the array.

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

select c1, array_contains(t_array,'1') from t_table_array;

+------------+------+
| c1         | _c1  |
+------------+------+
| 1000       | false |
| 1001       | false |
| 1002       | true |
+------------+------+

Function definition:

ARRAY sort_array(ARRAY<T>)

Example:

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;
Results:
[a, b, c, f] [2, 4, 5, 5, 7, 8] [He, You, Me]

Select sort_array (C1), sort_array (C2), sort_array (C3) from t_array;

[a, b, c, f] [2, 4, 5, 5, 7, 8] [He, You, Me]

Function definition:

posexplode(ARRAY<T>)

Example:

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

Function definition:

STRUCT struct(value1,value2, ...)

Example:

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

Function definition:

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

Parameter description:

• value: Each value can be of any type.
• name: Specifies the name of a String-type field.

Example:

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

Command Format:

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

If the field in Table t_table is ( t_struct struct<user_id:bigint,user_name:string,married:string,weight:double> <user_id: bigint,="" user_name:="" string,="" married:="" weight:="" double="">), the table data is as follows:</user_id:>

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

select inline(array(t_struct)) from t_table;

+------------+----+----+------------+------------+
| user_id    | user_name | married | weight     |
+------------+----+----+------------+------------+
| 10001      | LiLei     | N       | 63.5       |
| 10002      | HanMeiMei | Y       | 43.5       |
+------------+-----------+---------+------------+

Function definition:

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

Parameter description:

• num_keys: Bigint type constant, must be larger than or equal to 0. It is used as the number of columns to transpose key when converting to multiple rows.
• Key: Duplicate columns in multiple rows when converting one row to multiple rows.
• separator: String type constant. It is a separator used to split a string into multiple elements. Exception is thrown when it is null.
• keys: As column of key when you transpose. It is specified by num_keys. If num_keys specifies that all columns are keys (that is, num_keys equals the number of all columns), only one row is returned.
• cols: An array to convert to rows. All columns after keys are considered as an array to be transposed. String type. The stored contents are arrays of string format, such as “Hangzhou; Beijing; shanghai”, they are arrays separated by “;”.

The data in the t_table table is as follows:

+----------+----------+------------+
| 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 |
+----------+----------+------------+

Execute SQL:

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

Results:

+----------+----------+------------+
| 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 |
+----------+----------+------------+

If the table contains the following data:

Login_id LOGIN_IP LOGIN_TIME 
wangwangA 192.168.0.1,192.168.0.2 20120101010000

NULL is complemented to the no-value locales in the array:

Login_id Login_ip Login_time 
wangwangA 192.168.0.1 20120101010000
wangwangA 192.168.0.2 NULL