MaxCompute SQL
在这一课，我们开始学习MaxCompute SQL。希望通过这一课的学习，能基本掌握MaxCompute SQL的写法，清楚MaxCompute SQL和标准SQL的区别，还要能熟悉系统内建函数。
数据集
刚开始使用MaxCompute建议到这里免费体验。中间的账号注册、实名认证、数据上传一类的这里不再赘言。
进去后到脚本开发里创建一个自己的脚本。然后就可以开始写SQL执行了。
参考资料
- 可能需要参考MaxCompute关于[SQL部分的文档]，比如DML部分和内建函数。
常见错误
- 如果使用了Group by，那Select的部分要么是分组项，要么就得是聚合函数。
- Order by后面必须加Limit n。
- Select表达式里不能用子查询，可以用Join改写。
- Join不支持笛卡尔积，以及MapJoin的用法和使用场景。
- Union all需要改成子查询的格式。
- In/Not in语句对应的子查询只能有一列，而且返回的行数不能超过1000。否则也需要改成Join。
作业
- 还是之前学习Mysql SQL时候的题目，这里就不重复列出来以免有凑字数嫌疑 : )
- 此外文档里提到的输出到动态分区功能请熟练掌握
- 列出每个部门的薪水前3名的人员的姓名以及他们的名次(Top n的需求非常常见)
- 用一个SQL写出每个部门的人数、“CLERK”（办事员）的人数占该部门总人数占比
- 梳理内建函数里时间数据的各种格式的转换方式，包括时间戳<==>日期类型数据<==>字符串格式
参考答案
以下是容易出错的题目的答案
--1．列出至少有一个员工的所有部门。 Join改写。避免数据量太大的情况下导致“常见错误”6
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;
--7．列出最低薪金大于1500的各种工作。 Having的用法
SELECT emp.`JOB`
, MIN(emp.sal) AS sal
FROM `emp`
GROUP BY emp.`JOB`
HAVING MIN(emp.sal) > 1500;
--13．列出在每个部门工作的员工数量、平均工资和平均服务期限。 时间处理上有很多好用的内建函数
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`;
--22 列出每个部门的薪水前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;
--23 用一个SQL写出每个部门的人数、“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;UDF/UDAF/UDTF
内建函数已经能满足大部分的需求，但是总是无法避免有一些特殊业务逻辑无法用内建的函数来实现的。比如WM_CONCAT是没有排序的，如何实现根据某个字段进行排序的wm_concat。这个时候需要自己编写函数来实现。
准备工作
- 客户端工具安装
- Eclipse/IntelliJ IDEA开发环境的安装
- 依赖包配置
后续的课程都涉及客户端和IDE开发环境的配置，后续就不再专门提及。
注意事项
- UDF的evaluate方法必须是非static的public方法。而且名字不能变。
- UDAF/UDTF的注解（@Resolve）不能少。用于设置函数的输出输出数据类型。
- UDTF限制1:同一个SELECT子句中不允许有其他表达式
- UDTF限制2:UDTF不能嵌套使用
- UDTF限制3:不支持在同一个select子句中与 group by / distribute by / sort by 联用
作业
- UDF/UDAF/UDTF分别是在什么场景下使用。
- 用UDAF实现Median函数，并思考Median和平均值的实现上为什么有这么大的区别。
- 用UDTF实现Split函数，用于把一个字符串根据自定的分隔符分割成多个字符串。
思考题
- 如何用UDTF实现开窗函数
参考答案
SQL:
传参empno等参数是因为UDTF限制1
把distribute by sort by放到子查询里是因为UDTF限制3
SELECT my_window(empno,ename,job,sal) AS (empno,ename,job,wrn,wsum) FROM (SELECT * FROM emp DISTRIBUTE BY job SORT BY job,sal) a;
JAVA:
package com.aliyun.odps.udtf;
import com.aliyun.odps.udf.ExecutionContext;
import com.aliyun.odps.udf.UDTF;
import com.aliyun.odps.udf.annotation.Resolve;
import com.aliyun.odps.udf.UDFException;
@Resolve({ "bigint,string,string,double->bigint,string,string,bigint,double" })
public class MyWindow extends UDTF {
private Long cnt;
private Double sum;
private String lastJob = "";
@Override
public void setup(ExecutionContext ctx) throws UDFException {
cnt = 0l;
sum = 0d;
super.setup(ctx);
}
@Override
public void process(Object[] args) throws UDFException {
Long empno = (Long) args[0];
String ename = (String) args[1];
String job = (String) args[2];
Double b = (Double) args[3];
//为了让例子更易懂，去掉了对lastJob和job为空的处理逻辑
if (!lastJob.equals(job)) {
lastJob = job;
cnt = 0l;
sum = 0d;
}
sum += b;
cnt++;
forward(empno, ename, job, cnt, sum);
}
}
