全部产品
存储与CDN 数据库 安全 应用服务 数加·人工智能 数加·大数据基础服务 互联网中间件 视频服务 开发者工具 解决方案 物联网
MaxCompute

SELECT操作

更新时间:2017-10-24 16:12:12

Select 语法介绍

命令格式如下

  1. SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  2. FROM table_reference
  3. [WHERE where_condition]
  4. [GROUP BY col_list]
  5. [ORDER BY order_condition]
  6. [DISTRIBUTE BY distribute_condition [SORT BY sort_condition] ]
  7. [LIMIT number]

在使用 Select 语句时,请注意以下几点:

  • Select 操作从表中读取数据,要读的列可以用列名指定,或者用 * 代表所有的列,一个简单的 Select 语句,如下所示:

    1. select * from sale_detail;

    若您只读取 sale_detail 的一列 shop_name,如下所示:

    1. select shop_name from sale_detail;

    在 where 中可以指定过滤的条件,如下所示:

    1. select * from sale_detail where shop_name like 'hang%';

    当使用 Select 语句屏显时,目前最多只能显示 10000 行结果。当 Select 作为子句时,无此限制,Select 子句会将全部结果返回给上层查询。

  • 在 table_reference 中支持使用嵌套子查询,如下所示:

    1. select * from (select region from sale_detail) t where region = 'shanghai';
  • where 子句支持的过滤条件,如下表所示:

过滤条件 描述
> , < , =, >=, <=, <> 关系操作符
like, rlike like和rlike的source和pattern参数均仅接受string类型。
in, not in 如果在in/not in条件后加子查询,子查询只能返回一列值,且返回值的数量不能超过1000。

在 Select 语句的 where 子句中,您可以指定分区范围,这样可以仅仅扫描表的指定部分,避免全表扫描。如下所示:

  1. SELECT sale_detail.*
  2. FROM sale_detail
  3. WHERE sale_detail.sale_date >= '2008'
  4. AND sale_detail.sale_date <= '2014';

MaxCompute SQL 的 where 子句支持 between…and 条件查询,上述 SQL 可以重写如下:

  1. SELECT sale_detail.*
  2. FROM sale_detail
  3. WHERE sale_detail.sale_date BETWEEN '2008' AND '2014';
  • distinct:如果有重复数据行时,在字段前使用 distinct,会将重复字段去重,只返回一个值,而使用 all 将返回字段中所有重复的值,不指定此选项时默认效果和 all 相同。

使用 distinct 只返回一行记录,如下所示:

  1. select distinct region from sale_detail;
  2. select distinct region, sale_date from sale_detail;
  3. -- distinct多列,distinct的作用域是 Select 的列集合,不是单个列。
  • group by:分组查询,一般 group by 是和聚合函数配合使用。在 Select 中包含聚合函数时有以下规则:

    • 用 group by 的 key 可以是输入表的列名。

    • 也可以是由输入表的列构成的表达式,不允许是 Select 语句的输出列的别名。

    • 规则 i 的优先级高于规则 ii。当规则 i 和规则 ii 发生冲突时,即 group by 的 key 即是输入表的列或表达式,又是 Select 的输出列,以规则 i 为准。

示例如下

  1. select region from sale_detail group by region;
  2. -- 直接使用输入表列名作为group by的列,可以运行
  3. select sum(total_price) from sale_detail group by region;
  4. -- region值分组,返回每一组的销售额总量,可以运行
  5. select region, sum(total_price) from sale_detail group by region;
  6. -- region值分组,返回每一组的region值(组内唯一)及销售额总量,可以运行
  7. select region as r from sale_detail group by r;
  8. -- 使用select列的别名运行,报错返回
  9. select 'China-' + region as r from sale_detail group by 'China-' + region;
  10. -- 必须使用列的完整表达式
  11. select region, total_price from sale_detail group by region;
  12. -- 报错返回,select的所有列中,没有使用聚合函数的列,必须出现在group by
  13. select region, total_price from sale_detail group by region, total_price;
  14. -- 可以运行

有这样的限制是因为:在 SQL 解析中,group by 操作通常是先于 Select 操作的,因此 group by 只能接受输入表的列或表达式为 key。

注意

  • 关于聚合函数的详情请参见 聚合函数

  • order by:对所有数据按照某几列进行全局排序。如果您希望按照降序对记录进行排序,可以使用 DESC 关键字。由于是全局排序,order by 必须与 limit 共同使用。对在使用 order by 排序时,NULL 会被认为比任何值都小,这个行为与 MySQL 一致,但是与 Oracle 不一致。

    与 group by 不同,order by 后面必须加 Select 列的别名,当 Select 某列时,如果没有指定列的别名,将列名作为列的别名。

    1. select * from sale_detail order by region;
    2. -- 报错返回,order by没有与limit共同使用
    3. select * from sale_detail order by region limit 100;
    4. select region as r from sale_detail order by region limit 100;
    5. -- 报错返回,order by后面必须加列的别名。
    6. select region as r from sale_detail order by r limit 100;
  • [limit number] 的 number 是常数,限制输出行数。当使用无 limit 的 Select 语句直接从屏幕输出查看结果时,最多只输出 5000 行。每个项目空间的这个屏显最大限制限制可能不同,可以通过控制台面板控制。

  • distribute by:对数据按照某几列的值做 hash 分片,必须使用 Select 的输出列别名。

    1. select region from sale_detail distribute by region;
    2. -- 列名即是别名,可以运行
    3. select region as r from sale_detail distribute by region;
    4. -- 报错返回,后面必须加列的别名。
    5. select region as r from sale_detail distribute by r;
  • sort by:局部排序,语句前必须加 distribute by。实际上 sort by 是对 distribute by 的结果进行局部排序。必须使用 Select 的输出列别名。

    1. select region from sale_detail distribute by region sort by region;
    2. select region as r from sale_detail sort by region;
    3. -- 没有distribute by,报错退出。
  • order by 不和 distribute by/sort by 共用,同时 group by 也不和 distribute by/sort by 共用,必须使用 Select 的输出列别名。

注意

  • order by/sort by/distribute by 的 key 必须是 Select 语句的输出列,即列的别名。

  • 在 MaxCompute SQL 解析中, order by/sort by/distribute by 是后于 Select 操作的,因此它们只能接受 Select 语句的输出列为 key。

Select 语序

按照上述 Select 语法格式书写的 Select 语句,实际上的逻辑执行顺序与标准的书写语序实际并不相同,如下语句:

  1. SELECT key, max(value) FROM src t WHERE value > 0 GROUP BY key HAVING sum(value) > 100 ORDER BY key LIMIT 100;

实际上的逻辑执行顺序是FROM->WHERE->GROUY BY->HAVING->SELECT->ORDER BY->LIMIT。order by 中只能引用 Select 列表中生成的列,而不是访问 FROM 的源表中的列。HAVING 可以访问的是 group by key 和聚合函数。Select 的时候,如果有 group by,就只能访问 group key 和聚合函数,而不是 FROM 中源表中的列。

为了避免混淆,MaxCompute 支持以执行顺序书写查询语句,例如上面的语句可以写为:

  1. FROM src t WHERE value > 0 GROUP BY key HAVING sum(value) > 100 SELECT key, max(value) ORDER BY key LIMIT 100;

子查询

子查询基本定义

普通的 Select 是从几张表中读数据,如 select column_1, column_2 … from table_name,但查询的对象也可以是另外一个 Select 操作,如下所示:

  1. select * from (select shop_name from sale_detail) a;

注意:子查询必须要有别名。

在 from 子句中,子查询可以当作一张表来使用,与其它的表或子查询进行 Join 操作,如下所示:

  1. create table shop as select * from sale_detail;
  2. select a.shop_name, a.customer_id, a.total_price from
  3. (select * from shop) a join sale_detail on a.shop_name = sale_detail.shop_name;

IN SUBQUERY / NOT IN SUBQUERY

IN SUBQUERY 与 LEFT SEMI JOIN 类似。

示例如下

  1. SELECT * from mytable1 where id in (select id from mytable2);
  2. --等效于
  3. SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

目前 MaxCompute 不仅支持 IN SUBQUERY,还支持 correlated 条件.

示例如下

  1. SELECT * from mytable1 where id in (select id from mytable2 where value = mytable1.value);

其中子查询中的 where value = mytable1.value 就是一个 correlated 条件,原有 MaxCompute 对于这种既引用了子查询中源表,又引用了外层查询源表的表达式时,会报错。现在 MaxCompute 已经支持这种用法,这样的过滤条件事实上构成了 SEMI JOIN 中 ON 条件的一部分。

NOT IN SUBQUERY,类似于 LEFT ANTI JOIN,但是也有显著不同。

示例如下

  1. SELECT * from mytable1 where id not in (select id from mytable2);
  2. --如果mytable2中的所有id都不为NULL,则等效于
  3. SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;

如果 mytable2 中有任何为 NULL 的列,则 not in 表达式会为 NULL,导致 where 条件不成立,无数据返回,此时与 LEFT ANTI JOIN 不同。

MaxCompute 1.0 版本也支持 [NOT] IN SUBQUERY 不作为 JOIN 条件,例如出现在非 WHERE 语句中,或者虽然在 WHERE 语句中,但无法转换为 JOIN 条件。当前 MaxCompute 2.0 版本仍然支持这种用法,但是此时因为无法转换为 SEMI JOIN 而必须实现启动一个单独的作业来运行 SUBQUERY,所以不支持 correlated 条件。

示例如下

  1. SELECT * from mytable1 where id in (select id from mytable2) OR value > 0;

因为 WHERE 中包含了 OR,导致无法转换为 SEMI JOIN,会单独启动作业执行子查询。

另外在处理分区表的时候,也会有特殊处理:

  1. SELECT * from sales_detail where ds in (select dt from sales_date);

其中的 ds 如果是分区列,则select dt from sales_date 会单独启动作业执行子查询,而不会转化为 SEMIJOIN,执行后的结果会逐个与 ds 比较,sales_detail 中 ds 值不在返回结果中的分区不会读取,保证分区裁剪仍然有效。

EXISTS SUBQUERY/NOT EXISTS SUBQUERY

EXISTS SUBQUERY 时,当 SUBQUERY 中有至少一行数据时候,返回 TRUE,否则 FALSE。NOT EXISTS 的时候则相反。

目前只支持含有 correlated WHERE 条件的子查询。EXISTS SUBQUERY/NOT EXISTS SUBQUERY 实现的方式是转换为 LEFT SEMI JOIN 或者 LEFT ANTI JOIN。

示例如下

  1. SELECT * from mytable1 where exists (select * from mytable2 where id = mytable1.id);
  2. --等效于
  3. SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

  1. SELECT * from mytable1 where not exists (select * from mytable2 where id = mytable1.id);
  2. --等效于
  3. SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;

UNION ALL / UNION [DISTINCT]

命令格式如下

  1. select_statement UNION ALL select_statement;
  2. select_statement UNION [DISTINCT] select_statement;
  • UNION ALL:将两个或多个 Select 操作返回的数据集联合成一个数据集,如果结果有重复行时,会返回所有符合条件的行,不进行重复行的去重处理。

  • UNION [DISTINCT]:其中 DISTINCT 可忽略。将两个或多个 Select 操作返回的数据集联合成一个数据集,如果结果有重复行时,将进行重复行的去重处理。

UNION ALL 示例如下:

  1. select * from sale_detail where region = 'hangzhou'
  2. union all
  3. select * from sale_detail where region = 'shanghai';

UNION 示例如下:

  1. SELECT * FROM src1 UNION SELECT * FROM src2;
  2. --执行的效果相当于
  3. SELECT DISTINCT * FROM (SELECT * FROM src1 UNION ALL SELECT * FROM src2) t;

注意

  • union all/union 操作对应的各个查询的列个数、名称和类型必须一致。如果列名不一致时,可以使用列的别名加以解决。

  • 一般情况下,MaxCompute 最多允许 256 个表的 union all/union,超过此限制报语法错误。

关于 UNION 后 LIMIT 的语义,如下所示:

UNION 后如果有 CLUSTER BY,DISTRIBUTE BY,SORT BY,ORDER BY 或者 LIMIT 子句,其作用于前面所有 UNION 的结果,而不是 UNION 的最后一个 select_statement。MaxCompute 目前在set odps.sql.type.system.odps2=true;的时候,也采用此行为。

示例如下

  1. set odps.sql.type.system.odps2=true;
  2. SELECT explode(array(3, 1)) AS (a) UNION ALL SELECT explode(array(0, 4, 2)) AS (a) ORDER BY a LIMIT 3;

返回结果如下:

  1. +------+
  2. | a |
  3. +------+
  4. | 0 |
  5. | 1 |
  6. | 2 |
  7. +------+

JOIN 操作

MaxCompute 的 JOIN 支持多路链接,但不支持笛卡尔积,即无 on 条件的链接。

命令格式如下

  1. join_table:
  2. table_reference join table_factor [join_condition]
  3. | table_reference {left outer|right outer|full outer|inner} join table_reference join_condition
  4. table_reference:
  5. table_factor
  6. | join_table
  7. table_factor:
  8. tbl_name [alias]
  9. | table_subquery alias
  10. | ( table_references )
  11. join_condition:
  12. on equality_expression ( and equality_expression )*

注意:equality_expression 是一个等式表达式。

left join 会从左表(shop)中返回所有的记录,即使在右表(sale_detail)中没有匹配的行。

  1. select a.shop_name as ashop, b.shop_name as bshop from shop a
  2. left outer join sale_detail b on a.shop_name=b.shop_name;
  3. -- 由于表shopsale_detail中都有shop_name列,因此需要在select子句中使用别名进行区分。

right outer join:右连接,返回右表中的所有记录,即使在左表中没有记录与它匹配。

示例如下

  1. select a.shop_name as ashop, b.shop_name as bshop from shop a
  2. right outer join sale_detail b on a.shop_name=b.shop_name;

full outer join:全连接,返回左右表中的所有记录。

示例如下

  1. select a.shop_name as ashop, b.shop_name as bshop from shop a
  2. full outer join sale_detail b on a.shop_name=b.shop_name;

在表中存在至少一个匹配时,inner join 返回行。 关键字 inner 可省略。

  1. select a.shop_name from shop a inner join sale_detail b on a.shop_name=b.shop_name;
  2. select a.shop_name from shop a join sale_detail b on a.shop_name=b.shop_name;

连接条件,只允许 and 连接的等值条件,并且最多支持 16 路 join 操作。只有在 MAPJOIN 中,可以使用不等值连接或者使用 or 连接多个条件。

  1. select a.* from shop a full outer join sale_detail b on a.shop_name=b.shop_name
  2. full outer join sale_detail c on a.shop_name=c.shop_name;
  3. -- 支持多路join链接示例,最多支持16join
  4. select a.* from shop a join sale_detail b on a.shop_name != b.shop_name;
  5. -- 不支持不等值Join链接条件,报错返回。

IMPLICIT JOIN,MaxCompute 支持如下 Join 方式:

  1. SELECT * FROM table1, table2 WHERE table1.id = table2.id;
  2. --执行的效果相当于
  3. SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;

SEMI JOIN

MaxCompute 支持 SEMI JOIN(半连接)。SEMI JOIN 中,右表只用来过滤左表的数据而不出现在结果集中。支持 LEFT SEMI JOIN 和 LEFT ANTI JOIN 两种语法。

LEFT SEMI JOIN

当 Join 条件成立时,返回左表中的数据。也就是 mytable1 中某行的 Id 在 mytable2 的所有 Id 中出现过,此行就保留在结果集中。

示例如下

  1. SELECT * from mytable1 a LEFT SEMI JOIN mytable2 b on a.id=b.id;

只会返回 mytable1 中的数据,只要 mytable1 的 Id 在 mytable2 的 Id 中出现。

LEFT ANTI JOIN

当 Join 条件不成立时,返回左表中的数据。也就是 mytable1 中某行的 Id 在 mytable2 的所有 Id 中没有出现过,此行便保留在结果集中。

示例如下

  1. SELECT * from mytable1 a LEFT ANTI JOIN mytable2 b on a.id=b.id;

只会返回 mytable1 中的数据,只要 mytable1 的 Id 在 mytable2 的 Id 没有出现。

MAPJOIN HINT

当一个大表和一个或多个小表做 Join 时,可以使用 MapJoin,性能比普通的 Join 要快很多。MapJoin 的基本原理为:在小数据量情况下,SQL 会将您指定的小表全部加载到执行 Join 操作的程序的内存中,从而加快 Join 的执行速度。

当您使用 MapJoin 时,要注意以下问题:

  • left outer join 的左表必须是大表。

  • right outer join 的右表必须是大表。

  • inner join 左表或右表均可以作为大表。

  • full outer join 不能使用 MapJoin。

  • MapJoin 支持小表为子查询。

  • 使用 MapJoin 时,需要引用小表或是子查询时,需要引用别名。

  • 在 MapJoin 中,可以使用不等值连接或者使用 or 连接多个条件。

  • 目前,MaxCompute 在 MapJoin 中最多支持指定 8 张小表,否则报语法错误。

  • 如果使用 MapJoin,则所有小表占用的内存总和不得超过 512MB。由于 MaxCompute 是压缩存储,因此小表在被加载到内存后,数据大小会急剧膨胀。此处的 512MB 限制是加载到内存后的空间大小。

  • 多个表 Join 时,最左边的两个表不能同时是 MapJoin 的表。

示例如下

  1. select /* + mapjoin(a) */
  2. a.shop_name,
  3. b.customer_id,
  4. b.total_price
  5. from shop a join sale_detail b
  6. on a.shop_name = b.shop_name;

MaxCompute SQL 不支持在普通 Join 的 on 条件中使用不等值表达式,or 逻辑等复杂的 Join 条件,但是在 MapJoin 中可以进行如上操作。

示例如下

  1. select /*+ mapjoin(a) */
  2. a.total_price,
  3. b.total_price
  4. from shop a join sale_detail b
  5. on a.total_price < b.total_price or a.total_price + b.total_price < 500;

HAVING 子句

由于 MaxCompute SQL 的 WHERE 关键字无法与合计函数一起使用,可以采用 HAVING 字句。

命令格式如下

  1. SELECT column_name, aggregate_function(column_name)
  2. FROM table_name
  3. WHERE column_name operator value
  4. GROUP BY column_name
  5. HAVING aggregate_function(column_name) operator value

示例如下

比如有一张订单表 Orders,包括客户名称(Customer),订单金额(OrderPrice),订单日期(Order_date),订单号(Order_id)四个字段。现在希望查找订单总额少于 2000 的客户。SQL 语句如下:

  1. SELECT Customer,SUM(OrderPrice) FROM Orders
  2. GROUP BY Customer
  3. HAVING SUM(OrderPrice)<2000

Explain

MaxCompute SQL 提供 Explain 操作,用来显示对应于 DML 语句的最终执行计划结构的描述。所谓执行计划就是最终用来执行 SQL 语义的程序。

命令格式如下

  1. EXPLAIN <DML query>;

Explain 的执行结果包含如下内容:

  • 对应于该 DML 语句的所有 Task 的依赖结构。

  • Task 中所有 Task 的依赖结构。

  • Task 中所有 Operator 的依赖结构。

示例如下

  1. EXPLAIN
  2. SELECT abs(a.key), b.value FROM src a JOIN src1 b ON a.value = b.value;

Explain 的输出结果会有以下三个部分:

  • 首先是 Job 间的依赖关系:

    job0 is root job

    因为该 query 只需要一个 Job(job0),所以只需要一行信息。

  • 其次是 Task 间的依赖关系:

    1. In Job job0:
    2. root Tasks: M1_Stg1, M2_Stg1
    3. J3_1_2_Stg1 depends on: M1_Stg1, M2_Stg1

    job0 包含三个 Task,M1_Stg1 和 M2_Stg1 这两个 Task 会先执行,执行完成后,再执行 J3_1_2_Stg1。

    Task 的命名规则如下:

    • 在 MaxCompute 中,共有四种 Task 类型:MapTask,ReduceTask,JoinTask 和 LocalWork。

    • Task 名称的第一个字母表示了当前 Task 的类型,如 M2Stg1 就是一个 MapTask。

    • 紧跟着第一个字母后的数字,代表了当前 Task 的 ID,这个 ID 在所有对应当前 query 的 Task 中是唯一的。

    • 之后用下划线分隔的数字代表当前 Task 的直接依赖,如 J3_1_2_Stg1 意味着当前 Task(ID 为 3)依赖 ID 为 1 和 ID 为 2 的两个 Task。

  • 第三部分即 Task 中的 Operator 结构,Operator 串描述了一个 Task 的执行语义:

    1. In Task M1_Stg1:
    2. Data source: yudi_2.src # "Data source"描述了当前Task的输入内容
    3. TS: alias: a # TableScanOperator
    4. RS: order: + # ReduceSinkOperator
    5. keys:
    6. a.value
    7. values:
    8. a.key
    9. partitions:
    10. a.value
    11. In Task J3_1_2_Stg1:
    12. JOIN: a INNER JOIN b # JoinOperator
    13. SEL: Abs(UDFToDouble(a._col0)), b._col5 # SelectOperator
    14. FS: output: None # FileSinkOperator
    15. In Task M2_Stg1:
    16. Data source: yudi_2.src1
    17. TS: alias: b
    18. RS: order: +
    19. keys:
    20. b.value
    21. values:
    22. b.value
    23. partitions:
    24. b.value

    各 Operator 的含义,如下所示:

    • TableScanOperator:描述了 query 语句中的 FROM 语句块的逻辑,Explain 结果中会显示输入表的名称(alias)。

    • SelectOperator:描述了 query 语句中的 Select 语句块的逻辑,Explain 结果中会显示向下一个 operator 传递的列,多个列由逗号分隔。

      • 如果是列的引用,会显示成”< alias >.< column_name >”。

      • 如果是表达式的结果,会显示函数的形式,如“func1(arg1_1, arg1_2, func2(arg2_1, arg2_2))”。

      • 如果是常量,则直接显示值内容。

    • FilterOperator:描述了 query 语句中的 WHERE 语句块的逻辑,Explain 结果中会显示一个 WHERE 条件表达式,形式类似 SelectOperator 的显示规则。

    • JoinOperator:描述了 query 语句中的 Join 语句块的逻辑,Explain 结果中会显示哪些表用哪种方式 Join 在一起。

    • GroupByOperator:描述了聚合操作的逻辑,如果 query 中使用了聚合函数,就会出现该结构,Explain 结果中会显示聚合函数的内容。

    • ReduceSinkOperator:描述了 Task 间数据分发操作的逻辑,如果当前 Task 的结果会传递给另一个 Task,则必然需要在当前 Task 的最后,使用 ReduceSinkOperator 来执行数据分发操作。Explain 的结果中会显示输出结果的排序方式、分发的 key、value 以及用来求 hash 值的列。

    • FileSinkOperator:描述了最终数据的存储操作,如果 query 中有 Insert 语句块,Explain 结果中会显示目标表名称。

    • LimitOperator:描述了 query 语句中的 LIMIT 语句块的逻辑,Explain 结果中会显示 limit 数。

    • MapjoinOperator:类似 JoinOperator,描述了大表的 Join 操作。

注意:

如果 query 足够复杂,Explain 的结果太多,会导致触发 API 的限制,使得您看到的 Explain 结果不完整。这时候可以通过拆分 query,各部分分别 Explain,来了解 Job 的结构。

Common Table Expression(CTE)

MaxCompute 支持 SQL 标准的 CTE,提高 SQL 语句的可读性与执行效率。

命令格式如下所示:

  1. WITH
  2. cte_name AS
  3. (
  4. cte_query
  5. )
  6. [,cte_name2 AS
  7. (
  8. cte_query2
  9. )
  10. ,……]
  • cte_name,指 CTE 的名称,不能与当前 with 子句中的其他 CTE 的名称相同。查询中任何使用到 cte_name 标识符的地方,都是指 CTE。

  • cte_query,是一个 Select 语句,它产生的结果集用于填充 CTE。

示例如下

  1. INSERT OVERWRITE TABLE srcp PARTITION (p='abc')
  2. SELECT * FROM (
  3. SELECT a.key, b.value
  4. FROM (
  5. SELECT * FROM src WHERE key IS NOT NULL ) a
  6. JOIN (
  7. SELECT * FROM src2 WHERE value > 0 ) b
  8. ON a.key = b.key
  9. ) c
  10. UNION ALL
  11. SELECT * FROM (
  12. SELECT a.key, b.value
  13. FROM (
  14. SELECT * FROM src WHERE key IS NOT NULL ) a
  15. LEFT OUTER JOIN (
  16. SELECT * FROM src3 WHERE value > 0 ) b
  17. ON a.key = b.key AND b.key IS NOT NULL
  18. )d;

顶层的 UNION 两侧各为一个 Join,Join 的左表是相同的查询。通过写子查询的方式,只能重复这段代码。

使用 CTE 的方式重写以上语句:

  1. with
  2. a as (select * from src where key is not null),
  3. b as (select * from src2 where value>0),
  4. c as (select * from src3 where value>0),
  5. d as (select a.key,b.value from a join b on a.key=b.key ),
  6. e as (select a.key,c.value from a left outer join c on a.key=c.key and c.key is not null )
  7. insert overwrite table srcp partition (p='abc')
  8. select * from d union all select * from e;

重写后,a 对应的子查询只需写一次,便可在后面进行重用。CTE 的 with 子句中可以指定多个子查询,像使用变量一样在整个语句中反复重用。除重用外,也不必反复嵌套。

本文导读目录