本文为您介绍MaxCompute SQL常见使用场景,让您快速掌握SQL的写法。

准备数据集

本文以emp表和dept表为示例数据集。您可以自行在MaxCompute项目上创建表并上传数据。数据导入请参见数据上传下载概述

下载emp表数据文件dept表数据文件

创建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示例

  • 示例1:查询员工人数大于零的所有部门。
    为了避免数据量太大,此场景下建议您使用JOIN子句。
    SELECT d.*
    FROM dept d
    JOIN (
        SELECT DISTINCT deptno AS no
        FROM emp
    ) e
    ON d.deptno = e.no;
  • 示例2:查询薪金比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;
  • 示例3:查询所有员工的姓名及其直接上级的姓名。
    此场景为等值连接。
    SELECT a.ename
        , b.ename
    FROM emp a
    LEFT OUTER JOIN emp b
    ON b.empno = a.mgr;
  • 示例4:查询基本薪金大于1500的所有工作。
    此场景下需要使用HAVING子句。
    SELECT emp.`JOB`
        , MIN(emp.sal) AS sal
    FROM `emp`
    GROUP BY emp.`JOB`
    HAVING MIN(emp.sal) > 1500;
  • 示例5:查询在每个部门工作的员工数量、平均工资和平均服务期限。
    此场景为使用内建函数的典型场景。
    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`;
  • 示例6:查询每个部门的薪水前3名的人员的姓名以及其排序。
    此场景为典型的Top N场景。
    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;
  • 示例7:查询每个部门的人数以及该部门中办事员(CLERK)人数的占比。
    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;

注意事项

  • 使用GROUP BY时,SELECT部分必须是分组项或聚合函数。
  • ORDER BY后面必须加LIMIT N。
  • SELECT表达式中不能用子查询,可以改写为JOIN。
  • JOIN不支持笛卡尔积,可以使用MAPJOIN替代。
  • UNION All需要改成子查询的格式。
  • IN/NOT IN语句对应的子查询只能有一列,而且返回的行数不能超过1000,否则也需要改成JOIN操作执行。