<3>hive 查询，排序，函数

6.查询
简单查询配置:set hive.fetch.task.conversion=more;/hive --hiveconf hive.fetch.task.conversion=more;/修改hive-site.xml文件;
函数nvl(x,0)不为空的则为0
当有值为空时的判断 is null/is not null
HQL严格区分字符大小写：
select * from view_student2 where nam='King';
不同
select * from view_student2 where nam='king';
去重:
distinct：select distinct id from view_student2/select distinct id,x from view_student2;
模糊查询:
like:
select * from view_student2 where name like 'S%';
_ 需要使用转译 //_
使用正则表达式来制定列？？？？？进行查询
select symbol,'price.*' from stocks;（后面会写）

7.排序:
列:select * from view_student2 order by x;(默认升序)
列:select * from view_student2 order by x desc;(降序)
order by 后面跟:列,表达式,别名,序号
表达式排序:select * from view_student2 order by x*12;
别名排序:select id,name,x,x*12 as m from view_student2 order by m;
序号排序:hive.groupby.orderby.position.alias=true;使用前需开启参数
select id,name,x,x*12 from view_student2 order by 4;
hive里面空值如何排序:
升序 null在最前面/降序 null在最后面

8.hive函数
内置函数:
数学函数；round 四舍五入/ceil 向上取整/floor 向下取整
字符函数: lower 字符串转小写/upper 字符串转大写/length 字符串长度/concat 拼接字符串/substr 求一个字符串子串/trim 去除字符串两端的空值/lpad左填充/rpad右填充
收集函数: size(map(<key,value>,<key2,value2>))： size(map(<1;'tom'>,<2;'mary'>)) ->  2
转换函数: cast:select cast(1 as float)->1.0
日期函数:
条件函数: coalesce 从左到右返回第一个不为null的值/case...when... case a when b then c[where d then e]*[else f] end;
select id,name,gender,x,
case x when 90 then x+1000
when 88 then x+888
else x*10 end
from external_student;
聚合函数:count,sum,min,max,avg
select count(*),sum(x),min(x),max(x),avg(x) from view_student2;
表生成函数:explode map转行
select explode(map(1,'tom',2,'jack',3,'mike'));
1    tom
2    jack
3    mike

limit语句
select * from sample limit 10;