全部产品
MaxCompute

SELECT操作

更新时间:2017-09-21 18:20:08   分享:   

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条件查询,具体可以参考其他函数中的between and表达式说明。

  • 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中包含聚合函数时有以下规则:

    1. 用group by的key可以是输入表的列名;
    2. 也可以是由输入表的列构成的表达式,不允许是select语句的输出列的别名;
    3. 规则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;

子查询中关于subquery和exists的介绍如下:

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的客户。现在我们可以写如下语句:

  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的依赖结构

我们以下面的query为例:

  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语句的可读性与执行效率。

CTE的语法结构:

  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 子句中可以指定多个子查询,像使用变量一样在整个语句中反复重用。除重用外,也不必反复嵌套。

本文导读目录
本文导读目录
以上内容是否对您有帮助?