Hive——常用函数
主要内容：
- 介绍Hive中常用的部分函数
数值计算
1、取整函数: round
语法: round(double a)
返回值: BIGINT
说明: 返回double类型的整数值部分 （四舍五入）。
2、指定精度取整函数: round
语法: round(double a, int d)
返回值: DOUBLE
说明: 返回指定精度d的double类型。
3、向下取整函数: floor
语法: floor(double a)
返回值: BIGINT
说明: 返回等于或者小于该double变量的最大的整数。
4、向上取整函数: ceil （ceiling）
语法: ceil(double a)
返回值: BIGINT
说明: 返回等于或者大于该double变量的最小的整数。
日期函数
1、UNIX时间戳转日期函数: from_unixtime
语法: from_unixtime(bigint unixtime[, string format])
返回值: string
说明: 转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式。
2、获取当前UNIX时间戳函数: unix_timestamp
语法: unix_timestamp()
返回值: bigint
说明: 获得当前时区的UNIX时间戳
3、日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date)
返回值: bigint
说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到UNIX时间戳。如果转化失败，则返回0。
4、指定格式日期转UNIX时间戳函数: unix_timestamp
语法: unix_timestamp(string date, string pattern)
返回值: bigint
说明: 转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0。
5、日期时间转日期函数: to_date
语法: to_date(string timestamp)
返回值: string
说明: 返回日期时间字段中的日期部分。
6、日期比较函数: datediff
语法: datediff(string enddate, string startdate)
返回值: int
说明: 返回结束日期减去开始日期的天数。
7、日期增加函数: date_add
语法: date_add(string startdate, int days)
返回值: string
说明: 返回开始日期startdate增加days天后的日期。
条件函数
1、If函数: if
语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)
返回值: T
说明: 当条件testCondition为TRUE时，返回valueTrue；否则返回valueFalseOrNull。
2、非空查找函数: COALESCE
语法: COALESCE(T v1, T v2, …)
返回值: T
说明: 返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL。
3、条件判断函数：CASE
语法: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
返回值: T
说明：如果a等于b，那么返回c；如果a等于d，那么返回e；否则返回f
4、条件判断函数：CASE
语法: CASE WHEN a THEN b [WHEN c THEN d]* [ELSE e] END
返回值: T
说明：如果a为TRUE，则返回b；如果c为TRUE，则返回d；否则返回e。
字符串函数
1、字符串连接函数：concat
语法: concat(string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，支持任意个输入字符串。
2、带分隔符字符串连接函数：concat_ws
语法: concat_ws(string SEP, string A, string B…)
返回值: string
说明：返回输入字符串连接后的结果，SEP表示各个字符串间的分隔符。
复合类型
1、Map类型构建: map
语法: map (key1, value1, key2, value2, …)
说明: 根据输入的key和value对构建map类型。
访问: M[key]，M为map类型，key为map中的key值。
2、Array类型构建: array
语法: array(val1, val2, …)
说明: 根据输入的参数构建数组array类型。
访问: A[n]，A为array类型，n为int类型。
3、Struct类型构建: struct
语法: struct(val1, val2, val3, …)
说明: 根据输入的参数构建结构体struct类型
访问: S.x，S为struct类型，返回结构体S中的x字段。
Hive当中的 lateral view 与 explode以及reflect和窗口函数
1、使用explode函数将hive表中的Map和Array字段进行拆分
lateral view经常和split、explode等UDTF函数一起使用，能将一行数据拆分成多行数据。在此基础上可以对拆分的数据进行聚合，lateral view首先为原始表的每行调用UDTF，UDTF会把一行拆分成一行或者多行，lateral view在把结果组合，产生一个支持别名表的虚拟表。其中explode还可以用于将hive一列中复杂的array或者map结构拆分成多行。Outer关键字可以把不输出的UDTF的空结果，输出成NULL，防止丢失数据。
2、行转列
CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串。CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL 和空字符串。分隔符将被加到被连接的字符串之间。
COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生array类型字段。
3、列转行
EXPLODE(col)：将hive一列中复杂的array或者map结构拆分成多行。
LATERAL VIEW用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias
解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。
行转列和列转行请参考：行转列及列转行最佳实践
4、reflect函数
reflect函数可以支持在sql中调用java中的自带函数，秒杀一切udf函数。
select reflect("java.lang.Math", "max", 1, 2) 输出：2
排序函数
1、 ROW_NUMBER()
从1开始，按照顺序，生成分组内记录的序列。值相同时，名次不同。
2、 RANK()
计算跳跃排名。相同组的值相同的行数据获得的排名相同，且排名跳跃。例如两个第二名后为第四名。
3、 DENSE_RANK()
计算连续排名。相同组的值相同的行数据获得的排名相同，且排名连续。例如两个第二名后为第三名。
+-----------+------------+--------+----+-----+-----+--+
| user_id | user_type | sales | r | rn | dr |
+-----------+------------+--------+----+-----+-----+--+
| wutong | new | 6 | 1 | 1 | 1 |
| qishili | new | 5 | 2 | 2 | 2 |
| lilisi | new | 5 | 2 | 3 | 2 |
| wagner | new | 3 | 4 | 4 | 3 |
| zhangsan | new | 2 | 5 | 5 | 4 |
| qiuba | new | 1 | 6 | 6 | 5 |
| liliu | new | 1 | 6 | 7 | 5 |
| liwei | old | 3 | 1 | 1 | 1 |
| wangshi | old | 2 | 2 | 2 | 2 |
| lisi | old | 1 | 3 | 3 | 3 |
+-----------+------------+--------+----+-----+-----+--+
多维分析
1、GROUPING SETS
对于经常需要对数据进行多维度的聚合分析的场景，您既需要对A列做聚合，也要对B列做聚合，同时要对A、B两列做聚合，因此需要多次使用union all。您可以使用grouping sets快速解决此类问题。可参考：GROUPING SETS