Day05-Hive常用函数

1、内置函数

内置函数是Hive提供的一组预定义函数，如数值函数、集合函数、类型函数、字符串函数等。

查看所有函数：
show functions;
查看某函数使用帮助文档: desc function extended 函数名; （extended关键字能查看详细信息示例，可以加可以不加）

如：
describe function extended avg;
运行结果：

翻译：

2、自定义函数

目前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 function;
查看某函数使用文档
extended可加可不加，加上extended关键字能查看详细信息示例
 desc function extended 函数名; 
1、复杂类型函数

①array类型——数组类型

取值: 字段名[索引] 注意: 索引从0开始

如查询数组
select array('caijiuduolian','666','888');
获取长度的函数: size(字段名)

如查询数组长度
select size(array('caijiuduolian','666','888'));
判断是否包含某个数据的函数: array_contains(字段名,某数据)
如查询这个数组中是否有这个数据——运行得到true
select array_contains(array('caijiuduolian','666','888'),'caijiuduolian');
对数组进行排序的函数: sort_array(数组)

对这个数组排序——运行得到 [1,2,3,4,5]
select sort_array(array(3,1,5,2,4));
②map类型——映射类型（键值对）

取值: 字段名[key]——类似于根据键查询
 select map('a',1,'b',2,'c',3);
获取长度的函数: size(字段名)—— 查询键值对长度
select size(map('a',1,'b',2,'c',3));
获取所有key的函数: map_keys() —— 查询所有的键，运行得到 ["a","b","c"]
 select map_keys(map('a',1,'b',2,'c',3));
获取所有value的函数: map_values()——查询所有的值，运行得到[1,2,3]
 select map_keys(map('a',1,'b',2,'c',3));
③struct类型——结构类型

取值: 字段名.子字段名n

2、字符串函数

常见字符串函数

①concat：字符串紧凑拼接到一起生成新字符串
--如：运行得到 'duolian666888'

select concat('duolian', '666', '888'); 
②concat_ws：字符串用指定分隔符拼接到一起生成新字符串
--如： 运行得到'duolian-666-888'

select concat_ws('-', 'duolian', '666', '888');
③length：获取字符串长度
--如：运行得到7

select length('cai-666');
④lower：把字符串中的字母全部变成小写
--如：运行得到'cai-666'

select lower('CAI-666');
⑤upper：把字符串中的字母全部变成大写
--如：运行得到'CAI-666'

select upper('cai-666');
⑥trim：把字符串两端的空白去除
--如：运行得到'cai 666'
select trim('   cai666  ');
另外：
rtrim：去掉字符串右端空白
ltrim：去掉字符串左端空白

其他字符串函数

①substr—— 截取字符串

格式： substr(字符串,开始索引,截取长度): 截取字符串,截取长度如果不写默认到结尾
        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 substr(`current_date`(),1,7);
②replace——替换字符串

replace(大字符串,敏感词,替换后的内容):
 select replace('你靓仔哦','靓仔','菜');--将靓仔替换为菜
③regexp_replace——正则替换字符串

格式：regexp_replace(str, regexp, rep)
--\d+ 表示匹配一个或多个连续的数字字符，第一个\表示转义，因为\d有特殊含义会识别不到，\\d后就变成识别0-9的数字

--如下，
select regexp_replace('cai-666', '\\d+', 'num');
另：解析函数：regexp_extract(str, regexp[, idx])
正则中()代表分组,自动从1开始生成编号,提取正则匹配到的指定组内容
--运行得到888，其中 '(\\d+)-(\\d+)'为捕获两个数字序列，888为第二个数字序列
select regexp_extract('duolian-666-888', '(\\d+)-(\\d+)', 2);
④parse_url—— 解析url(统一资源定位符)

协议/主机地址:端口号/资源路径?查询参数
-- 返回URL的主机部分： www.caijiuduolian.cn
select parse_url('http://www.caijiuduolian.cn/path/cai.html?user=cai&pwd=123', 'HOST');

--返回URL的路径部分：/path/cai.html

select parse_url('http://www.caijiuduolian.cn/path/cai.html?user=cai&pwd=123', 'PATH');
-- 返回URL的查询字符串部分：user=cai&pwd=123
select parse_url('http://www.caijiuduolian.cn/path/cai.html?user=cai&pwd=123', 'QUERY');

--返回URL查询字符串中名为user的参数值：cai
select parse_url('http://www.caijiuduolian.cn/path/cai.html?user=cai&pwd=123', 'QUERY', 'user');
--返回URL查询字符串中名为pwd的参数值：123
select parse_url('http://www.caijiuduolian.cn/path/cai.html?user=cai&pwd=123', 'QUERY', 'pwd');
⑤get_json_object—— 获取json对象解析对应数据

格式：get_json_object(json_txt, path)

整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
json字符串的格式: {键:值, 键: 值}
json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}]
-- 运行得到杨, $表示json对象
select get_json_object('{"name":"杨", "age":"18"}', '$.name');     

-- 运行得到{"name":"杨", "age":"18"}

select get_json_object('[{"name":"杨", "age":"18"}, {"name":"小龙", "age":"26"}]', '$.[0]'); 

-- 运行得到小龙,   $表示json对象

select get_json_object('[{"name":"杨", "age":"18"}, {"name":"小龙", "age":"26"}]', '$.[1].name'); 
3、日期时间函数

①日期:获取指定的时间的年月日...
--如：运行得到2023

select year('2023-05-21 11:19:31.222000000'); 
year: 获取指定日期时间中的年
month:获取指定日期时间中的月
day:获取指定日期时间中的日
hour:获取指定日期时间中的时
minute:获取指定日期时间中的分
second:获取指定日期时间中的秒
dayofmonth: 获取指定日期时间中的月中第几天
dayofweek:获取指定日期时间中的周中第几天
quarter:获取指定日期时间中的所属季度
weekofyear:获取指定日期时间中的年中第几周

②current_timestamp：获取当前时间戳(时间原点到现在的秒/毫秒)

获取时间原点到现在的秒/毫秒,底层自动转换方便查看的日期格式
select current_timestamp();
③unix_timestamp：获取当前时间戳(时间原点到现在的秒/毫秒)
--格式
select unix_timestamp();
--如： 可以使用yyyyMMdd HH:mm:ss进行格式化转换
select unix_timestamp('2023-05-21 11:38:56','yyyy-MM-dd HH:mm:ss'); 
④from_unixtime：把unix时间戳转换为日期格式的时间

如果传入的参数是0,获取的是时间原点1970-01-01 00:00:00
--如：
select from_unixtime(1684669136); -- 2023-05-21 11:38:56
--获取时间原点日期
select from_unixtime(0); -- 1970-01-01 00:00:00
⑤current_date：获取当前日期(年月日)
--如：
select from_unixtime(1684669136); -- 2023-05-21 11:38:56
--获取时间原点日期
select from_unixtime(0); -- 1970-01-01 00:00:00
⑥to_date：字符串格式时间戳转日期(年月日)
--手动输入或用时间戳转
select to_date('2023-05-21 11:19:31.222000000');
select to_date(current_timestamp());
⑦datediff：获取两个指定时间的差值
--如：当前日期和2023-11-03的时间差

select datediff(`current_date`(),'2023-11-03'); 
⑧date_add：在指定日期时间上加几天
-- 获取明天的日期
select date_add(current_timestamp(),1);

--获取昨天的日期
select date_add(current_timestamp(),-1);
⑨date_sub：在指定日期时间上减几天
--获取明天的日期
select date_sub(current_timestamp(),-1);
-- 获取昨天的日期
select date_sub(current_timestamp(),1);
4、数字函数

①round: 指定小数保留位数

四舍五入设置保留位数
--如：运行得到3.1416

select round(pi(),4);
②rand: 生成0-1的随机数

应用解决数据倾斜问题,可以把之前大量相同的值后面拼接随机数
--如：可能得到0.1112324354643242

select rand();
③pi: 生成π结果
select pi();
④ceil: 向上取整——floor: 向下取整
--如：运行得到4
select ceil(pi());

--如：运行得到3
select floor(pi());
5、条件函数

①if(条件判断,true的时候执行此处,false的时候执行此处)
select if(10 > 5, '真', '假'); -- 真
②isnull(数据) :判断是否为空

为空: true
不为空:false
 select isnull(null); -- true
③isnotnull(数据):判断是否为空

为空null:false
不为空: true
select isnotnull('菜就多练'); -- true
④nvl(数据,参数2)：如果数据不为空打印数据,为空null打印第二个参数
--如：为空打印666
select nvl(null,'666');
--如：不为空打印cai
select nvl('cai','666');
⑤coalesce(参数1,参数2...):返回第一个不是null的值

从左到右依次查找,返回第一个不是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
⑥条件转换函数格式
--case给值判断
    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;
--一个一个判断是否等于
    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;
6、类型转换函数——cast(数据 as 要转换的类型)
 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
应用场景：concat_ws要求被连接的必须是字符串,如果直接用666就报错
--如以下代码会报错,因为concat_ws只能拼接字符串类型
select concat_ws('_','cai',666,'888'); 
--转换后，得到cai_666_888
select concat_ws('_','cai',cast(666 as string),'888'); 
7、数据脱敏函数

①mask_hash：返回指定字符串的hash编码
select mask_hash('cai');
②mask：覆盖值

将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
--如，运行得到xxxnnnXXX
select mask("abc123DEF");
--自定义替换的字母: 依次为大写小写数字，运行得到小小小数数数大大大
select mask("abc123DEF",'大','小','数');
--运行得到...%%%///
select mask("abc123DEF",'/','.','%');
③mask_first_n(string str[, int n]：对前n个进行脱敏替换

大写字母转换为X，小写字母转换为x，数字转换为n。
--如：换前6个，得到xxxnnnDEF
select mask_first_n("abc123DEF",6);
④mask_show_first_n(string str[, int n])：除了前n个字符，其余进行掩码处理
--如：除了前6个都换，得到abc123XXX
select mask_show_first_n("abc123DEF",6);
⑤mask_last_n(string str[, int n])：对后n个进行脱敏替换
--如：换后6个，得到abcnnnXXX

select mask_last_n("abc123DEF",6);
⑥mask_show_last_n(string str[, int n])：除了后n个字符，其余进行掩码处理
--如：除了后6个都换，xxx123DEF

select mask_show_last_n("abc123DEF",6);
8、炸裂函数——explode(容器)

把一个容器的多个数据炸裂出单独展示:
炸裂函数一般配合侧视图使用，属于UDTF—一进多出
--格式：

select 原表别名.字段名,侧视图名.字段名 from 原表 原表别名 lateral view explode(要炸开的字段) 侧视图名 as 字段名 ;
--如：拆数组
select explode(array('cai', '666', '888'));

--如： 拆键值对
select explode(map('a', 1, 'b', 2, 'c', 3));
--实例： 先使用炸裂函数测试是否能够变成多行,再使用侧视图保证炸裂结果和原表数据同时展示

select col1,col2,lv.col33 from col2row2 lateral view explode(split(col3,',')) lv as col33;
9、开窗函数

窗口函数结果都是单独生成一列存储对应数据

常用开窗函数

与SQL相同的开窗函数（在sql查询篇）

开窗函数可以与分组、排序关键字配合使用

开窗函数控制范围

unbounded: 无界限
x preceding:往前x行
x following:往后x行
current row:当前行
unbounded preceding :表示从前面的起点第一行
unbounded following :表示到后面的终点最后一行

例子：
--默认从第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;

--第一行到当前行 等效于rows between不写 默认就是第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;


--向前3行至当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;

--向前3行 向后1行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;

--当前行至最后一行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;

--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
from website_pv_info;
其他开窗函数

①ntile(x)功能: 将分组排序之后的数据分成指定的x个部分（x个桶）

ntile规则:尽量平均分配，优先满足最小(编号1)的桶，彼此最多不相差1个。
--把每个分组内的数据分为3桶
SELECT
    cookieid,
    createtime,
    pv,
    ntile(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;
②lag: 用于统计窗口内往上第n行值
--lag 用于统计窗口内往上第n行值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    lag(createtime, 1) over (partition by cookieid order by createtime) la1,
    lag(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;
③lead: 用于统计窗口内往下第n行值
--lead 用于统计窗口内往下第n行值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    lead(createtime, 1) over (partition by cookieid order by createtime) la1,
    lead(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;
④first_value: 取分组内排序后，截止到当前行，第一个值
--FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    first_value(url) over (partition by cookieid order by createtime) fv
from website_url_info;
⑤last_value : 取分组内排序后，截止到当前行，最后一个值
--LAST_VALUE  取分组内排序后，截止到当前行，最后一个值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    last_value(url) over (partition by cookieid order by createtime rows between unbounded preceding and unbounded following) fv
from website_url_info;
10、其他函数

①hash——取哈希值函数
select hash("cai");
②md5(string/binary)—— MD5加密
结果是32位的
 select md5("cai");
③ sha1(string/binary)——SHA-1加密

结果是40位的
select sha1("cai"); 
④sha2(string/binary, int) ——SHA-2家族算法加密
(SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("cai",224);
select sha2("cai",512);
⑤crc32加密
select crc32("cai"); 
⑥获取当前用户、登录用户、当前数据库和数据库版本信息

其中

    current_user()：返回执行当前查询的用户的名称。
   logged_in_user()：返回当前登录用户的用户名
   current_database()：返回当前正在使用的数据库的名称。
   version()：返回数据库管理系统的版本信息。
select current_user(),logged_in_user(),current_database(),version();

另：行转列、列转行

行转列

collect_set(字段名): 把多个数据收集到一起,默认去重
collect_list(字段名): 把多个数据收集到一起,默认不去重
把多个子串用指定分隔符拼接成一个大字符串: concat_ws(分隔符,多个数据...) 注意: 如果拼接数据不是字符串可以使用cast转换
--建表
create table row2col2(
                         col1 string,
                         col2 string,
                         col3 int
)row format delimited fields terminated by '\t';

--加载数据到表中
--数据为以下
a	b	1
a	b	2
a	b	3
c	d	4
c	d	5
c	d	6

-- 验证数据
select * from row2col2;
/*
需求1: 把原表数据变成以下格式
a b [1,2,3]
c d [4,5,6]
*/
select
    col1,
    col2,
    collect_list(col3)
from
    row2col2
group by
    col1, col2;

/*
需求2: 把原表数据变成以下格式
a b '1-2-3'
c d '4-5-6'
*/
select
    col1,
    col2,
    concat_ws('-',collect_list(cast(col3 as string)))
from
    row2col2
group by
    col1, col2;
列转行

把字符串按照指定分隔符切割: split(字符串,分隔符)

炸裂函数配合侧视图使用格式: select 原表别名.字段名,侧视图名.字段名 from 原表原表别名 lateral view explode(要炸开的字段) 侧视图名 as 字段名 ;
--创建表
create table col2row2(
                         col1 string,
                         col2 string,
                         col3 string
)row format delimited fields terminated by '\t';

--加载数据
--以下为数据
a	b	1,2,3
c	d	4,5,6

-- 验证数据
select * from col2row2;


-- 单列数据先切割再炸开
select split(col3,',') from col2row2;
select explode(split(col3,',')) from col2row2;

-- 再去完成需求,列转行生成最后完整表
select col1,col2,tmp.col3
from col2row2
 lateral view explode(split(col3,',')) tmp as col3;