文章目录
前言:查看函数详细描述的SQL:
describe function extended func_name;
1. 内置函数(Built-in Functions)
官方文档地址:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF
1.1 字符串函数
下面重点解释上面红色函数:
-
字符串拼接
-
直接拼接:
concat(string + | array(string) +)
select concat("angela","baby"); -- 输出:"angelababy" -- 注意:只要有一个元素为null,则返回null select concat("angela","baby", null); -- 输出:null -- 注意:也可以传入集合 select concat(array("a", "b", "c"));
-
指定拼接符:
concat_ws(separator, [string | array(string)]+)
select concat_ws('.', 'www', array('baidu', 'com')); -- 输出:"www.baidu.com" -- 注意1:可以存在元素为null,它会先去掉null,再拼接。比如: select concat_ws("#", "123", "abc", null); -- 输出:”123#abc“ -- 注意2:全部元素为null时,返回的是空字符串'',而不是 null select concat_ws("#", null, null); -- 注意3:也可以传入集合 select concat_ws("#", array("a", "b", "c"));
-
推荐使用
concat_ws()函数
时,使用空字符串''
作为分隔符,因为会更加准确。会使得无用行只算一次,不要小看这一次,当concat_ws('',collection_nid_src,cuid)
,每个cuid会造成一条无效数据,如果有30万用户,使用''
与-
就会相差30万条数据。
当然最准确的做法是将脏数据提前过滤掉,比如:count(distinct case when collection_first='0' and collection_nid_src is not null and collection_nid_src != '' and cuid is not null and cuid != '' then concat_ws('-',collection_nid_src,cuid) end)
-
为什么使用空字符串做分隔更加准确:
- 使用
''
分隔:
- 使用
'-'
分隔:
- 使用
-
-
-
字符串切分
-
切分后是一个数组,可以通过下标访问元素:
split(str, regex | separator)
// 切分字符串 select split('apache hive', ' '); -- 输出:['apache', 'hive'] // 切分字符串并访问元素 select split('apache hive', ' ')[0]; -- 输出:'apache' // 注意:切分null,结果为null select split(null, ','), -- 输出:NULL size(null) -- 输出:-1
注意:当待切分的对象中没有分隔符时,整个对象对作为一个元素
// 比如 select split('ab', ','); -- 输出:['ab'] // 切分空字符串也是同理 select split('', ','); -- 输出:['']
-
切分并访问元素:
substring_index(要处理的字符串, 分隔符, num)
-- 1. num可以为正 select substring_index("www.baidu.com", '.', 1); -- 输出:www select substring_index("www.baidu.com", '.', 2); -- 输出:www.baidu select substring_index("www.baidu.com", '.', 3); -- 输出:www.baidu.com -- 2. num可以为负 select substring_index("www.baidu.com", '.', -1); -- 输出:com -- 3. 像数组用下标一样访问中间元素: 两个方向结合即可 select substring_index( substring_index("www.baidu.com", '.', 2), ".", -1 ); -- 输出:baidu
-
-
获取子字符串:
-
substr(str, pos[, len])
或者substring(str, pos[, len])
【字符串的下标从1开始】-- (1) 如果只有一个数值,则表示从这个位置开始截取到末尾 select substr("angelababy", 2); -- 正数,则从下标2处到末尾。故输出:"ngelababy" select substr("angelababy",-2); -- 正数,则从下标倒数第2处到末尾。故输出:"by" -- (2) 有两个数值,则第一个数值为下标,第二个数值为向后截取多长 select substr("angelababy",2,2); -- 输出:"ng"
-
正则表达式提取:
regexp_extract(str, regexp[, idx])
-- 1. 表达式中,只有一次提取,可以不传 idx 参数 select regexp_extract('100-200', '(\\d+)-\\d+'); -- 输出:"100" -- 2. 表达式中,有多次提取,则需要传 idx 参数,指名提取哪一个。idx从1开始 select regexp_extract('100-200', '(\\d+)-(\\d+)', 2); -- 提取第2个,故输出:"200" select regexp_extract('100-200', '(\\d+)-(\\d+)'); -- 如果没有传 idx,默认提取第1个,故输出:"100"
注意:若待切分的对象非常工整时,可考虑转为Json,借助Json处理
-
利用字符串替换,
regexp_replace
函数,将不需要的替换为空字符串,则等价转换为提取子字符串:-- 提取[]内的字符串 select regexp_replace('[{"payAmount":"375000","payChannelCode":"BOC"},{"payAmount":"376000"}]','\\[|\\]','')
-
-
查询子字符串:
instr(str, substr)
返回substr在str中 第一次 出现的下标。如果substr没有出现,则返回0.【字符串下标从1开始】select instr("aaa,123456123", "123"); -- 输出:5
-
字符串替换
-
被替换部分为固定的字符串:
replace(str, from_str, to_str)
select replace('100-200', '100', 'num'); -- 所有都替换,故为:"100-num"
-
被替换部分需要用正则表达式匹配:
regexp_replace(str, regexp, rep)
select regexp_replace('100-200', '(\\d+)', 'num'); -- 所有都替换,故为:"num-num"
-
-
URL解析函数:parse_url 注意要想一次解析出多个 可以使用
select parse_url('http://www.itcast.cn/path/p1.php?query=1', 'HOST'); -- 输出:"www.itcast.cn"
-
json解析函数:get_json_object(json_txt, path)
--$表示json对象 select get_json_object('[{"website":"www.baidu.com1","name":"allenwoon"}, {"website":"www.baidu.com2","name":"carbondata 中文文档"}]', '$.[1].website'); -- 输出:"www.baidu.com2"
-
空格字符串函数:space
-- 语法: space(int n) -- 返回值: string -- 说明:返回长度为n的空格字符串 select space(10) from dual; -- " " select length(space(10)) from dual; -- 10
1.2 日期函数
下面重点解释上面红色函数:
-- 1. 获取当前日期: current_date
select current_date(); -- 输出:"2022-12-1"
select current_time(); -- 输出:"20:03:19"
-- 2. 获取当前时间戳: current_timestamp: 同一查询中对current_timestamp的所有调用均返回相同的值。
select current_timestamp();
-- 3. 日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2011-12-07 13:01:03"); -- 默认传入的日期格式:默认是"yyyy-MM-dd HH:mm:ss"格式
select unix_timestamp('20111207 13:01:03','yyyyMMdd'); -- 若传入的不是默认日期格式,则需要声明日期格式(都是可以只出现部分日期的)
-- 4. UNIX时间戳转日期函数: from_unixtime
-- from_unixtime() 中必须传入参数,不传入参数会报错
select from_unixtime(1618238391); -- 默认为转化为"yyyy-MM-dd HH:mm:ss"格式
select from_unixtime(0, 'yyyy-MM-dd HH:mm:ss'); -- 可以指定转换后的格式
-- 5. 日期格式转换:
--- (1) 默认格式可以直接转为任意格式
select date_format('2022-08-08','yyyy年-MM月') -- 输出:2022年-08月
--- (2) 从非默认格式转任意格式:必须先转为时间戳,再转为任意格式
select from_unixtime(unix_timestamp('20171205','yyyyMMdd'),'yyyy-MM-dd') from dual;
-- 5. 求两日期天数只差:日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'
-- (1) 单位: 日
-- 是第一个参数 减去 第二个参数,结果可以为负数,结果类似是整型,不是字符串类型
select datediff('2012-12-08','2012-05-09'); -- 输出:213
-- (2) 单位自己指定:timestampdiff(单位, 开始时间, 结束时间)
-- 结果为:结束时间 - 开始时间,再转为对应单位
-- 单位枚举: year、quarter、month、week、day、hour、minute、second
select timestampdiff(minute, "2021-09-01 09:01:00", "2021-09-01 09:31:01"); -- 正数
-- 6. 日期增加函数: date_add
-- (1) 单位: 日
select date_add('2012-02-28',10);
-- (2) 单位自己指定
-- 单位枚举:year、quarter、month、week、day、hour、minute、second
select timestampadd(minute, 20, "2021-09-01 09:31:01");
-- 7. 日期减少函数: date_sub
-- (1) 单位: 日
select date_sub('2012-01-1',10);
-- (2) 单位自己指定
-- 单位枚举:sql_tsi_year、sql_tsi_quarter、sql_tsi_month、sql_tsi_week、sql_tsi_day、sql_tsi_hour、sql_tsi_minute、sql_tsi_second
-- 没有 timestampsub,使用 timestampadd 传入负数实现减法
select timestampadd(minute, -20, "2021-09-01 09:31:01");
-- 8. 获取日期的年月日,返回类型是整型,不是字符串
select year('2012-01-1')
select month('2012-01-1')
select day('2012-01-1')
-- 9. 一年中的多少?
select week('2012-01-1') -- 一年中的第几周
select dayofyear('2012-01-1') -- 一年中的第几天
select dayofmonth('2012-01-1') -- 一个月中的第几天
select dayofweek('2012-01-1') -- 一周中的第几天:返回日期date的星期索引(1=星期天,2=星期一, ……7=星期六)
select weekday(); -- 一周中的第几天::返回date的星期索引(0=星期一,1=星期二, ……6= 星期天)。
1.3 数学函数
-- 1. 四舍五入取整
-- (1) 取整数
select round(3.1415926); -- 输出:3
-- (2) 指定小数位数
select round(3.1415926,4); -- 输出;3.1415
-- 2. 向下取整函数: floor
select floor(-3.1415926); -- 输出:-4
-- 3. 向上取整函数: ceil
select ceil(-3.1415926); -- 输出:-3
-- 4. 随机数
-- (1) 无参:返回一个0到1范围内的随机数,每次执行都不一样
select rand();
-- (2) 指定种子: 返回一个0到1范围内的随机数,使用相同种子得到的随机数相同
select rand(2);
1.4 集合函数
- 【Hive】array相关的函数总结 『数组 | 数组与元素 | 两个数组』
- map
-- 1. 集合大小: size(Map<K.V>) size(Array<T>) select size(array(11,22,33)); -- 输出:3 select size(map("id",10086,"name","zhangsan","age",18)); -- 输出:3 select size(null); -- 输出:-1 select size(from_json("[]", 'array<string>')) -- 输出:0 -- 2. 取map集合keys函数: map_keys(Map<K.V>) select map_keys(map("id",10086,"name","zhangsan","age",18)); -- 3. 取map集合values函数: map_values(Map<K.V>) select map_values(map("id",10086,"name","zhangsan","age",18));
1.5 条件函数
原始数据为:
select * from student limit 3;
以上面的表进行演示:
-- 1. if条件判断: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
select if(1=2,100,200); -- 输出:200
select if(sex ='男','M','W') from student limit 3;
-- 输出:'M'
'W'
'W'
-- 2. if的嵌套: 还是使用上面的if函数,只不过是嵌套使用
-- 注意:if嵌套,它是有一个先后判断关系的,外层的先判断,内层的后判断;
-- 但是if的嵌套可以用if-ifelse-else等价替换,所以if的嵌套不常用
例子请查看网址:https://leetcode.cn/problems/tree-node/description/?favorite=qgq7m9e
-- 2. if-ifelse-else:
-- case
-- when 关于同一个列colName布尔表达式 then 值
-- [when 关于同一个列colName布尔表达式 then 值]
-- ....
-- else 值
-- end as colName1
-- 注意:
-- 1. 一个case...end只能处理一个字段
-- 2. case when,如果有多个,如果一条记录满足第一个case when就不会再去执行后面的case when,所以要想保证某个case when一定执行,就把她写在最前面。这些语法都和java一样,当做java使用就可以了
select
name,
case
when age < 18 then "未成年"
when age >= 18 and age <= 60 then "成年人"
when age >60 then "老年人"
end as ageKind
from student;
-- 3. switch-case:
-- case colName
-- when 值 then 值
-- [when 值 then 值]
-- ....
-- else 值
-- end as colName1
-- 注意:一个case...end只能处理一个字段
select
case sex
when '男' then 'male'
else 'female'
end as sexEnglish
from student limit 3;
-- 3. 空判断函数
select isnull("allen");
select isnotnull(null);
-- 4. 空值转换函数: nvl(T value, T default_value)
-- 如果value为null,就返回default_value替代;如果不为空,则返回value自己
select nvl("allen","itcast"); -- 输出:"allen"
select nvl(null,"itcast"); -- 输出:"itcast"
-- 6. nullif( a, b ):
-- 若a == b,则返回NULL,否则返回第一个数
select nullif(11,11); -- 输出;null
select nullif(11,12); -- 输出:11
-- 7. assert_true(condition)
-- 如果'condition'为false,则引发异常;为true则返回null
SELECT assert_true(11 >= 0); -- 输出:null
SELECT assert_true(-1 >= 0); -- 抛出异常
- 查找多个列的第一个非空值:
coalesce(T v1, T v2, ...)
要求:v1, v2…参数列表的数据类型一样-- 每一行都返回该行的第一个非空值;如果该行的所有列都为NULL,那么返回NULL select colasce(null,11,22,33); -- 输出:11 select colasce(null,null,null); -- 输出:null
- 应用场景1:比如我们要登记用户的电话,数据库中包含他的person_tel,home_tel,office_tel,我们只要取一个非空的就可以,则我们可以写查询语句
select coalesce(person_tel,home_tel,office_tel) as contact_number from Contact;
- 应用场景2:假如某个字段默认是null,你想其返回的不是null,而是比如0或其他值,可以使用这个函数
select coalesce(field_name,0) as value from table;
- 应用场景1:比如我们要登记用户的电话,数据库中包含他的person_tel,home_tel,office_tel,我们只要取一个非空的就可以,则我们可以写查询语句
1.6 数据类型转换函数
-- 如果不能转换,则返回null
select cast(12.14 as bigint); -- 输出:12
select cast(12.14 as string); -- 输出:“12.14”
select cast("abc" as bigint) -- 输出:null
- 注意:为表新添一个字段,且值全制为
null
时,不能null as 字段名
,只能cast(null as 类型) 字段名
。比如:
1.7 数据脱敏函数
-- 1. mask:敏感字符屏蔽
-- (1) 默认:大写字母转换为X,小写字母转换为x,数字转换为n。
select mask("abc123DEF"); -- 输出:"xxxnnnXXX"
-- (2) 自定义替换的字母
select mask("abc123DEF",'-','.','^'); -- 输出:”...^^^---“
-- 2. 对前n个进行脱敏替换: mask_first_n(string str[, int n]
select mask_first_n("abc123DEF",4); -- 输出:"xxxn23DEF"
-- 3. 对末尾n个进行脱敏替换: mask_last_n(string str[, int n])
select mask_last_n("abc123DEF",4); -- 输出:"abc12nXXX"
-- 4. 除了前n个字符,其余进行脱敏替换:mask_show_first_n(string str[, int n])
select mask_show_first_n("abc123DEF",4); -- 输出:"abc1nnXXX"
-- 5. 除了末尾n个字符,其余进行脱敏替换:mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",4); -- 输出:"xxxnn3DEF"
-- 6. 字符串的hash编码:mask_hash(string|char|varchar str)
select mask_hash("abc123DEF");
1.8 其他函数(反射、加密解密、等等)
--hive调用java方法: java_method(class, method[, arg1[, arg2..]])
select java_method("java.lang.Math","max",11,22);
--反射函数: reflect(class, method[, arg1[, arg2..]])
select reflect("java.lang.Math","max",11,22);
--取哈希值函数:hash
select hash("allen");
--current_user()、logged_in_user()、current_database()、version()
--SHA-1加密: sha1(string/binary)
select sha1("allen");
--SHA-2家族算法加密:sha2(string/binary, int) (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("allen",224);
select sha2("allen",512);
--crc32加密:
select crc32("allen");
--MD5加密: md5(string/binary)
select md5("allen");