Hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库表，并提供简单的SQL查询功能，可以将SQL语句转换为MapReduce任务进行运行。其优点是学习成本低，可以通过类SQL语句快速实现简单的MapReduce统计，不必开发专门的MapReduce应用，十分适合数据仓库的统计分析。
Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。好处在于可以避免用户反复写逻辑，可以直接拿来使用。重点在于用户需要知道函数叫什么，能做什么。
Hive函数大致分为五类：单行函数、聚合函数、炸裂函数、窗口函数、自定义函数，今天我们主要介绍一下除自定义函数外的函数部分案例使用。
自定义函数咱们之前已经细致的讲过了，可以根据连接查看：HIVE函数讲解之自定义函数
一、单行函数
单行函数的特点是一进一出，大致可以分为数学函数，日期函数，字符串函数，流程控制函数，集合函数。
1. 数学函数
数学函数针对于数值形式的计算，返回值均为数值类型。
示例：
select 1 + 1; 2
select 1 - 1; 0
select 2 * 2; 4
select 3 / 2; 1.5
select 5 % 2; 1
select 1 & 2; 0
select 1 | 2; 3
select 1 ^ 2; 3
select ~1; -2
2. 日期函数
① unix_timestamp(string time)：返回值为bigint类型。
select unix_timestamp('2023-04-06 15:31:26');
1680795086
② from_unixtime(bigint time [,string format])：返回值为
string类型。
select from_unixtime(1680795086);
2023-04-06 15:31:26
③ year(string time)：返回值为int类型。
select year('2023-04-06 15:31:26');
2023
④ month(string time)：返回值为int类型。
select month('2023-04-06 15:31:26');
4
⑤ day(string time)：返回值为int类型。
select day('2023-04-06 15:31:26');
6
⑥ datediff(string time,string time)：返回值类型为int类型。
select datediff('2023-04-07','2023-04-06');
1
⑦ date_add(string date,int day)：返回值为date类型。
select date_add('2023-04-06',1);
2023-04-07
⑧ date_sub(string date,int day)：返回值为date类型。
select date_sub('2023-04-06',1);
2023-04-05
⑨ date_format(string date,string format)：返回值为string类型。
select date_format('2023-04-06 15:45:25','yyyy-MM-dd');
2023-04-06
3. 字符串函数
① upper(string str)：返回值为string类型。
select upper('abc');
ABC
② lower(string str)：返回值为string类型。
select lower('ABC');
abc
③ length(string str)：返回值为int类型。
select length('atguigu');
7
④ trim(string str)：返回值为string类型。
select trim(' abc ');
abc
⑤ substring(string str,int start[,int end])：返回值为string类型。
select substring('atguigu',2,5);
tguig
⑥ replace(string str,string bef,string las)：返回值为string类型。
select replace('atguigu','a','b');
btguigu
⑦ split(string str,string par)：返回值为array类型。
select split('atguigu','g');
["at","ui","u"]
⑧concat_ws(string spl,string s1,string s2,...)：返回值为string类型。
select concat_ws('-','a','b','c');
a-b-c
⑨ get_json_object(string json,string path)：返回值为string类型。
select get_json_object('{"data1":1,"data2":2}','$.data2');
2
4. 流程控制函数
示例：
select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end;
mary
select if(2>3,2,3);
3
5. 集合函数
① size(array/map m)：返回值为int类型。
select size(array('a','b','c'));
3
② map_keys(map m)：返回值为array类型。
select map_keys(test) from test;
[1,2,3]
test字段值为{"1":"a","2":"b","3","c"}
③ map_values(map m)：返回值为array类型。
select map_values(test) from test;
["a","b","c"]
④ array_contains(array arr,string str)：返回值为boolean类型。
select array_contains(array('1','2','3'),'4');
false
⑤ sort_array(array arr)：返回值为array类型。
select sort_array(array(4,2,3,1));
[1,2,3,4]
二、聚合函数
聚合函数对一组数据进行计算，并返回单个值。
测试数据test表：
1. 普通聚合
示例：
select count(*) from test;
4
select sum(score) from test;
260
select max(score) from test;
80
select min(score) from test;
50
select avg(score) from test;
65
2. 高级聚合
示例：
select collect_list(name) from test;
["atguigu1","atguigu2","atguigu3","atguigu3"]
select collect_set(name) from test;
["atguigu1","atguigu2","atguigu3"]
3. 分组
Group By语句通常会和聚合函数一起使用，按照一个或者多个列队结果进行分组，然后对每个组执行聚合操作，同时可以使用having进行过滤。
示例：
select
id,
avg(score) avg_s
from test
group by id
having avg_s > 60;
三、炸裂函数
Hive处理的数据字段是可再分的，不满足原子性，即DDL可以定义一个字段类型为数组，因此才有了explode()函数，用于给这个字段展开降维，把指定的数组字段拆分降维展开为多行。类似于UDTF函数，作用于单/多个数据行，并且产生多个数据行，以一个表作为输出。
1. explode 将数组或者map展开
例：
select explode(array('a','b','c','d'));
结果：
a b c d
2. json_tuple 取出json字符串中的属性值
例：
select json_tuple('{"name":"王二狗","sex":"男","age":"25"}','name','sex','age');
结果：
王二狗 男 25
3. lateral view 侧写
炸裂函数和聚合函数一样不支持和普通列一起查询，所以一般会配合侧写进行查询。
用法：lateral view udtf(expression) tableAlias AS columnAlias
解释：lateral view用于和split，explode等UDTF一起使用，它能够将一行数据拆分成多行数据，在此基础上可以对拆分后的数据进行聚合。
lateral view首先为原始表的每行调用UDTF，UDTF会把每一行拆分成一行或者多行，lateral view再把结果组合，产生一个支持别名表的虚拟表。
例：
select movie, category_namefrom movie_infolateral viewexplode(split(category,",")) movie_info_tmp AS category_name;
四、窗口函数
窗口函数，也可以叫开窗函数，其从本质来看是将Hive 中一些复杂的查询封装成了窗口的形式，进行数据统计时使用并且操 作十分方便，窗口函数为统计时使用的聚合函数指定一个聚合的范围。
1. 语法
基本语法：函数 + over( [partition by ...] [order by ...] [窗口子句] )
- over表示开窗，默认窗口大小会包含所有数据。
- partition by表示根据字段再划分一个细窗口，相同字段进入同一个细窗口里面，每个窗口之间相互独立，窗口子句对于每个细窗口独立生效。
- order by表示窗口内按什么排序，如果只有over表示直接最大窗口排序；如果有partition by每个细窗口单独排序。
- 窗口子句，可以进一步限定范围
(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
示例：
rows between unbounded preceding and unbounded following
行的范围为上无边界到下无边界（第一行到最后一行）。
注：窗口函数是一行一行执行的。
2. 使用
① 窗口函数
lag(col,n,default_val)：往前第n行数据。
lead(col,n, default_val)：往后第n行数据。
first_value (col,true/false)：当前窗口下的第一个值，第二个参数为true，跳过空值。
last_value (col,true/false)：当前窗口下的最后一个值，第二个参数为true，跳过空值。
② 聚合函数
max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。
③ 排名分析函数
rank：排名相同时会重复总数不会减少。
dense_rank：排名相同时会重复总数会减少。
row_number：行号。
ntile：分组并给上组号。
3. 注意事项
- 窗口函数的执行次序是在group by之后。
- 有时窗口子句加与不加结果是一致的，原因是窗口子句有默认值，当有order by 但是缺少窗口子句时，范围是上无边界到当前行，当order by和窗口子句都缺少时，范围为上无边界到下无边界。
- 不是所有函数都需要写窗口子句,rank、dense_rank、ntile、row_number、lag、lead 这些函数不支持窗口子句。
- 排名分析函数中不需要写参数，会将排好序数据进行标号。
4. 使用示例
例如，表table，字段为id，sub，score，需求出表每门学科的成绩排名。
select
id,
sub,
score,
rank() over(distribute by sub sort by score desc) rank
from table;
五、总结
本文给大家介绍了Hive中一些常用的基本函数、炸裂函数和窗口函数的理论及使用，各位程序猿们需要多多练习函数的使用，体会函数的效果以及它们之间的联合使用！下期为大家介绍HIVE自定义函数的方法，实现我们自己想要的效果。