Hive之函数篇（使用函数看这篇足够了）

目录

查询函数相关信息：

（1）查看系统所有的内置函数

（2）查看string相关的所有函数

（3）查询某个函数的详细信息

单行函数：

（1）算数运算函数：

（2）数值函数：

（3）字符串函数：

 （4）日期函数：（年月日）

（5）流程控制函数

（6）集合函数：

(7)高级聚合函数

炸裂函数：

（一）explode：（炸裂函数---最常用）

（二）posexplode:

（三） inline函数：

  （四）Lateral View:

 窗口函数：

语法-----窗口----基于行

语法-----窗口----基于值：

语法-----窗口----分区：

 语法-----窗口----缺省：

 窗口函数--跨行取值函数：

（一）lead和lag:

 （二）first_value和last_value

 窗口函数---排名函数：

---

hive中的函数其实与Java与MySQL中的函数一样，都是内置函数，实现某些功能。

查询函数相关信息：

（1）查看系统所有的内置函数

show functions;

（2）查看string相关的所有函数

show functions like '*string';

（3）查询某个函数的详细信息

desc function extended substring;

单行函数：

单行函数的特点是一进一出，即输入一行，输出一行。

（1）算数运算函数：

        （一）按位与

select 3&2;

 运算思想:   输入的是10进制  底层会把两个数转换成2进制，让后让两个二进制数进行按位与运算，最后的结果再转换成十进制进行输出。

（2）数值函数：

        （一）round函数（四舍五入）

 round函数默认是直接四舍五入到整数位，但是可以进行限制保留几位小数

select round(3.345,1);

（3）字符串函数：

        （一）substring函数（字符串截取函数）

完整函数有三个参数，参数1：字符串  参数2：从什么位置开始截取（从1开始，正数表示从左往右数数，找到指定位置，负数表示从右往左数数，从-1开始，找到指定位置）参数3：表示截取的字符数，不写默认截取到最后

select substring('facesbook',5);

select substring('facesbook',-4);

 

         （二）replace函数（替换函数）

该函数根据名称便可知道它的功能（替换的是子字符串），参数不再一一解释，运行结果如下：

select replace('xiaotangtongxue','x','X')

 

 （三）regexp_replace函数(正则替换)：

其实就是利用正则表达式进行替换

regexp_replace(string A, string B, string C)

参数一：主要字符串

参数二：正则表达式规则（遇到\要想一想是否使用转义字符）

参数三：替换成的字符串

select regexp_replace("abcd-123-abcd","[0-9]{1,}","&")

（四）regexp(正则匹配)：

若字符串符合正则表达式，则返回true，否则返回false。

（同样是匹配有了like为什么还要使用regexp进行匹配那？）

上述是我自己在学习过程中的想法，经过查资料得出结以下结论：

like只是适用于简单的模糊匹配（如以特定字符开头或结尾，或者包含某些固定模式的字符串），

而正则表达式就适合多种复杂形式，更加灵活，如下例情况：

下述这种情况使用like就不是很好进行匹配的了

select 'dfsaaaa1234' regexp "[0-9]{1,}";

（五）repeat（重复字符串）：

select repeat("123",3);

（六）split(分割函数) ：

在hive中的切割符号不是一个单纯的符号，而是一个使用正则表达式表示

select split("192.168.10.102","\.");

（七）nvl(A,B)（替换null值）

若A的值不为null，则返回A，否则返回B。 

select nvl(null,0);
select nvl(4,0);

（八）concat ：拼接字符串 

concat(string A, string B, string C, ……)

将A,B,C……等字符拼接为一个字符串

select concat("1","-","a","b");

（九）concat_ws：以指定分隔符拼接字符串或者字符串数组

 concat_ws(string A, string…| array(string))

使用分隔符A拼接多个字符串，或者一个数组的所有元素。

select concat_ws("-","qq","weixin","bb","cc");

select concat_ws("-",array("aa","bb","cc","dd"));

（十）get_json_object：（解析json字符串）

函数有两个参数

参数1：传入的是json文件

参数2：传入的是需要查找的位置（加一个$(指代的前边传入的字符串).符号） 

select get_json_object('[{"name":"大海海","sex":"男","age":"25"},{"name":"小宋宋","sex":"男","age":"47"}]','$.[0].name')

 （4）日期函数：（年月日）

（一）unix_timestamp：返回当前或指定时间的时间戳

时间戳：我们常说的时间戳是unix时间戳----指从1970年1月1日00:00:00（UTC--世界协调时间，便于统一时区---可以认为是0时区）起算的秒数，它用于表示某个时间点相对于UNIX纪元时间的时间差。UNIX时间戳通常以整数形式表示。

默认是返回的当前时间的时间戳

select unix_timestamp();

获取指定时间的时间戳：

select unix_timestamp('2022/08/08 08-08-08','yyyy/MM/dd HH-mm-ss');

（注意，在给定时间转换成时间戳的时候是按照0时区---unix格式进行转换，并不是按照当地时区）

逗号之前是要获取的时间，逗号之后是你所提供的时间的格式

（二）from_unixtime：转化UNIX时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

第一个参数为需要转换的时间戳

第二个参数为你需要的特定格式（可以省略）

（三）from_utc_timestamp函数：根据时间戳转换时区

第一个参数：

 第一个参数：是传入的整数（单位为毫秒），我们使用的时间戳为秒,s*1000

第二个参数:是传入的字符串，写时区代码

这里需要注意第一个参数传入的时间戳为int   要把时间戳转换成bigint类型，防止溢出

select from_utc_timestamp(cast(1659946088 as bigint)*1000,'GMT+8')

运行结果会有很多后缀0，如果想去掉则需要调用日期格式化函数进行格式化成你规定的格式

（四）select current_date ： 查看当前日期（当前时区）

select current_date;

（五）current_timestamp：当前的日期加时间，并且精确的毫秒 （当前时区）

select current_timestamp;

（六）month：获取指定日期中的月份

select month('2022-08-08 08:08:08');

（七）day：获取日期中的日

（八）hour：获取日期中的小时

 （九）datediff：两个日期相差的天数（结束日期减去开始日期的天数）

datediff(string enddate, string startdate)

select datediff('2021-08-08','2022-10-09');

（十）date_add：日期加天数

语法：date_add(string startdate, int days)

返回开始日期 startdate 增加 days 天后的日期

select date_add('2022-08-08',2);  

（十一）date_sub：日期减天数

（十二）date_format:将标准日期解析成指定格式字符串 

select date_format('2022-08-08','yyyy年-MM月-dd日')  

（5）流程控制函数

（一）case when：条件判断函数

语法一：case when a then b [when c then d]* [else e] end

when后边跟的是一个判断语句

说明：如果a为true，则返回b；如果c为true，则返回d；否则返回 e

select case when 'tan'='tanh' then '棒' when 'xia'='xia' then '棒2' when 'con'='con' then '糖' end

语法二： case a when b then c [when d then e]* [else f] end

说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f

select case'tan' when 'tanh' then '棒' when 'xia' then '棒2' when 'con' then '糖' else '都不对' end;

（二）if: 条件判断，类似于Java中三元运算符 

语法：if（boolean testCondition, T valueTrue, T valueFalseOrNull）

说明：当条件testCondition为true时，返回valueTrue；否则返回valueFalseOrNull

select if(10 > 5,'正确','错误');

（6）集合函数：

（一）size：集合中元素的个数

select size(friends) from test;

（二）map：创建map集合

语法：map (key1, value1, key2, value2, …)

说明：根据输入的key和value对构建map类型

select map('xiaohai',1,'dahai',2); 

 （三）map_keys： 返回map中的key

select map_keys(map('xiaohai',1,'dahai',2));

（四）map_values: 返回map中的value

select map_values(map('xiaohai',1,'dahai',2));

（五）array 声明array集合

语法：array(val1, val2, …)

说明：根据输入的参数构建数组array类

 select array('1','2','3','4');

 （六）array_contains: 判断array中是否包含某个元素

select array_contains(array('a','b','c','d'),'e');

（七）sort_array：将array中的元素排序

 select sort_array(array('a','d','c'));

（八）struct：声明结构体

根据输入的参数构建结构体struct类

只是声明了一个结构体 

select struct('name','age','weight');

 （九）named_struct声明struct的属性和值

select named_struct('name','xiaosong','age',18,'weight',80);

(7)高级聚合函数

（一）collect_list ：收集并形成list集合，结果不去重

select collect_list(job) from employee

（二）collect_set ：收集并形成set集合，结果去重

select collect_set(job) from employee

样例：

每个月的入职人数以及姓名

select month(replace(hiredate,'/','-')) , count(1),collect_list(name) from employee group by month(replace(hiredate,'/','-'));

炸裂函数：

炸裂函数：UDTF：输入一行数据，输出一行或多行数据

注意：（炸裂的类型都是数组--array）

（一）explode：（炸裂函数---最常用）

功能：传入一个数组，它会把数组中的数据进行炸裂成多行

案例一:

select explode(array("1","b","c"))  as itmo

案例二：

select explode(`map`("a",1,"b",2)) as (key,value)

 

（二）posexplode:

 功能：返回两列数据（炸裂的元素和下标）

select posexplode(`array`("a","b","c")) as (pos,item)

（三） inline函数：

功能：传入的是结构体类型的数据进行炸裂

select inline(array( named_struct("id",1,"name","zs"),
                      named_struct("id",2,"name","ls"),
                      named_struct("id",3,"name","txc")))
    as (id,name);

  （四）Lateral View:

炸裂函数一般炸裂一行数据，Lateral View是相当于对每行数据使用炸裂函数让后与源表进行相连，在查询过程中是把他们联合后的虚表当成查询的源表。

 参数1介绍：

tmp是一个explode炸裂函数炸裂之后所形成的表的表名

 参数2介绍：

hobby是表示的给tmp表中字段起的字段名，如果有多个就用   ,    隔开

炸裂函数案例：

select type,count(1) 
from movie_info  
LATERAL view explode(split(category,','))  tmp 
as type group by type;

 窗口函数：

窗口函数是窗口+函数的结合体，窗口用于定义计算范围，函数是用于定义计算逻辑，对窗口范围中的数据进行计算。

语法：

大多数聚合函数能配合窗口函数进行计算（都属于多到一）

窗口的定义有两种：（1）基于行的（行关系）（2）基于值的（值关系）

 

语法-----窗口----基于行

在基于行进行计算的过程中，在使用mapreduce进行计算的过程中会形成切片，得到的窗口，顺序并不一定是原表的顺序，所以在定义窗口范围的时候需要使用order by排序某个字段（在切片的时候就会按照某种顺序进行）

语法样例：

语法-----窗口----基于值：

 基于值的order by的作用是：选择那个字段进行划分

注：（order by在使用preceding与following的时候注意所选的字段为数值类型）

语法-----窗口----分区：

分区：定义窗口的时候会指定分区字段，每个分区字段单独划分窗口

 partition by就是分区的字段（这个字段的值不同就是不同的分区）

 语法-----窗口----缺省：

 这里的缺省主要是针对在使用窗口函数的过程中的关键字的缺省

 窗口函数--跨行取值函数：

（一）lead和lag:

功能：获取当前行的上/下边某行，某字段的值

 语法：

 注：lag和lead函数不支持自定义窗口（只需要在over中标注需不需要分区，需不需要排序即可）。

 （二）first_value和last_value

功能：获取窗口内的指定列（一列）的第一个值/最后一个值

（都是逐行进行，每行的数据不一定相同，都从第一条数据开始）

语法： 

 这两个函数可以自己定义窗口

运行样例结果：

 窗口函数---排名函数：

常用的排名函数为：rank 、dense_rank、row_number

上述三种方法在排名规则上略有不同，请看下边样例

功能：计算排名

注：rank 、dense_rank、row_number不支持自定义窗口。

语法： 

查询结果：