函数
函数简介
方便完成我们一些复杂的操作，就好像我们 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); --返回hello select substring("hello",1,2); --返回he select substring("hello",-4); --返回ello select 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');
注意：
- 如果是 json 数组，可以通过 '$.[0].name' 的方式获取到下标为 0 的json对象的 name 属性值。
- 注意如果 json 字符串内用的是双引号，那么我们的函数参数最好用单引号。
1.4、日期函数
我们的日期时间基本格式为：年-月-日 时:分:秒
1）unix_timestamp（[date_str，format]）：返回当前或指定时间的时间戳
解释：将时间字符串 date_str 根据 format 格式解析为时间戳。
注意：一定要注意字符串 date_str 中的间隔符必须要和 format 中一一对应。
select unix_timestamp(); --当前时间的时间戳 1696390814 select unix_timestamp('2023/10/04 11-41-08','yyyy/MM/dd HH-mm-ss'); -- 1696419668
2）from_unixtime（timestamp）：将时间戳转为时间
注意：
- 这里的参数不是字符串，而是一个数值。
- from_unixtime() 只能精确到秒级别。
- from_utc_time() 可以精确到毫秒级别，但相应的如果参数是 unix_timestamp 需要*1000 转为毫秒数。
select from_unixtime(1696419668); --输出 2023-10-04 11:41:08 select 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-30 select 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, case when 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 score when 90 then 'A' when 80 then 'B' when 70 then 'C' when 60 then 'D' end as grade from score_info;
2）if：条件判断，类似于 Java 的三元运算符
1. select stu_id,course_id, 2. if(score>60,'及格','不及格') `grade` 3. 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 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));
需求
1）统计每个月的入职人数
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,'/','-') birthday from 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;
