Hive数学函数，日期函数，条件函数，字符函数用法详解

最新推荐文章于 2022-07-15 19:01:27 发布

dontlikerabbit

最新推荐文章于 2022-07-15 19:01:27 发布

阅读量866

点赞数

文章标签： hive

本文链接：https://blog.csdn.net/dontlikerabbit/article/details/109623942

版权

#数学函数
1、log(double base, double a) #以base为底的a的对数

select log(2,10);

2、pow(double a, double p) #计算a的p次幂

select pow(2,3);

3、conv(bigint/string num, #将bigint/string类型的num从from_base进制转换成to_base进制
int from_base, int to_base)

select conv('2',10,2);//把2从十进制转化为2进制

4、pmod(int/double a, int/double b)#a对b取模

select pmod(5,8);//5除8的余数等于5

5、hex(string a) unhex(string a) #求一个字符串a的正反16进制转化，类似于简单的加密

select hex('2');//字符串2的ASCII码是50，转成16进制是32
select unhex('32');//16进制数32，转成10进制是50,50对应的字符串是2

6、positive(int/double a) #返回a

select positive(55);

7、negative(int/double a) #返回a的相反数

select negative(55);

8、sign(double/decimal a) #如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0

select sign(-20);

9、e()/pi() #数学常数e/pi

select e();
select pi();

10、greatest(T v1, T v2, ...) #求最大值

select greatest(1,5,8,10);

11、least(T v1, T v2, ...) #求最小值

select least(1,5,8,10);

           12、bround(double a,int d)           #double 财务舍入法
           13、factorial(int a)               #求a 20以内的阶乘
           14、shiftleft(int a, int b)           #按位左移
15、shiftright(int a, int b)       #按拉右移

       #集合函数

           1、int        size(Map<K.V>)       #求map的长度
           2、int           size(Array<T>)       #求数组的长度
           3、array<K>   map_keys(Map<K.V>)   #返回map中的所有key
           4、array<V>   map_values(Map<K.V>)#返回map中的所有value
           5、array<T>   sort_array(Array<T>)#按自然顺序对数组进行排序并返回
           6、boolean       array_contains(Array<T>, value)#如该数组Array<T>包含value返回true，否则返回false


       #类型转换函数

           type cast(expr as <type>)#将expr转换成type类型

select cast('2' as int);//字符串2转换成int类型，如果转换失败将返回NULL

#日期函数
1、string from_unixtime(bigint unixtime, string format)#将时间的秒值转换成format格式

select from_unixtime(15487935451,'yyyy-MM-dd');

2、string date_format(date/timestamp/string date,string format)

select date_format('2460-10-16 15:45:34','MM-dd');

3、string current_date()

select current_date();

4、string to_date(string timestamp) #返回时间字符串的日期部分

select to_date('2460-10-16 15:45:34');

5、string current_timestamp() #返回现在的时间

select current_timestamp();

6、bigint unix_timestamp()#获取本地时区下的时间戳

select unix_timestamp();

7、bigint unix_timestamp(string date)#将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳

select unix_timestamp('2022-08-20 19:25:01');

8、bigint unix_timestamp(string date, string format_pattern)#将指定时间字符串格式字符串转换成Unix时间戳

select unix_timestamp('2022-08','yyyy-MM-dd');//格式不对返回null
select unix_timestamp('2022-08-20 19:25:01','yyyy-MM-dd');//格式正确返回Unix的时间秒数

9、string date_add(string date,int days)

select date_add('2022-08-26',25);

10、string add_months(current_date(),int m);

select add_months('2022-08-26',-5);

11、string next_day(current_date(),string dayofweek); #日期之后的下一个Mo/Tu/WE/Th/Fr/Sa/Su

select next('2020-11-11','Sa');

12、string last_day(current_date());#返回日期该月的最后一天

select last_day('2020-11-11');

13、string trunc(current_date(),'YY/MM');#返回日期的本年/本月的第一天

select trunc('2020-11-12','YY');//返回2020年的第一天
select trunc('2020-11-12','MM');//返回2020年11月的第一天

扩展：
#返回日期的本季度第一天

select concat_ws('-',cast(year('2020-11-11') as string),cast(ceil(month(current_date())/3)*3-2 as string),'01');

#返回日期的本周的第一天(以周日算一周的第一天)

select date_add(next_day('2020-11-11','SU'),-7);

14、int datediff(string enddate, string startdate)#计算开始时间startdate到结束时间enddate相差的天数

select datediff('2020-12-25','2019-05-12');

15、double months_between(string enddate, string startdate)#计算开始时间startdate到结束时间enddate相差的月数

select month_between('2020-12-25','2019-05-12');

       #条件函数

       1、T       if(boolean Condition, T vTrue, T vFalse)           #如果Condition 为true就返回vTrue,否则返回vFalse

select if(true,1,0);

2、T nvl(T value, T default_value) #如果value值为NULL就返回default_value,否则返回value

select nvl(null,5);
select nvl(1,5);

3、T coalesce(T v1, T v2, ...) #返回第一非null的值，如果全部都为NULL就返回NULL

select coalesce(null,2,3,null);

4、T case a when b then c [when d then e]* [else f] end #如果a=b就返回c,a=d就返回e，否则返回f

select case 1 when 1 then 'a' when 2 then 'b' else 'c' end;

5、T case when a then b [when c then d]* [else e] end #如果a=ture就返回b,c= ture就返回d，否则返回e

select case when true then 'a' when false then 'b' else 'c' end;

6、boolean isnull( a ) #如果a为null就返回true，否则返回false

select isnull(a);

7、boolean isnotnull ( a ) #如果a为非null就返回true，否则返回false

select isnotnull('a');

       #字符函数

       1、int       ascii(string str)#返回str中首个ASCII字符串的整数值

select ascii('223');//字符串223中首个字符是2，其ASCII是50

2、string concat(string|binary A, string|binary B...)#对二进制字节码或字符串按次序进行拼接

select concat('abc','ABC');
select concat(10,1011);

3、string concat_ws(string SEP, string A, string B...)#与concat()类似，但使用指定的分隔符进行分隔

select concat_ws('-','abc','ABC');

4、string concat_ws(string SEP, array<string>)#拼接Array中的元素并用指定分隔符进行分隔

select concat_ws('-',array('abc','A'));

5、array<array<string>> sentences(string sentence);#字符串sentence将被转换成单词数组，不同符号结果不一样

select sentences(‘hello world,how are you’);
select sentences(‘hello world?how are you’);
select sentences("hello world!how are you");

       6、array<struct<string,double>>   ngrams(array<array<string>>, int N, int K)  #按N个连续的单词出现的频次，倒序取TOP K (若N个单词的频次一样，则随机取K个)

select ngrams(sentences('hello world?how are you'),1,2);

7、array<struct<string,double>> context_ngrams(array<array<string>>, array<string>, int K) #与array中指定单词之后配合出现的频次，倒序取TOP K，array中指定单词后面要加null

 select context_ngrams(sentences('hello china?how are you,hello world,are you ok?'),array('you',null),2);

       8、string   decode(binary bin, string charset)
           #使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL

select encode('我爱中华001abc你们','UTF-16BE');

9、binary encode(string src, string charset)

select decode(encode('我爱中华001abc你们','UTF-16BE'),'UTF-16BE');

10、string format_number(number x, int d) #将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数

 select format_number(478493145631.2546,5);

       11、string   get_json_object(string json_string, string path)
           #从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字字母下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制

select get_json_object('{"name":"henry"}','$.name');
select get_json_object('{"name":"henry","info":{"city":"nj"}}','$.info.city');
select get_json_object('{"name":"henry","info":["city","nj"]}','$.info[0]');

12、boolean in_file(string line, string path)#path中指向的文件是否包含line

       13、string   parse_url(string urlString, string partToExtract [, string keyToExtract])

       #返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，
       这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,
       例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') ='facebook.com'，
       如果参数partToExtract值为QUERY则必须指定第三个参数key
       如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') =‘v1’

       14、string   printf(String format, T...t)#按照printf风格格式输出字符串
           select printf('%s,%d,%.2f','henry',18,23456.789);---henry,18,23456.79


       15、Boolean   like   # %

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

16、Boolean rlike # [] {} ? + \d \w ...

select * from shop where contact.mobile rlike '18\\d';//查表中手机号是18开头的

select * from shop where contact.mobile rlike '18\\d{9}';//查询手机号18开头的且是九位数的

17、string regexp_replace(string src, string regex, string replacement)

#按照Java正则表达式PATTERN将字符串src中符合条件的部分成replacement所指定的字符串，如里replacement这空的话，抽符合正则的部分将被去掉

select regexp_replace('you me he you','you','YOU');

select regexp_replace('you me he y_ou young yuuuuuug','y\\w{2,3}

 select regexp_replace('["henry","pola","ariel"]','\\[|\\]|"','');

18、string regexp_extract(string src, string regex, int index)

抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串

select regexp_extract('namehenryokdarlingduck','name(.*?)ok(.*?)duck',2);

19、array<string> split(string str, string regex)

按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回

select split('henry.chen@hotmail.com','\\.|@');

20、map<string,string> str_to_map(string str,string spel1,string spel2)

将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"："

str_to_map('name:henry,age:22,gender:male');

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

       array/struct/map转类型

select translate('abcabcabaac','ab','*#');

22、initcap 首字母大写

23、string substr(string|binary A, int start) substring(string|binary A, int start) #对于字符串A,从start位置开始截取字符串并返回

select substr('henry',2);

24、string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)

对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回

select substr('henry',2,3);

25、int locate(string substr, string str[, int pos])#查找字符串str中的pos位置后字符串substr第一次出现的位置，pos从1开始

select locate('en','henry',1);

26、int instr(string str, string substr) #查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的

select instr('henry','en');

md5单向加密

select md5('abc');

String base64（binary bin）将二进制值转换64位的字符串
binary unbase64(string str)将64位的字符串转换二进制值

select base64(cast('henry' as binary));

 select unbase64('aGVucnk=');

hex/unhex(双向的可以解密)

   aes_encrype/aes_decrype(双向的可以解密)

       sha2(string/binary,int(0/1));单向加密int选0或1

select sha2('henry',1);

       int   levenshtein(string A, string B)
       计算两个字符串之间的差异大小

select levenshtein('a','b');

string soundex(string A)
将普通字符串转换成soundex字符串

select soundex('bc');

string initcap(string A)
将字符串A转换第一个字母大写其余字母的字符串

       #聚合函数

       BIGINT   count(*), #统计总行数，包括含有NULL值的行
       int       count(expr), #统计提供非NULL的expr表达式值的行数
       BIGINT   count(DISTINCT expr[, expr...])#统计提供非NULL且去重后的expr表达式值的行数

       double   sum(col), sum(DISTINCT col)
               sum(col),表示求指定列的和，sum(DISTINCT col)#表示求去重后的列的和

double avg(col), #表示求指定列的平均值
avg(DISTINCT col)#表示求去重后的列的平均值

double min(col) #求指定列的最小值

double max(col) #求指定列的最大值

double var_pop(col) #求指定列数值的方差:离散程度

double var_samp(col) #求指定列数值的样本方差：变异程度

double stddev_pop(col) #求指定列数值的标准偏差

double stddev_samp(col) #求指定列数值的样本标准偏差

double covar_pop(col1, col2) #求指定列数值的协方差

double covar_samp(col1, col2) #求指定列数值的样本协方差

double corr(col1, col2) #返回两列数值的相关系数

       double percentile(BIGINT col, p)   #返回col的p%分位数
               collect_list(col)   #行转列
               collect_set(col)   #

       #表生成函数

       1、Array Type   explode(array<TYPE> a)#对于a中的每个元素，将生成一行且包含该元素

2、N rows explode(ARRAY<T>) 每行对应数组中的一个元素
select name,city from employee_id lateral view explode(cities) ct as city;

3、N rows explode(MAP<K,v>) 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
select name,pos,score from employee_id lateral view explode(scores) st as pos,score;

       4、N rows   posexplode(ARRAY)#与explode类似，不同的是还返回各元素在数组中的位置
       select posexplode(array('aa','bb','cc'));

       5、N rows   stack(INT n, v_1, v_2, ..., v_k)#把M列转换成N行，每行有M/N个字段，其中n必须是个常数
       select stack(2,'aa','bb','cc','dd');

6、tuple json_tuple(jsonStr, k1, k2, ...)#从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值

7、tuple parse_url_tuple(url, p1, p2, ...)#返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,....是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>

       select printf('%s %s',fn,ln) name,age,hobby from jsontuple
       lateral view json_tuple(line,'name','hobbies','age') jt as name,hobbies,age
       lateral view json_tuple(name,'first','last') jt1 as fn,ln
       lateral view explode(split(regexp_replace(hobbies,'\\[|\\]|"',''),',')) hs as hobby;

       8、inline(ARRAY<STRUCT[,STRUCT]>)#将结构体数组提取出来并插入到表中
       select inline(array(struct('aa','bb','cc'),struct('ee','dd','ff')));

dontlikerabbit

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Hive数学函数，日期函数，条件函数，字符函数用法详解

#数学函数 1、log(double base, double a) #以base为底的a的对数 2、pow(double a, double p) #计算a的p次幂 3、conv(bigint/string num, #将bigint/string类型的num从from_base进制转换成to_base进制 int from_base, int to...
复制链接

扫一扫