大数据实践-

建表：

CREATE TABLE IF NOT EXISTS emp

(

　　EMPNO STRING ,

　　ENAME STRING ,

　　JOB STRING ,

　　MGR BIGINT ,

　　HIREDATA DATETIME,

　　SAL DOUBLE ,

　　COMM DOUBLE ,

　　DEPTNO BIGINT

) ;

CREATE TABLE IF NOT EXISTS dept

(

　　DEPTNO BIGINT,

　　DNAME STRING,

　　LOC STRING

) ;

(1)  使用JOIN子句。查询员工人数大于零的所有部门。

SELECT DEPTNO,DNAME,LOC FROM dept NATURAL JOIN emp ;

select dept.DEPTNO,dept.DNAME,dept.LOC from emp inner join dept on dept.DEPTNO = emp.DEPTNO;

SELECT d.* FROM dept d JOIN ( SELECT DISTINCT deptno AS no FROM emp ) e ON d.deptno = e.no;

(2)  使用MAPJOIN，查询薪金比SMITH高的所有员工。

 

select ename,sal,dname from emp join dept on emp.deptno = dept.deptno where sal >( select sal from emp where ename = 'SMITH');

select /*+ mapjoin(a) */ a.EMPNO,a.ENAME,a.JOB,a.MGR,a.HIREDATA,a.SAL,a.COMM,a.DEPTNO FROM emp a join dept on a.sal > (select SAL from emp where ename='SMITH');

select empno,ename,job,mgr,hiredata,sal,comm,dname from emp join dept on emp.deptno = dept.deptno where sal >( select sal from emp where ename = 'SMITH');

(3)  通过3个线程上传源数据到数据表

tunnel upload D:\ODPS\emp.txt emp -t 3;（地址为文件在本机的地址）

tunnel upload D:\ODPS\dept.txt dept -t 3; 

(4)  查询所有员工的姓名及其直接上级的姓名。

select e.ename,m.ename boss_name from emp e left join emp m on e.mgr=m.empno;

 

(5)  查询基本薪金大于1500的所有工作。

select ename,sal from emp where sal > 1500;

(6)  查询在每个部门工作的员工数量、平均工资和平均服务期限。

SELECT COUNT(empno) AS cnt_emp , ROUND(AVG(sal), 2) AS avg_sal, ROUND(AVG(datediff(getdate(), hiredate, 'dd')), 2) AS avg_hire FROM `emp` GROUP BY `DEPTNO`;

(7) 查询每个部门的薪水前3名的人员的姓名以及其排序

select * from (select deptno,ename,sal,row_number() over (partition by deptno order by sal desc) top from emp) where top <4;

(8)查询每个部门的人数以及该部门中办事员（CLERK）人数的占比

SELECT deptno , COUNT(empno) AS 总人数 , ROUND(SUM(CASE WHEN job = 'CLERK' THEN 1 ELSE 0 END) / COUNT(empno), 2) AS 占比 FROM `EMP` GROUP BY deptno;