入门篇3-hive常用函数

Aronup

已于 2023-08-02 14:09:07 修改

阅读量193

点赞数 1

文章标签： hive 数据仓库

于 2023-08-02 14:01:51 首次发布

快乐的小阿白

本文链接：https://blog.csdn.net/aronup/article/details/131768199

版权

常用函数202307

欢迎大家交流学习

数学函数

1.round(Double a[,INT b])
四舍五入
round(11.54) 结果 12
round(11.54，1) 结果 11.5

2.floor(Double a)
向下取整
floor(10.9) 结果：10
floor(-10.9) 结果：-11

3.ceil(Double a)
向上取整
ceil(10.9) 结果：11
ceil(-10.9) 结果：-10

4.rand(INT seed) 
产生0-1的随机数,seed随机因子,可不传
rand() ...

5.abs()取绝对值

日期函数

--当前日期
current_date();
--当前天数
day(current_date());
--当前月份
month(current_date());
--当前年份
year(current_date());
--当前时间
current_timestamp();
--当前小时
hour(current_timestamp());
--当前时间戳long类型
unix_timestamp();

日期加减

--1.date_add/date_sub 加减天数
date_add(DATE startdate,INT days)
date_add(current_date(),2) --加两天
date_add(current_date(),-2) --减两天

date_sub(DATE startdate,INT days)
date_sub(current_date(),-2) --加两天
date_sub(current_date(),2) --减两天


--2.add_months
add_months(DATE|STRING|TIMESTAMP startdate,INT months)
add_months(current_date(),2) --加两个月
add_months(current_date(),-2) --减两个月

--3.年份
current_date() + INTERVAL 1 year --加一年
current_date() - INTERVAL 1 year --减一年

ps:interval函数

日期转换函数

1.date_format
date_format(DATE|STRING|TIMESTAMP date,String fmt)
date_format(current_date(),'y') --获取当前年
date_format(current_date(),'M') --获取当前月
date_format(current_date(),'d') --获取当前天
==两个MM/dd 日期格式会带0，否则不带0==
date_format(current_date(),'Y-MM-dd') --获取当前年月日
date_format(current_date(),'YMMdd') --获取当前年月日不带-
date_format(current_date(),'Y-MM') --获取当前年月
date_format(current_date(),'YM') --获取当前年月 不带- 不带0   


date_format(current_timestamp(),'yyyy-MM-dd'). --获取日期格式
date_format('2023-07-18 19:50:10','yyyy-MM-dd HH:mm:ss') --字符串转时间戳

2.to_date()
to_date(current_timestamp()) --时间戳转日期

3.unix_timestamp()
unix_timestamp(current_timestamp()) --时间戳转long类型

4.from_unixtime()
转换unix时间戳为日期类型
from_unixtime(unix_timestamp(),'yyyy-MM-dd')

5.trunc()
trunc 截取日期
--本年1月1号
trunc(current_date(),'YEAR')  
trunc(current_date(),'YY') 
trunc(current_date(),'YYYY') 

--本月1号
trunc(current_date(),'MONTH')
trunc(current_date(),'MON')
trunc(current_date(),'MM')

日期差

1.datediff()获取相差的天数,前面减后面
datediff(current_date(),'2023-07-18') 

2.month_between(DATE|STRING|TIMESTAMP date1,DATE|STRING|TIMESTAMP date2) 获取相差的月份，前减后
month_between(current_date,'2023-07-18')

3.unix时间戳方式
(unix_timestamp(date1)-unix_timestamp(date2)) --相差的秒数
(unix_timestamp(date1)-unix_timestamp(date2))/60 --相差分钟数
...
...
...

字符串函数

字符串截取

1.split(String str,String pat),返回值是Array
按照pat去分割str
split("898978800","7")
结果:["8989","8800"]

2.substr(String|Binary a, INT start[,INT len]),返回值字符串
从start位置开始截取字符串，len截取长度，不写默认截取到末尾
substr("89757",1,2)
结果：89
substr("89757",3)
结果：757

字符串替换

replace(String a,String old,String new)
replace("7899","7","88")
结果：88899

字符串拼接

translate(String a,String old,String new) 
ps：测试只能替换new的长度要小于等于old，超出长度不会替换
eg：translate("889988","99","777")
结果：887788

collect_list(列名) 按照查询维度进行列转行，返回数组
collect_set(列名) 按照查询维度进行列转行，并去重，返回数组

聚合函数

count(列名) 按照查询维度统计个数
max(列名) 按照查询维度求最大值
sum(列名) 按照查询维度求和
min(列名) 按照查询维度求最小值
avg(列名) 按照查询维度求平均值

开窗函数

开窗函数不能和order by 一起使用，在全量的数据范围中，再划分出一个范围，按照划分的范围去统计数据，比如求一笔订单中，每个订单与最高订单价格的差额等等
功能：按照partition by的列做分组，然后在进行计算

sum(列名)over(partition by 列名 order by 列名) 加order by 会按照分组后按照排序列求累计值
max(列名)over(partition by 列名 order by 列名) 
min(列名)over(partition by 列名 order by 列名)

数据：20 20 30 50 60 （升序排列）
row_number()over(partition by 列名 order by 列名) 分组排序
结果：1 2 3 4 5
rank()over(partition by 列名 order by 列名)
结果： 1 1 3 4 5
dense_rank()over(partition by 列名 order by 列名)
结果：1 1 2 3 4 

......

集合函数

1.size(Map<K,V>|Array<T> a) 返回包含元素的个数
size(map("他",33,"她",44)) 结果：2 
size(array(1,2,3,4,5,6)) 结果：6

2.map_keys()
获取map的键值,返回数组
map_keys(map("他",33,"她",44)) 结果：["他","她"]

3.map_values()
获取map的值，返回数组
map_values(map("他",33,"她",44)) 结果：[33,33]

5.array_contains(array<T>,val)
判断数组是否包含val，返回boolean值
array_contains(array(1,2,3,4,5,6),6) 结果：true

6.sort_array()
对数组进行排序
sort_array(array(5,3,2,4,1)) 结果：1，2，3，4，5

爆炸函数💣

explode(列名) 将map、数组拆分成多行,使用爆炸函数之后不能在使用其他的列，所以一般配合侧视图(lateral view)使用

eg：mans
------｜----------
age	  ｜  name		
------｜----------
20	  ｜["小明","小刚","小狗"]
21    ｜["小月","小鬼","大鬼"]
------------------
select age,name.value from 
mans 
lateral view explode(name) name as value;

结果:
------｜----------
age	  ｜  name		
------｜----------
20	  ｜ 小明
20	  ｜ 小刚
20	  ｜ 小狗
21    ｜ 小月
21	  ｜ 小鬼
21	  ｜ 大鬼
------------------

其他

distinct 去重,慎用
order by 全局排序,慎用
distribute by 分发数据，默认对分发列做hash，然后对reduce的个数取模，相同的分到一个reduce上
sort by 局部排序，一般配合distribute by 一起使用
cluster by 兼具distribute by 和 sort by 的功能，只能升序排列

regexp系列

1.regexp_extract

regexp_extract(String subject,String pattern,INT index)
按照pattern中的正则表达式返回结果,其中index代表pattern中分组的序号
ps:pattern中()表示一个分组，另外不是按照分组重新匹配,而是在匹配结果的基础上返回分组匹配到的数据
0:代表按照整个分组返回匹配结果
1:代表按照pattern中的第一个分组返回结果
2:代表按照pattern中的第二个分组返回结果
....
n:代表按照pattern中的第n个分组返回结果

eg：
01.select regexp_extract("小明去了姑姑家5天,five days...","([0-9].).(five)",0)
结果：5天,five

02.select regexp_extract("小明去了姑姑家5天,five days...","([0-9].).(five)",1)
结果：5天

03.select regexp_extract("小明去了姑姑家5天,five days...","([0-9].).(five)",2)
结果：five

2.regexp_replace

regexp_replace(String initial_string,String pattern,String replacement)
根据pattern中的表达式匹配结果，替换为replacement
eg:
01.select regexp_replace("小明去了姑姑家5天,five days...","[0-9]",10)
(把数字替换为10)
结果:小明去了姑姑家10天,five days...

02.select regexp_replace("小明去了姑姑家5天,five days...","\\.*","")
(去除.)
结果:小明去了姑姑家5天,five days