开发者社区> 传学> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

MaxCompute 学习计划(二)

简介: 学习MaxCompute SQL和UDF的checklist
+关注继续查看

MaxCompute SQL

在这一课,我们开始学习MaxCompute SQL。希望通过这一课的学习,能基本掌握MaxCompute SQL的写法,清楚MaxCompute SQL和标准SQL的区别,还要能熟悉系统内建函数。

数据集

刚开始使用MaxCompute建议到这里免费体验。中间的账号注册、实名认证、数据上传一类的这里不再赘言。

进去后到脚本开发里创建一个自己的脚本。然后就可以开始写SQL执行了。

参考资料

常见错误

  • 如果使用了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);
    }
}

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云 MaxCompute 2020-12 月刊
2020年12月 MaxCompute 在查询加速、流式数据写入、消费限制、实时作业监控、元数据服务方面有最新发布,欢迎通过本月刊了解12月最新发布详情。
12421 0
阿里云新品发布会周刊第87期 丨 阿里云MaxCompute 联合帆软重磅发布企业级BI分析解决方案 + 1分钟快速搭建属于自己的数字孪生应用
1月27日阿里云新品发布会“阿里云MaxCompute 联合帆软重磅发布企业级BI分析解决方案”&1月29日云计算情报局“1分钟快速搭建属于自己的数字孪生应用 ”重磅来袭!关注阿里云新品发布会直播间~为您第一时间推送最新信息!
0 0
阿里云 MaxCompute 2021-1 月刊
2021年1月,SaaS模式云数据仓库MaxCompute存储单价由梯度定价统一下调整为月固定单价,中小规模企业数据仓库存储成本最高降四成。
0 0
阿里云 MaxCompute 2021-1 月刊
2021年1月,SaaS模式云数据仓库MaxCompute存储单价由梯度定价统一下调整为月固定单价,中小规模企业数据仓库存储成本最高降四成。
4436 0
阿里云 MaxCompute 2021-2 月刊
2021年2月,MaxCompute发布云数仓内的机器学习 MaxCompute SQLML功能,以及MaxCompute湖仓一体OSS数据湖集成方案等15项新功能,欢迎阅读产品2月刊了解新发功能详情。
0 0
阿里云 MaxCompute 2021-2 月刊
2021年2月,MaxCompute发布云数仓内的机器学习 MaxCompute SQLML功能,以及MaxCompute湖仓一体OSS数据湖集成方案等15项新功能,欢迎阅读产品2月刊了解新发功能详情。
2332 0
基于阿里云大数据平台开发大数据应用(一):精挑细选,选定MaxCompute
本文是基于阿里云大数据平台开发大数据应用系列文章的第一部分,主要谈谈为什么在进行技术调研以后,选定阿里云MaxCompute作为大数据项目开发的平台。
0 0
基于阿里云大数据平台开发大数据应用(二):MaxCompute 初体验
本文是基于阿里云大数据平台开发大数据应用系列文章的第二篇,主要谈谈阿里云MaxCompute作为大数据项目开发的平台的特点与优点。
0 0
基于阿里云大数据平台开发大数据应用(三):基于MaxCompute 的慕课网站数据仓库
本文是基于阿里云大数据平台开发大数据应用系列文章的第三部分,主要介绍如何基于阿里云MaxCompute 平台,开发慕课网站的数据仓库。
0 0
阿里云 MaxCompute 2021-3 月刊
2021年3月,MaxCompute管家等多项功能上新,快来一起看看吧。
0 0
+关注
传学
传学,专注于大数据领域的解决方案
文章
问答
来源圈子
更多
MaxCompute(原ODPS)是一项面向分析的大数据计算服务,它以Serverless架构提供快速、全托管的在线数据仓库服务,消除传统数据平台在资源扩展性和弹性方面的限制,最小化用户运维投入,使您经济并高效的分析处理海量数据。
+ 订阅
相关文档: MaxCompute
文章排行榜
最热
最新
相关电子书
更多
MaxCompute索引优化实践分享
立即下载
亲宝宝的MaxCompute实践
立即下载
MaxCompute技术公开课第四季 之 MaxCompute Tunnel上传典型问题场景实战
立即下载