Other functions

cast(<expr> as <type>)

• expr: required. The expression whose computing result you want to convert.
• type: required. The data type to which you want to convert the data.

The return value is of the specified data type.

• Example 1: common usage. Sample command:

  -- 1 is returned.
  select cast('1' as bigint);

• Example 2: incorrect usage. If the conversion fails or an unsupported type conversion occurs, NULL is returned. Sample command:

  -- NULL is returned.
  select cast('abc' as bigint);

coalesce(<expr1>, <expr2>, ...)

Returns the first non-NULL value in <expr1>, <expr2>, .... If all values in the list are NULL, NULL is returned.

expr: required. A value that you want to check. Except the NULL values, all other values must be of the same data type. Otherwise, an error is returned. If all values are NULL, an error is returned.

The data type of the return value is the same as the data type of the parameter.

• Example 1: common usage. Sample command:

  -- 1 is returned.
  select coalesce(null,null,1,null,3,5,7);

• Example 2: incorrect usage. The data types of parameter values are not the same, and an error is returned. Sample command:

  -- An error is returned, and the value abc cannot be identified.
  select coalesce(null,null,1,null,abc,5,7);

• Example 3: incorrect usage. Non-NULL values do not exist and an error is returned. Sample command:

  -- An error is returned because non-NULL values do not exist.
  select coalesce(null,null,null,null);

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

Implements the IF-THEN-ELSE conditional branching feature.

• expression: required. The expression that you want to compare.
• search: required. The search item that you use to compare with the expression.
• result: required. The value returned when the value of search matches the value of expression.
• default: optional. If no search items match the expression, the value of default is returned. If this parameter is not specified, NULL is returned.

• If a search item matches the expression, result is returned.
• If no search item matches the expression, default is returned.
• If default is not specified, NULL is returned.
• If duplicate search items match the expression, the first value is returned.
• In most cases, NULL is returned when MaxCompute SQL calculates NULL=NULL. However, the DECODE function considers that the two NULL values are the same.

-- If the value of customer_id is 1, Taobao is returned. If the value is 2, Alipay is returned. If the value is 3, Aliyun is returned. If the value is NULL, N/A is returned. In other cases, Others is returned.
select
decode(customer_id,
'1', 'Taobao',
'2', 'Alipay',
'3', 'Aliyun',
Null, 'N/A',
'Others') as result
from sale_detail;
-- The preceding statement is equivalent to the following statement:
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>)

Returns the current age based on the ID card number. The current age is the current year minus the birth year in the ID card number.

idcardno: required. A 15-digit or 18-digit ID card number of the STRING type. During calculation, the validity of the ID card is checked based on the province code and the last digit of the ID card number. NULL is returned if the check fails.

A value of the BIGINT type is returned. If the input value is NULL, NULL is returned.

get_idcard_birthday(<idcardno>)

Returns the date of birth based on the ID card number.

idcardno: required. A 15-digit or 18-digit ID card number of the STRING type. During calculation, the validity of the ID card is checked based on the province code and the last digit of the ID card number. NULL is returned if the check fails.

A value of the DATETIME type is returned. If the input value is NULL, NULL is returned.

get_idcard_sex(<idcardno>)

Returns the gender based on the ID card number. Valid values: M and F. M indicates male and F indicates female.

idcardno: required. The 15-digit or 18-digit ID card number of the STRING type. During calculation, the validity of the ID card is checked based on the province code and the last digit of the ID card number. NULL is returned if the check fails.

A value of the STRING type is returned. If the input value is NULL, NULL is returned.

greatest(<var1>, <var2>, …)

Returns the maximum value of the input parameters.

var: required. The value can be of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type. If the values of all input parameters are NULL, NULL is returned.

• The maximum value of the input parameters is returned. If no implicit conversion is required, the return value is of the same data type as the input parameter.
• NULL is interpreted as the minimum value.
• If the input parameters are of different data types, those of the DOUBLE, BIGINT, DECIMAL, and STRING types need to be converted to the DOUBLE type for comparison, and those of the STRING and DATETIME types need to be converted to the DATETIME type for comparison. Implicit conversions of other data types are not allowed.
• If odps.sql.hive.compatible is set to true and the value of any input parameter is NULL, NULL is returned.

ordinal(bigint <nth>, <var1>, <var2>, …)

Sorts the values of the input variables in ascending order and returns the value that is ranked nth.

• nth: required. The position of the value that you want to return. The value is of the BIGINT type. If it is set to NULL, NULL is returned.
• var: required. The value is of the BIGINT, DOUBLE, DATETIME, or STRING type.

• The value that is ranked nth is returned. If no implicit conversion is required, the return value is of the same data type as the input parameter.
• If a data type conversion is performed among the DOUBLE, BIGINT, and STRING types, a value of the DOUBLE type is returned. If a data type conversion is performed between the STRING and DATETIME types, a value of the DATETIME type is returned. Implicit conversions of other data types are not allowed.
• NULL is interpreted as the minimum value.

-- 2 is returned.
select ordinal(3, 1, 3, 2, 5, 2, 4, 6); 

boolean partition_exists(string <table_name>, string... <partitions>)

Checks whether a specified partition exists in a table.

• table_name: required. The table name, which is of the STRING type. You can specify a project name in the table name, for example, my_proj.my_table. If you do not specify a project name, the current project name is used.
• partitions: required. The partition name, which is of the STRING type. In this parameter, you must specify the values of partition key columns in a table based on the sequence of these columns. The number of values must be the same as the number of partition key columns.

A value of the BOOLEAN type is returned. If the specified partition exists, True is returned. Otherwise, False is returned.

-- Create the foo partitioned table.
create table foo (id bigint) partitioned by (ds string, hr string);
-- Add partitions to the foo table.
alter table foo add partition (ds='20190101', hr='1');
-- Check whether partitions 20190101 and 1 exist. True is returned.
select partition_exists('foo', '20190101', '1');

least(<var1>, <var2>, …)

Returns the minimum value of the input parameters.

var: required. The values of the input parameters, which are of the BIGINT, DOUBLE, DECIMAL, DATETIME, or STRING type. If the values of all input parameters are NULL, NULL is returned.

• The minimum value of input parameters is returned. If no implicit conversion is required, the return value is of the same data type as the input parameter.
• If a data type conversion is performed among the DOUBLE, BIGINT, and STRING types, a value of the DOUBLE type is returned. If a data type conversion is performed between the STRING and DATETIME types, a value of the DATETIME type is returned. If a data type conversion is performed among the DECIMAL, DOUBLE, BIGINT, and STRING types, a value of the DECIMAL type is returned. Implicit conversions of other data types are not allowed.
• NULL is interpreted as the minimum value.

max_pt(<table_full_name>)

Returns the name of the largest level-1 partition in a partitioned table and reads the data files of this partition. This function determines the largest partition by sorting partitions in alphabetical order.

table_full_name: required. The table name, which must be specified with the project name, for example, prj.src. You must have read permissions on the table.

The name of the largest partition is returned.

string uuid()

Returns a random ID, which is in the format of 29347a88-1e57-41ae-bb68-a9edbdd9****.

boolean sample(<x>, <y>, [<column_name1>, <column_name2>,...])

Samples all values read from column_name based on x and y, and filters out the rows that do not meet sampling conditions.

• x and y: x is required. x and y are integer constants that are greater than 0. Their values are of the BIGINT type. They indicate that the values fall into x portions based on the hash function and the yth portion is used.

  If y is not specified, you do not need to specify column_name. In this case, the first portion is used.

  An error is returned if x or y is of another data type, the value of x or y less than or equal to 0, or y is greater than x. If the value of x or y is NULL, NULL is returned.

• column_name: optional. The name of the destination column on which sampling is performed. This parameter is optional. If it is not specified, random sampling is performed based on the values of x and y. It can be of any data type, and the column value can be NULL. No implicit conversion is performed. If column_name is constant NULL, an error is returned.
  Note To avoid data skew due to the NULL value, uniform hashing is performed on the NULL values in column_name in x portions. If column_name is not specified and the amount of data is small, the output is not necessarily uniform. In this case, we recommend that you specify column_name to obtain a uniform output.

A value of the BOOLEAN type is returned.

Assume that the tbla table exists and contains the cola column.

-- The values in the cola column fall into four portions based on the hash function, and the first portion is used. True is returned.
select * from tbla where sample (4, 1 , cola);
-- The values in each row are randomly hashed to four portions, and the second portion is used. True is returned.
select * from tbla where sample (4, 2);

Returns the value of result based on the calculation result of value or _condition.

• value: required. The value used for comparison.
• _condition: required. The specified condition.
• result: required. The return value.

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

Checks whether testCondition evalutes to true. If testCondition evalutes to true, the value of valueTrue is returned. Otherwise, the value of valueFalseOrNull is returned.

• testCondition: required. The expression that you want to evaluate. The value is of the BOOLEAN type.
• valueTrue: required. The value returned when testCondition evaluates to true
• valueFalseOrNull: the value returned when testCondition evaluates to false. You can set this parameter to NULL.

The data type of the return value is the same as that of valueTrue or valueFalseOrNull.

-- 200 is returned.
select if(1=2,100,200); 

split(<str>, <pat>)

Returns an array after str is split with pat.

• str: required. The string that you want to split, which is of the STRING type.
• pat: required. The STRING-type delimiter, which can be a regular expression.

array <string> is returned.

-- ["a","b","c"] is returned.
select split("a,b,c",",");

str_to_map(<text> [, <delimiter1> [, <delimiter2>]])

Splits text into key-value pairs by using delimiter1 and then separates keys from values in the key-value pairs by using delimiter2.

• text: required. The string that you want to split, which is of the STRING type.
• delimiter1: required. The delimiter of the STRING type. If this parameter is not specified, commas (,) are used.
• delimiter2: required. The delimiter of the STRING type. If this parameter is not specified, equal signs (=) are used.
  Note If the delimiter is a regular expression or special character, you must add two backslashes (\\) before the delimiter for escaping. The following special characters can be used as a delimiter: . ? + * :

A value of the map<string, string> type is returned. The return value indicates that the string specified by text is split by using delimiter1 and delimiter2.

-- {"test2":"2","test1":"1"} is returned.
select str_to_map('test1&1-test2&2','-','&');
-- {"test2":"2","test1":"1"} is returned.
select str_to_map("test1.1,test2.2", ",", "\\.") ;

• A SELECT statement can contain only one EXPLODE function, and no other columns of a table are allowed.
• This function cannot be used with the GROUP BY, CLUSTER BY, DISTRIBUTE BY, or SORT BY clause.

explode (<var>)

var: the data type, which can be array<T> or map<K, V>.

Rows after transposition are returned.

select explode(array(null, 'a', 'b', 'c')) col;
-- Return result:
+------------+
| col        |
+------------+
| NULL       |
| a          |
| b          |
| c          |
+------------+

stack(n, expr1, ..., exprk) 

Splits expr1, ..., exprk into n rows. Unless otherwise specified, the output result uses the default column names col0, col1....

• n: required. The number of rows obtained after splitting.
• expr: required. The parameter that you want to split expr1,... exprk must be of the INTEGER type, and the number of parameters must be an integer multiple of n. The parameter must be able to be split into n complete rows. Otherwise, an error is returned.

n rows with a specific number of columns are returned. The number of columns is equal to the number of parameters divided by n.

-- Split the parameter group of 1, 2, 3, 4, 5, 6 into three rows.
select stack(3, 1, 2, 3, 4, 5, 6);
-- Return result:
+------+------+
| col0 | col1 |
+------+------+
| 1    | 2    |
| 3    | 4    |
| 5    | 6    |
+------+------+

-- Split 'A',10,date '2015-01-01','B',20,date '2016-01-01' into two rows.
select stack(2,'A',10,date '2015-01-01','B',20,date '2016-01-01') as (col0,col1,col2);
-- Return result:
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

-- Split the parameter group of a, b, c, and d into two rows. If the source table contains multiple rows, this function is called for each row.
select stack(2,a,b,c,d) as (col,value)
from values 
    (1,1,2,3,4),
    (2,5,6,7,8),
    (3,9,10,11,12),
    (4,13,14,15,null)
as t(key,a,b,c,d);
-- Return result:
+------+-------+
| col  | value |
+------+-------+
| 1    | 2     |
| 3    | 4     |
| 5    | 6     |
| 7    | 8     |
| 9    | 10    |
| 11   | 12    |
| 13   | 14    |
| 15   | NULL  |
+------+-------+

-- Use this function with the LATERAL VIEW clause.
select tf.* from (select 0) t lateral view stack(2,'A',10,date '2015-01-01','B',20, date '2016-01-01') tf as col0,col1,col2;
-- Return result:
+------+------+------+
| col0 | col1 | col2 |
+------+------+------+
| A    | 10   | 2015-01-01 |
| B    | 20   | 2016-01-01 |
+------+------+------+

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

Defines a MAP parameter based on a specific key-value pair.

• key: required. All keys must be of the same data types, including the types after an implicit conversion. The data types must be basic types.
• value: required. All values must be of the same data type, including the data types after an implicit conversion. Random data types can be used.

The MAP parameter is returned.

ARRAY map_keys(map<K, V>)

Returns all keys in the MAP parameter as an array.

The parameter is of the MAP type.

A value of the ARRAY type is returned. If the input value is NULL, NULL is returned.

ARRAY map_values(map<K, V>)

Returns all values in the MAP parameter as an array.

The parameter is of the MAP type.

A value of the ARRAY type is returned. If the input value is NULL, NULL is returned.

select map_values(map('a',123,'b',456));
-- Return result:
[123, 456]

ARRAY array(value1,value2, ...)

Creates an array by using given values.

The parameters can be of any type, but the types of all parameters must be the same.

A value of the ARRAY type is returned.

INT size(map)
INT size(array)

Uses size(map) to return the number of key-value pairs in a specified MAP parameter. Uses size(array) to return the number of elements in a specified array.

• map: the data of the MAP type.
• array: the data of the ARRAY type.

-- 2 is returned.
select size(map('a',123,'b',456)); 
-- 3 is returned.
select size(map('a',123,'b',456,'c',789)); 
-- 2 is returned.
select size(array('a','b'));
-- 3 is returned. 
select size(array(123,456,789)); 

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

Checks whether array a contains v.

• a: the data of the ARRAY type.
• v: The value of v must be of the same data type as the data in the array.

ARRAY sort_array(ARRAY<T>)

Sorts given arrays.

ARRAY<T>: the data of the ARRAY type. Data in the array can be of any data type.

A value of the ARRAY type is returned.

posexplode(ARRAY<T>)

Converts a given array into a table that has two columns. The first column lists subscripts of each value in the array, starting from 0. The second column lists array elements.

ARRAY<T>: the data of the ARRAY type. Data in the array can be of any data type.

The generated table is returned.

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

STRUCT struct(value1,value2, ...)

Creates a struct based on a given value list.

value: It can be of any data type.

A value of the STRUCT type is returned. Column names are sequentially named as col1, col2, ....

select struct('a',123,'ture',56.90);
-- Return result:
+------------+
| _c0        |
+------------+
| {"col1":"a","col2":123,"col3":"ture","col4":56.9} |
+------------+
1 records (at most 10000 supported) fetched by instance tunnel.

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

Creates a struct based on given name-value pairs.

• value: It can be of any data type.
• name: the column name of the STRING type. This parameter is a constant.

A value of the STRUCT type is returned. Column names are sequentially named as name1, name2, ....

select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50);
-- Return result:
+------------+
| _c0        |
+------------+
| {"user_id":10001,"user_name":"LiLei","married":"F","weight":63.5} |
+------------+
1 records (at most 10000 supported) fetched by instance tunnel.

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

Expands a given struct array. Each array element corresponds to a row and each struct element corresponds to a column in each row.

STRUCT: The values in the array can be of any data type.

The function generated by the table is returned.

boolean table_exists(string table_name)

Checks whether a specified table exists.

table_name: the name of the table that you want to query. It is of the STRING type. You can specify a project name in the table name, for example, my_proj.my_table. If you do not specify a project name, the current project name is used.

A value of the BOOLEAN type is returned. If the specified table exists, True is returned. Otherwise, False is returned.

-- Used for the list in the SELECT statement.
select from(table_exists('abd'), col1, col2) FROM src;

• All columns that are used as key must be placed before the columns are to be transposed.
• Only one UDTF is allowed in a SELECT statement.
• This function cannot be used with the GROUP BY, CLUSTER BY, DISTRIBUTE BY, or SORT BY clause.

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

Transposes one row of data into multiple rows. This function is a UDTF that transposes an array separated by fixed delimiters in a column into multiple rows.

• num_keys: the number of columns that can be used as key when you transpose one row into multiple rows. The value is a constant of the BIGINT type, which must be greater than or equal to 0.
• key: duplicate columns in multiple rows after transposition.
• separator: the delimiter used to split a string into multiple elements. It is a constant of the STRING type. If it is left blank, an error is returned.
• keys: the columns that are used as key during the transposition. The number of keys is specified by num_keys. If num_keys equals the total number of all columns (which means that all columns are used as key), only one row is returned.
• cols: the array that you want to be converted to rows. All columns that follow keys are considered arrays to be transposed. The parameter value must be of the STRING type to store arrays in the STRING format, such as Hangzhou;Beijing;shanghai. The values in this array are separated by semicolons (;).

Transposed rows are returned. The new column name is specified by as. The data types of columns that are used as key remain unchanged. All other columns are of the STRING type. The number of separated rows is based on the array with the maximum number of elements. If the number of rows is insufficient, NULL is added.

• All columns that are used as key must be placed before the columns are to be transposed.
• Only one UDTF is allowed in a SELECT statement.

trans_cols (num_keys, key1,key2,…,col1, col2,col3) as (idx, key1,key2,…,col1, col2)

Transposes one row of data into multiple rows. This function is a UDTF that transposes columns into rows.

• num_keys: The value is a constant of the BIGINT type, which must be greater than or equal to 0. This parameter specifies the number of columns that can be used as key when you transpose one row into multiple rows.
• key: duplicate columns in multiple rows when you transpose one row to multiple rows.
• keys: the columns that are used as key during the transposition. The number of keys is specified by num_keys. If num_keys equals the total number of all columns (which means that all columns are used as key), only one row is returned.
• col: the column you want to convert into a row.

Transposed rows are returned. The new column name is specified by as. The first output column is the subscripts of the transposition, which start with 1. The data types of the columns that are used as key remain unchanged, whereas the data types of other columns remain unchanged.

get_user_id()

Obtains the ID of the current account, which is the user ID (UID).

No input parameters are required, and an error is returned.

The ID of the current account is returned.

select get_user_id();
-- Return result:
+------------+
| _c0        |
+------------+
| 1117xxxxxxxx8519 |
+------------+