有一組成績(jī)數(shù)據(jù)(學(xué)號(hào)；科目；成績(jī))

1001 01 90
1001 02 90
1001 03 90
1002 01 85
1002 02 85
1002 03 70
1003 01 70
1003 02 70
1003 03 85

啟動(dòng)hive交互模式，選庫(kù)

[root@yaong ~]# hive
hive (default)> use default;

建成績(jī)表

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

導(dǎo)數(shù)據(jù)入表

hive (default)> load data local inpath '/data/scores.dat' into table score;

查看導(dǎo)入數(shù)據(jù)

hive (default)> select * from score;

1001 01 90
1001 02 90
1001 03 90
1002 01 85
1002 02 85
1002 03 70
1003 01 70
1003 02 70
1003 03 85

計(jì)算平均成績(jī)

select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from
    score;

uid score avg_score
1003 70 81.66666666666667
1002 85 81.66666666666667
1001 90 81.66666666666667
1003 70 81.66666666666667
1002 85 81.66666666666667
1001 90 81.66666666666667
1003 85 81.66666666666667
1002 70 81.66666666666667
1001 90 81.66666666666667

以平均成績(jī)做標(biāo)記（大于標(biāo)0，否則標(biāo)1）

select
    uid, score, avg_score,
    if(score>avg_score,0,1) flag
from
(select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from
    score) as t1;

uid score avg_score flag
1003 70 81.66666666666667 1
1002 85 81.66666666666667 0
1001 90 81.66666666666667 0
1003 70 81.66666666666667 1
1002 85 81.66666666666667 0
1001 90 81.66666666666667 0
1003 85 81.66666666666667 0
1002 70 81.66666666666667 1
1001 90 81.66666666666667 0

統(tǒng)計(jì)flag和為0的學(xué)號(hào)（所有學(xué)科都大于平均成績(jī)）

select
    uid
from
(select
    uid, score, avg_score,
    if(score>avg_score,0,1) flag
from
(select
    uid,
    score,
    avg(score) over(partition by subject_id) avg_score
from
    score) as t1) as t2
group by uid
having sum(flag)=0;

uid
1001