11、其它函数

11、其它函数

0、概述

1、表达式与条件判断类

• BETWEEN AND 表达式：筛选满足区间条件的数据。

<a> [NOT] between <b> and <c>

SELECT *
FROM user 
WHERE age BETWEEN 18 AND 15;

• CASE WHEN 表达式：根据表达式的计算结果，灵活地返回不同的值。

-- 格式一：
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 age = 18  THEN '18岁'
        WHEN age = 19  THEN '19岁'
        ELSE       '其它年龄'
    END  AS age_name
FROM my_user;

SELECT CASE age
        WHEN 18 THEN '18岁'
        WHEN 19  THEN '18岁'
        ELSE       '其它年龄'
    END  AS age_name
FROM my_user;

• IF：判断指定的条件是否为真。

if(<testCondition>, <valueTrue>, <valueFalseOrNull>)

--返回200。
select if(1=2, 100, 200); 

• NULLIF：比较两个入参是否相等，二者相等时返回NULL

T nullif(T <expr1>, T <expr2>)

--返回2。
select nullif(2, 3);
--返回NULL。
select nullif(2, 2);
--返回3。
select nullif(3, null);

• NVL：指定值为NULL的参数的返回结果。

nvl(T <value>, T <default_value>)

• 入参
  • value：必填。输入参数。
    default_value：必填。替换后的值。

SELECT nvl(name,new_name);
-- 2
SELECT nvl(null,2);

• COALESCE：返回参数列表中第一个非NULL的值。
  • COALESCE函数会从左至右的顺序对输入的参数进行逐一检查，一旦遇到第一个非NULL的值，就会立即返回该值，并不再继续检查后面的参数。

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

-- 1。
SELECT coalesce(null,null,1,null,3,5,7);

• DECODE：实现if-then-else分支选择的功能。

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

--当customer_id的值为c1时，返回Taobao；值为c2时，返回Alipay；值为c3时，返回Aliyun；值为NULL时，返回N/A；其他场景返回Others。
SELECT DECODE(user_id
                ,'c1', 'Taobao'
                , 'c2', 'Alipay'
                , 'c3', 'Aliyun'
                , Null, 'N/A'
                , 'Others'
        ) AS RESULT FROM sale_detail;

--等效于如下语句。
IF user_id     = c1 THEN RESULT := 'Taobao';
ELSEIF user_id = c2 THEN RESULT := 'Alipay';
ELSEIF user_id = c3 THEN RESULT := 'Aliyun';
ELSE RESULT := 'Others';
END IF;

2、类型转换

• CAST：将表达式的结果转换为目标数据类型。

cast(<expr> as <type>)

• 入参
  • expr：必填。待转换数据源。
    type：必填。目标数据类型。
    • cast(double as bigint)
    • cast(string as bigint)
    • cast(string as datetime)或cast(datetime as string)：默认格式：``yyyy-mm-dd hh:mi:ss`
    • 基本数据类型与JSON类型之间的相互转换
      • cast(json as string)
      • cast(string as json)

-- 1
SELECT cast(1.2 AS bigint);
-- 10
SELECT cast('10' AS bigint);
-- 2025-03-04 12:12:12
SELECT cast('2025-03-04 12:12:12' AS datetime);

-- json转成string
select cast(json '123' as string);
-- string转为json
SELECT cast('{"a":1,"b":2}' AS json);
-- json转成string的错误示例，不支持array/object类型的JSON表达式转换为string。
SELECT cast(json_object('a',1,'b',2) AS string);

• STR_TO_MAP：将字符串按照指定的分隔符分割得到Key和Value。

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

• 入参
  • `mapDupKeyPolicy：可选。STRING类型。指定出现重复Key时的处理方式。取值范围如下：
    • exception：如果出现重复的Key，返回报错。
    • last_win：如果出现重复的Key，后边的值将覆盖前边的值。
  • text：必填。STRING类型，指被分割的字符串。
  • delimiter1：可选。STRING类型，分隔符，不指定时默认为英文逗号（,）。
  • delimiter2：可选。STRING类型，分隔符，不指定时默认为等于号（=）。

-- {"b":"2","a":"1"}
SELECT str_to_map('a&1-b&2','-','&');
-- {"b":"2","a":"1"}
SELECT str_to_map('a.1,b.2',',','\\.');
-- {"b":"3","a":"1"}
SELECT str_to_map("a.1,b.2,b.3",",","\\.");

• BASE64：将二进制表示值转换为BASE64编码格式字符串。

string base64(binary <value>)

--返回YWxpYmFiYQ==。
select base64(cast ('alibaba' as binary));
--返回NULL。
select base64(null);

• UNBASE64：将BASE64编码格式字符串转换为二进制表示值。

binary unbase64(string <str>)

--返回alibaba。
select unbase64('YWxpYmFiYQ==');
select cast(unbase64('YWxpYmFiYQ==') as string);
--返回NULL。
select unbase64(null);

3、加密/哈希/压缩类

• CRC32：计算字符串或二进制数据的循环冗余校验值。

bigint crc32(string|binary <expr>)

-- 2743272264
SELECT crc32('ABC');
-- NULL 
SELECT crc32(null);

• HASH：根据输入参数计算Hash值。

bigint hash(<value1>, <value2>[, ...]);

--返回66。
select hash(0, 2, 4);
--返回97。
select hash(0, 'a');

• SHA：计算字符串或二进制数据的SHA-1哈希值。

string sha(string|binary <expr>)

-- 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8
SELECT sha('ABC');
-- NULL 
SELECT sha(null);

• SHA1：计算字符串或二进制数据的SHA-1哈希值。

string sha1(string|binary <expr>)

-- 3c01bdbb26f358bab27f267924aa2c9a03fcfdb8
SELECT sha1('ABC');
-- NULL
SELECT sha1(null);

• SHA2：计算字符串或二进制数据的SHA-2哈希值。

string sha2(string|binary <expr>, bigint <number>)

• 入参
  • expr：必填。STRING或BINARY类型。
  • number：必填。BIGINT类型。哈希位长，取值必须是224、256、384、512、0（同256）。

-- b5d4045c3f466fa91fe2cc6abe79232a1a57cdf104f7a26e716e0a1e2789df78
SELECT sha2('ABC',256);
-- NULL
SELECT sha2('ABC',null);

• COMPRESS：对STRING或BINARY类型输入参数按照GZIP算法进行压缩。

binary compress(string <str>)
binary compress(binary <bin>)

-- =1F=8B=08=00=00=00=00=00=00=03=CBH=CD=C9=C9=07=00=86=A6=106=05=00=00=00
SELECT compress('hello');
-- =1F=8B=08=00=00=00=00=00=00=03=03=00=00=00=00=00=00=00=00=00
SELECT compress('');
-- NULL
SELECT compress(null);

• DECOMPRESS：对BINARY类型输入参数按照GZIP算法进行解压。

binary decompress(binary <bin>)

-- hello
SELECT decompress(compress('hello'));
-- ''
SELECT decompress(compress(''));
-- Null
SELECT decompress(null);

4、身份信息与用户工具类

• GET_IDCARD_AGE：根据身份证号码返回当前的年龄。

get_idcard_age(<idcardno>)

• 入参
  • idcardno：必填。STRING类型，15位或18位身份证号码。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性。如果校验不通过，会返回NULL。

-- 31
SELECT get_idcard_age('130131199403075219');

• GET_IDCARD_BIRTHDAY：根据身份证号码返回出生日期。

get_idcard_birthday(<idcardno>)

入参

• idcardno：必填。STRING类型，15位或18位身份证号码。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性。如果校验不通过，会返回NULL。

-- 生日：1994-03-07 00:00:00
SELECT get_idcard_birthday('130131199403075219');

• GET_IDCARD_SEX：根据身份证号码返回性别。

get_idcard_sex(<idcardno>)

入参

• idcardno：必填。STRING类型，15位或18位身份证号码。在计算时会根据省份代码以及最后一位校验码检查身份证的合法性。如果校验不通过，会返回NULL。

-- 性别：M
SELECT get_idcard_sex('130131199403075219');

• GET_USER_ID：获取当前账号的账号ID。

get_user_id()

select get_user_id();
--返回结果如下。
+------------+
| _c0        |
+------------+
| 1117xxxxxxxx8519 |
+------------+

• UNIQUE_ID：返回一个随机ID，运行效率高于UUID函数。

string unique_id()

-- e23f4a5d-e26f-8c34-d162-45836f6bd1aa
SELECT unique_id();

• UUID：返回一个随机ID。

string uuid()

-- f1a0f244-b0e3-f58f-6944-6d78761d32fe
string uuid();

5、集合/聚合/比较类

• GREATEST：返回输入参数中的最大值。

greatest(<var1>, <var2>[,...])

• 入参
  • var：必填，输入值。BIGINT、DOUBLE、DECIMAL、DATETIME、DATE或STRING类型。
  • 当输入参数数据类型不相同时，会进行隐式转换，规则如下：
    • DOUBLE、BIGINT、STRING之间的比较会转换为DOUBLE类型。
    • DECIMAL、DOUBLE、BIGINT和STRING之间的比较会转换为DECIMAL类型。
    • STRING、DATETIME的比较会转换为DATETIME类型。

-- 3.4
SELECT greatest(1 ,3 ,3.4);
-- 2025-11-11 13:13:13
SELECT greatest(datetime'2025-10-01 12:12:12',datetime'2025-11-11 13:13:13');
-- 2025-10-01
SELECT greatest(date'2025-10-01',date'2025-01-01');

• LEAST：返回输入参数中最小的值。

least(<var1>, <var2>[,...])

• 入参
  • var：必填，输入值。BIGINT、DOUBLE、DECIMAL、DATETIME、DATE或STRING类型。
  • 当输入参数数据类型不相同时，会进行隐式转换，规则如下：
    • DOUBLE、BIGINT、STRING之间的比较会转换为DOUBLE类型。
    • DECIMAL、DOUBLE、BIGINT和STRING之间的比较会转换为DECIMAL类型。
    • STRING、DATETIME的比较会转换为DATETIME类型。

-- 1.0
SELECT least(1 ,3 ,3.4);
-- 2025-10-01 12:12:12
SELECT least(datetime'2025-10-01 12:12:12',datetime'2025-11-11 13:13:13');
-- 2025-01-01
SELECT least(date'2025-10-01',date'2025-01-01');

• ORDINAL：将输入变量按从小到大排序后，返回指定位置的值。

ordinal(bigint <nth>, <var1>, <var2>[,...])

• 入参
  • nth：必填，位置序号，起始从1开始。BIGINT类型。指定要返回的位置值为NULL时，返回NULL。
  • var：必填，待排序的值。BIGINT、DOUBLE、DATETIME或STRING类型。

-- 6.0
SELECT ordinal(CAST(3 AS bigint ) ,5.0 ,1.0 ,8.0 ,6.0);

6、分区与元数据判断类

• MAX_PT：返回分区表的一级分区的最大值。

MAX_PT(<table_full_name>)

• 入参
  • table_full_name：必填。STRING类型。指定表名。必须对表有读权限。

SELECT * FROM table WHERE pt=MAX_PT("table");
-- 等效于
SELECT * FROM table WHERE pt = (SELECT MAX(pt) FROM table);

• PARTITION_EXISTS：查询指定的分区是否存在。

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

• 入参
  • table_name：必填。表名称，STRING类型。
    partitions: 必填。分区名称，STRING类型。按照表分区列的顺序依次写出分区值，分区值数目必须与分区列数目一致。

-- 查询分区ds='20190101' 是否存在。
SELECT partition_exists('my_table','20250707');

-- 查询分区ds='20190101'、hour='1'是否存在。
SELECT partition_exists('my_table','20250707','12');

• TABLE_EXISTS：查询指定的表是否存在。

boolean table_exists(string <table_name>)

• 入参
  • table_name：必填。表名称。

--在select列表中使用。
select table_exists('t_user_info');

7、采样与数据操作类

• SAMPLE：对所有读入的列值，采样并过滤掉不满足采样条件的行。

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

• 入参
  • x、y：x必填。BIGINT类型，取值范围为大于0的整型常量。表示哈希为x份，取第y份。
  • column_name：可选。采样的目标列。

-- 对表数据做随机哈希分配为2份，取第1份：
select * from mf_sample where sample(2,1);
-- 根据id列对表数据做随机哈希分配为2份，取第1份：
select * from mf_sample where sample(2,1,id);

• STACK：将指定的参数组分割为指定的行数。

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

• 入参
  • n：必填。分割的行数。
    expr：必填。待分割的参数。

--将1, 2, 3, 4, 5, 6排为3行。
select stack(3, 1, 2, 3, 4, 5, 6);
-- col0 col1
-- 1    2
-- 3    4
-- 5    6

• TRANS_ARRAY：将一行数据转为多行的UDTF，将列中存储的以固定分隔符格式分隔的数组转为多行。

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

• 入参
  • num_keys：必填。 在转为多行时作为转置key的列的个数。
  • separator：必填。用于将字符串拆分成多个元素的分隔符。为空时返回报错。
  • keys：必填。转置时作为key的列， 个数由num_keys指定。
  • cols： 必填。要转为行的数组，keys之后的所有列视为要转置的数组，必须为STRING类型，存储的内容是字符串格式的数组，例如Hangzhou;Beijing;shanghai，是以分号（;）分隔的数组。

--执行SQL。
select trans_array(1, ",", login_id, login_ip, login_time) as (login_id,login_ip,login_time) from t_table;

--用两个key，id和name进行转数组，执行SQL。
select trans_array(2, ",", Id,Name, login_ip, login_time) as (Id,Name,login_ip,login_time) from t_table;

• TRANS_COLS：将一行数据转换为多行数据的UDTF，将不同的列拆分为不同的行。
  • 所有作为key的列必须处在前面，而要转置的列必须放在后面。
  • 在一个select中只能有一个UDTF，不可以再出现其他的列。

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

• 入参
  • num_keys：必填。在转为多行时作为转置key的列的个数。
  • keys：必填。转置时作为key的列， 个数由num_keys指定。
  • idx：必填。转换后的行号。
  • cols：必填。 要转为行的列。

+----------+----------+------------+
| Login_id | Login_ip1 | Login_ip2 |
+----------+----------+------------+
| wangwangA | 192.168.0.1 | 192.168.0.2 |
+----------+----------+------------+
--执行SQL。
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