本文通过课程实践的方式为您介绍MaxCompute SQL,让您快速掌握SQL的写法以及了解MaxCompute SQL和标准SQL的区别。

数据集准备

本文以emp/dept表为示例数据集。单击emp表数据文件dept表数据文件下载数据文件。您可自行在MaxCompute项目上创建表并上传数据。建表语句如下,数据导入请参见数据上传下载概述

创建emp表。

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

创建dept表。

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

常见问题

初学SQL时,遇到常见问题如下:
  • 使用Group by时,Select的部分要么是分组项,要么是聚合函数。
  • Order by后面必须加Limit n。
  • Select表达式中不能用子查询,可以改写为Join。
  • Join不支持笛卡尔积,以及MapJoin的用法和使用场景。
  • Union all需要改成子查询的格式。
  • In/Not in语句对应的子查询只能有一列,而且返回的行数不能超过1000,否则也需要改成Join。

SQL示例

  • 示例一:列出员工人数大于零的所有部门。
    为了避免数据量太大,您需要使用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;