2021-01-20 hive聊一聊分组和开窗 group by 和over()

目录

目录

前言

一、什么是分组和开窗？

二、实例

1.分组

2.开窗

总结

---

---

前言

分组和开窗，在分析数据的时候，具有重要意义。

首先要理解，为什么有这样的操作，举一个简单例子：一个学校进行期末考试，出成绩时所有人信息都在一张大大的表里(包含姓名，班级，学号，科目，成绩)  --千万不要跟别人说为什么不分开放，因为建表太多消耗资源，任务依赖繁琐等等因素会让工作越来越复杂

那么，在这张表的基础上想要统计个人总分/均分、班级总分/均分、班级各科总分/均分、年级各科总分/均分，就需要进行分组或者开窗了。
 

一、什么是分组和开窗？

思考了很多天，想着怎么形象地去描述，最后还是工作环境和"开窗"这两个字给了我灵感

分组：以某几个字段(记为A)作为条件，将A一致的数据放在一个临时区域，等待分析；

开窗：在处理上类似分组，但其独特的地方就和它的名字一样，在一间房子某个位置开个窗户，可以按照局部要求进行分析。

按照密闭房子(长方体，六个面，长宽高各不相等)的场景来看，我想透过这密闭的墙壁，发现墙壁背后的东西，那么分组像是建落地门窗，是要整体考虑一面墙的大小，就会被分为三组（相对的面为一组），但是开窗就不一样了，可以根据需要进行选择性"分组"，可以在某一个面上的某个区域“开个窗”，甚至可以选择面面相交的角落。

如果有更好的描述方法，可以一起探讨，上述内容完全自己想的，佩服把over(partition by ) 解释为开窗的人！

二、实例

1.分组

现有一张成绩表 t1(name,code,class,grade,Chinese,Math,English)  字段含义分别是：姓名，学号，班级，年级，语文，数学，英语

如果我想通过这张表获取每个班级的总分和均分

select 
    class   --本身是关键字，不冲突
   ,grade
   ,sum(Chinese)+sum(Math)+sum(English) as sum_grade  --总分
   ,avg(Chinese)+avg(Math)+avg(English) as avg_grade  --均分
 from t1
 group by class and grade

上面的计算逻辑是假设数据都是合法合规的，sum/avg会对NULL至作0处理，但是+对数据要求严格，自行注意，如果是面试过程可以在外围嵌套一层coalesce()处理，对于空值进行转化，在此便不对这个函数细说了。

以年级和班级作为分组条件，例如会把三年级一班的所有同学放到一组，再进行统计总分和均分，分组总数=班级数(例如高中：高一7个班，高二8个班，高三10个班，分组数目就是25)

那如果想要对高三各班级进行各科均分统计

select 
    class
   ,avg(Chinese) as avg_Chinese
   ,avg(Math)
   ,avg(English)  
 from t1
 where grade = '高三' 
 group by class

这里就会牵扯到一段sql执行的顺序，首先是 from  找到这张表，然后是where  筛选出满足 grade 满足'高三'的，然后分组 avg聚合分析  然后select查询显示

注意点：

1、分组一定要聚合，不然没有意义，分组后不聚合处理，那显示出来的结果跟直接排序查询没有区别！！！

2、查询字段，除了聚合操作的字段外，其余都要出现在group by 后    (其余字段 相当于 约束条件 一整面墙尺寸)

2.开窗

假设我想看高三各班级每个人各科与均分的差距：在每位同学成绩的后面加上这一门课的班级均分(方便各科老师约谈)

select 
    name
   ,class
   ,code
   ,Chinese
   ,avg(Chinese)over(partition by class) as avg_CHinese
   ,Math
   ,avg(Math)over(partition by class) as ava_Math
   ,English
   ,avg(English)over(partition by class)  as avg_English
 from t1
 where grade = '高三' 

这样就可以在高三的每个班级每位同学的各科成绩后面加上均分了；

from—where后只有高三各班级的数据，但是我现在查询除了分析聚合的avg外有name，class，code，Chinese，Math，English，但是我们想的是取一个班级的均分，就不能对所有的分组，只能对class这个字段分组，这时候就需要用到开窗了，单独以class作为约束，在一面墙上开一个我目标尺寸的“窗户”，以达到我所要的效果。

---

总结

经过以上对分组和开窗的形象描述和实例，相信诸位已经有了进一步了解，文章开始对于分组和开窗的描述如果有更好的理解，欢迎来讨论！