一、列转行

行转列上次分享过了，主要是用 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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
二、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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
三、开窗函数
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


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
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个值。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
四、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;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- with语句用法 ,其实就是和建临时表差不多的意思，最后把这些临时表串起来，挺简单的。
/*
with t1 as(
select 语句
), -- 这里不加逗号
t2 as (
select 语句
), -- 这里加逗号
t3 as (
select 语句
) -- 这里不加逗号
select
*
from t1 left join t2 on 条件
left jon t3 on 条件 ; -- 这里加分号

*/

免责申明：

本文系转载，版权归原作者所有，如若侵权请联系我们进行删除！

《数据治理行业实践白皮书》下载地址：https://fs80.cn/4w2atu

《数栈V6.0产品白皮书》下载地址：https://fs80.cn/cw0iw1

想了解或咨询更多有关袋鼠云大数据产品、行业解决方案、客户案例的朋友，浏览袋鼠云官网：https://www.dtstack.com/?src=bbs

同时，欢迎对大数据开源项目有兴趣的同学加入「袋鼠云开源框架钉钉技术群」，交流最新开源技术信息，群号码：30537511，项目地址：https://github.com/DTStack