hive函数总结

abs：绝对值

select abs(-5);

array：用给定的元素创建一个数组

select array('1','ad','aa','2021-10-07');

array_contains：如果数组包含返回true，否则false

select array_contains(array('a','b','c'),'e');

ascii：返回参数第一个字符的ASCII

select ascii('string');

avg：平均值

select avg(x);

bin：以二进制形式返回参数

select bin(3);

cbrt：返回双精度值得立方根

select cbrt(8);

ceil：返回大于等于参数得最小整数

select ceil(1.2);

select ceiling(0.2);

coalesce：返回第一个非空参数

select coalesce(1,null);

select coalesce(null,1);

select coalesce(null,null,1);

collect_list：返回具有重复项的集合

select collect_list(x);

collect_set：返回消除了重复项的集合

select collect_set(x);

concat：将参数连接起来

select concat('a','=','1');

concat_ws：返回由分隔符分隔的参数得串联

select concat_ws(',',array('a=1','b=2','c=3'));

count：计算行数

select count(*);

current_database：返回当前使用的数据库名称

select current_database();

时间相关

--current_date：返回当前日期
select current_date();

--current_timestamp：返回当前的时间戳
select current_timestamp();

--add_months：加减月份
select add_months('2021-10-07',-2);
select add_months('2021-10-07',1);

--date_add：加日期
select date_add('2021-10-07',-1);

--date_sub：减日期
select date_sub('2021-10-07',2);

--date_format：将日期、时间戳、字符串格式化为指定的格式
select date_format(current_timestamp(),'yyyyMMdd');

--datediff：日期相减，返回日期1与日期2之间的天数
select datediff('2021-10-08','2021-10-01');

--dayofmonth：返回日期、时间戳中的日
select dayofmonth('2021-10-07');

--last_day：返回日期所属月份的最后一天
select last_day('2021-02-02');

--months_between：返回时间1和时间2之间的月份差
select months_between('2021-10-07 12：30：30','2021-02-07 12：30：30');

--to_date：提取日期或日期表达式的日期部分
select to_date('2021-10-07 12：51：30');

--to_unix_timestamp：根据日期格式返回时间戳
select to_unix_timestamp('2021-10-07 12：51：30');
select to_unix_timestamp('20211007','yyyyMMdd');

--unix_timestamp：根据日期格式返回时间戳
select unix_timestamp('2021-10-07 12：51：30')
select unix_timestamp('20211007','yyyyMMdd');

--from_unixtime：将毫秒值格式化为指定格式日期
select from_unixtime(UNIX_TIMESTAMP('20211007','yyyyMMdd'),'yyyy-MM-dd');

--weekofyear：返回给定日期是一年中的第几周
select weekofyear('2021-10-13')

--next_day：返回时间参数之后的星期几
select next_day('2021-10-07','mon');

--year：返回日期、时间戳中的年份
select year('2021-10-13');

--second：返回字符串、时间戳中的秒
select second('2021-10-13 16：22：30');

--month：返回字符串、时间戳中的月份
select month('2021-10-07 12：30：30');

--day：返回日期、时间戳中的日
select day('2021-10-07');

--hour：返回字符串、时间戳中的小时
select hour('2021-10-07 12：30：30');

--minute：返回字符串、时间戳中的分钟
select minute('2021-10-07 12：30：30');

返回当前的用户名

select current_user();

elt：返回第n个字符串

select elt(2,'a','b','c');

explode：将数组中的元素分成多行（lateral view explode）

select explode(array('a','b','c'))

find_in_set：返回逗号分隔得字符串中参数以1出现的位置

select find_in_set('b=2',concat_ws(',',array('a=1','b=2','c=3')))

floor：找出小于等于参数得最大整数

select floor(2.2);

format_number：将数字四舍五入，保留参数2隔小数位数

select format_number(2.34567,2);

get_json_object：从参数二的路径中提取一个json对象

select get_json_object('{"name"："xxx","age"：15,"sex"："nan"}','$.name');

select get_json_object('[{"name"："xxx","age"：15,"sex"："nan"},{"name"："xxxxx","age"：16,"sex"："nv"}]','$[1].name');

json_tuple：类似get_json_object

select json_tuple('{"name"："xxx","age"：15,"sex"："nan"}','name','age','sex');

greatest：返回值列表中的最大值

select greatest(1,3,2,5,4);

if：参数1为true，返回参数2，否则返回参数3

select if(false,1,2);

index：返回参数1中的指定元素

select index(array('a','b','c'),2);

initcap：返回首字母大写的单词

select initcap('str hello bigdata');

inline：将数组中的结构体炸到一个表中

select inline(array(named_struct('name','hive','age',15),named_struct('name','hadoop','age',20)));

instr：返回参数二在参数一中出现的索引

select instr('astrbcstr','t');

lcase：将所有字符改为小写

select lcase('Hello Hadoop');

least：返回值列表中的最小值

select least(7,3,2,5,4);

length：返回字符串长度

select length('hive');

locate：返回指定位置之后第一次出现参数一的位置

select locate('str','astrbcstr',3)

lower：将所有字符改为小写

select lower('Hello Hadoop');

lpad：返回参数1，左侧用参数3填充至长度为参数2

select lpad('123',5,'0');

rpad：返回用参数3，右侧填充至参数2位的参数1

select rpad('123',8,'0');

ltrim：删除参数前面的空格

select ltrim('  str');

rtrim：去除右侧的空格

select rtrim('asc   ');

map：使用给定键值对创建map

select map('name','hive','age',15);

map_values：返回map中的key

select map_keys(map('name','hive','age',15));

map_values：返回map中的value

select map_values(map('name','hive','age',15));

max：最大值

select max(x);

min：最小值

select min(x);

named_struct：使用给定字段名和值创建结构体

select named_struct('name','hive','age',15);

nvl：如果参数1为null，返回参数二；否则返回参数1

select nvl(1,null);

select nvl(null,1);

parse_url：解析网址

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'PROTOCOL');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'HOST');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'REF');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'PATH');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'QUERY');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'FILE');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'AUTHORITY');

select parse_url('https：//www.baidu.com/s?ie=UTF-8&wd=lol', 'USERINFO');

posexplode：类似于explode，但包括原始数组中元素的位置

select posexplode(array('a','b','c'));

regexp_extract：提取匹配的正则表达式组，0表示把整个正则表达式对应的结果全部返回，1表示返回正则表达式中第一个() 对应的结果 以此类推

select regexp_extract('123456789','(2)([0-9]{3})(.*)',0);

regexp_replace：替换符合正则表达式的字符串

手机号中间四位脱敏
select regexp_replace('13503571234','([0-9]{3})([0-9]{4})','$1****');
姓名脱敏：
select if(length('山鬼谣')=2,regexp_replace('山鬼谣','(.{1})(.+)','$1*'),regexp_replace('山鬼谣','(.{1})(.+)','$1**'));

repeat：重复字符串n次

select repeat('ac',2);

reverse：字符串反转

select reverse('qwertyui');

round：四舍五入，保留几位小数

select round(123.456,2);

sort_array：将输入的数组按照自然顺序升序排序

select sort_array(array('1','2','33','11','22'))

split：切割字符串

select split('中国,北京,三里屯',',')

str_to_map：将字符串转换为map

select str_to_map('name=张三&age=18&sex=man','&','=')

substr：返回从参数2开始，参数三长度的字串

select substr('abcdefg',2,3)

substring：返回从参数2开始，参数三长度的字串

select substring('abcdefg',2,3)

sum：求和

select sum(x);

trim：去除字符串前后的空格

select trim(' asda ');

upper：将所有字符串变成大写

select upper('abcd');

case when：条件语句

CASE WHEN when_value THEN
		statement_list
	ELSE
		statement_list
END

窗口函数

row_number() over(partation ... order by ...) 生成序号：1,2,3,4
rank() over(partation ... order by ...) 生成序号：1,2,2,4
dense_rank() over(partation ... order by ...) 生成序号：1,2,2,3

base64

select base64(binary('abc'));
select unbase64('YWJj');