目录
一、概述
最初Hive函数分为 内置函数 和 用户自定义函数两大类, 其中用户自定义函数又被分为3类, 分别是:
用户自定义函数:
UDF: 全称叫 User Defined Functions, 普通函数, 即: 一进一出.
例如: select * from stu;
UDAF: 全称叫 User Defined Aggregation Functions, 聚合函数, 即: 多进一出.
例如: select count(id) from stu;
UDTF: 全称叫 User Defined Table-Generating Functions, 表生成函数, 即: 一进多出.
例如: select explode(array(11, 22, 33));
现在, hive中的函数没有内置函数 和 自定义函数之分了, 取而代之的是: UDF, UDAF, UDTF.
查看Hive针对于该函数的 说明文档
describe function rand; -- 查看函数的说明信息(简单信息)
describe function extended rand; -- 查看函数的说明信息(详细信息), 包括 测试案例
二、函数
1.字符串相关函数
字符串切割
split 参数1: 要操作的字符串, 参数2: 切割符
select split('aa, bb, cc', ', '); -- ["aa","bb","cc"]
substr/substring 参1: 要操作的字符串. 参2: 起始索引, 参数3: 个数
substr() 和 substring()效果一样, 没有区别.
select substring('2023-05-19 11:51:27', 1, 10); -- 2023-05-19
select substr('2023-05-19 11:51:27', 1, 10); -- 2023-05-19
字符串拼接
select concat('aa','bb','cc'); -- 拼接符: 默认是空, 结果为: aabbcc
select concat_ws('#', 'aa','bb','cc'); -- 指定拼接符, 参1是拼接符, 后续的参数是要拼接的内容. 即: aa#bb#cc
正则替换
参1: 要被处理的字符串. 参2:正则表达式. 参3: 用来替换的内容
select regexp_replace('100-200', '\\d+', '夯哥');
处理json字符串
json解析函数：get_json_object(json_txt, path), 细节: 整个json字符串用单引号'包裹, json字符串中的键, 值用双引号"包裹.
json字符串的格式: {键:值, 键: 值}
json数组的格式: [{键:值, 键: 值}, {键:值, 键: 值}, {键:值, 键: 值}] -- 索引从 0 开始.
select get_json_object('{"name":"杨过", "age":"18"}', '$.name'); -- 杨过, $表示json对象
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name'); - 索引从 0 开始2.日期相关函数
获取当前时间, 年月日格式
select current_date();
获取年月日
select year('2023-05-19 14:31:02'); -- 2023
select month('2023-05-19 14:31:02'); -- 5
select day('2023-05-19 14:31:02'); -- 19
时间相关操作
select date_add('2023-05-19', 2); -- 2023-05-21
select date_add('2023-05-19', -2); -- 2023-05-17
select datediff('2023-05-19', '2023-05-20'); -- 前 - 后, -1
扩展, 计算某年的2月有多少天
select day(date_add('2023-03-01', -1));
select dayofmonth(date_add('2024-03-01', -1));3.数字相关函数
rand函数 0.0 ~ 1.0, 包左不包右
select rand();
四舍五入 原理: +0.5, 然后求地板数
select round(4.3);
求绝对值 select abs(-10); 4.非空校验函数
非空查找 coalesce(值1, 值2....), 获取参数列表中, 第1个非null值
select coalesce(null, null, null); -- null
select coalesce(null, 11, 22); -- 11
if函数(条件表达式, 值1, 值2) 先执行条件表达式, 看起结果是否成立, 成立执行值1, 否则执行值2
select if(5 > 3, '郑州', '新乡'); -- 郑州
case when转换
select
case
when 5 > 3 then '大于'
when 5 < 3 then '小于'
else '等于'
end as eq ;
select
case 5
when 1 then '周1'
when 3 then '周3'
when 5 then '周5'
else '不知道周几, 睡迷糊了!'
end as week;5.类型转换函数
select cast(10.3 as int); -- 10
select cast(10 as string); -- '10'
select cast('12.3' as double); -- 12.3
select cast('12.3a' as double); -- null6.行转列相关函数
建表
create table row2col2(
col1 string,
col2 string,
col3 int
)row format delimited fields terminated by '\t';
行转列涉及到的函数
数据采集函数
collect_list() 采集数据, 可重复, 有序
collect_set() 采集数据, 唯一, 无序.
数据拼接函数
concat() 拼接符: 默认为空.
concat_ws() 拼接符: 可以自定义.
最终
select
col1, col2,
-- cast(col3 as string) 把 col3内容转成 字符串, 方便后续 concat_ws()拼接.
-- collect_list(cast(col3 as string)) 把 col3列的内容, 全部采集到, 有序, 可重复.
-- concat_ws('-', collect_list(cast(col3 as string))) 把上述的内容, 按照-,拼接到一起.
concat_ws('-', collect_list(cast(col3 as string))) as col3
from row2col2 group by col1, col2;7.列转行相关函数
建表
create table col2row2(
col1 string,
col2 string,
col3 string
)row format delimited fields terminated by '\t';
列转行涉及到的函数
字符串切割函数 split
数据爆炸函数 explode
侧视图 lateral view
最终
select t1.col1, t1.col2, t2.col3 from col2row2 t1
lateral view explode(split(col3, ',')) t2 as col3;8.窗口函数
8.1概述
窗口函数指的是 over()函数, 它可以结合特定的函数一起使用, 完成不同的功能.
目的/作用:
窗口函数 = 给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
格式:
能和窗口函数一起使用的函数 over(partition by 分组字段 order by 排序字段 rows between 起始行 and 结束行)
能和窗口函数一起使用的函数解释:
聚合函数: count(), sum(), max(), min(), avg()
排序函数: row_number(), rank(), dense_rank(), ntile()
其它函数: lag(), lead(), first_value(), last_value()
8.2细节
1. 窗口函数相当于给表新增一列, 至于新增的内容是什么, 取决于窗口函数和什么函数一起使用.
2. 如果不写partition by, 表示: 统计表中所有的数据, 如果写了 表示统计组内所有的数据.
3. 如果不写order by, 表示: 统计组内所有的数据, 如果写列, 表示统计组内第一行截止到当前行的数据.
4. rows between表示统计的范围, 它可以写的关键字如下:
unbounded preceding 第一行
unbounded following 最后一行
n preceding 向上几行
n following 向下几行
current row 当前行
5. ntile(数字)表示几分之几, 里边的数字表示把数据分成几份, 如果不够分, 优先参考最小分区.
例如: 7条数据分成3份, 则最终结果为: 1, 1, 1 2, 2 3, 3
窗口 + 聚合
求出网站总的pv数 所有用户所有访问加起来
select *, sum(pv) over() as total_pv from website_pv_info;
-- 不写partition by 表示统计表中所有的数据.
求出每个用户总pv数
select *, sum(pv) over(partition by cookieid) as total_pv from website_pv_info; -- 写partition by 统计组内所有的数据.
如果写了order by(表示排序): 则默认操作 组内第一行 至 当前行的数据
select *, sum(pv) over(partition by cookieid order by createtime) as total_pv
from website_pv_info;
上述的代码, 等价于如下的内容:
select *,
sum(pv) over(partition by cookieid
order by createtime
rows between unbounded preceding and current row )
as total_pv
from website_pv_info;
需求: 统计每个cookieID的pv(访问量), 只统计: 当前行及 向前3行 向后1行
select *,
sum(pv) over(partition by cookieid
order by createtime
rows between 3 preceding and 1 following )
as total_pv
from website_pv_info;
窗口 + 排序
排序函数指的是: row_number(), rank(), dense_rank()
例如: 数据是100, 90, 90, 60, 则: row_number是: 1, 2, 3, 4, rank: 1, 2, 2, 4, dense_rank: 1, 2, 2, 3
需求: 根据cookieID进行分组, 获取每组点击量最高的前4名数据
with t1 as (
select
*,
dense_rank() over (partition by cookieid order by pv desc) drk
from website_pv_info
)
select * from t1 where drk <= 4;
ntile
ntile(数字,表示分成几份) 采用均分策略, 每份之间的差值不超过1, 优先参考最小的那个部分, 即: 7分成3份, 则是: 3, 2, 2
需求: 按照cookieid分组, 按照点击量降序排列, 只要每组前三分之一的数据
with t1 as (
select
*,
ntile(3) over (partition by cookieid order by pv desc) nt
from website_pv_info
)
select * from t1 where nt = 1;
窗口 + 其它
LAG 概述 用于统计窗口内往上第n行值
格式: lag(字段, n, 默认值) 向上获取字段的第n个值, 如果没有写写默认值, 找不到就是null, 如果写了默认值, 找不到就用默认值.
需求: 显示用户上一次的访问时间
select
*,
-- 向上1个, 找到就显示, 找不到就显示为 null
lag(createtime) over(partition by cookieid order by createtime) `lag1`,
-- 向上2个, 找到就显示, 找不到就用默认值: '2023-05-20 10:52:05'
lag(createtime, 2, '2023-05-20 10:52:05') over(partition by cookieid order by createtime) `lag2`
from website_url_info;
LEAD
FIRST_VALUE
取分组内排序后，截止到当前行，第一个值
LAST_VALUE
取分组内排序后，截止到当前行，最后一个值
1161
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
