HIVE函数讲解之单行函数、聚合函数、炸裂函数、窗口函数

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自定义函数的方法，实现我们自己想要的效果。