Hive相关函数

已于 2023-11-16 12:03:24 修改
阅读量148 收藏
点赞数 6
分类专栏： Hive学习 文章标签： hive hadoop 数据仓库
于 2023-11-16 11:55:36 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_73500885/article/details/134437026
版权

目录

函数分类标准

知识点:

原生分类标准:  内置函数 和 用户定义函数(UDF,UDAF,UDTF)

分类标准扩大化:  本来，UDF 、UDAF、UDTF这3个标准是针对用户自定义函数分类的； 但是，现在可以将这个分类标准扩大到hive中所有的函数，包括内置函数和自定义函数；

目前hive三大标准
UDF:（User-Defined-Function）普通函数:  特点是一进一出(输入一行数据输出一行数据)        举例: split
UDAF:（User-Defined Aggregation Function）聚合函数: 特点是多进一出(输入多行输出一行)   举例: count sum max  min  avg
UDTF:（User-Defined Table-Generating Functions）表生成函数:  特点是一进多出(输入一行输出多行)   举例: explode

查询所有hive函数名称:  show functions;
查看某函数使用帮助文档: desc function [extended] 函数名;         注意: 加上extended关键字能查看详细信息示例

示例:

-- 查看所有函数
show functions;

-- 分类标准扩大化
-- UDF: 普通函数 特点: 一进一出  举例: split()
-- 查看官方示例
describe function extended split;
-- 演示官方示例
SELECT split('oneAtwoBthreeCfour', '[ABC]'); -- ["one","two","three","four"]

-- UDAF: 聚合函数 特点: 多进一出 举例: sum() count() avg() max()  min()
-- 查看官方示例
describe function extended count;
/*
count(*): 不忽略null值统计个数
count(字段名): 忽略null值统计个数
count(常量): 举例 : count(1)  count(2)  ...
count(distinct 字段名): 忽略null值并且去重统计个数
*/

-- UDTF: 表生成函数 特点: 一进多哦出 举例: explode()
-- 查看官方示例
describe function extended explode;
/*
将数组a的元素分成多行，或将映射的元素分成多行和多列
数组: array[元素1,元素2,元素3...]   array(10,20,30)能够构造出数组[10,20,30]
映射: map{k1:v1,k2:v2...}  map('a',10,'b',20,'c',30)构造出映射{'a':10,'b':20,'c':30}
*/
-- 演示炸裂函数
select explode(array(10,20,30));
select explode(map('a',10,'b',20,'c',30));

复杂类型函数

知识点:

hive复杂类型:   array  struct  map

array类型: 又叫数组类型,存储同类型的单数据的集合
	 取值: 字段名[索引]   注意: 索引从0开始
	 获取长度的函数: size(字段名)       常用
	 判断是否包含某个数据的函数: array_contains(字段名,某数据)   常用
	 对数组进行排序的函数: sort_array(数组)

struct类型: 又叫结构类型,可以存储不同类型单数据的集合
	 取值: 字段名.子字段名n
	
map类型: 又叫映射类型,存储键值对数据的映射(根据key找value)
	取值: 字段名[key]
	获取长度的函数: size(字段名)        常用       
	获取所有key的函数: map_keys()            常用 
	获取所有value的函数: map_values()        常用

示例:

-- 演示集合函数
select array('binzi','666','888');
select size(array('binzi','666','888'));
select array_contains(array('binzi','666','888'),'binzi');
select sort_array(array(3,1,5,2,4)); -- [1,2,3,4,5]


select map('a',1,'b',2,'c',3);
select size(map('a',1,'b',2,'c',3));
select map_keys(map('a',1,'b',2,'c',3));-- ["a","b","c"]
select map_values(map('a',1,'b',2,'c',3));-- [1,2,3]

字符串函数

知识点:

字符串常见的函数:
concat: 字符串紧凑拼接到一起生成新字符串
concat_ws: 字符串用指定分隔符拼接到一起生成新字符串        常用 
length: 获取字符串长度             常用 
lower: 把字符串中的字母全部变成小写
upper: 把字符串中的字母全部变成大写
trim: 把字符串两端的空白去除         常用 

拓展字符串函数
substr: 截取字符串         常用 
replace: 替换字符串        常用 
regexp_replace: 正则替换字符串
parse_url: 解析url(统一资源定位符)  组成: 协议/主机地址:端口号/资源路径?查询参数
get_json_object: 获取json对象解析对应数据

示例:

-- 1.字符串相关函数
-- 演示字符串常见的函数
-- concat: 字符串紧凑拼接到一起生成新字符串
select concat('binzi', '666', '888'); -- 'binzi666888'
-- concat_ws: 字符串用指定分隔符拼接到一起生成新字符串
select concat_ws('-', 'binzi', '666', '888'); -- 'binzi-666-888'
-- length: 获取字符串长度
select length('binzi-666'); -- 9
-- lower: 把字符串中的字母全部变成小写
select lower('BINZI-666'); -- 'binzi-666'
-- upper: 把字符串中的字母全部变成大写
select upper('binzi-666'); -- 'BINZI-666'
-- trim: 把字符串两端的空白去除
select '   binzi 666  '; -- '   binzi 666   '
select trim('   binzi 666  ');-- 'binzi 666'




-- substr(字符串,开始索引,截取长度): 截取字符串,截取长度如果不写默认到结尾
-- substring(字符串,开始索引,截取长度): 截取字符串
-- 注意: 正索引从1开始正着数  负索引从-1开始倒着数
select substr('binzi666',1,2); -- 'bi'
select substr('binzi666',1); -- -- 'binzi666'
select substr('binzi666',-4);--'i666'

-- 已知'2023-05-21'要求分别截取年月日
select substr('2023-05-21',1,4); -- 结果2023
select substr('2023-05-21',1,7); -- 结果2023-05
select substr('2023-05-21',6,2); -- 结果05
select substr('2023-05-21',-2,2); -- 结果21
-- substr经常用于截取日期中的年月
select `current_date`();
select substr(`current_date`(),1,7);




-- replace(大字符串,敏感词,替换后的内容):替换字符串
select replace('你TMD哦','TMD','挺萌的');
select replace('binzi-666', '666', 'num');

--正则表达式替换函数：regexp_replace(str, regexp, rep)
select regexp_replace('binzi-666', '\\d+', 'num');
--正则表达式解析函数：regexp_extract(str, regexp[, idx])
-- 正则中()代表分组,自动从1开始生成编号,提取正则匹配到的指定组内容
select regexp_extract('binzi-666-888', '(\\d+)-(\\d+)', 1);
select regexp_extract('binzi-666-888', '(\\d+)-(\\d+)', 2);


--URL解析函数：parse_url 注意要想一次解析出多个 可以使用parse_url_tuple这个UDTF函数
-- URL: 统一资源定位符 也就是咱们常说的网址   组成: 协议/主机地址:端口号/资源路径?查询参数
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'HOST'); -- www.itcast.cn
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'PATH'); -- /path/binzi.html
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY'); -- user=binzi&pwd=123
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'user'); -- binzi
select parse_url('http://www.itcast.cn/path/binzi.html?user=binzi&pwd=123', 'QUERY', 'pwd'); -- 123

-- 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"}]', '$.[0]'); -- {"name":"杨过", "age":"18"}
select get_json_object('[{"name":"杨过", "age":"18"}, {"name":"小龙女", "age":"26"}]', '$.[1].name'); -- 小龙女,   $表示json对象

日期时间函数

知识点:

current_timestamp: 获取时间原点到现在的秒/毫秒,底层自动转换方便查看的日期格式        常用 
to_date: 字符串格式时间戳转日期(年月日)
current_date: 获取当前日期(年月日)        常用

year: 获取指定日期时间中的年        常用 
month:获取指定日期时间中的月        常用 
day:获取指定日期时间中的日          常用 
hour:获取指定日期时间中的时
minute:获取指定日期时间中的分
second:获取指定日期时间中的秒

dayofmonth: 获取指定日期时间中的月中第几天
dayofweek:获取指定日期时间中的周中第几天
quarter:获取指定日期时间中的所属季度
weekofyear:获取指定日期时间中的年中第几周

datediff: 获取两个指定时间的差值        常用 
date_add: 在指定日期时间上加几天        常用 
date_sub: 在指定日期时间上减几天

unix_timestamp: 获取unix时间戳(时间原点到现在的秒/毫秒)    注意: 可以使用yyyyMMdd HH:mm:ss进行格式化转换
from_unixtime:  把unix时间戳转换为日期格式的时间          注意: 如果传入的参数是0,获取的是时间原点1970-01-01 00:00:00

示例:

-- 2.日期时间函数
-- 获取当前时间戳(时间原点到现在的秒/毫秒)
select unix_timestamp(); -- 10位的数字代表多少秒
select current_timestamp(); -- 自动转换 年月日时分秒格式
-- 获取当前日期
select current_date();
-- 字符串格式时间戳转日期
select to_date('2023-05-21 11:19:31.222000000');
select to_date(current_timestamp());
-- 依次获取年月日时分秒
select year('2023-05-21 11:19:31.222000000'); -- 2023
select month('2023-05-21 11:19:31.222000000'); -- 5
select day('2023-05-21 11:19:31.222000000'); -- 21

select hour('2023-05-21 11:19:31.222000000'); -- 11
select minute('2023-05-21 11:19:31.222000000'); -- 19
select second('2023-05-21 11:19:31.222000000'); -- 31
-- 依次获取月中第几天,周中第几天,季度,年中第几周
select dayofmonth('2023-05-21 11:19:31.222000000'); -- 21
select dayofweek('2023-05-21 11:19:31.222000000'); -- 1
select quarter('2023-05-21 11:19:31.222000000'); -- 2
select weekofyear('2023-05-21 11:19:31.222000000'); -- 20

-- 计算时间差
select datediff(`current_date`(),'2023-11-03'); -- 12

-- 获取明天的日期
select date_add(current_timestamp(),1);
select date_sub(current_timestamp(),-1);
-- 获取昨天的日期
select date_sub(current_timestamp(),1);
select date_add(current_timestamp(),-1);


-- 拓展
--获取当前UNIX时间戳函数: unix_timestamp
select unix_timestamp();

--字符串日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp("2023-5-21 11:38:56"); -- 1684669136

--指定格式日期转UNIX时间戳函数: unix_timestamp
select unix_timestamp('2023-05-21 11:38:56','yyyy-MM-dd HH:mm:ss'); --1684669136

--UNIX时间戳转日期函数: from_unixtime
select from_unixtime(1684669136); -- 2023-05-21 11:38:56
-- 获取时间原点日期
select from_unixtime(0); -- 1970-01-01 00:00:00

数学函数

round: 指定小数保留位数    常用
rand: 生成0-1的随机数
pi: 生成π结果
ceil: 向上取整
floor: 向下取整

-- 演示数学函数
-- 随机数
select rand();
-- 应用解决数据倾斜问题,可以把之前大量相同的值后面拼接随机数
select concat('男',rand());
select concat('男',rand());

-- 获取π值
select '3.1415926';
select pi();
-- 四舍五入设置保留位数
select round(pi(),4);
-- 向上取整
select ceil(pi());
-- 向下取整
select floor(pi());

条件函数

知识点:

if(参数1,参数2,参数3): 如果参数1结果为true,就执行参数2内容,否则执行参数3的内容
case...when.then...end: 条件判断类似于编程语言中的if..else if ...else...     常用


isnull(数据) : 为空null: true 不为空:false

isnotnull(数据): 不为空: true 为空null:false

nvl(数据,参数2): 如果数据不为空打印数据,为空null打印第二个参数        常用 

coalesce(参数1,参数2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null    常用

示例:

-- 演示条件函数
-- if(条件判断,true的时候执行此处,false的时候执行此处)
select if(10 > 5, '真', '假'); -- 真
select if(10 < 5, '真', '假');
--条件转换函数格式1: CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END
select
       case 7
           when 1 then '周一上班'
           when 2 then '周二上班'
           when 3 then '周三上班'
           when 4 then '周四上班'
           when 5 then '周五上班'
           when 6 then '周六休息'
           when 7 then '周日休息'
           else '老弟啊,你是外星人吗?'
       end;

-- 条件转换函数格式2:CASE  WHEN a==b THEN a==c [WHEN a==d THEN a==e]* [ELSE f] END
select
       case
           when 7==1 then '周一上班'
           when 7==2 then '周二上班'
           when 7==3 then '周三上班'
           when 7==4 then '周四上班'
           when 7==5 then '周五上班'
           when 7==6 then '周六休息'
           when 7==7 then '周日休息'
           else '老弟啊,你是外星人吗?'
       end;


-- 演示null相关函数
-- isnull(数据) 为空: true 不为空:false
select isnull(null); -- true
-- isnotnull(数据) 不为空: true 为空:false
select isnotnull('斌子'); -- true
-- nvl(数据,前面的数据是null的时候执行此处): 如果数据不为空打印数据,为空打印第二个参数
select nvl('binzi','666');
select nvl(null,'666');
-- coalesce(v1,v2...): 从左到右依次查找,返回第一个不是null的值,如果找到最后都是null,就返回null
select COALESCE(null,11,22,33);-- 11
select COALESCE(null,null,22,33);--22
select COALESCE(null,null,null,33);--33
select COALESCE(null,null,null,0);--0
select COALESCE(null,null,null,null);--null

类型转换函数

类型转换: cast(数据 as 要转换的类型)        常用 

-- 演示类型转换函数

-- cast: 主要用于类型转换 注意: 转换失败返回null
select cast(3.14 as int); -- 3
select cast(3.14 as string) ; -- '3.14'
select cast('3.14' as float); -- 3.14
select cast('3.14' as int); -- 3
select cast('binzi' as int); -- null

-- -- 注意: 很多时候底层都默认做了自动转换
select '3'+3; -- 6


-- 实际应用场景:concat_ws要求被连接的必须是字符串,如果直接用666就报错
select concat_ws('_','binzi',666,'888'); --此行报错,因为concat_ws只能拼接字符串类型
select concat_ws('_','binzi',cast(666 as string),'888'); -- binzi_666_888

数据脱敏函数

-- 演示数据脱敏函数[了解]
-- mask_hash:  返回指定字符串的hash编码
select mask_hash('binzi');


-- 拓展
--将查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。
select mask("abc123DEF"); -- xxxnnnXXX
--自定义替换的字母: 依次为大写小写数字
select mask("abc123DEF",'大','小','数');
select mask("abc123DEF",'/','.','%');

--mask_first_n(string str[, int n]
--对前n个进行脱敏替换 大写字母转换为X，小写字母转换为x，数字转换为n。
select mask_first_n("abc123DEF",6);

--mask_last_n(string str[, int n])
--对后n个进行脱敏替换 大写字母转换为X，小写字母转换为x，数字转换为n。
select mask_last_n("abc123DEF",6);

--mask_show_first_n(string str[, int n])
--除了前n个字符，其余进行掩码处理
select mask_show_first_n("abc123DEF",6);

--mask_show_last_n(string str[, int n])
select mask_show_last_n("abc123DEF",6);

其他函数

-- 演示其他函数
--取哈希值函数:hash
select hash("binzi"); -- 93742710

--MD5加密: md5(string/binary)
select md5("binzi"); -- 32位   072853027b387fcf891a610137f8dc1b
select length('072853027b387fcf891a610137f8dc1b');


--SHA-1加密: sha1(string/binary)
select sha1("binzi"); -- 40位 66368c80ca9125f9a8a945aaf1e1ec3f8b21f7f9
select length('66368c80ca9125f9a8a945aaf1e1ec3f8b21f7f9');

--SHA-2家族算法加密：sha2(string/binary, int)  (SHA-224, SHA-256, SHA-384, SHA-512)
select sha2("binzi",224);
select sha2("binzi",512);

--crc32加密:
select crc32("binzi"); -- 3221865747


-- 当前环境相关的
select current_user(),logged_in_user(),current_database(),version();

炸裂函数实战

知识点:

把一个容器的多个数据炸裂出单独展示:  explode(容器)

炸裂函数配合侧视图使用如下
格式:select 原表别名.字段名,侧视图名.字段名 from 原表 原表别名 lateral view explode(要炸开的字段) 侧视图名 as 字段名 ;

示例:

-- UDTF: 一进多出
select explode(array('binzi', '666', '888'));
select explode(map('a', 1, 'b', 2, 'c', 3));

实战

-- 将NBA总冠军球队数据使用explode进行拆分，并且根据夺冠年份进行倒序排序。
--step1:建表
create table the_nba_championship(
           team_name string,
           champion_year array<string>
) row format delimited
fields terminated by ','
collection items terminated by '|';

--step2:加载数据文件到表中  先上传到hdfs/source目录
load data  inpath '/source/The_NBA_Championship.txt' into table the_nba_championship;

--step3:验证
select * from the_nba_championship;


-- 只查询冠军年份,降序排序
select explode(champion_year) as year from the_nba_championship ;

-- 配合侧视图完成需求
with tmp as(
    select  a.team_name,b.year
    from the_nba_championship a
    lateral view explode(champion_year) b as year
    )
select * from tmp order by year desc;

堆内存错误

报错

Error while processing statement: FAILED: Execution Error, return code -101 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask. Java heap space

解决方案:

方式1: 找到hive-env.sh,添加以下内容

	export HADOOP_HEAPSIZE=2048

方式2: 找到hive-site.xml添加以下内容

	<!-- hive堆内存-->
	<property>
        <name>hive.heapsize</name>
        <value>2048</value>
    </property>

高频面试题

行转列

知识点:

collect_set(字段名): 把多个数据收集到一起,默认去重
collect_list(字段名): 把多个数据收集到一起,默认不去重
把多个子串用指定分隔符拼接成一个大字符串: concat_ws(分隔符,多个数据...)      
	注意: 如果拼接数据不是字符串可以使用cast转换

需求:

1、直接拼接
行转列需求1
2、按照时间顺序拼接

需求2

示例:

-- 数据准备
--建表
-- 准备数据
create table a
(
    uid       string,
    content   string,
    send_time int
) row format delimited
    fields terminated by '\t';

--加载数据到表中
load data inpath '/data/a.txt' into table a;

-- 验证数据
select * from a;
/*
需求1: 把原表数据content行转列
a	hello hi
b	thank you I love u,baby see you
*/
-- 直接拼接
select uid, concat_ws(' ', collect_list(content)) as content
from a
group by uid;

/*
需求2: 按照时间顺序拼接
a	hi hello
b	I love u,baby thank you see you
*/
select uid, content
from
    (select uid,
            dense_rank() over (partition by uid order by send_time desc) rk,
            concat_ws(" ", collect_list(content) over (partition by uid order by send_time)) as content
    from a) t1
where rk = 1;

列转行

知识点

把字符串按照指定分隔符切割: split(字符串,分隔符)

炸裂函数配合侧视图使用格式: 
select 原表别名.字段名,
	侧视图名.字段名 
from 原表 原表别名 
lateral view explode(要炸开的字段) 侧视图名 as 字段名 ;

需求

1、直接全散开
需求1
2、按照对应关系散开

需求2-1
需求2-2

示例

-- 列转行
--创建表
create table temptable
(
    uid       string,
    game_list string
) row format delimited
    fields terminated by '|';
-- 加载数据
load data inpath '/data/a.txt' into table temptable;
-- 验证数据
select * from temptable;
-- 需求1
select a.uid,
       b.game
from temptable t
         lateral view explode(split(t.uid, ',')) a as uid
         lateral view explode(split(game_list, ',')) b as game;
         
-- 需求2-1：用posexplode()函数，其可以输出对应的index
-- 注意，此时加入了一个元素，不然不是一一对应关系无法完成
select uid_name, game_name
from stu s
lateral view posexplode(split(s.uid, ',')) a as uid_index, uid_name
lateral view posexplode(split(s.game_list, ',')) b as game_index, game_name
where uid_index = game_index ;

-- 需求2-2
-- 创建表
create table table1(
    column1 string,
    column2 string,
    column3 string,
    X1 int,
    X2 int,
    X3 int,
    X4 int
)row format delimited
fields terminated by '|';
-- 加载数据
load data inpath '/data/a.txt' into table table1;
-- 检测数据
select * from table1;
select column1, column2, column3, map('x1',x1,'x2',x2,'x3',x3,'x4',x4) as map1
from table1;
-- 列转行
select *
from (select column1, column2, column3, map('x1',x1,'x2',x2,'x3',x3,'x4',x4) as map1
from table1) t
lateral view explode(map1) a as k, v;

JSON文件处理

知识点:

get_json_object: 获取json对象解析对应数据  一次只能提取一个字段

json_tuple: 直接获取json对应数据  这是一个UDTF函数 可以一次解析提取多个字段

注意: 因为json_tuple是UDTF函数,所以也可以配合侧视图使用

示例:

-- 演示json解析
-- 需求: 把json解析后的数据保存成一个新表
--创建表
create table tb_json_test1 (
    json string
);

--加载数据
load data  inpath '/source/device.json' into table tb_json_test1;
-- 查看数据
select * from tb_json_test1;

-- 方式1: 逐个(字段)处理, get_json_object UDF函数 最大弊端是一次只能解析提取一个字段
-- get_json_object UDF函数 最大弊端是一次只能解析提取一个字段
create table device1 as
select
    --获取设备名称
    get_json_object(json,"$.device") as device,
    --获取设备类型
    get_json_object(json,"$.deviceType") as deviceType,
    --获取设备信号强度
    get_json_object(json,"$.signal") as signal,
    --获取时间
    get_json_object(json,"$.time") as stime
from tb_json_test1;


-- 方式2: 逐条处理. json_tuple 这是一个UDTF函数 可以一次解析提取多个字段
--json_tuple 这是一个UDTF函数 可以一次解析提取多个字段
--单独使用 解析所有字段
create table device2 as
select
    json_tuple(json,"device","deviceType","signal","time") as (device,deviceType,signal,stime)
from tb_json_test1;

--搭配侧视图使用(本次了解)
select
    device,deviceType,signal,stime
from tb_json_test1
         lateral view json_tuple(json,"device","deviceType","signal","time") b
         as device,deviceType,signal,stime;


-- 方式3: 在建表时候, 直接处理json, row format SerDe '能处理Json的SerDe类'
--建表的时候直接使用JsonSerDe解析
create table tb_json_test2 (
                               device string,
                               deviceType string,
                               signal double,
                               `time` string
)ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe' STORED AS TEXTFILE;
-- 加载数据
load data  inpath '/source/device.json' into table tb_json_test2;
-- 查看
select * from tb_json_test2;

开窗函数

基础使用

基础知识点

开窗函数格式:  select ... 开窗函数 over(partition by 分组字段名 order by 排序字段名 asc|desc) ... from 表名;

聚合开窗函数: 原来学的聚合函数(max,min,sum,count,avg)配合over()使用的时候,这些聚合函数也可以叫开窗函数

排序开窗函数: row_number  dense_rank  rank
            row_number: 巧记: 1234   特点: 唯一且连续
            dense_rank: 巧记: 1223   特点: 并列且连续
               rank   : 巧记: 1224   特点: 并列不连续

-- 开窗函数: hive和mysql8都能使用
-- 开窗函数本质在表后新增了一列
-- 聚合开窗函数: max min sum avg count
-- 聚合函数配合over()使用,也可以叫开窗函数
select col1,
       max(col3) over()
from row2col2;


-- 排序开窗函数: row_number  rank  dense_rank
-- 排序函数必须配合over(order by 排序字段 asc|desc)
/*
row_number: 巧记: 1234   特点: 唯一且连续
dense_rank: 巧记: 1223   特点: 并列且连续
   rank   : 巧记: 1224   特点: 并列不连续
*/
select *,
       row_number() over (order by signal desc),
       dense_rank() over (order by signal desc),
       rank() over (order by signal desc)
from device1;

-- 开窗函数分组
-- 注意不能用group by ,需要使用partition by,可以理解成partition by是group by的子句
-- 演示排序函数和分组配合使用: 先分组再组内排序
select *,
       row_number() over (partition by deviceType order by signal desc),
       dense_rank() over (partition by deviceType order by signal desc),
       rank() over (partition by deviceType order by signal desc)
from device1;
-- 演示聚合函数和分组配合使用
select *,
       max(signal) over(partition by deviceType)
from device1;


-- 演示聚合函数同时和分组以及排序关键字配合使用
--需求：求出每个用户截止到当天，累积的总pv数
---建表并且加载数据
create table website_pv_info(
   cookieid string,
   createtime string,   --day
   pv int
) row format delimited
fields terminated by ',';
-- 建表
create table website_url_info (
    cookieid string,
    createtime string,  --访问时间
    url string       --访问页面
) row format delimited
fields terminated by ',';
-- 加载数据  直接上传website_pv_info.txt和website_url_info.txt到hdfs中指定表路径中
-- 查询数据
select * from website_pv_info;
select * from website_url_info;

--需求：求出每个用户截止到当天，累积的总pv数
--sum(...) over( partition by... order by ... )，在每个分组内，连续累积求和
select cookieid, createtime,  pv,
       sum(pv) over(partition by cookieid order by createtime) as current_total_pv
from website_pv_info;

开窗函数控制范围

开窗函数控制范围: rows between
					- unbounded: 无界限
                    - x preceding:往前x行
                    - x following:往后x行
                    - current row:当前行
       
                    - unbounded preceding :表示从前面的起点  第一行
                    - unbounded following :表示到后面的终点  最后一行         

-- 演示窗口范围的控制
/*
rows between
	- preceding：往前
	- following：往后
	- current row：当前行
	- unbounded：起点
	- unbounded preceding 表示从前面的起点  第一行
	- unbounded following：表示到后面的终点  最后一行
*/
--默认从第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime) as pv1
from website_pv_info;

--第一行到当前行 等效于rows between不写 默认就是第一行到当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
from website_pv_info;


--向前3行至当前行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
from website_pv_info;

--向前3行 向后1行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
from website_pv_info;

--当前行至最后一行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
from website_pv_info;

--第一行到最后一行 也就是分组内的所有行
select cookieid,createtime,pv,
       sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding  and unbounded following) as pv6
from website_pv_info;

其他开窗函数

其他开窗函数: ntile   lag和lead   first_value和last_value

ntile(x)功能: 将分组排序之后的数据分成指定的x个部分（x个桶）   
		注意ntile规则:尽量平均分配 ，优先满足最小(编号1)的桶，彼此最多不相差1个。

lag: 用于统计窗口内往上第n行值
lead:用于统计窗口内往下第n行值

first_value: 取分组内排序后，截止到当前行，第一个值
last_value : 取分组内排序后，截止到当前行，最后一个值

注意: 窗口函数结果都是单独生成一列存储对应数据

-- 演示其他函数
-- 演示ntile
--把每个分组内的数据分为3桶
SELECT
    cookieid,
    createtime,
    pv,
    ntile(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2
FROM website_pv_info
ORDER BY cookieid,createtime;

--需求：统计每个用户pv数最多的前3分之1天。
--理解：将数据根据cookieid分 根据pv倒序排序 排序之后分为3个部分 取第一部分
SELECT * from
(SELECT
     cookieid,
     createtime,
     pv,
     NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn
 FROM website_pv_info) tmp where rn =1;




--lag 用于统计窗口内往上第n行值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    lag(createtime, 1) over (partition by cookieid order by createtime) la1,
    lag(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;


--lead 用于统计窗口内往下第n行值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    lead(createtime, 1) over (partition by cookieid order by createtime) la1,
    lead(createtime, 2, '2000-01-01 00:00:00') over (partition by cookieid order by createtime) la2
from website_url_info;


--FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    first_value(url) over (partition by cookieid order by createtime) fv
from website_url_info;


--LAST_VALUE  取分组内排序后，截止到当前行，最后一个值
select cookieid, createtime, url,
    row_number() over (partition by cookieid order by createtime) rn,
    last_value(url) over (partition by cookieid order by createtime rows between unbounded preceding and unbounded following) fv
from website_url_info;

往下第n行值
select cookieid, createtime, url,
row_number() over (partition by cookieid order by createtime) rn,
lead(createtime, 1) over (partition by cookieid order by createtime) la1,
lead(createtime, 2, ‘2000-01-01 00:00:00’) over (partition by cookieid order by createtime) la2
from website_url_info;

–FIRST_VALUE 取分组内排序后，截止到当前行，第一个值
select cookieid, createtime, url,
row_number() over (partition by cookieid order by createtime) rn,
first_value(url) over (partition by cookieid order by createtime) fv
from website_url_info;

–LAST_VALUE 取分组内排序后，截止到当前行，最后一个值
select cookieid, createtime, url,
row_number() over (partition by cookieid order by createtime) rn,
last_value(url) over (partition by cookieid order by createtime rows between unbounded preceding and unbounded following) fv
from website_url_info;
早安Naor
关注
  • 6
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
hive窗口函数
01-09
hive窗口系列函数
Hive自定义函数
01-20
Hive自定义函数 一. UDF(user defined function) 背景 系统内置函数无法解决所有的实际业务问题，需要开发者自己编写函数实现自身的业务实现诉求。 应用场景非常多，面临的业务不同导致个性化实现很多，故udf...
Hive 常用函数
长不大的大灰狼
03-08 1533
Hive 常用函数
HIVE自定义函数
asdsdsd25的博客
05-06 1617
Hive自定义函数 一.UDF(userdefinedfunction) 背景 系统内置函数无法解决所有的实际业务问题，需要开发者自己编写函数实现自身的业务实现诉求。 应用场景非常多，面临的业务不同导致个性化实现很多，故udf很需要。 意义 函数扩展得到解决，极大丰富了可定制化的业务需求。 IO要求-要解决的问题 in:out=1:1,只能输入一条记录当中的数据，同时返回一条处理结果。 属于最常见的自定义函数，像cos,sin,sub...
hive自定义函数
kiritobryant的博客
11-12 3452
hive的udf有两种实现方式或者实现的API，一种是udf比较简单，一种是GenericUDF比较复杂。如果所操作的数据类型都是基础数据类型，如（Hadoop&Hive 基本writable类型，如Text,IntWritable,LongWriable,DoubleWritable等等）。那么简单的org.apache.hadoop.hive.ql.exec.UDF就可以做到。
Hive常用函数大全
weixin_45425565的博客
09-09 4689
Hive常用函数大全
Hive常用函数 - abs
Kingsley's Blog
03-21 3387
hive常用函数知识点
Hive 内置函数
扛麻袋的少年的博客
06-07 4482
九、Hive 内置函数 1.nvl 2.case when then else end 3.concat 4.concat_ws 5.collect_set 6.collect_list 7.explode + lateral view侧写表 8.grouping sets 多维分析 9.over 开窗函数 10.rank 11.dense_rank 12.row_number
Hive常用函数
weixin_46011754的博客
12-01 2395
行转列列转行 1：行转列 将多行的数据合并到一列中 select collect_list(empname ) from emp; --去重 select collect_set(deptno) from emp; 这两个都是聚合函数 将收集的多行数据聚集成一个数组集合 2：concat 拼接 参数是可变参数 拼接字符串 select concat(deptno,empno) from emp; 3：concat_ws 参数一 拼接符 参数二 可变个数的字符串 数组 sel
Hive常用函数大全一览
一个数据小开发的博客
08-10 1507
1、关系运算 1、等值比较: = 语法：A=B操作类型：所有基本类型描述: 如果表达式A与表达式B相等，则为TRUE；否则为FALSE hive> select 1 from iteblog where 1=1; 1 2、不等值比较: <> 语法: A <> B操作类型: 所有基本类型描述: 如果表达式A为NULL，或者表达式B为NULL，返回NULL；如果表达式A与表达式B不相等，则为TRUE；否则为FALSE hive>
Hive 窗口函数
01-07
over()：指定分析函数工作的数据窗口的大小，这个数据窗口大小会随着行的变而变化。over跟在聚合函数后面，只对聚合函数生效。 current row：当前行 n preceding：往前n行数据 n following：往后n行数据 unbounded：...
hive常用函数
04-17
hive常用函数，包括时间、类型、udf、udaf等等的归纳。
hive窗口函数.docx
07-08
数据蛙hive窗口函数 - 精心总结
五、Listen
最新发布
Alyson_jm的博客
05-21 193
创建一个新的 Customer 对象: com.test.Customer@517b372b, time: Tue Apr 21 12:27:47 CST 2020, 并放入到 session 中。HttpSessionBindingListener:监听实现了该接口的 Java 类的对象被绑定到 session 或从 session 中解除绑定的事件。从执行结果来看，首先调用的方法是绑定到Session,然后关闭浏览器，调用sessionWillPassivate方法，最后解除绑定.从内存中写到磁盘上…
【Hive SQL 每日一题】统计最近7天内连续下单3日的用户量
JIE的博客 --- moon_coder
05-18 578
通过上面的规律可以发现，如果日期是连续的，那么当它们减去一串顺序的数字后，得到的结果是一致的，我们可以利用这一规则，判断日期是否连续。在这个需求中，并不关心用户购买了多少商品，在意的是用户是否进行了下单，同天下单多次的用户也只算作一次。日内满足多次该条件，但是在这个需求中，每个用户只能算一次，所以还需要去重才能得到最终结果。其实很简单，我们可以通过开窗的方式，计算日期是否连续，我这里是通过打标记来实现的。那么现在来说说这个需求是如何实现的，通过如下。现在得到的结果并不是最终的结果，因为某个用户（
计算机毕业设计PyFlink+Spark+Hive民宿推荐系统 酒店推荐系统 民宿酒店数据分析可视化大屏 民宿爬虫 民宿大数据 知识图谱 机器学习
从业计算机毕业设计10年，打算用一年时间分享10年毕设经验！
05-19 774
计算机毕业设计PyFlink+Spark+Hive民宿推荐系统 酒店推荐系统 民宿酒店数据分析可视化大屏 民宿爬虫 民宿大数据 知识图谱 机器学习
Hive的字符串操作
早拾碗吧的博客
05-17 295
【代码】Hive的字符串操作。
csdn hive 窗口函数
01-13
CSND Hive窗口函数是Hive数据库中用于处理分组数据的一种强大工具。在数据仓库中，通常需要对数据进行分组计算和分析，而Hive窗口函数可以帮助我们实现这些操作。窗口函数可以在数据分组的基础上对分组数据进行排序...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

早安Naor

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值