Maxcompute sql 练习题

© 康大校（herry）

数据集准备

这里选择大家比较熟悉的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;

UML 图表

可以使用UML图表进行渲染。 Mermaid. 例如下面产生的一个序列图：:

一个简单的表格是这么创建的：

项目	Value	数量
电脑	$1600	1
手机	$12	1
导管	$1	1

注释也是必不可少的

Markdown将文本转换为 HTML。

$\Gamma(z) = \int_0^\infty t^{z-1}e^{-t}dt\,.$Γ(z)=∫0∞​tz−1e−tdt.
Gamma公式展示 $\Gamma(n) = (n-1)!\quad\forall n\in\mathbb N$Γ(n)=(n−1)!∀n∈N 是通过欧拉积分