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);
    }
}