Hive入门(二) SQL操作

1.在Hive中用show functions;查看所有系统自带的函数.
2.查询每个部门薪资最高的数目
select deptno,max(sal) from emp GROUP BY deptno;
3.查询显示员工姓名,员工编号,部门名称
select e.enam,e.empno,d.danme FROM emp e JOIN dept d ON e.deptno=d.deptno;
4.按照部门进行薪资的排位
select ename,empno,sal,deptno ,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp;
over()不能单独使用必须和其他函数一起使用.
5.每个部门薪资的前两名
select temp.ename,temp.empno,temp.sal,temp.deptno from (
select ename,empno,sal,deptno,ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC) rank FROM emp
) temp where rank >=2;
6.查询部门10的所有员工,并且按照薪资降序排序(默认升序)
select * from emp where deptno = “10” order by sal desc;
7.按照部门进行分组,按照薪资进行降序排序,每个部门薪资最高的那个人薪资显示在最后一列
select empno,ename,deptno,max(sal) over(partition by deptno order by sal desc)as dept_maxsal from emp;
8.按照部门进行分组,按照薪资进行降序排序,最后一列为序号,薪资最高为1,依次向下累加.
select empno,ename,deptno,sal,row_number() over(partition by deptno order by sal desc ) as rn from emp;
9.按照部门进行分组,按照薪资进行降序排序,最后一列为序号,薪资最高为1,依次向下累加.并且只显示前两位
select empno,ename,deptno,sal from(select empno,ename,deptno,sal,row_number() over(partition by deptno order by sal desc ) as rn from emp) temp where rn <3;
10.统计PV UV
select temp.date,COUNT(temp.url) pv,COUNT(DISTINCT temp.guid) uv
FROM( select substring(trackTime,0,10) date,url,guid from track_log where
length(url)>0)temp group by temp.date;
11.计算公司一个人拿的总工资工资+提成
select ename,case when comm is null then 0 + sal else comm + sal end from emp;
相当于if else
12.公司评定
显示收入评级：
select ename, case when sal < 1000 then “lower” when sal >= 1000 and sal <= 2000 then “mid” else “high” end from db_hive_demo.emp;
select ename, case when sal < 1000 then deptno when sal >= 1000 and sal <= 2000 then comm else “UNKNOWN” end from db_hive_demo.emp;