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
havi