Other functions

Command format:

cast(expr as <type>)

Converts the result of expression to object type. For example, cast (‘1’ as bigint) converts string ‘1’ to bigint ‘1’. If the conversion is unsuccessful or not supported, an exception is thrown.

Command format:

coalesce(expr1, expr2, ...)

Returns value type is the same as parameter type.

Command format:

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

Parameter description:

• expression: An expression to be compared.
• search: The search string; which is compared with the expression.
• result: Return value when the values of search and expression match.
• default: Optional. If all search items do not match the expression, return this default value. If it is not specified, return NULL.

Return value:

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

  1. Specify at least three parameters.
  2. All of the result types must be the same or NULL. Inconsistent data type throws an exception. All of the ‘search’ and ‘expression’ types must be consistent, otherwise an exception is thrown.
  3. If the option ‘search’ in ‘decode’ has duplicate or matched records, 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 earlier 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;

Command format:

get_idcard_age(idcardno)

Command format:

get_idcard_birthday(idcardno)

Command format:

get_idcard_sex(idcardno)

Command format:

greatest(var1, var2, ...)

Command format:

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 into Double type.
  • For String and Datetime type, convert them into Datetime type.
  • Other implicit conversions are not allowed.
• NULL is the least value.

Example:

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

Command format:

least(var1, var2, ...)

Return value:

• The least value in the 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 into Double type.
  • For String and Datetime type, convert them into Datetime type.
  • Convert to Decimal type when Decimal type compares to Double, Bigint or String type.
  • Other implicit conversions are not allowed.

• NULL is the least value.

Command prompt:

max_pt(table_full_name)

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

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

Command format:

string uuid()

Command format:

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 the 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 thrown. If any input of x and y is NULL, return NULL.
• column_name: The destination column to be sampled.
  • column_name can be excluded, in this 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. Implicit type conversion is not needed.
  • If column_name is the constant NULL, an exception is thrown.

Suppose that the table 'tbla' already exists 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.

The following are the two case types that MaxCompute offers:

case value
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 are 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;

Command format:

if(testCondition, valueTrue, valueFalseOrNull)

Parameter description:

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

Example:

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

Command format:

split(str, pat)

Parameter description:

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

Example:

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

Command format:

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 the other for the value.

Rows after conversion are returned.

Example:

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

Command format:

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

key/value

• All key types must be the same basic type. 
• Any value types can be used, but all value types must be consistent.

Example:

select map('a',123,'b',456) from dual;

Result:

{a:123, b:456}

Command format:

ARRAY map_keys(map<K, V> )

Example:

select map_keys(map('a',123,'b',456)) from dual;
Result:
[a, b]

Command format:

ARRAY map_values(map<K, V>)

Example:

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

Command format:

ARRAY array(value1,value2, ...)

Example:

select array(123,456,789) from dual;

Result:

[123, 456, 789]

Command format:

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

Command format:

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.

Example:

select array_contains(array('a','b'), 'a') from dual; --Returns true
select array_contains(array(456,789),123) from dual; -- Returns false

Command format:

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

Command format:

posexplode(ARRAY<T>)

Example:

select posexplode(array('a','c','f','b')) from dual;
Result:
+------------+-----+
| 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;
Result:
{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: A 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;
Result:
{user_id:10001, user_name:LiLei, married:F, weight:63.5}

Command format:

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

Example:

select inline(array(named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50))) from dual;
Result:
+------------+-----------+---------+------------+
| user_id | user_name | married | weight |
+------------+-----------+---------+------------+
| 10001 | LiLei | F | 63.5 |
+------------+-----------+---------+------------+

Command format:

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

The table contains the following data:

Login_id LOGIN_IP LOGIN_TIME 
wangwangA 192.168.0.1,192.168.0.2 20120101010000,20120102010000

Use the trans_array function:

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

Result:

Login_id Login_ip Login_time
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