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);
}
}
相关资源：python入门教程(PDF版)
