函数分类标准
知识点
原生分类标准: 内置函数 和 用户定义函数(UDF,UDAF,UDTF)
分类标准扩大化: 本来,UDF 、UDAF、UDTF这3个标准是针对用户自定义函数分类的; 但是,现在可以将这个分类标准扩大到hive中所有的函数,包括内置函数和自定义函数;
目前hive三大标准
UDF:(User-Defined-Function)普通函数: 特点是一进一出(输入一行数据输出一行数据) 举例: split
UDAF:(User-Defined Aggregation Function)聚合函数: 特点是多进一出(输入多行输出一行) 举例: count sum max min avg
UDTF:(User-Defined Table-Generating Functions)表生成函数: 特点是一进多出(输入一行输出多行) 举例: explode查询所有hive函数名称: show functions;
查看某函数使用帮助文档: desc function [extended] 函数名; 注意: 加上extended关键字能查看详细信息示例
示例
-- 查看所有函数
show functions;
-- 分类标准扩大化
-- UDF: 普通函数 特点: 一进一出 举例: split()
-- 查看官方示例
describe function extended split;
-- 演示官方示例
SELECT split('oneAtwoBthreeCfour', '[ABC]'); -- ["one","two","three","four"]
-- UDAF: 聚合函数 特点: 多进一出 举例: sum() count() avg() max() min()
-- 查看官方示例
describe function extended count;
/*
count(*): 不忽略null值统计个数
count(字段名): 忽略null值统计个数
count(常量): 举例 : count(1) count(2) ...
count(distinct 字段名): 忽略null值并且去重统计个数
*/
-- UDTF: 表生成函数 特点: 一进多哦出 举例: explode()
-- 查看官方示例
describe function extended explode;
/*
将数组a的元素分成多行,或将映射的元素分成多行和多列
数组: array[元素1,元素2,元素3...] array(10,20,30)能够构造出数组[10,20,30]
映射: map{k1:v1,k2:v2...} map('a',10,'b',20,'c',30)构造出映射{'a':10,'b':20,'c':30}
*/
-- 演示炸裂函数
select explode(array(10,20,30));
select explode(map('a',10,'b',20,'c',30));
复杂类型函数
hive复杂类型: array struct map
array类型: 又叫数组类型,存储同类型的单数据的集合
取值: 字段名[索引] 注意: 索引从0开始
获取长度的函数: size(字段名) 常用
判断是否包含某个数据的函数: array_contains(字段名,某数据) 常用
对数组进行排序的函数: sort_array(数组)struct类型: 又叫结构类型,可以存储不同类型单数据的集合
取值: 字段名.子字段名n
map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)
取值: 字段名[key]
获取长度的函数: size(字段名) 常用
获取所有key的函数: map_keys() 常用
获取所有value的函数: map_values() 常用
-- 演示集合函数
select array('binzi','666','888');
select size(array('binzi','666','888'));
select array_contains(array('binzi','666','888'),'binzi');
select sort_array(array(3,1,5,2,4)); -- [1,2,3,4,5]
select map('a',1,'b',2,'c',3);
select size(map('a',1,'b',2,'c',3));
select map_keys(map('a',1,'b',2,'c',3));-- ["a","b","c"]
select map_values(map('a',1,'b',2,'c',3));-- [1,2,3]
字符串函数
字符串常见的函数:
concat: 字符串紧凑拼接到一起生成新字符串
concat_ws: 字符串用指定分隔符拼接到一起生成新字符串 常用
length: 获取字符串长度 常用
lower: 把字符串中的字母全部变成小写
upper: 把字符串中的字母全部变成大写
trim: 把字符串两端的空白去除 常用拓展字符串函数
substr: 截取字符串 常用
replace: 替换字符串 常用
regexp_replace: 正则替换字符串
parse_url: 解析url(统一资源定位符) 组成: 协议/主机地址:端口号/资源路径?查询参数
get_json_object: 获取json对象解析对应数据
-- 1.字符串相关函数
-- 演示字符串常见的函数
-- concat: 字符串紧凑拼接到一起生成新字符串
select concat('binzi', '666', '888'); -- 'binzi666888'
-- concat_ws: 字符串用指定分隔符拼接到一起生成新字符串
select concat_ws('-', 'binzi', '666', '888'); -- 'binzi-666-888'
-- length: 获取字符串长度
select length('binzi-666'); -- 9
-- lower: 把字符串中的字母全部变成小写
select lower('BINZI-666'); -- 'binzi-666'
-- upper: 把字符串中的字母全部变成大写
select upper('binzi-666'); -- 'BINZI-666'
-- trim: 把字符串两端的空白去除
select ' binzi 666 '; -- ' binzi 666 '
select trim(' binzi 666 ');-- 'binzi 666'
-- substr(字符串,开始索引,截取长度): 截取字符串,截取长度如果不写默认到结尾
-- substring(字符串,开始索引,截取长度): 截取字符串
-- 注意: 正索引从1开始正着数 负索引从-1开始倒着数
select substr('binzi666',1,2); -- 'bi'
select substr('binzi666',1); -- -- 'binzi666'
select substr('binzi666',-4);--'i666'
-- 已知'2023-05-21'要求分别截取年月日
select substr('2023-05-21',1,4); -- 结果2023
select substr('2023-05-21',1,7); -- 结果2023-05
select substr('2023-05-21',6,2); -- 结果05
select substr('2023-05-21',-2,2); -- 结果21
-- substr经常用于截取日期中的年月
select `current_date`();
select substr(`current_date`(),1,7);
-- replace(大字符串,敏感词,替换后的内容):替换字符串
select replace('你TMD哦','TMD','挺萌的');
select replace('binzi-666', '666', 'num');
--正则表达式替换函数:regexp_replace(str, regexp, rep)
select regexp_replace('binzi-666', '\\d+', 'num');
--正则表达式解析函数:regexp_extract(str, regexp[, idx])
-- 正则中()代表分组,自动从1开始生成编号,提取正则匹配到的指定组内容
select regexp_extract('binzi-666-888', '(\\d+)-(\\d+)', 1);
select regexp_extract('binzi-666-888', '(\\d+)-(\\d+)', 2);
--URL解析函数:parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
-- URL: 统一资源定位符 也就是咱们常说的网址 组成: 协议/主机地址:端口号/资源路径?查询参数
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'HOST'); -- www.itcast.cn
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'PATH'); -- /path/binzi.html
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY'); -- user=binzi&pwd=123
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'user'); -- binzi
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'pwd'); -- 123
-- json解析函数:get_json_object(json_txt, path), 细节: 整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
-- json字符串的格式: {键:值, 键: 值}
-- json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}] -- 索引从 0 开始.
select get_json_object('{"name":"杨过", "age":"18"}', '$.name'); -- 杨过, $表示json对象
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[0]'); -- {"name":"杨过", "age":"18"}
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name'); -- 小龙女, $表示json对象
日期时间函数
current_timestamp: 获取时间原点到现在的秒/毫秒,底层自动转换方便查看的日期格式 常用
to_date: 字符串格式时间戳转日期(年月日)
current_date: 获取当前日期(年月日) 常用year: 获取指定日期时间中的年 常用
month:获取指定日期时间中的月 常用
day:获取指定日期时间中的日 常用
hour:获取指定日期时间中的时
minute:获取指定日期时间中的分
second:获取指定日期时间中的秒dayofmonth: 获取指定日期时间中的月中第几天
dayofweek:获取指定日期时间中的周中第几天
quarter:获取指定日期时间中的所属季度
weekofyear:获取指定日期时间中的年中第几周datediff: 获取两个指定时间的差值 常用
date_add: 在指定日期时间上加几天 常用
date_sub: 在指定日期时间上减几天unix_timestamp: 获取unix时间戳(时间原点到现在的秒/毫秒) 注意: 可以使用yyyyMMdd HH:mm:ss进行格式化转换
from_unixtime: 把unix时间戳转换为日期格式的时间 注意: 如果传入的参数是0,获取的是时间原点1970-01-01 00:00:00
-- 2.日期时间函数
-- 获取当前时间戳(时间原点到现在的秒/毫秒)
select unix_timestamp(); -- 10位的数字代表多少秒
select current_timestamp(); -- 自动转换 年月日时分秒格式
-- 获取当前日期
select current_date();
-- 字符串格式时间戳转日期
select to_date('2023-05-21 11:19:31.222000000');
select to_date(current_timestamp());
-- 依次获取年月日时分秒
select year('2023-05-21 11:19:31.222000000'); -- 2023
select month('2023-05-21 11:19:31.222000000'); -- 5
select day('2023-05-21 11:19:31.222000000'); -- 21
select hour('2023-05-21 11:19:31.222000000'); -- 11
select minute('2023-05-21 11:19:31.222000000'); -- 19
select second('2023-05-21 11:19:31.222000000'); -- 31
-- 依次获取月中第几天,周中第几天,季度,年中第几周
select dayofmonth('2023-05-21 11:19:31.222000000'); -- 21
select dayofweek('2023-05-21 11:19:31.222000000'); -- 1
select quarter('2023-05-21 11:19:31.222000000'); -- 2
select weekofyear('2023-05-21 11:19:31.222000000'); -- 20
-- 计算时间差
select datediff(`current_date`(),'2023-11-03'); -- 12
-- 获取明天的日期
select date_add(current_timestamp(),1);
select date_sub(current_timestamp(),-1);
-- 获取昨天的日期
select date_sub(current_timestamp(),1);
select date_add(current_timestamp(),-1);
-- 拓展
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();
--字符串日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2023-5-21 11:38:56"); -- 1684669136
--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('2023-05-21 11:38:56','yyyy-MM-dd HH:mm:ss'); --1684669136
--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1684669136); -- 2023-05-21 11:38:56
-- 获取时间原点日期
select from_unixtime(0); -- 1970-01-01 00:00:00
数学函数
round: 指定小数保留位数 常用
rand: 生成0-1的随机数
pi: 生成π结果
ceil: 向上取整
floor: 向下取整
-- 演示数学函数
-- 随机数
select rand();
-- 应用解决数据倾斜问题,可以把之前大量相同的值后面拼接随机数
select concat('男',rand());
select concat('男',rand());
-- 获取π值
select '3.1415926';
select pi();
-- 四舍五入设置保留位数
select round(pi(),4);
-- 向上取整
select ceil(pi());
-- 向下取整
select floor(pi());
条件函数
if(参数1,参数2,参数3): 如果参数1结果为true,就执行参数2内容,否则执行参数3的内容
case...when.then...end: 条件判断类似于编程语言中的if..else if ...else... 常用
isnull(数据) : 为空null: true 不为空:falseisnotnull(数据): 不为空: true 为空null:false
nvl(数据,参数2): 如果数据不为空打印数据,为空null打印第二个参数 常用
coalesce(参数1,参数2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null
-- 演示条件函数
-- if(条件判断,true的时候执行此处,false的时候执行此处)
select if(10 > 5, '真', '假'); -- 真
select if(10 < 5, '真', '假');
--条件转换函数格式1: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select
case 7
when 1 then '周一上班'
when 2 then '周二上班'
when 3 then '周三上班'
when 4 then '周四上班'
when 5 then '周五上班'
when 6 then '周六休息'
when 7 then '周日休息'
else '老弟啊,你是外星人吗?'
end;
-- 条件转换函数格式2:CASE WHEN a==b THEN a==c [WHEN a==d THEN a==e]* [ELSE f] END
select
case
when 7==1 then '周一上班'
when 7==2 then '周二上班'
when 7==3 then '周三上班'
when 7==4 then '周四上班'
when 7==5 then '周五上班'
when 7==6 then '周六休息'
when 7==7 then '周日休息'
else '老弟啊,你是外星人吗?'
end;
-- 演示null相关函数
-- isnull(数据) 为空: true 不为空:false
select isnull(null); -- true
-- isnotnull(数据) 不为空: true 为空:false
select isnotnull('斌子'); -- true
-- nvl(数据,前面的数据是null的时候执行此处): 如果数据不为空打印数据,为空打印第二个参数
select nvl('binzi','666');
select nvl(null,'666');
-- coalesce(v1,v2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null
select COALESCE(null,11,22,33);-- 11
select COALESCE(null,null,22,33);--22
select COALESCE(null,null,null,33);--33
select COALESCE(null,null,null,0);--0
select COALESCE(null,null,null,null);--null
类型转换函数
类型转换: cast(数据 as 要转换的类型)
-- 演示类型转换函数
-- cast: 主要用于类型转换 注意: 转换失败返回null
select cast(3.14 as int); -- 3
select cast(3.14 as string) ; -- '3.14'
select cast('3.14' as float); -- 3.14
select cast('3.14' as int); -- 3
select cast('binzi' as int); -- null
-- -- 注意: 很多时候底层都默认做了自动转换
select '3'+3; -- 6
-- 实际应用场景:concat_ws要求被连接的必须是字符串,如果直接用666就报错
select concat_ws('_','binzi',666,'888'); --此行报错,因为concat_ws只能拼接字符串类型
select concat_ws('_','binzi',cast(666 as string),'888'); -- binzi_666_888
数据脱敏函数
-- 演示数据脱敏函数[了解]
-- mask_hash: 返回指定字符串的hash编码
select mask_hash('binzi');
-- 拓展
--将查询回的数据,大写字母转换为X,小写字母转换为x,数字转换为n。
select mask("abc123DEF"); -- xxxnnnXXX
--自定义替换的字母: 依次为大写小写数字
select mask("abc123DEF",'大','小','数');
select mask("abc123DEF",'/','.','%');
--mask_first_n(string str[, int n]
--对前n个进行脱敏替换 大写字母转换为X,小写字母转换为x,数字转换为n。
select mask_first_n("abc123DEF",6);
--mask_last_n(string str[, int n])
--对后n个进行脱敏替换 大写字母转换为X,小写字母转换为x,数字转换为n。
select mask_last_n("abc123DEF",6);
--mask_show_first_n(string str[, int n])
--除了前n个字符,其余进行掩码处理
select mask_show_first_n("abc123DEF",6);
--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",6);
其他函数
-- 演示其他函数
--取哈希值函数:hash
select hash("binzi"); -- 93742710
--MD5加密: md5(string/binary)
select md5("binzi"); -- 32位 072853027b387fcf891a610137f8dc1b
select length('072853027b387fcf891a610137f8dc1b');
--SHA-1加密: sha1(string/binary)
select sha1("binzi"); -- 40位 66368c80ca9125f9a8a945aaf1e1ec3f8b21f7f9
select length('66368c80ca9125f9a8a945aaf1e1ec3f8b21f7f9');
--SHA-2家族算法加密:sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("binzi",224);
select sha2("binzi",512);
--crc32加密:
select crc32("binzi"); -- 3221865747
-- 当前环境相关的
select current_user(),logged_in_user(),current_database(),version();