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：条件判断函数

--语法1：case 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

--语法2：case 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）

UDTF（Table-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、跨行取值函数
（1）lead和lag
获取当前行的上/下边某行、某个字段的值
注意：lead和lag函数不支持自定义窗口

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

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

1.6 自定义函数

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

1.7 自定义UDF函数