1)创建Hive表,将如上数据load到Hive中
create table hiveweek3 (id int,sex string,shengao int) row format delimited fields terminated by ','
hdfs dfs -put /usr/local/xujunqi/hiveweek03.txt /user/hive/warehouse/week03;
- 用 SQL 语句的方式统计男性中身高超过 180cm 的人数
select * from week03 where shengao >180 and sex ='M';
3)用 SQL 语句的方式统计女性中身高超过 160cm 的人数
select * from week03 where shengao >160 and sex ='F';
4)对人群按照性别分组统计男女人数
select count(*) ,sex from week03 group by sex;
5)统计并打印身高大于 175cm 的前 5 名男性
select * from week03 where shengao >175 and sex='M' and limit 5 ;
7)统计男性的平均身高
select avg(shengao) from week03 where sex ='M';
select avg(shengao) from week03 where shengao >180 and sex ='M';
8)自定义函数:根据F返回”女”,M返回“男”,并测试
9)按照性别分组求各性别前3名身高(请使用一条语句实现,否则不得此分)
select * from (select *,row_number() over(partition by sex order by fenshu desc )rank from hiveweek3 )t where t.rank<=3;