hive-7（常用函数）

hive-7（常用函数）

常用函数

• 数据准备
• first_value last_value
• 正则函数
• • regexp
  • regexp_extract
  • regexp_replace
• URL解析函数
• JSON_OBJECT和JSON_TUPLE
• 分位函数
• LEAD LAG
• 窗口范围限定

数据准备

CREATE DATAbase IF NOT EXISTS test_data;

-- 创建学生表并插入数据
CREATE TABLE IF NOT EXISTS test_data.student(
sid string,
sname string,
sage string,
gender string
);
INSERT INTO test_data.student VALUES
('01' , '赵雷' , '1990-01-01' , '男'),
('02' , '钱电' , '1990-12-21' , '男'),
('03' , '孙风' , '1990-12-20' , '男'),
('04' , '李云' , '1990-12-06' , '男'),
('05' , '周梅' , '1991-12-01' , '女'),
('06' , '吴兰' , '1992-01-01' , '女'),
('07' , '郑竹' , '1989-01-01' , '女'),
('09' , '张三' , '2017-12-20' , '女'),
('10' , '李四' , '2017-12-25' , '女'),
('11' , '李四' , '2012-06-06' , '女'),
('12' , '赵六' , '2013-06-13' , '女'),
('13' , '孙七' , '2014-06-01' , '女');

-- 创建课程表并插入数据
CREATE TABLE IF NOT EXISTS test_data.course(
cid string,
cname string,
tid string);
INSERT INTO test_data.course VALUES
('01' , '语文' , '02'),
('02' , '数学' , '01'),
('03' , '英语' , '03');

-- 创建老师表并插入数据
CREATE TABLE IF NOT EXISTS test_data.teacher(
tid string,
tname string);
INSERT INTO test_data.teacher VALUES
('01' , '张三'),
('02' , '李四'),
('03' , '王五');

-- 创建成绩表并插入数据
CREATE TABLE IF NOT EXISTS test_data.score(
sid string,
cid string,
score decimal(18,1));
INSERT INTO test_data.score VALUES
('01' , '01' , 80),
('01' , '02' , 90),
('01' , '03' , 99),
('02' , '01' , 70),
('02' , '02' , 60),
('02' , '03' , 80),
('03' , '01' , 80),
('03' , '02' , 80),
('03' , '03' , 80),
('04' , '01' , 50),
('04' , '02' , 30),
('04' , '03' , 20),
('05' , '01' , 76),
('05' , '02' , 87),
('06' , '01' , 31),
('06' , '03' , 34),
('07' , '02' , 89),
('07' , '03' , 98);

以下将使用上述数据演示各个函数的用途

set hive.cli.print.header=true;  -- 配置Hive查询结果显示列名
set hive.resultset.use.unique.column.names=false; -- 配置查询结果只显示列名不显示表名

first_value last_value

函数用法
first_value(x) over(partition by y order by z [desc])
last_value(x) over(partition by y order by z [desc])

含义
在y字段指定的分区内，数据根据z字段排序，显示截至当前行第一次出现或最后一次出现的x字段的值
这两个函数都是窗口函数，不可以用作聚合函数，last_value的作用是在指定分区和指定排序下，找到截至当前最后一次出现的列数值，first_value函数则是找到第一次出现的数值

示例

SELECT *,
first_value(cid) over(partition by sid order by score desc) as first,
last_value(cid) over(partition by sid order by score desc) as last
FROM SCORE;

在数仓设计中经常会使用增量的方式导入数据，当对应的OLTP数据库数据变化时，就在数仓中新插入一条数据来覆盖旧数据，此时使用last_value就会很方便（把上面的score理解为数据修改时间，cid理解为数据状态会更容易理解）

在ClickHouse中也有同样的函数，argmin和argmax函数分别对应Hive中的first_value和last_value函数

正则函数 regexp

函数用法
regexp(str, regexp) 或者直接str regexp regexp也可以

含义
正则匹配函数，判断目标字符串str是否符合正则规则(regexp参数)，成功匹配返回true，否则返回false

示例

select
regexp(c1,'([A-Z]{1})([A-Z]{2})([A-H]{4})[A-Z]*'),
c1 regexp '[A-Z]{1}[A-Z]{2}[A-H]{4}[A-Z]*'
from
(
select 'LAJKSDOFQWEFNLASD' as c1 union all
select 'OJOSDOJFOWENFFOSD' as c1 union all
select 'ABCDEFGHIJKLMNOPQ' as c1 union all
select 'ABCDEABCDEABCDEAB' as c1
) as t;

regexp_extract

函数用法
regexp_extract(str, regexp[, idx])

含义
正则抽取函数，先将目标字符串str与正则规则(regexp参数)进行匹配，匹配成功后取其中指定字串(idx参数)，idx=0时取整个字符串，idx=1时取regexp参数中第一个括号内的子串，idx=2则取第二个，依次类推

示例

select
regexp_extract(c1,'([A-Z]{1})([A-Z]{2})([A-H]{4})[A-Z]*',0),
regexp_extract(c1,'([A-Z]{1})([A-Z]{2})([A-H]{4})[A-Z]*',1),
regexp_extract(c1,'([A-Z]{1})([A-Z]{2})([A-H]{4})[A-Z]*',2),
regexp_extract(c1,'([A-Z]{1})([A-Z]{2})([A-H]{4})[A-Z]*',3)
from
(
select 'LAJKSDOFQWEFNLASD' as c1 union all
select 'OJOSDOJFOWENFFOSD' as c1 union all
select 'ABCDEFGHIJKLMNOPQ' as c1 union all
select 'ABCDEABCDEABCDEAB' as c1
) as t;

regexp_replace

函数用法
regexp_replace(str, regexp, rep)

含义
正则替换函数，将str中符合regexp规则的部分替换为rep，如果单个字符串内可以多次匹配成功，会将全部匹配成功的替换掉，返回替换后的字符串

示例

select
regexp_replace(c1,'[A-L]{2}','123')
from
(
select 'LAJKSDOFQWEFNLASD' as c1 union all
select 'OJOSDOJFOWENFFOSD' as c1 union all
select 'ABCDEFGHIJKLMNOPQ' as c1 union all
select 'ABCDEABCDEABCDEAB' as c1
) as t;

URL解析函数

函数用法
parse_url(url, partToExtract[, key])
parse_url_tuple(url, partname1, partname2, ..., partnameN)

含义
从给定的URL中解析出指定的部分，partToExtract指定解析URL的哪个部分，有几个可选项，以案例说明各选项作用：

-- protocol（协议）：https，一种可以通过安全的 HTTPS 访问该资源的传输协议
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','PROTOCOL');
-- https

-- 解析主机名：hostname（主机名）
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','HOST');
-- www.baidu.com

-- path（路径）：由零或多个“/”符号隔开的字符串，一般用来表示主机上的一个目录或文件地址
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','PATH');
-- /hzy

-- query(查询)：可选，用于给动态网页传递参数，可有多个参数，用“&”符号隔开，每个参数的名和值用“=”符号隔开
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','QUERY');
-- user_id=10000&platform=ios

-- 解析QUERY部分的value值：
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','QUERY','user_id');
-- 10000
select parse_url('https://www.baidu.com/hzy?user_id=10000&platform=ios','QUERY','platform');
-- ios

-- 以下几个选项不常用
select parse_url('http://facebook.com/path/p1.php?query=1', 'REF');
-- 空
​select parse_url('http://facebook.com/path/p1.php?query=1','FILE');
-- /path/p1.php?query=1
select parse_url('http://facebook.com/path/p1.php?query=1', 'AUTHORITY');
-- facebook.com
select parse_url('http://facebook.com/path/p1.php?query=1', 'USERINFO');
-- 空

JSON_OBJECT和JSON_TUPLE

函数用法
get_json_object(json_txt, path)
json_tuple(jsonStr, p1, p2, ..., pn)

含义
解析JSON字符串，返回指定属性对应的数值

示例

{
	"clazz": [
		{
			"clazzId": 11,
			"students": [
				{
					"age": 20,
					"name": "张三",
					"studentId": 1
				},
				{
					"age": 32,
					"name": "李四",
					"studentId": 2
				},
				{
					"age": 25,
					"name": "王五",
					"studentId": 3
				}
			]
		},
		{
			"clazzId": 12,
			"students": [
				{
					"age": 54,
					"name": "赵六",
					"studentId": 4
				},
				{
					"age": 52,
					"name": "田七",
					"studentId": 5
				}
			]
		}
	],
	"schoolId": 111
}

-- get_json_object是可以多级解析的，但是初始输入的字符串不可以是数组形式
select
get_json_object(json_str,'$.schoolId') as school_id,
get_json_object(json_str,'$.clazz.clazzId') as clazzId,
get_json_object(json_str,'$.clazz.students.studentId') as studentId,
get_json_object(json_str,'$.clazz.students.name') as name,
get_json_object(json_str,'$.clazz.students.age') as age
from
(select
'{"clazz":[{"clazzId":11,"students":[{"age":20,"name":"张三","studentId":1},{"age":32,"name":"李四","studentId":2},{"age":25,"name":"王五","studentId":3}]},{"clazzId":12,"students":[{"age":54,"name":"赵六","studentId":4},{"age":52,"name":"田七","studentId":5}]}],"schoolId":111}' as json_str) js;

-- json_tuple函数一次可以读取JSON串中的多个属性值，不需要使用路径指定，但是也不能读取多层
select
json_tuple(json_str,'schoolId','clazz.clazzId','clazz')
from
(select
'{"clazz":[{"clazzId":11,"students":[{"age":20,"name":"张三","studentId":1},{"age":32,"name":"李四","studentId":2},{"age":25,"name":"王五","studentId":3}]},{"clazzId":12,"students":[{"age":54,"name":"赵六","studentId":4},{"age":52,"name":"田七","studentId":5}]}],"schoolId":111}' as json_str) js;

一般认为在读取多个属性值时，JSON_TUPLE函数会比GET_JSON_OBJECT函数效率更高，原因在于JSON_TUPLE函数只需要调用一次。但是JSONT_TUPLE函数只能读取JSON字符串的第一层数值，而GET_JSON_OBJECT可以读取多层且支持正则和嵌套，工作中灵活选取。

分位函数

函数用法
percentile(expr, pc)
percentile_approx(expr, pc, [nb])

含义
percentile函数的作用是找到指定列的指定百分位数，所谓百分位数，就是一系列数值从小到大排列后，从开始到结束为0%到100%，位于p%位置的数字为这一系列数值的p%分位值。
函数的第一个参数为目标数值列，这个列必须是int类型，pc参数为指定的百分位值，为0到1之间的数值，如果需要查询多个百分位值，pc参数也可以是array类型参数

percentile_approx函数类似于percentile函数，区别在于找到的近似分位值，同时percentile_approx输入的列只要是数值类型就可以，不局限于int类型，percentile_approx函数支持传入第三个参数nb，该参数空值内存消耗的近似精度，nb越大，值越精确，默认值为10000，当expr字段中的distinct值的个数小于nb时，结果为准确的百分位数。

示例

-- percentile函数需要将decimal类型转换成int后才能使用
select
	percentile(c1,array(0.1,0.2,0.5,0.9,1.0))
from (select cast(score as int) as `c1` from score order by c1 limit 10) as t1;

-- percentile_approx函数可以直接传入decimal类型数值
select
percentile_approx(score,array(0.1,0.2,0.5,0.9),5)
from score limit 10;

LEAD LAG

函数用法
LAG (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)
LEAD (scalar_expression [,offset] [,default]) OVER ([query_partition_clause] order_by_clause)

含义
两个函数都是窗口函数，lag函数用于查看当前行向上行的数据，lead函数用于查看当前行向下行的数据，以lag函数为例解释参数含义

• scalar_expression为上几行需要查看到的列名
• offset为向上查看的行数，默认为1
• default是默认值，即当前行为第一行时，没有上一行，此时函数的结果会是default，default默认是null

示例

select
cid,score,
lag(score,2,-1) over(partition by cid order by score desc) as lag,
lead(score,1) over(partition by cid order by score desc) as lead
from score;

框选的结果就是在指定分区内没有向上(下)指定行数的数值，lag列指定了默认值，所以默认显示-1，lead列没有指定，所以结果为null

窗口范围限定

一般常用的窗口大小限定为partition by，也就是通过字段分区，同一字段的划分为一个窗口，除此之外Hive还支持行级别的窗口范围限定，一般语法为

over([partition by ...] [order by ... [desc]] [rows between ... and ...])

这里rows between用于限定窗口的函数，这里有几个关键词

• unbounded：没有行数界限，直到第一行或最后一行，这里的第一行或最后一行如果在有partition by指定分区时，为当前分区的第一行或最后一行
• preceding：向前
• following：向后
• current row：当前行

-- 以cid为分区，sid升序排列，查看分区内第一行到当前行上一行内最大的score值
select
*,
max(score) over(partition by cid order by sid rows between unbounded preceding and 1 preceding)
from score;

根据实际需要灵活限定即可，这里再举几个例子

-- 从第一行到当前行
over(partition by cid order by sid rows between unbounded preceding and current row)

-- 从当前行上一行到最后一行
over(partition by cid order by sid rows between 1 preceding and unbounded following)

-- 当前行的上2行到下1行
over(partition by cid order by sid rows between 2 preceding and 1 following)

-- 上下不设限，和只设定partition效果相同
over(partition by cid order by sid rows between unbounded preceding and unbounded following)