（三）hive学习笔记——1.3hive的函数

最新推荐文章于 2023-11-20 22:18:22 发布

我是菜鸟程序员

最新推荐文章于 2023-11-20 22:18:22 发布

阅读量498

点赞数

分类专栏：大数据学习之路文章标签：大数据 hive hadoop

本文链接：https://blog.csdn.net/qq_35245507/article/details/114682193

版权

大数据学习之路专栏收录该内容

14 篇文章 0 订阅

订阅专栏

（三）hive学习笔记——1.3hive的函数

1.Hive函数介绍
2.hive常用函数
3.hive重要函数
4.hive练习1
5.hive练习2
6.hive练习3
7.hive窗口函数
8.hive排名函数

1.Hive函数介绍

函数有库的概念，系统提供的除外，系统提供的函数可以在任意库使用！

hive (default)> show functions;#查看当前库所有的函数
hive (default)> desc function year;#查看函数的使用
hive (default)> desc function extended year;#查看函数的详细使用

函数的分类
函数的分为系统函数和用户自定义的函数。
用户自定义的函数中：
1. 遵循hive函数类的要求，自定义一个函数类
2. 打包函数，放入到hive的lib目录下，或在HIVE_HOME/auxlib(auxlib用来存放hive可以加载的第三方jar包的目录)
3. 创建一个函数，让这个函数和之前编写的类关联
4. 使用函数
函数按照特征分：
1. UDF:用户自定义的函数。一进一出。输入单个参数，返回单个结果！cast(‘a’ as int) 返回null
2. UDTF:用户定义的表生成函数。一进多出。传入一个参数（集合类型），返回一个结果集
3. UDAF:用户定义的聚集函数。多进一出，传入一列多行的数据，返回一个结果。count,avg,sum

2.hive常用函数

日期操作

1. unix_timestamp返回当前或指定时间的时间戳
2. from_unixtime将时间戳转换为日期格式
3. current_date返回当前的日期
4. current_timestamp返回当前的日期加时间
5. to_date抽取日期部分
6. year获取年
7. month获取月
8. day获取日
9. hour获取时
10. minute获取分
11. second 获取秒
12. weekofyear当前时间时一年中的第几周
13. dayofmonth当前时间时一个月中的第几天
14. months_berween两个日期间的月份，前-后
15. add_months日期加减月
16. datediff两个日期相差的天数，前-后
17. date_add日期加天数
18. date_sub日期减天数
19. last_day日期的当月的最后一天

常用取整函数

1. round四舍五入
2. ceil向上取整
3. floor向下取整

常用字符串操作函数

1. upper转大写
2. lower转小写
3. length长度
4. trim前后去空格
5. lpad向左补齐，到指定长度
6. rpad向右补齐，到指定长度

集合操作

1.size 中的元素的个数
2.map_keys返回map中的key
3.map_values返回map中的value
4.array_contains判断array中是否包含某个元素
5.sort_array将array中的元素排序

3.hive重要函数

1.NVL

NVL(str,replace_with:判断str是否为null，如果为null，使用replace_with替换null，否则不做操作！
在以下场景使用：①将NULL替换为默认值②运行avg()计算平均值函数

2.concat

字符串拼接。可以在参数中传入多个string类型的字符串，一旦有一个参数为null，返回null！

3.concat_ws

使用指定的分割符完成字符串拼接！
concat_ws(分隔符,[str|array<string>]+)

4.collect_set

将此列的多行记录何为一个set集合，去重

5.collect_list

将此列的多行记录何为一个set集合，不去重

6.explode

参数只能为array或map
将array类型参数转为1列N行
将map类型参数转为2列N行

4.hive练习1

数据

悟空    A       男 
大海    A       男 
宋宋    B       男 
凤姐    A       女 
婷姐    B       女 
婷婷    B       女

创建hive数据表

hive (default)> create table emp_sex(name string,dept_id string,sex string)row format delimited fields terminated by "\t";

导入数据到数据库中

hive (default)> load data local inpath '/opt/data/emp_sex.txt' into table emp_sex;
Loading data to table default.emp_sex
Table default.emp_sex stats: [numFiles=1, totalSize=78]
OK
Time taken: 0.254 seconds

查询各个部门的男女分别有多少人

hive (default)>  select t1.dept_id,male_count,female_count from (select dept_id,count(*) male_count from emp_sex where sex='男' group by dept_id) t1 join (select dept_id,count(*) female_count from emp_sex where sex='女' group by dept_id) t2 on t1.dept_id=t2.dept_id;

hive (default)>  select dept_id,sum(case sex when '男' then 1 else 0 end)male_count,sum(case sex when '女' then 1 else 0 end)famale_count from emp_sex group by dept_id;

5.hive练习2

数据

孙悟空  白羊座  A
大海    射手座  A
宋宋    白羊座  B
猪八戒  白羊座  A
凤姐    射手座  A

创建hive数据表

hive (default)> create table person_info(name string,constellation string,blood_type string)row format delimited fields terminated by "\t";
OK
Time taken: 0.142 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/data/costellation.txt' into table person_info;
Loading data to table default.person_info
Table default.person_info stats: [numFiles=1, totalSize=101]
OK
Time taken: 0.279 seconds

把星座和血型一样的人归类在一起

hive (default)> select concat(constellation,',',blood_type),concat_ws('|',collect_list(name)) from person_info group by constellation,blood_type;

6.hive练习3

数据

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

创建hive数据表

hive (default)> create table movice_info (movice string,category array<string>)row format delimited fields terminated by "\t" collection items terminated by ",";
OK
Time taken: 0.078 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/data/movice.txt' into table movice_info;
Loading data to table default.movice_info
Table default.movice_info stats: [numFiles=1, totalSize=151]
OK
Time taken: 0.16 seconds

将电影分类展开

hive (default)> select movice,coll from movice_info Lateral view explode(category) temp1 as coll;

7.hive窗口函数

窗口函数：窗口+函数
窗口：函数运行时计算的数据集的范围
	Windowing functions:
		LEAD:LEAD(scalar_expression[,offset][,default]):返回当前行以下N行的指定列的列值！如果找不到，就采用默认值
		LAG:LAG(scalar_expression[,offset][,default]):返回当前行以上N行的指定列的列值！如果找不到，就采用默认值
		FIRST_VALUE:FIRST_VALUE(列名,[false(默认)])返回当前窗口指定列的第一个；第二个参数如果为true，代表加入第一个值为null，跳过空值，继续寻找
		LAST_VALUE:LAST_VALUE(列名,[false(默认)])返回当前窗口指定列的最后一个；第二个参数如果为true，代表加入第一个值为null，跳过空值，继续寻找
函数：运行的函数！
注意：暴怒是所有的函数在运行嗾使可以通过改变窗口的大小，来控制计算的数据集的范围！
	 所有的排名函数和LAG,LEAD，支持使用over()，但是在over()中不能定义window_clause
格式：函数 over(parttition by 字段,order by 字段 window_clause)
特殊情况：①在over()中既没有出现windows_clause，也没有指定order by，窗口默认为rows between UNBOUNDED PRECEDING and NBOUNDED FOLLOWING②在over（）中没有出现windows_clause，指定order by了，窗口默认为rows between UNBOUNDED PRECEDING and CURRENT ROW

数据

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

创建hive数据表

hive (default
)> create table business(name string,orderdate string,cost int)ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
OK
Time taken: 0.851 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/data/business.txt' into table business;
Loading data to table default.business
Table default.business stats: [numFiles=1, totalSize=267]
OK
Time taken: 0.419 seconds

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

hive (default)> select name,count (*) over(rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) from business where year(orderdate)='2017' and month(orderdate)='04' group by name;

(2)查询顾客的购买明细及月购买总额

hive (default)> select name,substring(orderdate,1,7),cost,sum(cost) over (partition by name,substring(orderdate,1,7) rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)  from business;

(3)查询顾客的购买明细上述的场景，要将cost按照日期累加

hive (default)> select name,orderdate,cost,sum(cost) over (partition by name,substring(orderdate,1,7) order by orderdate rows between UNBOUNDED PRECEDING and CURRENT ROW)  from business;

(4)查看顾客的购买明细及顾客上次的购买时间

hive (default)> select name,orderdate,cost,lag(orderdate,1,'无数据')over(partition by name order by orderdate)from business;

(5)查看顾客的购买明细及顾客下次的购买时间

hive (default)> select name,orderdate,cost,lead(orderdate,1,'无数据')over(partition by name order by orderdate)from business;

(6)查看顾客的购买明细及顾客第一次的购买时间

hive (default)> select name,orderdate,cost,first_value(orderdate,true)over(partition by name,substring(orderdate,1,7) order by orderdate)from business;

(7)查看顾客的购买明细及顾客最后一次的购买时间

hive (default)> select name,orderdate,cost,last_value(orderdate,true)over(partition by name,substring(orderdate,1,7) order by orderdate rows between UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING)from business;

(8)查看顾客的购买明细及顾客最近三次的花费

hive (default)> select name,orderdate,cost,sum(cost)over(partition by name order by orderdate rows between 2 PRECEDING  and CURRENT ROW)from business;

(8)查询前20%时间的订单信息

hive (default)> select * from(select name,orderdate,cost,cume_dist() over(order by orderdate)cdnum from business)tmp where cdnum <=0.2;

8.hive排名函数

RANK:允许并列，一旦有并列的跳号！
ROW_NUMBER:行号!连续的，每行差1
DENSE_RANK：允许并列，但是不跳号！
CUME_DIST:从排序后的第一行到当前值之间数据，占整个数据集的百分比！
PERCECENT_RANK:rank-1/总数据量-1
NTILE:将数据集均分到X个组中，返回每条记录所在的组号
注意：排名函数可以跟over()，但是不能定义window_clause
	 在计算

数据

孙悟空  数学    95
孙悟空  英语    68
大海    语文    94
大海    数学    56
大海    英语    84
宋宋    语文    64
宋宋    数学    86
宋宋    英语    84
婷婷    语文    65
婷婷    数学    85
婷婷    英语    78

创建hive数据表

hive (default)> create table score(name string,subject string,score int)row format delimited fields terminated by "\t";
OK
Time taken: 0.072 seconds

导入数据到数据表中

hive (default)> load data local inpath '/opt/data/score.txt' into table score;
Loading data to table default.score
Table default.score stats: [numFiles=1, totalSize=193]
OK
Time taken: 0.159 seconds

(1)按照科目进行排名

 hive (default)> select * ,rank() over(partition by subject order by score desc)from score ;

(2)给每个学生的总分进行排名

 hive (default)> select name,sumscore,rank()over(order by sumscore desc) from(select name ,sum(score ) sumscore from score group by name) tmp;

(3)给每个学生的明细及每个学生的总分和总分排名

 hive (default)> select *,DENSE_RANK() over(order by sumscore desc) from(select *,sum(score) over(partition by name)sumscore from score)tmp;

(4)只查询每个科目的成绩的前两名

 hive (default)> select * from(select *,rank() over(partition by subject order by score desc)rn from score)tmp where rn <=2;

(5)查询学生成绩明细，便显示当前学科的最高分

 hive (default)> select *,max(score)over(partition by subject)from score;

(6)查询学生成绩明细，便显示当前学科的最低分

 hive (default)> select *,min(score)over(partition by subject)from score;

我是菜鸟程序员

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
2
评论
（三）hive学习笔记——1.3hive的函数

（三）hive学习笔记——1.3hive的函数1.Hive函数介绍2.hive常用函数1.Hive函数介绍函数有库的概念，系统提供的除外，系统提供的函数可以在任意库使用！hive (default)> show functions;#查看当前库所有的函数hive (default)> desc function year;#查看函数的使用hive (default)> desc function extended year;#查看函数的详细使用函数的分类函数的分为
复制链接

扫一扫