全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

快速掌握SQL写法

更新时间:2017-10-26 01:48:04

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

数据集准备

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

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

  1. CREATE TABLE IF NOT EXISTS emp (
  2. EMPNO string ,
  3. ENAME string ,
  4. JOB string ,
  5. MGR bigint ,
  6. HIREDATE datetime ,
  7. SAL double ,
  8. COMM double ,
  9. DEPTNO bigint );

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

  1. CREATE TABLE IF NOT EXISTS dept (
  2. DEPTNO bigint ,
  3. DNAME string ,
  4. 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 进行改写。如下所示:

  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 的需求非常常见)。

SQL 语句如下所示:

  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(办事员)的人数占该部门总人数占比

SQL 语句如下所示:

  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;
本文导读目录