hive常用函数

众所周知，Hive中有许多函数…

什么聚合函数、开窗函数，数学函数，时间函数等等…

所以今天我就来总结一下Hive中常用的函数，以便日后方便查询！

---

函数基本操作

【1】查看系统自带的函数
show functions;
【2】显示自带的函数的用法
desc function upper;
【3】详细显示自带的函数的用法
desc function extended upper;

聚合函数

【1】count（返回行数）： count(*)\count(1)\count(常量)
【2】sum（组内某列求和） sum(num) 忽略null值
【3】min（组内某列最小值）： min(num/str) 忽略null值
【4】max（组内某列最大值）： max(num/str) 忽略null值
【5】avg（组内某列平均值） avg(num) 忽略null值

数学函数

【1】round（四舍五入）： round（num）/round(num , 取小数点多少位)
【2】ceil（向上取整）: ** ceil(num)
【3】floor（向下取整）: floor(num)
【4】weekofyear（返回输入日期在该年中是第几个星期）： weekofyear(str/date)
【5】datediff（两个日期相减，返回相差天数）： datediff(date1/st1,date2/str2)
【6】date_add（在一个日期基础上增加天数）： date_add(date/str,num)
【7】date_sub（在一个日期基础上减去天数）： date_sub(date/str,num)
【8】rand （取随机数函数）： rand()/rand(int seed)
【9】exp（返回自然对数e的a次方）： exp(double a)
【10】 log10 （返回以10为底的a的对数）： log10(double a)
【11】 log2 （返回以2为底的a的对数）： log2(double a)
【12】 pow（返回a的p次幂）： pow(double a, double p)
【13】 sqrt（返回a的平方根）： sqrt(double a)
【14】 bin（返回a的二进制代码表示）： bin(BIGINT a)
【15】 abs（返回数值a的绝对值）： abs(double a)/abs(int a)

时间函数

【1】to_date（取出日期部分）： to_date(str/date) 例 —》 select to_date(“2020-01-02 10:22:33”);
【2】year（从一个日期中取出相应的年）： year(str/date)
【3】month（从一个日期中取出相应的月）： month(str/date)
【4】day（从一个日期中取出相应的日）： day(str/date)
【5】hour （从一个日期中取出相应的时）： hour (str/date)
【6】minute（从一个日期中取出相应的分）： minute(str/date)
【7】second（从一个日期中取出相应的秒）： second(str/date)
【8】date_format（时间格式化）： date_format(str/date,"yyyy-MM-ss")
【9】dayofweek（返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。）： select DAYOFWEEK('1998-02-03');
【10】weekofyear（返回该周在一年中的第几周）： select weekofyear("2020-04-27")
【11】unix_timestamp()（ 获取当前时间戳）： select unix_timestamp()
【12】unix_timestamp(string timestame)（ 获取指定时间戳）： select unix_timestamp('2020-08-15 16:40:00')
【13】from_unixtime(bigint unixtime,string format)（ 将时间戳秒数转化为UTC时间，并用字符串表示）： select from_unixtime(1565858389,'yyyy-MM-dd HH:mm:ss')
【14】CURRENT_DATE()（ 获取当前时间）： SELECT CURRENT_DATE()

字符函数

【1】lower（转小写）：lower(str)
【2】upper（转大写）： upper(str)
【3】length（字符串长度，字符数）:length(str)
【4】concat（字符串拼接）: concat(str1, str2, ... strN)
【5】concat_ws（字符串拼接）: concat_ws(separator, [string | array(string)]+)
【5】substr（求子串）:

• substr(str,a)：从字符串str中，第a位开始取，取右边所有的字符
• substr(str,a,b)：从字符串str中，第a为开始取，取b个字符
  注意：空格算占用一个字符的空间

【6】trim（去前后空格）: trim(str)
【7】lpad（左填充）: lpad(str,字符串总长度,从左填充的内容)
【8】rpad（右填充）: rpad(str,字符串总长度,从右填充的内容)
【9】reverse（返回字符串A的反转结果）: reverse(string A)
【10】ltrim（去除字符串左边的空格）: ltrim(string A)
【11】rtrim（右边去空格函数）: rtrim(string A)
【12】regexp_replace（正则表达式替换）: regexp_replace(string A, string B, string C)
【13】rpad（右填充）: rpad(str,字符串总长度,从右填充的内容)
【14】regexp_extract（正则表达式解析函数）: regexp_extract(string subject, string pattern, int index)
【15】split（按照指定字符串分割）: split(string str, string pat)
【16】find_in_set（集合查找函数）: find_in_set(string str, string strList)

转换函数

【1】cast（转换数据类型）： cast(原始数据 as 转换成的类型) 例 —》 cast(“2020-01-02” as Date)

条件函数

【1】coalesce（从左到右返回第一个不为null的值）：
【2】case…when…（条件表达式）： (case when 条件1 then 结果1 when 条件1 then 结果2 else 结果3 end)
【3】if（判断语句）： if(条件,true显示,false显示)

表生成函数

【1】explode（将map中的每一个key/value生成一行） explode(map(k1,v1,k2,v2...))

开窗函数

【1】over( 开窗 )

CURRENT ROW：当前行
n PRECEDING：往前n行数据
n FOLLOWING：往后n行数据
UNBOUNDED：起点，UNBOUNDED PRECEDING 表示从前面的起点， UNBOUNDED FOLLOWING表示到后面的终点

LAG(col,n)：往前第n行数据
LEAD(col,n)：往后第n行数据
NTILE(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，编号从1开始，对于每一行，NTILE返回此行所属的组的编号。注意：n必须为int类型
RANK() 排序相同时会重复，总数不会变
DENSE_RANK() 排序相同时会重复，总数会减少
ROW_NUMBER() 会根据顺序计算

cume_dist：
小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
percent_rank：
分组内当前行的RANK值-1/分组内总行数-1

grouping sets:
在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

【2】explode ( 炸裂 )：将hive一列中复杂的array或者map结构拆分成多行
【3】lateral view (侧写) ：laterral view udtf(expression) tableAlias as columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合
【4】reflect()
【5】行转列：collect、collect_ws、collect_set、collect_list
【6】str_to_map ：字符串转Map

---

都看到这里了，点赞评论一下吧！！！