Hive（五）常用函数

Hive常用函数

字符串函数

返回值	函数	描述
string	concat(string/binary A, string/binary B…)	对二进制字节码或字符串按次序进行拼接
int	instr(string str, string substr)	查找字符串str中子字符串substr出现的位置
int	length(string A)	返回字符串的长度
int	locate(string substr, string str[, int pos])	查找字符串str中的pos位置后字符串substr第一次出现的位置
string	lower(string A) /upper(string A)	将字符串A的所有字母转换成小写/大写字母
string	regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT)	按正则表达式PATTERN将字符串中符合条件的部分替换成REPLACEMENT所指定的字符串
array	split(string str, string pat)	按照正则表达式pat来分割字符串str
string	substr(string/binary A, int start, int len)substring(string/binary A, int start, int len)	对字符串A,从start位置开始截取长度为len的字符串并返回
string	trim(string A)	将字符串A前后出现的空格去掉
map	str_to_map(text[, delimiter1, delimiter2])	将字符串str按照指定分隔符转换成Map
binary	encode(string src, string charset)	用指定字符集charset将字符串编码成二进制值
string	concat_ws(separator,string A, string B…)	用指定拼接符separator对字符串按次序进行拼接

类型转换函数

返回值	函数	描述
“type”	cast(expr as )	将expr转换成type类型 如：cast(“1” as BIGINT) 将字符串1转换成了BIGINT类型
binary	binary(string/binary)	将输入的值转换成二进制

数学函数

返回值	函数	描述
DOUBLE	round(DOUBLE a)	返回对a四舍五入的BIGINT值
binary	round(DOUBLE a, INT d)	返回对a四舍五入并保留d位小数位的值
BIGINT	floor(DOUBLE a)	向下取整，如：6.10->6 -3.4->-4
DOUBLE	rand(INT seed)	返回一个DOUBLE型随机数，seed是随机因子
DOUBLE	power(DOUBLE a, DOUBLE p)	计算a的p次幂
DOUBLE	abs(DOUBLE a)	计算a的绝对值

日期函数

返回值	函数	描述
string	from_unixtime(bigint unixtime[, string format])	将时间戳转换成format格式
int	unix_timestamp()	获取本地时区下的时间戳
bigint	unix_timestamp(string date)	将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳
string	to_date(string timestamp)	返回时间字符串的日期部分
int	year(string date) month/day/hour/minute/second/weekofyear	返回时间字符串的年份部分 返回月/天/时/分/秒/第几周
int	datediff(string enddate, string startdate)	计算开始时间到结束时间相差的天数
string	date_add(string startdate, int days)	从开始时间startdate加上days
string	date_sub(string startdate, int days)	从开始时间startdate减去days
date	current_date	返回当前时间的日期
timestamp	current_timestamp	返回当前时间戳
string	date_format(date/timestamp/string ts, string fmt)	按指定格式返回时间date 如：date_format(“2016-06-22”,“MM-dd”)=06-22

集合函数

返回值	函数	描述
int	size(Map<K.V>)	返回map中键值对个数
int	size(Array)	返回数组的长度
array	map_keys(Map<K.V>)	返回map中的所有key
array	map_values(Map<K.V>)	返回map中的所有value
boolean	array_contains(Array, value)	如该数组Array包含value返回true，否则返回false
array	sort_array(Array)	对数组进行排序

条件函数

返回值	函数	描述
T	if(boolean testCondition, T valueTrue, T valueFalseOrNull)	如果testCondition为true就返回valueTrue,否则返回valueFalseOrNull
T	nvl(T value, T default_value)	value为NULL返回default_value,否则返回value
T	COALESCE(T v1, T v2, …)	返回第一非null的值，如果全部都为NULL就返回NULL
T	CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END	如果a=b就返回c,a=d就返回e，否则返回f
T	CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END]* [ELSE f] END	如果a=ture就返回b,c= ture就返回d,否则返回e
boolean	isnull(a)	如果a为null就返回true，否则返回false
boolean	isnotnull (a)	如果a为非null就返回true，否则返回false

聚合函数

1、基础聚合函数：sum()、count()、max()、min()、avg()
（1）没有group by关键字就是对整个表聚合；有group by 就是对每个组聚合。

--1.分组聚合：统计男同学、女同学人数
select count(*) from student group by sex;
--2.表聚合：统计全班人数
select count(*) from student;

• count(*)等价于count(1)：统计所有行，包括含有null值的行
• count(col)：只会对col中非null进行统计。其他的基础聚合函数也一样的，对字段聚合处理，若存在该字段值为null，则忽略该行。
• count(只能传入一个参数)，如果要传入多个参数，可以count(distinct col1, col2)，只要去重对结果没有影响就可以这样。

（2）聚合函数内：

• 可以搭配if()、case when ... then else end、isnull()这种单列函数使用，但是聚合函数内不可搭配聚合函数
• 先对每组数据内的所有行都执行单列函数，再对每组数据进行聚合
• 可以搭配distinct关键字去重–>count(distinct col)
• 聚合函数内使用struct集合数据类型，是对其第一个列操作。因此可以实现查找女生年龄最大的人的姓名
  select max(struct(age,name) from student);

2、高级聚合函数
（1）collect_list()：收集并形成list集合，结果不去重
（2）collect_set()：收集并形成set集合，结果去重

select 
  sex,
  collect_list(job) as job
from employee
group by sex
-- 结果：
sex			job
男	["销售"]
女	["行政","行政"]