hive常用查询函数之列转行、窗口函数、排名Rank

最新推荐文章于 2023-09-13 15:22:07 发布

我是超级小白

最新推荐文章于 2023-09-13 15:22:07 发布

阅读量307

点赞数

本文链接：https://blog.youkuaiyun.com/lady88888888/article/details/108394069

版权

本文介绍了Hive中的两种高级查询技巧：如何使用Lateral View进行列转行操作，以及如何利用窗口函数进行复杂的数据分析，特别是Rank函数的用法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

列转行

使用的函数

	explode(clo)
	
		将一列复杂的array或者map结构拆分为多行
	LATERAL VIEW
	
		为侧视图，意义就是为了配合UDTF来使用，把某一行数据拆分成多行数据，不加lateral view的UDTF只能提取单个字段拆分,并不能塞会原来数据表中.加上lateral view就可以将拆分的单个字段数据与原始表数据关联上.

在使用lateral view的时候需要指定视图别名和生成的新列别名
select
movie,
category_name
from
movie_info lateral view explode(category) table_tmp as category_name;

窗口函数

相关的函数：
	over()
		指定分析函数工作的数据窗口大小

窗口函数通常是分析人员使用 hive ql 进行一些复杂逻辑计算时使用的特殊函数

	current row
		当前行
	n preceding
		往前n行
	n following
		往后n行
	unbounded
		尽头
		unbounded preceding
			从起点
		unbounded following
			最后，终点
	lag(clo,n)
		往前第n行数据
	lead(clo,n)
		往后第n行数据
	ntile(n)
		将数据分成几组，并编号，从1开始，n为int

例子

	（1）查询在2017年4月份购买过的顾客及总人数
		select name,count(*) over () 
from window1 
where substring(orderdate,1,7) = '2017-04' 
group by name;
	（2）查询顾客的购买明细及月购买总额
		select name,orderdate,cost,sum(cost) over(partition by month(orderdate)) from window1;
	（3）上述的场景,要将cost按照日期进行累加
		select name,orderdate,cost, 
sum(cost) over() as sample1,--所有行相加 
sum(cost) over(partition by name) as sample2,--按name分组，组内数据相加 
sum(cost) over(partition by name order by orderdate) as sample3,--按name分组，组内数据累加 
sum(cost) over(partition by name order by orderdate rows between UNBOUNDED PRECEDING 	and current row ) as sample4 ,--和sample3一样,由起点到当前行的聚合 
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 window1;
	（4）查询顾客上次的购买时间
		select name,orderdate,cost, 
lag(orderdate,1,'1900-01-01') over(partition by name order by orderdate ) as time1, lag(orderdate,2) over (partition by name order by orderdate) as time2 
from window1;
	（5）查询前20%时间的订单信息
		select * from (
select name,orderdate,cost, ntile(5) over(order by orderdate) sorted
from window1
) t
where sorted = 1;

Rank

排名
相关说明
	ranl()
		排序值相同时相同排名，总是不变
	dense_rank()
		排序值相同时相同排名，总数会减少
	row_number()
		会依次排序
例子
	计算每门学科成绩排名
	select name,
subject,
score,
rank() over(partition by subject order by score desc) rp,
dense_rank() over(partition by subject order by score desc) drp,
row_number() over(partition by subject order by score desc) rmp
from score;