MaxCompute SQL数据开发最佳实践

MaxCompute SQL最佳实践

MaxCompute SQL最佳实践是在开发文档的内容基础上，提供的一些实践性的使用指南，具体的内容见：MaxCompute SQL最佳实践文档，这里选出了一些重要且常用的内容进行说明。内容包括：

• 快速掌握SQL写法
• 分组取出每组数据的前N条
• SQL实现多行数据转一条
• MaxCompute SQL中的JOIN ON条件

快速掌握SQL写法

数据集准备

这里以emp/dept表为示例数据集。单击emp表数据文件、dept表数据文件下载数据文件。这里将下载的文件存储为：csv格式文件，文件中的数据内容不变。然后在MaxCompute项目上使用如下命令创建相应的表：

创建emp表：

-- 创建emp表
CREATE TABLE IF NOT EXISTS emp (
  EMPNO string ,
  ENAME string ,
  JOB string ,
  MGR bigint ,
  HIREDATE datetime ,
  SAL double ,
  COMM double ,
  DEPTNO bigint
);

创建dept表：

-- 创建dept表
CREATE TABLE IF NOT EXISTS dept (
  DEPTNO bigint ,
  DNAME string ,
  LOC string
);

创建表完成后，上传数据使用odpscmd客户端的tunnel命令，将下载的数据文件导入到数据表中，odpscmd客户端以及MaxCompute Studio中使用odpscmd的开发环境搭建见：MaxCompute Studio数据开发工具的使用 。上传命令如下：

odps@ YITIAN_BJ_MC>tunnel upload /Users/yitian/Documents/MaxCompute/maxcompute-data/emp.csv emp;

Upload session: 202004212318540c47df0b1829ea42
Start upload:/Users/yitian/Documents/MaxCompute/maxcompute-data/emp.csv
Using \n to split records
Upload in strict schema mode: true
Total bytes:744  Split input to 1 blocks
2020-04-21 23:18:55     scan block: '1'
2020-04-21 23:18:55     scan block complete, block id: 1
2020-04-21 23:18:55     upload block: '1'
2020-04-21 23:18:55     upload block complete, block id: 1
upload complete, average speed is 744 bytes/s
OK

odps@ YITIAN_BJ_MC>tunnel upload /Users/yitian/Documents/MaxCompute/maxcompute-data/dept.csv dept;

上传完成后，可使用如下命令进行来查看导入数据的正确性：

odps@ YITIAN_BJ_MC>select count(*) from emp;
+------------+
| _c0        |
+------------+
| 14         |
+------------+

常见问题

刚开始学习MaxCompute SQL时，可能会遇到的常见问题如下：

• 使用Group by时，Select的部分要么是分组项，要么是聚合函数。
• Order by后面必须加Limit n。
• Select表达式中不能用子查询，可以改写为Join。
• Join不支持笛卡尔积，以及MapJoin的用法和使用场景。
• Union all需要改成子查询的格式。
• In/Not in语句对应的子查询只能有一列，而且返回的行数不能超过1000，否则也需要改成Join。

SQL示例

示例一：列出员工人数大于零的所有部门。有如下两种查询方法：

-- 列出员工人数大于0的所有部门，方法1
select d.deptno, d.dname, d.loc
from dept as d
join (
  select deptno, count(*) as num from emp
  group by deptno
) as e on d.deptno=e.deptno
where e.num>0;

-- 方法2
select d.*
from dept d
join (
  select distinct deptno as no
  from emp
) e on d.deptno=e.no;

返回的结果均为：

分析一下上面的两个方法：

就本查询来说，方法2的方式更为简单，查询效率应该更高，而且满足正确的查询需要。方法1使用了分组的思想来统计emp表中各部门的人数num，在进行join连接，并使用num作为过滤条件，这种方式的查询效率低于方法2。但如果查询需要统计：列出员工人数大于零的所有部门，并列出各部门的总人数，这种情况下方法2就不能满足需要了，但方法1中可以在第一行的输出列中加入e.num，即可以完成该查询需求。

示例二：列出薪金比SMITH多的所有员工（MapJoin使用场景）。这里依然存在两种查询方案：

-- 列出薪资比smith多的所有员工，方法1
select empno, ename, sal from emp
join (
  select max(sal) as smith_sal from emp
  where ename='SMITH'
) s on emp.sal>s.smith_sal;

-- 方法2
select /*+ mapjoin(a)*/ e.empno, e.ename, e.sal
from emp e
join (
  select max(sal) as smith_sal from emp
  where ename='SMITH'
) a on e.sal>a.smith_sal;

返回结果均为：

 

方法1为普通的join连接查询，但其实此时是MapJoin的典型场景：当一个大表和一个或多个小表JOIN时，您可以在SELECT语句中显式指定MAPJOIN以提升查询性能。这里emp相对来说是大表，而子查询为小表，因此可以使用mapjoin进行查询，以提升查询性能。

示例三：列出所有员工的姓名及其直接上级的姓名。自连接查询场景：

-- 列出所有员工的姓名及其直接上级的姓名
select t1.ename as emp_name, t2.ename as leader_name
from emp t1
left outer join emp t2 on t1.mgr=t2.empno;

返回结果如下：

示例四：列出基本薪金大于1500的所有工作（having语句使用场景）。下面有两种查询方法

-- 列出基本薪金大于1500的所有工作，方法1（错误）
select job, min(sal) as sal
from emp
where sal>1500
group by job;

-- 方法2（正确）
select job, min(sal) as sal
from emp
group by job
having min(sal)>1500;

以上的两个方法，方法1的执行顺序是：from-where-select-group by。因此它会先将sal>1500的所有job找到，然后在进行grouop by。此时由于job=SALESMAN的多条数据中，存在sal>1600的数据，因此最终查询结果会包含SALESMAN。结果如下：

但该查询的需求中“基本工资”这里文档中理解为最低工资，而SALESMAN的最低工资为1250，因此它并不应该包含在最后的结果集中，此时就需要使用方法2进行查询，方法2的查询结果为正确的结果。返回的内容如下：

示例五：列出在每个部门工作的员工数量、平均工资和平均服务期限。时间处理上有很多好用的内建函数，如下所示。

-- 列出在每个部门工作的员工数量、平均工资和平均服务期限
select deptno,
       count(empno) as cnt_emp,
       round(avg(sal)) as avg_sal,
       round(avg(datediff(getdate(), hiredate, 'dd')), 2) as avg_hire
from emp
group by deptno;

 返回结果如下：

示例六： 列出每个部门的薪水前3名的人员的姓名以及其排序（Top n的需求非常常见）。

-- 列出每个部门的薪水前3名的人员的姓名以及其排序
select *
from (
  select deptno,
         ename,
         sal,
         ROW_NUMBER() over (partition by deptno order by sal desc) as nums
  from emp
) t1
where t1.nums < 4;

返回结果如下：

 

其中使用了MaxCompute中窗口函数ROW_NUMBER，该函数由于对窗口内分组数据进行排序，并返回排序后的行号，命令格式如下，具体见：MaxCompute窗口函数文档内容。 

row_number() over(partition by [col1, col2…]
order by [col1[asc|desc], col2[asc|desc]…])

示例七： 用一个SQL写出每个部门的人数、CLERK（办事员）的人数占该部门总人数占比。

-- 用一个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;

返回结果如下：

 

这里对clerk人数统计时使用了MaxCompute中一些特有的函数，这部分函数包括CAST、DECODE、LEAST、ARRAY、SPLIT、MAP等，具体见：MaxCompute 其他函数文档。 

分组取出每组数据的前N条

这部分的内容其实上面已经使用过一次了（上面示例6），就是使用ROW_NUMBER窗口函数进行分组排序后，在将每组的前N条数据进行取出即可。命令如下：

SELECT * FROM (
  SELECT empno
  , ename
  , sal
  , job
  , ROW_NUMBER() OVER (PARTITION BY job ORDER BY sal) AS rn
  FROM emp
) tmp
WHERE rn < 10;

返回结果如下：

此外，可以将所有职工根据部门分组，每个组内根据sal做降序排序，获得职工自己组内的序号。

select deptno, 
       ename, 
       sal, 
       ROW_NUMBER() over (partition by deptno order by sal desc) as nums
from emp;
--执行结果如下。
+------------+-------+------------+------------+
| deptno     | ename | sal        | nums       |
+------------+-------+------------+------------+
| 10         | JACCKA | 5000.0     | 1          |
| 10         | KING  | 5000.0     | 2          |
| 10         | CLARK | 2450.0     | 3          |
| 10         | WELAN | 2450.0     | 4          |
| 10         | TEBAGE | 1300.0     | 5          |
| 10         | MILLER | 1300.0     | 6          |
| 20         | SCOTT | 3000.0     | 1          |
| 20         | FORD  | 3000.0     | 2          |
| 20         | JONES | 2975.0     | 3          |
| 20         | ADAMS | 1100.0     | 4          |
| 20         | SMITH | 800.0      | 5          |
| 30         | BLAKE | 2850.0     | 1          |
| 30         | ALLEN | 1600.0     | 2          |
| 30         | TURNER | 1500.0     | 3          |
| 30         | MARTIN | 1250.0     | 4          |
| 30         | WARD  | 1250.0     | 5          |
| 30         | JAMES | 950.0      | 6          |
+------------+-------+------------+------------+

SQL实现多行数据转一条

这部分说明如何使用 SQL 实现多条数据压缩为一条。

需求1：将emp表，根据job，获取该job中的所有ename作为一列，格式如下：

job	ename
job1	ename1,ename2,ename3
job2	ename4,ename5

查询语句如下：

-- 将emp表，根据job，获取该job中的所有ename作为一列
select job, wm_concat(',', ename) as ename_list
from emp
group by job;

返回结果如下：

 

需求2：在emp表中，统计每个部门的总人数，CLERK职位的人数及占比，MANAGER的总人数及占比，命令如下：

-- 在emp表中，统计每个部门的总人数，CLERK职位的人数及占比，MANAGER的总人数及占比
select deptno,
       count(empno),
       sum(case when job='CLERK' then 1 else 0 end) as clerk_cnt,
       round(sum(case when job='CLERK' then 1 else 0 end)/count(empno), 2) as clerk_rate,
       sum(case when job='MANAGER' then 1 else 0 end) as manager_cnt,
       round(sum(case when job='MANAGER' then 1 else 0 end)/count(empno), 2) as manager_rate
from emp
group by deptno;

返回结果如下：

MaxCompute SQL中的JOIN ON条件

目前MaxCompute提供了以下几种Join类型：

类型	含义
Inner Join	输出符合关联条件的数据。
Left Join	输出左表的所有记录，对于右表符合关联的数据，输出右表，没有符合的，右表补null。
Right Join	输出右表的所有记录，对于左表符合关联的数据，输出左表，没有符合的，左表补null。
Full Join	输出左表和右表的所有记录，对于没有关联上的数据，未关联的另一侧补null。
Left Semi Join	对于左表中的一条数据，如果右表存在符合关联条件的行，则输出左表。
Left Anti Join	对于左表中的一条数据，如果对于右表所有的行，不存在符合关联条件的数据，则输出左表。

 根据不同的场景，可以使用不同的Join类型来实现对应的关联操作。但是在实际使用过程中，存在如下错误示例。

A (LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI) JOIN B
ON a.key = b.key and A.ds='20180101' and B.ds='20180101';

正确的join语句如下，应该是在A和B中获取某一个分区的数据，然后在进行JOIN操作：

(SELECT * FROM A WHERE ds='20180101') A
(LEFT/RIGHT/FULL/LEFT SEMI/LEFT ANTI)  JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key

然而针对不同的Join类型，两者可能并不等价。

连接条件和执行顺序

对于如下包含JOIN和WHERE条件的语句：

(SELECT * FROM A WHERE {subquery_where_condition} A) A
JOIN
(SELECT * FROM B WHERE {subquery_where_condition} B) B
ON {on_condition}
WHERE {where_condition}

计算顺序为：

1. 子查询中的{subquery_where_condition}。
2. JOIN的{on_condition}的条件。
3. JOIN结果集合{where_condition}的计算。

对于不同的JOIN类型，过滤语句放在{subquery_where_condition}、{on_condition}和{where_condition}中，有时结果是一致的，有时候结果又是不一致的。下面分情况进行讨论。

太长不看版

以下实验的结论总结：

过滤条件放在不同的位置语义可能大不相同，如果只是进行过滤数据后再JOIN的操作，请注意以下几点：

• INNER JOIN/LEFT SEMI JOIN两侧的表达式可以随便写。
• LEFT JOIN/LEFT ANTI JOIN左表的过滤条件要放到{subquery_where_condition}或者{where_condition}，右表的过滤条件要放到{subquery_where_condition}或者{on_condition}中。
• RIGHT JOIN和LEFT JOIN相反，右表的过滤条件要放到{subquery_where_condition}或者{where_condition}，左表的过滤条件要放到{subquery_where_condition}或者{on_condition}。
• FULL OUTER JOIN只能放到{subquery_where_condition}中。

如果还是觉得规则比较复杂，最好的方法就是把过滤条件写到子查询中。

详细版：各类join操作实验示例

数据准备

执行如下的建表语句：

-- JOIN操作示例数据
CREATE TABLE JOIN_A AS
  SELECT * FROM VALUES (1, 20180101),(2, 20180101),(2, 20180102) t (key, ds);

CREATE TABLE JOIN_B AS
  SELECT * FROM VALUES (1, 20180101),(3, 20180101),(2, 20180102) t (key, ds);

表A、表B的示例数据如下：

-- 表A示例数据
key	ds
+----+---+
1	20180101
2	20180101
2	20180102

-- 表B示例数据
key	ds
+----+---+
1	20180101
3	20180101
2	20180102

表A和表B的笛卡尔乘积如下：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
1	20180101	3	20180101
1	20180101	2	20180102
2	20180101	1	20180101
2	20180101	3	20180101
2	20180101	2	20180102
2	20180102	1	20180101
2	20180102	3	20180101
2	20180102	2	20180102

inner join测试 

Inner Join的处理逻辑是将左右表进行笛卡尔乘积，然后选择满足ON表达式的行进行输出。

1. 第一种情况，在子查询中过滤：

-- inner join 情况1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

结果如下：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

2. 第二种情况，在JOIN 条件中过滤:

-- inner join 情况2
select a.*, b.*
from JOIN_A a
join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

3. 第三种情况，在JOIN后的WHERE条件过滤:

-- inner join 情况3
select a.*, b.*
from JOIN_A a
join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条，如下。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180102	2	20180102
2	20180101	2	20180102

此时对于这个结果再进行过滤A.ds='20180101' and B.ds='20180101'，结果只有1条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，将过滤条件放在三个不同的地方，得到了相同的结果。仅此该部分的结论如下：

结论：inner join中，过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}中都是等价的。

Left Join

Left Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于左表中不满足ON表达式的行，输出左表，右表补NULL。

1. 第一种情况，在子查询中过滤：

-- left join 情况1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a left join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

过滤后，左右侧有两条，右侧有一条，结果有两条：

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL

并且，left join等价于left outer join，上面命令等价于如下，查询结果相同：

select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a left outer join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

 2. 第二种情况，在JOIN 条件中过滤：

select a.*, b.*
from JOIN_A a
left join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL

3. 第三种情况，JOIN后的WHERE条件过滤:

-- left join 情况3
select a.*, b.*
from JOIN_A a
left join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

此时对于这个结果再进行过滤A.ds='20180101' and B.ds='20180101'，结果只有1条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，将过滤条件放在三个不同的地方，得到了三种不同的结果。

结论：过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。

• 对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
• 对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。

Right Join

Right Join和Left Join是类似的，只是左右表的区别，结论如下：

• 过滤条件在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。
• 对于右表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
• 对于左表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的。

Full Join

FULL Join的处理逻辑是将左右表进行笛卡尔乘积，然后对于满足ON表达式的行进行输出，对于两侧表中不满足ON表达式的行，输出有数据的表，另一侧补NULL。

1. 第一种情况，子查询中过滤：

-- full join 情况1
select a.*, b.*
from (
  select * from JOIN_A where ds='20180101'
) a full join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

过滤后，左右侧有两条，右侧有两条，结果有三条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
NULL	NULL	3	20180101

2. 第二种情况，JOIN 条件中过滤:

-- full join 情况2
select a.*, b.*
from JOIN_A a
full join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件的结果同样只有1条，则对于左表剩余的两条输出左表，右表补NULL。右表剩余的两条输出右表，左表补NULL。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	NULL	NULL
2	20180102	NULL	NULL
NULL	NULL	3	20180101
NULL	NULL	2	20180102

3. 第三种情况，JOIN后的WHERE条件过滤:

-- full join 情况3
select a.*, b.*
from JOIN_A a
full join JOIN_B b on a.key=b.key
where a.ds='20180101' and b.ds='20180101';

笛卡尔积的结果有9条，满足ON条件a.key = b.key的结果有3条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102

再对没有JOIN上的数据进行输出，另一侧补NULL，得到结果如下。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101
2	20180101	2	20180102
2	20180102	2	20180102
NULL	NULL	3	20180101

此时对于这个结果再进行过滤A.ds='20180101' and B.ds='20180101'，结果只有1条。

a.key	a.ds	b.key	b.ds
1	20180101	1	20180101

可以看到，和LEFT JOIN类似，得到了三种不同的结果。

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}均不等价。

Left Semi Join

LEFT SEMI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果匹配成功，则输出左表。这里需要注意的是由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常用来实现exists的语义。

1. 第一种情况，在子查询中过滤：

-- left semi join 情况1
select a.*
from (
  select * from JOIN_A where ds='20180101'
) a left semi join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

过滤后，左右侧有两条，最终符合a.key = b.key的只有一条。

a.key	a.ds
1	20180101

2. 第二种情况，JOIN 条件中过滤：

-- left semi join 情况2
select a.*
from JOIN_A a
left semi join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

对于左侧的三条记录，满足ON条件的结果同样只有1条。

a.key	a.ds
1	20180101

3. 第三种情况，JOIN后的WHERE条件过滤：

-- left semi join 情况3
select a.*
from JOIN_A a
left semi join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key
where a.ds='20180101';

左侧能符合ON条件的有一条。

a.key	a.ds
1	20180101

此时对于这个结果再进行过滤A.ds='20180101'，结果仍然保持1条。

a.key	a.ds
1	20180101

可以看到，LEFT SEMI JOIN和INNER JOIN类似，无论过滤条件放在哪里，结果都是一致的。

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}是等价的。

Left Anti Join

LEFT ANTI Join的处理逻辑是对于左表的每一条记录，都去和右表进行匹配，如果右表所有的记录都没有匹配成功，则输出左表。同样由于只输出左表，所以JOIN后的Where条件中不能写右侧的过滤条件。LEFT SEMI JOIN常常用来实现not exists的语义。

1. 第一种情况，子查询中过：

-- left anti join 情况1
select a.*
from (
  select * from JOIN_A where ds='20180101'
) a left anti join (
  select * from JOIN_B where ds='20180101'
) b on a.key=b.key;

过滤后，左侧有两条，右侧有两条，结果有1条。

a.key	a.ds
2	20180101

2. 第二种情况，JOIN 条件中过滤：

-- left anti join 情况2
select a.*
from JOIN_A a
  left anti join JOIN_B b on a.key=b.key and a.ds='20180101' and b.ds='20180101';

对于左侧的三条记录，只有第一条有满足ON条件的结果，所以输出剩余的两条记录。

a.key	a.ds
2	20180101
2	20180102

3. 第三种情况，JOIN后的WHERE条件过滤：

-- left anti join 情况3
select a.*
from JOIN_A a
  left anti join (
  select * from JOIN_B where ds='20180101'
  ) b on a.key=b.key
where a.ds='20180101';

左侧能通过ON条件的有两条。

a.key	a.ds
2	20180101
2	20180102

此时对于这个结果再进行过滤A.ds='20180101'，结果为1条。

a.key	a.ds
2	20180101

可以看到，LEFT ANTI JOIN中，过滤条件WHERE语句分别放在JOIN ON条件中、条件前和条件后，得到的结果是不相同的。

结论：过滤条件写在{subquery_where_condition}、{on_condition}和{where_condition}不一定等价。

• 对于左表的过滤条件，放在{subquery_where_condition}和{where_condition}是等价的。
• 对于右表的过滤条件，放在{subquery_where_condition}和{on_condition}中是等价的，右表表达式不能放在{where_condition}中。

以上内容只是针一个常用场景测试的几种不同的写法，没有具体的推导过程，对于涉及到不等值表达式的场景会更加复杂，如果您有兴趣可以尝试推导一下。