hive 使用hive的hql实现男女各自第一名及其它

1.8 使用hive的hql实现男女各自第一名及其它

id sex chinese_s math_s
0 0 70 50
1 0 90 70
2 1 80 90
1、男女各自语文第一名（0:男，1:女）
2、男生成绩语文大于80，女生数学成绩大于70

create table if not exists student(
id int,
sex string,
chinese_s double,
math_s double
)
row format delimited fields terminated by ' '
lines terminated by '\n'
stored as textfile
;

load data local inpath '/root/hivedata/student.txt' into table student;

1.
select
id,
sex,
chinese_s
from
(
select
id,
sex,
chinese_s,
row_number() over(distribute by sex sort by chinese_s desc) ord
from
student) tmp
where ord=1
;

select
*
from
student
where (sex='0' and chinese_s>80) or (sex='1' and math_s>70)
;