Hive的表操作5

Hive系列

注：大家觉得博客好的话，别忘了点赞收藏，本人每周都会更新关于人工智能和大数据相关的内容，内容多为原创，Python Java Scala SQL 代码，CV NLP 推荐系统等，Spark Flink Kafka Hbase Hive Flume等等~写的都是纯干货，各种顶会的论文解读，一起进步。
今天继续和大家分享一下Hive的表操作
#博学谷IT学习技术支持

---

---

前言

1、Hive是数仓管理工具,用来管理数仓
2、Hive可以将数仓存在HDFS上的文件变成一张张的表
3、Hive提供一种HiveSQL可以表进行分析处理
4、HiveSQL底层默认是MapReduce，以后可以换成其他的引擎（Spark），我们写HiveSQL会去匹配底层的MR模板，匹配上则执行，否则不能执行

---

一、列转行

行转列上次分享过了，主要是用 collect_set(去重)/collect_list(不去重)+group by
今天继续讲列转行，主要是用爆炸函数lateral view explode()

--准备数据
  10      CLARK|KING|MILLER|SCOTT|KING
  20      SMITH|CLARK|JONES|SCOTT|ADAMS|FORD
  30      ALLEN|WARD|CLARK|MARTIN|BLAKE|TURNER|JAMES
  
-- 创建表
  create table myhive.emp2(
  deptno int,
  names array<string>
  )
  row format delimited fields terminated by '\t'
  collection items terminated by '|';
  
-- 加载数据
  load data  local inpath '/export/data/hivedatas/emp2.txt' overwrite into table myhive.emp2;

-- lateral view是一个侧视图关键字，后边一般跟UDTF函数（一进多出函数）
-- 格式： select 字段 from 原表  lateral view  UDTF函数
  select deptno,name   from emp2 lateral view explode(names) tmp_tb as name;

二、Reflect函数

该函数可以调用Java中的静态方法，必须是静态的，而且是JDK自带的

案例1：找出两列的最大值
--创建hive表
create table test_reflect(col1 int,col2 int) row format delimited fields terminated by ',';

--准备数据 test_reflect.txt
1,2
4,3
6,4
7,5
5,6

--加载数据
load data local inpath '/export/data/hivedatas/test_reflect.txt'  into table test_udf;

--使用java.lang.Math当中的Max求两列当中的最大值
select reflect("java.lang.Math","max",col1,col2) from test_udf;

--案例2 在student表中给sid前面拼接随机数
select concat(reflect('java.util.UUID','randomUUID'),'-',sid) ,* from student;

三、开窗函数

1.分组排序函数(RANK,DENSE_RANK,ROW_NUMBER)

--数据准备
user1,2018-04-10,1
user1,2018-04-11,5
user2,2018-04-14,3
user1,2018-04-12,7
user2,2018-04-11,3
user1,2018-04-13,3
user1,2018-04-16,4
user2,2018-04-10,2
user2,2018-04-12,5
user1,2018-04-14,2
user2,2018-04-13,6
user2,2018-04-15,9
user1,2018-04-15,4
user2,2018-04-16,7
 
CREATE TABLE test_window_func1(
userid string,
createtime string,   --day 
pv INT
) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
  
-- 加载数据：
load data local inpath '/export/data/hivedatas/test1.txt' into table test_window_func1;

SELECT 
	*,
	RANK() OVER(PARTITION BY userid ORDER BY pv desc) AS rn1,
	DENSE_RANK() OVER(PARTITION BY userid ORDER BY pv desc) AS rn2,
	ROW_NUMBER() OVER(PARTITION BY userid ORDER BY pv DESC) AS rn3 
FROM test_window_func1 

2.聚合统计开窗函数(sum,avg,max,min)

这里以sum为例，其他用法都一样

-- 默认从开头累加到当前行
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime) as pv1
  from test_window_func1;
  
-- 从开头累加到当前行
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime rows between unbounded preceding and current row) as pv2
  from test_window_func1;

--如果没有order  by排序语句  默认把分组内的所有数据进行sum操作
  select userid,createtime,pv,
  sum(pv) over(partition by userid) as pv3
  from test_window_func1;
  
--  从当前行向上推三行 累加 到当前行
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime rows between 3 preceding and current row) as pv4
  from test_window_func1;
  
--  从当前行向上推三行 累加 到当前行的下一行
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime rows between 3 preceding and 1 following) as pv5
  from test_window_func1;
  
-- 从当前行累加到最后
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime rows between current row and unbounded following) as pv6
  from test_window_func1;
  
-- 从上一行 累加到最后
  select userid,createtime,pv,
  sum(pv) over(partition by userid order by createtime rows between 1 preceding and unbounded following) as pv6
  from test_window_func1;

3.常用的开窗函数

用法和我之前分享的MySql的开窗函数用法基本一样。感兴趣的可以参考一下，有详细的案例说明
https://blog.csdn.net/weixin_53280379/article/details/126087929?spm=1001.2014.3001.5501

  -- 聚合开窗函数
  count(); 	-- 窗口内总条数
  sum();		-- 窗口内数据的和
  min();		-- 窗口内最小值
  max();		-- 窗口内最大值
  avg();		-- 窗口内的平均值
  -- 排序开窗函数
  row_number();	-- 从1开始，按照顺序，生成分组内记录的序列
  rank();			-- 生成数据项在分组中的排名，排名相等会在名次中留下空位
  dense_rank();	-- 生成数据项在分组中的排名，排名相等会在名次中不会留下空位
  ntile(n);		-- 将分区中已排序的行划分为大小尽可能相等的指定数量的排名的组，
  				-- 并返回给定行所在的组的排名。
  percent_rank(); -- 计算给定行的百分比排名。可以用来计算超过了百分之多少的人。(当前行的rank值-1)/(分组内的总行数-1)
  				-- 如360小助手开机速度超过了百分之多少的人。
  cume_dist();	-- 计算某个窗口或分区中某个值的累积分布。假定升序排序，则使用以下公式确定累积分布：
  				-- 小于等于当前值x的行数 / 窗口或partition分区内的总行数。其中，x 等于 order by 子句中指定的列的当前行中的值。
  -- 其他窗口函数
  FIRST_VALUE();		-- 返回分区中的第一个值。
  LAST_VALUE();		-- 返回分区中的最后一个值。
  LAG(col,n,default);	-- 用于统计窗口内往上第n个值。	
  LEAD(col,n,default);-- 用于统计窗口内往下第n个值。

四、With语句

with 语句主要解决子查询嵌套太多，代码可读性太差的问题。
ps:有的公司领导对代码的整洁性和可读性要求特别高，用with语句可以装一下。都懂的。。。
自己不会写，P事还特别多，我就多嵌套几个子查询怎么了？哈哈。。。开个玩笑

-- 子查询方式，子查询包裹的表必须有别名，否则报错。
 select * from
 (
     select
       *,
       dense_rank() over (partition by  userid order by pv desc) rk
     from test_window_func1
 )t
 where rk <=3;

--用with语句代替，效果一样
with t as
 (
     select
       *,
       dense_rank() over (partition by  userid order by pv desc) rk
     from test_window_func1
 )
select * from t where rk <=3;

 -- with语句用法 ,其实就是和建临时表差不多的意思，最后把这些临时表串起来，挺简单的。
  /*
  with t1 as(
    select 语句
  ),    -- 这里不加逗号 
  t2 as (
    select 语句
  ),      -- 这里加逗号 
  t3 as (
    select 语句
  )       -- 这里不加逗号 
  select
  * 
  from t1 left join t2 on 条件
          left jon  t3 on 条件 ;    -- 这里加分号  
  
  */
  

---

总结

今天继续和大家分享一下Hive的表操作5。