函数

函数简介

方便完成我们一些复杂的操作，就好像我们 Spark 中的 UDF 函数，避免用户反复写逻辑。

Hive  提供了大量的内置函数，主要可以分为以下几类：

• 单行函数
• 聚合函数
• 炸裂函数
• 窗口函数

下面的命令可以查看内置函数的相关信息：

1）查看系统自带的函数

show functions;

2）显示自带的函数的用法

查看函数 upper 的用法（显示函数参数）：

desc function upper;

3）详细显示自带的函数的用法

查看函数 upper 的详细用法：

desc function extended upper;

---

1、单行函数

特点：输入一行，输出一行（或多行）。

按照功能可以分为：

• 日期函数
• 字符串函数
• 集合函数
• 数学函数
• 流程控制函数

1.1、算数运算函数

处理常用的 +、-、*、/ 还有 %、&、^、~、| 等

查询出员工薪资后全部 +100 显示：

select sal+1 from emp; 

1.2、数值函数

1）round：四舍五入

默认不保留小数部分，直接四舍五入为整数。

select round(3.5);  -- 输出4

可以设置第二个参数（控制小数部分的长度）：

select round(3.1415,2)  --输出3.14

2）ceil：向上取整

select ceil(3.11);  -- 输出4

3）floor：向下取整

select floor(3.6);    --输出3

1.3、字符串函数

1）substring（str，beg[，len]）：截取字符串

解释：从第 beg 个下标对 str 进行截取，共截取 len 长度的字符串。

注意: 下标是从 1 开始的，负数代表取后 beg 位（-4代表取后4位）。

select substring("hello",1);    --返回helloselect substring("hello",1,2);  --返回heselect substring("hello",-4);    --返回elloselect substring("hello",-4,2);    --返回el

2）replace（str1,str2,str3）：替换

解释：将字符串 str1 中的子字符串 str2 用 str3 替换。

select replace("hello sxau",'sxau','tylg'); --返回 hello tylg

3）regexp_replace（str1,regex,str2）：正则替换

解释：将字符串 str1 中满足正则表达式 regex 的部分用 str2 替换掉。

select regexp_replace("身份证号:141125...","\\d",'*');  --返回 身份证号:******...

注意：这里的正则表达式语法是我们 Java 中的语法，比正常的正则表达式多一个斜杠 \；

4）regexp：正则匹配

解释：满足正则表达式返回 true 否则返回 false。

select '141125' regexp '\\d+';  --返回true

5）repeat（str，count）：重复字符串

将字符串 str 重复 count 次。

select repeat('good ',3);    --返回 good good good 

6）split（str，regex）：字符串切割

解释：根据正则表达式 regex 将字符串切割开来。

注意：这里返回的是一个数组。

select split("name sex id sal",' '); --返回 ["name","sex","id","sal"]

7）nvl（a，b）：替换 null 值

解释：如果 a 不是null，则返回a，否则返回 b。

用法：通常用于判断字段是否为空。

select nvl(null,1);    --返回1

8）concat（str1，str2，...）：拼接字符串

select concat('tom','jerry','school');  --返回 tomjerryschool

9）concat_ws：以指定的分隔符拼接字符串

select concat_ws('.','192','168','88','134') --返回 192.168.88.134

10）get_json_object（json_string，path）：解析json字符串

JSON 的相关知识可以查看菜鸟教程。

解释：解析 json 字符串 json_string，并返回 path 指定的内容。如果输入的 json 字符串无效，那么返回 null。

-- 返回 lyh
select get_json_object('{"name": "lyh",	"friends": ["my","zht"],"students": {"drj": 48,"lyf": 30},"address": {"street": "chang an jie","city":	"beijing","postal_code": 10010}}','$.name');

注意：

1. 如果是 json 数组，可以通过 '$.[0].name' 的方式获取到下标为 0 的json对象的 name 属性值。
2. 注意如果 json 字符串内用的是双引号，那么我们的函数参数最好用单引号。

---

1.4、日期函数

我们的日期时间基本格式为：年-月-日 时:分:秒 

1）unix_timestamp（[date_str，format]）：返回当前或指定时间的时间戳

解释：将时间字符串 date_str 根据 format 格式解析为时间戳。

注意：一定要注意字符串 date_str 中的间隔符必须要和 format 中一一对应。

select unix_timestamp();    --当前时间的时间戳  1696390814select unix_timestamp('2023/10/04 11-41-08','yyyy/MM/dd HH-mm-ss');    -- 1696419668

2）from_unixtime（timestamp）：将时间戳转为时间

注意：

1. 这里的参数不是字符串，而是一个数值。
2. from_unixtime() 只能精确到秒级别。
3. from_utc_time() 可以精确到毫秒级别，但相应的如果参数是 unix_timestamp 需要*1000 转为毫秒数。

select from_unixtime(1696419668);    --输出 2023-10-04 11:41:08select from_utc_timestamp(cast(1696419668 as bigint)*1000,'GMT+8');

3）current_date（）：当前日期

注意：括号可以省略。

select current_date;    --返回 2023-10-04

4）current_timestamp（）：当前日期的时间，并精确到毫秒

select current_timestamp();     --返回 2023-10-04 11:49:51.696000000

5）month（str）：获取日期中的月

select month(current_date());    --返回 10

6）day（str）：获取日期的日

select day(current_date());    --返回 4

7）hour（str）：获取日期的时

select hour('2023-10-04 11:59:10');    --返回 11

8）datediff（enddate，startdate）：两个日期相差的天数

select datediff('2023-10-4','2023-10-1');    --返回 3

9）date_add（startdate，days）：日期加天数

注意：有 add 就有 sub，一个加，一个减。

select date_add('2023-10-1',365);    --返回 2024-9-30select date_sub('2023-10-1',365);    --返回 2022-10-1

10）date_format（date-str，format）：将日期字符串转为指定格式的字符串

select date_format('2023-10-4 12:00:05','yyyy年MM月dd日-HH时mm分:ss秒'); --返回2023年10月04日-12时00分:05秒

---

1.5、流程控制函数

1）case when：条件判断函数

select stu_id,course_id,score,casewhen score>=90 then 'A'when score>=80 then 'B'when score>=70 then 'C'when score>=60 then 'D'else '不及格'end as grade
from score_info;

如果是等值查询（直接 case 后面跟字段名，when 条件处直接写值即可）：

select stu_id,course_id,score,case scorewhen 90 then 'A'when 80 then 'B'when 70 then 'C'when 60 then 'D'end as grade
from score_info;

2）if：条件判断，类似于 Java 的三元运算符

select stu_id,course_id,if(score>60,'及格','不及格') `grade`
from score_info;

---

1.6、集合函数

集合函数用来处理复杂的数据类型，比如 array、map 和 struct。

1）size ：集合中元素的个数

select size(array(1,2,3));    --3

2）map（k1，v1，k2，v2）：创建 map 集合

select map('hadoop',1,'spark',1);    --{"hadoop":1,"spark":1}

3）map_keys（map）：返回 map 中的 key

select map_keys(map('hadoop',1,'spark',1));    --["hadoop","spark"]

4）map_values（map）：返回 map 中的 value

select map_values(map('hadoop',1,'spark',1));    --[1,1]

5）array：声明 array 集合

select array(1,2,3);    --[1,2,3]

6）array_contains（arr，ele）：判断 arr 中是否包含元素 ele

select array_contains(array(1,2,3),3);    --true

7）sort_array（arr）：将 arr 中的元素排序

select sort_array(array(5,3,4,1));    --[1,3,4,5]

8）struct：声明 struct

select struct('name','age','sex');    --{"col1":"name","col2":"age","col3":"sex"}

9）named_struct（）：声明 struct 的属性和值

select named_struct('name',"tom",'age',"18",'sex',"男");    --{"name":"tom","age":"18","sex":"男"}

---

 案例演示

数据准备

create  table  employee(name string,  --姓名sex  string,  --性别birthday string, --出生年月hire_date string, --入职日期job string,   --岗位salary double, --薪资bonus double,  --奖金friends array<string>, --朋友children map<string,int> --孩子
);
insert into employeevalues('张无忌','男','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));

需求

1）统计每个月的入职人数

select month(replace(hire_date,'/','-')) as month,count(*) as cn
from employee
group by
month(replace(hire_date,'/','-'));

2）查询每个人的年龄（年+月）

select name,concat(if(month>=0,year,year-1),'年',if(month>=0,month,12+month),'月') age
from(
select name,year(current_date())-year(t1.birthday) year,month(current_date())-month(t1.birthday) month
from (select name,replace(birthday,'/','-') birthdayfrom employee
)t1)t2;

3）按照薪资进行倒序，如果奖金为 null 置为 0

select name,salary+nvl(bonus,0) 
from employee
order by salary;

4）查询每个人有多少个朋友

select name,size(friends) `朋友` from employee;

5）查询每个人孩子的姓名

select name,map_keys(children) from employee;

6）查询每个岗位男女各多少人

select job,sum(if(sex='男',1,0)) male,sum(if(sex='女',1,0)) female
from employee
group by job;