Hive函数_hive decode函数

一、查看hive的函数库

show functions;
1

二、查看某个具体的函数

desc function extended 函数名;
1

三、数学函数

1、log(double base,double a) 简介：底数为base的a的对数，base可以自定义 返回值类型：double

select log(10,100);
--返回2
1
2

2、pow(double base,double p) ：幂运算

select pow(2,3);
--返回8
1
2

3、conv(bigint/string v,int from_base,int to_base)

select conv(13,10,2);
--返回1102
1
2

4、pmod(int/double a,int/double b) 简介：取余运算

select pmod(2,4);
--返回2
1
2

5、 [a]sin(double/decimal a)/[a]cos(double/decimal
a)/[a]tan(double/decimal a) 简介：正弦余弦和正切函数，中括号的[a]加上之后就是反的

6、degrees(double/decimal a)#弧度转化为角度
radians(double/decimal a)#角度转化为弧度

7、 positive(int/double a)#返回数字本身

select positive(-10);
--返回-10
1
2

8、negative(int/double a)：返回相反数

select negative(10);
--返回-10
1
2

9、e():自然对数

select e();
--返回2.718281828459045
1
2

10、pi():圆周率

select pi();
--返回3.141592653589793
1
2

11、greatest(T…vs):横向找出最大值

select greatest(1,4,10,-1);
--返回10
1
2

12、least(T…vs):横向找出最小值

select least(-10,2,-13);
--返回-13
1
2

13、在1.0版本目前没有
bround(a,b)#财务舍入法，对小数5的处理，四舍六入五取偶
factorial(a)#int 20以内的阶乘，只能在20以内
shiftleft(a,b)#位左移
shiftright(a,b)#位右移

四、集合函数

1、 size(Map<K,V>/Array)，求map或array的长度，返回值是int。struct结构体是不可以使用size的

select size(hobbies) from clientinfo01;
1

2、map_keys(Map<K,V>)，获取map里的键，返回值是存放所有键的数组

select map_keys(deliveryadd) from clientinfo1;
1

3、map_values(Map<K,V>)，获取map里的值，返回值是存放所有值的数组

select map_values(deliveryadd) from clientinfo01;
1

4、array_contains(Array,T)，判断参数1的数组里是否包含参数2的元素，返回布尔值

select array_contains(array(1,2,3),2);
1

5、sort_array(Array)，对参数里的数组进行排序，从小到大

select sort_array(array('b','a','m','c'));
1

五、类型转换函数

1、cast(expr as )把expr转换为新的type类型，比如说在使用concat函数时，需要把数字类型的字段转换为字符串类型的

select cast(1 as float);
1

select cast(trim('   1  ') as double);
1

六、日期函数

1、current_timestamp:获取当前时间、

 select current_timestamp;
1

2、unix_timestamp:获取时间戳

 select unix_timestamp();
1

3、from_unixtime:将时间戳转换成format格式 转换为默认时间形式

select from_unixtime(1607914645);  后面可以指定格式 年月日时分秒 
1

select from_unixtime(1607914645,'yyyy-MM-dd HH:mm:ss');
yyyy:年
MM：月
dd:日
HH:小时
mm:分钟
ss秒
1
2
3
4
5
6
7

select from_unixtime(1607914645,'yyyy-MM-dd');
1

select substr(from_unixtime(1607914645),0,10);  利用substr截取字符串获取天数
1

 select from_unixtime(unix_timestamp());  将时间戳转换成日期格式
1

4、unix_timestamp(string datetime)#获取指定日期时间的长整数，不加参数就是当前日期\

select unix_timestamp(current_timestamp());  日期转换为时间戳
1

select unix_timestamp('2020-12-14','yyy-MM-dd');    0时
1

select from_unixtime(unix_timestamp('2020-12-14','yyy-MM-dd'));
1

select unix_timestamp('20201214','yyyyMMdd');
1

5、to_date:返回字符串的日期部分

select to_date((current_timestamp));
1

6、year:只拿取年

select year(current_timestamp());
1

select year('2020-12-14');
--返回2020
1
2

 select year(2020/12/14);  
 --返回 null 格式不对返回null
1
2

7、month：取月份

 select month(current_timestamp());
1

8、day：取日期

select day(current_timestamp());
1

9、current_date:当前日期

select substr(current_timestamp(),0,10);
--截取日期字符串10位
1
2

select current_date();
--返回 2020-12-14 
1
2

10、date_add:日期增加

select date_add('2020-12-14',8);
1

select date_add('2020-12-14',-3);  三天前的时间
1

select date_add(current_timestamp(),2);
1

11、datediff：时间差值

select datediff('2020-12-20','2020-12-13');    7  前面的日期-后面的日期
1

12、date_format(date/timestamp/string date,string format)#参数1为日期格式的日期，根据参数2的需要返回一个字符串值，参数2可以为YYYY-MM-dd hh:mm:ss中任意的一项或者多项，如果是一项，比如说M，则可以获取该日期的月份

select date_format('2020-12-14','yyyy dd hh ss');
1

select date_format('2020-12-14','MM-dd');
1

13、add_months(string date,int months)#加减月数±3/6/12

select add_months(current_date(),-3);
1

14、next_day(string date,string dayOfWeek)#返回接下来第一个 参数2指定的“星期几”格式 的日期

select next_day(current_date(),'Tu');
--从当前日期开始，返回下一个参数2的日期
1
2

15、last_day(string date)#返回该月的最后一天

select last_day(current_date());
1

16、trunc(string date,string format)#返回日期本年、本月的第一天，参数2的格式可以为多种YY/YEAR/MM/MONTH/MON

select trunc(current_date(),'MM');
1

select trunc(current_date(),'YY');
1

17、months_between(string datefrom,string dateto)#月数差

select months_between(current_date(),'2020-1-1');
1

测试题

a.本年第一天
select trunc(current_date(),'YY');
b.本月第一天
select trunc(current_date(),'MM');
c.本季度第一天
select concat_ws(’-’,cast(year(current_date()) as string),cast(ceil(month(current_date())/3)*3-2 as string),‘1’);
d求本周第一天的日期
select date_add(next_day(current_date(),‘SU’),-7);
1
2
3
4
5
6
7
8

七、条件函数

1、if(boolean,T vtrue,T vfalse)#嵌套时用case when

select if(false,0,1);
--返回1
1
2

2、 nvl(T value,T default)#和mysql里的ifnull相同，如果是空值返回第二个参数，如果不是空值null则返回第一个参数的值

select nvl(null,2);
--返回2
1
2

3、coalesce(T…vs)#返回第一个非空null的值

select coalesce(null,4,null,9);
--返回4
1
2

4、case a when b then … when c then … else …end
case when a then … when b then … else … end

5、isnull(v)，isnotnull(v)#判断是否是null,

select isnull(null);
--返回true
1
2

八、字符串函数

1、ascii(v)：返回参数字符串首位的字节码值

select ascii('ac');
--返回97
1
2

2、concat_ws(string sep,array/string…array):通过参数1指定的分隔符号，对参数2“可以是数组或连续的多个字符串”进行拼接，然后返回拼接好的字符串。

select concat_ws('-','aa','bb');
--返回aa-bb
1
2

3、sentences(string sentence)#拆分长字符串（句子）为字符串数组，返回一个嵌套数组array，根据感叹号！分割为多个数组，每个数组根据空格或者逗号分割为多个元素。

select sentences('hello world,my name is james,what is your name');
1

4、ngrams(array arr,int n,int k)#select ngrams(sentences(‘hello boy,how are you,hello word,hello you’),1,2);#第一个参数是多个字符串组成数组的集合（嵌套数组），第二个参数是几个连续的单词，第三个参数是取前几个，按N个单词出现频次，倒序取前K个，返回一个结构数组（array<struct<string,double>>，数组元素为{“ngram”:[“xxx”],“estfrequency”:n}）

select ngrams(sentences('hello kb10,how are you,hello boy,hello you'),1,2);
1

5、context_ngrams(array arr,array,int k)#与 sentences()函数一起使用,分词后,统计分词结果中与数组中指定的单词一起出现(包括顺序)频次最高的 TOP-K 结果，返回值是array<struct<string,double>>。

select context_ngrams(sentences('a b c d e f g h i a b d e'),array("a","b",null),10);
1

6、encode(string source,string charset)/decode(string source,string charset)
      #加密select encode(‘中国’,‘UTF-16BE’);
      #还原select decode(encode(‘中国’,‘UTF-16BE’),‘UTF-16BE’);

select encode('中国','UTF-16BE');
1

select decode(encode('中国','UTF-16BE'),'UTF-16BE');
1

9、format_number(decimal number,int d)#将数值 x 的小数位格式化成 d位,四舍五入，返回值是个字符串

select format_number(19.256,2);
--返回19.26
1
2

10、get_json_object()#字面意思，拿到json对象实例；解析 json 的字符串json_string,返回 path 指定的内容。如果输入的 json字符串无效,那么返回 NULL。
#select get_json_object(’{“name”:“henry”}’,’KaTeX parse error: Expected ‘EOF’, got ‘#’ at position 21: …’); #̲select get_json….info.city’);

select get_json_object('{"name":"henrry"}','$.name');
1

select get_json_object('{"name":"henrry","info":{"city":"nj"}}','$.info.city');
1

11、printf(string format,T…t)#%s %d
%.nf#格式化字符串(类似拼接)，某种情况下可以取代concat#select
printf(’%s,%d,%.2f’,‘henry’,18,234423.34535);

select printf('%s,%d,%.3f','henrry',28,234423.34535);
1

12、like# % #模糊匹配#select * from shop where contact.mobile like ‘18%’;

select * from shop where contact.mobile like '18%';
1

13、rlike#正则的模糊匹配# [] {} ? + * \d \w … #select * from shop where contact.mobile rlike ‘18\d{9}’;

select * from shop where contact.mobile rlike '18\d{9}';
1

14、regexp_replace(string src,string newStr,string oldStr)#select regexp_replace(‘you me young’,‘you’,‘YOU’);#select regexp_replace(‘y1 me y_u youngad’,‘y\w{2,3}’,‘YOU’);找到以y开头，后面有至少2个字符串时，替换y和后面的3个字符串为参数三的值

select regexp_replace('y1 me y_u youngad','y\\w{2,3}','YOU');
1

15、regexp_extract(string src,string regex,int index)#以括号为单位，分组要有界限，参数3是要提取的内容#select regexp_extract(‘namehenryokdalingduck’,‘name(.?)(ok)(.?)duck’,3);

select regexp_extract('namehenrryyokdalingduck','name(.*?)(ok)(.*?)duck',3);
1

16、split(string src,string regex)#正则分割，返回一个数组#select split(regexp_replace(’[“henry”,“pola”,“ariel”]’,’
|
|"’,’’),’,’);#select split(‘henry.lili@foxmail.com’,’.|@’);

select split(regexp_replace('["henrry","pola","ariel"]','\\[|\\]|\"',''),',');
1

17、str_to_map(string src,string regex)#自动变为map键值，返回一组键值对map<string,string>#select         str_to_map(‘name:henry,age:22,gender:male’);#select         str_to_map(‘name#henry|age#22|gender#male’,’|’,’#’);select         str_to_map(‘name#henry|age#22+gender#male’,’||+’,’#’);#正则，|要转义#

select str_to_map('name:henrry,age:22,gender:male');
1

select str_to_map('name#henrry|age#22|gender#male','\\|','#');
1

select str_to_map('name#henrry|age#22+gender#male','\\||\\+','#');
1

18、translate(string src,string chars,string dchars)#参数2的字符匹配多少替换多少#替换#select translate(‘abcabcabcabaac’,‘ab’,’’);a和b分别变成了，按照字母来的#select translate(‘abcabcabcabaac’,‘ab’,’*#’);

select translate('abcabcabcabaac','ab','*');
1

select translate('abcabcabcabaac','ab','*#');
1

19、initcap(string str)#单词的首字母大写#select initcap(‘henry haha’);

select initcap('i am fine,thank you');
1

20、substr(string src,int bigint [,int len])#select substr(‘henry’,2);#select substr(‘henry’,2,1);截取字符串，第一个参数是起始位置，最后一个参数是长度

select substr('2020-12-14 22:22:23.23',5,2);
1

21、locate(string sub,string src,int startPos)#startPos从1开始#select locate(‘en’,‘henry’,2);

select locate('ove','henrrylovelily',2);
1