HIVE查询实例和Shell去重（面试题）

一、HIVE查询
hive中students表

nid    nname   ncourse   ngrade
1    张三    yuwen       85
2    李四    yuwen       75
3    张三    shuxue      75
4    李四    shuxue      95
5    张三    yingyu       65
6    李四    yingyu       83

create table students(nid int,nname string,ncourse string,ngrade int)
row format delimited fields terminated by '\t';

load data local inpath '/home/hadoop/test' overwrite into table students;


问题1、使用HQL得出张三的所有科目的成绩，打印一行，如：张三  85  75  65

查询关于张三的所有内容
select x.nid,x.nname,x.ncourse,x.ngrade from students x where x.nname = '张三';

只查张三，两种方法
select nname,
max(case ncourse when 'yuwen' then ngrade else 0 end) yuwen,
max(case ncourse when 'shuxue' then ngrade else 0 end) shuxue,
max(case ncourse when 'yingyu' then ngrade else 0 end) yingyu
from students group by nname HAVING nname = '张三';

select nname,
max(case ncourse when 'yuwen' then ngrade else 0 end) yuwen,
max(case ncourse when 'shuxue' then ngrade else 0 end) shuxue,
max(case ncourse when 'yingyu' then ngrade else 0 end) yingyu
from (select * from students where nname='张三') a group by nname;

查所有人的
select nname,
max(case ncourse when 'yuwen' then ngrade else 0 end) yuwen,
max(case ncourse when 'shuxue' then ngrade else 0 end) shuxue,
max(case ncourse when 'yingyu' then ngrade else 0 end) yingyu
from students group by nname;


2、显示姓名，各科成绩都在70以上的学生，如 李四

MSSQL可以，但是这个查询命令在Hive无法执行
select nname from students where nid not in(select distinct nid from students where ngrade < 70);
请各位朋友帮助下，分享下HIVE中的方法。


二、shell进行文件去重
aaa.txt
111
222
333
444
333
222
555


代码：
cat -n aaa.txt | sort -k2,2 -k1,1n | uniq -f1 | sort -k1,1n | cut -f2-

代码解释:
cat -n in.txt : 输出文本，并在前面加上行号，以\t分隔
sort -k2,2 -k1,1n : 对输入内容排序，primary key是第二个字段，second key是第一个字段并且按数字大小排序
uniq －f1 : 忽略第一列，对文本进行去重，但输出时会包含第一列
sort -k1,1n : 对输入内容排序，key是第一个字段并按数字大小排序
cut -f2- : 输出第2列及之后的内容，默认分隔符为\t


补充内容 (2015-7-31 17:50):
select a.nname from students a left join (select distinct nname from students where ngrade < 70) as b on a.nname=b.nname where b.nname is null group by a.nname;


补充内容 (2015-7-31 17:50):
select nname from students group by nname having min(if(ngrade>=70,1,0))==1;

补充内容 (2015-7-31 17:53):
hive0.9.0无法执行，hive1.0.1可以执行 select a.nname from students a left join (select distinct nname from students where ngrade < 70) as b on a.nname=b.nname where b.nname is null group by a.nname;

select distinct a.nname from students a left join (select distinct nname from students where ngrade < 70) as b on a.nname=b.nname where b.nname is null;