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

数据集准备

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

创建emp表的DDL语句,如下所示:


CREATE TABLE IF NOT EXISTS emp (
  EMPNO string ,
  ENAME string ,
  JOB string ,
  MGR bigint ,
  HIREDATE datetime ,
  SAL double ,
  COMM double ,
  DEPTNO bigint );

创建 dept 表的 DDL 语句,如下所示:


CREATE TABLE IF NOT EXISTS dept (
  DEPTNO bigint ,
  DNAME string ,
  LOC string);

SQL操作

初学SQL常遇到的问题点
  • 使用Group by,那么Select的部分要么是分组项,要么就得是聚合函数。

  • Order by后面必须加Limit n。

  • Select表达式中不能用子查询,可以改写为Join。

  • Join不支持笛卡尔积,以及MapJoin的用法和使用场景。

  • Union all需要改成子查询的格式。

  • In/Not in语句对应的子查询只能有一列,而且返回的行数不能超过1000,否则也需要改成Join。

编写SQL进行解题

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

为了避免数据量太大的情况下导致 常遇问题点 中的第6点,您需要使用Join 进行改写。如下所示:


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

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

MapJoin的典型场景,如下所示:


SELECT /*+ MapJoin(a) */ e.empno
    , e.ename
    , e.sal
FROM emp e
JOIN (
    SELECT MAX(sal) AS sal
    FROM `emp`
    WHERE `ENAME` = 'SMITH'
) a
ON e.sal > a.sal;

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

非等值连接,如下所示:


SELECT a.ename
    , b.ename
FROM emp a
LEFT OUTER JOIN emp b
ON b.empno = a.mgr;

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

Having 的用法,如下所示:


SELECT emp.`JOB`
    , MIN(emp.sal) AS sal
FROM `emp`
GROUP BY emp.`JOB`
HAVING MIN(emp.sal) > 1500;

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

时间处理上有很多好用的内建函数,如下所示:


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`;

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

SQL 语句如下所示:


SELECT *
FROM (
  SELECT deptno
    , ename
    , sal
    , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY sal DESC) AS nums
  FROM emp
) emp1
WHERE emp1.nums < 4;

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

SQL语句如下所示:


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