HiveSQL常用函数

1.字符串函数

1.1 split()函数

用于切割字符串

格式: split('参数1','参数2'),这里的参数1为数据,参数2为切割方式

细节:参数二可以用正则表达式

举例

select split('abcd','c');  --结果为["ab","d"]
select split('asd11mcd22', '11');  --结果为["asd","mcd22"]

select split('asd11mc22dd', '\\d+');  --结果为["asd","mc","dd"]

1.2 concat()函数

用于拼接字符串

格式: concat('数据1','数据2'......)

细节:该函数拼接没有拼接符,直接拼接在一起

举例

select concat('asd123','ff');  --结果为asd123ff

1.3 concat_ws()函数

也是用于拼接字符串

格式: concat_ws('拼接符','数据1','数据2',......)

举例

select concat_ws('-','abcfs','mm','cc');  --结果为abcfs-mm-cc

1.4 substr()函数

用于截取字符串

格式: substr('数据',起始位置,截取多少位)

细节:空格也算一位

举例

select substr('2001-07-09 00:00:00',1,10);  --获取年月日 2001-07-09
select substr('2001-07-09 00:00:00',12,8);  --获取时间 00:00:00
select substr('2001-07-09 00:00:00',1,4);  --获取年 2001
select substr('2001-07-09 00:00:00',6,2);  --获取月 07

2.时间函数

2.1 current_timestamp()函数

返回当前日期及时间

举例

select `current_timestamp`();

2.2 current_date()函数

获取当前日期

举例

select current_date();

2.3 unix_timestamp()函数

获取时间原点

解释:时间原点指的是自1970年1月1日00:00:00 UTC以来的秒数，作为计算机系统中记录时间的基准。

举例

select  unix_timestamp();  --1705844247

2.5 from_unixtime()

用于将UNIX时间戳（表示从1970年1月1日00:00:00 UTC开始经过的秒数）转换为日期时间格式。

举例

select from_unixtime(1888888889); --输出结果为 2029-11-09 11:21:29

2.6 year month day函数

获取年月日

举例

-- 获取时间年
select year('2024-10-12 10:12:10');

-- 获取时间月
select month('2024-10-12 10:12:10');

-- 获取日
select day('2024-10-12 10:12:10');

2.7 quarter()函数

获取季度函数

举例

select quarter('2024-10-12 10:12:10'); --输出结果为4,表示第四季度
select quarter('2024-5-12');  --输出结果为2,表示第二季度

3.数学函数

3.1 abs()函数

绝对值函数

举例

select abs(-10); --输出结果为10

3.2 round()函数

格式:round(参数1,参数2)

参数1表示数据,参数2表示保留小数位数,注意四舍五入

举例

select round(100.156,2);  --输出结果为100.16

3.3 rand()函数

取随机数

举例

select rand();  --取0到1之间的随机数

select rand()*100;  --取0到100之间的随机数

3.4 ceil()函数

天花板函数

解释:取给定的数值转为最接近且大于等于它的整数

举例

select ceil(10.6);  --输出结果为11

3.5 floor()函数

地板函数

解释:取给定的数值转为最接近且小于等于它的整数

举例

select `floor`(10.6); --输出结果为10

3.6 pow()函数

求次方函数

格式:pow(参数1,参数2)

表示参数1的参数2次方

举例

select pow(3,2);  --表示3的2次方,结果为9

4.转换函数

4.1 if()函数

举例

select `if`(5>3,true ,false); --输出为true
select `if`(0<-1,true,false); --输出为flase

4.2 isnull()函数

判断是否为空

注意:只有为null时才是空

举例

select isnull('');  --输出为false
select isnull(null);  --输出为true
select isnull(0);   --输出为false

4.3 isnotnull()函数

判断是否不为空

举例

select isnotnull('');  --输出为true
select isnotnull(null);  --输出为false
select isnotnull(0);  --输出为true

4.4 nvl()函数

空值转换函数

判断参数1是否为空,不为空就返回参数1,为空就返回参数2

举例

select nvl(1,2);  --输出值为1
select nvl(null,2);  --输出值为2

4.5 coalesce()函数

格式:coalesce(参数1,参数2,参数3......)

返回第一个不为空的值

举例

select coalesce(1,2,3,4,5,6);  --输出结果为1
select coalesce(null,1,5,null,67); --输出结果为1
select coalesce(null,null,null,5,1,null,88,null);  --输出结果为5

4.6 case when 函数

格式 case when 条件 then 结果 else 结果

举例

select case
    when 5>3 then '大于'
    when 5>3 then '小于'
    else '等于'
end;   --结果为大于

case when 语法糖

select case 5
 when 1 then '星期一'
 when 2 then '星期二'
 when 3 then '星期三'
 when 4 then '星期四'
 when 5 then '星期五'
else '周末'
end;   --结果为星期五

4.7 cast()函数

用于转换类型

举例

select cast(1 as string);
select cast(10.5 as int);  --结果为10
select cast(10.3 as int);  --结果为10
select cast(15 as double);
select cast('123' as boolean);  --结果为true
select cast('0' as boolean);  --结果为false
select cast('' as boolean);   --结果为false

5.加密 脱敏相关函数

5.1 hash()函数

哈希函数

举例

select hash('asd123');

5.2 加密函数

md5,sha1,sha2,crc32

举例

select md5('123');  --加密后为202cb962ac59075b964b07152d234b70
select sha1('123');  --加密后结果为:40bd001563085fc35165329ea1ff5c5ecbdbbeef
select sha2('123',512);  --加密后结果为3c9909afec25354d551dae21590bb26e38d53f2173b8d3dc3eee4c047e7ab1c1eb8b85103e3be7ba613b31bb5c9c36214dc9f14a42fd7a2fdb84856bca5c44c2
select crc32('123');  --加密后结果为:2286445522

5.3 mask()函数

脱敏函数

默认为大写字母为X,小写字母为x,数字为n

举例

select mask('AAbb123'); --默认为大写字母为X,小写字母为x,数字为n 所以结果为XXxxnn

5.4 mask_first_n()函数

只脱敏前几个数

举例

select mask_first_n('AAbb123',4); --结果为XXxx123,只脱敏前四个数

5.5 mask_last_n()函数

只脱敏后几个数

举例

select mask_last_n('AAbb123',4);  --结果为AAbxnnn,只脱敏后四位数

6.其他函数

6.1 current_user()函数

查看当前用户函数

举例

select current_user();  --root

6.2 current_database()函数

查看当前使用的数据库

6.3 version()函数

查看当前Hive版本