全部产品
MaxCompute

快速掌握SQL写法

更新时间:2017-06-07 13:26:11   分享:   

本文通过课程实践方式介绍MaxCompute SQL,快速掌握SQL的写法,并清楚MaxCompute SQL和标准SQL的区别。请结合MaxCompute SQL基础文档阅读。

数据集准备

这里选择大家比较熟悉的Emp/Dept表做为数据集。为了方便大家操作,给出了相关的MaxCompute 建表语句和数据文件(emp表数据文件dept表数据文件),可自行在MaxCompute项目上创建表并上传数据。

  1. --创建empDDL语句
  2. CREATE TABLE IF NOT EXISTS emp (
  3. EMPNO string ,
  4. ENAME string ,
  5. JOB string ,
  6. MGR bigint ,
  7. HIREDATE datetime ,
  8. SAL double ,
  9. COMM double ,
  10. DEPTNO bigint );
  11. --创建deptDDL语句
  12. CREATE TABLE IF NOT EXISTS dept (
  13. DEPTNO bigint ,
  14. DNAME string ,
  15. LOC string);

SQL操作

初学SQL常遇到的问题点

  1. 使用Group by,那Select的部分要么是分组项,要么就得是聚合函数。
  2. Order by后面必须加Limit n。
  3. Select表达式里不能用子查询,可以用Join改写。
  4. Join不支持笛卡尔积,以及MapJoin的用法和使用场景。
  5. Union all需要改成子查询的格式。
  6. In/Not in语句对应的子查询只能有一列,而且返回的行数不能超过1000。否则也需要改成Join。

编写SQL进行解题

题目一:列出至少有一个员工的所有部门。

为了避免数据量太大的情况下导致“常遇问题点”中的第6点,我们需要使用Join进行改写:

  1. SELECT d.*
  2. FROM dept d
  3. JOIN (
  4. SELECT DISTINCT deptno AS no
  5. FROM emp
  6. ) e
  7. ON d.deptno = e.no;

题目二:列出薪金比“SMITH”多的所有员工。

MapJoin的典型场景:

  1. SELECT /*+ MapJoin(a) */ e.empno
  2. , e.ename
  3. , e.sal
  4. FROM emp e
  5. JOIN (
  6. SELECT MAX(sal) AS sal
  7. FROM `emp`
  8. WHERE `ENAME` = 'SMITH'
  9. ) a
  10. ON e.sal > a.sal;

题目三:列出所有员工的姓名及其直接上级的姓名。

非等值连接:

  1. SELECT a.ename
  2. , b.ename
  3. FROM emp a
  4. LEFT OUTER JOIN emp b
  5. ON b.empno = a.mgr;

题目四:列出最低薪金大于1500的各种工作。

Having的用法:

  1. SELECT emp.`JOB`
  2. , MIN(emp.sal) AS sal
  3. FROM `emp`
  4. GROUP BY emp.`JOB`
  5. HAVING MIN(emp.sal) > 1500;

题目五:列出在每个部门工作的员工数量、平均工资和平均服务期限。

时间处理上有很多好用的内建函数:

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

题目六: 列出每个部门的薪水前3名的人员的姓名以及他们的名次(Top n的需求非常常见)。

  1. SELECT *
  2. FROM (
  3. SELECT deptno
  4. , ename
  5. , sal
  6. , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
  7. FROM emp
  8. ) emp1
  9. WHERE emp1.nums < 4;

题目七: 用一个SQL写出每个部门的人数、“CLERK”(办事员)的人数占该部门总人数占比

  1. SELECT deptno
  2. , COUNT(empno) AS cnt
  3. , ROUND(SUM(CASE
  4. WHEN job = 'CLERK' THEN 1
  5. ELSE 0
  6. END) / COUNT(empno), 2) AS rate
  7. FROM `EMP`
  8. GROUP BY deptno;
本文导读目录
本文导读目录
以上内容是否对您有帮助?