Hive基本操作之平均成绩、优秀统计

有一组成绩数据(学号；科目；成绩)

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

启动hive交互模式，选库

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

建成绩表

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

导数据入表

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

查看导入数据

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

计算平均成绩

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

以平均成绩做标记（大于标0，否则标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

统计flag和为0的学号（所有学科都大于平均成绩）

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