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

最新推荐文章于 2022-12-05 16:17:39 发布

鸭梨山大哎

最新推荐文章于 2022-12-05 16:17:39 发布

阅读量1.8k

点赞数 4

分类专栏： hive 文章标签： hive hql

本文链接：https://blog.csdn.net/u010711495/article/details/113997741

版权

hive 专栏收录该内容

114 篇文章 14 订阅

订阅专栏

本文通过SQL查询演示了如何从数据表中找出所有学生的科目成绩均高于对应科目平均分的情况。首先创建成绩表，然后计算每个科目的平均分，接着设置标志位判断每个学生每科是否超过平均分，最后通过分组和求和验证学生所有科目是否全部高于平均分。查询结果显示，uid为1001的学生满足条件。

摘要由CSDN通过智能技术生成

表结构：uid,subject_id,score
求：找出所有科目成绩都大于某一学科平均成绩的学生
数据集如下

1）建表语句

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

2）求出每个学科平均成绩

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

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

with a as (
    select uid,
           subject_id,
           score,
           avg(score) over (distribute by subject_id) avg_score
    from score
    group by uid, subject_id, score
)
select uid,
       if(score > avg_score, 0, 1) flag
from a;

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

with a as (
    select uid,
           subject_id,
           score,
           avg(score) over (distribute by subject_id) avg_score
    from score
    group by uid, subject_id, score
),
     b as (select uid,
                  if(score > avg_score, 0, 1) flag
           from a)
select uid
from b
group by uid
having sum(b.flag) = 0;

换种写法如下,结果是一样的

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;