主要内容:

  • 介绍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