hive sql系列（一）——找出所有科目成绩都大于某一学科平均成绩的学生

需求：找出所有科目成绩都大于某一学科平均成绩的学生

建表语句

create table score(
    uid string,
    subject_id string,
    score int
)
row format delimited fields terminated by '\t'
;

数据

insert overwrite table score values
("1001","01",100),
("1001","02",100),
("1001","03",100),
("1002","01",90),
("1002","02",70),
("1002","03",50),
("1003","01",80),
("1003","02",60),
("1003","03",40);

实现

select
    t2.uid
from(
    select
        t1.uid,
        t1.subject_id,
        t1.score,
        t1.avg_score,
        if(t1.score > t1.avg_score,0,1) as flag --每科成绩大于某科平均分
    from(
        select
            uid,
            subject_id,
            score,
            cast(avg(score) over(partition by subject_id) as int) as avg_score
        from
            score
        )t1
    )t2
group by t2.uid
having sum(t2.flag)=0;

结果

t2.uid
1001
Time taken: 33.267 seconds, Fetched: 1 row(s)

分析

1、重点是对科目开窗求每个平均分，这样每个科目后面都会匹配对应的平均分
2、每一条数据都有学生每科的成绩和这个科目的平均分，可以直接判断科目成绩是否大于科目平均分
3、核心逻辑是flag处理，学生每科成绩大于所有科目的平均分，对于每个学生来说，只有一种情况，全部为0，
而每科成绩小于一科平均分便为1，这是一种反向思维
4、然后对flag求和，和为0就是符合要求的结果

扩展

select
    uid,
    subject_id,
    score,
    round(avg(score) over(partition by subject_id),2) as avg_score
from
    score
;
uid  subject_id  score  avg_score
1003  01  80  90.0
1002  01  90  90.0
1001  01  100  90.0
1003  02  60  76.67
1002  02  70  76.67
1001  02  100  76.67
1003  03  40  63.33
1002  03  50  63.33
1001  03  100  63.33
Time taken: 14.056 seconds, Fetched: 9 row(s)
 
 
开窗的结果会根据分区键进行匹配，也就是说在原有的行记录再添加一列，
这个列的每一行的值是开窗结果集，结果集的在每一行的值是与分区键匹配
用程序语言表述一下：
rows[keys:values] -- 多个不同的key对应多个相同或不同的values
over(partition by key) --开窗是以key作为分区键来读取数据
key:values --分区之后，原有数据变成多个子集：一个key对应多个values
avg() over(partition by key)--对每个结果集进行运算
key:result -- 运算之后，一个key对应一个result
rows[keys:results] --最终展示的结果是在原有的数据不变+新增一列result
 
select
    subject_id,
    round(avg(score),2)
from
    score
group by subject_id
;
subject_id  _c1
01  90.0
02  76.67
03  63.33
Time taken: 24.313 seconds, Fetched: 3 row(s)
 
分组统计会根据分组键进行分组，找到分组键列和聚合函数作用的列，相当于从原始数据集中
取出两列，行数不变
【扩展】where条件会减少行数
然后根据分组键切分成多个数据集，每个数据集数据类型相同，然后使用聚合函数计算，返回结果
再与分组键组成kv结构，就是最终看到的效果
用程序语言表述一下：
rows[rows:cols] -- 原始数据集，多行多列
group by key -- 指定分组键,
select col1,col2 -- 取出多列，数据变成多行少列
group by key & select col1,聚合函数(key)-- 这里会忽略聚合函数作用列，比较select中的key是否是group by中的key的子集
avg(key) --对每个子集的数据计算，返回结果
key:result -- 运算之后，一个key对应一个result，这就是最终结果
 
对比：
1、over(partition by)和group by的效果都是分组统计
2、over是原始数据不变，新增一列，group by是从原始数据集中选出子集，只能看到结果，没有原始数据信息
3、over(partition by)效果=原始数据集 join group by原始数据集