Hive学习---4、函数（单行函数、高级聚合函数、炸裂函数、窗口函数）

1、函数

1.1 函数简介

Hive会将常用的逻辑封装成函数给用户进行使用类似java中的函数。
好处避免用户反复写逻辑可以直接拿来使用
重点用户需要知道函数叫什么能做什么

Hive提供了大量的内置函数按照其特点大致可分为如下几类单行函数、聚合函数、炸裂函数、窗口函数。

以下命令可用于查询所有内置函数的相关信息

--1、查看系统内置函数
show functions

--2、查看内置函数用法
desc function upper

--3查看内置函数详细信息
desc function extended upper

1.2 单行函数

单行函数的特点是一进一出即输入一行输出一行。
单行函数按照功能可分为如下几类日期函数、字符串函数、集合函数、数学函数、流程控制函数。

1.2.1 算术运算函数

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A	B
A^B	A和B按位取异或
~A	A按位取反

--查询出所有员工的薪水后加100显示。
select sal+100 from emp;

1.2.2 数值函数

1、round四舍五入

select round(3.3) ;   3

2、ceil向上取整

select ceil(3.1);   4

3、floor向下取整

select floor(6.8);   6

1.2.3 字符串函数

1、substring截取字符串

--语法1
--substring(string A,int start)
--返回值string
--说明返回字符串A从start位置到结尾的字符串

--1获取第二个字符以后得所有字符
select substring("zhmagoodboy",2);  hmagoodboy

--2获取倒数第三个字符以后得所有字符
select substring("zhmagoodboy",-3)
boy

--语法2
--substring(string A,int start,int len)
--返回值string
--说明返回字符串A从start位置开始长度为len的字符串

--从第三个字符开始向后获取2个字符
select substring("zhmagoodboy",3,2)
ma

2、replace替换

--语法replace(string A, string B, string C) 
--返回值string
--说明将字符串A中的子字符串B替换为C。

select regexp_replace('zhm','h','H')   zHm

3、regexp_replace:正则替换

--语法regexp_replace(string A,string B,string C)
--返回值string
--说明将字符串A中的符合java正则表达式B的部分替换为C。注意在有些情况下要使用转义字符。

select regexp_replace('100-200','(\\d+)','num') num-num

4、regexp正则匹配

--语法字符串 regexp 正则表达式
--返回值boolean
--说明若字符串符合正则表达式则返回true否则返回false。
--(1)正则匹配成功输出true
select 'dfsaaaa' regexp 'dfsa+'; true

--(2)正则匹配失败输出false
select 'dfsaaaa' regexp 'dfsb+'; false

5、repeat重复字符串

--语法repeat(string A, int n)
--返回值string
--说明将字符串A重复n遍。
select repeat('123',2);  123123

6、spilt字符串切割

--语法split(string str, string pat) 
--返回值array
--说明按照正则表达式pat匹配到的内容分割str分割后的字符串以数组的形式返回。
select spilt('a-b-c-d','-');  ["a","b","c","d"]

7、nvl替换null值

--语法nvl(A,B) 
--说明若A的值不为null则返回A否则返回B。 
select nvl(null,1);  1

8、concat拼接字符串

--语法concat(string A, string B, string C, ……) 
--返回string
--说明将A,B,C……等字符拼接为一个字符串

select concat('beijing','-','shanghai','-','shenzhen')  beijing-shanghai-shenzhen

9、concat_ws:以指定分割符拼接字符串或者字符串数组

--语法concat_ws(string A, string…| array(string)) 
--返回值string
--说明使用分隔符A拼接多个字符串或者一个数组的所有元素。

select concat_ws('-','beijing','shanghai','shenzhen'); beijing-shanghai-shenzhen

10、get_json_object解析json字符串

--语法get_json_object(string json_string,string path)
--返回值string
--说明解析json的字符串json_string,返回path指定的内容。如果输入的json字符无效那么返回null。

--1获取json数组里面取得json具体数据
select get_json_object('[{"name":"邹哥哥","sex":"男","age":"24"},{"name":"邹小哥哥","sex":"男","age":"18"}]','$.[0].name');   邹哥哥

--2获取json数组里面的数据
 select get_json_object('[{"name":"邹哥哥","sex":"男","age":"24"},{"name":"邹小哥哥","sex":"男","age":"18"}]','$.[0]');
输出{"name":"邹哥哥","sex":"男","age":"24"}

1.2.4 日期函数

1、unix_timestamp:返回当前或指定时间的时间戳

--语法unix_timestamp()
--返回值bigint
select unix_timestamp('2022/08/08','yyyy/MM/dd HH-mm-ss')
输出:1659946088

2、from_unixtime转化UNIX时间戳从 1970-01-01 00:00:00 UTC 到指定时间的秒数到当前时区的时间格式

--语法from_unixtime(bigint unixtime[, string format]) 
--返回值string 
select from_unixtime(1659946088);   
输出2022-08-08 08:08:08

3、current_date当前日期

select current_date;  
输出2023-06-2

4、current_timestamp当前的日期加时间并且精确的毫秒

select current_timestamp; 
输出2023-06-2 15:32:22.402

5、month获取日期中的月

--语法month (string date) 
--返回值int 
select month('2023-06-02 08:08:08');
输出6

6、day获取日期中的日

--语法day (string date) 
--返回值int 
select day('2023-06-02 08:08:08');
输出2

7、day获取日期中的小时

--语法hour (string date) 
--返回值int 
select hour('2023-06-02 08:08:08');
输出8

8、datediff两个日期相差的天数结束日期减去开始日期的天数

--语法datediff(string enddate, string startdate) 
--返回值int 
select datediff('2021-08-08','2022-10-09')
输出-427

9、data_add:日期加天数

--语法date_add(string startdate, int days) 
--返回值string 
--说明返回开始日期 startdate 增加 days 天后的日期
select data_add('2023-06-02',2);
输出2023-06-04

10、date_sub日期减天数

--语法date_sub (string startdate, int days) 
--返回值string 
--说明返回开始日期startdate减少days天后的日期。

select data_sub('2023-06-04',2);
输出2023-06-02

11、date_format:将标准日期解析成指定格式字符串

select date_format('2023-06-02','yyyy年-MM月-dd日')
输出2023年-06月-02月

1.2.5 流程控制函数

1、case when条件判断函数

--语法1case when a then b[when c then d] * [else e] end
--返回值T
--说明如果a是true 则返回b如果c为true 则返回d否则返回e

select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from table ename;
输出结果mary

--语法2case a when b then c [when d then e]*[else f] end
--返回值T
--说明如果a等于b那么返回c如果a等于d那么返回e否则返回f

select case 100 when 50 then 'tom' when 100 then 'mary' 
else 'tim' end from tableName;
输出结果mary

2、if条件判断类型java中的三元运算符

-- 语法if(boolean testCondition,T valueTrue,T valueFalseOrNull)
-- 返回值T
-- 说明当条件testCondition为true时返回valueTrue否则返回valueFalseOrNull
--(1)条件满足输出正确
select if(10>5,'正确','错误');
输出正确
--(2)条件满足输出错误
select if(10<5,'正确','错误');
输出错误

1.2.6 聚合函数

1、size集合中元素个数

select size(friends) from test;  --2/2  每一行数据中的friends集合里的个数

2、map创建map集合

--语法map(key1,value1,key2,value2,...)
--说明根据输入的key和value对构建map类型
select map('zhm',1,'zhm2',2);
输出{"zhm":1,"zhm2":2}

3、map_keys返回map中的key

select map_keys(map('zhm',1,'zhm2',2))
输出["zhm","zhm1"]

4、map_keys返回map中的key

select map_values(map('zhm',1,'zhm2',2))
输出[1,2]

5、array声明array集合

--语法array(val1,val2,...)
--说明根据输入的参数构建数组array类
select array('1','2','3','4');
输出["1","2","3","4"]

6、array_contains判断array中是否包含某元素

select array_contains(array('a','b','c'),'a');
输出true

7、sort_array将array中的元素排序

select sort_array(array('a','d','c'))
输出['a','c','d']

8、struct声明struct中的各属性

--语法struct(val1,val2,val3,...)
--说明根据输入的参数构建结构体struct类
select struct('name','age','weight');
输出{"col1":"name","col2":"age","col3":"weight"}

9、named_struct声明struct的属性和值

select named_strcut('name','zhm','age',18,'weight',80);
输出{"name":"zhm","age":18,"weight":80}

1.3 高级聚合函数

多进一出多行传入一行输出
1、collect_list() 收集并形成list集合结果不去重

select 
  sex,
  collect_list(job)
from employee
group by  sex

结果
女 [“行政”,“研发”,“行政”,“前台”]
男 [“销售”,“研发”,“销售”,“前台”]

2、collect_set() 收集并形成set集合结果去重

select 
  sex,
  collect_set(job)
from employee
group by  sex

结果
女 [“行政”,“研发”,“前台”]
男 [“销售”,“研发”,“前台”]

1.4 炸裂函数UDTF

UDTFTable-generating Functions,接受一行数据输出一行或多行数据

1、数据准备
1表结构

movie	category
《疑犯追踪》	悬疑动作科幻剧情
《Lie to me》	悬疑警匪动作心理剧情
《战狼2》	战争动作灾难

2建表语句

create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) 
row format delimited fields terminated by "\t";

3输入数据

insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),("《战狼2》", "战争,动作,灾难");

2、需求
1根据上述电影信息表统计各分类的电影数量期望结果如下

剧情	2
动作	3
心理	1
悬疑	2
战争	1
灾难	1
科幻	1
警匪	1

2代码实现

select cate count(*)
from (
	select movie,cate
	from (
		select movie 
		spilt(category,',') cates
		from movie_info
	) t1 lateral view explode(cates) tmp as cate
) t2
group by cate;

1.5 窗口函数开窗函数

1.5.1 概述

窗口函数能为每行数据划分一个窗口然后对窗口范围内的数据进行计算最后将计算结果返回给该行的数据。

1.5.2 常用窗口函数

按照功能常用窗口可划分如下几类聚合函数、跨行取值函数、排名函数。
1、聚合函数
max最大值。
min最小值。
sum求和。
avg平均值。
count计数。
2、跨行取值函数
1lead和lag
获取当前行的上/下边某行、某个字段的值
注意lead和lag函数不支持自定义窗口

2first_value和last_value
获取窗口内某一列的第一个值/最后一个值

3、排名函数
常用的排名函数–rank、dense_rank、row_number
功能计算排名

1.6 自定义函数

1、hive自带了一些函数比如max/min等但是数量有限自己可以通过自定义UDF来方便扩展。
2、当Hive提供的内置函数无法满足你的业务处理需求时此时就可以考虑使用用户自定义函数UDFuser-defined function。
3、根据用户自定义函数类别分为以下三种
1UDFUser-Defined-Function
一进一出
2UDAFUser-Defined Aggregation Function
用户自定义聚合函数多进一出
3UDTFUser-Defined table-Generating Functions
用户自定义表生成函数一进多出

1.7 自定义UDF函数