第一章 函数概念
- 函数有库的概念，除了系统提供的函数之外，系统的提供的函数可以在任意库使用
1.1 函数相关操作
show functions;
- 查看函数的使用，加上extended表示查看函数的详细使用
desc function [extended] 函数名;
1.2 函数分类
1.2.1 按来源分类
- 系统函数：系统自带的，可以直接使用
- 用户自定义函数
- 遵守hive函数类的要求，自定义一个函数类
- 打包函数，放入到hive的lib目录下，或者在HIVE_HOME目录下新建一个auxlib目录用来存放，hive可以自动加载auxlib下存放的第三方jar包目录
- 创建一个函数，让这个函数和之前编写的类关联，函数有库的概念
- 使用函数
1.2.2 按特征来分类
- UDF：用户定义的函数。 一进一出。 输入单个参数，返回单个结果。
- UDTF：用户定义的表生成函数。 一进多出。传入一个参数(集合类型)，返回一个结果集。
- UDAF：用户定义的聚集函数。 多进一出。 传入一列多行的数据，返回一个结果(一列一行) 。
第二章 常用函数
2.1 常用日期函数
- hive默认解析的日期必须是： 2019-11-24 08:09:10
- 常用的日期函数如下，具体用法可以使用desc命令查看，hive中给出了示例
unix_timestamp:返回当前或指定时间的时间戳
from_unixtime：将时间戳转为日期格式
current_date：当前日期
current_timestamp：当前的日期加时间
to_date：抽取日期部分
year：获取年
month：获取月
day：获取日
hour：获取时
minute：获取分
second：获取秒
weekofyear：当前时间是一年中的第几周
dayofmonth：当前时间是一个月中的第几天
months_between： 两个日期间的月份，前-后
add_months：日期加减月
datediff：两个日期相差的天数，前-后
date_add：日期加天数
date_sub：日期减天数
date_format：格式化日期
last_day：日期的当月的最后一天
2.2 常用取整函数
- 常用的取整函数如下，具体用法可以使用desc命令查看，hive中给出了示例
round：四舍五入
ceil：向上取整
floor：向下取整
2.3 常用的字符串操作函数
- 常用的字符串操作函数如下，具体用法可以使用desc命令查看，hive中给出了示例
upper：转大写
lower：转小写
length：长度
trim：前后去空格
lpad：向左补齐，到指定长度
rpad：向右补齐，到指定长度
regexp_replace：使用正则表达式匹配目标字符串，匹配成功后替换！
2.4 常用集合操作函数
- 常用的集合操作函数如下，具体用法可以使用desc命令查看，hive中给出了示例
size：集合（map和list）中元素的个数
map_keys：返回map中的key
map_values: 返回map中的value
array_contains: 判断array中是否包含某个元素
sort_array：将array中的元素排序
2.5 其他常用函数
2.5.1 函数介绍
- NVL函数：判断string1是否为null，如果为null，使用replace_with替换null，否则不做操作
- 使用场景
- 格式：
NVL(string1,replace_with)
- concat函数：字符串拼接。 可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回Null
- concat_ws: 使用指定的分隔符完成字符串拼接，第一个参数是分隔符，后面的参数可以是字符串，也可以是数组
- 格式：
concat_ws(分隔符,[string | array<string>]+);
- collect_set: 将此列的多行记录合并为一个set集合，去重。
- 格式：
collect_set(列名)
- collect_list：将此列的多行记录合并为一个set集合，不去重
- 格式：
collect_list(列名)
- explode：参数只能是array或map，将array类型转换为1列N行，将map类型参数转换为2列N行
- 格式：
explode(列名)
2.5.2 练习
name dept_id sex
悟空 A 男
大海 A 男
宋宋 B 男
凤姐 A 女
婷姐 B 女
婷婷 B 女
- 需求：每个部门男女各有多少人
- 使用判断函数case...when，格式如下：
case 列名
when 值1 then 值2
when 值3 then 值4
...
else 值5
end
select dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;
- 使用if函数，格式如下，表达式值为真时，返回值1，否则返回值2
if(判断表达式,值1,值2)
select dept_id,
sum(if(sex='男',1,0)) male_count,
sum(if(sex='女',1,0)) female_count
from emp_sex
group by dept_id;
name constellation blood_type
孙悟空 白羊座 A
大海 射手座 A
宋宋 白羊座 B
猪八戒 白羊座 A
凤姐 射手座 A
射手座,A 大海|凤姐
select concat(constellation,',',blood_type),concat_ws('|',collect_list(name))
from person_info
group by constellation,blood_type;
- 注意：分组后，select后面只能写分组后的字段和聚集函数。
- 练习三
- 数据
movie category
《疑犯追踪》 悬疑,动作,科幻,剧情
《Lie to me》 悬疑,警匪,动作,心理,剧情
《战狼2》 战争,动作,灾难
- 需求：将每一部电影和其后的每一种类型匹配，格式如下：
《疑犯追踪》 悬疑
《疑犯追踪》 动作
《疑犯追踪》 科幻
《疑犯追踪》 剧情
《Lie to me》 悬疑
...
select movie,col1
from movie_info Lateral view explode(category) tmp1 as col1;
- 注意：explode函数属于UDTF，UDTF在使用时，不能和其他表达式一起出现在select子句后，只能单独出现在select子句后。
- 练习四
- 数据
names tags hobbys
jack|tom|jerry 阳光男孩|肌肉男孩|直男 晒太阳|健身|说多喝热水
marry|nancy 阳光女孩|肌肉女孩|腐女 晒太阳|健身|看有内涵的段子
jack 阳光男孩 健身
jack 阳光男孩 说多喝热水
jack 肌肉男孩 晒太阳
jack 肌肉男孩 健身
jack 肌肉男孩 说多喝热水
.....
select name,tag,hobby
from person_info2
lateral view explode(names) tmp1 as name
lateral view explode(tags) tmp1 as tag
lateral view explode(hobbys) tmp1 as hobby;
第三章 窗口函数
3.1 概念
- 窗口函数：窗口+函数
- 窗口：函数运行时计算的数据集的范围
- 函数：运行的函数
- 支持的函数
- Windowing functions：下一小节介绍
- 统计类的函数(一般都需要结合over使用)
- 排名分析：RANK、ROW_NUMBER、DENSE_RANK、CUME_DIST、PERCENT_RANK、NTILE
- 分组函数和窗口函数的区别
- 如果是分组操作，select后只能写分组后的字段
- 如果是窗口函数，窗口函数是在指定的窗口内，对每条记录都执行一次函数
- 如果是分组操作，有去重效果，而partition不去重
3.2 相关函数说明
- over：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化
- current row：当前行
- n preceding：往前n行数据
- n following：往后n行数据
- unbounded：起点。unbounded preceding 表示从前面起点，unbounded following表示到后面终点
- 格式：
函数 over( partition by 字段 ,order by 字段 window_clause)
- 窗口的大小可以通过windows_clause来指定
- 特殊情况：
- 在over()中既没有出现windows_clause，也没有出现order by，窗口默认为rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING
- 在over()中(没有出现windows_clause)，指定了order by，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW
(rows | range) between (unbounded | [num]) preceding and ([num] preceding | current row | (unbounded | [num]) following)
(rows | range) between current row and (current row | (unbounded | [num]) following)
(rows | range) between [num] following and (unbounded | [num]) following
- lag：返回当前行以上N行的指定列的列值，如果找不到，就采用默认值
LAG(scalar_expression [,offset] [,default])
- lead：返回当前行以下N行的指定列的列值，如果找不到，就采用默认值
LEAD(scalar_expression [,offset] [,default])
- first_value：返回当前窗口指定列的第一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找。
FIRST_VALUE(列名,[false(默认)])
- last_value：返回当前窗口指定列的最后一个值，第二个参数如果为true,代表加入第一个值为null，跳过空值，继续寻找。
LAST_VALUE(列名,[false(默认)])
3.3 练习
3.3.1 数据
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
3.3.2 需求
select name,count(*) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)
from business
where substring(orderdate,1,7)='2017-04'
group by name;
select name,orderdate,cost,sum(cost) over(partition by name,substring(orderdate,1,7))
from business;
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate)
from business;
select name,orderdate,cost,lag(orderdate,1,'无数据') over(partition by name order by orderdate) from business;
select name,orderdate,cost,lead(orderdate,1,'无数据') over(partition by name order by orderdate) from business;
select name,orderdate,cost,FIRST_VALUE(orderdate,true) over(partition by name,substring(orderdate,1,7) order by orderdate ) from business;
select name,orderdate,cost,LAST_VALUE(orderdate,true)
over(partition by name,substring(orderdate,1,7) order by orderdate rows between CURRENT row and UNBOUNDED FOLLOWING) from business;
select name,orderdate,cost,sum(cost) over(partition by name order by orderdate rows between 1 PRECEDING and 1 FOLLOWING)
from business;
select *
from
(select name,orderdate,cost,cume_dist() over(order by orderdate ) cdnum
from business) tmp
where cdnum<=0.2;
第四章 排名函数
- 排名函数可以跟Over()，但是不能定义window_clause在计算名次前，需要先排序。
4.1 相关排名函数介绍
- rank：允许并列，一旦有并列跳号
- row_number：行号，连续的，每个号之间差1
- dense_tank：允许并列，一旦有并列不跳号
- cume_dist：从排序后的第一行到当前值之间数据 占整个数据集的百分比
- percent_rank：rank-1/ 总数据量-1
- nitle(n)：将数据集均分到X个组中，返回每条记录所在的组号
4.2 练习
4.2.1 数据
孙悟空 数学 95
孙悟空 英语 68
大海 语文 94
大海 数学 56
大海 英语 84
宋宋 语文 64
宋宋 数学 86
宋宋 英语 84
婷婷 语文 65
婷婷 数学 85
婷婷 英语 78
4.2.1 需求
select *,rank() over(order by score) ranknum,
ROW_NUMBER() over(order by score) rnnum,
DENSE_RANK() over(order by score) drnum,
CUME_DIST() over(order by score) cdnum,
PERCENT_RANK() over(order by score) prnum
from score;
select *,ntile(5) over() from score;
select *,rank() over(partition by subject order by score desc) from score;
select name,sumscore,rank() over( order by sumscore desc)
from
(select name,sum(score) sumscore
from score
group by name) tmp;
select *,DENSE_RANK() over(order by tmp.sumscore desc)
from
(select *,sum(score) over(partition by name) sumscore
from score) tmp;
select *
from
(select *,rank() over(partition by subject order by score desc) rn
from score) tmp
where rn<=2;
select *,max(score) over(partition by subject) from score;
select *,FIRST_VALUE(score) over(partition by subject order by score) from score;
第五章 自定义函数
5.1 相关maven依赖
<dependencies>
<!-- 添加依赖组件，根据上方配置的版本参数和repository知识库下载依赖 -->
<dependency>
<groupId>org.apache.hadoop</groupId>
<artifactId>hadoop-common</artifactId>
<version>2.7.2</version>
</dependency>
<dependency>
<groupId>org.apache.hive</groupId>
<artifactId>hive-exec</artifactId>
<version>1.2.1</version>
</dependency>
</dependencies>
5.2 自定义函数步骤
- 编写自定义函数
- 自定义UDF函数，继承UDF类
- 提供提供evaluate()，可以提供多个重载的此方法，但是方法名是固定的
- evaluate()不能返回void，但是可以返回null
- 打包
- 安装
- 在HIVE_HOME/auxlib 目录下存放jar包
- 创建函数
create [temporary] function 函数名 as 自定义的函数的全类名;
- 注意：用户自定义的函数，是有库的范围。指定库下创建的函数，只在当前库有效。
5.3 示例
package hive.tiger;
import org.apache.hadoop.hive.ql.exec.UDF;
public class MyFunction extends UDF {
public String evaluate(String name) {
return name + "hahah";
}
}
697
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
