Hive SQL系列

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

表结构：uid,subject_id,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

建表语句

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

插入数据

insert into 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);

以下内容将整体sql拆分成几步来分析

1、求出每个学科平均成绩

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

2、根据是否大于平均成绩记录flag，大于则记为0否则记为1

select
    uid,
    if(score>avg_score,0,1) flag
from
    t1;t2

3、根据学生id进行分组统计flag的和，和为0则是所有学科都大于平均成绩

select
    uid
from
    t2
group by
    uid
having
    sum(flag)=0;

4、最终sql

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