函数

hive函数一共有481个。官网文档可参考LanguageManual+UDF

1. 函数简介

Hive会将常用的逻辑封装成函数给用户进行使用，类似于Java中的函数。
好处:避免用户反复写逻辑，可以直接拿来使用。
重点:用户需要知道函数叫什么，能做什么。
Hive提供了大量的内置函数，按照其特点可大致分为如下几类:单行函数、聚合函数、炸裂函数、窗口函数。

sql

-- 查看系统内置函数
show functions;
-- 查看内置函数用法
desc function upper;
-- 查看内置函数详细信息
desc function extended upper;

查询所有的函数 hive函数查询图所有的函数都有对应的类实现，比如查看substring函数，比如看substring函数的源码可以查看org.apache.hadoop.hive.ql.udf.UDFSubstr类 Alt text

2. 单行函数

单行函数的特点是一进一出，即输入一行，输出一行。
单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

3. 算术运算函数

运算符	描述
A+B	A和B 相加
A-B	A减去B
A*B	A和B 相乘
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A\|B	A和B按位取或
A^B	A和B按位取异或
~A	A按位取反

以下sql执行按位与操作:
Alt text

4. 数值函数

4.1 round(四舍五入)

保留2位小数 Alt text

4.2 向上取整

sql

select ceil(3.1) ;

4.3 向下取整

sql

select floor(4.8);

5. 字符串函数

5.1 substring:截取字符串

语法一:substring(string A, int start)
返回值:string
说明:返回字符串A从start位置到结尾的字符串
语法二:substring(string A, int start, int len)
返回值:string
说明:返回字符串A从start位置开始，长度为len的字符串

5.2 replace:替换

语法:replace(string A, string B, string C)
返回值:string
说明:将字符串A中的子字符串B替换为C。
Alt text

5.3 regexp_replace:正则替换

语法:regexp_replace(string A, string B, string C)
返回值:string
说明:将字符串A中的符合java正则表达式B的部分替换为C。注意，在有些情况下要使用转义字符。
Alt text

5.4 regexp:正则匹配

语法:字符串regexp正则表达式
返回值:boolean
说明:若字符串符合正则表达式，则返回true，否则返回false。

sql

select 'dfsaaaa' regexp 'dfsa+'

5.5 repeat:重复字符串

语法:repeat(string A, int n)
返回值:string
说明:将字符串A重复n遍。
Alt text

5.6 split:字符串切割

语法:split(string str, string pat)
返回值:array
说明:按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回。
Alt text

5.7 nvl:替换null值

语法:nvl(A,B)
说明:若A的值不为null，则返回A，否则返回B。
Alt text

5.8 concat:拼接字符串

语法:concat(string A, string B, string C, ……)
返回:string
说明:将A,B,C……等字符拼接为一个字符串
Alt text

5.9 concat_ws:以指定分隔符拼接字符串或者字符串数组

语法:concat_ws(string A, string…| array(string))
返回值:string
说明:使用分隔符A拼接多个字符串，或者一个数组的所有元素。
Alt text

5.10 get_json_object:解析json字符串

语法:get_json_object(string json_string, string path)
返回值:string
说明:解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。 Alt text

6. 日期函数

6.1 unix_timestamp:返回当前或指定时间的时间戳

语法:unix_timestamp()
返回值:bigint
说明:返回1970年1月1日0时0分0秒到现在的秒数 Alt text 获取指定的时间戳

6.2 from_unixtime:转换时间戳为日期字符串

转化UNIX时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式
语法:from_unixtime(bigint unixtime[, string format])
返回值:string

✏️提示

在Hive4.x之后，时区由hive.local.time.zone进行控制，默认为本地时区，如果本地时区为GMT+8(东八区)，那么时间戳将得到和本地时区相关的时间。使用date -R查看Hive所在服务器日期时区, 可以看到服务器时区为东八区:

[jack@hadoop102 ~]$ date -R
Wed, 12 Jun 2024 20:44:49 +0800

Alt text

6.3 from_utc_timestamp:转换时间戳为日期

第一个参数为毫秒值，第二个参数为时区字符串。用的比较少。
语法: from_utc_timestamp({any primitive type} ts,string timezone)
返回值: timestamp
Alt text

6.4 to_utc_timestamp:转换时间戳为指定时区日期

第一个参数为毫秒值，第二个参数为哪个时区字符串。用的比较少。
语法: to_utc_timestamp({any primitive type} ts,string timezone)
返回值: timestamp
Alt text

from_utc_timestamp和to_utc_timestam区别

想象你有一个世界时钟(UTC)：

FROM_UTC_TIMESTAMP: 你知道世界时钟上的时间，你想知道某个特定城市(时区)现在几点。
TO_UTC_TIMESTAMP: 你知道某个特定城市(时区)现在几点，你想知道世界时钟上现在几点。

6.5 current_date:当前日期

Alt text

6.6 current_timestamp:当前的日期加时间

精确的毫秒 Alt text

6.7 month:获取日期中的月

语法:month(string date)
返回值:int
Alt text

6.8 day:获取日期中的日

语法:day(string date)
返回值:int
Alt text

6.9 hour:获取日期中的小时

语法:hour(string date)
返回值:int
Alt text

6.10 datediff:两个日期相差的天数(结束日期减去开始日期的天数)

语法:datediff(string enddate, string startdate)
返回值:int
Alt text

6.11 date_add:日期加天数

语法:date_add(string startdate, int days)
返回值:string
说明:返回开始日期startdate增加days天后的日期 Alt text days可以传入负数:

6.12 date_sub:日期减天数

语法:date_sub (string startdate, int days)
返回值:string
说明:返回开始日期startdate减少days天后的日期。

6.13 date_format:将标准日期解析成指定格式字符串

Alt text

7. 流程控制函数

7.1 case when:条件判断函数

搜索CASE表达式:case when a then b [when c then d]* [else e] end
返回值:T
说明:如果a为true，则返回b；如果c为true，则返回d；否则返回e
简单CASE表达式: case a when b then c [when d then e]* [else f] end
返回值: T
说明:如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

提示

简单CASE表达式正如其名，写法简单，但能实现的事情比较有限。简单CASE表达式能写的条件，搜索CASE表达式也能写。需要注意CASE表达式里各个分支返回的数据类型是否一致。某个分支返回字符型，而其他分支返回数值型的写法是不正确的。

7.2 if:条件判断

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明:当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull
类似于Java中三元运算符:
Alt text

8. 集合函数

8.1 size:集合中元素的个数

Alt text

8.2 map:创建map集合

语法: map (key1, value1, key2, value2, …)
说明: 根据输入的key和value对构建map类型
Alt text

8.3 str_to_map: 字符串转map集合

语法: str_to_map(text[, delimiter1, delimiter2])
说明: delimiter1表示每对kv键值对的分隔符，delimiter2表示key和value的分隔符 Alt text

8.4 map_keys:返回map中的key

Alt text

8.5 map_values:返回map中的value

Alt text

8.6 array:声明array集合

语法: array(val1, val2, …)
说明: 根据输入的参数构建数组array类
Alt text

8.7 array_contains:判断array中是否包含某个元素

Alt text

8.8 sort_array:将array中的元素排序

只支持升序排序:
Alt text

8.9 struct:声明struct中的各属性

语法:struct(val1, val2, val3, …)
说明:根据输入的参数构建结构体struct类
Alt text

8.10 named_struct声明struct的属性和值

Alt text

9. 高级聚合函数

多进一出 (多行传入，一个行输出)。
数据准备employee表:

name	sex	birthday	hiredate	job	salary	bonus	friends	children
张无忌	男	1980/02/12	2022/08/09	销售	3000	12000	[阿朱，小昭]	{张小无:8,张小忌:9}
赵敏	女	1982/05/18	2022/09/10	行政	9000	2000	[阿三，阿四]	{赵小敏:8}
黄蓉	女	1982/04/13	2022/06/11	行政	12000	Null	[东邪，西毒]	{郭芙:5,郭襄:4}

建表语句:

sql

create  table  employee(
    name string comment '姓名',  
    sex  string comment '性别',  
    birthday string comment '出生年月', 
    hiredate string comment '入职日期', 
    job string comment '岗位',   
    salary double comment '薪资',  
    bonus double comment '奖金',  
    friends array<string> comment '朋友',  
    children map<string,int> comment '孩子' 
);
insert into employee  
  values('张无忌','男','1980/02/12','2022/08/09','销售',3000,12000,array('阿朱','小昭'),map('张小无',8,'张小忌',9)),
        ('赵敏','女','1982/05/18','2022/09/10','行政',9000,2000,array('阿三','阿四'),map('赵小敏',8)),
        ('宋青书','男','1981/03/15','2022/04/09','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
        ('周芷若','女','1981/03/17','2022/04/10','研发',18000,1000,array('王五','赵六'),map('宋小青',7,'宋小书',5)),
        ('郭靖','男','1985/03/11','2022/07/19','销售',2000,13000,array('南帝','北丐'),map('郭芙',5,'郭襄',4)),
        ('黄蓉','女','1982/12/13','2022/06/11','行政',12000,null,array('东邪','西毒'),map('郭芙',5,'郭襄',4)),
        ('杨过','男','1988/01/30','2022/08/13','前台',5000,null,array('郭靖','黄蓉'),map('杨小过',2)),
        ('小龙女','女','1985/02/12','2022/09/24','前台',6000,null,array('张三','李四'),map('杨小过',2));

9.1 普通聚合

比如count、sum,用法见前面章节

9.2 collect_list:收集并形成list集合，结果不去重

每个月的入职人数以及姓名(同姓需要保留):
Alt text

9.3 collect_set:收集并形成set集合，结果去重

查询所有人的岗位 Alt text

10. 炸裂函数(UDTF)

定义: UDTF(Table-Generating Functions)，接收一行数据，输出一行或多行数据。也就是输出表的效果，UDTF函数也被称为制表函数。 Alt text

10.1 explode函数

语法： explode(ARRAY<T> a)或者explode(Map<K,V> m)
功能：将数组数据炸裂成一列多行，将Map数据炸裂成2列多行。下图为explode函数执行效果：
Alt text sql执行，将数组炸裂开，分散成1列多行数据炸裂个人信息成2列多行

10.2 posexplode函数

语法： posexplode(ARRAY<T> a)
功能效果如图：
Alt text sql执行，将数组炸裂开

10.3 inline函数

语法： inline(ARRAY<STRUCT<f1:T1,...,fn:Tn>> a) 功能效果如图：
Alt text sql执行，将对象数组炸裂开

10.4 Lateral View

定义: Latera View通常与UDTF配合使用。Lateral View可以将UDTF应用到源表的每行数据，将每行数据转换为一行或多行，并将源表中每行的输出结果与该行连接起来，形成一个虚拟表。
获取employee表的朋友信息 Alt text

10.5 数据准备

movie_info表结构

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

建表语句：

sql

create table movie_info(
    movie string,     --电影名称
    category string   --电影分类
) 
row format delimited fields terminated by "\t";
insert overwrite table movie_info
values ("《疑犯追踪》", "悬疑,动作,科幻,剧情"),
       ("《Lie to me》", "悬疑,警匪,动作,心理,剧情"),
       ("《战狼2》", "战争,动作,灾难");

10.6 统计各分类的电影数量

sql

SELECT
category_,  COUNT(*)
FROM (
SELECT movie, category, category_  
FROM movie_info LATERAL view explode(split(category, ',')) tmp AS category_
) t1 GROUP BY  category_

执行结果：
Alt text

11. 窗口函数(开窗函数)

定义: (window functions)窗口函数，能为每行数据划分一个窗口，然后对窗口范围内的数据进行计算，最后将计算结果返回给该行数据。
语法: 窗口函数的语法中主要包括"窗口"和"函数"两部分。其中"窗口"用于定义计算范围，"函数"用于定义计算逻辑。

sql

select  字段1,字段2,... 
函数(amount) over (窗口范围) 别名(如total_amount) 
from 表名;

11.1 函数

绝大多数的聚合函数都可以配合窗口使用，例如max(),min(),sum(),count(),avg()等。

11.2 窗口范围

窗口范围的定义分为两种类型，一种是基于行的，一种是基于值的。

基于行：
要求每行数据的窗口为上一行到当前行

其中使用order by是在maprduce指定顺序，指定顺序也就固定了每行的位置，使得between and中的行生效
基于值：
要求每行数据的窗口为值等于当前值-1，到当前值范围

其中使用order by并不是起的指定顺序的作用，根据值范围涉及到字段值和顺序无关，order by后面跟具体字段，非要说作用就是指明sql中用该字段的值进行操作。
[num] preceding和[num] following等不再是指定行范围而是指定值范围是[字段值-num, 字段值+num]。并且num必须是整型，否则会失效。
如果值范围使用的是unbounded preceding和current row等，那么该字段不一定必须为数字类型，可以为字符串等。
基于行、值举例：

窗口分区：
定义窗口范围时，可以指定分区字段，每个分区单独划分窗口。如图划分窗口范围时，将数据划分为了"黄"和"绿"两个区。窗口分区举例：

sql

select  
order_id, order_date, amount, 
sum(amount) over (partition by user_id order by order_date rows between unbounded preceding and current row) total_amount
from order_info;

窗口缺省 over( )中的三部分内容partition by、order by、(rows|range) between… and… 均可省略不写。

partition by省略不写，表示不分区
order by省略不写，表示不排序
(rows|range) between … and … 省略不写，则使用其默认值，默认值如下：
若over()中包含order by，则默认值为range between unbounded preceding and current row
若over()中不包含order by，则默认值为rows between unbounded preceding and unbounded following
执行效果：

11.3 常用窗口函数

按照功能，常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数。

聚合函数
max：最大值。
min：最小值。
sum：求和。
avg：平均值。
count：计数。
跨行取值函数
(1) lead和lag
功能：获取当前行的上/下边某行里面某个字段的值。
语法：lead | lag(字段名,偏移量, 默认值) over (partition by 分区字段 order by 排序字段)

提示

lag和lead函数不支持自定义窗口范围。

比如：

sql

select order_id, 
user_id,order_date,amount, 
lag(order_date,1, '1970-01-01') over (partition by user_id order by order_date) last_date,
lead(order_date,1'9999-12-31') over (partition by user_id order by order_date) next_date
from order_info;

执行效果：
Alt text (2) first_value和last_value
功能：获取窗口内某一列的第一个值/最后一个值，其中参数是否跳过null,若为true,表示第一个值为null,就从第二行取，直到取到不为null的行的值，默认值为false。
语法：first_value | last_value(字段, 是否跳过null) over (partition by 字段 order by 字段)
比如：

sql

select order_id, 
user_id, order_date, amount, 
first_value(order_date, false) over (partition by user_id order by order_date) first_date,
last_value(order_date, false) over (partition by user_id order by order_date) last_date
from order_info;

执行效果：
Alt text 3. 排名函数
rank、dense_rank、row_number，其中rank出现并列情况，后续排名+2; dense_rank若出现并列情况，后续排名+1; row_number表示行号。
功能：计算排名,实际统计中常用来解决分组TopN。

提示

rank 、dense_rank、row_number不支持自定义窗口。

比如：

sql

select 
stu_id, course,score,
rank() over(partition by course order by score desc) rk,
dense_rank() over(partition by course order by score desc) dense_rk,
row_number() over(partition by course order by score desc) rn
from score_info;

执行效果：
Alt text

函数 ​

1. 函数简介 ​

2. 单行函数 ​

3. 算术运算函数 ​

4. 数值函数 ​

4.1 round(四舍五入) ​

4.2 向上取整 ​

4.3 向下取整 ​

5. 字符串函数 ​

5.1 substring:截取字符串 ​

5.2 replace:替换 ​

5.3 regexp_replace:正则替换 ​

5.4 regexp:正则匹配 ​

5.5 repeat:重复字符串 ​

5.6 split:字符串切割 ​

5.7 nvl:替换null值 ​

5.8 concat:拼接字符串 ​

5.9 concat_ws:以指定分隔符拼接字符串或者字符串数组 ​

5.10 get_json_object:解析json字符串 ​

6. 日期函数 ​

6.1 unix_timestamp:返回当前或指定时间的时间戳 ​

6.2 from_unixtime:转换时间戳为日期字符串 ​

6.3 from_utc_timestamp:转换时间戳为日期 ​

6.4 to_utc_timestamp:转换时间戳为指定时区日期 ​

6.5 current_date:当前日期 ​

6.6 current_timestamp:当前的日期加时间 ​

6.7 month:获取日期中的月 ​

6.8 day:获取日期中的日 ​

6.9 hour:获取日期中的小时 ​

6.10 datediff:两个日期相差的天数(结束日期减去开始日期的天数) ​

6.11 date_add:日期加天数 ​

6.12 date_sub:日期减天数 ​

6.13 date_format:将标准日期解析成指定格式字符串 ​

7. 流程控制函数 ​

7.1 case when:条件判断函数 ​

7.2 if:条件判断 ​

8. 集合函数 ​

8.1 size:集合中元素的个数 ​

8.2 map:创建map集合 ​

8.3 str_to_map: 字符串转map集合 ​

8.4 map_keys:返回map中的key ​

8.5 map_values:返回map中的value ​

8.6 array:声明array集合 ​

8.7 array_contains:判断array中是否包含某个元素 ​

8.8 sort_array:将array中的元素排序 ​

8.9 struct:声明struct中的各属性 ​

8.10 named_struct声明struct的属性和值 ​

9. 高级聚合函数 ​

9.1 普通聚合 ​

9.2 collect_list:收集并形成list集合，结果不去重 ​

9.3 collect_set:收集并形成set集合，结果去重 ​

10. 炸裂函数(UDTF) ​

10.1 explode函数 ​

10.2 posexplode函数 ​

10.3 inline函数 ​

10.4 Lateral View ​

10.5 数据准备 ​

10.6 统计各分类的电影数量 ​

11. 窗口函数(开窗函数) ​

11.1 函数 ​

11.2 窗口范围 ​

11.3 常用窗口函数 ​

函数

1. 函数简介

2. 单行函数

3. 算术运算函数

4. 数值函数

4.1 round(四舍五入)

4.2 向上取整

4.3 向下取整

5. 字符串函数

5.1 substring:截取字符串

5.2 replace:替换

5.3 regexp_replace:正则替换

5.4 regexp:正则匹配

5.5 repeat:重复字符串

5.6 split:字符串切割

5.7 nvl:替换null值

5.8 concat:拼接字符串

5.9 concat_ws:以指定分隔符拼接字符串或者字符串数组

5.10 get_json_object:解析json字符串

6. 日期函数

6.1 unix_timestamp:返回当前或指定时间的时间戳

6.2 from_unixtime:转换时间戳为日期字符串

6.3 from_utc_timestamp:转换时间戳为日期

6.4 to_utc_timestamp:转换时间戳为指定时区日期

6.5 current_date:当前日期

6.6 current_timestamp:当前的日期加时间

6.7 month:获取日期中的月

6.8 day:获取日期中的日

6.9 hour:获取日期中的小时

6.10 datediff:两个日期相差的天数(结束日期减去开始日期的天数)

6.11 date_add:日期加天数

6.12 date_sub:日期减天数

6.13 date_format:将标准日期解析成指定格式字符串

7. 流程控制函数

7.1 case when:条件判断函数

7.2 if:条件判断

8. 集合函数

8.1 size:集合中元素的个数

8.2 map:创建map集合

8.3 str_to_map: 字符串转map集合

8.4 map_keys:返回map中的key

8.5 map_values:返回map中的value

8.6 array:声明array集合

8.7 array_contains:判断array中是否包含某个元素

8.8 sort_array:将array中的元素排序

8.9 struct:声明struct中的各属性

8.10 named_struct声明struct的属性和值

9. 高级聚合函数

9.1 普通聚合

9.2 collect_list:收集并形成list集合，结果不去重

9.3 collect_set:收集并形成set集合，结果去重

10. 炸裂函数(UDTF)

10.1 explode函数

10.2 posexplode函数

10.3 inline函数

10.4 Lateral View

10.5 数据准备

10.6 统计各分类的电影数量

11. 窗口函数(开窗函数)

11.1 函数

11.2 窗口范围

11.3 常用窗口函数