一、Hive函数
1.函数概述
从函数输入与输出角度来看，函数可分为：
（1）标准函数
以一行数据中心的一列或多列数据作为输入参数，返回的结果是一个值的函数。
（2）聚合函数
以多行中的零个到多个列的数据作为输入，返回单一值的函数。
（3）表生成函数
接受零个或多个输入，产生多列或多行输出。
2.查看函数
show functions命令可以显示当前Hive会话中所加载的所有函数，包括内置函数与自定义函数。
#查看函数详情，在Hive命令行中运行命令
DESCRIBE FUNCTIONS <function_name>;
函数更多详细文档可以通过增加EXTENDED关键字查看。
3.调用函数
调用函数直接在HiveSQL中使用函数名调用，如需传入参数，直接传入相应参数即可调用。
函数调用可在SELECT与WHERE子句中实现，具体包含下面3种典型情况：
# table 表中选取 col1 和 col2 列的值，并将它们连接成一个字符串，作为结果集的一列
SELECT concat(col1,col2) AS x FROM table;
# 连接两个字符串常量 'abc' 和 'def'，并返回连接后的结果
SELECT concat(‘abc’,‘def’);
# 从 table 表中选取所有列的数据，并筛选出 col 列的长度小于 10 的记录。
SELECT * FROM table WHERE length(col)<10;
二、内置函数
- 字符处理
- 类型转换
- 聚合运算
- 数学计算
- 日期计算
1.字符函数
| 返回值 | 函数签名 | 描述 |
| string | concat(a, b,...) | 它返回从a后串联b产生的字符串 |
| string | concat_ws(sep,a,b,...) | 与concat类似，但第一个参数为指定的连接符 |
| string | concat_ws(sep,array<string>) | 与concat_ws类似，但拼接的是指定array中的元素 |
| array<> | split(str,pat) | 按正则表达式pat来分割字符串str，并将分割后的结果以数组返回 |
| map<string,string> | str_to_map(text[,deli1,deli2]) | 将字符串转换为map，第一个参数是需要转换的字符串，第二个参数是元素之间分隔符，默认”,”，第三个参数是键值分隔符默认“=” |
| int | instr(str,substr) | 查找字符串str中子字符串substr出现的位置，如果查找失败返回0 |
| string | substr(a,start[,len]) | 对字符串a从start位置开始截取长度为len的字符串并返回 |
| int | locate(substr,str[,pos]) | 查找字符串str中pos位置后字符串substr第一次出现的位置 |
| int | length(a) | 返回字符串长度 |
| string | lower(a) | 将字符串中所有字母转换成小写 |
| string | upper(a) | 将字符串中所有字母转换成大写 |
# 在零售数据顾客表中转换顾客姓名为全大写
select upper(concat_ws('',customer_fname,customer_lname))as fullname
from customers limit 10;
2.聚合函数
常用包括count、sum、max/min、avg等。与标准函数的不同之处在于，聚合函数是在一组多行数据中进行计算并返回单一值的函数。
3.类型转换函数
如下表：
| 返回值 | 函数签名 | 描述 |
| binary | binary(string|binary) | 将输入的值转换成二进制 |
| <type> | cast(expr as <type>) | 将expr转换成type类型，转换失败返回NULL |
4.数学函数
通常针对数据类型进行操作。
| double | round(a) | 返回对a四舍五入的值，小数位为0 |
| double | round(a,d) | 返回对a四舍五入的值，并且保留d位小数 |
| bigint | floor(a) | 向下取整，如floor(3.84) = 3 |
| double | rand(),rand(seed) | 返回随机数，可指定随机因子seed |
5.日期函数
| 返回值 | 函数签名 | 描述 |
| string | from_unixtime(unixtime[,format]) | 将时间的秒值转换成format格式，如”yyyy-MM-dd hh:mm:ss” |
| bigint | unix_timestamp() | 获取当前本地时区下的时间戳 |
| bigint | unix_timestamp(date) | 将”yyyy-MM-dd hh:mm:ss”格式的时间字符串转为时间戳 |
| string | to_data(timestamp) | 返回时间字符串的日期部分 |
| int | year(date) | 返回时间字符串的年份部分 |
| int | month(date) | 返回时间字符串的月份部分 |
| int | day(date) | 返回时间字符串的天 |
| int | hour(date) | 返回时间字符串的小时 |
| int | minute(date) | 返回时间字符串的分钟 |
| int | second(date) | 返回时间字符串的秒钟 |
| int | datediff(startdate,enddate) | 计算开始时间到结束时间的天数 |
| string | date_add(startdate,days) | 从startdate时间开始加上days天 |
| string | date_sub(startdate,days) | 从startdate时间开始减去days天 |
# 统计月度订单数量
select from_unixtime(unix_timestamp(order_date),"yyyy-MM")as year_month,
count(order_id) from orders
group by from_unixtime(unix_timestamp(order_date),"yyyy-MM")6.条件函数
| 返回值 | 函数签名 | 描述 |
| T | if(testCondition,valueTrue,valueFalseOrNull) | 如果testCondition为True，返回valueTrue，否则返回valueFalse |
| boolean | isnull(a) | 如果a为Null返回True，否则返回False |
| boolean | isnotnull(a) | 如果a不为Null返回True，否则返回False |
| T | nvl(value,defaultValue) | 如果value为Null，返回defaultValue，否则返回value |
| T | coalesce(v1,v2,…) | 返回第一个非Null的值，如coalesce(NULL,1,2)=1 |
| 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 | 当a=true返回b，当c=true返回d否则返回e |
| void | assert_true(condition) | 用于测试，当condition=false抛出异常 |
# 根据商品价格将商品分为3个级别：0~100、100~200及200以上，并分别统计各档商品个数。
select level,count(*)from ( select *,
case when product_price<100 then 1
when product_price between 100 and 200 then 2
else 3 end as level
from products ) as a group by level;7.集合函数
集合函数用于对集合进行操作。
eg.雇员表empdb.emp，使用集合函数查询其中所有员工所在部门的名称及总数。
use empdb;
select name,map_keys(depart_title),size(depart_title)from emp;8.表生成函数
可以将一个或多个输入值转换为多行输出，该输出可以作为表来使用。
eg.(1)使用表生成函数将如下数据生成为表。
['Apple','Orange','Mongo']
['A':'Apple','O':'Orange']
select explode(array('Apple','Orange','Mongo'));
select explode(map('A','Apple','O','Orange'));(2)将json字符串"{"name":"Jason","age":"18"}"转换为表
select json_tuple('{"name":"Jason","age":"18"}','name','age');