hive常用的内置函数实例解析--是真的细！

hive常用的内置函数实例解析

• 一、 日期函数
• 二、字符串函数

一、 日期函数

1.1 日期>>>>时间戳： unix_timestamp()

• 返回值：bigint

①获取当前时间戳： unix_timestamp()

select unix_timestamp();      
--1565858389

②将日期格式字符串转为时间戳： unix_timestamp(string timestame)

输入的时间戳格式必须为 ‘yyyy-MM-dd HH:mm:ss’ ,如不符合则返回 null

select unix_timestamp('2019-08-15 16:40:00');   
--1565858400

select unix_timestamp('2019-08-15');  
--null

③将指定时间字符串格式字符串转化成unix时间戳： unix_timestamp(string date,string pattern)
如日期不符合对应格式则返回 null

select unix_timestamp('2019-08-15','yyyy-MM-dd');   
--1565798400

select unix_timestamp('2019-08-15','yyyy-MM-dd HH:mm:ss');
--null

select unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss');
--1565858400

1.2 时间戳>>>>日期： from_unixtime(bigint unixtime,string format)

• 返回值：string

①将时间戳秒数转化为UTC时间，并用字符串表示，可通过format规定的时间格式，指定输出的时间

select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss');
--2019-08-15 16:39:49

select from_unixtime(1565858389,'yyyy-MM-dd');
-- 2019-08-15

②如果 unixtime 是13位的，需要先转成10位

select from_unixtime(cast(1553184000488/1000 as int),'yyyy-MM-dd HH:mm:ss'); 
-- 2019-03-22 00:00:00

--或者使用字符串函数 substr 从第一位截取10个数字，截取时间戳前10位 
select from_unixtime(cast(substr(1553184000488,1,10) as int),'yyyy-MM-dd HH:mm:ss');
-- 2019-03-22 00:00:00

1.3 获取当前日期：current_date()

• 返回值：string

select current_date();
-- 2020-09-28

1.4 获取当前日期 + 分钟：current_timestamp()

• 返回值：string

select current_timestamp();  
-- 2020-09-19 20:46:46.232

1.5 日期格式字符串转化为日期：date(string date)

• 返回值：string

select date('2020-07-25');  
-- 2020-07-25

select date('2020-07-25 00:00:00');  
-- 2020-07-25

1.6 获取日期格式字符串中具体的 年、月、日、时、分、秒：year()、month()、day()、hour()、minute()、second()

• 返回值：int

select year('2020-09-22 15:16:02');
-- 2020

select month('2020-09-22 15:16:02');
-- 9

select day('2020-09-22 15:16:02');
-- 22

select hour('2020-09-22 15:16:02');
-- 15

select minute('2020-09-22 15:16:02');
-- 16

select second('2020-09-22 15:16:02');
-- 2

-- 注：字符串不带分钟，查询 时、分、秒 返回 null
select hour('2020-09-22');
-- null

1.7 日期比较函数，返回两个日期的差值 ：datediff(string date1,string date2)

• 返回值：int

函数输入参数的日期格式为：yyyy-MM-dd HH:mm:ss 、 yyyy-MM-dd

select datediff('2020-01-20','2020-01-10');
-- 10 

select datediff('2020-01-10','2020-01-20');
-- 返回-10，所以日期相减，应该是新的日期在前 

1.8 日期减少函数，返回减少 x 天后的日期 ：date_sub(string date,int x)

• 返回值：string

select date_sub('2020-09-04',3);
-- 2020-09-01
select date_sub('2020-09-04 09:12:41',2); 
-- 2020-09-02

---

1.9 日期增加函数,返回增加 x 天后的日期 ：date_add(string date, int x)

• 返回值：string

select date_add('2020-09-04',3);
-- 2020-09-07
select date_add('2020-09-04 09:12:41',2); 
-- 2020-09-06

---

1.10 日期转周函数，获取一年当中的第几周 ：weekofyear(string date)

• 返回值：int

select weekofyear('2020-01-02 13:49:02');
-- 1
select weekofyear('2020-01-02'); 
-- 1

1.11 日期转天函数,获取当天日期是一周当中的第几天：dayofweek(string date)

• 返回值：int

select dayofweek('2020-09-22'); 
-- 返回3 (当前是星期二，但是一周是从星期天开始，星期天是1，所以星期二就是返回3)
select dayofweek('2020-09-22 13:49:02'); 
-- 3

二、字符串函数

2.1 获取字符串长度：length(string A)

• 返回值： int

select length("abcd");
-- 4

2.2 字符串反转： reverse(string A)

• 返回值： string

select reverse("abcd");
-- dcba

2.3 字符串连接：concat(string A, string B…)

• 返回值: string

select concat('abc','def','g');
-- abcdefg

2.4 带分隔符的字符串连接：concat_ws((string SEP, string A, string B…)

• 返回值: string
• 返回输入字符串连接后的结果，SEP可以指定各个字符串间的分隔符

select concat_ws(',','abc','def','gh');
-- abc,def,gh

2.5 字符串截取：substr,substring

• 二者用法一致，返回值为 string

① 从指定位置 start 开始截取，截取到末尾，起始下标为 1： substr(string A, int start),substring(string A, int start)

select substr('abcdef',3);
-- cdef

select substring('abcdef',3);
-- cdef

-- 输入负值 -x，末尾往头部从 "倒数第x位" 反转截取，截取到末尾
select substr('abcdef',-3);
-- def

select substring('abcdef',-3);
-- def

②从指定位置 start 开始截取，截取长度为len的字符串，起始下标为 1：substr(string A, int start, int len),substring(string A, int start, int len)

select substr('abcdef',3,2);
-- cd

select substring('abcdef',3,2);
-- cd

-- 重倒数第3位开始截取，保留两个字符
select substr('abcdef',-3,2);
-- de

select substring('abcdef',-3,2);
-- de

2.6 字符串转大写： upper(string A) 、ucase(string A)

• 返回值: string

select upper('abcde');
-- ABCDE

select upper('aBcDe');
-- ABCDE

select ucase('abcde');
-- ABCDE

select ucase('aBcDe');
-- ABCDE

2.7 字符串转小写：lower(string A) 、lcase(string A)

• 返回值: string

select lower('ABCDE');
-- abcde

select lower('ABCde');
-- abcde

select lcase('ABCDE');
-- abcde

select lcase('ABCde');
-- abcde

2.8 去除字符串两边的空格：trim(string A)

• 返回值: string

select trim(' abc ');
-- abc

select trim(' a b c ');
-- 不会取出字符串中间的空格a b c

2.9 去除字符串左边的空格：ltrim(string A)

• 返回值: string

select ltrim(' abc ');
-- abc(空格) 

select length(ltrim(' abc '));
-- 4

2.10 去除字符串右边的空格：rtrim(string A)

• 返回值: string

select rtrim(' abc ');
-- (空格)abc

select length(rtrim(' abc '));
-- 4

2.11 使用正则表达式替换：regexp_replace(string A, string B, string C)

• 将 字符串A 中的符合 java正则表达式B 的部分替换为 字符串C
• 返回值: string

select regexp_replace('oneDay-2019-03-15','\\d{4}-\\d{2}-\\d{2}','2020-09-22');
-- oneDay-2020-09-22

2.12 使用正则表达式匹配：regexp_extract(string subject, string pattern, int index)

• 将字符串subject按照pattern正则表达式的规则拆分，返回index指定的字符
  subject 是需要匹配的字符串
  pattern 代表正则表达式，表达式中需包含一个或两个括号 字符串选择器 ()
  index 包含0、1、2 三个数字，0代表匹配整个 pattern 中的内容，1代表匹配 第一个() 中的内容，2代表匹配 第二 () 中的内容
• 无论 subject 中有有几个符合匹配条件的元素，默认取第一个
• 返回值: string

例①：

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([0-9]+)([a-z]+)',0);
-- x=111abc

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([0-9]+)([a-z]+)',1);
-- 111

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([0-9]+)([a-z]+)',2);
-- abc

例②：

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([a-z]+)([0-9]+)',0);
-- x=ab123

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([a-z]+)([0-9]+)',1);
-- ab

select regexp_extract('x=ab123&x=111abc&x=123&x=3aaa','x=([a-z]+)([0-9]+)',2);
-- 123

例③：

select regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',0);   
-- id=522228774076

select regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',1);    
-- 522228774076

例④：

select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',0);   
-- i41915173660

select regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',1);
-- 41915173660

例⑤：

select regexp_extract('123-basketball-#@','bas(.*?)(ball)',0);
-- basketball

select regexp_extract('123-basketball-#@','bas(.*?)(ball)',1);
-- ket

select regexp_extract('123-basketball-#@','bas(.*?)(ball)',2);
-- ball

2.13 URL 解析函数：parse_url(string urlString, string partToExtract [, string keyToExtract])

• 返回URL中指定的部分，partToExtract 的选项包含：[ host， path，query，ref，protocol，file， authority，userinfo ]

select parse_url('http://facebook.com/path/p1.php?query=1', 'PROTOCOL') from dual;   
--http

select parse_url('http://facebook.com/path/p1.php?query=1', 'HOST') from dual;
--facebook.com​

select parse_url('http://facebook.com/path/p1.php?query=1', 'REF') from dual;
--null

select parse_url('http://facebook.com/path/p1.php?query=1', 'PATH') from dual;
--/path/p1.php​

select parse_url('http://facebook.com/path/p1.php?query=1', 'QUERY') from dual;
--空null

​select parse_url('http://facebook.com/path/p1.php?query=1', 'FILE') from dual;​
--/path/p1.php?query=1​

​select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY') from dual;​
--facebook.com​

​select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO') from dual;
​--null