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 <type>)
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
14. 近似中位数函数: percentile_approx
15. 直方图: histogram_numeric
16. 集合去重数：collect_set
17. 集合不去重函数：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 '2697566722@qq.com' 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,'$.name') name,get_json_object(line,'$.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类型拆分
