67-94-hive-函数-开窗函数-常用函数-udf自定义函数

67-hive-函数：

UDF:一进一出，普通函数

UDAF:多进一出，聚合函数

UDTF：一进多出，炸裂函数

一、多指的是输入数据的行数。一行变多行，多行变一行。

函数

#查看系统自带的函数
hive> show functions;
#显示自带的函数的用法
hive> desc function upper;
#详细显示自带的函数的用法
hive> desc function extended upper;

NVL：给值为 NULL 的数据赋值，它的格式是 NVL( value，default_value)。它的功能是如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值。

select comm,nvl(comm, -1) from emp;

CASE WHEN THEN ELSE END

select dept_id,sum(case sex when '男' then 1 else 0 end) male_count,sum(if(sex='女',1,0) then 1 else 0 end) female_count from emp_sex group by dept_id;

concat字符串拼接

#数据
孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A
苍老师  白羊座  B
#建表
create table person_info(name string, constellation string, blood_type string) row format delimited fields terminated by "\t";

#load数据
load data local inpath "/opt/module/hive/datas/person_info.txt" into table person_info;

#查询
SELECT t1.c_b,CONCAT_WS("|",collect_set(t1.name)) FROM (SELECT NAME, CONCAT_WS(',',constellation,blood_type) c_b FROM person_info)t1 GROUP BY t1.c_b

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,…)：特殊的 CONCAT()。参数一为剩余参数间的分隔符。

注意: CONCAT_WS must be "string or array

COLLECT_SET(col)：只接受基本数据类型，将某字段的值进行去重汇总，产生 Array 类型字段。COLLECT_LIST(col)不去重。

EXPLODE(col)：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

#准备数据
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》悬疑,警匪,动作,心理,剧情
《战狼 2》 战争,动作,灾难
#建表
create table movie_info(movie string,category string) row format delimited fields terminated by "\t";
#导入数据
load data local inpath "/opt/module/hive/datas/movie_info.txt" into table movie_info;
#查询
select movie,category_name from movie_info lateral view explode(split(category,",")) movie_info_tmp as category_name;

窗口函数（开窗函数）

相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行

n PRECEDING：往前 n 行数据

n FOLLOWING：往后 n 行数据

UNBOUNDED：起点，

UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING 表示到后面的终点

LAG(col,n,default_val)：往前第 n 行数据

LEAD(col,n, default_val)：往后第 n 行数据

NTILE(n)：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对于每一行，NTILE 返回此行所属的组的编号。注意：n 必须为 int 类型。

#数据
jack,2017-01-01,10
tony,2017-01-02,15
jack,2017-02-03,23
tony,2017-01-04,29
jack,2017-01-05,46
jack,2017-04-06,42
tony,2017-01-07,50
jack,2017-01-08,55
mart,2017-04-08,62
mart,2017-04-09,68
neil,2017-05-10,12
mart,2017-04-11,75
neil,2017-06-12,80
mart,2017-04-13,94

#建表
create table business(name string,orderdate string,cost int) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

#加载数据
load data local inpath "/opt/module/hive/datas/business.txt" into table business;

（1） 查询在 2017 年 4 月份购买过的顾客及总人数

初理解over(),所谓开窗及在最后查的数据，对没一条数据都进行一个新的窗口或者语句。
select name,count(*) over () from business where substring(orderdate,1,7) = '2017-04' group by name;

相比较
select name,count(*) over () from business;#对表中所有数据都开一个窗口进行count计算。
select name,count(*) from business where substring(orderdate,1,7) = '2017-04' group by name;#不加over，即对没一个组进行count

（2） 查询顾客的购买明细及月购买总额

select name,orderdate,cost,sum(cost) over(partition by name,month(orderdate)) from business;

（3） 将每个顾客的 cost 按照日期进行累加

select name,orderdate,cost,
sum(cost) over() as sample1,--所有行相加
sum(cost) over(partition by name) as sample2,--按 name 分组，组内数据相加
sum(cost) over(partition by name order by orderdate) as sample3,--按 name
分组，组内数据累加
sum(cost) over(partition by name order by orderdate rows between 
UNBOUNDED PRECEDING and current row ) as sample4 ,--和 sample3 一样,由起点到
当前行的聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING and current row) as sample5, --当前行和前面一行做聚合
sum(cost) over(partition by name order by orderdate rows between 1 
PRECEDING AND 1 FOLLOWING ) as sample6,--当前行和前边一行及后面一行
sum(cost) over(partition by name order by orderdate rows between current 
row and UNBOUNDED FOLLOWING ) as sample7 --当前行及后面所有行
from business;

rows 必须跟在 order by 子句之后，对排序的结果进行限制，使用固定的行数来限制分区中的数据行数量

（4） 查看顾客上次的购买时间

select name,orderdate,cost,lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1 from business; 
select name,orderdate,cost,lag(orderdate,2) over (partition by name order by orderdate) as time2 from business; 

（5） 查询前 20%时间的订单信息

select * from (select name,orderdate,cost, ntile(5) over(order by orderdate) sorted from business)  t
where sorted = 1;

Rank

1）函数说明

RANK() 排序相同时会重复，总数不会变

DENSE_RANK() 排序相同时会重复，总数会减少

ROW_NUMBER() 会根据顺序计算

#数据
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78
#建表
create table score(name string,subject string, score int) row format delimited fields terminated by "\t";
#加载数据
load data local inpath '/opt/module/hive/datas/score.txt' into table score;
#计算每门学科成绩排名。
select name,subject,score,rank() over(partition by subject order by score desc) rp from score;
select name,subject,score,dense_rank() over(partition by subject order by score desc) drp from score;
select name,subject,score,row_number() over(partition by subject order by score desc) rmp from score;
#求出每门学科前三名的学生？
select name,subject,score from (select name,subject,score,rank() over(partition by subject order by score desc) rp from score) t1 where rp<=3;

其他常用函数，desc function unix_timestamp

常用日期函数
select unix_timestamp();										#返回当前或指定时间的时间戳
select unix_timestamp("2020-10-28",'yyyy-MM-dd');				#返回当前或指定时间的时间戳
select from_unixtime(1603843200);								#将时间戳转为日期格式
select current_date;											#当前日期
select current_timestamp;										#当前的日期加时间
select to_date('2020-10-28 12:12:12');							#抽取日期部分
select year('2020-10-28 12:12:12');								#获取年
select month('2020-10-28 12:12:12');							#获取月
select day('2020-10-28 12:12:12');								#获取日
select hour('2020-10-28 12:12:12');								#获取时
select minute('2020-10-28 12:12:12');							#获取分
select second('2020-10-28 12:12:12');							#获取秒
select weekofyear('2020-10-28 12:12:12');						#当前时间是一年中的第几周
select dayofmonth('2020-10-28 12:12:12');						#当前时间是一个月中的第几天
select months_between('2020-04-01','2020-10-28');				#两个日期间的月份
select add_months('2020-10-28',-3);								#日期加减月
select datediff('2020-11-04','2020-10-28');						#两个日期相差的天数
select date_add('2020-10-28',4);								#日期加天数
select date_sub('2020-10-28',-4);								#日期减天数
select last_day('2020-02-30');									#日期的当月的最后一天
select date_format('2020-10-28 12:12:12','yyyy/MM/dd HH:mm:ss');#格式化日期

常用取整函数
select round(3.14);												#四舍五入
select ceil(3.14);												#向上取整
select floor(3.54);												#向下取整

常用字符串操作函数
select upper('low');											#转大写
select lower('low');											#转小写
select length("atguigu");										#长度
select trim(" atguigu ");										#前后去空格
select lpad('atguigu',9,'g');									#向左补齐，到指定长度
select rpad('atguigu',9,'g');									#向右补齐，到指定长度
SELECT regexp_replace('2020/10/25', '/', '-');					#使用正则表达式匹配目标字符串，匹配成功后替换！

集合操作
select size(friends) from test3;#集合中元素的个数
select map_keys(children) from test3;#返回map中的key
select map_values(children) from test3;# 返回map中的value
select array_contains(friends,'bingbing') from test3;#判断array中是否包含某个元素
select sort_array(friends) from test3;#将array中的元素排序

grouping_set:多维分析

自定义函数

1）Hive 自带了一些函数，比如：max/min 等，但是数量有限，自己可以通过自定义 UDF 来

方便的扩展。

2）当 Hive 提供的内置函数无法满足你的业务处理需要时，此时就可以考虑使用用户自定义

函数（UDF：user-defined function）。

3）根据用户自定义函数类别分为以下三种：

（1）UDF（User-Defined-Function）一进一出

（2）UDAF（User-Defined Aggregation Function）聚集函数，多进一出，类似于：count/max/min

（3）UDTF（User-Defined Table-Generating Functions）一进多出，如 lateral view explode()

4）官方文档地址https://cwiki.apache.org/confluence/display/Hive/HivePlugins

5）编程步骤：

（1）继承 Hive 提供的类

org.apache.hadoop.hive.ql.udf.generic.GenericUDF

org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;

（2）实现类中的抽象方法

（3）在 hive 的命令行窗口创建函数

添加 jar

add jar linux_jar_path

创建 function

create [temporary] function [dbname.]function_name AS class_name;

（4）在 hive 的命令行窗口删除函数

drop [temporary] function [if exists] [dbname.]function_name;

自定义 UDF 函数

0）需求:

自定义一个 UDF 实现计算给定字符串的长度，例如：

hive(default)> select my_len(“abcd”);

路径：https://gitee.com/HaoZhouRS/bigdata-study-code/tree/master/big-data-study/Hive-UDF

1）打成 jar 包上传到服务器/opt/module/data/myudf.jar

2）将 jar 包添加到 hive 的 classpath

hive (default)>add jar /opt/module/hive/mylibs/Hive-UDF-1.0-SNAPSHOT.jar;

3）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function my_len as "com.zhhive.demo.MyUDF";

4）即可在hql中使用自定义的函数

hive (default)> select my_len(name) from business;

hive (default)> select my_len(“aaaaa”);

自定义 UDTF 函数

1）需求

自定义一个 UDTF 实现将一个任意分割符的字符串切割成独立的单词，例如：

hive(default)> select myudtf("hello,world,hadoop,hive", ",");
hello
world
hadoop
hive

2）打成 jar 包上传到服务器/opt/module/hive/data/myudtf.jar

3）将jar 包添加到 hive 的 classpath 下

hive (default)> add jar /opt/module/hive/data/myudtf.jar;

4）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function myudtf as “com.zhhive.demo.MyUDTF”;

5）使用自定义的函数hive (default)> select myudtf(“hello,world,hadoop,hive”,“,”);

grouping_set:多维分析（自行了解）

2）打成 jar 包上传到服务器/opt/module/hive/data/myudtf.jar

3）将jar 包添加到 hive 的 classpath 下

hive (default)> add jar /opt/module/hive/data/myudtf.jar;

4）创建临时函数与开发好的 java class 关联

hive (default)> create temporary function myudtf as “com.zhhive.demo.MyUDTF”;

5）使用自定义的函数hive (default)> select myudtf(“hello,world,hadoop,hive”,“,”);

grouping_set:多维分析（自行了解）