关于大数据: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