大数据03--Hive04 行列转换，窗口函数，Rank

粥ou

已于 2022-10-01 16:51:49 修改

阅读量152

点赞数

分类专栏：大数据学习文章标签： sql 数据库

于 2022-09-28 21:46:19 首次发布

大数据学习专栏收录该内容

13 篇文章 1 订阅

订阅专栏

UDF 一进一出可无限嵌套

UDA（Aggregation）F 聚合函数，多进一出如 sum ()

UDT（Generating）F 一进多出如 lateral view explode()

orc的存储格式+snappy的压缩格式

hive相当于是Hadoop的一个客户端,存数据依赖于hdfs,运行依赖mr

常用内置函数

NVL：给值为 NULL 的数据赋值 NVL( value，default_value)

如果 value 为 NULL，则 NVL 函数返回 default_value 的值，否则返回 value 的值，如果两个参数都为 NULL ，则返回 NULL。

emp表：

查询：如果员工的 sal 为 NULL，则用领导 id 代替

select ename,nvl(sal,mgr) from emp;
OK
ename	_c1
SMITH	800.0
ALLEN	1600.0
WARD	1250.0
JONES	2975.0
MARTIN	1250.0
BLAKE	2850.0
CLARK	2450.0
SCOTT	3000.0
KING	10.0
TURNER	1500.0
ADAMS	1100.0
JAMES	950.0
FORD	3000.0
MILLER	1300.0

UDF:一行->一行可任意嵌套

CASE WHEN THEN ELSE END 有点类似于三元表达式

也可以做多个when then

case a when b then c when d then e when f then g else h end

数据准备

select * from emp_sex;
OK
emp_sex.name	emp_sex.dept_id	emp_sex.sex
悟空	A	男
大海	A	男
宋宋	B	男
凤姐	A	女
婷姐	B	女
婷婷	B	女

hive (default)> select
              > dept_id,
              > sum(case sex when '男' then 1 else 0 end) malecount,
              > sum(case sex when '女' then 1 else 0 end) femalecount
              > from
              > emp_sex
              > group by dept_id;


dept_id	malecount	femalecount
A	2	1
B	1	2

也可以用if 语句 if(条件，value1,value2) 也可以嵌套做多个分支（if(if(条件，value1,value2)，value3,value4))

select dept_id,sum(if(sex='男',1,0)) malecount,sum(if(sex='女',1,0)) femalecoount from emp_sex group by dept_id;

行转列将多行的内容转换为一个列单元内容

相关函数说明

CONCAT(string A/col, string B/col … ) ：返回输入字符串连接后的结果，支持任意个输入字

符串 ;

CONCAT_WS(separator, str1, str2,...)：第一个参数剩余参数间的分隔符

CONCAT_WS must be "string or array<string>

COLLECT_SET(col)：只接受基本数据类型

将某字段的值进行去重汇总，产生 Array 类型字段

1.Hive不允许直接访问非group by字段；
2.对于非group by字段，可以用Hive的collect_set函数(这也是UDAF函数）收集这些字段，返回一个数组；

数据准备

select * from person_info;
OK
person_info.name	person_info.constellation	person_info.blood_type
孙悟空	白羊座	A
大海	射手座	A
宋宋	白羊座	B
猪八戒	白羊座	A
凤姐	射手座	A
苍老师	白羊座	B

将星座和血型拼接再一起
select name,concat_ws(',',constellation,blood_type) c_b from person_info;
OK
name	c_b
孙悟空	白羊座,A
大海	射手座,A
宋宋	白羊座,B
猪八戒	白羊座,A
凤姐	射手座,A
苍老师	白羊座,B

select t1.c_b, concat_ws('|',collect_set(t1.name)) name from(select name,concat_ws(',',constellation,blood_type) c_b from person_info) t1 group by t1.c_b;

t1.c_b	name
射手座,A	大海|凤姐
白羊座,A	孙悟空|猪八戒
白羊座,B	宋宋|苍老师

列转行将一个列单元的数据转为多行

函数说明

EXPLODE(col) ：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。

LATERAL VIEW LATERAL VIEW udtf(expression) tableAlias AS columnAlias

用于和 split, explode 等 UDTF 一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。后面As 为炸裂出来字段的别名

数据准备

select * from movie_info;
OK
movie_info.movie	movie_info.category
《疑犯追踪》	悬疑,动作,科幻,剧情

这里用lateral view 是为了和原表能产生关联

select movie,category_name from movie_info lateral view explode(split(category,',')) movie_info as category_name;
OK
movie	category_name
《疑犯追踪》	悬疑
《疑犯追踪》	动作
《疑犯追踪》	科幻
《疑犯追踪》	剧情

窗口函数（开窗函数）可理解为高级的group by

一行对应一个窗口，不加条件时，每个窗口对应所有行，对聚合函数生效

相关函数说明

OVER()：指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变而变化。

CURRENT ROW：当前行 n PRECEDING ：往前 n 行数据 n FOLLOWING ：往后 n 行数据

UNBOUNDED：起点， UNBOUNDED PRECEDING 表示从前面的起点，

UNBOUNDED FOLLOWING 表示到后面的终点

LAG(col,n,default_val) ：往前第 n 行数据 LEAD(col,n, default_val) ：往后第 n 行数据

NTILE(n) ：把有序窗口的行分发到指定数据的组中，各个组有编号，编号从 1 开始，对

于每一行， NTILE 返回此行所属的组的编号。注意： n 必须为 int 类型。

数据准备

select * from business;
OK
business.name	business.orderdate	business.cost
jack	2017-01-01	10
tony	2017-01-02	15
jack	2017-02-03	23
tony	2017-01-04	29
jack	2017-01-05	46
jack	2017-04-06	42
tony	2017-01-07	50
jack	2017-01-08	55
mart	2017-04-08	62
mart	2017-04-09	68
neil	2017-05-10	12
mart	2017-04-11	75
neil	2017-06-12	80
mart	2017-04-13	94
Time taken: 0.151 seconds, Fetched: 14 row(s)

（1）查询在 2017 年 4 月份购买过的顾客及总人数这里的总人数就是指有几个人

不加窗口函数
select name,count(*) from business where substring(orderdate,1,7)='2017-04' group by name;
name	_c1
jack	1
mart	4

会显示每个人4月份来的次数，而并不是总人数
加窗口函数
select name,count(*) over() from business where substring(orderdate,1,7)='2017-04' group by name;
name	count_window_0
mart	2
jack	2

（2）查询顾客的购买明细及月购买总额（就是每个月的总额，总体的总额）

select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from business;

name	orderdate	cost	sum_window_0
jack	2017-01-01	10	205
jack	2017-01-08	55	205
tony	2017-01-07	50	205
jack	2017-01-05	46	205
tony	2017-01-04	29	205
tony	2017-01-02	15	205
jack	2017-02-03	23	23
mart	2017-04-13	94	341
jack	2017-04-06	42	341
mart	2017-04-11	75	341
mart	2017-04-09	68	341
mart	2017-04-08	62	341
neil	2017-05-10	12	12
neil	2017-06-12	80	80
Time taken: 17.591 seconds, Fetched: 14 row(s)

（3）将每个顾客的 cost 按照日期进行累加

select name,orderdate,cost,sum(cost) over() as sample1,
sum(cost) over(partition by name) as sample2,
sum(cost) over(partition by name order by orderdate) as sample3,
sum(cost) over(partition by name order by orderdate rows between unbounded preceding and current row) as sample4 
from business;
name	orderdate	cost	sample1	sample2	sample3	sample4
jack	2017-01-01	10	661	176	10	10
jack	2017-01-05	46	661	176	56	56
jack	2017-01-08	55	661	176	111	111
jack	2017-02-03	23	661	176	134	134
jack	2017-04-06	42	661	176	176	176
mart	2017-04-08	62	661	299	62	62
mart	2017-04-09	68	661	299	130	130
mart	2017-04-11	75	661	299	205	205
mart	2017-04-13	94	661	299	299	299
neil	2017-05-10	12	661	92	12	12
neil	2017-06-12	80	661	92	92	92
tony	2017-01-02	15	661	94	15	15
tony	2017-01-04	29	661	94	44	44
tony	2017-01-07	50	661	94	94	94
Time taken: 67.901 seconds, Fetched: 14 row(s)

sample1 没加任何条件，是所有行相加的结果
sample2 按照name分组，组内数据相加
sample3 按照name分组，组内数据相加 但是是根据orderdate排序后的数据 没加条件就默认从组内起点到 
        当前点
sample4 和sample3一样

select name,orderdate,cost,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and current row) as sample5,
sum(cost) over(partition by name order by orderdate rows between 1 preceding and 1 following) as sample6,
sum(cost) over(partition by name order by orderdate rows between current row and unbounded following) as sample7 
from business;

name	orderdate	cost	sample5	sample6	sample7
jack	2017-01-01	10	10	56	176
jack	2017-01-05	46	56	111	166
jack	2017-01-08	55	101	124	120
jack	2017-02-03	23	78	120	65
jack	2017-04-06	42	65	65	42
mart	2017-04-08	62	62	130	299
mart	2017-04-09	68	130	205	237
mart	2017-04-11	75	143	237	169
mart	2017-04-13	94	169	169	94
neil	2017-05-10	12	12	92	92
neil	2017-06-12	80	92	92	80
tony	2017-01-02	15	15	44	94
tony	2017-01-04	29	44	94	79
tony	2017-01-07	50	79	79	50

sample5 是前面一行和当前行数据相加
sample6 是前面一行和当前行以及后面一行相加
sample7 是从当前到到最后一行相加

rows 必须跟在 order by 子句之后，对排序的结果进行限制

（4）查看顾客上次的购买时间

select name,orderdate,cost,lag(orderdate,1,'2020-20-20') over(partition by name order by orderdate) pre_time,
LEAD(orderdate,1,'2020-02-10') over(partition by name order by orderdate) flo_time
from business;

name	orderdate	cost	pre_time	flo_time
jack	2017-01-01	10	2020-20-20	2017-01-05
jack	2017-01-05	46	2017-01-01	2017-01-08
jack	2017-01-08	55	2017-01-05	2017-02-03
jack	2017-02-03	23	2017-01-08	2017-04-06
jack	2017-04-06	42	2017-02-03	2020-02-10
mart	2017-04-08	62	2020-20-20	2017-04-09
mart	2017-04-09	68	2017-04-08	2017-04-11
mart	2017-04-11	75	2017-04-09	2017-04-13
mart	2017-04-13	94	2017-04-11	2020-02-10
neil	2017-05-10	12	2020-20-20	2017-06-12
neil	2017-06-12	80	2017-05-10	2020-02-10
tony	2017-01-02	15	2020-20-20	2017-01-04
tony	2017-01-04	29	2017-01-02	2017-01-07
tony	2017-01-07	50	2017-01-04	2020-02-10
Time taken: 17.318 seconds, Fetched: 14 row(s)

（5）查询前 20% 时间的订单信息

select * from(select name,orderdate,cost,ntile(5) over(order by orderdate) sorted from business) t where sorted=1;

t.name	t.orderdate	t.cost	t.sorted
jack	2017-01-01	10	1
tony	2017-01-02	15	1
tony	2017-01-04	29	1
Time taken: 18.186 seconds, Fetched: 3 row(s)


将14组数据分为了5组 按照order顺序 做个子查询，查询第一个

Rank

函数说明

RANK() 排序相同时 会重复 ， 总数不会变

DENSE_RANK() 排序相同时 会重复 ， 总数会减少

ROW_NUMBER() 会根据顺序计算

数据准备

score.name	score.subject	score.score
孙悟空	语文	87
孙悟空	数学	95
孙悟空	英语	68
大海	语文	94
大海	数学	56
大海	英语	84
宋宋	语文	64
宋宋	数学	86
宋宋	英语	84
婷婷	语文	65
婷婷	数学	85
婷婷	英语	78

select name,subject,score,rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) rp,
row_number() over(partition by subject order by score desc) rp
from score;

name	subject	score	rp	rp	rp
孙悟空	数学	95	1	1	1
宋宋	数学	86	2	2	2
婷婷	数学	85	3	3	3
大海	数学	56	4	4	4
宋宋	英语	84	1	1	1
大海	英语	84	1	1	2
婷婷	英语	78	3	2	3
孙悟空	英语	68	4	3	4
大海	语文	94	1	1	1
孙悟空	语文	87	2	2	2
婷婷	语文	65	3	3	3
宋宋	语文	64	4	4	4

这里求完后，可以根据rank() 别名来取想要的内容（类似where rk<=3等等），不过要嵌套一个子查询（因为执行的顺序）