在失败中寻找答案：如何利用开窗取每组中的前N个，分组和开窗的区别

如何利用开窗取每组中的前N个，分组和开窗的区别

1.找出每个同学最高的分数2..找出每个同学前两高的分数3.找出每个同学前两高的分数的平均数4.总结

①开窗和分组的区别②取每组中的前N个

准备数据

tom,109
tom,107
tom,105
jery,211
jery,209
jery,213
may,128
may,131
may,123

创建表

create table if not exists info(
	name string,
    score int
)
row format delimited
fields terminated by ','
stored as textfile
location '/user/hive/warehouse/info';

上传数据到hive表中

dfs -put /opt/module/hive/datas/info.txt /user/hive/warehouse/student;

查看数据是否上传成功

不小心建表失手少写了一个n，刚好复习一下修改字段名把

alter table info CHANGE ame name string;

开始测试：

分组：group by

select 
	name,
	max(score)
from info
group by name;

开窗：over()

select
	name,
	max(score)
	over()
from info;

开窗加分区：over(partition by name)

select
	name,
	max(score)
	over(partition by name)
from info;

开窗加分组：over() … group by name

select 
	name,
	max(score)
	over(partition by name)
from info
group by name;

典型的topN，可以使用row_number()，然后将row_number<=2的取出

select 
	name,
	score,
	row_number()
	over(partition by name order by score desc) topN
from info; 

然后按照name分组，将row小于2的过滤即可

select 
	tmp.name,
	tmp.score
from (
    select 
        name,
        score,
        row_number()
        over(partition by name order by score desc) topN
    from info
) tmp
where tmp.topN <= 2;

select 
	tmp.name,
	avg(tmp.score) avg_score
from (
    select 
        name,
        score,
        row_number()
        over(partition by name order by score desc) topN
    from info
) tmp
where tmp.topN <= 2
group by tmp.name;

简单的来说，就是数据维度的区别

分组：多对多---->一对一，需要使用到聚合函数

开窗：多对多---->多对多

分组的结果：

开窗的结果：

分组和开窗的区别对比图：

这个可以使用rank()函数，之前总是在想怎么在开窗中一步到位，现在虽然没有实现，但是我猜应该也是可以这样实现。

现在能行的方法就是采用排序函数，然后作为子查询取前N个数值

需要注意的是三个排序的udf

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

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

ROW_NUMBER() 会根据顺序计算

分组和rank函数详情见：
hive常用的内置函数