大数据学习之Hive函数

一. Hive函数基础

1. 聚合函数

hive 支持 count(),max(),min(),sum(),avg() 等常用的聚合函数

注意：

聚合操作时要注意 null 值
count(*) 包含 null 值，统计所有行数
count(id) 不包含 null 值
min 求最小值是不包含 null，除非所有值都是 null
avg 求平均值也是不包含 null

2. 关系运算

支持：等值(=)、不等值(!= 或 <>)、小于(<)、小于等于(<=)、大于(>)、大于等于(>=)

空值判断(is null)、非空判断(is not null)

LIKE 比较: LIKE

语法: A LIKE B

操作类型: strings

描述: 如果字符串 A 或者字符串 B 为 NULL，则返回 NULL；如果字符串 A 符合表达式 B 的正则语法，则为TRUE；

3. 数学运算

支持所有数值类型：加(+)、减(-)、乘(*)、除(/)、取余(%)、位与(&)、位或(|)、位异或(^)、位取反(~)

4. 逻辑运算

支持：逻辑与(and)、逻辑或(or)、逻辑非(not)

5. 数值运算

• 取整函数: round

语法: round(double a)

返回值: BIGINT

说明: 返回 double 类型的整数值部分 （遵循四舍五入）

• 指定精度取整函数: round

语法: round(double a, int d)

返回值: DOUBLE

说明: 返回指定精度 d 的 double 类型

• 向下取整函数: floor

语法: floor(double a)

返回值: BIGINT

说明: 返回等于或者小于该 double 变量的最大的整数

• 向上取整函数: ceil

语法: ceil(double a)

返回值: BIGINT

说明: 返回等于或者大于该 double 变量的最小的整数

• 取随机数函数: rand

语法: rand(),rand(int seed)

返回值: double

说明: 返回一个 0 到 1 范围内的随机数。如果指定种子 seed，则会等到一个稳定的随机数序列

• 自然指数函数: exp

语法: exp(double a)

返回值: double

说明: 返回自然对数 e 的 a 次方

• 以 10 为底对数函数: log10

语法: log10(double a)

返回值: double

说明: 返回以 10 为底的 a 的对数

此外还有：以 2 为底对数函数: log2()、对数函数: log()

• 幂运算函数: pow

语法: pow(double a, double p)

返回值: double

说明: 返回 a 的 p 次幂

• 开平方函数: sqrt

语法: sqrt(double a)

返回值: double

说明: 返回 a 的平方根

• 二进制函数: bin

语法: bin(BIGINT a)

返回值: string

说明: 返回 a 的二进制代码表示

6. 条件函数

• If 函数: if

语法: if(boolean testCondition, T valueTrue, T valueFalseOrNull)

返回值: T

说明: 当条件 testCondition 为 TRUE 时，返回 valueTrue；否则返回 valueFalseOrNull

• 非空查找函数: coalesce

语法: coalesce(T v1, T v2, …)

返回值: T

说明: 返回参数中的第一个非空值；如果所有值都为 NULL，那么返回 NULL

• 条件判断函数：case when (两种写法，其一)

语法: case when a then b [when c then d]* [else e] end

返回值: T

说明：如果 a 为 TRUE,则返回 b；如果 c 为 TRUE，则返回 d；否则返回 e

• 条件判断函数：case when (两种写法，其二)

语法: case a when b then c [when d then e]* [else f] end

返回值: T

说明：如果 a 等于 b，那么返回 c；如果 a 等于 d，那么返回 e；否则返回 f

7. 日期函数

• 获取当前 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp()

返回值: bigint

说明: 获得当前时区的 UNIX 时间戳

• UNIX 时间戳转日期函数: from_unixtime

语法: from_unixtime(bigint unixtime[, string format])

返回值: string

说明: 转化 UNIX 时间戳（从 1970-01-01 00:00:00 UTC 到指定时间的秒数）到当前时区的时间格式

• 日期转 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp(string date)

返回值: bigint

说明: 转换格式为"yyyy-MM-dd HH:mm:ss"的日期到 UNIX 时间戳。如果转化失败，则返回 0。

• 指定格式日期转 UNIX 时间戳函数: unix_timestamp

语法: unix_timestamp(string date, string pattern)

返回值: bigint

说明: 转换 pattern 格式的日期到 UNIX 时间戳。如果转化失败，则返回 0。

• 日期时间转日期函数: to_date

语法: to_date(string timestamp)

返回值: string

说明: 返回日期时间字段中的日期部分。

• 日期转年函数: year

语法: year(string date)

返回值: int

说明: 返回日期中的年。

• 日期转月函数: month

语法: month (string date)

返回值: int

说明: 返回日期中的月份。

• 日期转天函数: day

语法: day (string date)

返回值: int

说明: 返回日期中的天。

• 日期转小时函数: hour

语法: hour (string date)

返回值: int

说明: 返回日期中的小时。

• 日期转分钟函数: minute

语法: minute (string date)

返回值: int

说明: 返回日期中的分钟。

• 日期转秒函数: second

语法: second (string date)

返回值: int

说明: 返回日期中的秒。

• 日期转周函数: weekofyear

语法: weekofyear (string date)

返回值: int

说明: 返回日期在当前的周数。

• 日期比较函数: datediff

语法: datediff(string enddate, string startdate)

返回值: int

说明: 返回结束日期减去开始日期的天数。

• 日期增加函数: date_add

语法: date_add(string startdate, int days)

返回值: string

说明: 返回开始日期 startdate 增加 days 天后的日期。

• 日期减少函数: date_sub

语法: date_sub (string startdate, int days)

返回值: string

说明: 返回开始日期 startdate 减少 days 天后的日期。

8. 字符串函数

• 字符串长度函数：length

语法: length(string A)

返回值: int

说明：返回字符串 A 的长度

• 字符串反转函数：reverse

语法: reverse(string A)

返回值: string

说明：返回字符串 A 的反转结果

• 字符串连接函数：concat

语法: concat(string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，支持任意个输入字符串

• 带分隔符字符串连接函数：concat_ws

语法: concat_ws(string SEP, string A, string B…)

返回值: string

说明：返回输入字符串连接后的结果，SEP 表示各个字符串间的分隔符

• 字符串截取函数：substr,substring

语法: substr(string A, int start),substring(string A, int start)

返回值: string

说明：返回字符串 A 从 start 位置到结尾的字符串

• 字符串截取函数：substr,substring

语法: substr(string A, int start, int len),substring(string A, int start, int len)

返回值: string

说明：返回字符串 A 从 start 位置开始，长度为 len 的字符串

• 字符串转大写函数：upper,ucase

语法: upper(string A) ucase(string A)

返回值: string

说明：返回字符串 A 的大写格式

• 字符串转小写函数：lower,lcase

语法: lower(string A) lcase(string A)

返回值: string

说明：返回字符串 A 的小写格式

• 去空格函数：trim

语法: trim(string A)

返回值: string

说明：去除字符串两边的空格

• 左边去空格函数：ltrim

语法: ltrim(string A)

返回值: string

说明：去除字符串左边的空格

• 右边去空格函数：rtrim

语法: rtrim(string A)

返回值: string

说明：去除字符串右边的空格

• 正则表达式替换函数：regexp_replace

语法: regexp_replace(string A, string B, string C)

返回值: string

说明：将字符串 A 中的符合 java 正则表达式 B 的部分替换为 C。注意，在有些情况下要使用转义字符，类似 oracle 中的 regexp_replace 函数。

• 正则表达式解析函数：regexp_extract

语法: regexp_extract(string subject, string pattern, int index)

返回值: string

说明：将字符串 subject 按照 pattern 正则表达式的规则拆分，返回 index 指定的字符。

• URL 解析函数：parse_url

语法: parse_url(string urlString, string partToExtract [, string keyToExtract])

返回值: string

说明：返回 URL 中指定的部分。partToExtract 的有效值为：

• json 解析函数：get_json_object

语法: get_json_object(string json_string, string path)

返回值: string

说明：解析 json 的字符串 json_string,返回 path 指定的内容。如果输入的 json 字符串无效，那么返回 NULL。

• 空格字符串函数：space

语法: space(int n)

返回值: string

说明：返回长度为 n 的字符串

• 重复字符串函数：repeat

语法: repeat(string str, int n)

返回值: string

说明：返回重复 n 次后的 str 字符串

• 分割字符串函数: split

语法: split(string str, string pat)

返回值: array

说明: 按照 pat 字符串分割 str，会返回分割后的字符串数组

二. Hive函数进阶

1. UDTF之explode函数

• explode语法功能

explode函数接收map或者array类型的数据作为参数，然后把参数中的每个元素炸开变成一行数据。一个元素一行。这样的效果正好满足于输入一行输出多行。

explode函数在关系型数据库中本身是不该出现的。

因为他的出现本身就是在操作不满足第一范式的数据（每个属性都不可再分）。本身已经违背了数据库的设计原理，但是在面向分析的数据库或者数据仓库中，这些规范可以发生改变

explode(array)将array列表里的每个元素生成一行；

explode(map)将map里的每一对元素作为一行，其中key为一列，value为一列；

一般情况下，explode函数可以直接使用即可，也可以根据需要结合lateral view侧视图使用。

• explode函数的使用

select explode(`array`(11,22,33)) as item;
select explode(`map`("id",10086,"name","zhangsan","age",18));

2. Lateral View侧视图

• 概念

Lateral View是一种特殊的语法，主要用于搭配UDTF类型功能的函数一起使用，用于解决UDTF函数的一些查询限制的问题。

侧视图的原理是将UDTF的结果构建成一个类似于视图的表，然后将原表中的每一行和UDTF函数输出的每一行进行连接，生成一张新的虚拟表。这样就避免了UDTF的使用限制问题。使用lateral view时也可以对UDTF产生的记录设置字段名称，产生的字段可以用于group by、order by 、limit等语句中，不需要再单独嵌套一层子查询。

一般只要使用UDTF，就会固定搭配lateral view使用。

• UDTF配合侧视图使用

lateral view侧视图基本语法如下

select …… from tabelA lateral view UDTF(xxx) 别名as col1,col2,col3……;

• 行转列

相关参数说明:

CONCAT(string A/col, string B/col…)：返回输入字符串连接后的结果，支持任意个输入字符串;

CONCAT_WS(separator, str1, str2,...)：它是一个特殊形式的 CONCAT()。

第一个参数剩余参数间的分隔符。分隔符可以是与剩余参数一样的字符串。如果分隔符是 NULL，返回值也将为 NULL。这个函数会跳过分隔符参数后的任何 NULL和空字符串。分隔符将被加到被连接的字符串之间;

COLLECT_SET(col)：函数只接受基本数据类型，它的主要作用是将某字段的值进行去重汇总，产生 array 类型字段。

• 列转行

所需函数:

EXPLODE(col)：将 hive 一列中复杂的 array 或者 map 结构拆分成多行。

LATERAL VIEW

用法：LATERAL VIEW udtf(expression) tableAlias AS columnAlias

解释：用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

三. Hive窗口函数

1. 窗口函数概述

窗口函数（Window functions）是一种SQL函数，非常适合于数据分析，因此也叫做OLAP函数，其最大特点是：输入值是从SELECT语句的结果集中的一行或多行的“窗口”中获取的。你也可以理解为窗口有大有小（行有多有少）。

通过OVER子句，窗口函数与其他SQL函数有所区别。如果函数具有OVER子句，则它是窗口函数。如果它缺少OVER子句，则它是一个普通的聚合函数。

窗口函数可以简单地解释为类似于聚合函数的计算函数，但是通过GROUP BY子句组合的常规聚合会隐藏正在聚合的各个行，最终输出一行，窗口函数聚合后还可以访问当中的各个行，并且可以将这些行中的某些属性添加到结果集中。

2. 窗口函数语法

Function(arg1,..., argn) OVER ([PARTITION BY <...>] [ORDER BY <....>] [<window_expression>])
--其中Function(arg1,..., argn) 可以是下面分类中的任意一个
    --聚合函数：比如sum max avg等
    --排序函数：比如rank row_number等
    --分析函数：比如lead lag first_value等
--OVER [PARTITION BY <...>] 类似于group by 用于指定分组  每个分组你可以把它叫做窗口
--如果没有PARTITION BY 那么整张表的所有行就是一组
--[ORDER BY <....>]  用于指定每个分组内的数据排序规则 支持ASC、DESC
--[<window_expression>] 用于指定每个窗口中 操作的数据范围 默认是窗口中所有行