MaxCompute 学习计划(二)

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改写。避免数据量太大的情况下导致“常见错误”6SELECT d.*FROM dept dJOIN (    SELECT DISTINCT deptno AS no    FROM emp) eON d.deptno = e.no;--2．列出薪金比“SMITH”多的所有员工。 MapJoin的典型场景 SELECT /*+ MapJoin(a) */ e.empno    , e.ename    , e.salFROM emp eJOIN (    SELECT MAX(sal) AS sal    FROM `emp`    WHERE `ENAME` = 'SMITH') aON e.sal > a.sal;--3．列出所有员工的姓名及其直接上级的姓名。 非等值连接SELECT a.ename    , b.enameFROM emp aLEFT OUTER JOIN emp bON b.empno = a.mgr;--7．列出最低薪金大于1500的各种工作。 Having的用法SELECT emp.`JOB`    , MIN(emp.sal) AS salFROM `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_hireFROM `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) emp1WHERE 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 rateFROM `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限制3SELECT 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);    }}

原文链接：

http://click.aliyun.com/m/13924/