hive函数大全

在hive内部有许多函数，如下：

内置运算符
关系运算符
算术运算符
逻辑运算符
复杂类型函数
内置函数内置聚合函数
数学函数
收集函数
类型转换函数
日期函数
条件函数
字符函数
内置聚合函数
内置表生成函数
1.1关系运算符

1. 等值比较: =
2. 等值比较:<=>
3. 不等值比较: <>和!=
4. 小于比较: <
5. 小于等于比较: <=
6. 大于比较: >
7. 大于等于比较: >=
8. 空值判断: IS NULL
9. 非空判断: IS NOT NULL
10. LIKE 比较: LIKE
11. JAVA 的 LIKE 操作: RLIKE
12. REGEXP 操作: REGEXP

1.2算术运算符

1. 加法操作: +
2. 减法操作: –
3. 乘法操作: *
4. 除法操作: /
5. 取余操作: %
6. 位与操作: &
7. 位或操作: |
8. 位异或操作: ^
   9．位取反操作: ~

1.3逻辑运算符

1. 逻辑与: AND 、&&
2. 逻辑或: OR 、|
3. 逻辑非: NOT、!

1.4复杂类型函数

1. 获取array中的元素
2. 获取map中的元素
3. 获取struct中的元素

2.1数学函数

1. 取整函数: round(double a) 　　　　　　　　四舍五入
2. 指定精度取整函数: round(double a,int d)　　小数部分d位之后数字四舍五入
3. 向下取整函数: floor(double a) 　　　　　　对给定数据进行向下舍入最接近的整数
4. 向上取整函数: ceil(double a) 　　　　　　 将参数向上舍入为最接近的整数
5. 向上取整函数: ceiling(double a)
6. 取随机数函数: rand
7. 自然指数函数: exp(double a)　　　　　　　　返回e的n次方
8. 以 10 为底对数函数: log10
9. 以 2 为底对数函数: log2
10. 对数函数: log(double base,double a)　　　 返回给定底数及指数返回自然对数
11. 幂运算函数: pow(double a,double p) 　　　 返回某数的乘幂
12. 幂运算函数: power(double a,double p)
13. 开平方函数: sqrt 　　　　　　　　　　　　返回数值的平方根
14. 二进制函数: bin
15. 十六进制函数: hex
16. 反转十六进制函数: unhex
17. 进制转换函数: conv
18. 绝对值函数: abs
19. 正取余函数: pmod(int a,int b)
20. 正弦函数: sin
21. 反正弦函数: asin
22. 余弦函数: cos
23. 反余弦函数: acos
24. positive 函数: positive(int a)　　　　返回a的值
25. negative 函数: negative(int a)　　　　返回a的相反数

2.2收集函数

array/map类型大小: size　　　　　　　　　返回array/map类型的元素数量

2.3类型转换函数

基础类型之间强制转换：cast(field/expr as )

2.4日期函数

1. UNIX 时间戳转日期函数: from_unixtime
2. 获取当前 UNIX 时间戳函数: unix_timestamp
3. 日期转 UNIX 时间戳函数: unix_timestamp
4. 指定格式日期转 UNIX 时间戳函数: unix_timestamp
5. 日期时间转日期函数: to_date
6. 日期转年函数: year
7. 日期转月函数: month
8. 日期转天函数: day
9. 日期转小时函数: hour
10. 日期转分钟函数: minute
11. 日期转秒函数: second
12. 日期转周函数: weekofyear
13. 日期比较函数: datediff
14. 日期增加函数: date_add
15. 日期减少函数: date_sub
16. 日期查看函数: date_format
    17 日期函数:last_day 返回当前月最后一天日期

2.5条件函数

1. if 函数: if(boolean condition,true_value,false_value)
2. 非空查找函数: coalesce(v1,v2,v3…)　　　　　　返回一组数据中第一个不为null的值
3. 条件判断函数：case when a then b else c end　　当值为a时返回b,否则返回c

2.6字符函数

1. 字符 ascii 码函数：ascii
2. base64 字符串
3. 字符串连接函数：concat(String a,String b) 　　　　　　　　　　连接多个字符串，合并为一个字符串
4. 带分隔符字符串连接函数：concat_ws(joinstr,String a,String b) 连接多个字符串，字符串之间以指定的分隔符分开
5. 小数位格式化成字符串函数：format_number
6. 字符串截取函数:substr(String a,int start),substring(String a,int start)从文本字符串指定的起始位置后的字符
7. 字符串查找函数：instr
8. 字符串长度函数：length
9. 字符串查找函数：locate
10. 字符串格式化函数：printf
11. 字符串转换成 map 函数：str_to_map
12. base64 解码函数：unbase64(string str)
13. 字符串转大写函数：upper,ucase
14. 字符串转小写函数：lower,lcase
15. 去空格函数：trim
16. 左边去空格函数：ltrim
17. 右边去空格函数：rtrim
18. 正则表达式替换函数：regexp_replace(string a,string b,string c) 字符串a中的b字符被c字符替代
19. 正则表达式解析函数：regexp_extract
20. URL 解析函数：parse_url
21. json 解析函数：get_json_object(string json_string,string path) 拆分取值
22. 空格字符串函数：space 　　　　　　　　　　　　　　　　　　　　 返回指定数量的空格
23. 重复字符串函数：repeat(string a,int b)　　　　　　　　　　　　　重复b次a字符串
24. 左补足函数：lpad
25. 右补足函数：rpad
26. 分割字符串函数: split
27. 集合查找函数: find_in_set
28. 分词函数：sentences(string a)　　　　　　　　　　　　　　　　　　将字符串中内容按语句分组，每个单词间以逗号分隔，最后返回数组
29. 分词后统计一起出现频次最高的 TOP-K
30. 分词后统计与指定单词一起出现频次最高的 TOP-K
31. 倒序字符串：reverse
32. ngrams
33. context_ngrams
34. first_value/last_value 　　　　　　　　返回当前列第一个(最后一个)值
35. lag/lead　　　　　　　　　　　　　　　　　　　　　　　返回当前值的前后值

3.内置聚合函数

1. 个数统计函数: count
2. 总和统计函数: sum
3. 平均值统计函数: avg
4. 最小值统计函数: min
5. 最大值统计函数: max
6. 返回指定列的方差: var_pop
7. 返回指定列的样本方差: var_samp
8. 返回指定列的偏差: stddev_pop
9. 返回指定列的样本偏差: stddev_samp
10. 两列数值协方差：covar_pop
11. 两列数值样本协方差：covar_samp
12. 返回两列数值的相关系数：corr(col1,col2)
    13．中位数函数: percentile
13. 近似中位数函数: percentile_approx
14. 直方图: histogram_numeric
15. 集合去重数：collect_set
16. 集合不去重函数：collect_list

4.内置表生成函数

1. array/map拆分多行：explode
   2.json_tuple

测试：

1.1关系运算符

关系运算符：= <=> > >= < <=

– select 5<=>5 true
– select 6>=5 true
– select 8<=7 false
like/rlike比较

– select ‘football’ like ‘foot%’ true
– select ‘[e-mail]’ rlike ‘\@[0-9a-z]{2,}.(com|cn|org|edu)’ true
regexp

– select ‘football’ regexp ‘\w{9,}[a-z]’ false
1.2算术运算符

复制代码
– select 5+5 10
– select 5-5 0
– select 5*5 25
– select 5/5 1
– select 5%4 1
– select 5&9 1
– select 5|9 13
– select 5^9 12
复制代码
1.4复杂类型函数

– select array(1,4,5,6,8) [1,4,5,6,8]
– select map(‘name’,‘张三’,‘age’,18) {“name”:“张三”,“age”:“18”}
– select struct(array(1,3,5),map(‘name’,‘张三’,‘age’,18)) {“col1”:[1,3,5],“col2”:{“name”:“张三”,“age”:“18”}}
2.1数学函数

复制代码
– select round(5.3423) 5
– select round(5.345,2) 5.35
– select floor(5.9) 5
– select ceil(5.1) 6
– select rand() 0.7311469360199058
– select exp(2) 7.38905609893065
– select log(5,25) 2
– select pow(2,3) 8
– select sqrt(25) 5
– select bin(8) 1000
– select hex(16) 10
– select unhex(100)
– select abs(-10) 10
– select pmod(5,2) 1
– select sin(3.1415926/2) 0.9999999999999997
– select asin(0.9999999999999997) 1.5707963009853283
– select positive(10) 10
– select negative(10) -10
复制代码
2.2收集函数

– select size(array(1,3,4,5,6,7,8)) 7
2.4日期函数

复制代码
– select from_unixtime(0,‘yyyy-MM-dd HH:mm:ss’) 1969-12-31 19:00:00
– select unix_timestamp() 1598707426
– select to_date(‘2020-1-1’) 2020-01-01
– select year(current_date()) 2020
– select month(current_date()) 8
– select day(current_date()) 29
– select hour(current_timestamp()) 9
– select minute(current_timestamp()) 26
– select second(current_timestamp()) 15
– select weekofyear(current_date()) 35
– select datediff(current_date(),‘2020-08-01’) 28
– select date_add(current_date(),1) 2020-08-30
– select date_sub(current_date(),1) 2020-08-28
– select date_format(current_date(),‘y’) 2020
– select last_day(‘2020-01-02’) 2020-01-31
复制代码
2.5条件函数

– select if(1=3,3,2) 2
– select coalesce(null,3,null,4) 3
– select case when 5>9 then 4 else 0 end 0
2.6字符函数

复制代码
– select ascii(‘,’) 44
– select concat(‘张三’,‘吃饭’) 张三吃饭
– select concat_ws(‘-’,‘张三’,‘干嘛去了’) 张三-干嘛去了
– select format_number(2.4234432532,3) 2.423
– select substr(87654321,3,2) 65
– select instr(‘张三在哪’,‘在’) 3
– select length(‘324322’) 6
– select locate(‘吗’,‘吃了吗’) 3
– select printf(‘你好’) 你好
– select str_to_map(‘name:李四,age:18’,‘,’,‘:’) {“name”:“李四”,“age”:“18”}
– select upper(‘abc’) ABC
– select lower(‘ABC’) abc
– select trim(’ 你好 ‘) 你好
– select regexp_replace(‘早上好’,‘早’,‘晚’) 晚上好
– SELECT regexp_extract(‘100-200’, ‘(\d+)-(\d+)’, 1) FROM src LIMIT 1 100
– select parse_url(‘https://home.cnblogs.com/u/afeiiii/’,‘PROTOCOL’) https
– select id,get_json_object(line,’$.nam{e}^{\prime })name,ge{t}_{j}so{n}_{o}bject(line{,}^{\prime }$.age’) age,get_json_object(line,‘$.gender’) gender from jsontest
– select repeat(‘a’,4) aaaa
– select split(‘howAareByou’,‘[AB]’) [“how”,“are”,“you”]
– select find_in_set(‘aa’,‘aa,bb,aa’) 1
– select sentences(‘你,吃了吗’) [[“你”,“吃了吗”]]
复制代码
4.内置表生成函数

–explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns 　　explode适合array/map的拆分
–select id,t.name,t.age,t.gender from jsontest lateral view json_tuple(line,‘name’,‘age’,‘gender’)t as name,age,gender json_tuple适合String类型拆分