1 手写HQL 第1题
表结构: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
1)建表语句
create table score(
uid string,
subject_id string,
score int)
row format delimited fields terminated by '\t';
2)求出每个学科平均成绩
select
uid,
score,
avg(score) over(partition by subject_id) avg_score
from
score;t1
3)根据是否大于平均成绩记录flag,大于则记为0否则记为1
select
uid,
if(score>av