Other functions

cast(expr as <type>)

• cast(double as bigint): converts a value of the DOUBLE type to the BIGINT type.
• cast(string as bigint): converts a value of the STRING type to the BIGINT type. If the string is comprised of numerals expressed in the INTEGER form, it is converted to the BIGINT type. If the string is comprised of numerals expressed in the FLOAT or EXPONENTIAL form, it is converted to the DOUBLE type and then to the BIGINT type.
• The date format for cast(string as datetime) and cast(datetime as string) is yyyy-mm-dd hh:mi:ss.

coalesce(expr1, expr2, ...)

• expr: the value you want to test. All the values must be of the same data type or be NULL. Otherwise, an error is returned.
• At least one parameter is required. Otherwise, an error is returned.

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

• expression: the expression that you want to compare.
• search: the search item that you want to compare with expression.
• result: the value returned if the values of search and expression match.
• default: the value returned if no search item matches the expression. If default is not specified, NULL is returned. This parameter is optional.

• The matched search value is returned.
• If no matched item exists, default is returned.
• If default is not specified, NULL is returned.
  Note

  • At least three parameters are specified.
  • The values of the result parameter must be of the same type or be NULL. Different data types cause an error. The values of search and expression must be of the same type. Otherwise, an error is returned.
  • If search in the DECODE function has duplicated values that can match the expression, the first mapping result is returned.

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

• The maximum value of the input parameters is returned. If no implicit conversion is required, the return value is of the same type as the input parameter.
• NULL is interpreted as the minimum value.
• If the input parameters are of different 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 types are not allowed.
• If set 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, ...)

• nth: the position of the value to be returned. The value is of the BIGINT type. If it is set to NULL, NULL is returned.
• var1 and var2: the input variables. They can be of the BIGINT, DOUBLE, DATETIME, or STRING type.

• The value in the nth bit is returned. If no implicit conversion is required, the return value is of the same 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 types are not allowed.
• NULL is interpreted as the minimum value.

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

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

• 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 by default.
• partitions: the name of the partition that you want to query. It is of the STRING type. The value of this parameter is a list of partitioning column values based on the sequence of partitioning columns in the table. The numbers of partitioning column values and partitioning columns must be the same.

-- Create partitioned table foo.
CREATE TABLE foo (id BIGINT) PARTITIONED BY (ds STRING, hr STRING);
-- Add a partition to foo.
ALTER TABLE foo ADD PARTITION (ds='20190101', hr='1');
-- Check whether partitions ds='20190101' and hr='1' exist.
SELECT partition_exists('foo', '20190101', '1');

least(var1, var2, ...)

• The minimum value of the input parameters is returned. If no implicit conversion is required, the return value is of the same 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 types are not allowed.
• NULL is interpreted as the minimum value.

max_pt(table_full_name)

pt ='20120901'
pt ='20120902'

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

string TRING uuid()

boolean sample(x, y, column_name1,column_name2,...)

• x and y: indicate that the values fall into x portions by calling the hash function and the yth portion is used. The values of these parameters are of the BIGINT type. y is optional. If it is not specified, the first portion is used, and you must not specify column_name. x and y are integer constants, which are greater than 0. If they are of another type or their values are less than or equal to 0, an exception is reported. If y is greater than x, an exception is also reported. If the value of x or y is NULL, NULL is returned.
• column_name: the column you want to sample. 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 type, and the column value can be NULL. No implicit conversion is performed. If column_name is the constant NULL, an error is reported.

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

• case value
  when value1 then result1
  when value2 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: the expression that you want to check, which is of the BOOLEAN type.
• valueTrue: the value returned if testCondition is true.
• valueFalseOrNull: the value returned if testCondition is false. It can be set to NULL.

select if(1=2,100,200) from dual; 
-- The result is as follows:
+------------+
| _c0 |
+------------+
| 200 |
+------------+

split(str, pat)

• str: the string that you want to split, which is of the STRING type.
• pat: the delimiter of the STRING type, which supports regular expressions.

select split("a,b,c",",") from dual;
-- The result is as follows:
+------+
| _c0 |
+------+
| [a, b, c] |
+------+

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

• text: the string that you want to split, which is of the STRING type.
• delimiter1: the delimiter used to split a string, which is of the STRING type. If it is not specified, a comma (,) is used by default.
• delimiter2: the delimiter used to separate keys from values, which is of the STRING type. If it is not specified, a period (.) is used by default.

select str_to_map('test1&1-test2&2','-','&');
-- The result is as follows:
+------------+
| a          |
+------------+
| {test1:1, test2:2} |

explode (var)

• If the parameter is of the ARRAY<T> type, the data of the ARRAY type stored in a column is converted to multiple-row data.
• If the parameter is of the MAP<K, V> type, each key-value pair of the MAP type stored in a column is transposed to a row with two columns, one for the key and the other for the value.

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

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

• All key types, including those after implicit conversions, must be the same and be of basic data types.
• All value types, including those after implicit conversions, must be the same and can be of any data types.

+------------+----+----+------------+------------+
| 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;
-- The result is as follows:
+------+
| _c0  |
+------+
| {k11:86, k21:15} |
| {k12:97, k22:2} |
| {k13:99, k23:1} |
+------+

ARRAY map_keys(map<K, V>)

ARRAY map_values(map<K, V>)

select map_values(map('a',123,'b',456));
-- The result is as follows:
[123, 456]

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;
-- The result is as follows:
+------+
| _c0  |
+------+
| [k11, 86, k21, 15] |
| [k12, 97, k22, 2] |
| [k13, 99, k23, 1] |
+------+

INT size(map)
INT size(array)

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

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

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

• a: the data of the ARRAY type.
• v: The value must be of the same type as the data type 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;
-- The result is as follows:
+------------+------+
| c1         | _c1  |
+------------+------+
| 1000       | false |
| 1001       | false |
| 1002       | true |
+------------+------+

ARRAY sort_array(ARRAY<T>)

+------------+---------+--------------+
| c1         | c2      | c3           |
+------------+---------+--------------+
| [a, c, f, b]  | [4, 5, 7, 2, 5, 8]  |  [You, Me, Him] |
+------------+---------+--------------+

posexplode(ARRAY<T>)

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

STRUCT struct(value1,value2, ...)

select struct('a',123,'ture',56.90) from dual;
-- The result is as follows:
{col1:a, col2:123, col3:true, col4:56.9}

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

• value: It can be of any type.
• name: the name of the field of the STRING type.

select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50) from dual;
-- The result is as follows:
{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:N, weight:63.5} |
| {user_id:10002, user_name:HanMeiMei, married:Y, weight:43.5} |
+----------+

select inline(array(t_struct)) from t_table;
-- The result is as follows:
+------------+-----------+---------+------------+
| user_id    | user_name | married | weight     |
+------------+-----------+---------+------------+
| 10001      | LiLei     | N       | 63.5       |
| 10002      | HanMeiMei | Y       | 43.5       |
+------------+-----------+---------+------------+

boolean table_exists(string table_name)

-- Used in the SELECT statement.
SELECT IF(table_exists('abd'), col1, col2) FROM src;

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

• 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 into multiple rows.
• separator: the separator used to separate a string into multiple elements. It is a constant of the STRING type. If it is left blank, an error is returned.
• keys: 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 to be converted to rows. All columns following keys are considered as 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 with semicolons (;).

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

• 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.
• separator: the separator used to separate a string into multiple elements. It is a constant of the STRING type. If it is left blank, an error is returned.
• keys: 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 columns to be converted into rows. The columns must be of the same type.

+----------+----------+------------+
| Login_id | Login_ip1 | Login_ip2 |
+----------+----------+------------+
| wangwangA | 192.168.0.1 | 192.168.0.2 |
+----------+----------+------------+

select trans_cols(1, login_id, login_ip1, login_ip2) as (idx, login_id, login_ip) from t_table;

idx    login_id    login_ip
1    wangwangA    192.168.0.1
2    wangwangA    192.168.0.2