hive常用SQL函数及案例

1 函数简介

Hive会将常用的逻辑封装成函数给用户进行使用类似于Java中的函数。
好处避免用户反复写逻辑可以直接拿来使用。
重点用户需要知道函数叫什么能做什么。
Hive提供了大量的内置函数按照其特点可大致分为如下几类单行函数、聚合函数、炸裂函数、窗口函数。
以下命令可用于查询所有内置函数的相关信息。

1查看系统内置函数

show functions;

2查看内置函数用法

 desc function upper;

3查看内置函数详细信息

 desc function extended upper;

2 单行函数

单行函数的特点是一进一出即输入一行输出一行。
单行函数按照功能可分为如下几类: 日期函数、字符串函数、集合函数、数学函数、流程控制函数等。

1算术运算函数

案例实操查询出所有员工的薪水后加1显示

select sal + 1 from emp;

3 数值函数

1round四舍五入

select round(3.3);

2ceil向上取整

select ceil(3.1) ;

3floor向下取整

select floor(4.8);

4 字符串函数

(1) substring截取字符串

语法一substring(string A, int start)
返回值string
说明返回字符串A从start位置到结尾的字符串
语法二substring(string A, int start, int len)
返回值string
说明返回字符串A从start位置开始长度为len的字符串
说明获取第二个字符以后的所有字符

说明获取倒数第三个字符以后的所有字符

 select substring("atguigu",-3);

说明从第3个字符开始向后获取2个字符

select substring("atguigu",3,2);

(2) replace 替换

语法replace(string A, string B, string C)
返回值string
说明将字符串A中的子字符串B替换为C

select replace('atguigu', 'a', 'A')

(3regexp_replace正则替换

语法regexp_replace(string A, string B, string C)
返回值string
说明将字符串A中的符合java正则表达式B的部分替换为C。注意在有些情况下要使用转义字符。

select regexp_replace('100-200', '(\\d+)', 'num') 

(4regexp正则匹配

语法字符串 regexp 正则表达式
返回值boolean
说明若字符串符合正则表达式则返回true否则返回false。
说明正则匹配成功输出true

select 'dfsaaaa' regexp 'dfsa+'

说明正则匹配失败输出false

select 'dfsaaaa' regexp 'dfsb+';

(5repeat重复字符串

语法repeat(string A, int n)
返回值string
说明将字符串A重复n遍。

select repeat('123', 3);

(6split 字符串切割

语法split(string str, string pat)
返回值array
说明按照正则表达式pat匹配到的内容分割str分割后的字符串以数组的形式返回。

(7nvl 替换null值

语法nvl(A,B)
说明若A的值不为null则返回A否则返回B。

select nvl(null,1); 

(8concat 拼接字符串

语法concat(string A, string B, string C, ……)
返回string
说明将A,B,C……等字符拼接为一个字符串

select concat('beijing','-','shanghai','-','shenzhen');

(9concat_ws以指定分隔符拼接字符串或者字符串数组

语法concat_ws(string A, string…| array(string))
返回值string
说明使用分隔符A拼接多个字符串或者一个数组的所有元素。

(10get_json_object解析json字符串

语法get_json_object(string json_string, string path)
返回值string
说明解析json的字符串json_string返回path指定的内容。如果输入的json字符串无效那么返回NULL。

获取json数组里面的json具体数据

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

获取json数组里面的数据

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

5 日期函数

(1unix_timestamp返回当前或指定时间的时间戳

语法unix_timestamp()
返回值bigint
说明-前面是日期后面是指日期传进来的具体格式

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

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

语法from_unixtime(bigint unixtime[, string format])
返回值string

select from_unixtime(1659946088);

(3current_date当前日期

select current_date;

(4current_timestamp当前的日期加时间并且精确的毫秒

select current_timestamp;

(5month获取日期中的月

语法month (string date)
返回值int

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

(6day获取日期中的日

语法day (string date)
返回值int

select day('2022-08-08 08:08:08')    

(7hour获取日期中的小时

语法hour (string date)
返回值int

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

(8datediff两个日期相差的天数结束日期减去开始日期的天数

语法datediff(string enddate, string startdate)
返回值int

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

(9date_add日期加天数

语法date_add(string startdate, int days)
返回值string
说明返回开始日期 startdate 增加 days 天后的日期

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

(10date_sub日期减天数

语法date_sub (string startdate, int days)
返回值string
说明返回开始日期startdate减少days天后的日期。

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

(11date_format:将标准日期解析成指定格式字符串

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

6 流程控制函数

(1case when条件判断函数

语法一case when a then b [when c then d]* [else e] end
返回值T
说明如果a为true则返回b如果c为true则返回d否则返回 e

select case when 1=2 then 'tom' when 2=2 then 'mary' else 'tim' end from location; 

语法二 case a when b then c [when d then e]* [else f] end
返回值: T
说明如果a等于b那么返回c如果a等于d那么返回e否则返回f

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end from location; 

(2if: 条件判断类似于Java中三元运算符

语法ifboolean testCondition, T valueTrue, T valueFalseOrNull
返回值T
说明当条件testCondition为true时返回valueTrue否则返回valueFalseOrNull
条件满足输出正确

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

条件满足输出错误

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

7 集合函数

(1size集合中元素的个数

 select size(array('beijing','shenzhen','shanghai')) from location;

(2map创建map集合

语法map (key1, value1, key2, value2, …)
说明根据输入的key和value对构建map类型

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

(3map_keys 返回map中的key

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

(4map_values: 返回map中的value

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

(5array 声明array集合

语法array(val1, val2, …)
说明根据输入的参数构建数组array类

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

(6array_contains: 判断array中是否包含某个元素

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

(7sort_array将array中的元素排序

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

(8struct声明struct中的各属性

语法struct(val1, val2, val3, …)
说明根据输入的参数构建结构体struct类

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

(9named_struct声明struct的属性和值

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

8 高级聚合函数

(1collect_list 收集并形成list集合结果不去重

select 
  sex,
  collect_list(job)
from
  employee
group by 
  sex

(2) collect_set 收集并形成set集合结果去重

select 
  sex,
  collect_set(job)
from
  employee
group by 
  sex

9 常用窗口函数

参考以下文章

开窗函数的使用详解(聚合函数图文详解)

原文链接https://blog.csdn.net/m0_52606060/article/details/129150481

开窗函数的使用详解(窗口范围ROWS与RANGE图文详解)

原文链接https://blog.csdn.net/m0_52606060/article/details/129132985

10 自定义函数

参考以下文章

hive自定义函数及案例

原文链接https://blog.csdn.net/m0_52606060/article/details/134826464