原文链接:http://click.aliyun.com/m/13924/
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); } }